81 lines
1.9 KiB
SQL
81 lines
1.9 KiB
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 ; |