75 lines
2.0 KiB
MySQL
75 lines
2.0 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `cache`.`departure_timing`(vWarehouseId INT)
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
DECLARE done BOOL DEFAULT FALSE;
|
||
|
|
DECLARE rsDeparture INT;
|
||
|
|
DECLARE rsHoras DECIMAL(5,2);
|
||
|
|
DECLARE rsInicio DECIMAL(5,2);
|
||
|
|
DECLARE resto DECIMAL(5,2);
|
||
|
|
DECLARE horasProd DECIMAL(5,2);
|
||
|
|
DECLARE inicioProd DECIMAL(5,2) DEFAULT 24.00;
|
||
|
|
DECLARE myTime DECIMAL(5,2);
|
||
|
|
DECLARE departureLimit INT;
|
||
|
|
DECLARE myMinSpeed DECIMAL(10,2);
|
||
|
|
DECLARE vLoadingDelay DECIMAL(5) DEFAULT 2;
|
||
|
|
|
||
|
|
DECLARE rs CURSOR FOR
|
||
|
|
SELECT Departure
|
||
|
|
, Sum(pb.m3)/(GREATEST(IFNULL(lhp.m3,0),IFNULL(dp.minSpeed,0))) AS Horas
|
||
|
|
, util.VN_CURDATE()+(Departure + vLoadingDelay -(Sum(pb.m3)/(GREATEST(IFNULL(lhp.m3,0),IFNULL(minSpeed,0))))/24) AS InicioPreparacion
|
||
|
|
FROM tmp.production_buffer pb
|
||
|
|
LEFT JOIN vn.lastHourProduction lhp ON lhp.warehouseFk = pb.warehouse_id
|
||
|
|
LEFT JOIN cache.departure_limit dp ON dp.warehouse_id = pb.warehouse_id AND dp.fecha = util.VN_CURDATE()
|
||
|
|
WHERE pb.Fecha = util.VN_CURDATE()
|
||
|
|
AND pb.graphCategory > 1
|
||
|
|
AND IFNULL(Departure,0) > 0
|
||
|
|
GROUP BY Departure
|
||
|
|
ORDER BY Departure DESC;
|
||
|
|
|
||
|
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
||
|
|
|
||
|
|
SET myTime = HOUR(util.VN_NOW()) + MINUTE(util.VN_NOW()) / 60;
|
||
|
|
|
||
|
|
OPEN rs;
|
||
|
|
|
||
|
|
FETCH rs INTO rsDeparture, rsHoras , rsInicio;
|
||
|
|
|
||
|
|
WHILE NOT done DO
|
||
|
|
|
||
|
|
SET resto = IF(inicioProd < rsDeparture, rsDeparture - inicioProd,0);
|
||
|
|
|
||
|
|
SET inicioProd = rsDeparture - rsHoras;
|
||
|
|
|
||
|
|
IF inicioProd - resto < myTime THEN
|
||
|
|
|
||
|
|
SET done = TRUE;
|
||
|
|
|
||
|
|
ELSE
|
||
|
|
|
||
|
|
SET departureLimit = rsDeparture;
|
||
|
|
|
||
|
|
FETCH rs INTO rsDeparture, rsHoras , rsInicio;
|
||
|
|
|
||
|
|
-- SELECT rsDeparture, rsHoras , rsInicio;
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
END WHILE;
|
||
|
|
|
||
|
|
SET departureLimit = IFNULL(departureLimit,24);
|
||
|
|
SET departureLimit = IF(departureLimit = 0, 24, departureLimit);
|
||
|
|
|
||
|
|
SELECT minSpeed INTO myMinSpeed
|
||
|
|
FROM cache.departure_limit
|
||
|
|
WHERE warehouse_id = vWarehouseId
|
||
|
|
AND fecha = util.VN_CURDATE();
|
||
|
|
|
||
|
|
REPLACE cache.departure_limit(warehouse_id, fecha, hora, minSpeed)
|
||
|
|
VALUES (vWarehouseId, util.VN_CURDATE(), departureLimit, IFNULL(myMinSpeed,0));
|
||
|
|
|
||
|
|
CLOSE rs;
|
||
|
|
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|