96 lines
3.1 KiB
SQL
96 lines
3.1 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`catalog_componentReverse`(vWarehouse INT,
|
|
vCost DECIMAL(10,3),
|
|
vM3 DECIMAL(10,3),
|
|
vAddressFk INT,
|
|
vZoneFk INT)
|
|
RETURNS decimal(10,3)
|
|
DETERMINISTIC
|
|
BEGIN
|
|
/* Calcula los componentes para un cliente a partir de un coste y volumen
|
|
* @param vWarehouse
|
|
* @param vCost Valor de coste del articulo
|
|
* @param vM3 m3 del articulo
|
|
* @param vAddressFk
|
|
* @param vZoneFk
|
|
* @return vRetailedPrice precio de venta sin iva
|
|
* @return tmp.catalog_component (warehouseFk, itemFk, componentFk, cost)
|
|
*/
|
|
|
|
DECLARE vBoxVolume BIGINT;
|
|
DECLARE vCustomer INT;
|
|
DECLARE vComponentRecovery INT DEFAULT 17;
|
|
DECLARE vComponentMana INT DEFAULT 39;
|
|
DECLARE vDeliveryComponent INT DEFAULT 15;
|
|
DECLARE vComponentMargin INT DEFAULT 29;
|
|
DECLARE vComponentCost INT DEFAULT 28;
|
|
DECLARE vRetailedPrice DECIMAL(10,2);
|
|
DECLARE vItem INT DEFAULT 98;
|
|
|
|
SELECT volume INTO vBoxVolume
|
|
FROM vn.packaging
|
|
WHERE id = '94';
|
|
|
|
SELECT clientFk INTO vCustomer FROM address WHERE id = vAddressFk;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tmp.catalog_component;
|
|
CREATE TEMPORARY TABLE tmp.catalog_component (
|
|
`warehouseFk` INT UNSIGNED NOT NULL,
|
|
`itemFk` INT NOT NULL,
|
|
`componentFk` INT UNSIGNED NOT NULL,
|
|
`cost` DECIMAL(10,4) NOT NULL,
|
|
INDEX `itemWarehouse` USING BTREE (`itemFk` ASC, `warehouseFk` ASC),
|
|
UNIQUE INDEX `itemWarehouseComponent` (`itemFk` ASC, `warehouseFk` ASC, `componentFk` ASC));
|
|
|
|
-- Cost
|
|
INSERT INTO tmp.catalog_component (warehouseFk, itemFk, componentFk, cost)
|
|
SELECT vWarehouse, vItem, vComponentCost, vCost
|
|
FROM vn.rate
|
|
WHERE dated <= util.VN_CURDATE()
|
|
AND warehouseFk = vWarehouse
|
|
ORDER BY dated DESC
|
|
LIMIT 1;
|
|
|
|
-- Margen
|
|
-- No se aplica margen, cau 12589
|
|
/* INSERT INTO tmp.catalog_component (warehouseFk, itemFk, componentFk, cost)
|
|
SELECT vWarehouse, vItem, vComponentMargin, (vCost / ((100 - rate2) / 100)) - vCost
|
|
FROM vn.rate
|
|
WHERE dated <= util.VN_CURDATE()
|
|
AND warehouseFk = vWarehouse
|
|
ORDER BY dated DESC
|
|
LIMIT 1;
|
|
*/
|
|
-- Recobro
|
|
INSERT INTO tmp.catalog_component (warehouseFk, itemFk, componentFk, cost)
|
|
SELECT vWarehouse, vItem, vComponentRecovery, ROUND(LEAST(recobro,0.25), 3)
|
|
FROM bi.claims_ratio
|
|
WHERE Id_Cliente = vCustomer AND recobro > 0.009;
|
|
|
|
-- Componente de maná automático, en función del maná acumulado por el comercial.
|
|
INSERT INTO tmp.catalog_component (warehouseFk, itemFk, componentFk, cost)
|
|
SELECT vWarehouse, vItem, vComponentMana, ROUND(wm.pricesModifierRate, 3)
|
|
FROM client c
|
|
JOIN vn.workerMana wm ON c.salesPersonFk = wm.workerFk
|
|
WHERE wm.isPricesModifierActivated AND c.id = vCustomer LIMIT 1;
|
|
|
|
-- Reparto
|
|
INSERT INTO tmp.catalog_component (warehouseFk, itemFk, componentFk, cost)
|
|
SELECT vWarehouse, vItem, vDeliveryComponent,
|
|
ROUND(
|
|
vM3
|
|
* (z.price - z.bonus)
|
|
* z.inflation
|
|
/ vBoxVolume, 4
|
|
)
|
|
FROM zone z
|
|
WHERE z.id = vZoneFk;
|
|
|
|
-- Precio de venta
|
|
SELECT SUM(cost) INTO vRetailedPrice
|
|
FROM tmp.catalog_component;
|
|
|
|
RETURN vRetailedPrice;
|
|
END$$
|
|
DELIMITER ;
|