26 lines
631 B
SQL
26 lines
631 B
SQL
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 ;
|