54 lines
1.5 KiB
SQL
54 lines
1.5 KiB
SQL
DELIMITER $$
|
||
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `cache`.`stock_refresh`(v_refresh BOOL)
|
||
proc: BEGIN
|
||
/**
|
||
* Crea o actualiza la cache con el disponible hasta el día de
|
||
* ayer. Esta cache es usada como base para otros procedimientos
|
||
* como el cálculo del visible o del ATP.
|
||
*
|
||
* @param v_refresh %TRUE para forzar el recálculo de la cache
|
||
**/
|
||
DECLARE v_calc INT;
|
||
DECLARE v_date_inv DATE;
|
||
DECLARE vCURDATE DATE;
|
||
DECLARE v_last_refresh DATETIME;
|
||
|
||
DECLARE EXIT HANDLER FOR SQLEXCEPTION
|
||
BEGIN
|
||
CALL cache_calc_unlock (v_calc);
|
||
RESIGNAL;
|
||
END;
|
||
|
||
CALL cache_calc_start (v_calc, v_refresh, 'stock', NULL);
|
||
|
||
IF !v_refresh
|
||
THEN
|
||
LEAVE proc;
|
||
END IF;
|
||
|
||
SET v_date_inv = vn.getInventoryDate();
|
||
SET vCURDATE = util.VN_CURDATE();
|
||
|
||
DELETE FROM stock;
|
||
|
||
INSERT INTO stock (item_id, warehouse_id, amount)
|
||
SELECT item_id, warehouse_id, SUM(amount) amount FROM
|
||
(
|
||
SELECT itemFk AS item_id, warehouseFk AS warehouse_id, quantity AS amount
|
||
FROM vn.itemTicketOut
|
||
WHERE shipped >= v_date_inv AND shipped < vCURDATE
|
||
UNION ALL
|
||
SELECT itemFk ASitem_id, warehouseInFk AS warehouse_id, quantity AS amount
|
||
FROM vn.itemEntryIn
|
||
WHERE landed >= v_date_inv AND landed < vCURDATE AND isVirtualStock is FALSE
|
||
UNION ALL
|
||
SELECT itemFk AS item_id ,warehouseOutFk AS warehouse_id, quantity AS amount
|
||
FROM vn.itemEntryOut
|
||
WHERE shipped >= v_date_inv AND shipped < vCURDATE
|
||
) t
|
||
GROUP BY item_id, warehouse_id HAVING amount != 0;
|
||
|
||
CALL cache_calc_end (v_calc);
|
||
END$$
|
||
DELIMITER ;
|