· 8 years ago · Apr 19, 2018, 11:54 AM
1#####################################
2### CRÉATION DE LA BASE DE DONNÉE ###
3#####################################
4
5-- MySQL Workbench Forward Engineering
6
7SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
8SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
9SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
10
11-- -----------------------------------------------------
12-- Schema TP3
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `TP3` DEFAULT CHARACTER SET utf8 ;
15USE `TP3` ;
16
17-- -----------------------------------------------------
18-- Table `TP3`.`tblVille`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `TP3`.`tblVille` (
21 `idVille` INT NOT NULL AUTO_INCREMENT,
22 `nomVille` VARCHAR(50) NOT NULL,
23 PRIMARY KEY (`idVille`))
24ENGINE = InnoDB;
25
26-- -----------------------------------------------------
27-- Table `TP3`.`tblAbonne`
28-- -----------------------------------------------------
29CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonne` (
30 `idAbonne` INT NOT NULL AUTO_INCREMENT,
31 `nomAbonne` VARCHAR(50) NOT NULL,
32 `prenomAbonne` VARCHAR(25) NULL DEFAULT NULL,
33 `codePostal` VARCHAR(7) NOT NULL,
34 `telephone` VARCHAR(15) NOT NULL,
35 `courriel` VARCHAR(50) NOT NULL,
36 `typeAbonne` CHAR(1) NOT NULL,
37 `idVille` INT NOT NULL,
38 PRIMARY KEY (`idAbonne`),
39 INDEX `FK_tblAbonne_idVille` (`idVille` ASC),
40 CONSTRAINT `FK_tblAbonne_idVille`
41 FOREIGN KEY (`idVille`)
42 REFERENCES `TP3`.`tblVille` (`idVille`))
43ENGINE = InnoDB;
44
45-- -----------------------------------------------------
46-- Table `TP3`.`tblVehicule`
47-- -----------------------------------------------------
48CREATE TABLE IF NOT EXISTS `TP3`.`tblVehicule` (
49 `idVehicule` VARCHAR(7) NOT NULL,
50 `marque` VARCHAR(25) NOT NULL,
51 `modele` VARCHAR(25) NOT NULL,
52 `couleur` VARCHAR(15) NOT NULL,
53 PRIMARY KEY (`idVehicule`))
54ENGINE = InnoDB;
55
56-- -----------------------------------------------------
57-- Table `TP3`.`tblMethodPaiement`
58-- -----------------------------------------------------
59CREATE TABLE IF NOT EXISTS `TP3`.`tblMethodPaiement` (
60 `idPaiement` INT NOT NULL AUTO_INCREMENT,
61 `typePaiement` CHAR(1) NOT NULL,
62 `descPaiement` VARCHAR(25) NOT NULL,
63 PRIMARY KEY (`idPaiement`))
64ENGINE = InnoDB;
65
66-- -----------------------------------------------------
67-- Table `TP3`.`tblOccasionnel`
68-- -----------------------------------------------------
69CREATE TABLE IF NOT EXISTS `TP3`.`tblOccasionnel` (
70 `idOccas` VARCHAR(12) NOT NULL,
71 `dateDebutOccas` DATETIME NOT NULL,
72 `dateFinOccas` DATETIME NULL,
73 `idTransaction` INT NULL,
74 PRIMARY KEY (`idOccas`),
75 INDEX `FK_tblOccasionnel_idTransaction` (`idTransaction` ASC),
76 CONSTRAINT `FK_tblOccasionnel_idTransaction`
77 FOREIGN KEY (`idTransaction`)
78 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
79ENGINE = InnoDB;
80
81-- -----------------------------------------------------
82-- Table `TP3`.`tblTransaction`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `TP3`.`tblTransaction` (
85 `idTransaction` INT NOT NULL AUTO_INCREMENT,
86 `montant` DECIMAL(6,2) NULL,
87 `numPaiement` VARCHAR(16) NULL,
88 `idPaiement` INT NULL,
89 `idAbonnement` INT NULL,
90 `idOccas` VARCHAR(12) NULL,
91 PRIMARY KEY (`idTransaction`),
92 INDEX `FK_tblTransaction_idPaiement` (`idPaiement` ASC),
93 INDEX `FK_tblTransaction_idAbonnement` (`idAbonnement` ASC),
94 INDEX `FK_tblTransaction_idOccas` (`idOccas` ASC),
95 CONSTRAINT `FK_tblTransaction_idPaiement`
96 FOREIGN KEY (`idPaiement`)
97 REFERENCES `TP3`.`tblMethodPaiement` (`idPaiement`),
98 CONSTRAINT `FK_tblTransaction_idAbonne`
99 FOREIGN KEY (`idAbonnement`)
100 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`),
101 CONSTRAINT `FK_tblTransaction_idOccas`
102 FOREIGN KEY (`idOccas`)
103 REFERENCES `TP3`.`tblOccasionnel` (`idOccas`))
104ENGINE = InnoDB;
105
106-- -----------------------------------------------------
107-- Table `TP3`.`tblAbonnement`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonnement` (
110 `idAbonnement` INT NOT NULL AUTO_INCREMENT,
111 `dateDebutAbonne` DATE NOT NULL,
112 `dateFinAbonne` DATE NOT NULL,
113 `idAbonne` INT NOT NULL,
114 `idVehicule` VARCHAR(7) NOT NULL,
115 `idPlace` SMALLINT NOT NULL,
116 `idTransaction` INT NOT NULL,
117 PRIMARY KEY (`idAbonnement`),
118 INDEX `FK_tblAbonnement_idAbonne_tblAbonne` (`idAbonne` ASC),
119 INDEX `FK_tblAbonnement_idVehicule` (`idVehicule` ASC),
120 INDEX `FK_tblAbonnement_idPlace` (`idPlace` ASC),
121 INDEX `FK_tblAbonnement_idTransaction` (`idTransaction` ASC),
122 CONSTRAINT `FK_tblAbonnement_idAbonne_tblAbonne`
123 FOREIGN KEY (`idAbonne`)
124 REFERENCES `TP3`.`tblAbonne` (`idAbonne`),
125 CONSTRAINT `FK_tblAbonnement_idVehicule`
126 FOREIGN KEY (`idVehicule`)
127 REFERENCES `TP3`.`tblVehicule` (`idVehicule`),
128 CONSTRAINT `FK_tblAbonnement_idPlace`
129 FOREIGN KEY (`idPlace`)
130 REFERENCES `TP3`.`tblPlace` (`idPlace`),
131 CONSTRAINT `FK_tblAbonnement_idTransaction`
132 FOREIGN KEY (`idTransaction`)
133 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
134ENGINE = InnoDB;
135
136-- -----------------------------------------------------
137-- Table `TP3`.`tblPlace`
138-- -----------------------------------------------------
139CREATE TABLE IF NOT EXISTS `TP3`.`tblPlace` (
140 `idPlace` SMALLINT NOT NULL AUTO_INCREMENT,
141 `typePlace` VARCHAR(25) NOT NULL,
142 `idAbonne` INT NULL,
143 PRIMARY KEY (`idPlace`),
144 INDEX `FK_tblPlace_idAbonne` (`idAbonne` ASC),
145 CONSTRAINT `FK_tblPlace_idAbonne`
146 FOREIGN KEY (`idAbonne`)
147 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
148ENGINE = InnoDB;
149
150SET SQL_MODE=@OLD_SQL_MODE;
151SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
152SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
153
154###############################
155### FONCTIONS ET PROCÉDURES ###
156###############################
157
158-- -----------------------------------------------------
159-- Fonctions
160-- -------------------------------------------------------------------------------------------
161-- Fonction FnNbPlaceAbonne() qui retourne le nombre de place utilisé par un abonnement valide
162-- -------------------------------------------------------------------------------------------
163DELIMITER $$
164CREATE FUNCTION FnNbPlaceAbonne()
165RETURNS int
166BEGIN
167 DECLARE v_nb int;
168 SELECT count(*) as v_nb
169 FROM tblAbonnement as abon
170 WHERE abon.dateFinAbonne > now()
171 into v_nb;
172RETURN v_nb;
173END $$
174DELIMITER ;
175
176-- -----------------------------------------------------------------------------------------
177-- Fonction FnNbPlaceOccas() qui retourne le nombre de place utilisé par un occasionel actif
178-- -----------------------------------------------------------------------------------------
179DELIMITER $$
180CREATE FUNCTION FnNbPlaceOccas()
181RETURNS int
182BEGIN
183 DECLARE v_nb int;
184 SELECT count(*) as v_nv
185 FROM tblOccasionnel as ocas
186 WHERE ocas.dateFinOccas is null
187 into v_nb;
188RETURN v_nb;
189END $$
190DELIMITER ;
191
192-- -------------------------------------------------------------
193-- Fonction pour calculer le montant à payer pour un occasionnel
194-- -------------------------------------------------------------
195DELIMITER $$
196CREATE FUNCTION FnCalculeMontant(p_dateDebut datetime, p_dateFin datetime)
197RETURNS decimal(6,2)
198BEGIN
199 DECLARE v_tHeure int DEFAULT 5;
200 DECLARE v_tJour int DEFAULT 20;
201 DECLARE v_timeDiff float;
202 DECLARE v_montant decimal(6,2);
203
204 SET v_timeDiff = CEIL(TIMESTAMPDIFF(MINUTE, p_dateDebut, p_dateFin)/60);
205 IF (v_timeDiff > 24) THEN
206 SET v_montant = CEIL(v_timeDiff/24 * v_tJour);
207 ELSEIF (v_timeDiff > 23) THEN
208 SET v_montant = v_tJour;
209 ELSE
210 SET v_montant = v_timeDiff * v_tHeure;
211 END IF;
212 RETURN v_montant;
213END$$
214DELIMITER ;
215
216#select FnCalculeMontant("2018-04-08 21:25:00", "2018-04-08 22:25:00") as 'Montant à payer';
217
218-- ---------------------------------------------------------------------------------------
219-- Fonction FnCreditAbonne() qui retourne le nombre de paiement Abonnement fait par crédit
220-- ---------------------------------------------------------------------------------------
221DELIMITER $$
222CREATE FUNCTION FnCreditAbonne(p_dateTrans DATE)
223RETURNS DECIMAL(9,2)
224BEGIN
225 DECLARE v_Montant DECIMAL(9,2) default 0;
226 SELECT SUM(trans.montant)
227 FROM tblTransaction as trans
228 INNER JOIN tblMethodPaiement as methode
229 ON trans.idPaiement = methode.idPaiement
230 INNER JOIN tblAbonnement as abon
231 ON trans.idAbonnement = abon.idAbonnement
232 WHERE methode.typePaiement in ('V','M','X') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
233 INTO v_Montant;
234 if (v_Montant is null) then
235 set v_Montant = 0;
236 end if;
237RETURN v_Montant;
238END $$
239DELIMITER ;
240
241select FnCreditAbonne(curdate()) as 'Nombre de vente par crédit';
242
243-- ---------------------------------------------------------------------------------------
244-- Fonction FnComptantAbonne() qui retourne le nombre de paiement Abonnement fait comptant
245-- ---------------------------------------------------------------------------------------
246DELIMITER $$
247CREATE FUNCTION FnComptantAbonne(p_dateTrans DATE)
248RETURNS DECIMAL(9,2)
249BEGIN
250 DECLARE v_Montant DECIMAL(9,2);
251 SELECT SUM(trans.montant)
252 FROM tblTransaction as trans
253 INNER JOIN tblMethodPaiement as methode
254 ON trans.idPaiement = methode.idPaiement
255 INNER JOIN tblAbonnement as abon
256 ON trans.idAbonnement = abon.idAbonnement
257 WHERE methode.typePaiement in ('A','C') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
258 INTO v_Montant;
259 if (v_Montant is null) then
260 set v_Montant = 0;
261 end if;
262RETURN v_Montant;
263END $$
264DELIMITER ;
265
266select FnComptantAbonne(curdate()) as 'Nombre de vente comptant/chèque';
267
268
269 -- ---------------------------------------------------------------------------------------
270-- Fonction FnCreditOcca() qui retourne le nombre de paiement Occasionnel fait par crédit
271-- ---------------------------------------------------------------------------------------
272DELIMITER $$
273CREATE FUNCTION FnCreditOcca(p_dateTrans DATE)
274RETURNS DECIMAL(9,2)
275BEGIN
276 DECLARE v_Montant DECIMAL(9,2);
277 SELECT SUM(trans.montant)
278 FROM tblTransaction as trans
279 INNER JOIN tblMethodPaiement as methode
280 ON trans.idPaiement = methode.idPaiement
281 INNER JOIN tblOccasionnel as occa
282 ON trans.idOccas = occa.idOccas
283 WHERE methode.typePaiement in ('V','M','X') AND idAbonnement is null AND cast(occa.dateFinOccas as date) = p_dateTrans
284 INTO v_Montant;
285 if (v_Montant is null) then
286 set v_Montant = 0;
287 end if;
288RETURN v_Montant;
289END $$
290DELIMITER ;
291
292select FnCreditOcca(curdate()) as 'Nombre de vente par crédit';
293
294-- ---------------------------------------------------------------------------------------
295-- Fonction FnComptantOcca() qui retourne le nombre de paiement Occasionnel fait comptant
296-- ---------------------------------------------------------------------------------------
297DELIMITER $$
298CREATE FUNCTION FnComptantOcca(p_dateTrans DATE)
299RETURNS DECIMAL(9,2)
300BEGIN
301 DECLARE v_Montant DECIMAL(9,2);
302 SELECT SUM(trans.montant)
303 FROM tblTransaction as trans
304 INNER JOIN tblMethodPaiement as methode
305 ON trans.idPaiement = methode.idPaiement
306 INNER JOIN tblOccasionnel as occa
307 ON trans.idOccas = occa.idOccas
308 WHERE methode.typePaiement in ('A','C') AND idAbonnement is null AND cast(occa.dateFinOccas as DATE) = p_dateTrans
309 INTO v_Montant;
310 if (v_Montant is null) then
311 set v_Montant = 0;
312 end if;
313RETURN v_Montant;
314END $$
315DELIMITER ;
316
317SELECT SUM(trans.montant) FROM tblTransaction as trans
318 INNER JOIN tblMethodPaiement as methode
319 ON trans.idPaiement = methode.idPaiement
320 INNER JOIN tblOccasionnel as occa
321 ON trans.idOccas = occa.idOccas
322 WHERE methode.typePaiement in ('A','C') AND idAbonnement is null AND DATE(occa.dateFinOccas) = curdate()
323 INTO @v_Montant;
324
325select @v_Montant;
326select curdate();
327select dateFinOccas from tblOccasionnel;
328select FnComptantOcca(curdate()) as 'Nombre de vente comptant/chèque';
329select SUM(montant) from tblTransaction where idPaiement = 4 and idAbonnement is null;
330delete from tblTransaction where idTransaction = 111;
331-- ------------------------------------------------------------
332-- Fonction pour générer des plaques de voiture
333-- ------------------------------------------------------------
334DELIMITER $$
335CREATE FUNCTION randomPlaque()
336 RETURNS VARCHAR(6)
337BEGIN
338 DECLARE plaque VARCHAR(6) DEFAULT "";
339 SELECT concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
340 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
341 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
342 substring('0123456789', rand()*9+1, 1),
343 substring('0123456789', rand()*9+1, 1),
344 substring('0123456789', rand()*9+1, 1))
345 into @idVehicule;
346
347 SET @rcount = -1;
348 SELECT COUNT(*) INTO @rcount FROM `tblVehicule` WHERE `idVehicule` = @idVehicule ;
349
350 IF @rcount = 0 THEN
351 SET plaque = @idVehicule ;
352 END IF ;
353
354 RETURN plaque ;
355END$$
356DELIMITER ;
357
358show create table tblAbonnement;
359show create table tblVehicule;
360select randomPlaque();
361
362-- -----------------------------------------------------
363-- Procédures
364-- ------------------------------------------------------------------------------
365-- Procédure pour la création des places dans tblPlace selon le nombre et le type
366-- ------------------------------------------------------------------------------
367DELIMITER $$
368CREATE PROCEDURE `InsertPlaces`(in p_nb int, in p_type varchar(25))
369BEGIN
370 DECLARE v_i int DEFAULT 1;
371
372 WHILE v_i <= p_nb DO
373 INSERT INTO tblPlace (typePlace,idAbonne) VALUES(p_type, null);
374 SET v_i = v_i + 1;
375 END WHILE;
376END $$
377DELIMITER ;
378
379-- -------------------------------------------------------------------------
380-- Procédure CompterPlaceDispo(), va retourner le nombre de place disponible
381-- en tenant compte des places utilisés par les abonnés et occasionnels
382-- -------------------------------------------------------------------------
383DELIMITER $$
384CREATE PROCEDURE `CompterPlaceDispo`()
385BEGIN
386 DECLARE v_nbOccas int;
387 DECLARE v_nbAbon int;
388 DECLARE v_nbTotal int;
389
390 SET v_nbOccas = FnNbPlaceOccas();
391 SET v_nbAbon = FnNbPlaceAbonne();
392 SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
393 SELECT v_nbTotal as 'Stationnement disponible';
394END$$
395DELIMITER ;
396
397call CompterPlaceDispo();
398-- -----------------------------------------------------------------------------------
399-- Procédure SommesPercuesParType(), va calculer les montants percus par type d'usager
400-- en se basant sur des dates fournis
401-- -----------------------------------------------------------------------------------
402DELIMITER $$
403CREATE PROCEDURE `SommesPercuesParType`(IN p_dateDebut Date, IN p_dateFin Date)
404BEGIN
405 DECLARE v_type varchar(8);
406 DECLARE v_nbAbon int;
407 DECLARE v_nbOccas int;
408 DECLARE v_somme decimal(9,2);
409 DECLARE v_total int;
410
411 SELECT * FROM tblAbonne as ab
412 INNER JOIN tblAbonnement as abt
413 ON ab.idAbonne = abt.idAbonne
414 WHERE abt.dateDebutAbonne >= p_dateDebut and abt.dateFinAbonne <= p_dateFin;
415END$$
416DELIMITER ;
417
418call SommesPercuesParType('2018-01-01', curdate());
419-- -----------------------------------------------------
420-- Procédure AjoutAbonne(), va faire l'ajout d'un abonné
421-- -----------------------------------------------------
422DELIMITER $$
423CREATE PROCEDURE `AjoutAbonne`(in p_nomAbonne varchar(50), in p_prenomAbonne varchar(25), in p_codePostal varchar(7),
424 in p_telephone varchar(15), in p_courriel varchar(50), in p_typeAbonne char(1), in p_idVille int)
425BEGIN
426 SET @rcount = -1;
427 SELECT COUNT(*) INTO @rcount FROM `tblAbonne` WHERE `idAbonne` = p_nomAbonne ;
428
429 IF @rcount = 0 THEN
430 INSERT INTO tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille)
431 VALUES(p_nomAbonne,p_prenomAbonne,p_codePostal,p_telephone,p_courriel,p_typeAbonne,p_idVille);
432 END IF ;
433
434END$$
435DELIMITER ;
436
437select * from tblAbonne;
438
439-- -----------------------------------------------------------------------------------------------------------------------
440-- Procédure AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement et retourner l'id de celle-ci
441-- -----------------------------------------------------------------------------------------------------------------------
442DELIMITER $$
443CREATE PROCEDURE `AjoutTransactionAbonne`(in p_duree int, in p_numPaiement varchar(16), in p_idPaiement int, out LIDTRANSACABON int)
444BEGIN
445 DECLARE v_tMois int DEFAULT 240;
446 DECLARE v_tAn int DEFAULT 2500;
447 DECLARE v_mTotal int;
448 DECLARE v_montant decimal(6,2);
449 IF (p_duree > 11) THEN
450 SET v_mTotal = CEIL(p_duree / 12) * v_tAn;
451 ELSE
452 SET v_mTotal = p_duree * v_tMois;
453 END IF;
454
455 SET v_montant = v_mTotal;
456
457 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
458 VALUES(v_montant,p_numPaiement,p_idPaiement,null,null);
459
460 SET LIDTRANSACABON = LAST_INSERT_ID();
461END$$
462DELIMITER ;
463
464#call AjoutTransactionAbonne(2,null,4);
465#select * from tblMethodPaiement;
466
467-- --------------------------------------------------
468-- Procédure AjoutAbonnement(), va créer l'abonnement
469-- --------------------------------------------------
470DELIMITER $$
471CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_duree int, in p_idAbonne int, in p_idPaiement int, in p_numPaiement varchar(16)
472 , in p_idVehicule varchar(7), in p_idPlace smallint)
473BEGIN
474 DECLARE v_idTransac int;
475 DECLARE v_dateFin date;
476
477 SET v_dateFin = DATE_ADD(p_dateDebut, INTERVAL p_duree MONTH);
478 -- Appele la procedure pour générer la transaction de l'abonnement
479 call AjoutTransactionAbonne(p_duree, p_numPaiement, p_idPaiement, @LIDTRANSACABON);
480
481 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
482 VALUES(p_dateDebut,v_dateFin,p_idAbonne,p_idVehicule,p_idPlace,@LIDTRANSACABON);
483
484 UPDATE tblTransaction set idAbonnement = LAST_INSERT_ID() where idTransaction = @LIDTRANSACABON;
485
486END$$
487DELIMITER ;
488
489#SET FOREIGN_KEY_CHECKS = 1;
490
491-- ----------------------------------------------------------------------------------------
492-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour l'action d'occasionnel
493-- ----------------------------------------------------------------------------------------
494DELIMITER $$
495CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas int, in p_idPaiement int, in p_numPaiement varchar(16), out LIDTRANSACOCCAS int)
496BEGIN
497 DECLARE v_montant decimal(6,2);
498 DECLARE v_dateDebut datetime;
499
500 SELECT dateDebutOccas FROM tblOccasionnel AS occas
501 INNER JOIN tblTransaction as trans
502 ON occas.idOccas = trans.idOccas
503 WHERE occas.idOccas = p_idOccas
504 INTO @v_dateDebut;
505
506 SET v_montant = FnCalculeMontant(@v_dateDebut, now());
507
508 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
509 VALUES(v_montant,p_numPaiement,p_idPaiement,null,p_idOccas);
510
511 SET LIDTRANSACOCCAS = LAST_INSERT_ID();
512END$$
513DELIMITER ;
514
515SELECT dateDebutOccas FROM tblOccasionnel AS occas
516 INNER JOIN tblTransaction as trans
517 ON occas.idOccas = trans.idOccas
518 WHERE occas.idOccas = 201804181
519 INTO @v_dateDebut;
520
521select @v_dateDebut;
522select FnCalculeMontant(@v_dateDebut, now());
523-- ---------------------------------------------------------------
524-- Procédure AjoutOccasionnel(), va créer l'ajout d'un occasionnel
525-- ---------------------------------------------------------------
526DELIMITER $$
527CREATE PROCEDURE `AjoutOccasionnel`()
528BEGIN
529
530 DECLARE v_nbIdTotal int DEFAULT 0;
531 DECLARE v_nbIdAvant int DEFAULT 0;
532 DECLARE v_idOccas varchar(12);
533
534 SELECT count(*) into v_nbIdTotal
535 FROM tblOccasionnel;
536 SELECT count(*) into v_nbIdAvant
537 FROM tblOccasionnel WHERE TIMESTAMPDIFF(DAY, dateDebutOccas, now() != 0);
538
539 SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);
540
541 INSERT INTO tblOccasionnel VALUES(v_idOccas,now(),null,null);
542
543END$$
544DELIMITER ;
545
546-- --------------------------------------------------------------------------------------
547-- Procédure SortieOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
548-- --------------------------------------------------------------------------------------
549drop procedure if exists SortieOccasionnel;
550
551DELIMITER $$
552CREATE PROCEDURE `SortieOccasionnel`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16))
553BEGIN
554 call AjoutTransactionOccas(p_idOccas, p_idPaiement, p_numPaiement, @LIDTRANSACOCCAS);
555
556 UPDATE tblOccasionnel set dateFinOccas = now(), idTransaction = @LIDTRANSACOCCAS
557 WHERE idOccas = p_idOccas;
558 #call CompterPlaceDispo();
559END$$
560DELIMITER ;
561UPDATE tblOccasionnel set dateFinOccas = now() AND idTransaction = @LIDTRANSACOCCAS WHERE idOccas = '201804183';
562select * from tblOccasionnel;
563call SortieOccasionnel('201804186', 1, '4000000000000002');
564select * from tblTransaction order by idTransaction desc limit 1;
565select dateFinOccas from tblOccasionnel where idOccas = '201804186';
566-- -------------------------------------------------------------------------
567-- Procédure DepotQuotidien(), va retourner les achats par type de paiement et
568-- par type d'utilisateur
569-- -------------------------------------------------------------------------
570drop procedure if exists DepotQuotidien;
571DROP TEMPORARY TABLE if exists TMP_depot;
572
573DELIMITER $$
574CREATE PROCEDURE `DepotQuotidien`(p_DateVerif Date)
575BEGIN
576 DECLARE v_CrOcca int;
577 DECLARE v_CrAbon int;
578 DECLARE v_CoOcca int;
579 DECLARE v_CoAbon int;
580 set v_CrOcca = FnCreditOcca(p_DateVerif);
581 set v_CrAbon = FnCreditAbonne(p_DateVerif);
582 set v_CoOcca = FnComptantOcca(p_DateVerif);
583 set v_CoAbon = FnComptantAbonne(p_DateVerif);
584
585
586 CREATE TEMPORARY TABLE TMP_depot (
587 type_Depot varchar(25),
588 Abonnements DECIMAL(9,2),
589 Occasionnels DECIMAL(9,2));
590
591 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
592 VALUE ('Carte de crédit', v_CrAbon, v_CrOcca);
593 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
594 VALUE ('Argent comptant', v_CoAbon, v_CoOcca);
595 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
596 VALUE ('Total', (v_CoAbon + v_CrAbon), (v_CoOcca + v_CrOcca));
597
598 SELECT * From TMP_depot;
599
600 DROP TEMPORARY TABLE TMP_depot;
601
602END$$
603DELIMITER ;
604
605call DepotQuotidien(curdate());
606#call DepotQuotidien('2018-02-02');
607#SELECT SUM(FnCreditOcca(curdate()) + FnComptantOcca(curdate()));
608
609###############################
610### INSERTIONS DES DONNÉÉES ###
611###############################
612
613SET FOREIGN_KEY_CHECKS = 1;
614
615use TP3;
616
617-- appele de la procédure pour faire l'insertion des places
618call InsertPlaces(500, 'plein air');
619call InsertPlaces(2000, 'couvert');
620
621-- insertion manuelle, car pas besoin de beaucoup de ville
622INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
623
624-- insertion manuelle avec des données produite par generatedata
625INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
626 VALUES ("Harveys",NULL,"Q4A2O7","1-195-388-6491","nec@Nulla.co.uk","S","3"),("Valentine","Cooper","K6A6V3","1-398-119-9628","habitant.morbi@diamluctuslobortis.com","P","3"),("Hedley","Harrell","B9D5S6","1-630-751-9399","leo@cursusaenim.ca","P","8"),("Gary","Roberts","Q2R2V3","1-965-296-3762","Morbi.accumsan.laoreet@fermentum.co.uk","P","7"),("Francis","Austin","P8O0F3","1-892-685-0409","tempor.diam@sociis.org","P","1"),("Edward","Mosley","S8J5W1","1-385-850-5982","et@urnaet.ca","P","3"),("Benedict","Bell","U0K6N4","1-570-196-4909","nunc.sit.amet@penatibusetmagnis.co.uk","P","8"),("Stewart","Soto","B1A4D5","1-893-189-9061","nec.metus.facilisis@enimcondimentumeget.ca","P","1"),("Neville","Marquez","O1J0H8","1-868-749-2997","ipsum.ac@diamnunc.edu","P","3"),("Zachery","Zimmerman","Y6T5A4","1-765-696-4719","et.tristique.pellentesque@dignissimtemporarcu.edu","P","5");
627INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
628 VALUES ("Simons",NULL,"Q9R5F5","1-878-506-6536","dictum.eu@a.edu","S","1"),("Steven","Hampton","T0V9T4","1-736-971-6586","Lorem.ipsum@ante.com","P","9"),("Jamal","Ramirez","V3F5Y9","1-826-233-8755","Donec.consectetuer@fringilla.net","P","2"),("Norman","Fields","M9C8J1","1-411-164-1888","a.feugiat@erosnectellus.net","P","2"),("Wyatt","Christensen","J8E3J3","1-746-245-0521","feugiat.Lorem.ipsum@nullaInteger.ca","P","1"),("Bert","Frazier","O0S9T6","1-781-873-6623","Aliquam.fringilla@Quisquelibero.com","P","7"),("Louis","House","I8M6E3","1-974-211-5774","ultrices.mauris@Integervitae.net","P","9"),("Alden","Cote","F3J7B6","1-893-631-0078","tristique.ac@quisturpis.net","P","5"),("Dolan","Fischer","L9Z4M5","1-141-234-6710","risus.Quisque@odiosagittissemper.co.uk","P","4"),("Zane","Manning","L6Y8S3","1-595-890-4447","imperdiet@placerataugueSed.com","P","4");
629INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
630 VALUES ("Sears",NULL,"Q7J7T4","1-337-614-9853","lacinia.vitae@malesuada.co.uk","S","6"),("Channing","Chapman","I9D6T3","1-530-608-2930","velit.Quisque@lectus.edu","P","7"),("Declan","Gamble","F4T1A0","1-431-958-2193","Fusce@lectusantedictum.co.uk","P","1"),("Zeph","Grant","U6Q2T3","1-662-303-3018","elit.Aliquam@nibh.ca","P","6"),("Reuben","Crane","Q2M9Z2","1-108-887-7001","dui.semper.et@felisDonec.co.uk","P","1"),("Cairo","Hensley","Z6V4Q0","1-193-858-1665","erat@nonluctussit.org","P","1"),("Acton","Garza","S5P7Q3","1-410-750-3337","neque@Quisque.net","P","1"),("Victor","Beach","B6V2T6","1-361-585-6601","at.risus@nonquamPellentesque.net","P","9"),("Brett","Doyle","O9P4R4","1-929-469-2034","aliquam@semmolestie.edu","P","9"),("Quentin","Haynes","X1D1L4","1-489-636-2755","nisl.Nulla.eu@vitaedolorDonec.edu","P","1");
631INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
632 VALUES ("Canac",NULL,"Y1C5C7","1-321-806-6027","In.ornare@adipiscingMaurismolestie.co.uk","S","6"),("Amery","Fletcher","A7V3H7","1-922-766-4167","ut.sem@sitametconsectetuer.edu","P","8"),("Lawrence","Conley","N2J8S1","1-309-234-5089","nec@luctuslobortisClass.net","P","9"),("Herman","Clarke","C4Q6N4","1-594-470-9280","Morbi.metus@dolor.net","P","9"),("Alvin","Garrison","I9N0E9","1-807-832-5544","augue.malesuada.malesuada@Nuncsedorci.co.uk","P","7"),("Jesse","Meyers","G0O1X7","1-593-459-8094","adipiscing.ligula@semper.edu","P","6"),("Martin","Montgomery","L8Z8W1","1-951-412-8004","molestie@Donecconsectetuer.net","P","9"),("Yuli","Wiley","T8I8E4","1-397-165-4584","diam.luctus@nec.org","P","8"),("Kareem","Singleton","Z3H8Q9","1-199-250-4773","et.libero.Proin@leo.edu","P","4"),("Ezekiel","Hardin","N3A6B8","1-425-440-1465","euismod@placerat.co.uk","P","4");
633INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
634 VALUES ("Ardenne",NULL,"A0G8T1","1-520-529-5925","Sed.nunc.est@antebibendumullamcorper.com","S","4"),("Donovan","Boyle","V9U4T5","1-116-449-6440","iaculis.odio.Nam@ipsumdolor.ca","P","5"),("Zachary","Wagner","I5I8I3","1-768-867-8790","Integer.aliquam@auguescelerisque.co.uk","P","4"),("Josiah","Pickett","W5L4F1","1-859-110-2038","enim.Suspendisse@leo.ca","P","7"),("Demetrius","Reed","K6E8S2","1-979-137-2222","Sed.nunc.est@laciniaatiaculis.org","P","6"),("Ezra","Strong","H7C2N6","1-403-362-0716","laoreet@metusAliquam.org","P","4"),("Slade","Mann","P7C1V8","1-812-124-1004","venenatis@laciniavitae.org","P","8"),("Emmanuel","George","U2R8C6","1-477-621-3758","feugiat.nec.diam@non.net","P","8"),("John","Warner","P8D4H6","1-276-604-5994","mi@feugiat.co.uk","P","3"),("Lucius","Rocha","U2D1F6","1-191-164-7881","erat.volutpat@posuerevulputatelacus.com","P","7");
635
636-- insertion manuelle, car compliquer de le faire avec generatedata
637INSERT INTO tblVehicule VALUES(randomPlaque(), "Honda", "Civic", "Bleu"),(randomPlaque(), "Mazda", "Protégé", "Gris"),(randomPlaque(), "Pontiac", "Sundance", "Gris")
638 ,(randomPlaque(), "Chevrolet", "Cavalier", "Orange"),(randomPlaque(), "Pontiac", "Sunfire", "Rose"),(randomPlaque(), "Ford", "Escort", "Bleu")
639 ,(randomPlaque(), "Suzuki", "Swift", "Vert"),(randomPlaque(), "Volskwagen", "Passat", "Jaune"),(randomPlaque(), "Honda", "Accord", "Noir")
640 ,(randomPlaque(), "Suzuki", "Grand Vitara", "Bleu");
641
642select * from tblVehicule;
643-- insertion manuelle, car pas assé de donnée à générer
644INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
645
646-- -------------------------------------------------------------------
647-- Procédure pour ajouter des occasionnels en lot selon une qte donnée
648-- -------------------------------------------------------------------
649DELIMITER $$
650CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
651BEGIN
652 DECLARE v_i int DEFAULT 1;
653 WHILE v_i <= p_nb DO
654 call AjoutOccasionnel();
655 SET v_i = v_i + 1;
656 END WHILE;
657END $$
658DELIMITER ;
659
660call AjoutOccasEnLot(50);
661
662-- -----------------------------------------------------------------
663-- Procédure pour ajouter des Abonnement en lot selon une qte donnée
664-- -----------------------------------------------------------------
665DELIMITER $$
666CREATE PROCEDURE `AjoutAbonEnLot`(in p_nb int)
667BEGIN
668 DECLARE v_i int DEFAULT 1;
669 DECLARE v_idAbon int;
670 DECLARE v_nbDuree int;
671 DECLARE v_idPaiement int;
672 DECLARE v_idVehicule varchar(6);
673 DECLARE v_idPlace int;
674 WHILE v_i <= p_nb DO
675 SET v_idAbon = FLOOR(RAND()*(50-1+1))+1;
676 SET v_nbDuree = FLOOR(RAND()*(12-1+1))+1;
677 SET v_idPaiement = FLOOR(RAND()*(5-1+1))+1;
678 SET v_idVehicule = (SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1);
679 SET v_idPlace = FLOOR(RAND()*(2500-1+1))+1;
680 IF (v_idPaiement = 4) THEN
681 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,null,v_idVehicule,v_idPlace);
682 ELSE
683 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,'4000000000000002',v_idVehicule,v_idPlace);
684 END IF;
685 SET v_i = v_i + 1;
686 END WHILE;
687END $$
688DELIMITER ;
689
690USE TP3;
691
692call AjoutAbonEnLot(10);
693SELECT idVehicule FROM tblVehicule;
694select * from tblAbonnement;
695delete from tblAbonnement where idAbonnement = 1;
696select * from tblTransaction;
697SELECT FLOOR(RAND()*(50-1+1))+1;
698select * from tblMethodPaiement;
699select count(*) from tblVehicule;
700show create table tblVehicule;
701SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1;
702select FnNbPlaceAbonne() as 'Nombre d\'abonnement';
703select FnNbPlaceOccas() as 'Nombre d\'occasionnel';
704#call CompterPlaceDispo();
705select curdate();
706SELECT DATE_FORMAT(curdate(), '%Y%m%d');
707#call AjoutAbonnement(curdate(), 2, 3, 4, null, 'ZYW467', 3);
708select * from tblAbonnement where idAbonnement = 16;
709select * from tblTransaction where idTransaction = 20;