Created
November 19, 2021 10:03
-
-
Save gonzaleztroyano/b84afadb370aac164121e64ad5dc0c52 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
RARO | |
CREATE TABLE IF NOT EXISTS Pedir_Productos ( | |
id INT(2) PRIMARY KEY | |
); | |
DELIMITER ## | |
DROP FUNCTION IF EXISTS productos_vendidos_semana## | |
CREATE FUNCTION productos_vendidos_semana(producto INT(2)) | |
RETURNS INT | |
DETERMINISTIC | |
BEGIN | |
DECLARE cuenta_vendidos_semana INT; | |
SELECT SUM(unidades) | |
FROM pedidos | |
WHERE producto_no = 10 | |
AND fecha BETWEEN | |
CURDATE() AND | |
(CURDATE() - INTERVAL 7 DAY) into cuenta_vendidos_semana; | |
RETURN cuenta_vendidos_semana; | |
END ## | |
DROP FUNCTION IF EXISTS productos_en_stock## | |
CREATE FUNCTION productos_en_stock(producto INT(2)) | |
RETURNS INT | |
DETERMINISTIC | |
BEGIN | |
DECLARE stock_de_producto INT2; | |
SELECT stock | |
FROM productos | |
WHERE producto_no =producto INTO stock_de_producto; | |
RETURN stock_de_producto; | |
END ## | |
CREATE EVENT comprueba_ventas_stock | |
ON SCHEDULE EVERY 1 SECOND | |
STARTS CURRENT_TIMESTAMP | |
ENDS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE | |
DO | |
CALL main_check_stock; | |
CREATE PROCEDURE main_check_stock | |
BEGIN | |
DECLARE num_filas INT DEFAULT 0; | |
DECLARE cuenta_bucle INT DEFAULT 0; | |
DECLARE local_product_no INT(2) DEFAULT 99; | |
DECLARE local_cuenta_vendidos_semana INT DEFAULT 0; | |
DECLARE local_productos_en_stock INT DEFAULT 0; | |
DECLARE cursor_busca_pedidos CURSOR FOR | |
SELECT DISTINCT producto_no | |
FROM pedidos | |
WHERE fecha BETWEEN | |
CURDATE() AND | |
(CURDATE() - INTERVAL 7 DAY) into cuenta; | |
SELECT FOUND_ROWS( ) INTO num_filas; | |
OPEN cursor_busca_pedidos; | |
WHILE cuenta_bucle < num_filas DO | |
BEGIN | |
FETCH cursor_busca_pedidos INTO local_product_no; | |
SELECT productos_vendidos_semana(local_product_no) | |
INTO local_cuenta_vendidos_semana; | |
SELECT productos_en_stock(local_product_no) | |
INTO local_productos_en_stock; | |
IF productos_vendidos_semana > productos_en_stock THEN | |
INSERT local_product_no | |
INTO Pedir_Productos (local_product_no); | |
END | |
END ## | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment