124 lines
2.7 KiB
SQL
124 lines
2.7 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`entry_splitByShelving`(
|
|
vShelvingCode VARCHAR(10),
|
|
vFromEntryFk INT,
|
|
vToEntryFk INT
|
|
)
|
|
BEGIN
|
|
/**
|
|
* Divide las compras entre dos entradas de
|
|
* acuerdo con lo ubicado en una matricula.
|
|
*
|
|
* @param vShelvingCode Código 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 shelving sh ON sh.id = ish.shelvingFk
|
|
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 sh.code = vShelvingCode 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 ish
|
|
JOIN shelving sh ON sh.id = ish.shelvingFk
|
|
SET ish.isSplit = TRUE
|
|
WHERE sh.code = vShelvingCode COLLATE utf8_general_ci;
|
|
END LOOP;
|
|
CLOSE cur;
|
|
END$$
|
|
DELIMITER ;
|