77 lines
1.4 KiB
SQL
77 lines
1.4 KiB
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 ;
|