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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`ticket_cloneAll`(
	vTicketFk INT,
	vNewShipped DATE,
	vWithWarehouse BOOLEAN,
	OUT vNewTicketFk INT
)
BEGIN
	DECLARE vDone BOOLEAN DEFAULT FALSE;
	DECLARE vOldSaleFk INT;
	DECLARE vNewSaleFk INT;

	DECLARE cur1 CURSOR FOR
		SELECT id
			FROM sale
			WHERE ticketFk = vTicketFk;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;

	SET vNewShipped = IFNULL(vNewShipped, util.VN_CURDATE());

	CALL ticket_Clone(vTicketFk, vNewTicketFk);

	UPDATE ticket
		SET landed = TIMESTAMPADD(DAY, DATEDIFF(vNewShipped, shipped), landed),
			shipped = vNewShipped,
			warehouseFk = IF(vWithWarehouse, warehouseFk, NULL)
		WHERE id = vNewTicketFk;

	OPEN cur1;

	read_loop: LOOP

		FETCH cur1 INTO vOldSaleFk;

		IF vDone THEN
			LEAVE read_loop;
		END IF;

		INSERT INTO sale(
				ticketFk,
				itemFk,
				quantity,
				concept,
				price,
				foreignPrice,
				discount,
				priceFixed,
				isPriceFixed
			)SELECT vNewTicketFk,
				itemFk,
				quantity,
				concept,
				price,
				foreignPrice,
				discount,
				priceFixed,
				isPriceFixed
				FROM sale
				WHERE id = vOldSaleFk;

		SELECT MAX(id) INTO vNewSaleFk
			FROM sale
			WHERE ticketFk = vNewTicketFk;

		INSERT INTO saleComponent(saleFk, componentFk, value, isGreuge)
			SELECT vNewSaleFk, componentFk, value, isGreuge
				FROM saleComponent
				WHERE saleFk = vOldSaleFk;

	END LOOP;

	CLOSE cur1;

END$$
DELIMITER ;