· 8 years ago · Apr 20, 2018, 02:22 AM
1
2-- --------------------------------------------------------------------------------
3-- Procédure ventilation donne la ventilation dans un tableau des sommes
4-- perçues par type de personne (physique ou société), les véhicules occasionnels.
5-- --------------------------------------------------------------------------------
6drop procedure if exists ventilation;
7DROP TEMPORARY TABLE if exists TMP_ventilation;
8
9DELIMITER $$
10CREATE PROCEDURE `ventilation`(p_DateDebut Date, p_DateFin Date)
11BEGIN
12 DECLARE v_NnOcca int;
13 DECLARE v_NbAbonSoc int;
14 DECLARE v_NbAbonPers int;
15 DECLARE v_SommeOcca DEC(9,2);
16 DECLARE v_SommeAbonSoc DEC(9,2);
17 DECLARE v_SommeAbonPers DEC(9,2);
18
19 SET v_NnOcca = nbOcca(p_DateDebut, p_DateFin);
20 SET v_NbAbonSoc = nbAbonSoc(p_DateDebut Date, p_DateFin Date);
21 SET v_NbAbonPers = nbAbonPers(p_DateDebut Date, p_DateFin Date);
22 SET v_SommeOcca = sommeOcca(p_DateDebut Date, p_DateFin Date);
23 SET v_SommeAbonSoc = sommeAbonSoc(p_DateDebut Date, p_DateFin Date);
24 SET v_SommeAbonPers = sommeAbonPers(p_DateDebut Date, p_DateFin Date);
25
26 CREATE TEMPORARY TABLE TMP_ventilation (
27 TypeDeClient varchar(25),
28 NbAbonnements INT,
29 SommeAbonnements DECIMAL(9,2),
30 NbOccasionnels INT,
31 SommeOccasionnels DECIMAL(9,2));
32
33 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
34 VALUE ('Personne', v_NbAbonPers, v_SommeAbonPers, v_NnOcca, v_SommeOcca);
35 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
36 VALUE ('Société', v_NbAbonSoc, v_SommeAbonSoc, null, null);
37 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
38 VALUE ('Total', (v_NbAbonPers+v_NbAbonSoc), (v_SommeAbonPers+v_SommeAbonSoc), v_NnOcca, v_SommeOcca);
39
40 SELECT TypeDeClient as '', NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels From TMP_depot;
41
42 DROP TEMPORARY TABLE TMP_ventilation;
43
44END$$
45DELIMITER ;
46
47##call ventilation( 2 dates );
48
49
50
51
52
53-- ------------------------------------------------------------
54-- Fonction pour connaitre NOMBRE ABONNEMENT PERSONNE
55-- ------------------------------------------------------------
56DELIMITER $$
57CREATE FUNCTION nbAbonPers(p_DateDebut Date, p_DateFin Date)
58 RETURNS int
59BEGIN
60 DECLARE v_nb int;
61SELECT count(*) FROM tblTransaction as trans
62 INNER JOIN tblAbonnement as abonnem
63 ON trans.idTransaction = abonnem.idTransaction
64 INNER JOIN tblAbonne as abon
65 ON abonnem.idAbonne = abon.idAbonne
66 WHERE abon.prenom is null AND trans.idOcca is null AND **********DATESSONTCORRECTS**********
67 INTO v_nb;
68
69 IF (v_nb is null) THEN
70 SET v_nb = 0;
71 END IF ;
72
73 RETURN v_nb ;
74END$$
75DELIMITER ;
76
77 -- ------------------------------------------------------------
78-- Fonction pour connaitre NOMBRE ABONNEMENT SOCIETE
79-- ------------------------------------------------------------
80DELIMITER $$
81CREATE FUNCTION nbAbonSoc(p_DateDebut Date, p_DateFin Date)
82 RETURNS int
83BEGIN
84 DECLARE v_nb int;
85SELECT count(*) FROM tblTransaction as trans
86 INNER JOIN tblAbonnement as abonnem
87 ON trans.idTransaction = abonnem.idTransaction
88 INNER JOIN tblAbonne as abon
89 ON abonnem.idAbonne = abon.idAbonne
90 WHERE abon.prenom is not null AND trans.idOcca is null AND **********DATESSONTCORRECTS**********
91 INTO v_nb;
92
93 IF (v_nb is null) THEN
94 SET v_nb = 0;
95 END IF ;
96
97 RETURN v_nb ;
98END$$
99DELIMITER ;
100
101 -- ------------------------------------------------------------
102-- Fonction pour connaitre SOMME ABONNEMENT PERSONNE
103-- ------------------------------------------------------------
104DELIMITER $$
105CREATE FUNCTION sommeAbonPers(p_DateDebut Date, p_DateFin Date)
106 RETURNS int
107BEGIN
108 DECLARE v_somme int;
109SELECT sum(trans.montant) FROM tblTransaction as trans
110 INNER JOIN tblAbonnement as abonnem
111 ON trans.idTransaction = abonnem.idTransaction
112 INNER JOIN tblAbonne as abon
113 ON abonnem.idAbonne = abon.idAbonne
114 WHERE abon.prenom is null AND trans.idOcca is null AND **********DATESSONTCORRECTS**********
115 INTO v_somme;
116
117 IF (v_somme is null) THEN
118 SET v_somme = 0;
119 END IF ;
120
121 RETURN v_somme ;
122END$$
123DELIMITER ;
124
125 -- ------------------------------------------------------------
126-- Fonction pour connaitre SOMME ABONNEMENT SOCIETE
127-- ------------------------------------------------------------
128DELIMITER $$
129CREATE FUNCTION sommeAbonSoc(p_DateDebut Date, p_DateFin Date)
130 RETURNS int
131BEGIN
132 DECLARE v_somme int;
133SELECT sum(trans.montant) FROM tblTransaction as trans
134 INNER JOIN tblAbonnement as abonnem
135 ON trans.idTransaction = abonnem.idTransaction
136 INNER JOIN tblAbonne as abon
137 ON abonnem.idAbonne = abon.idAbonne
138 WHERE abon.prenom is not null AND trans.idOcca is null AND **********DATESSONTCORRECTS**********
139 INTO v_somme;
140
141 IF (v_somme is null) THEN
142 SET v_somme = 0;
143 END IF ;
144
145 RETURN v_somme ;
146END$$
147DELIMITER ;
148
149-- --------------------------------------------
150-- Fonction pour connaitre NOMBRE OCCASIONNEL
151-- --------------------------------------------
152DELIMITER $$
153CREATE FUNCTION nbOcca(p_DateDebut Date, p_DateFin Date)
154 RETURNS int
155BEGIN
156 DECLARE v_nb int;
157SELECT count(*) FROM tblTransaction as trans
158 INNER JOIN tblAbonnement as abonnem
159 ON trans.idTransaction = abonnem.idTransaction
160 INNER JOIN tblOccasionnel as occa
161 ON trans.idOccas = occa.idOccas
162 WHERE trans.idAbonnement is null AND occa.dateFinOccas **********DATESSONTCORRECTS**********
163 INTO v_nb;
164
165 IF (v_nb is null) THEN
166 SET v_nb = 0;
167 END IF ;
168
169 RETURN v_nb ;
170END$$
171DELIMITER ;
172
173-- --------------------------------------------
174-- Fonction pour connaitre SOMME OCCASIONNEL
175-- --------------------------------------------
176DELIMITER $$
177CREATE FUNCTION sommeOcca(p_DateDebut Date, p_DateFin Date)
178 RETURNS int
179BEGIN
180 DECLARE v_nb int;
181SELECT sum(trans.montant) FROM tblTransaction as trans
182 INNER JOIN tblAbonnement as abonnem
183 ON trans.idTransaction = abonnem.idTransaction
184 INNER JOIN tblOccasionnel as occa
185 ON trans.idOccas = occa.idOccas
186 WHERE trans.idAbonnement is null AND occa.dateFinOccas **********DATESSONTCORRECTS**********
187 INTO v_nb;
188
189 IF (v_nb is null) THEN
190 SET v_nb = 0;
191 END IF ;
192
193 RETURN v_nb ;
194END$$
195DELIMITER ;