salix/db/routines/vn/procedures/company_getSuppliersDebt.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`company_getSuppliersDebt`(vSelf INT, vMonthsAgo INT)
BEGIN
/**
 * Generates a temporary table containing outstanding payments to suppliers.
 *
 * @param vSelf company id
 * @param vMonthAgo time interval to be consulted
 */
	DECLARE vStartingDate DATETIME DEFAULT TIMESTAMPADD (MONTH,- vMonthsAgo,util.VN_CURDATE());
	DECLARE vCurrencyEuroFk INT;
	DECLARE vStartDate DATE;
	DECLARE vInvalidBalances DOUBLE;

	SELECT dated, invalidBalances INTO vStartDate, vInvalidBalances  FROM supplierDebtConfig;
	SELECT id INTO vCurrencyEuroFk FROM currency WHERE code = 'EUR';

	DROP TEMPORARY TABLE IF EXISTS tOpeningBalances;
	CREATE TEMPORARY TABLE tOpeningBalances (
		supplierFk INT NOT NULL,
		companyFk INT NOT NULL,
		openingBalances DOUBLE NOT NULL,
		closingBalances DOUBLE NOT NULL,
		currencyFk INT NOT NULL,
		PRIMARY KEY (supplierFk, companyFk, currencyFk)
	) ENGINE = MEMORY;

	-- Calculates the opening and closing balance for each supplier
	INSERT INTO tOpeningBalances
		SELECT supplierFk,
			companyFk,
			SUM(amount * isBeforeStarting) AS openingBalances,
			SUM(amount) closingBalances,
			currencyFk
			FROM (
				SELECT p.supplierFk,
						p.companyFk,
						IF (p.currencyFk = vCurrencyEuroFk, p.amount, p.divisa) AS amount,
						p.dueDated < vStartingDate isBeforeStarting,
						p.currencyFk
					FROM payment p
					WHERE p.received > vStartDate
						AND p.companyFk = vSelf
					UNION ALL
				SELECT r.supplierFk,
						r.companyFk,
						- IF (r.currencyFk = vCurrencyEuroFk, rv.amount, rv.foreignValue) AS Total,
						rv.dueDated < vStartingDate isBeforeStarting,
						r.currencyFk
					FROM invoiceIn r
						INNER JOIN invoiceInDueDay rv ON r.id = rv.invoiceInFk
					WHERE r.issued > vStartDate
						AND r.isBooked
						AND r.companyFk = vSelf
			) sub GROUP BY companyFk, supplierFk, currencyFk;

	DROP TEMPORARY TABLE IF EXISTS tPendingDuedates;
	CREATE TEMPORARY TABLE tPendingDuedates (
		id INT auto_increment,
		expirationId INT,
		dated DATE,
		supplierFk INT NOT NULL,
		companyFk INT NOT NULL,
		amount DECIMAL(10, 2) NOT NULL,
		currencyFk INT NOT NULL,
		pending DECIMAL(10, 2) DEFAULT 0,
		balance DECIMAL(10, 2) DEFAULT 0,
		endingBalance DECIMAL(10, 2) DEFAULT 0,
		isPayment BOOLEAN,
		isReconciled BOOLEAN,
		PRIMARY KEY (id),
		INDEX (supplierFk, companyFk, currencyFk)
	) ENGINE = MEMORY;

	INSERT INTO tPendingDuedates (
		expirationId,
		dated,
		supplierFk,
		companyFk,
		amount,
		currencyFk,
		isPayment,
		isReconciled
	)SELECT p.id,
			p.dueDated,
			p.supplierFk,
			p.companyFk,
			IF (p.currencyFk = vCurrencyEuroFk, p.amount, p.divisa),
			p.currencyFk,
			TRUE isPayment,
			p.isConciliated
		FROM payment p
		WHERE p.dueDated >= vStartingDate
			AND p.companyFk = vSelf
	UNION ALL
	SELECT r.id,
			rv.dueDated,
			r.supplierFk,
			r.companyFk,
			-IF (r.currencyFk = vCurrencyEuroFk, rv.amount, rv.foreignValue),
			r.currencyFk,
			FALSE isPayment,
			TRUE
		FROM invoiceIn r
			LEFT JOIN tOpeningBalances si ON r.companyFk = si.companyFk
				AND r.supplierFk = si.supplierFk
				AND r.currencyFk = si.currencyFk
			JOIN invoiceInDueDay rv ON r.id = rv.invoiceInFk
		WHERE rv.dueDated >= vStartingDate
			AND (si.closingBalances IS NULL OR si.closingBalances <> 0)
			AND r.isBooked
			AND r.companyFk = vSelf
		ORDER BY supplierFk, companyFk, companyFk, dueDated, isPayment DESC, id;
	-- Now, we calculate the outstanding amount for each receipt in descending order
	SET @risk := 0.0;
	SET @supplier := 0.0;
	SET @company := 0.0;
	SET @moneda := 0.0;
	SET @pending := 0.0;
	SET @day := util.VN_CURDATE();

	UPDATE tPendingDuedates vp
		LEFT JOIN tOpeningBalances si ON vp.companyFk = si.companyFk
			AND vp.supplierFk = si.supplierFk
			AND vp.currencyFk = si.currencyFk
		SET vp.balance = @risk := (
			IF (
				@company <> vp.companyFk
				OR @supplier <> vp.supplierFk
				OR @moneda <> vp.currencyFk,
				IFNULL(si.openingBalances, 0),
				@risk
				) +
			vp.amount
		),
		-- if there is a change of company or supplier or currency, the balance is reset
		vp.pending = @pending := IF (
			@company <> vp.companyFk
			OR @supplier <> vp.supplierFk
			OR @moneda <> vp.currencyFk
			OR @day <> vp.dated,
			vp.amount * (NOT vp.isPayment),
			@pending + vp.amount
		),
		vp.companyFk = @company := vp.companyFk,
		vp.supplierFk = @supplier := vp.supplierFk,
		vp.currencyFk = @moneda := vp.currencyFk,
		vp.dated = @day := vp.dated,
		vp.balance = @risk,
		vp.pending = @pending;

	CREATE OR REPLACE TEMPORARY TABLE tRowsToDelete ENGINE = MEMORY
		SELECT expirationId,
			dated,
			supplierFk,
			companyFk,
			currencyFk,
			balance
		FROM tPendingDuedates
		WHERE balance < vInvalidBalances
			AND balance > - vInvalidBalances;

	DELETE vp.*
		FROM tPendingDuedates vp
			JOIN tRowsToDelete rd ON (
				vp.dated < rd.dated
				OR (vp.dated = rd.dated AND vp.expirationId <= rd.expirationId)
			)
			AND vp.supplierFk = rd.supplierFk
			AND vp.companyFk = rd.companyFk
			AND vp.currencyFk = rd.currencyFk
		WHERE NOT vp.isPayment;

	SELECT vp.expirationId,
			vp.dated,
			vp.supplierFk,
			vp.companyFk,
			vp.currencyFk,
			vp.amount,
			vp.pending,
			vp.balance,
			s.payMethodFk,
			vp.isPayment,
			vp.isReconciled,
			vp.endingBalance,
			cr.amount clientRiskAmount,
			co.CEE
		FROM tPendingDuedates vp
			LEFT JOIN supplier s ON s.id = vp.supplierFk
			LEFT JOIN client c ON c.fi = s.nif
			LEFT JOIN clientRisk cr ON cr.clientFk = c.id
				AND cr.companyFk = vp.companyFk
			LEFT JOIN supplierAccount sa ON sa.supplierFk = s.id
			LEFT JOIN bankEntity be ON be.id = sa.bankEntityFk
			LEFT JOIN country co ON co.id = be.countryFk
		GROUP BY vp.id;

	DROP TEMPORARY TABLE tOpeningBalances;
	DROP TEMPORARY TABLE tPendingDuedates;
	DROP TEMPORARY TABLE tRowsToDelete;
END$$
DELIMITER ;