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, discount, priceFixed, isPriceFixed)
			SELECT vNewTicketFk, itemFk, quantity, concept, price, 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 ;