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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`item_devalueA2`(
	vSelf INT, 
	vShelvingCode VARCHAR(10) COLLATE utf8mb3_general_ci, 
	vBuyingValue DECIMAL(10,4),
	vQuantity INT
)
BEGIN
/**
 * Devalua un item modificando su calidad de A1 a A2.
 * Si no existe el item A2 lo crea y genera los movimientos de las entradas 
 * de almacén y shelvings correspondientes
 *
 * @param vSelf Id de artículo a devaluar
 * @param vShelvingCode Código de shelving / ubicación
 * @param vBuyingValue Nuevo precio de coste
 * @param vQuantity Cantidad del ítem a pasar a A2
 */
	DECLARE vItemA2Fk INT;
	DECLARE vLastBuyFk BIGINT;
	DECLARE vA1BuyFk INT;
	DECLARE vA2BuyFk INT;
	DECLARE vTargetEntryFk INT;
	DECLARE vTargetEntryDate DATE;
	DECLARE vTargetItemShelvingFk BIGINT;
	DECLARE vWarehouseFk INT;
	DECLARE vCacheFk INT;
	DECLARE vLastEntryFk INT;
	DECLARE vCurrentVisible INT;
	DECLARE vDevalueTravelFk INT;
	DECLARE vCurdate DATE;
	DECLARE vBuyingValueOriginal DECIMAL(10,4);

	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
		RESIGNAL;
	END;

	IF (SELECT TRUE FROM item WHERE id = vSelf AND (category <> 'A1' OR category IS NULL)) THEN 
		CALL util.throw('Item has not category A1');
	END IF; 

	SELECT warehouseFk INTO vWarehouseFk
		FROM userMultiConfig 
		WHERE userFk = account.myUser_getId();

	IF NOT vWarehouseFk OR  vWarehouseFk IS NULL THEN 
		CALL util.throw ('Operator has not a valid warehouse');
	END IF;

	IF vQuantity <= 0 OR vQuantity IS NULL THEN 
		CALL util.throw ('The quantity is incorrect');
	END IF;

	SELECT util.VN_CURDATE() INTO vCurdate;
	
	SELECT t.id INTO vDevalueTravelFk
		FROM travel t
			JOIN travelConfig tc
		WHERE t.shipped = vCurdate
			AND t.landed = vCurdate
			AND t.warehouseInFk = vWarehouseFk
			AND t.warehouseOutFk = tc.devalueWarehouseOutFk
			AND t.agencyModeFk = tc.devalueAgencyModeFk
		LIMIT 1;

	IF NOT vDevalueTravelFk OR vDevalueTravelFk IS NULL THEN
		INSERT INTO travel ( 
				shipped, 
				landed, 
				warehouseInFk, 
				warehouseOutFk, 
				`ref`, 
				isReceived, 
				agencyModeFk)
			SELECT vCurdate, 
					vCurdate, 
					vWarehouseFk, 
					tc.devalueWarehouseOutFk, 
					tc.devalueRef, 
					TRUE, 
					tc.devalueAgencyModeFk
				FROM travelConfig tc;
		SET vDevalueTravelFk = LAST_INSERT_ID();	
	END IF;

	SELECT id, DATE(dated) INTO vTargetEntryFk, vTargetEntryDate
		FROM `entry` e
 		WHERE DATE(dated) = vCurdate
			AND typeFk = 'devaluation'
			AND travelFk = vDevalueTravelFk
		ORDER BY created DESC
		LIMIT 1;

	CALL buy_getUltimate(vSelf, vWarehouseFk, vCurdate);

	SELECT b.entryFk, bu.buyFk,IFNULL(b.buyingValue, 0) INTO vLastEntryFk, vLastBuyFk, vBuyingValueOriginal
		FROM tmp.buyUltimate bu
			JOIN vn.buy b ON b.id = bu.buyFk
		WHERE bu.itemFk = vSelf
			AND bu.warehouseFk = vWarehouseFk;

	IF vBuyingValue > vBuyingValueOriginal THEN
			CALL util.throw ('Price not valid');
	END IF;

	IF vLastEntryFk IS NULL OR vLastBuyFk IS NULL THEN
		CALL util.throw ('The item has not a buy');
	END IF;

	SELECT ish.id, ish.visible INTO vTargetItemShelvingFk, vCurrentVisible
		FROM itemShelving ish
			JOIN shelving sh ON sh.id = ish.shelvingFk
		WHERE sh.code = vShelvingCode
			AND ish.itemFk = vSelf
		LIMIT 1;
	
	IF vCurrentVisible IS NULL THEN
		CALL util.throw ('The shelving has not a visible for this item');
	END IF;

	IF vQuantity > vCurrentVisible THEN
		CALL util.throw('Quantity is greater than visible');
	END IF;	
		
	START TRANSACTION;

	IF NOT vTargetEntryFk OR vTargetEntryFk IS NULL 
			OR NOT vTargetEntryDate <=> vCurdate THEN
		INSERT INTO entry(
			travelFk,
			supplierFk,
			dated,
			commission,
			currencyFk,
			companyFk,
			clonedFrom,
			typeFk
		)
		SELECT vDevalueTravelFk,
				supplierFk,
				vCurdate,
				commission,
				currencyFk,
				companyFk,
				vLastEntryFk,
				'devaluation'
			FROM entry
			WHERE id = vLastEntryFk;
			
		SET vTargetEntryFk 	= LAST_INSERT_ID();	
	END IF;
	
	SELECT i.id INTO vItemA2Fk
		FROM item i
			JOIN (
				SELECT i.id,
						i.name,
						i.subname,
						i.value5,
						i.value6,
						i.value7,
						i.value8,
						i.value9,
						i.value10,
						i.NumberOfItemsPerCask,
						i.EmbalageCode,
						i.quality
					FROM item i
					WHERE i.id = vSelf
			)i2 ON i2.name <=> i.name
				AND i2.subname <=> i.subname
				AND i2.value5 <=> i.value5
				AND i2.value6 <=> i.value6
				AND i2.value8 <=> i.value8
				AND i2.value9 <=> i.value9
				AND i2.value10 <=> i.value10
				AND i2.NumberOfItemsPerCask <=> i.NumberOfItemsPerCask
				AND i2.EmbalageCode <=> i.EmbalageCode
				AND i2.quality <=> i.quality
		WHERE i.id <> i2.id 
			AND i.category = 'A2'
		LIMIT 1;

	IF vItemA2Fk IS NULL THEN
		INSERT INTO item (
				equivalent, 
				name, 
				`size`, 
				stems, 
				minPrice, 
				isToPrint, 
				family, 
				box, 
				category, 
				originFk, 
				doPhoto, 
				image, 
				inkFk, 
				intrastatFk, 
				hasMinPrice, 
				created, 
				comment, 
				typeFk, 
				generic, 
				producerFk, 
				description, 
				density, 
				relevancy, 
				expenseFk, 
				isActive, 
				longName, 
				subName, 
				minimum, 
				upToDown, 
				supplyResponseFk, 
				hasKgPrice, 
				isFloramondo, 
				isFragile, 
				numberOfItemsPerCask, 
				embalageCode, 
				quality, 
				stemMultiplier, 
				itemPackingTypeFk, 
				packingOut, 
				genericFk, 
				isLaid, 
				lastUsed,
				weightByPiece, 
				editorFk, 
				recycledPlastic, 
				nonRecycledPlastic) 
			SELECT equivalent, 
					name, 
					`size`, 
					stems, 
					minPrice, 
					isToPrint, 
					family, 
					box, 
					'A2', 
					originFk, 
					doPhoto, 
					image, 
					inkFk, 
					intrastatFk, 
					hasMinPrice, 
					created, 
					comment, 
					typeFk, 
					generic, 
					producerFk, 
					description, 
					density, 
					relevancy, 
					expenseFk, 
					isActive, 
					longName, 
					subName,
					minimum, 
					upToDown, 
					supplyResponseFk, 
					hasKgPrice, 
					isFloramondo, 
					isFragile, 
					numberOfItemsPerCask, 
					embalageCode, 
					quality, 
					stemMultiplier, 
					itemPackingTypeFk, 
					packingOut, 
					genericFk, 
					isLaid, 
					lastUsed,
					weightByPiece, 
					editorFk, 
					recycledPlastic, 
					nonRecycledPlastic
				FROM item 
				WHERE id = vSelf;

		SET vItemA2Fk = LAST_INSERT_ID();

		INSERT INTO itemTag (itemFk, tagFk, `value`, intValue, priority, editorFk) 
			SELECT vItemA2Fk, tagFk, `value`, intValue, priority, editorFk
				FROM itemTag 
				WHERE id = vSelf;

		UPDATE itemTaxCountry itc
				JOIN itemTaxCountry itc2 ON itc2.itemFk = vSelf
					AND itc2.countryFk = itc.countryFk
			SET itc2.taxClassFk = itc.taxClassFk
			WHERE itc.id = vItemA2Fk;

		INSERT INTO itemBotanical (itemFk, genusFk, specieFk) 
			SELECT vItemA2Fk, genusFk, specieFk
				FROM itemBotanical 
				WHERE itemFk = vSelf;
	END IF;

	IF vQuantity = vCurrentVisible THEN
		DELETE FROM itemShelving
			WHERE id = vTargetItemShelvingFk;
	ELSE
		UPDATE itemShelving
			SET visible = vCurrentVisible - vQuantity,
				available = GREATEST(0, available - vQuantity)
			WHERE id = vTargetItemShelvingFk;
	END IF;

	INSERT INTO buy(
			entryFk,
			itemFk,
			quantity,
			buyingValue,
			freightValue,
			isIgnored,
			stickers,
			packagingFk,
			packing,
			`grouping`,
			groupingMode,
			comissionValue,
			packageValue,
			price1,
			price2,
			price3,
			minPrice,
			isChecked,
			location,
			weight,
			itemOriginalFk)
		SELECT vTargetEntryFk,
				itemFk,
				- LEAST(vQuantity, vCurrentVisible),
				buyingValue,
				freightValue,
				TRUE,
				stickers,
				packagingFk,
				packing,
				`grouping`,
				groupingMode,
				comissionValue,
				packageValue,
				price1,
				price2,
				price3,
				minPrice,
				isChecked,
				location,
				weight,
				itemOriginalFk
			FROM vn.buy 
			WHERE id = vLastBuyFk
		UNION 
		SELECT vTargetEntryFk,
				vItemA2Fk,
				vQuantity,
				vBuyingValue,
				freightValue,
				TRUE,
				stickers,
				packagingFk,
				packing,
				`grouping`,
				groupingMode,
				comissionValue,
				packageValue,
				price1,
				price2,
				price3,
				minPrice,
				isChecked,
				location,
				weight,
				itemOriginalFk
			FROM vn.buy 
			WHERE id = vLastBuyFk;

	INSERT IGNORE INTO itemShelving (
			itemFk, 
			shelvingFk, 
			visible, 
			`grouping`, 
			packing, 
			packagingFk, 
			userFk, 
			isChecked) 
		SELECT vItemA2Fk, 
				ish.shelvingFk, 
				vQuantity ,
				ish.`grouping`, 
				ish.packing, 
				ish.packagingFk, 
				account.myUser_getId(), 
				ish.isChecked
			FROM itemShelving ish
				JOIN shelving sh ON sh.id = ish.shelvingFk
			WHERE ish.itemFK = vSelf
				AND sh.code = vShelvingCode
			ON DUPLICATE KEY UPDATE 
				visible = vQuantity + VALUES(visible);

	COMMIT;
	CALL cache.visible_refresh(vCacheFk, TRUE, vWarehouseFk);
	CALL cache.available_refresh(vCacheFk, TRUE, vWarehouseFk, vCurdate);
	CALL buy_recalcPricesByBuy(vA2BuyFk);
END$$
DELIMITER ;