salix/db/routines/stock/procedures/buyOut_refresh.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 ;