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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`travel_moveRaids`()
BEGIN
/**
 * Desplaza los travels en el futuro y avisa a los compradores
 *
 */
	DECLARE vDone BOOL DEFAULT FALSE;
	DECLARE vBuyerEmail VARCHAR(40);
	DECLARE vTravelLink TEXT;
	DECLARE vMailBody TEXT DEFAULT '';

	DECLARE vCur CURSOR FOR
		SELECT GROUP_CONCAT(DISTINCT
				CONCAT('https://salix.verdnatura.es/#!/travel/',
					ttm.travelFk,
					'/summary ')
				ORDER BY ttm.travelFk SEPARATOR '\n\r') travelLink,
			CONCAT(u.name, '@verdnatura.es') buyerEmail
			FROM tTravelToMove ttm
				JOIN entry e ON e.travelFk = ttm.travelFk
				JOIN buy b ON b.entryFk = e.id
				JOIN item i ON i.id = b.itemFk
				JOIN itemType it ON it.id = i.typeFk
				JOIN account.user u ON u.id = it.workerFk
			GROUP BY u.name;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;

	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
		RESIGNAL;
	END;

	CREATE OR REPLACE TEMPORARY TABLE tTravelToMove
		SELECT id travelFk,
				util.VN_CURDATE() + INTERVAL daysInForward  DAY newLanded
			FROM travel
			WHERE isRaid
				AND daysInForward;

	START TRANSACTION;

	UPDATE travel tr
			JOIN tTravelToMove ttm ON ttm.travelFk = tr.id
		SET tr.landed = ttm.newLanded;

	OPEN vCur;

	l: LOOP
		SET vDone = FALSE;
		FETCH vCur INTO vTravelLink, vBuyerEmail;

		IF vDone THEN
			LEAVE l;
		END IF;

		CALL `vn`.`mail_insert`(
			vBuyerEmail,
			'noreply@verdnatura.es',
			'Cambio de fecha en Redadas',
			CONCAT('Se ha movido los siguientes travels: \n\r ', vTravelLink));
	END LOOP;
	CLOSE vCur;
	COMMIT;
	DROP TEMPORARY TABLE tTravelToMove;
END$$
DELIMITER ;