52 lines
1.2 KiB
SQL
52 lines
1.2 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`entry_updateComission`(vCurrency INT)
|
|
BEGIN
|
|
/**
|
|
* Actualiza la comision de las entradas de hoy a futuro y las recalcula
|
|
*
|
|
* @param vCurrency id del tipo de moneda(SAR,EUR,USD,GBP,JPY)
|
|
*/
|
|
DECLARE vCurrencyName VARCHAR(25);
|
|
DECLARE vComission INT;
|
|
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
|
BEGIN
|
|
ROLLBACK;
|
|
RESIGNAL;
|
|
END;
|
|
|
|
START TRANSACTION;
|
|
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.recalcEntryCommision
|
|
SELECT e.id
|
|
FROM vn.entry e
|
|
JOIN vn.travel t ON t.id = e.travelFk
|
|
JOIN vn.warehouse w ON w.id = t.warehouseInFk
|
|
WHERE t.landed >= util.VN_CURDATE()
|
|
AND e.currencyFk = vCurrency
|
|
AND NOT e.isBooked;
|
|
|
|
SET vComission = currency_getCommission(vCurrency);
|
|
|
|
UPDATE vn.entry e
|
|
JOIN tmp.recalcEntryCommision tmp ON tmp.id = e.id
|
|
SET e.commission = vComission;
|
|
|
|
SELECT `name` INTO vCurrencyName
|
|
FROM currency
|
|
WHERE id = vCurrency;
|
|
|
|
CALL entry_recalc();
|
|
|
|
COMMIT;
|
|
|
|
SELECT util.notification_send(
|
|
'entry-update-comission',
|
|
JSON_OBJECT('currencyName', vCurrencyName, 'referenceCurrent', vComission),
|
|
NULL
|
|
);
|
|
|
|
DROP TEMPORARY TABLE tmp.recalcEntryCommision;
|
|
END$$
|
|
DELIMITER ;
|