117 lines
2.6 KiB
SQL
117 lines
2.6 KiB
SQL
DELIMITER $$
|
||
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`entry_splitByShelving`(vShelvingFk VARCHAR(3), vFromEntryFk INT, vToEntryFk INT)
|
||
BEGIN
|
||
/**
|
||
* Divide las compras entre dos entradas de acuerdo con lo ubicado en una matr<74>cula
|
||
*
|
||
* @param vShelvingFk Identificador de vn.shelving
|
||
* @param vFromEntryFk Entrada origen
|
||
* @param vToEntryFk Entrada destino
|
||
*/
|
||
DECLARE vBuyFk INT;
|
||
DECLARE vIshStickers INT;
|
||
DECLARE vBuyStickers INT;
|
||
DECLARE vDone BOOLEAN DEFAULT FALSE;
|
||
|
||
DECLARE cur CURSOR FOR
|
||
SELECT bb.id buyFk,
|
||
LEAST(bb.stickers, FLOOR(ish.visible / ish.packing)) ishStickers,
|
||
bb.stickers buyStickers
|
||
FROM itemShelving ish
|
||
JOIN (SELECT b.id, b.itemFk, b.stickers
|
||
FROM buy b
|
||
WHERE b.entryFk = vFromEntryFk
|
||
ORDER BY b.stickers DESC
|
||
LIMIT 10000000000000000000) bb ON bb.itemFk = ish.itemFk
|
||
WHERE ish.shelvingFk = vShelvingFk COLLATE utf8_general_ci
|
||
AND NOT ish.isSplit
|
||
GROUP BY ish.id;
|
||
|
||
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;
|
||
|
||
OPEN cur;
|
||
|
||
read_loop: LOOP
|
||
SET vDone = FALSE;
|
||
|
||
FETCH cur INTO vBuyFk, vIshStickers, vBuyStickers;
|
||
|
||
IF vDone THEN
|
||
LEAVE read_loop;
|
||
END IF;
|
||
|
||
IF vIshStickers = vBuyStickers THEN
|
||
UPDATE buy
|
||
SET entryFk = vToEntryFk
|
||
WHERE id = vBuyFk;
|
||
ELSE
|
||
UPDATE buy
|
||
SET stickers = stickers - vIshStickers,
|
||
quantity = stickers * packing
|
||
WHERE id = vBuyFk;
|
||
|
||
INSERT INTO buy(entryFk,
|
||
itemFk,
|
||
quantity,
|
||
buyingValue,
|
||
freightValue,
|
||
isIgnored,
|
||
stickers,
|
||
packing,
|
||
`grouping`,
|
||
groupingMode,
|
||
comissionValue,
|
||
packageValue,
|
||
location,
|
||
packagingFk,
|
||
price1,
|
||
price2,
|
||
price3,
|
||
minPrice,
|
||
workerFk,
|
||
isChecked,
|
||
isPickedOff,
|
||
ektFk,
|
||
weight,
|
||
deliveryFk,
|
||
itemOriginalFk)
|
||
SELECT
|
||
vToEntryFk,
|
||
itemFk,
|
||
vIshStickers * packing,
|
||
buyingValue,
|
||
freightValue,
|
||
isIgnored,
|
||
vIshStickers,
|
||
packing,
|
||
`grouping`,
|
||
groupingMode,
|
||
comissionValue,
|
||
packageValue,
|
||
location,
|
||
packagingFk,
|
||
price1,
|
||
price2,
|
||
price3,
|
||
minPrice,
|
||
workerFk,
|
||
isChecked,
|
||
isPickedOff,
|
||
ektFk,
|
||
weight,
|
||
deliveryFk,
|
||
itemOriginalFk
|
||
FROM buy
|
||
WHERE id = vBuyFk;
|
||
|
||
UPDATE buy SET printedStickers = vIshStickers WHERE id = LAST_INSERT_ID();
|
||
END IF;
|
||
|
||
UPDATE itemShelving
|
||
SET isSplit = TRUE
|
||
WHERE shelvingFk = vShelvingFk COLLATE utf8_general_ci;
|
||
END LOOP;
|
||
CLOSE cur;
|
||
END$$
|
||
DELIMITER ;
|