salix/db/routines/bi/procedures/defaulting.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `bi`.`defaulting`(IN `vDate` DATE)
BEGIN
	DECLARE vDone BOOLEAN;
	DECLARE vClient INT;
	DECLARE vAmount INT;
	DECLARE vDued DATE;
	DECLARE vAmountInvoice DECIMAL(10,2);
	DECLARE vGraceDays INT;
	DECLARE defaulters CURSOR FOR
		SELECT d.client, d.amount, pm.graceDays
			FROM bi.defaulters d 
				JOIN vn.client c ON c.id = d.client
				JOIN vn.payMethod pm ON pm.id = c.payMethodFk
			WHERE hasChanged AND date = vDate;
		
	DECLARE invoices CURSOR FOR
		SELECT dued Vencimiento, amount importe FROM vn.invoiceOut
			WHERE issued >= '2016-01-01' AND clientFk = vClient ORDER BY issued DESC;
		
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;

	DELETE FROM bi.defaulters WHERE date = vDate;

	INSERT INTO bi.defaulters(client, date, amount)
		SELECT c.id, vDate, IFNULL(FLOOR(SUM(cro.amount)),0) AS amount
			FROM vn.`client` c
				LEFT JOIN bi.customerRiskOverdue cro ON c.id = cro.customer_id
			GROUP BY c.id;
	
	-- marcamos si ha cambiado y heredamos la fecha defaulterSince
	UPDATE bi.defaulters d
		LEFT JOIN (
			SELECT * FROM(
				SELECT client, amount , defaulterSince, frozened FROM bi.defaulters
					WHERE date <= TIMESTAMPADD(DAY,-1, vDate)
					ORDER BY date DESC
					LIMIT 10000000000000000000) t GROUP BY client
		) yesterday using(client)
	SET d.hasChanged = (IFNULL(d.amount,0) <> IFNULL(yesterday.amount,0)),
		d.defaulterSince = yesterday.defaulterSince,
		d.frozened = yesterday.frozened
	WHERE d.date = vDate ;
	
	OPEN defaulters;
	defaulters: LOOP
		SET vDone = FALSE;
		SET vAmount = 0;
		FETCH defaulters INTO vClient,vAmount, vGraceDays;
		IF vDone THEN
			LEAVE defaulters;
		END IF;
		OPEN invoices;
		invoices:LOOP
			
			FETCH invoices INTO vDued, vAmountInvoice;
			IF vDone THEN
				LEAVE invoices;
			END IF;
			
			IF TIMESTAMPADD(DAY, vGraceDays, vDued) <= vDate THEN
				SET vAmount = vAmount - vAmountInvoice;
				IF vAmount <= 0 THEN
									
					UPDATE defaulters SET defaulterSince = vDued
						WHERE client = vClient and date = vDate;
						
					SET vAmount = 0;
					LEAVE invoices;
				END IF;
			END IF;
		END LOOP;
		CLOSE invoices;     
	END LOOP;
		CLOSE defaulters;
	
	DELETE FROM defaulters
		WHERE amount = 0 
			AND hasChanged = FALSE
			AND `date` = vDate;
		
	UPDATE defaulters d
			JOIN vn.config ON TRUE
		SET d.frozened = NULL
		WHERE `date` = vDate 
			AND d.amount <= config.defaultersMaxAmount;
			
	CALL vn.clientFreeze();
	
	-- actualizamos defaulting
	DELETE FROM bi.defaulting WHERE date = vDate;

	INSERT INTO bi.defaulting(date, amount)
		SELECT vDate, SUM(amount)
			FROM bi.defaulters
			WHERE date = vDate and amount > 0;

END$$
DELIMITER ;