DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `bs`.`manaSpellers_actualize`()
BEGIN
/**	
 * Recalcula el valor del campo con el modificador de precio 
 * para el componente de maná automático.
 */
	UPDATE vn.workerMana wm 
			JOIN (
				SELECT c.lastSalesPersonFk, 
					FLOOR(SUM(s.amount) / 12) amount
					FROM salesByclientSalesPerson s
						JOIN vn.client c ON c.id = s.clientFk
					WHERE s.dated BETWEEN util.VN_CURDATE() - INTERVAL 1 YEAR AND util.VN_CURDATE()
					GROUP BY c.lastSalesPersonFk
			)avgPortfolioWeight ON avgPortfolioWeight.lastSalesPersonFk = wm.workerFk 
			JOIN vn.salespersonConfig spc
		SET wm.pricesModifierRate = 
			IFNULL(
					GREATEST(
						spc.manaMinRate, 
						LEAST(
							spc.manaMaxRate, 
							ROUND( - wm.amount / avgPortfolioWeight.amount, 3)
						)
					)
			,0);
END$$
DELIMITER ;