· 8 years ago · Apr 24, 2018, 02:02 PM
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-- -----------------------------------------------------
14DROP SCHEMA IF EXISTS TP3;
15
16CREATE SCHEMA IF NOT EXISTS `TP3` DEFAULT CHARACTER SET utf8 ;
17USE `TP3` ;
18
19-- -----------------------------------------------------
20-- Table `TP3`.`tblVille`
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS `TP3`.`tblVille` (
23 `idVille` INT NOT NULL AUTO_INCREMENT,
24 `nomVille` VARCHAR(50) NOT NULL,
25 PRIMARY KEY (`idVille`),
26 INDEX `idx_nomVille` (`nomVille` ASC))
27ENGINE = InnoDB;
28
29-- -----------------------------------------------------
30-- Table `TP3`.`tblAbonne`
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonne` (
33 `idAbonne` INT NOT NULL AUTO_INCREMENT,
34 `nomAbonne` VARCHAR(50) NOT NULL,
35 `prenomAbonne` VARCHAR(25) NULL DEFAULT NULL,
36 `codePostal` VARCHAR(7) NOT NULL,
37 `telephone` VARCHAR(15) NOT NULL,
38 `courriel` VARCHAR(50) NOT NULL,
39 `typeAbonne` CHAR(1) NOT NULL,
40 `idVille` INT NOT NULL,
41 PRIMARY KEY (`idAbonne`),
42 INDEX `idx_nomAbonne` (`nomAbonne` ASC),
43 INDEX `FK_tblAbonne_idVille` (`idVille` ASC),
44 CONSTRAINT `FK_tblAbonne_idVille`
45 FOREIGN KEY (`idVille`)
46 REFERENCES `TP3`.`tblVille` (`idVille`))
47ENGINE = InnoDB;
48
49-- -----------------------------------------------------
50-- Table `TP3`.`tblVehicule`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `TP3`.`tblVehicule` (
53 `idVehicule` VARCHAR(7) NOT NULL,
54 `marque` VARCHAR(25) NOT NULL,
55 `modele` VARCHAR(25) NOT NULL,
56 `couleur` VARCHAR(15) NOT NULL,
57 PRIMARY KEY (`idVehicule`))
58ENGINE = InnoDB;
59
60-- -----------------------------------------------------
61-- Table `TP3`.`tblMethodPaiement`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `TP3`.`tblMethodPaiement` (
64 `idPaiement` INT NOT NULL AUTO_INCREMENT,
65 `typePaiement` CHAR(1) NOT NULL,
66 `descPaiement` VARCHAR(25) NOT NULL,
67 PRIMARY KEY (`idPaiement`))
68ENGINE = InnoDB;
69
70-- -----------------------------------------------------
71-- Table `TP3`.`tblOccasionnel`
72-- -----------------------------------------------------
73CREATE TABLE IF NOT EXISTS `TP3`.`tblOccasionnel` (
74 `idOccas` VARCHAR(12) NOT NULL,
75 `dateDebutOccas` DATETIME NOT NULL,
76 `dateFinOccas` DATETIME NULL,
77 `idTransaction` INT NULL,
78 PRIMARY KEY (`idOccas`),
79 INDEX `FK_tblOccasionnel_idTransaction` (`idTransaction` ASC),
80 CONSTRAINT `FK_tblOccasionnel_idTransaction`
81 FOREIGN KEY (`idTransaction`)
82 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
83ENGINE = InnoDB;
84
85-- -----------------------------------------------------
86-- Table `TP3`.`tblTransaction`
87-- -----------------------------------------------------
88CREATE TABLE IF NOT EXISTS `TP3`.`tblTransaction` (
89 `idTransaction` INT NOT NULL AUTO_INCREMENT,
90 `montant` DECIMAL(6,2) NULL,
91 `numPaiement` VARCHAR(16) NULL,
92 `idPaiement` INT NULL,
93 `idAbonnement` INT NULL,
94 `idOccas` VARCHAR(12) NULL,
95 PRIMARY KEY (`idTransaction`),
96 INDEX `FK_tblTransaction_idPaiement` (`idPaiement` ASC),
97 INDEX `FK_tblTransaction_idAbonnement` (`idAbonnement` ASC),
98 INDEX `FK_tblTransaction_idOccas` (`idOccas` ASC),
99 CONSTRAINT `FK_tblTransaction_idPaiement`
100 FOREIGN KEY (`idPaiement`)
101 REFERENCES `TP3`.`tblMethodPaiement` (`idPaiement`),
102 CONSTRAINT `FK_tblTransaction_idAbonne`
103 FOREIGN KEY (`idAbonnement`)
104 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`),
105 CONSTRAINT `FK_tblTransaction_idOccas`
106 FOREIGN KEY (`idOccas`)
107 REFERENCES `TP3`.`tblOccasionnel` (`idOccas`))
108ENGINE = InnoDB;
109
110-- -----------------------------------------------------
111-- Table `TP3`.`tblAbonnement`
112-- -----------------------------------------------------
113CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonnement` (
114 `idAbonnement` INT NOT NULL AUTO_INCREMENT,
115 `dateDebutAbonne` DATE NOT NULL,
116 `dateFinAbonne` DATE NOT NULL,
117 `idAbonne` INT NOT NULL,
118 `idVehicule` VARCHAR(7) NOT NULL,
119 `idPlace` SMALLINT NOT NULL,
120 `idTransaction` INT NOT NULL,
121 PRIMARY KEY (`idAbonnement`),
122 INDEX `FK_tblAbonnement_idAbonne_tblAbonne` (`idAbonne` ASC),
123 INDEX `FK_tblAbonnement_idVehicule` (`idVehicule` ASC),
124 INDEX `FK_tblAbonnement_idPlace` (`idPlace` ASC),
125 INDEX `FK_tblAbonnement_idTransaction` (`idTransaction` ASC),
126 CONSTRAINT `FK_tblAbonnement_idAbonne_tblAbonne`
127 FOREIGN KEY (`idAbonne`)
128 REFERENCES `TP3`.`tblAbonne` (`idAbonne`),
129 CONSTRAINT `FK_tblAbonnement_idVehicule`
130 FOREIGN KEY (`idVehicule`)
131 REFERENCES `TP3`.`tblVehicule` (`idVehicule`),
132 CONSTRAINT `FK_tblAbonnement_idPlace`
133 FOREIGN KEY (`idPlace`)
134 REFERENCES `TP3`.`tblPlace` (`idPlace`),
135 CONSTRAINT `FK_tblAbonnement_idTransaction`
136 FOREIGN KEY (`idTransaction`)
137 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
138ENGINE = InnoDB;
139
140-- -----------------------------------------------------
141-- Table `TP3`.`tblPlace`
142-- -----------------------------------------------------
143CREATE TABLE IF NOT EXISTS `TP3`.`tblPlace` (
144 `idPlace` SMALLINT NOT NULL AUTO_INCREMENT,
145 `typePlace` VARCHAR(25) NOT NULL,
146 `idAbonne` INT NULL,
147 PRIMARY KEY (`idPlace`),
148 INDEX `FK_tblPlace_idAbonne` (`idAbonne` ASC),
149 CONSTRAINT `FK_tblPlace_idAbonne`
150 FOREIGN KEY (`idAbonne`)
151 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
152ENGINE = InnoDB;
153
154SET SQL_MODE=@OLD_SQL_MODE;
155SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
156SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
157
158###############################
159### FONCTIONS ET PROCÉDURES ###
160###############################
161
162-- -----------------------------------------------------
163## Fonctions ###########################################
164-- -----------------------------------------------------
165-- --------------------------------------------------------------------------------------------
166-- Fonction FnNbPlaceAbonne() qui retourne le nombre de place utilisé par un abonnement valide
167-- --------------------------------------------------------------------------------------------
168DROP FUNCTION IF EXISTS FnNbPlaceAbonne;
169
170DELIMITER $$
171CREATE FUNCTION FnNbPlaceAbonne()
172RETURNS int
173BEGIN
174 DECLARE v_nb int;
175 SELECT count(*) as v_nb
176 FROM tblAbonnement as abon
177 WHERE abon.dateFinAbonne > curdate()
178 into v_nb;
179RETURN v_nb;
180END $$
181DELIMITER ;
182
183-- ------------------------------------------------------------------------------------------
184-- Fonction FnNbPlaceOccas() qui retourne le nombre de place utilisé par un occasionel actif
185-- ------------------------------------------------------------------------------------------
186DROP FUNCTION IF EXISTS FnNbPlaceOccas;
187
188DELIMITER $$
189CREATE FUNCTION FnNbPlaceOccas()
190RETURNS int
191BEGIN
192 DECLARE v_nb int;
193 SELECT count(*) as v_nv
194 FROM tblOccasionnel as ocas
195 WHERE ocas.dateFinOccas is null
196 into v_nb;
197RETURN v_nb;
198END $$
199DELIMITER ;
200
201-- --------------------------------------------------------------
202-- Fonction pour calculer le montant à payer pour un occasionnel
203-- --------------------------------------------------------------
204DROP FUNCTION IF EXISTS FnCalculerMontant;
205
206DELIMITER $$
207CREATE FUNCTION FnCalculerMontant(p_idOccas varchar(12))
208RETURNS DECIMAL(6, 2)
209BEGIN
210 DECLARE v_tHeure int DEFAULT 5;
211 DECLARE v_tJour int DEFAULT 20;
212 DECLARE v_mJour DECIMAL(6, 2);
213 DECLARE v_mHeure DECIMAL(6, 2);
214 DECLARE v_dateDebut DATETIME;
215 DECLARE v_dTemps TIME;
216 DECLARE v_nbJour INT;
217
218 SELECT dateDebutOccas INTO v_dateDebut FROM tblOccasionnel
219 WHERE idOccas = p_idOccas;
220 SET v_dTemps = TIMEDIFF(CURRENT_TIME(), TIME(v_dateDebut));
221
222 IF (TIME(v_dateDebut) > CURRENT_TIME()) THEN
223 SET v_dTemps = TIMEDIFF('23:59:59', v_dTemps);
224 END IF;
225
226 SET v_nbJour = DATEDIFF(NOW(), v_dateDebut);
227 SET v_mJour = v_nbJour * v_tJour;
228 SET v_mHeure = CEIL(time_to_sec(v_dtemps) / (60 * 60)) * v_tHeure;
229
230 IF (v_mHeure > v_tJour)
231 THEN SET v_mHeure = v_tJour;
232 END IF;
233 RETURN (v_mJour + v_mHeure);
234END $$
235DELIMITER ;
236
237-- ----------------------------------------------------------------------------------------
238-- Fonction FnCreditAbonne() qui retourne le nombre de paiement Abonnement fait par crédit
239-- ----------------------------------------------------------------------------------------
240DROP FUNCTION IF EXISTS FnCreditAbonne;
241
242DELIMITER $$
243CREATE FUNCTION FnCreditAbonne(p_dateTrans DATE)
244RETURNS DECIMAL(9,2)
245BEGIN
246 DECLARE v_Montant DECIMAL(9,2) default 0;
247 SELECT SUM(trans.montant)
248 FROM tblTransaction as trans
249 INNER JOIN tblMethodPaiement as methode
250 ON trans.idPaiement = methode.idPaiement
251 INNER JOIN tblAbonnement as abon
252 ON trans.idAbonnement = abon.idAbonnement
253 WHERE methode.typePaiement in ('V','M','X') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
254 INTO v_Montant;
255 if (v_Montant is null) then
256 set v_Montant = 0;
257 end if;
258RETURN v_Montant;
259END $$
260DELIMITER ;
261
262-- ----------------------------------------------------------------------------------------
263-- Fonction FnComptantAbonne() qui retourne le nombre de paiement Abonnement fait comptant
264-- ----------------------------------------------------------------------------------------
265DROP FUNCTION IF EXISTS FnComptantAbonne;
266
267DELIMITER $$
268CREATE FUNCTION FnComptantAbonne(p_dateTrans DATE)
269RETURNS DECIMAL(9,2)
270BEGIN
271 DECLARE v_Montant DECIMAL(9,2);
272 SELECT SUM(trans.montant)
273 FROM tblTransaction as trans
274 INNER JOIN tblMethodPaiement as methode
275 ON trans.idPaiement = methode.idPaiement
276 INNER JOIN tblAbonnement as abon
277 ON trans.idAbonnement = abon.idAbonnement
278 WHERE methode.typePaiement in ('A','C') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
279 INTO v_Montant;
280 if (v_Montant is null) then
281 set v_Montant = 0;
282 end if;
283RETURN v_Montant;
284END $$
285DELIMITER ;
286
287 -- ---------------------------------------------------------------------------------------
288-- Fonction FnCreditOcca() qui retourne le nombre de paiement Occasionnel fait par crédit
289-- ---------------------------------------------------------------------------------------
290DROP FUNCTION IF EXISTS FnCreditOcca;
291
292DELIMITER $$
293CREATE FUNCTION FnCreditOcca(p_dateTrans DATE)
294RETURNS DECIMAL(9,2)
295BEGIN
296 DECLARE v_Montant DECIMAL(9,2);
297 SELECT SUM(trans.montant)
298 FROM tblTransaction as trans
299 INNER JOIN tblMethodPaiement as methode
300 ON trans.idPaiement = methode.idPaiement
301 INNER JOIN tblOccasionnel as occa
302 ON trans.idOccas = occa.idOccas
303 WHERE methode.typePaiement in ('V','M','X') AND idAbonnement is null AND cast(occa.dateFinOccas as date) = p_dateTrans
304 INTO v_Montant;
305 if (v_Montant is null) then
306 set v_Montant = 0;
307 end if;
308RETURN v_Montant;
309END $$
310DELIMITER ;
311
312-- ---------------------------------------------------------------------------------------
313-- Fonction FnComptantOcca() qui retourne le nombre de paiement Occasionnel fait comptant
314-- ---------------------------------------------------------------------------------------
315DROP FUNCTION IF EXISTS FnComptantOcca;
316
317DELIMITER $$
318CREATE FUNCTION FnComptantOcca(p_dateTrans DATE)
319RETURNS DECIMAL(9,2)
320BEGIN
321 DECLARE v_Montant DECIMAL(9,2);
322 SELECT SUM(trans.montant)
323 FROM tblTransaction as trans
324 INNER JOIN tblMethodPaiement as methode
325 ON trans.idPaiement = methode.idPaiement
326 INNER JOIN tblOccasionnel as occa
327 ON trans.idOccas = occa.idOccas
328 WHERE methode.typePaiement in ('A','C') AND idAbonnement is null AND cast(occa.dateFinOccas as DATE) = p_dateTrans
329 INTO v_Montant;
330 if (v_Montant is null) then
331 set v_Montant = 0;
332 end if;
333RETURN v_Montant;
334END $$
335DELIMITER ;
336
337-- ------------------------------------------------------------
338-- Fonction pour générer des plaques de voiture aléatoirement,
339-- tout en validant qu'il n'y a pas de doublons.
340-- ------------------------------------------------------------
341DROP FUNCTION IF EXISTS FnRandomPlaque;
342
343DELIMITER $$
344CREATE FUNCTION FnRandomPlaque()
345 RETURNS VARCHAR(6)
346BEGIN
347 DECLARE plaque VARCHAR(6) DEFAULT "";
348 SELECT concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
349 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
350 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
351 substring('0123456789', rand()*9+1, 1),
352 substring('0123456789', rand()*9+1, 1),
353 substring('0123456789', rand()*9+1, 1))
354 into @idVehicule;
355
356 SET @rcount = -1;
357 SELECT COUNT(*) INTO @rcount FROM `tblVehicule` WHERE `idVehicule` = @idVehicule ;
358
359 IF @rcount = 0 THEN
360 SET plaque = @idVehicule ;
361 END IF ;
362
363 RETURN plaque ;
364END$$
365DELIMITER ;
366
367-- ----------------------------------------------------------------------------
368-- Fonction pour retourner le nombre d'Abonnement de type Personne ou Société.
369-- Prend en entré 2 dates et le type désiré.
370-- ----------------------------------------------------------------------------
371DROP FUNCTION IF EXISTS FnNbAbon;
372
373DELIMITER $$
374CREATE FUNCTION FnNbAbon(p_DateDebut Date, p_DateFin Date, p_TypeAbonne char)
375 RETURNS int
376BEGIN
377 DECLARE v_nb int;
378SELECT count(*) FROM tblTransaction as trans
379 INNER JOIN tblAbonnement as abonnem
380 ON trans.idTransaction = abonnem.idTransaction
381 INNER JOIN tblAbonne as abon
382 ON abonnem.idAbonne = abon.idAbonne
383 WHERE abon.typeAbonne = p_TypeAbonne AND abonnem.dateDebutAbonne <= p_DateFin AND abonnem.dateDebutAbonne > p_DateDebut
384 INTO v_nb;
385
386 IF (v_nb is null) THEN
387 SET v_nb = 0;
388 END IF ;
389
390 RETURN v_nb ;
391END$$
392DELIMITER ;
393
394-- ------------------------------------------------------------------------------
395-- Fonction pour connaitre la somme des abonnements de type Personne ou Société.
396-- Prend en paramètre 2 dates et le type désiré.
397-- ------------------------------------------------------------------------------
398DROP FUNCTION IF EXISTS FnSommeAbon;
399
400DELIMITER $$
401CREATE FUNCTION FnSommeAbon(p_DateDebut Date, p_DateFin Date, p_TypeAbonne char)
402 RETURNS int
403BEGIN
404 DECLARE v_somme int;
405SELECT sum(trans.montant) FROM tblTransaction as trans
406 INNER JOIN tblAbonnement as abonnem
407 ON trans.idTransaction = abonnem.idTransaction
408 INNER JOIN tblAbonne as abon
409 ON abonnem.idAbonne = abon.idAbonne
410 WHERE abon.typeAbonne = p_TypeAbonne AND abonnem.dateDebutAbonne <= p_DateFin AND abonnem.dateDebutAbonne > p_DateDebut
411 INTO v_somme;
412
413 IF (v_somme is null) THEN
414 SET v_somme = 0;
415 END IF ;
416
417 RETURN v_somme ;
418END$$
419DELIMITER ;
420
421-- ------------------------------------------------------------------------
422-- Fonction pour connaitre le nombre d'occasionnel toujours actif pour une
423-- période donnée avec 2 dates en paramètre
424-- ------------------------------------------------------------------------
425DROP FUNCTION IF EXISTS FnNbOcca;
426
427DELIMITER $$
428CREATE FUNCTION FnNbOcca(p_DateDebut Date, p_DateFin Date)
429 RETURNS int
430BEGIN
431 DECLARE v_nb int;
432SELECT count(*) FROM tblTransaction as trans
433 INNER JOIN tblOccasionnel as occa
434 ON trans.idOccas = occa.idOccas
435 WHERE CAST(occa.dateFinOccas as Date) <= p_DateFin AND CAST(occa.dateDebutOccas as Date) >= p_DateDebut
436 INTO v_nb;
437
438 IF (v_nb is null) THEN
439 SET v_nb = 0;
440 END IF ;
441
442 RETURN v_nb ;
443END$$
444DELIMITER ;
445
446-- -------------------------------------------------------------------------------
447-- Fonction pour connaitre la somme des transactions générées pour un occasionnel
448-- entre 2 dates donnée en paramètre
449-- -------------------------------------------------------------------------------
450DROP FUNCTION IF EXISTS FnSommeOcca;
451
452DELIMITER $$
453CREATE FUNCTION FnSommeOcca(p_DateDebut Date, p_DateFin Date)
454 RETURNS int
455BEGIN
456 DECLARE v_nb int;
457SELECT sum(trans.montant) FROM tblTransaction as trans
458 INNER JOIN tblOccasionnel as occa
459 ON trans.idOccas = occa.idOccas
460 WHERE CAST(occa.dateFinOccas as Date) <= p_DateFin AND CAST(occa.dateDebutOccas as Date) >= p_DateDebut
461 INTO v_nb;
462
463 IF (v_nb is null) THEN
464 SET v_nb = 0;
465 END IF ;
466
467 RETURN v_nb ;
468END$$
469DELIMITER ;
470
471-- -----------------------------------------------------
472## Procédures ##########################################
473-- --------------------------------------------------------------------------------------
474-- Procédure pour la création des places dans tblPlace selon le nombre et le type donné.
475-- --------------------------------------------------------------------------------------
476DROP PROCEDURE IF EXISTS InsertPlaces;
477
478DELIMITER $$
479CREATE PROCEDURE `InsertPlaces`(in p_nb int, in p_type varchar(25))
480BEGIN
481 DECLARE v_i int DEFAULT 1;
482
483 WHILE v_i <= p_nb DO
484 INSERT INTO tblPlace (typePlace,idAbonne) VALUES(p_type, null);
485 SET v_i = v_i + 1;
486 END WHILE;
487END $$
488DELIMITER ;
489
490-- ------------------------------------------------------------------------------------
491-- Procédure SommesPercuesParType(), va calculer les montants percus par type d'usager
492-- en se basant sur 2 dates fournis en paramètre
493-- ------------------------------------------------------------------------------------
494DROP PROCEDURE IF EXISTS SommesPercuesParType;
495
496DELIMITER $$
497CREATE PROCEDURE `SommesPercuesParType`(IN p_dateDebut Date, IN p_dateFin Date)
498BEGIN
499 DECLARE v_type varchar(8);
500 DECLARE v_nbAbon int;
501 DECLARE v_nbOccas int;
502 DECLARE v_somme decimal(9,2);
503 DECLARE v_total int;
504
505 SELECT * FROM tblAbonne as ab
506 INNER JOIN tblAbonnement as abt
507 ON ab.idAbonne = abt.idAbonne
508 WHERE abt.dateDebutAbonne >= p_dateDebut and abt.dateFinAbonne <= p_dateFin;
509END$$
510DELIMITER ;
511
512-- -------------------------------------------------------------------------------------------------------------
513-- Procédure AjoutAbonne(), va faire l'ajout d'un abonné, après avoir valider qu'il éxiste pas déjà dans la BD.
514-- -------------------------------------------------------------------------------------------------------------
515DROP PROCEDURE IF EXISTS AjoutAbonne;
516
517DELIMITER $$
518CREATE PROCEDURE `AjoutAbonne`(in p_nomAbonne varchar(50), in p_prenomAbonne varchar(25), in p_codePostal varchar(7),
519 in p_telephone varchar(15), in p_courriel varchar(50), in p_typeAbonne char(1), in p_idVille int)
520BEGIN
521 SET @abCount = -1;
522 SELECT COUNT(*) INTO @abCount FROM `tblAbonne` WHERE `nomAbonne` = p_nomAbonne and `prenomAbonne` = p_prenomAbonne
523 or `nomAbonne` = p_nomAbonne and `prenomAbonne` is null;
524 SELECT COUNT(*) INTO @abCount FROM tblAbonne as abon WHERE abon.nomAbonne = p_nomAbonne and abon.prenomAbonne = p_prenomAbonne and abon.telephone = p_telephone
525 or abon.nomAbonne = p_nomAbonne and abon.prenomAbonne is null and abon.telephone = p_telephone;
526 IF @abCount = 0 THEN
527 INSERT INTO tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille)
528 VALUES(p_nomAbonne,p_prenomAbonne,p_codePostal,p_telephone,p_courriel,p_typeAbonne,p_idVille);
529 ELSE
530 SELECT 'Abonné déjà existant';
531 END IF ;
532END$$
533DELIMITER ;
534
535-- ------------------------------------------------------------------------------------------------------------------------
536-- Procédure AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement et retourner l'id de celle-ci
537-- ------------------------------------------------------------------------------------------------------------------------
538DROP PROCEDURE IF EXISTS AjoutTransactionAbonne;
539
540DELIMITER $$
541CREATE PROCEDURE `AjoutTransactionAbonne`(in p_duree int, in p_numPaiement varchar(16), in p_idPaiement int, out LIDTRANSACABON int)
542BEGIN
543 DECLARE v_tMois int DEFAULT 240;
544 DECLARE v_tAn int DEFAULT 2500;
545 DECLARE v_mTotal int;
546 DECLARE v_montant decimal(6,2);
547 IF (p_duree > 11) THEN
548 SET v_mTotal = CEIL(p_duree / 12) * v_tAn;
549 ELSE
550 SET v_mTotal = p_duree * v_tMois;
551 END IF;
552
553 SET v_montant = v_mTotal;
554
555 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
556 VALUES(v_montant,p_numPaiement,p_idPaiement,null,null);
557
558 SET LIDTRANSACABON = LAST_INSERT_ID();
559END$$
560DELIMITER ;
561
562-- ------------------------------------------------------------------------------------------------------------
563-- Procédure AjoutAbonnement(), va créer l'abonnement et va géréné la transaction automatiquement avec l'appel
564-- de la procédure AjoutTransactionAbonne()
565-- ------------------------------------------------------------------------------------------------------------
566DROP PROCEDURE IF EXISTS AjoutAbonnement;
567
568DELIMITER $$
569CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_duree int, in p_idAbonne int, in p_idPaiement int, in p_numPaiement varchar(16)
570 , in p_idVehicule varchar(7), in p_idPlace smallint)
571BEGIN
572 DECLARE v_idTransac int;
573 DECLARE v_dateFin date;
574
575 SET @abtCount = -1;
576 SELECT COUNT(*) INTO @abtCount FROM tblAbonnement where idVehicule = p_idVehicule and dateFinAbonne >= p_dateDebut
577 or idPlace = p_idPlace and dateFinAbonne >= p_dateDebut;
578
579 IF (@abtCount = 0) THEN
580 SET v_dateFin = DATE_ADD(p_dateDebut, INTERVAL p_duree MONTH);
581 -- Appele la procedure pour générer la transaction de l'abonnement
582 call AjoutTransactionAbonne(p_duree, p_numPaiement, p_idPaiement, @LIDTRANSACABON);
583
584 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
585 VALUES(p_dateDebut,v_dateFin,p_idAbonne,p_idVehicule,p_idPlace,@LIDTRANSACABON);
586
587 UPDATE tblTransaction set idAbonnement = LAST_INSERT_ID() where idTransaction = @LIDTRANSACABON;
588 ELSE
589 SELECT 'Un abonnement avec la même plaque ou place est toujours actif!';
590 END IF;
591END$$
592DELIMITER ;
593
594-- --------------------------------------------------------------------------------------------------
595-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour d'occasionnel lors de la sortie
596-- --------------------------------------------------------------------------------------------------
597DROP PROCEDURE IF EXISTS AjoutTransactionOccas;
598
599DELIMITER $$
600CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16), out LIDTRANSACOCCAS int)
601BEGIN
602 DECLARE v_montantOccas decimal(6,2);
603 DECLARE v_dateDebut datetime;
604
605 SELECT dateDebutOccas FROM tblOccasionnel AS occas
606 INNER JOIN tblTransaction as trans
607 ON occas.idOccas = trans.idOccas
608 WHERE occas.idOccas = p_idOccas
609 INTO @v_dateDebut;
610
611 SET v_montantOccas = FnCalculerMontant(p_idOccas);
612
613 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
614 VALUES(v_montantOccas,p_numPaiement,p_idPaiement,null,p_idOccas);
615
616 SET LIDTRANSACOCCAS = LAST_INSERT_ID();
617END$$
618DELIMITER ;
619
620-- ----------------------------------------------------------------
621-- Procédure AjoutOccasionnel(), va créer l'ajout d'un occasionnel
622-- ----------------------------------------------------------------
623DROP PROCEDURE IF EXISTS AjoutOccasionnel;
624
625DELIMITER $$
626CREATE PROCEDURE `AjoutOccasionnel`()
627BEGIN
628
629 DECLARE v_nbIdTotal int DEFAULT 0;
630 DECLARE v_nbIdAvant int DEFAULT 0;
631 DECLARE v_idOccas varchar(12);
632
633 SELECT count(*) into v_nbIdTotal
634 FROM tblOccasionnel;
635 SELECT count(*) into v_nbIdAvant
636 FROM tblOccasionnel WHERE DAY(dateDebutOccas) != DAY(now());
637
638 SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);
639
640 INSERT INTO tblOccasionnel VALUES(v_idOccas,now(),null,null);
641
642END$$
643DELIMITER ;
644
645-- -----------------------------------------------------------------------------------------------
646-- Procédure SortieOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
647-- la transaction va être générée automatique avec l'appel de la procédure AjoutTransactionOccas()
648-- -----------------------------------------------------------------------------------------------
649DROP PROCEDURE IF EXISTS SortieOccasionnel;
650
651DELIMITER $$
652CREATE PROCEDURE `SortieOccasionnel`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16))
653BEGIN
654 SET @occasCount = -1;
655 SELECT COUNT(*) INTO @occasCount FROM tblOccasionnel where idOccas = p_idOccas and dateFinOccas is not null;
656 IF (@occasCount = 0) THEN
657 -- appel de la procédure pour la génération de la transaction
658 call AjoutTransactionOccas(p_idOccas, p_idPaiement, p_numPaiement, @LIDTRANSACOCCAS);
659
660 UPDATE tblOccasionnel set dateFinOccas = now(), idTransaction = @LIDTRANSACOCCAS WHERE idOccas = p_idOccas;
661 ELSE
662 SELECT 'Cet occasionel a déjà quitté';
663 END IF;
664END$$
665DELIMITER ;
666
667-- --------------------------------------------------------------------------------
668-- NUMÉRO 1
669-- Procédure PlacesAbonnes() donne les places occupées par des abonnements à tout
670-- moment. Ce moment doit être déterminé par une date passé en paramètre.
671-- --------------------------------------------------------------------------------
672DROP PROCEDURE IF EXISTS PlacesAbonnes;
673
674DELIMITER $$
675CREATE PROCEDURE `PlacesAbonnes`(p_Moment Date)
676BEGIN
677 SELECT place.idPlace as 'Place #',
678 concat(voiture.marque, ': ', voiture.idVehicule) as 'Véhicule',
679 abon.dateDebutAbonne as 'Date de début', abon.dateFinAbonne as 'Date de fin', count(*) as 'Nombre'
680 FROM tblAbonnement as abon
681 INNER join tblVehicule as voiture
682 ON abon.idVehicule = voiture.idVehicule
683 INNER join tblPlace as place
684 ON abon.idPlace = place.idPlace
685 WHERE abon.dateDebutAbonne <= p_Moment AND abon.dateFinAbonne >= p_Moment
686 GROUP BY place.idPlace WITH ROLLUP;
687END$$
688DELIMITER ;
689
690#call PlacesAbonnes(curdate());
691
692-- -------------------------------------------------------------------------
693-- NUMÉRO 2
694-- Procédure CompterPlaceDispo(), va retourner le nombre de place disponible
695-- en tenant compte des places utilisés par les abonnés et occasionnels
696-- -------------------------------------------------------------------------
697DROP PROCEDURE IF EXISTS CompterPlaceDispo;
698
699DELIMITER $$
700CREATE PROCEDURE `CompterPlaceDispo`()
701BEGIN
702 DECLARE v_nbOccas int;
703 DECLARE v_nbAbon int;
704 DECLARE v_nbTotal int;
705
706 SET v_nbOccas = FnNbPlaceOccas();
707 SET v_nbAbon = FnNbPlaceAbonne();
708 SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
709 SELECT v_nbTotal as 'Stationnement disponible';
710END$$
711DELIMITER ;
712
713#call CompterPlaceDispo();
714
715-- ---------------------------------------------------------------------------
716-- NUMÉRO 3
717-- Procédure DepotQuotidien(), va retourner les achats par type de paiement et
718-- par type d'utilisateur
719-- ---------------------------------------------------------------------------
720DROP PROCEDURE IF EXISTS DepotQuotidien;
721DROP TEMPORARY TABLE IF EXISTS TMP_depot;
722
723DELIMITER $$
724CREATE PROCEDURE `DepotQuotidien`(p_DateVerif Date)
725BEGIN
726 DECLARE v_CrOcca int;
727 DECLARE v_CrAbon int;
728 DECLARE v_CoOcca int;
729 DECLARE v_CoAbon int;
730 set v_CrOcca = FnCreditOcca(p_DateVerif);
731 set v_CrAbon = FnCreditAbonne(p_DateVerif);
732 set v_CoOcca = FnComptantOcca(p_DateVerif);
733 set v_CoAbon = FnComptantAbonne(p_DateVerif);
734
735
736 CREATE TEMPORARY TABLE TMP_depot (
737 type_Depot varchar(25),
738 Abonnements DECIMAL(9,2),
739 Occasionnels DECIMAL(9,2));
740
741 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
742 VALUE ('Carte de crédit', v_CrAbon, v_CrOcca);
743 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
744 VALUE ('Argent comptant', v_CoAbon, v_CoOcca);
745 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
746 VALUE ('Total', (v_CoAbon + v_CrAbon), (v_CoOcca + v_CrOcca));
747
748 SELECT * From TMP_depot;
749
750 DROP TEMPORARY TABLE TMP_depot;
751
752END$$
753DELIMITER ;
754
755#call DepotQuotidien(curdate());
756
757-- --------------------------------------------------------------------------------
758-- NUMÉRO 4
759-- Procédure ventilation donne la ventilation dans un tableau des sommes
760-- perçues par type de personne (physique ou société), les véhicules occasionnels.
761-- --------------------------------------------------------------------------------
762DROP PROCEDURE IF EXISTS ventilation;
763DROP TEMPORARY TABLE IF EXISTS TMP_ventilation;
764
765DELIMITER $$
766CREATE PROCEDURE `ventilation`(p_DateDebut Date, p_DateFin Date)
767BEGIN
768 DECLARE v_NnOcca int;
769 DECLARE v_NbAbonSoc int;
770 DECLARE v_NbAbonPers int;
771 DECLARE v_SommeOcca DEC(9,2);
772 DECLARE v_SommeAbonSoc DEC(9,2);
773 DECLARE v_SommeAbonPers DEC(9,2);
774
775 SET v_NnOcca = FnNbOcca(p_DateDebut, p_DateFin);
776 SET v_NbAbonSoc = FnNbAbon(p_DateDebut, p_DateFin, 's');
777 SET v_NbAbonPers = FnNbAbon(p_DateDebut, p_DateFin, 'p');
778 SET v_SommeOcca = FnSommeOcca(p_DateDebut, p_DateFin);
779 SET v_SommeAbonSoc = FnSommeAbon(p_DateDebut, p_DateFin, 's');
780 SET v_SommeAbonPers = FnSommeAbon(p_DateDebut, p_DateFin, 'p');
781
782 CREATE TEMPORARY TABLE TMP_ventilation (
783 TypeDeClient varchar(25),
784 NbAbonnements INT,
785 SommeAbonnements DECIMAL(9,2),
786 NbOccasionnels INT,
787 SommeOccasionnels DECIMAL(9,2));
788
789 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
790 VALUE ('Personne', v_NbAbonPers, v_SommeAbonPers, v_NnOcca, v_SommeOcca);
791 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
792 VALUE ('Société', v_NbAbonSoc, v_SommeAbonSoc, null, null);
793 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
794 VALUE ('Total', (v_NbAbonPers+v_NbAbonSoc), (v_SommeAbonPers+v_SommeAbonSoc), v_NnOcca, v_SommeOcca);
795
796 SELECT TypeDeClient as '', NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels From TMP_ventilation;
797
798 DROP TEMPORARY TABLE TMP_ventilation;
799
800END$$
801DELIMITER ;
802
803#call ventilation(curdate()-2, curdate());
804
805###############################
806### INSERTIONS DES DONNÉÉES ###
807###############################
808
809SET FOREIGN_KEY_CHECKS = 1;
810
811use TP3;
812
813-- appele de la procédure pour faire l'insertion des places
814call InsertPlaces(500, 'plein air');
815call InsertPlaces(2000, 'couvert');
816
817-- insertion manuelle, car pas besoin de beaucoup de ville
818INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
819
820-- insertion manuelle avec des données produite par generatedata
821INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
822 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");
823INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
824 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");
825INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
826 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");
827INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
828 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");
829INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
830 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");
831
832-- Insertion des véhicules avec une fonction qui génère de façon aléatoire les plaques, donc empêche un doublons
833INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Lexus","Multi-Segment","violet"),(FnRandomPlaque(),"Fiat","Pickup","grey"),(FnRandomPlaque(),"Lexus","Utilitaire","black"),(FnRandomPlaque(),"Ford","VUS","yellow"),(FnRandomPlaque(),"BMW","Utilitaire","blue"),(FnRandomPlaque(),"Acura","Utilitaire","blue"),(FnRandomPlaque(),"Lexus","Berline","red"),(FnRandomPlaque(),"Dacia","Multi-Segment","violet"),(FnRandomPlaque(),"Peugeot","Multi-Segment","violet"),(FnRandomPlaque(),"General Motors","Berline","blue");
834INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Kia Motors","Hatchback","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Hatchback","yellow"),(FnRandomPlaque(),"Audi","Berline","green"),(FnRandomPlaque(),"Dacia","Hatchback","black"),(FnRandomPlaque(),"Seat","Pickup","indigo"),(FnRandomPlaque(),"Audi","Sport","orange"),(FnRandomPlaque(),"Mitsubishi Motors","Sport","orange"),(FnRandomPlaque(),"Kia Motors","Hatchback","blue"),(FnRandomPlaque(),"Subaru","Hatchback","grey"),(FnRandomPlaque(),"Mahindra and Mahindra","Berline","indigo");
835INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Daihatsu","Utilitaire","indigo"),(FnRandomPlaque(),"Mercedes-Benz","Hatchback","grey"),(FnRandomPlaque(),"Infiniti","Pickup","blue"),(FnRandomPlaque(),"BMW","Pickup","indigo"),(FnRandomPlaque(),"Chrysler","Pickup","blue"),(FnRandomPlaque(),"Lincoln","Utilitaire","grey"),(FnRandomPlaque(),"Suzuki","Berline","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Hatchback","blue"),(FnRandomPlaque(),"BMW","Multi-Segment","blue"),(FnRandomPlaque(),"Dongfeng Motor","Pickup","orange");
836INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Nissan","VUS","violet"),(FnRandomPlaque(),"Subaru","Berline","yellow"),(FnRandomPlaque(),"Daimler","Berline","indigo"),(FnRandomPlaque(),"Daihatsu","Pickup","red"),(FnRandomPlaque(),"Honda","Multi-Segment","violet"),(FnRandomPlaque(),"BMW","Hatchback","yellow"),(FnRandomPlaque(),"Mazda","Pickup","violet"),(FnRandomPlaque(),"Kia Motors","Sport","green"),(FnRandomPlaque(),"Skoda","Sport","violet"),(FnRandomPlaque(),"Skoda","VUS","orange");
837INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Daimler","Berline","blue"),(FnRandomPlaque(),"Toyota","Utilitaire","grey"),(FnRandomPlaque(),"Renault","Multi-Segment","blue"),(FnRandomPlaque(),"Cadillac","Hatchback","red"),(FnRandomPlaque(),"Dodge","Pickup","red"),(FnRandomPlaque(),"Lexus","Berline","red"),(FnRandomPlaque(),"RAM Trucks","Multi-Segment","yellow"),(FnRandomPlaque(),"Daimler","Berline","orange"),(FnRandomPlaque(),"Chevrolet","Berline","green"),(FnRandomPlaque(),"Subaru","Sport","violet");
838INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Renault","Berline","blue"),(FnRandomPlaque(),"Mazda","Hatchback","indigo"),(FnRandomPlaque(),"Fiat","Utilitaire","indigo"),(FnRandomPlaque(),"Chrysler","Sport","blue"),(FnRandomPlaque(),"Mitsubishi Motors","VUS","violet"),(FnRandomPlaque(),"Lexus","Hatchback","grey"),(FnRandomPlaque(),"Chevrolet","VUS","indigo"),(FnRandomPlaque(),"Skoda","Hatchback","red"),(FnRandomPlaque(),"GMC","Utilitaire","orange"),(FnRandomPlaque(),"Subaru","VUS","yellow");
839INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Vauxhall","Sport","grey"),(FnRandomPlaque(),"Jeep","Pickup","yellow"),(FnRandomPlaque(),"Kenworth","VUS","grey"),(FnRandomPlaque(),"Chevrolet","Pickup","black"),(FnRandomPlaque(),"Isuzu","VUS","blue"),(FnRandomPlaque(),"JLR","Multi-Segment","violet"),(FnRandomPlaque(),"Dacia","VUS","indigo"),(FnRandomPlaque(),"Smart","Sport","blue"),(FnRandomPlaque(),"Lexus","Pickup","yellow"),(FnRandomPlaque(),"Mitsubishi Motors","Utilitaire","red");
840INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"GMC","Hatchback","red"),(FnRandomPlaque(),"Cadillac","Multi-Segment","green"),(FnRandomPlaque(),"Kia Motors","Pickup","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Pickup","blue"),(FnRandomPlaque(),"Citroën","VUS","indigo"),(FnRandomPlaque(),"Vauxhall","Hatchback","green"),(FnRandomPlaque(),"Fiat","Pickup","violet"),(FnRandomPlaque(),"Ferrari","Berline","black"),(FnRandomPlaque(),"Smart","Multi-Segment","indigo"),(FnRandomPlaque(),"Volkswagen","Utilitaire","grey");
841INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Volvo","Multi-Segment","orange"),(FnRandomPlaque(),"Hyundai Motors","Berline","black"),(FnRandomPlaque(),"Hyundai Motors","Multi-Segment","green"),(FnRandomPlaque(),"GMC","Utilitaire","orange"),(FnRandomPlaque(),"General Motors","Utilitaire","yellow"),(FnRandomPlaque(),"BMW","Sport","grey"),(FnRandomPlaque(),"Peugeot","Sport","black"),(FnRandomPlaque(),"Seat","Multi-Segment","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Berline","red"),(FnRandomPlaque(),"Lincoln","Pickup","black");
842INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Toyota","Pickup","grey"),(FnRandomPlaque(),"Lincoln","Berline","orange"),(FnRandomPlaque(),"Seat","Utilitaire","yellow"),(FnRandomPlaque(),"Lexus","Multi-Segment","red"),(FnRandomPlaque(),"Mahindra and Mahindra","VUS","grey"),(FnRandomPlaque(),"Subaru","Berline","blue"),(FnRandomPlaque(),"Daimler","Hatchback","indigo"),(FnRandomPlaque(),"MINI","Sport","yellow"),(FnRandomPlaque(),"Peugeot","Berline","yellow"),(FnRandomPlaque(),"Smart","Berline","yellow");
843
844select count(*) from tblVehicule;
845
846-- insertion manuelle, car pas assé de donnée à générer
847INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
848
849-- --------------------------------------------------------------------
850-- Procédure pour ajouter des occasionnels en lot selon une qte donnée
851-- --------------------------------------------------------------------
852DROP PROCEDURE IF EXISTS AjoutOccasEnLot;
853DELIMITER $$
854CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
855BEGIN
856 DECLARE v_i int DEFAULT 1;
857 WHILE v_i <= p_nb DO
858 call AjoutOccasionnel();
859 SET v_i = v_i + 1;
860 END WHILE;
861END $$
862DELIMITER ;
863
864-- Appel de la procédure
865call AjoutOccasEnLot(150);
866
867-- -----------------------------------------------------------------------------------------
868-- Procédure pour ajouter des abonnements en lot selon une qte donnée avec un mois aléatoire
869-- sur la date de début entre aujourd'hui et 12 mois dans le passé
870-- -----------------------------------------------------------------------------------------
871DROP PROCEDURE IF EXISTS AjoutAbonEnLot;
872DELIMITER $$
873CREATE PROCEDURE `AjoutAbonEnLot`(in p_nb int)
874BEGIN
875 DECLARE v_i int DEFAULT 1;
876 DECLARE v_alea int;
877 DECLARE v_date date;
878 DECLARE v_idAbon int;
879 DECLARE v_nbDuree int;
880 DECLARE v_idPaiement int;
881 DECLARE v_idVehicule varchar(6);
882 DECLARE v_idPlace int;
883 WHILE v_i <= p_nb DO
884 SET v_alea = (SELECT FLOOR(1 + (RAND() * 12)));
885 SET v_date = DATE_SUB(CURDATE(), INTERVAL v_alea MONTH);
886 SET v_idAbon = FLOOR(RAND()*(50-1+1))+1;
887 SET v_nbDuree = FLOOR(RAND()*(12-1+1))+1;
888 SET v_idPaiement = FLOOR(RAND()*(5-1+1))+1;
889 SET v_idVehicule = (SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1);
890 SET v_idPlace = FLOOR(RAND()*(2500-1+1))+1;
891 IF (v_idPaiement = 4) THEN
892 call AjoutAbonnement(v_date,v_nbDuree,v_idAbon,v_idPaiement,null,v_idVehicule,v_idPlace);
893 ELSE
894 call AjoutAbonnement(v_date,v_nbDuree,v_idAbon,v_idPaiement,'4000000000000002',v_idVehicule,v_idPlace);
895 END IF;
896 SET v_i = v_i + 1;
897 END WHILE;
898END $$
899DELIMITER ;
900
901-- Appel de la procédure, le nombre d'abonnement peut varier entre 1 et le nombre demandé, car il y a une validation pour les doublons.
902call AjoutAbonEnLot(100);
903
904############################
905########## TESTS ###########
906############################
907
908-- véfification du nombre d'Abonnement et d'ocassionel encore valide avant de commencer
909SELECT COUNT(*) as `Nombre d'abonnement` from tblAbonnement where dateFinAbonne >= curdate();
910SELECT COUNT(*) as `Nombre d'occasionel` from tblOccasionnel where dateFinOccas is null;
911-- appel de la procédure qui sort le nombre de place disponible, elle utilise les 2 fonctions pour sortir le nombre d'abonnement et occasionel
912-- et soustrait le nombre total de place du stationnement
913call CompterPlaceDispo();
914-- appel de la procédure pour rajouter des occasionnels
915call AjoutOccasEnLot(10);
916-- appel de nouveau la procédure CompterPlaceDispo pour valider que le nombre de place a diminuer de 10
917call CompterPlaceDispo();
918-- appel de la procédure pour ajouter des abonnements
919call AjoutAbonEnLot(10);
920-- appel de nouveau la procédure CompterPlaceDispo pour valider que le nombre de place a diminuer de 10
921call CompterPlaceDispo();
922-- appel de la procédure SortieOccasionnel
923call SortieOccasionnel('20180424151', 1, '1111111111111111');
924-- appel de nouveau la procédure CompterPlaceDispo pour valider que le nombre de place a augmenter de 1
925call CompterPlaceDispo();