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

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`buy_recalcPrices`()
BEGIN
/**
 * Recalcula los precios para las compras insertadas en tmp.buyRecalc.
 *
 * @param tmp.buyRecalc (id)
 */
	DECLARE vLanded DATE;
	DECLARE vWarehouseFk INT;
	DECLARE vIsWarehouseFloramondo BOOL;
	DECLARE vDone BOOL;
	DECLARE vTravels CURSOR FOR
		SELECT t.landed, t.warehouseInFk, (w.code = 'flm')
			FROM tmp.buyRecalc br
				JOIN buy b ON b.id = br.id
				JOIN entry e ON e.id = b.entryFk
				JOIN travel t ON t.id = e.travelFk
				JOIN warehouse w ON w.id = t.warehouseInFk
			GROUP BY t.landed, t.warehouseInFk;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;

	OPEN vTravels;
	l: LOOP
		SET vDone = FALSE;
		FETCH vTravels INTO vLanded, vWarehouseFk, vIsWarehouseFloramondo;

		IF vDone THEN
			LEAVE l;
		END IF;

		CALL rate_getPrices(vLanded, vWarehouseFk);

		UPDATE buy b
				JOIN tmp.buyRecalc br ON br.id = b.id AND (@buyId := b.id)
				LEFT JOIN packaging p ON p.id = b.packagingFk
				JOIN item i ON i.id = b.itemFk
				JOIN entry e ON e.id = b.entryFk
				JOIN itemType it ON it.id = i.typeFk
				JOIN travel tr ON tr.id = e.travelFk
				JOIN agencyMode am ON am.id = tr.agencyModeFk
				JOIN tmp.rate r
				JOIN volumeConfig vc
			SET b.freightValue = @PF:= IFNULL(((am.m3 * @m3:= item_getVolume(b.itemFk, b.packagingFk) / 1000000)
					/ b.packing) * IF(am.hasWeightVolumetric, GREATEST(b.weight / @m3 / vc.aerealVolumetricDensity, 1), 1), 0),
				b.comissionValue = @CF:= ROUND(IFNULL(e.commission * b.buyingValue / 100, 0), 3),
				b.packageValue = @EF:= IF(vIsWarehouseFloramondo, 0, IFNULL(ROUND(IF(p.isPackageReturnable, p.returnCost / b.packing , p.`value` / b.packing), 3),0)),
				b.price3 = @t3:= IF(r.rate3 = 0, b.buyingValue,ROUND((b.buyingValue + @CF + @EF + @PF) / ((100 - r.rate3 - it.promo ) /100) ,2)),    -- He añadido que el coste sea igual a tarifa3 si t3 = 0
				b.price2 = @t2:= round(@t3 * (1 + ((r.rate2 - r.rate3)/100)),2),
				b.price2 = @t2:= IF(@t2 <= @t3,@t3 , @t2)
			WHERE tr.landed = vLanded
				AND tr.warehouseInFk = vWarehouseFk;

		DROP TEMPORARY TABLE tmp.rate;
	END LOOP;
	CLOSE vTravels;
END$$
DELIMITER ;