96 lines
1.8 KiB
SQL
96 lines
1.8 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `stock`.`buyOut_refresh`(
|
|
`vTable` VARCHAR(255),
|
|
`vId` INT,
|
|
`vSource` VARCHAR(255))
|
|
BEGIN
|
|
/**
|
|
* This procedure contains the common code used to refresh the out lot cache.
|
|
*
|
|
* @param vTable The id source table
|
|
* @param vId The lot id
|
|
* @param vSource The lot source
|
|
* @table tLotStatus Lots to modify
|
|
* @table tLotAlive Updated/Created alive lots data
|
|
*/
|
|
DECLARE vHasLots BOOL;
|
|
|
|
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
|
BEGIN
|
|
ROLLBACK;
|
|
RESIGNAL;
|
|
END;
|
|
|
|
IF vTable = 'lot' THEN
|
|
SELECT COUNT(*) > 0 INTO vHasLots FROM tLotStatus;
|
|
|
|
IF NOT vHasLots THEN
|
|
INSERT INTO tLotStatus
|
|
SET lotFk = vId,
|
|
isExcluded = TRUE,
|
|
isIncluded = FALSE;
|
|
END IF;
|
|
END IF;
|
|
|
|
START TRANSACTION;
|
|
|
|
-- Delete excluded/deleted/dead outs
|
|
|
|
DELETE o FROM buyOut o
|
|
JOIN tLotStatus ls ON ls.lotFk = o.outFk
|
|
WHERE NOT ls.isIncluded;
|
|
|
|
-- Delete undead out picks
|
|
|
|
UPDATE buyLot l
|
|
JOIN buyPick p ON p.lotFk = l.lotFk
|
|
JOIN tLotStatus ls ON ls.lotFk = p.outFk
|
|
SET l.isSync = FALSE,
|
|
l.available = l.available + p.quantity
|
|
WHERE ls.isExcluded OR ls.isIncluded;
|
|
|
|
DELETE p FROM buyPick p
|
|
JOIN tLotStatus ls ON ls.lotFk = p.outFk
|
|
WHERE ls.isExcluded OR ls.isIncluded;
|
|
|
|
-- Update alive outs
|
|
|
|
INSERT INTO buyOut (
|
|
outFk,
|
|
source,
|
|
isSync,
|
|
warehouseFk,
|
|
dated,
|
|
itemFk,
|
|
quantity,
|
|
lack,
|
|
created,
|
|
isPicked
|
|
)
|
|
SELECT
|
|
lotFk,
|
|
vSource,
|
|
FALSE,
|
|
warehouseFk,
|
|
dated,
|
|
itemFk,
|
|
quantity,
|
|
quantity,
|
|
created,
|
|
isPicked
|
|
FROM tLotAlive
|
|
ON DUPLICATE KEY UPDATE
|
|
source = VALUES(source),
|
|
warehouseFk = VALUES(warehouseFk),
|
|
dated = VALUES(dated),
|
|
itemFk = VALUES(itemFk),
|
|
quantity = VALUES(quantity),
|
|
lack = VALUES(lack),
|
|
created = VALUES(created),
|
|
isPicked = VALUES(isPicked),
|
|
isSync = VALUES(isSync);
|
|
|
|
COMMIT;
|
|
END$$
|
|
DELIMITER ;
|