94 lines
2.2 KiB
SQL
94 lines
2.2 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`expedition_selfConsumptionPackaging`(
|
|
vSelf INT,
|
|
vAction ENUM('add', 'remove')
|
|
)
|
|
proc:BEGIN
|
|
/**
|
|
* Maneja el consumo de cajas para autoconsumo, permitiendo
|
|
* añadir o quitar cajas utilizadas según la acción indicada.
|
|
*
|
|
* @param vSelf Id de expedición
|
|
*/
|
|
DECLARE vClientFk INT;
|
|
DECLARE vAddressFk INT;
|
|
DECLARE vItemFk INT;
|
|
DECLARE vItemName VARCHAR(50);
|
|
DECLARE vWarehouseFk INT;
|
|
DECLARE vCreated DATE;
|
|
DECLARE vTicketFk INT;
|
|
DECLARE vSaleFk INT;
|
|
DECLARE vQuantity INT;
|
|
|
|
IF vAction NOT IN ('add', 'remove') THEN
|
|
CALL util.throw('Action not supported');
|
|
END IF;
|
|
|
|
SELECT pc.clientSelfConsumptionFk,
|
|
pc.addressSelfConsumptionFk,
|
|
i.id,
|
|
i.name,
|
|
t.warehouseFk,
|
|
e.created
|
|
INTO vClientFk,
|
|
vAddressFk,
|
|
vItemFk,
|
|
vItemName,
|
|
vWarehouseFk,
|
|
vCreated
|
|
FROM expedition e
|
|
LEFT JOIN packaging p ON p.id = e.packagingFk
|
|
LEFT JOIN item i ON i.id = p.itemFk
|
|
JOIN ticket t ON t.id = e.ticketFk
|
|
JOIN productionConfig pc
|
|
WHERE e.id = vSelf;
|
|
|
|
IF vClientFk IS NULL OR vItemFk IS NULL THEN
|
|
LEAVE proc;
|
|
END IF;
|
|
|
|
SET vCreated = DATE(vCreated);
|
|
|
|
SELECT id INTO vTicketFk
|
|
FROM ticket
|
|
WHERE shipped BETWEEN vCreated AND util.dayEnd(vCreated)
|
|
AND clientFk = vClientFk
|
|
AND addressFk = vAddressFk
|
|
AND warehouseFk = vWarehouseFk;
|
|
|
|
IF vTicketFk IS NULL AND vAction = 'add' THEN
|
|
INSERT INTO ticket(clientFk, warehouseFk, shipped, nickname, addressFk)
|
|
VALUES (vClientFk, vWarehouseFk, vCreated, 'CAJAS AUTOCONSUMO', vAddressFk);
|
|
|
|
SET vTicketFk = LAST_INSERT_ID();
|
|
END IF;
|
|
|
|
SELECT id, quantity INTO vSaleFk, vQuantity
|
|
FROM sale
|
|
WHERE itemFk = vItemFk
|
|
AND ticketFk = vTicketFk
|
|
LIMIT 1;
|
|
|
|
IF vAction = 'add' THEN
|
|
IF vSaleFk IS NOT NULL THEN
|
|
UPDATE sale
|
|
SET quantity = quantity + 1
|
|
WHERE id = vSaleFk;
|
|
ELSE
|
|
INSERT INTO sale(itemFk, ticketFk, concept, quantity)
|
|
VALUES (vItemFk, vTicketFk, vItemName, 1);
|
|
END IF;
|
|
ELSE
|
|
IF vSaleFk IS NOT NULL THEN
|
|
IF vQuantity > 1 THEN
|
|
UPDATE sale
|
|
SET quantity = quantity - 1
|
|
WHERE id = vSaleFk;
|
|
ELSE
|
|
DELETE FROM sale WHERE id = vSaleFk;
|
|
END IF;
|
|
END IF;
|
|
END IF;
|
|
END$$
|
|
DELIMITER ;
|