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); |
Erreur d'exécution
Répondre avec citation
Partager