26 lines
631 B
MySQL
26 lines
631 B
MySQL
|
DELIMITER $$
|
||
|
|
|
||
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost`
|
||
|
|
PROCEDURE `vn`.`client_setPackagesDiscountFactor`()
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Set the discount factor for the packages of the clients.
|
||
|
|
*/
|
||
|
|
UPDATE client c
|
||
|
|
JOIN (
|
||
|
|
SELECT t.clientFk,
|
||
|
|
LEAST((
|
||
|
|
SUM(t.packages) / COUNT(DISTINCT DATE(t.shipped))
|
||
|
|
) / cc.packagesOptimum, 1) discountFactor
|
||
|
|
FROM ticket t
|
||
|
|
JOIN clientConfig cc ON TRUE
|
||
|
|
WHERE t.shipped > util.VN_CURDATE() - INTERVAL cc.monthsToCalcOptimumPrice MONTH
|
||
|
|
AND t.packages
|
||
|
|
GROUP BY t.clientFk
|
||
|
|
) ca ON c.id = ca.clientFk
|
||
|
|
SET c.packagesDiscountFactor = ca.discountFactor;
|
||
|
|
|
||
|
|
END$$
|
||
|
|
|
||
|
|
DELIMITER ;
|