45 lines
1.3 KiB
MySQL
45 lines
1.3 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`clientFreeze`()
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Congela diariamente aquellos clientes que son morosos sin recobro,
|
||
|
|
* pero que no sean trabajadores,
|
||
|
|
* y que el riesgo no sea menor que cero
|
||
|
|
* hasta que no se gire la remesa no se congelan a los clientes de giro
|
||
|
|
*/
|
||
|
|
DECLARE vIsRemittanced BOOLEAN;
|
||
|
|
|
||
|
|
SELECT id into vIsRemittanced
|
||
|
|
FROM receipt
|
||
|
|
WHERE invoiceFk LIKE 'REMESA%'
|
||
|
|
AND payed > util.firstDayOfMonth(util.VN_CURDATE())
|
||
|
|
LIMIT 1;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.clientGetDebt;
|
||
|
|
CREATE TEMPORARY TABLE tmp.clientGetDebt
|
||
|
|
(INDEX (clientFk))
|
||
|
|
SELECT clientFk
|
||
|
|
FROM bs.defaulter
|
||
|
|
WHERE created = util.VN_CURDATE()
|
||
|
|
AND amount;
|
||
|
|
|
||
|
|
CALL client_getDebt(util.VN_CURDATE());
|
||
|
|
|
||
|
|
UPDATE client c
|
||
|
|
JOIN bi.defaulters d ON d.client = c.id AND d.date = util.VN_CURDATE()
|
||
|
|
JOIN config ON TRUE
|
||
|
|
LEFT JOIN recovery r ON r.clientFk = c.id AND r.finished IS NULL
|
||
|
|
LEFT JOIN payMethod pm ON pm.id = c.payMethodFk
|
||
|
|
LEFT JOIN tmp.risk rk ON rk.clientFk = c.id
|
||
|
|
SET c.isFreezed = TRUE,
|
||
|
|
d.frozened = util.VN_CURDATE()
|
||
|
|
WHERE (d.amount > config.defaultersMaxAmount
|
||
|
|
AND rk.risk > 0)
|
||
|
|
AND c.typeFk = 'normal'
|
||
|
|
AND r.id IS NULL
|
||
|
|
AND (vIsRemittanced OR pm.code <> 'bankDraft');
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE tmp.clientGetDebt;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|