salix/db/routines/vn/procedures/itemShelving_add.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`itemShelving_add`(
	vShelvingCode VARCHAR(10),
	vBarcode VARCHAR(22),
	vQuantity INT,
	vPackagingFk VARCHAR(10),
	vGrouping INT,
	vPacking INT,
	vWarehouseFk INT
)
BEGIN
/**
 * Añade registro o lo actualiza si ya existe.
 *
 * @param vShelvingCode matrícula del carro
 * @param vBarcode el id del registro
 * @param vQuantity indica la cantidad del producto
 * @param vPackagingFk el packaging del producto en itemShelving, NULL para coger el de la ultima compra
 * @param vGrouping el grouping del producto en itemShelving, NULL para coger el de la ultima compra
 * @param vPacking el packing del producto, NULL para coger el de la ultima compra
 * @param vWarehouseFk indica el sector
 *
 **/
	DECLARE vItemFk INT;
	DECLARE vBuyFk INT;
	DECLARE vShelvingFk INT;

	SELECT id INTO vBuyFk 
		FROM buy WHERE id = vBarcode;

	SELECT id INTO vShelvingFk
		FROM shelving
		WHERE code COLLATE utf8_unicode_ci = vShelvingCode;

	SELECT barcodeToItem(vBarcode) INTO vItemFk;

	IF vBuyFk IS NULL THEN
		CALL util.throw('The buy is required');
	END IF;

	IF vPacking IS NULL
	THEN
		SET vPacking = itemPacking(vBarcode, vWarehouseFk);
		SET vQuantity = vQuantity * vPacking;
	END IF;

	IF (SELECT COUNT(*) FROM itemShelving
			WHERE shelvingFk = vShelvingFk
				AND itemFk  = vItemFk
				AND packing  = vPacking
				AND buyFk = vBuyFk) THEN

		UPDATE itemShelving
			SET visible = visible + vQuantity,
				available = available + vQuantity
			WHERE shelvingFk = vShelvingFk
				AND itemFk = vItemFk
				AND packing = vPacking;

	ELSE

		INSERT INTO itemShelving(
					itemFk,
					shelvingFk,
					visible,
					grouping,
					packing,
					packagingFk,
					buyFk)
			SELECT vItemFk,
					vShelvingFk,
					vQuantity,
					IFNULL(vGrouping, b.grouping),
					IFNULL(vPacking, b.packing),
					IFNULL(vPackagingFk, b.packagingFk),
					id
				FROM buy b
				WHERE id = vBuyFk;
	END IF; 
END$$
DELIMITER ;