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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`clientGreugeSpray`(IN vClientFk INT, IN onlyForHisOwner BOOL, IN vWorkerCode VARCHAR(3), IN vWithMana BOOLEAN)
BEGIN

	DECLARE vGreuge DECIMAL(10,2);
	DECLARE vOwner INT;
	DECLARE vTotalSale INT;
	DECLARE vGreugeTypeFk INT DEFAULT 5; -- HERENCIA
	DECLARE vGreugeTypeMana INT DEFAULT 3;-- Maná
	DECLARE vMana DECIMAL(10,2);

	SELECT vn.clientGetMana(vClientFk) INTO vMana;

	IF vWithMana AND vMana THEN

		INSERT INTO vn.greuge(	clientFk,
								description,
								amount,
								shipped,
								greugeTypeFk)
		VALUES(	vClientFk,
				'Desasignación',
				-1 * vMana,
				util.VN_CURDATE(),
				vGreugeTypeMana);

	END IF;

	SELECT sum(amount) INTO vGreuge
		FROM vn.greuge
		WHERE clientFk = vClientFk;

	IF vGreuge != 0 THEN

		IF LENGTH(vWorkerCode) = 0 THEN

			SELECT salesPersonFk INTO vOwner
				FROM vn.client
				WHERE id = vClientFk;

		ELSE
		
			SELECT id INTO vOwner
				FROM vn.worker
				WHERE code = vWorkerCode COLLATE utf8_general_ci;

		END IF;
		
		DROP TEMPORARY TABLE IF EXISTS tmp.clientList;
		CREATE TEMPORARY TABLE tmp.clientList
			SELECT DISTINCT t.clientFk, floor(cr.yearSale / 12) monthSale
				FROM vn.ticket t
					JOIN vn.client c ON c.id = t.clientFk
					JOIN vn.workerMana wm ON wm.workerFk = c.salesPersonFk
					JOIN vn.claimRatio cr ON cr.clientFk = c.id
				WHERE wm.workerFk = IF(onlyForHisOwner, vOwner, wm.workerFk)
				AND t.shipped >= TIMESTAMPADD(MONTH,-1,util.VN_CURDATE())
				AND c.id != vClientFk
				HAVING monthSale > 100;
					
		SELECT SUM(monthSale) INTO vTotalSale
			FROM tmp.clientList;

		INSERT INTO vn.greuge(clientFk, description, amount, shipped, greugeTypeFk)
			SELECT clientFk, CONCAT('Cliente: ',vClientFk), vGreuge * monthSale / vTotalSale, util.VN_CURDATE(), vGreugeTypeFk
				FROM tmp.clientList
			UNION ALL
			SELECT vClientFk, 'Reparto greuge', -vGreuge, util.VN_CURDATE(), vGreugeTypeFk;

	END IF;
    
END$$
DELIMITER ;