salix/db/routines/bs/procedures/manaCustomerUpdate.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `bs`.`manaCustomerUpdate`()
BEGIN
	DECLARE vToDated DATE;
	DECLARE vFromDated DATE;
	DECLARE vForDeleteDated DATE;
	DECLARE vManaId INT;
	DECLARE vManaAutoId INT;
	DECLARE vClaimManaId INT;
	DECLARE vManaBankId INT;
	DECLARE vManaGreugeTypeId INT;
	DECLARE vManaFromDays INT;
	DECLARE vManaToDays INT;

	SELECT id INTO vManaId
		FROM vn.component WHERE code = 'mana';
		
	SELECT id INTO vManaAutoId
		FROM vn.component WHERE code = 'autoMana';
	
	SELECT id INTO vClaimManaId
		FROM vn.component WHERE code = 'manaClaim';
	
	SELECT id INTO vManaBankId
		FROM vn.accounting WHERE code = 'mana';
	
	SELECT id INTO vManaGreugeTypeId
		FROM vn.greugeType WHERE code = 'mana';

	SELECT manaFromDays, manaToDays
		INTO vManaFromDays, vManaToDays
		FROM vn.salespersonConfig;

	SELECT MAX(dated) INTO vFromDated
		FROM vn.clientManaCache;

	DELETE FROM vn.clientManaCache
		WHERE dated = vFromDated;
	
	SELECT MAX(dated) INTO vFromDated 
		FROM vn.clientManaCache;
			
	IF vFromDated IS NULL THEN
		SELECT manaDateFrom
			INTO vFromDated
			FROM vn.salespersonConfig;
	END IF;

	WHILE vFromDated + INTERVAL vManaToDays DAY < util.VN_CURDATE() DO
		SELECT vFromDated + INTERVAL vManaToDays DAY,
				vFromDated - INTERVAL vManaFromDays DAY 
			INTO  vToDated,
				vForDeleteDated;
			
		DELETE FROM vn.clientManaCache
			WHERE dated <= vForDeleteDated;

		INSERT INTO vn.clientManaCache(clientFk, mana, dated)
			SELECT Id_Cliente,
					SUM(mana),
					vToDated 
				FROM (
					SELECT a.clientFk Id_Cliente, s.quantity * sc.value mana
						FROM vn.ticket t
							JOIN vn.address a ON a.id = t.addressFk
							JOIN vn.sale s ON s.ticketFk = t.id
							JOIN vn.saleComponent sc ON sc.saleFk = s.id
						WHERE sc.componentFk IN (vManaAutoId, vManaId, vClaimManaId)
							AND t.shipped > vFromDated 
							AND DATE(t.shipped) <= vToDated
					UNION ALL
					SELECT clientFk, - amountPaid
						FROM vn.receipt
						WHERE bankFk = vManaBankId
							AND payed > vFromDated 
							AND payed <= vToDated
					UNION ALL
					SELECT clientFk, amount
						FROM vn.greuge
						WHERE greugeTypeFk = vManaGreugeTypeId
							AND shipped > vFromDated 
							AND shipped <= vToDated
					UNION ALL
					SELECT clientFk, mana
						FROM vn.clientManaCache
						WHERE dated = vFromDated
				) sub
				GROUP BY Id_Cliente
				HAVING Id_Cliente;

		SET vFromDated = vToDated;
	END WHILE;
END$$
DELIMITER ;