verdnatura/salix
verdnatura
/
salix
3
4
Fork 0
Code Issues Pull Requests 57 Releases Wiki Activity
salix/db/changes/234201/03-packagingFkProc.sql

1382 lines
40 KiB
SQL
Raw Permalink Blame History

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`travelVolume`(vTravelFk INT)
BEGIN
SELECT w1.name AS ORI,
w2.name AS DES,
tr.shipped shipment,
tr.landed landing,
a.name Agencia,
s.name Proveedor,
e.id Id_Entrada,
e.invoiceNumber Referencia,
CAST(ROUND(SUM(GREATEST(b.stickers ,b.quantity /b.packing ) *
vn.item_getVolume(b.itemFk ,b.packagingFk)) / vc.trolleyM3 / 1000000 ,1) AS DECIMAL(10,2)) AS CC,
CAST(ROUND(SUM(GREATEST(b.stickers ,b.quantity /b.packing ) *
vn.item_getVolume(b.itemFk ,b.packagingFk)) / vc.palletM3 / 1000000,1) AS DECIMAL(10,2)) AS espais
FROM vn.buy b
JOIN vn.entry e ON e.id = b.entryFk
JOIN vn.supplier s ON s.id = e.supplierFk
JOIN vn.travel tr ON tr.id = e.travelFk
JOIN vn.agencyMode a ON a.id = tr.agencyModeFk
JOIN vn.warehouse w1 ON w1.id = tr.warehouseInFk
JOIN vn.warehouse w2 ON w2.id = tr.warehouseOutFk
JOIN vn.volumeConfig vc
JOIN vn.item i ON i.id = b.itemFk
JOIN vn.itemType it ON it.id = i.typeFk
WHERE tr.id = vTravelFk;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`travelVolume_get`(vFromDated DATE, vToDated DATE, vWarehouseFk INT)
BEGIN
SELECT tr.landed Fecha,
a.name Agencia,
count(DISTINCT e.id) numEntradas,
FLOOR(sum(item_getVolume(b.itemFk, b.packagingFk) * b.stickers / 1000000 )) AS m3
FROM vn.travel tr
JOIN vn.agencyMode a ON a.id = tr.agencyModeFk
JOIN vn.entry e ON e.travelFk = tr.id
JOIN vn.buy b ON b.entryFk = e.id
WHERE tr.landed BETWEEN vFromDated AND vToDated
AND e.isRaid = FALSE
AND tr.warehouseInFk = vWarehouseFk
GROUP BY tr.landed , a.name ;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`travel_getEntriesMissingPackage`(vSelf INT)
BEGIN
DECLARE vpackageOrPackingNull INT;
DECLARE vTravelFk INT;
SELECT travelfk INTO vTravelFk
FROM entry
WHERE id = vSelf;
SELECT e.id entryFk
FROM travel t
JOIN entry e ON e.travelFk = t.id
JOIN buy b ON b.entryFk = e.id
WHERE t.id = vTravelFk
AND (b.packing IS NULL OR b.packagingFk IS NULL);
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`ticketBoxesView`(IN vTicketFk INT)
BEGIN
SELECT s.id,
s.itemFk,
s.concept,
floor(s.quantity / b.packing) as Cajas,
b.packing,
s.isPicked,
i.size
FROM ticket t
JOIN sale s ON s.ticketFk = t.id
JOIN item i ON i.id = s.itemFk
JOIN cache.last_buy lb on lb.warehouse_id = t.warehouseFk AND lb.item_id = s.itemFk
JOIN buy b on b.id = lb.buy_id
JOIN packaging p on p.id = b.packagingFk
WHERE s.quantity >= b.packing
AND t.id = vTicketFk
AND p.isBox
GROUP BY s.itemFk;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`stockBuyedByWorker`(
vDated DATE,
vWorker INT
)
BEGIN
/**
* Inserta el volumen de compra de un comprador
* en stockBuyed de acuerdo con la fecha.
*
* @param vDated Fecha de compra
* @param vWorker Id de trabajador
*/
CREATE OR REPLACE TEMPORARY TABLE tStockBuyed
(INDEX (userFk))
ENGINE = MEMORY
SELECT requested, reserved, userFk
FROM stockBuyed
WHERE dated = vDated
AND userFk = vWorker;
DELETE FROM stockBuyed
WHERE dated = vDated
AND userFk = vWorker;
CALL stockTraslation(vDated);
INSERT INTO stockBuyed(userFk, buyed, `dated`, reserved, requested, description)
SELECT it.workerFk,
SUM((ti.quantity / b.packing) * buy_getVolume(b.id)) / vc.palletM3 / 1000000,
vDated,
sb.reserved,
sb.requested,
u.name
FROM itemType it
JOIN item i ON i.typeFk = it.id
LEFT JOIN tmp.item ti ON ti.itemFk = i.id
JOIN itemCategory ic ON ic.id = it.categoryFk
JOIN warehouse wh ON wh.code = 'VNH'
JOIN tmp.buyUltimate bu ON bu.itemFk = i.id
AND bu.warehouseFk = wh.id
JOIN buy b ON b.id = bu.buyFk
JOIN volumeConfig vc
JOIN account.`user` u ON u.id = it.workerFk
LEFT JOIN tStockBuyed sb ON sb.userFk = it.workerFk
WHERE ic.display
AND it.workerFk = vWorker;
SELECT b.entryFk Id_Entrada,
i.id Id_Article,
i.name Article,
ti.quantity Cantidad,
(ac.conversionCoefficient * (ti.quantity / b.packing) * buy_getVolume(b.id))
/ (vc.trolleyM3 * 1000000) buyed,
b.packagingFk id_cubo,
b.packing
FROM tmp.item ti
JOIN item i ON i.id = ti.itemFk
JOIN itemType it ON i.typeFk = it.id
JOIN itemCategory ic ON ic.id = it.categoryFk
JOIN worker w ON w.id = it.workerFk
JOIN auctionConfig ac
JOIN tmp.buyUltimate bu ON bu.itemFk = i.id
AND bu.warehouseFk = ac.warehouseFk
JOIN buy b ON b.id = bu.buyFk
JOIN volumeConfig vc
WHERE ic.display
AND w.id = vWorker;
DROP TEMPORARY TABLE tmp.buyUltimate,
tmp.item,
tStockBuyed;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`itemShelvingMakeFromDate`(IN `vShelvingFk` VARCHAR(8), IN `vBarcode` VARCHAR(22), IN `vQuantity` INT, IN `vPackagingFk` VARCHAR(10), IN `vGrouping` INT, IN `vPacking` INT, IN `vWarehouseFk` INT, `vCreated` VARCHAR(22))
BEGIN
DECLARE vItemFk INT;
SELECT vn.barcodeToItem(vBarcode) INTO vItemFk;
SELECT itemFk INTO vItemFk
FROM vn.buy b
WHERE b.id = vItemFk;
IF (SELECT COUNT(*) FROM vn.shelving WHERE code = vShelvingFk COLLATE utf8_unicode_ci) = 0 THEN
INSERT IGNORE INTO vn.parking(`code`) VALUES(vShelvingFk);
INSERT INTO vn.shelving(`code`, parkingFk)
SELECT vShelvingFk, id
FROM vn.parking
WHERE `code` = vShelvingFk COLLATE utf8_unicode_ci;
END IF;
IF (SELECT COUNT(*) FROM vn.itemShelving
WHERE shelvingFk COLLATE utf8_unicode_ci = vShelvingFk
AND itemFk = vItemFk
AND packing = vPacking) = 1 THEN
UPDATE vn.itemShelving
SET visible = visible+vQuantity,
created = vCreated
WHERE shelvingFk COLLATE utf8_unicode_ci = vShelvingFk
AND itemFk = vItemFk
AND packing = vPacking;
ELSE
CALL cache.last_buy_refresh(FALSE);
INSERT INTO itemShelving( itemFk,
shelvingFk,
visible,
created,
`grouping`,
packing,
packagingFk)
SELECT vItemFk,
vShelvingFk,
vQuantity,
vCreated,
IF(vGrouping = 0, IFNULL(b.packing, vPacking), vGrouping) `grouping`,
IF(vPacking = 0, b.packing, vPacking) packing,
IF(vPackagingFk = '', b.packagingFk, vPackagingFk) packaging
FROM vn.item i
LEFT JOIN cache.last_buy lb ON i.id = lb.item_id AND lb.warehouse_id = vWarehouseFk
LEFT JOIN vn.buy b ON b.id = lb.buy_id
WHERE i.id = vItemFk;
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`itemShelving_add`(IN vShelvingFk VARCHAR(8), IN vBarcode VARCHAR(22), IN vQuantity INT, IN vPackagingFk VARCHAR(10), IN vGrouping INT, IN vPacking INT, IN vWarehouseFk INT)
BEGIN
/**
* Añade registro o lo actualiza si ya existe.
*
* @param vShelvingFk 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;
SELECT barcodeToItem(vBarcode) INTO vItemFk;
IF (SELECT COUNT(*) FROM shelving WHERE code = vShelvingFk COLLATE utf8_unicode_ci) = 0 THEN
INSERT IGNORE INTO parking(code) VALUES(vShelvingFk);
INSERT INTO shelving(code, parkingFk)
SELECT vShelvingFk, id
FROM parking
WHERE `code` = vShelvingFk COLLATE utf8_unicode_ci;
END IF;
IF (SELECT COUNT(*) FROM itemShelving
WHERE shelvingFk COLLATE utf8_unicode_ci = vShelvingFk
AND itemFk = vItemFk
AND packing = vPacking) = 1 THEN
UPDATE itemShelving
SET visible = visible+vQuantity
WHERE shelvingFk COLLATE utf8_unicode_ci = vShelvingFk AND itemFk = vItemFk AND packing = vPacking;
ELSE
CALL cache.last_buy_refresh(FALSE);
INSERT INTO itemShelving( itemFk,
shelvingFk,
visible,
grouping,
packing,
packagingFk)
SELECT vItemFk,
vShelvingFk,
vQuantity,
IFNULL(vGrouping, b.grouping),
IFNULL(vPacking, b.packing),
IFNULL(vPackagingFk, b.packagingFk)
FROM item i
LEFT JOIN cache.last_buy lb ON i.id = lb.item_id AND lb.warehouse_id = vWarehouseFk
LEFT JOIN buy b ON b.id = lb.buy_id
WHERE i.id = vItemFk;
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`itemFreight_Show`(vItemFk INT, vWarehouseFk INT)
BEGIN
SELECT cm3 Volumen_Entrada,
cm3delivery Volumen_Salida,
p.volume Volumen_del_embalaje,
p.width Ancho_del_embalaje,
p.`depth` Largo_del_embalaje,
b.packagingFk ,
IFNULL(p.height, i.`size`) + 10 Altura,
b.packing Packing_Entrada,
i.packingOut Packing_Salida,
i.id itemFk,
b.id buyFk,
b.entryFk,
w.name warehouseFk
FROM vn.itemCost ic
JOIN vn.item i ON i.id = ic.itemFk
LEFT JOIN cache.last_buy lb ON lb.item_id = ic.itemFk AND lb.warehouse_id = ic.warehouseFk
LEFT JOIN vn.buy b ON b.id = lb.buy_id
LEFT JOIN vn.packaging p ON p.id = b.packagingFk
LEFT JOIN vn.warehouse w ON w.id = ic.warehouseFk
WHERE ic.itemFk = vItemFk
AND ic.warehouseFk = vWarehouseFk;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`inventoryMake`(vDate DATE, vWh INT)
proc: BEGIN
/**
* Recalcula los inventarios de todos los almacenes, si vWh = 0
*
* @param vDate Fecha de los nuevos inventarios
* @param vWh almacen al cual hacer el inventario
*/
DECLARE vDone BOOL;
DECLARE vEntryFk INT;
DECLARE vTravelFk INT;
DECLARE vDateLastInventory DATE;
DECLARE vDateYesterday DATETIME DEFAULT vDate - INTERVAL 1 SECOND;
DECLARE vWarehouseOutFkInventory INT;
DECLARE vInventorySupplierFk INT;
DECLARE vAgencyModeFkInventory INT;
DECLARE cWarehouses CURSOR FOR
SELECT id
FROM warehouse
WHERE isInventory
AND vWh IN (0,id);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;
OPEN cWarehouses;
SET @isModeInventory := TRUE;
l: LOOP
SET vDone = FALSE;
FETCH cWarehouses INTO vWh;
IF vDone THEN
LEAVE l;
END IF;
SELECT w.id INTO vWarehouseOutFkInventory
FROM warehouse w
WHERE w.code = 'inv';
SELECT inventorySupplierFk INTO vInventorySupplierFk
FROM entryConfig;
SELECT am.id INTO vAgencyModeFkInventory
FROM agencyMode am
where code = 'inv';
SELECT MAX(landed) INTO vDateLastInventory
FROM travel tr
JOIN entry e ON e.travelFk = tr.id
JOIN buy b ON b.entryFk = e.id
WHERE warehouseOutFk = vWarehouseOutFkInventory
AND landed < vDate
AND e.supplierFk = vInventorySupplierFk
AND warehouseInFk = vWh
AND NOT isRaid;
IF vDateLastInventory IS NULL THEN
SELECT inventoried INTO vDateLastInventory FROM config;
END IF;
-- Generamos travel, si no existe.
SET vTravelFK = 0;
SELECT id INTO vTravelFk
FROM travel
WHERE warehouseOutFk = vWarehouseOutFkInventory
AND warehouseInFk = vWh
AND landed = vDate
AND agencyModeFk = vAgencyModeFkInventory
AND ref = 'inventario'
LIMIT 1;
IF NOT vTravelFK THEN
INSERT INTO travel SET
warehouseOutFk = vWarehouseOutFkInventory,
warehouseInFk = vWh,
shipped = vDate,
landed = vDate,
agencyModeFk = vAgencyModeFkInventory,
ref = 'inventario',
isDelivered = TRUE,
isReceived = TRUE;
SELECT LAST_INSERT_ID() INTO vTravelFk;
END IF;
-- Generamos entrada si no existe, o la vaciamos.
SET vEntryFk = 0;
SELECT id INTO vEntryFk
FROM entry
WHERE supplierFk = vInventorySupplierFk
AND travelFk = vTravelFk;
IF NOT vEntryFk THEN
INSERT INTO entry SET
supplierFk = vInventorySupplierFk,
isConfirmed = TRUE,
isOrdered = TRUE,
travelFk = vTravelFk;
SELECT LAST_INSERT_ID() INTO vEntryFk;
ELSE
DELETE FROM buy WHERE entryFk = vEntryFk;
END IF;
-- Preparamos tabla auxilar
CREATE OR REPLACE TEMPORARY TABLE tmp.inventory (
itemFk INT(11) NOT NULL PRIMARY KEY,
quantity int(11) DEFAULT '0',
buyingValue decimal(10,3) DEFAULT '0.000',
freightValue decimal(10,3) DEFAULT '0.000',
packing int(11) DEFAULT '0',
`grouping` smallint(5) unsigned NOT NULL DEFAULT '1',
groupingMode tinyint(4) NOT NULL DEFAULT 0 ,
comissionValue decimal(10,3) DEFAULT '0.000',
packageValue decimal(10,3) DEFAULT '0.000',
packageFk varchar(10) COLLATE utf8_unicode_ci DEFAULT '--',
price1 decimal(10,2) DEFAULT '0.00',
price2 decimal(10,2) DEFAULT '0.00',
price3 decimal(10,2) DEFAULT '0.00',
minPrice decimal(10,2) DEFAULT '0.00',
producer varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
INDEX (itemFK)) ENGINE = MEMORY;
-- Compras
INSERT INTO tmp.inventory(itemFk,quantity)
SELECT b.itemFk, SUM(b.quantity)
FROM buy b
JOIN entry e ON e.id = b.entryFk
JOIN travel tr ON tr.id = e.travelFk
WHERE tr.warehouseInFk = vWh
AND tr.landed BETWEEN vDateLastInventory
AND vDateYesterday
AND NOT isRaid
GROUP BY b.itemFk;
SELECT vDateLastInventory , vDateYesterday;
-- Traslados
INSERT INTO tmp.inventory(itemFk, quantity)
SELECT itemFk, quantityOut
FROM (
SELECT b.itemFk,- SUM(b.quantity) quantityOut
FROM buy b
JOIN entry e ON e.id = b.entryFk
JOIN travel tr ON tr.id = e.travelFk
WHERE tr.warehouseOutFk = vWh
AND tr.shipped BETWEEN vDateLastInventory
AND vDateYesterday
AND NOT isRaid
GROUP BY b.itemFk
) sub
ON DUPLICATE KEY UPDATE quantity = IFNULL(quantity, 0) + sub.quantityOut;
-- Ventas
INSERT INTO tmp.inventory(itemFk,quantity)
SELECT itemFk, saleOut
FROM (
SELECT s.itemFk, - SUM(s.quantity) saleOut
FROM sale s
JOIN ticket t ON t.id = s.ticketFk
WHERE t.warehouseFk = vWh
AND t.shipped BETWEEN vDateLastInventory AND vDateYesterday
GROUP BY s.itemFk
) sub
ON DUPLICATE KEY UPDATE quantity = IFNULL(quantity,0) + sub.saleOut;
-- Actualiza valores de la ultima compra
UPDATE tmp.inventory inv
JOIN cache.last_buy lb ON lb.item_id = inv.itemFk AND lb.warehouse_id = vWh
JOIN buy b ON b.id = lb.buy_id
JOIN item i ON i.id = b.itemFk
LEFT JOIN producer p ON p.id = i.producerFk
SET inv.buyingValue = b.buyingValue,
inv.freightValue = b.freightValue,
inv.packing = b.packing,
inv.`grouping`= b.`grouping`,
inv.groupingMode = b.groupingMode,
inv.comissionValue = b.comissionValue,
inv.packageValue = b.packageValue,
inv.packageFk = b.packagingFk,
inv.price1 = b.price1,
inv.price2 = b.price2,
inv.price3 = b.price3,
inv.minPrice = b.minPrice,
inv.producer = p.name;
INSERT INTO buy( itemFk,
quantity,
buyingValue,
freightValue,
packing,
`grouping`,
groupingMode,
comissionValue,
packageValue,
packagingFk,
price1,
price2,
price3,
minPrice,
entryFk)
SELECT itemFk,
GREATEST(quantity, 0),
buyingValue,
freightValue,
packing,
`grouping`,
groupingMode,
comissionValue,
packageValue,
packagingFk,
price1,
price2,
price3,
minPrice,
vEntryFk
FROM tmp.inventory;
SELECT vWh, COUNT(*), util.VN_NOW() FROM tmp.inventory;
-- Actualizamos el campo lastUsed de item
UPDATE item i
JOIN tmp.inventory i2 ON i2.itemFk = i.id
SET i.lastUsed = NOW()
WHERE i2.quantity;
-- DROP TEMPORARY TABLE tmp.inventory;
END LOOP;
CLOSE cWarehouses;
UPDATE config SET inventoried = vDate;
SET @isModeInventory := FALSE;
DROP TEMPORARY TABLE IF EXISTS tmp.entryToDelete;
CREATE TEMPORARY TABLE tmp.entryToDelete
(INDEX(entryId) USING BTREE) ENGINE = MEMORY
SELECT e.id as entryId,
t.id as travelId
FROM travel t
JOIN `entry` e ON e.travelFk = t.id
WHERE e.supplierFk = vInventorySupplierFk
AND t.shipped <= util.VN_CURDATE() - INTERVAL 12 DAY
AND (DAY(t.shipped) <> 1 OR shipped < util.VN_CURDATE() - INTERVAL 12 DAY);
DELETE e
FROM `entry` e
JOIN tmp.entryToDelete tmp ON tmp.entryId = e.id;
DELETE IGNORE t
FROM travel t
JOIN tmp.entryToDelete tmp ON tmp.travelId = t.id;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`inventory_repair`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS tmp.lastEntry;
CREATE TEMPORARY TABLE tmp.lastEntry
(PRIMARY KEY (buyFk))
SELECT
i.id AS itemFk,
w.id AS warehouseFk,
w.name AS warehouse,
tr.landed,
b.id AS buyFk,
b.entryFk,
b.isIgnored,
b.price2,
b.price3,
b.stickers,
b.packing,
b.grouping,
b.groupingMode,
b.weight,
i.stems,
b.quantity,
b.buyingValue,
b.packagingFk ,
s.id AS supplierFk,
s.name AS supplier
FROM itemType it
RIGHT JOIN (entry e
LEFT JOIN supplier s ON s.id = e.supplierFk
RIGHT JOIN buy b ON b.entryFk = e.id
LEFT JOIN item i ON i.id = b.itemFk
LEFT JOIN ink ON ink.id = i.inkFk
LEFT JOIN travel tr ON tr.id = e.travelFk
LEFT JOIN warehouse w ON w.id = tr.warehouseInFk
LEFT JOIN origin o ON o.id = i.originFk
) ON it.id = i.typeFk
LEFT JOIN edi.ekt ek ON b.ektFk = ek.id
WHERE (b.packagingFk = "--" OR b.price2 = 0 OR b.packing = 0 OR b.buyingValue = 0) AND tr.landed > util.firstDayOfMonth(TIMESTAMPADD(MONTH,-1,util.VN_CURDATE())) AND s.name = 'INVENTARIO';
DROP TEMPORARY TABLE IF EXISTS tmp.lastEntryOk;
CREATE TEMPORARY TABLE tmp.lastEntryOk
(PRIMARY KEY (buyFk))
SELECT
i.id AS itemFk,
w.id AS warehouseFk,
w.name AS warehouse,
tr.landed,
b.id AS buyFk,
b.entryFk,
b.isIgnored,
b.price2,
b.price3,
b.stickers,
b.packing,
b.grouping,
b.groupingMode,
b.weight,
i.stems,
b.quantity,
b.buyingValue,
b.packagingFk,
s.id AS supplierFk,
s.name AS supplier
FROM itemType it
RIGHT JOIN (entry e
LEFT JOIN supplier s ON s.id = e.supplierFk
RIGHT JOIN buy b ON b.entryFk = e.id
LEFT JOIN item i ON i.id = b.itemFk
LEFT JOIN ink ON ink.id = i.inkFk
LEFT JOIN travel tr ON tr.id = e.travelFk
LEFT JOIN warehouse w ON w.id = tr.warehouseInFk
LEFT JOIN origin o ON o.id = i.originFk
) ON it.id = i.typeFk
LEFT JOIN edi.ekt ek ON b.ektFk = ek.id
WHERE b.packagingFk != "--" AND b.price2 != 0 AND b.packing != 0 AND b.buyingValue > 0 AND tr.landed > util.firstDayOfMonth(TIMESTAMPADD(MONTH,-2,util.VN_CURDATE()))
ORDER BY tr.landed DESC;
DROP TEMPORARY TABLE IF EXISTS tmp.lastEntryOkGroup;
CREATE TEMPORARY TABLE tmp.lastEntryOkGroup
(INDEX (warehouseFk,itemFk))
SELECT *
FROM tmp.lastEntryOk tmp
GROUP BY tmp.itemFk,tmp.warehouseFk;
UPDATE buy b
JOIN tmp.lastEntry lt ON lt.buyFk = b.id
JOIN tmp.lastEntryOkGroup eo ON eo.itemFk = lt.itemFk AND eo.warehouseFk = lt.warehouseFk
SET b.packagingFk = eo.packagingFk WHERE b.packagingFk = "--";
UPDATE buy b
JOIN tmp.lastEntry lt ON lt.buyFk = b.id
JOIN tmp.lastEntryOkGroup eo ON eo.itemFk = lt.itemFk AND eo.warehouseFk = lt.warehouseFk
SET b.price2 = eo.price2 WHERE b.price2 = 0 ;
UPDATE buy b
JOIN tmp.lastEntry lt ON lt.buyFk = b.id
JOIN tmp.lastEntryOkGroup eo ON eo.itemFk = lt.itemFk AND eo.warehouseFk = lt.warehouseFk
SET b.packing = eo.packing WHERE b.packing = 0;
UPDATE buy b
JOIN tmp.lastEntry lt ON lt.buyFk = b.id
JOIN tmp.lastEntryOkGroup eo ON eo.itemFk = lt.itemFk AND eo.warehouseFk = lt.warehouseFk
SET b.buyingValue = eo.buyingValue WHERE b.buyingValue = 0;
DROP TEMPORARY TABLE tmp.lastEntry;
DROP TEMPORARY TABLE tmp.lastEntryOk;
DROP TEMPORARY TABLE tmp.lastEntryOkGroup;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`buy_afterUpsert`(vSelf INT)
BEGIN
/**
* Triggered actions when a buy is updated or inserted.
*
* @param vSelf The buy reference
*/
DECLARE vEntryFk INT;
DECLARE vItemFk INT;
DECLARE vPackingOut DECIMAL(10,2);
DECLARE vWarehouse INT;
DECLARE vStandardFlowerBox INT;
DECLARE vWarehouseOut INT;
DECLARE vIsMerchandise BOOL;
DECLARE vIsFeedStock BOOL;
DECLARE vWeight DECIMAL(10,2);
DECLARE vPacking INT;
SELECT b.entryFk,
b.itemFk,
i.packingOut,
ic.merchandise,
vc.standardFlowerBox,
b.weight,
b.packing
INTO
vEntryFk,
vItemFk,
vPackingOut,
vIsMerchandise,
vStandardFlowerBox,
vWeight,
vPacking
FROM buy b
LEFT JOIN item i ON i.id = b.itemFk
LEFT JOIN itemType it ON it.id = i.typeFk
LEFT JOIN itemCategory ic ON ic.id = it.categoryFk
LEFT JOIN packaging p ON p.id = b.packagingFk AND NOT p.isBox
JOIN volumeConfig vc ON TRUE
WHERE b.id = vSelf;
SELECT t.warehouseInFk, t.warehouseOutFk
INTO vWarehouse, vWarehouseOut
FROM entry e
JOIN travel t ON t.id = e.travelFk
WHERE e.id = vEntryFk;
IF vIsMerchandise THEN
REPLACE itemCost SET
itemFk = vItemFk,
warehouseFk = vWarehouse,
cm3 = buy_getUnitVolume(vSelf),
cm3Delivery = IFNULL((vStandardFlowerBox * 1000) / vPackingOut, buy_getUnitVolume(vSelf));
IF vWeight AND vPacking THEN
UPDATE itemCost SET
grams = vWeight * 1000 / vPacking
WHERE itemFk = vItemFk
AND warehouseFk = vWarehouse;
END IF;
END IF;
SELECT isFeedStock INTO vIsFeedStock
FROM warehouse WHERE id = vWarehouseOut;
IF vIsFeedStock THEN
INSERT IGNORE INTO producer(`name`)
SELECT es.company_name
FROM buy b
JOIN edi.ekt be ON be.id = b.ektFk
JOIN edi.supplier es ON es.supplier_id = be.pro
WHERE b.id = vSelf;
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `vn`.`buy_getUnitVolume`(vSelf INT)
RETURNS int(11)
DETERMINISTIC
BEGIN
/**
* Calculates the unit volume occupied by a buy.
*
* @param vSelf The buy id
* @return The unit volume in cubic centimeters
*/
DECLARE vItem INT;
DECLARE vPackaging VARCHAR(10);
DECLARE vPacking INT;
SELECT itemFk, packagingFk, packing
INTO vItem, vPackaging, vPacking
FROM buy
WHERE id = vSelf;
RETURN IFNULL(ROUND(item_getVolume(vItem, vPackaging) / vPacking), 0);
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`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 vHasNotPrice BOOL;
DECLARE vBuyingValue DECIMAL(10,3);
DECLARE vPackagingFk VARCHAR(10);
DECLARE vIsWarehouseFloramondo BOOL;
SELECT t.landed, t.warehouseInFk, (w.`name` = 'Floramondo')
INTO vLanded, vWarehouseFk, vIsWarehouseFloramondo
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
LIMIT 1;
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);
SELECT (b.buyingValue = b.price2), b.buyingValue, b.packagingFk
INTO vHasNotPrice, vBuyingValue, vPackagingFk
FROM vn.buy b
WHERE b.id = @buyId AND b.buyingValue <> 0.01;
DROP TEMPORARY TABLE tmp.rate;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `edi`.`ekt_load`(IN `vSelf` INT)
proc:BEGIN
DECLARE vRef INT;
DECLARE vBuy INT;
DECLARE vItem INT;
DECLARE vQty INT;
DECLARE vPackage INT;
DECLARE vPutOrderFk INT;
DECLARE vIsLot BOOLEAN;
DECLARE vForceToPacking INT DEFAULT 2;
DECLARE vEntryFk INT;
DECLARE vHasToChangePackagingFk BOOLEAN;
DECLARE vIsFloramondoDirect BOOLEAN;
DECLARE vTicketFk INT;
DECLARE vHasItemGroup BOOL;
DECLARE vDescription VARCHAR(255);
DECLARE vSaleFk INT;
-- Carga los datos necesarios del EKT
SELECT e.ref, qty, package, putOrderFk MOD 1000000, i2.id , NOT ISNULL(eea.addressFk), NOT ISNULL(igto.group_code),
CONCAT(e.`ref`, ' ', e.item, ' ', e.sub, ' EktFk:', e.id)
INTO vRef, vQty, vPackage, vPutOrderFk, vItem, vIsFloramondoDirect, vHasItemGroup, vDescription
FROM edi.ekt e
LEFT JOIN edi.item i ON e.ref = i.id
LEFT JOIN edi.putOrder po ON po.id = e.putOrderFk
LEFT JOIN vn.item i2 ON i2.supplyResponseFk = po.supplyResponseID
LEFT JOIN vn.ektEntryAssign eea ON eea.sub = e.sub
LEFT JOIN edi.item_groupToOffer igto ON igto.group_code = i.group_id
WHERE e.id = vSelf
LIMIT 1;
IF NOT vHasItemGroup THEN
CALL vn.mail_insert('logistica@verdnatura.es', 'nocontestar@verdnatura.es', 'Nuevo grupo en Floramondo', vDescription);
CALL vn.mail_insert('pako@verdnatura.es', 'nocontestar@verdnatura.es', CONCAT('Nuevo grupo en Floramondo: ', vDescription), vDescription);
LEAVE proc;
END IF;
-- Asigna la entrada
SELECT vn.ekt_getEntry(vSelf) INTO vEntryFk;
-- Inserta el cubo si no existe
IF vPackage = 800 THEN
SET vHasToChangePackagingFk = TRUE;
IF vItem THEN
SELECT vn.item_getPackage(vItem) INTO vPackage ;
ELSE
SET vPackage = 8000 + vQty;
INSERT IGNORE INTO vn.packaging(id, width, `depth`)
SELECT vPackage, vc.ccLength / vQty, vc.ccWidth
FROM vn.volumeConfig vc;
END IF;
ELSE
INSERT IGNORE INTO vn2008.Cubos (Id_Cubo, X, Y, Z)
SELECT bucket_id, ROUND(x_size/10), ROUND(y_size/10), ROUND(z_size/10)
FROM bucket WHERE bucket_id = vPackage;
IF ROW_COUNT() > 0
THEN
INSERT INTO vn2008.mail SET
`subject` = 'Cubo añadido',
`text` = CONCAT('Se ha añadido el cubo: ', vPackage),
`to` = 'ekt@verdnatura.es';
END IF;
END IF;
-- Si es una compra de Logiflora obtiene el articulo
IF vPutOrderFk THEN
SELECT i.id INTO vItem
FROM edi.putOrder po
JOIN vn.item i ON i.supplyResponseFk = po.supplyResponseID
WHERE po.id = vPutOrderFk
LIMIT 1;
END IF;
INSERT IGNORE INTO item_track SET
item_id = vRef;
IF IFNULL(vItem,0) = 0 THEN
-- Intenta obtener el artículo en base a los atributos holandeses
SELECT b.id, IFNULL(b.itemOriginalFk ,b.itemFk) INTO vBuy, vItem
FROM edi.ekt e
JOIN edi.item_track t ON t.item_id = e.ref
LEFT JOIN edi.ekt l ON l.ref = e.ref
LEFT JOIN vn.buy b ON b.ektFk = l.id
LEFT JOIN vn.item i ON i.id = b.itemFk
JOIN vn2008.config cfg
WHERE e.id = vSelf
AND l.id != vSelf
AND b.itemFk != cfg.generic_item
AND IF(t.s1, l.s1 = e.s1, TRUE)
AND IF(t.s2, l.s2 = e.s2, TRUE)
AND IF(t.s3, l.s3 = e.s3, TRUE)
AND IF(t.s4, l.s4 = e.s4, TRUE)
AND IF(t.s5, l.s5 = e.s5, TRUE)
AND IF(t.s6, l.s6 = e.s6, TRUE)
AND IF(t.pac, l.pac = e.pac, TRUE)
AND IF(t.cat, l.cat = e.cat, TRUE)
AND IF(t.ori, l.ori = e.ori, TRUE)
AND IF(t.pro, l.pro = e.pro, TRUE)
AND IF(t.package, l.package = e.package, TRUE)
AND IF(t.item, l.item = e.item, TRUE)
AND i.isFloramondo = vIsFloramondoDirect
ORDER BY l.now DESC, b.id ASC
LIMIT 1;
END IF;
-- Si no encuentra el articulo lo crea en el caso de las compras directas en Floramondo
IF ISNULL(vItem) AND vIsFloramondoDirect THEN
CALL edi.item_getNewByEkt(vSelf, vItem);
END IF;
INSERT INTO vn.buy
(
entryFk
,ektFk
,buyingValue
,itemFk
,stickers
,packing
,`grouping`
,quantity
,groupingMode
,packagingFk
,weight
)
SELECT
vEntryFk
,vSelf
,(@t := IF(i.stems, i.stems, 1)) * e.pri / IFNULL(i.stemMultiplier, 1) buyingValue
,IFNULL(vItem, cfg.generic_item) itemFk
,e.qty stickers
,@pac := IFNULL(i.stemMultiplier, 1) * e.pac / @t packing
,IFNULL(b.`grouping`, e.pac)
,@pac * e.qty
,vForceToPacking
,IF(vHasToChangePackagingFk OR ISNULL(b.packagingFk), vPackage, b.packagingFk)
,(IFNULL(i.weightByPiece,0) * @pac)/1000
FROM edi.ekt e
LEFT JOIN vn.buy b ON b.id = vBuy
LEFT JOIN vn.item i ON i.id = b.itemFk
LEFT JOIN vn.supplier s ON e.pro = s.id
JOIN vn2008.config cfg
WHERE e.id = vSelf
LIMIT 1;
DROP TEMPORARY TABLE IF EXISTS tmp.buyRecalc;
CREATE TEMPORARY TABLE tmp.buyRecalc
SELECT buy.id
FROM vn.buy
WHERE ektFk = vSelf;
CALL vn.buy_recalcPrices();
-- Si es una compra de Logiflora hay que informar la tabla vn.saleBuy
IF vPutOrderFk THEN
REPLACE vn.saleBuy(saleFk, buyFk, workerFk)
SELECT po.saleFk, b.id, account.myUser_getId()
FROM edi.putOrder po
JOIN vn.buy b ON b.ektFk = vSelf
WHERE po.id = vPutOrderFk;
END IF;
-- Si es una compra directa en Floramondo hay que añadirlo al ticket
IF vIsFloramondoDirect THEN
SELECT t.id INTO vTicketFk
FROM vn.ticket t
JOIN vn.ektEntryAssign eea
ON eea.addressFk = t.addressFk
AND t.warehouseFk = eea.warehouseInFk
JOIN edi.ekt e
ON e.sub = eea.sub
AND e.id = vSelf
WHERE e.fec = t.shipped
LIMIT 1;
IF ISNULL(vTicketFk) THEN
INSERT INTO vn.ticket (
clientFk,
shipped,
addressFk,
agencyModeFk,
nickname,
warehouseFk,
companyFk,
landed,
zoneFk,
zonePrice,
zoneBonus
)
SELECT
a.clientFk,
e.fec,
a.id,
a.agencyModeFk,
a.nickname,
eea.warehouseInFk,
c.id,
e.fec,
z.id,
z.price,
z.bonus
FROM edi.ekt e
JOIN vn.ektEntryAssign eea ON eea.sub = e.sub
JOIN vn.address a ON a.id = eea.addressFk
JOIN vn.company c ON c.code = 'VNL'
JOIN vn.`zone` z ON z.code = 'FLORAMONDO'
WHERE e.id = vSelf
LIMIT 1;
SET vTicketFk = LAST_INSERT_ID();
INSERT INTO vn.ticketLog
SET originFk = vTicketFk,
userFk = account.myUser_getId(),
`action` = 'insert',
description = CONCAT('EktLoad ha creado el ticket:', ' ', vTicketFk);
END IF;
INSERT INTO vn.sale (itemFk, ticketFk, concept, quantity, price)
SELECT vItem, vTicketFk, e.item, e.qty * e.pac, e.pri * ( 1 + fhc.floramondoMargin )
FROM edi.ekt e
JOIN edi.floraHollandConfig fhc
WHERE e.id = vSelf;
SELECT LAST_INSERT_ID() INTO vSaleFk;
REPLACE vn.saleBuy(saleFk, buyFk, workerFk)
SELECT vSaleFk, b.id, account.myUser_getId()
FROM vn.buy b
WHERE b.ektFk = vSelf;
INSERT INTO vn.saleComponent(saleFk, componentFk, value)
SELECT vSaleFk, c.id, e.pri
FROM edi.ekt e
JOIN vn.component c ON c.code = 'purchaseValue'
WHERE e.id = vSelf;
INSERT INTO vn.saleComponent(saleFk, componentFk, value)
SELECT vSaleFk, c.id, e.pri * fhc.floramondoMargin
FROM edi.ekt e
JOIN edi.floraHollandConfig fhc
JOIN vn.component c ON c.code = 'margin'
WHERE e.id = vSelf;
END IF;
DROP TEMPORARY TABLE tmp.buyRecalc;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `hedera`.`item_getVisible`(
vWarehouse TINYINT,
vDate DATE,
vType INT,
vPrefix VARCHAR(255))
BEGIN
/**
* Gets visible items of the specified type at specified date.
*
* @param vWarehouse The warehouse id
* @param vDate The visible date
* @param vType The type id
* @param vPrefix The article prefix to filter or %NULL for all
* @return tmp.itemVisible Visible items
*/
DECLARE vPrefixLen SMALLINT;
DECLARE vFilter VARCHAR(255) DEFAULT NULL;
DECLARE vDateInv DATE DEFAULT vn2008.date_inv();
DECLARE EXIT HANDLER FOR 1114
BEGIN
GET DIAGNOSTICS CONDITION 1
@message = MESSAGE_TEXT;
CALL vn.mail_insert(
'cau@verdnatura.es',
NULL,
CONCAT('hedera.item_getVisible error: ', @message),
CONCAT(
'warehouse: ', IFNULL(vWarehouse, ''),
', Fecha:', IFNULL(vDate, ''),
', tipo: ', IFNULL(vType,''),
', prefijo: ', IFNULL(vPrefix,'')));
RESIGNAL;
END;
SET vPrefixLen = IFNULL(LENGTH(vPrefix), 0) + 1;
IF vPrefixLen > 1 THEN
SET vFilter = CONCAT(vPrefix, '%');
END IF;
DROP TEMPORARY TABLE IF EXISTS `filter`;
CREATE TEMPORARY TABLE `filter`
(INDEX (itemFk))
ENGINE = MEMORY
SELECT id itemFk FROM vn.item
WHERE typeFk = vType
AND (vFilter IS NULL OR `name` LIKE vFilter);
DROP TEMPORARY TABLE IF EXISTS currentStock;
CREATE TEMPORARY TABLE currentStock
(INDEX (itemFk))
ENGINE = MEMORY
SELECT itemFk, SUM(quantity) quantity
FROM (
SELECT b.itemFk, b.quantity
FROM vn.buy b
JOIN vn.entry e ON e.id = b.entryFk
JOIN vn.travel t ON t.id = e.travelFk
WHERE t.landed BETWEEN vDateInv AND vDate
AND t.warehouseInFk = vWarehouse
AND NOT e.isRaid
UNION ALL
SELECT b.itemFk, -b.quantity
FROM vn.buy b
JOIN vn.entry e ON e.id = b.entryFk
JOIN vn.travel t ON t.id = e.travelFk
WHERE t.shipped BETWEEN vDateInv AND util.VN_CURDATE()
AND t.warehouseOutFk = vWarehouse
AND NOT e.isRaid
AND t.isDelivered
UNION ALL
SELECT m.itemFk, -m.quantity
FROM vn.sale m
JOIN vn.ticket t ON t.id = m.ticketFk
JOIN vn.ticketState s ON s.ticket = t.id
WHERE t.shipped BETWEEN vDateInv AND util.VN_CURDATE()
AND t.warehouseFk = vWarehouse
AND s.alertLevel = 3
) t
GROUP BY itemFk
HAVING quantity > 0;
DROP TEMPORARY TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp
(INDEX (itemFk))
ENGINE = MEMORY
SELECT *
FROM (
SELECT b.itemFk, b.packagingFk, b.packing
FROM vn.buy b
JOIN vn.entry e ON e.id = b.entryFk
JOIN vn.travel t ON t.id = e.travelFk
WHERE t.landed BETWEEN vDateInv AND vDate
AND NOT b.isIgnored
AND b.price2 >= 0
AND b.packagingFk IS NOT NULL
ORDER BY t.warehouseInFk = vWarehouse DESC, t.landed DESC
LIMIT 10000000000000000000
) t GROUP BY itemFk;
DROP TEMPORARY TABLE IF EXISTS tmp.itemVisible;
CREATE TEMPORARY TABLE tmp.itemVisible
ENGINE = MEMORY
SELECT i.id Id_Article,
SUBSTRING(i.`name`, vPrefixLen) Article,
t.packing, p.id Id_Cubo,
IF(p.depth > 0, p.depth, 0) depth, p.width, p.height,
CEIL(s.quantity / t.packing) etiquetas
FROM vn.item i
JOIN `filter` f ON f.itemFk = i.id
JOIN currentStock s ON s.itemFk = i.id
LEFT JOIN tmp t ON t.itemFk = i.id
LEFT JOIN vn.packaging p ON p.id = t.packagingFk
WHERE CEIL(s.quantity / t.packing) > 0
-- FIXME: Column Cubos.box not included in view vn.packaging
/* AND p.box */;
DROP TEMPORARY TABLE
`filter`,
currentStock,
tmp;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`buy_getVolume`()
BEGIN
/**
* Cálculo de volumen en líneas de compra
* @table tmp.buy(buyFk)
*/
SELECT t.name Temp,
CAST(ROUND(SUM(GREATEST(b.stickers ,b.quantity /b.packing ) *
item_getVolume(b.itemFk, b.packagingFk)) / vc.trolleyM3 / 1000000 ,1) AS DECIMAL(10,2)) carros ,
CAST(ROUND(SUM(GREATEST(b.stickers ,b.quantity /b.packing ) *
item_getVolume(b.itemFk, b.packagingFk)) / vc.palletM3 / 1000000,1) AS DECIMAL(10,2)) espais
FROM buy b
JOIN tmp.buy tb ON tb.buyFk = b.id
JOIN volumeConfig vc
JOIN item i ON i.id = b.itemFk
JOIN itemType it ON it.id = i.typeFk
LEFT JOIN temperature t ON t.code = it.temperatureFk
GROUP BY Temp;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`entry_checkPackaging`(vEntryFk INT)
BEGIN
/**
* Comprueba que los campos package y packaging no sean nulos
*
* @param vEntryFk Id de entrada
*/
DECLARE vpackageOrPackingNull INT;
SELECT count(*) INTO vpackageOrPackingNull
FROM buy b
WHERE b.entryFk = vEntryFk
AND (b.packing IS NULL OR b.packagingFk IS NULL);
IF vpackageOrPackingNull THEN
CALL util.throw("packageOrPackingNull");
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`fustControl`(vFromDated DATE, vToDated DATE)
BEGIN
DECLARE vSijsnerClientFk INT DEFAULT 19752;
DECLARE vDateStart DATETIME;
DECLARE vDateEnd DATETIME;
SET vDateStart = vFromDated;
SET vDateEnd = util.Dayend(vToDated);
SELECT p.id FustCode,
CAST(sent.stucks AS DECIMAL(10,0)) FH,
CAST(tp.stucks AS DECIMAL(10,0)) Tickets,
CAST(-sj.stucks AS DECIMAL(10,0)) Sijsner,
CAST(IFNULL(sent.stucks,0) - IFNULL(tp.stucks,0) + IFNULL(sj.stucks,0) AS DECIMAL(10,0)) saldo
FROM vn.packaging p
LEFT JOIN (
SELECT FustCode, sum(fustQuantity) stucks
FROM (
SELECT IFNULL(pe.equivalentFk ,b.packagingFk) FustCode, s.quantity / b.packing AS fustQuantity
FROM vn.sale s
JOIN vn.ticket t ON t.id = s.ticketFk
JOIN vn.warehouse w ON w.id = t.warehouseFk
JOIN vn.warehouseAlias wa ON wa.id = w.aliasFk
JOIN cache.last_buy lb ON lb.item_id = s.itemFk AND lb.warehouse_id = t.warehouseFk
JOIN vn.buy b ON b.id = lb.buy_id
JOIN vn.packaging p ON p.id = b.packagingFk
LEFT JOIN vn.packageEquivalent pe ON pe.packagingFk = p.id
JOIN vn.address a ON a.id = t.addressFk
JOIN vn.province p2 ON p2.id = a.provinceFk
JOIN vn.country c ON c.id = p2.countryFk
WHERE t.shipped BETWEEN vDateStart AND vDateEnd
AND wa.name = 'VNH'
AND p.isPackageReturnable
AND c.country = 'FRANCIA') sub
GROUP BY FustCode) sent ON sent.FustCode = p.id
LEFT JOIN (
SELECT FustCode, sum(quantity) stucks
FROM (
SELECT IFNULL(pe.equivalentFk ,tp.packagingFk) FustCode, tp.quantity
FROM vn.ticketPackaging tp
JOIN vn.ticket t ON t.id = tp.ticketFk
JOIN vn.warehouse w ON w.id = t.warehouseFk
JOIN vn.warehouseAlias wa ON wa.id = w.aliasFk
JOIN vn.packaging p ON p.id = tp.packagingFk
LEFT JOIN vn.packageEquivalent pe ON pe.packagingFk = p.id
JOIN vn.address a ON a.id = t.addressFk
JOIN vn.province p2 ON p2.id = a.provinceFk
JOIN vn.country c ON c.id = p2.countryFk
WHERE t.shipped BETWEEN vDateStart AND vDateEnd
AND wa.name = 'VNH'
AND p.isPackageReturnable
AND c.country = 'FRANCIA'
AND t.clientFk != vSijsnerClientFk
AND tp.quantity > 0) sub
GROUP BY FustCode) tp ON tp.FustCode = p.id
LEFT JOIN (
SELECT FustCode, sum(quantity) stucks
FROM (
SELECT IFNULL(pe.equivalentFk ,tp.packagingFk) FustCode, tp.quantity
FROM vn.ticketPackaging tp
JOIN vn.ticket t ON t.id = tp.ticketFk
JOIN vn.warehouse w ON w.id = t.warehouseFk
JOIN vn.warehouseAlias wa ON wa.id = w.aliasFk
JOIN vn.packaging p ON p.id = tp.packagingFk
LEFT JOIN vn.packageEquivalent pe ON pe.packagingFk = p.id
WHERE t.shipped BETWEEN TIMESTAMPADD(DAY, 1, vDateStart ) AND TIMESTAMPADD(DAY, 1, vDateEnd )
AND wa.name = 'VNH'
AND p.isPackageReturnable
AND t.clientFk = vSijsnerClientFk) sub
GROUP BY FustCode) sj ON sj.FustCode = p.id
WHERE sent.stucks
OR tp.stucks
OR sj.stucks;
END$$
DELIMITER ;
View Git Blame Copy Permalink