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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`balance_create`(
	vStartingMonth INT,
	vEndingMonth INT,
	vCompany INT,
	vIsConsolidated BOOLEAN,
	vInterGroupSalesIncluded BOOLEAN
)
BEGIN
/**
 * Crea un balance financiero para una empresa durante 
 * un período de tiempo determinado.
 *
 * @param vStartingMonth Mes de inicio del período
 * @param vEndingMonth Mes de finalización del período
 * @param vCompany Identificador de la empresa
 * @param vIsConsolidated Indica si se trata de un balance consolidado
 * @param vInterGroupSalesIncluded Indica si se incluyen las ventas del grupo
 */
	DECLARE intGAP INT DEFAULT 7;
	DECLARE vYears INT DEFAULT 2;
	DECLARE vYear TEXT;
	DECLARE vOneYearAgo TEXT;
	DECLARE vTwoYearsAgo TEXT;
	DECLARE vQuery TEXT;
	DECLARE vConsolidatedGroup INT;
	DECLARE vStartingDate DATE DEFAULT '2020-01-01';
	DECLARE vCurYear INT DEFAULT YEAR(util.VN_CURDATE());
	DECLARE vStartingYear INT DEFAULT vCurYear - 2;
	DECLARE vTable TEXT;

	SET vTable = util.quoteIdentifier('balanceNestTree');
	SET vYear = util.quoteIdentifier(vCurYear);
	SET vOneYearAgo = util.quoteIdentifier(vCurYear-1);
	SET vTwoYearsAgo = util.quoteIdentifier(vCurYear-2);

	-- Solicitamos la tabla tmp.nest, como base para el balance.
	DROP TEMPORARY TABLE IF EXISTS tmp.nest;

	EXECUTE IMMEDIATE CONCAT(
		'CREATE TEMPORARY TABLE tmp.nest
			SELECT node.id
					,CONCAT( REPEAT(REPEAT(" ",?), COUNT(parent.id) - 1), 
					node.name) name,
					node.lft,
					node.rgt,
					COUNT(parent.id) - 1 depth,
					CAST((node.rgt - node.lft - 1) / 2 AS DECIMAL) sons
				FROM ', vTable, ' node,
					', vTable, ' parent
				WHERE node.lft BETWEEN parent.lft AND parent.rgt
				GROUP BY node.id
				ORDER BY node.lft')
		USING intGAP;

	CREATE OR REPLACE TEMPORARY TABLE tmp.balance
		SELECT * FROM tmp.nest;

		SELECT companyGroupFk INTO vConsolidatedGroup
			FROM company
			WHERE id = vCompany;

	CREATE OR REPLACE TEMPORARY TABLE tCompanyReceiving
		SELECT id companyFk
			FROM company
			WHERE id = vCompany
				OR companyGroupFk = IF(vIsConsolidated, vConsolidatedGroup, NULL);

	CREATE OR REPLACE TEMPORARY TABLE tCompanyIssuing
		SELECT id companyFk
			FROM supplier p;

		IF NOT vInterGroupSalesIncluded THEN

			DELETE ci
				FROM tCompanyIssuing ci
					JOIN company e on e.id = ci.companyFk
				WHERE e.companyGroupFk = vConsolidatedGroup;

		END IF;

	-- Se calculan las facturas que intervienen, 
	-- para luego poder servir el desglose desde aqui.
	CREATE OR REPLACE TEMPORARY TABLE tmp.balanceDetail
		SELECT cr.companyFk receivingId,
				ci.companyFk issuingId,
				YEAR(IFNULL(r.bookEntried,IFNULL(r.booked, r.issued))) `year`,
				MONTH(IFNULL(r.bookEntried,IFNULL(r.booked, r.issued))) `month`,
				expenseFk,
				SUM(taxableBase) amount
			FROM invoiceIn r
				JOIN invoiceInTax ri on ri.invoiceInFk = r.id
				JOIN tCompanyReceiving cr on cr.companyFk = r.companyFk
				JOIN tCompanyIssuing ci ON ci.companyFk = r.supplierFk
			WHERE COALESCE(r.bookEntried, r.booked, r.issued) >= vStartingDate
				AND r.isBooked
			GROUP BY expenseFk, `year`, `month`, ci.companyFk, cr.companyFk;

	INSERT INTO tmp.balanceDetail(
			receivingId,
			issuingId,
			`year`,
			`month`,
			expenseFk,
			amount)
		SELECT em.companyFk,
				em.companyFk,
				`year`,
				`month`,
				expenseFk,
				SUM(em.amount)
			FROM expenseManual em
				JOIN tCompanyReceiving er ON er.companyFk = em.companyFk
			WHERE `year` >= vStartingYear
				AND `month` BETWEEN vStartingMonth AND vEndingMonth
			GROUP BY expenseFk, `year`, `month`, em.companyFk;

	DELETE FROM tmp.balanceDetail
		WHERE `month` < vStartingMonth
			OR `month` > vEndingMonth;

	-- Ahora el balance
	EXECUTE IMMEDIATE CONCAT(
		'ALTER TABLE tmp.balance
			ADD COLUMN ', vTwoYearsAgo ,' INT(10) NULL ,
			ADD COLUMN ', vOneYearAgo ,' INT(10) NULL ,
			ADD COLUMN ', vYear,' INT(10) NULL ,
			ADD COLUMN expenseFk VARCHAR(10) NULL,
			ADD COLUMN expenseName VARCHAR(45) NULL');

	-- Añadimos los gastos, para facilitar el formulario
	UPDATE tmp.balance b
			JOIN balanceNestTree bnt on bnt.id = b.id
			JOIN expense e ON e.id = bnt.expenseFk COLLATE utf8_general_ci
		SET b.expenseFk = e.id COLLATE utf8_general_ci,
			b.expenseName = e.name COLLATE utf8_general_ci ;

	-- Rellenamos los valores de primer nivel, los que corresponden 
	-- a los gastos simples.
	WHILE vYears >= 0 DO
		SET vQuery = CONCAT(
			'UPDATE tmp.balance b
					JOIN (
						SELECT expenseFk, SUM(amount) amount
							FROM tmp.balanceDetail
							WHERE year = ?
							GROUP BY expenseFk
					) sub on sub.expenseFk = b.expenseFk COLLATE utf8_general_ci
				SET ', util.quoteIdentifier(vCurYear - vYears), ' = - amount');

		EXECUTE IMMEDIATE vQuery
			USING vCurYear - vYears;

		SET vYears = vYears - 1;
	END WHILE;

	-- Añadimos las ventas.
	EXECUTE IMMEDIATE CONCAT(
		'UPDATE tmp.balance b
				JOIN (
					SELECT SUM(IF(year = ?, venta, 0)) y2,
							SUM(IF(year = ?, venta, 0)) y1,
							SUM(IF(year = ?, venta, 0)) y0,
							c.Gasto
						FROM bs.ventas_contables c
							JOIN tCompanyReceiving cr ON cr.companyFk = c.empresa_id
						WHERE month BETWEEN ? AND ?
						GROUP BY c.Gasto
				) sub ON sub.gasto = b.expenseFk COLLATE utf8_general_ci
			SET b.', vTwoYearsAgo, '= IFNULL(b.', vTwoYearsAgo, ', 0) + sub.y2,
				b.', vOneYearAgo, '= IFNULL(b.', vOneYearAgo, ', 0) + sub.y1,
				b.', vYear, '= IFNULL(b.', vYear, ', 0) + sub.y0')
		USING vCurYear-2,
			vCurYear-1,
			vCurYear,
			vStartingMonth,
			vEndingMonth;

	-- Ventas intra grupo.
	IF NOT vInterGroupSalesIncluded THEN

		SELECT lft, rgt INTO @groupLft, @groupRgt
			FROM tmp.balance b
			WHERE TRIM(b.`name`) = 'Grupo';

		DELETE
			FROM tmp.balance
			WHERE lft BETWEEN @groupLft AND @groupRgt;

	END IF;

	-- Rellenamos el valor de los padres con la suma de los hijos.
	CREATE OR REPLACE TEMPORARY TABLE tmp.balance_aux
		SELECT * FROM tmp.balance;

	EXECUTE IMMEDIATE
		CONCAT('UPDATE tmp.balance b
			JOIN (
				SELECT b1.id,
						b1.name,
						SUM(b2.', vYear,') thisYear,
						SUM(b2.', vOneYearAgo,') oneYearAgo,
						SUM(b2.', vTwoYearsAgo,') twoYearsAgo
					FROM tmp.nest b1
						JOIN tmp.balance_aux b2 on b2.lft BETWEEN b1.lft and b1.rgt
					GROUP BY b1.id
				)sub ON sub.id = b.id
			SET b.', vYear, ' = thisYear,
				b.', vOneYearAgo, ' = oneYearAgo,
				b.', vTwoYearsAgo, ' = twoYearsAgo');

	SELECT *, CONCAT('',IFNULL(expenseFk,'')) newgasto
		FROM tmp.balance;

	DROP TEMPORARY TABLE IF EXISTS tCompanyReceiving, tCompanyIssuing;
	
END$$
DELIMITER ;