99 lines
2.4 KiB
SQL
99 lines
2.4 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`buy_beforeUpdate`
|
|
BEFORE UPDATE ON `buy`
|
|
FOR EACH ROW
|
|
trig:BEGIN
|
|
DECLARE vGenericFk INT;
|
|
DECLARE vGenericInDate BOOL;
|
|
DECLARE vIsInventory BOOL;
|
|
DECLARE vDefaultEntry INT;
|
|
DECLARE vBuyerFk INT;
|
|
|
|
IF @isTriggerDisabled THEN
|
|
LEAVE trig;
|
|
END IF;
|
|
|
|
IF NOT (NEW.entryFk <=> OLD.entryFk) OR
|
|
NOT (NEW.quantity <=> OLD.quantity) OR
|
|
NOT (NEW.buyingValue <=> OLD.buyingValue) OR
|
|
NOT (NEW.packing <=> OLD.packing)
|
|
THEN
|
|
|
|
CALL entry_isEditable(OLD.entryFk);
|
|
END IF;
|
|
|
|
SET NEW.editorFk = account.myUser_getId();
|
|
|
|
SELECT defaultEntry INTO vDefaultEntry
|
|
FROM entryConfig;
|
|
|
|
IF (NOT OLD.entryFk <=> NEW.entryFk OR NOT OLD.printedStickers <=> NEW.printedStickers)
|
|
AND NEW.printedStickers <> 0 THEN
|
|
SELECT defaultEntry INTO vDefaultEntry
|
|
FROM entryConfig;
|
|
|
|
IF NEW.entryFk = vDefaultEntry THEN
|
|
CALL util.throw('There can be no lines with printed labels on the default entry');
|
|
END IF;
|
|
END IF;
|
|
|
|
IF NOT NEW.`grouping` <=> OLD.`grouping` THEN
|
|
CALL buy_checkGrouping(NEW.`grouping`);
|
|
END IF;
|
|
|
|
-- Generics
|
|
SELECT i.genericFk INTO vGenericFk
|
|
FROM item i
|
|
WHERE i.id = NEW.itemFk;
|
|
|
|
IF vGenericFk THEN
|
|
SELECT COUNT(*) INTO vGenericInDate
|
|
FROM genericAllocation ga
|
|
JOIN entry e ON e.id = NEW.entryFk
|
|
JOIN travel tr on tr.id = e.travelFk
|
|
WHERE ga.itemFk = vGenericFk
|
|
AND tr.landed BETWEEN ga.startDated AND ga.endDated;
|
|
IF vGenericInDate THEN
|
|
SET NEW.itemOriginalFk = NEW.itemFk;
|
|
SET NEW.itemFk = vGenericFk;
|
|
END IF;
|
|
END IF;
|
|
|
|
SELECT COUNT(*) INTO vIsInventory
|
|
FROM entry e
|
|
JOIN supplier s ON s.id = e.supplierFk
|
|
JOIN entryConfig ec ON ec.inventorySupplierFk = e.supplierFk
|
|
WHERE e.id = NEW.entryFk;
|
|
|
|
IF vIsInventory THEN
|
|
IF NOT NEW.printedStickers <=> OLD.printedStickers THEN
|
|
CALL util.throw("Stickers cannot be modified if they are inventory");
|
|
END IF;
|
|
|
|
IF OLD.entryFk <> NEW.entryFk THEN
|
|
CALL util.throw("Cannot transfer lines to inventory entry");
|
|
END IF;
|
|
END IF;
|
|
|
|
IF NEW.quantity < 0 THEN
|
|
SET NEW.isIgnored = TRUE;
|
|
END IF;
|
|
|
|
IF NOT (NEW.itemFk <=> OLD.itemFk) THEN
|
|
SELECT it.workerFk INTO vBuyerFk
|
|
FROM item i
|
|
JOIN itemType it ON it.id = i.typeFk
|
|
WHERE i.id = NEW.itemFk;
|
|
|
|
SET NEW.buyerFk = vBuyerFk;
|
|
END IF;
|
|
|
|
IF NOT (NEW.itemFk <=> OLD.itemFk) OR
|
|
NOT (OLD.entryFk <=> NEW.entryFk) THEN
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.buysToCheck
|
|
SELECT NEW.id;
|
|
CALL buy_checkItem();
|
|
END IF;
|
|
END$$
|
|
DELIMITER ;
|