1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
| -- TABLE
--------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `ps_abonnement` (
`id_abonnement` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id_order` INT NOT NULL ,
`id_product` INT NOT NULL ,
`id_customer` INT NOT NULL ,
`product_quantity` INT NOT NULL,
`date_add` DATETIME NOT NULL ,
`date_begin` DATE NOT NULL ,
`date_end` DATE NOT NULL ,
`deleted` BOOLEAN NOT NULL
)
-- VIEW
--------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW ps_ordersdetail AS
SELECT ps_orders.id_order, ps_orders.id_customer, ps_orders.current_state, ps_orders.valid, ps_orders.date_add,
ps_order_detail.product_id, ps_order_detail.product_quantity, ps_order_detail.product_reference
FROM ps_orders INNER JOIN ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order;
-- PROCEDURE STOCKEE
--------------------------------------------------------------------------------------------------------------------------------
DELIMITER |
CREATE PROCEDURE procedure_abonnement(proc_id_order INT(10), proc_current_state INT(10), old_state INT(10))
BEGIN
DECLARE fini INT(11) DEFAULT 0;
DECLARE product INT(11);
DECLARE customer INT(11);
DECLARE quantity INT(11);
DECLARE reference VARCHAR(11);
DECLARE date_ad DATETIME;
DECLARE cur_product CURSOR FOR SELECT product_id FROM ps_ordersdetail WHERE id_order=proc_id_order;
DECLARE cur_customer CURSOR FOR SELECT id_customer FROM ps_ordersdetail WHERE id_order=proc_id_order;
DECLARE cur_quantity CURSOR FOR SELECT product_quantity FROM ps_ordersdetail WHERE id_order=proc_id_order;
DECLARE cur_ref CURSOR FOR SELECT product_reference FROM ps_ordersdetail WHERE id_order=proc_id_order;
DECLARE cur_add CURSOR FOR SELECT date_add FROM ps_ordersdetail WHERE id_order=proc_id_order;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fini = 1;
IF proc_current_state = 1 THEN
OPEN cur_product;
OPEN cur_customer;
OPEN cur_quantity;
OPEN cur_ref;
OPEN cur_add;
WHILE fini <> 1 DO
FETCH cur_product INTO product;
FETCH cur_customer INTO customer;
FETCH cur_quantity INTO quantity;
FETCH cur_ref INTO reference;
FETCH cur_add INTO date_ad;
IF fini <> 1 THEN
IF proc_current_state <> old_state THEN
IF proc_current_state = 1 AND old_state = 0 THEN
IF reference LIKE "6%" THEN
INSERT INTO ps_abonnement (id_order, id_product, id_customer, product_quantity, date_add, date_begin, date_end)
VALUES (proc_id_order, product, customer, quantity, date_ad, NOW(), DATE_ADD(now(), INTERVAL 6 MONTH));
ELSEIF reference LIKE "12%" THEN
INSERT INTO ps_abonnement (id_order, id_product, id_customer, product_quantity, date_add, date_begin, date_end)
VALUES (proc_id_order, product, customer, quantity, date_ad, NOW(), DATE_ADD(now(), INTERVAL 12 MONTH));
END IF;
END IF;
END IF;
END IF;
END WHILE;
CLOSE cur_product;
CLOSE cur_customer;
CLOSE cur_quantity;
CLOSE cur_ref;
CLOSE cur_add;
SET fini=0;
ELSE
DELETE FROM ps_abonnement WHERE id_order = proc_id_order;
END IF;
END
|
-- TRIGGER
--------------------------------------------------------------------------------------------------------------------------------
CREATE TRIGGER trigger_abonnement AFTER UPDATE ON ps_orders
FOR EACH ROW
CALL procedure_abonnement(NEW.id_order, NEW.valid, OLD.valid); |
Partager