salix/db/routines/vn/procedures/client_getMana.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`client_getMana`()
BEGIN
/**
 * Devuelve el mana de los clientes de la tabla tmp.client(id)
 * @return tmp.clientMana(id, mana)
 *
 */
	DECLARE vFromDated DATE;

	DELETE tc FROM tmp.client tc
		JOIN client c ON c.id = tc.id
		WHERE c.typeFk <> 'normal';

	SELECT MAX(dated) INTO vFromDated
		FROM clientManaCache;

	CREATE OR REPLACE TEMPORARY TABLE tmp.clientMana
		SELECT id, SUM(mana) mana
			FROM
				(
				SELECT tc.id, cmc.mana
					FROM clientManaCache cmc
						JOIN tmp.client tc ON tc.id = cmc.clientFk
					WHERE cmc.dated = vFromDated
				UNION ALL
				SELECT tc.id, s.quantity * value
					FROM ticket t
						JOIN address a ON a.id = t.addressFk
						JOIN tmp.client tc ON tc.id = a.clientFk
						JOIN sale s on s.ticketFk = t.id
						JOIN saleComponent sc on sc.saleFk = s.id
						JOIN component comp ON sc.componentFk = comp.id
						JOIN componentType ct ON ct.id = comp.typeFk AND ct.code = 'mana'
					WHERE t.shipped > vFromDated
						AND t.shipped < util.tomorrow()
				UNION ALL
				SELECT tc.id, -amountPaid
					FROM receipt r
						JOIN `client` c ON c.id = r.clientFk
						JOIN tmp.client tc ON tc.id = c.id
						JOIN accounting a ON r.bankFk = a.id
							AND a.code = 'mana'
					WHERE r.payed > vFromDated
						AND r.payed <= util.VN_CURDATE()
				UNION ALL
				SELECT tc.id, g.amount
					FROM greuge g
						JOIN `client` c ON c.id = g.clientFk
						JOIN tmp.client tc ON tc.id = c.id
						JOIN greugeType gt ON gt.id = g.greugeTypeFk AND gt.code = 'mana'
					WHERE g.shipped > vFromDated
						AND g.shipped <= util.VN_CURDATE()
				) sub
			GROUP BY id;
END$$
DELIMITER ;