· 8 years ago · Apr 18, 2018, 04:40 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-- -----------------------------------------------------
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(6,2)
224BEGIN
225 DECLARE v_Montant DECIMAL(6,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 set v_Montant = 0;
235 end if;
236RETURN v_Montant;
237END $$
238DELIMITER ;
239
240select FnCreditAbonne(curdate()) as 'Nombre de vente par crédit';
241
242-- ---------------------------------------------------------------------------------------
243-- Fonction FnComptantAbonne() qui retourne le nombre de paiement Abonnement fait comptant
244-- ---------------------------------------------------------------------------------------
245DELIMITER $$
246CREATE FUNCTION FnComptantAbonne(p_dateTrans DATE)
247RETURNS DECIMAL(6,2)
248BEGIN
249 DECLARE v_Montant DECIMAL(6,2);
250 SELECT SUM(trans.montant)
251 FROM tblTransaction as trans
252 INNER JOIN tblMethodPaiement as methode
253 ON trans.idPaiement = methode.idPaiement
254 INNER JOIN tblAbonnement as abon
255 ON trans.idAbonnement = abon.idAbonnement
256 WHERE methode.typePaiement in ('A','C') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
257 INTO v_Montant;
258 if (v_Montant is null) then set v_Montant = 0;
259 end if;
260RETURN v_Montant;
261END $$
262DELIMITER ;
263
264select FnComptantAbonne(curdate()) as 'Nombre de vente comptant/chèque';
265
266
267 -- ---------------------------------------------------------------------------------------
268-- Fonction FnCreditOcca() qui retourne le nombre de paiement Occasionnel fait par crédit
269-- ---------------------------------------------------------------------------------------
270DELIMITER $$
271CREATE FUNCTION FnCreditOcca(p_dateTrans DATE)
272RETURNS DECIMAL(6,2)
273BEGIN
274 DECLARE v_Montant DECIMAL(6,2);
275 SELECT SUM(trans.montant)
276 FROM tblTransaction as trans
277 INNER JOIN tblMethodPaiement as methode
278 ON trans.idPaiement = methode.idPaiement
279 INNER JOIN tblOccasionnel as occa
280 ON trans.idOccas = occa.idOccas
281 WHERE methode.typePaiement in ('V','M','X') AND idAbonnement is null AND occa.dateFinOccas = p_dateTrans
282 INTO v_Montant;
283 if (v_Montant is null) then set v_Montant = 0;
284 end if;
285
286RETURN v_Montant;
287END $$
288DELIMITER ;
289
290select FnCreditOcca(curdate()) as 'Nombre de vente par crédit';
291
292-- ---------------------------------------------------------------------------------------
293-- Fonction FnComptantOcca() qui retourne le nombre de paiement Occasionnel fait comptant
294-- ---------------------------------------------------------------------------------------
295DELIMITER $$
296CREATE FUNCTION FnComptantOcca(p_dateTrans DATE)
297RETURNS DECIMAL(6,2)
298BEGIN
299 DECLARE v_Montant DECIMAL(6,2);
300 SELECT SUM(trans.montant)
301 FROM tblTransaction as trans
302 INNER JOIN tblMethodPaiement as methode
303 ON trans.idPaiement = methode.idPaiement
304 INNER JOIN tblOccasionnel as occa
305 ON trans.idOccas = occa.idOccas
306 WHERE methode.typePaiement in ('A','C') AND idAbonnement is null AND occa.dateFinOccas = p_dateTrans
307 INTO v_Montant;
308 if (v_Montant is null) then set v_Montant = 0;
309 end if;
310RETURN v_Montant;
311END $$
312DELIMITER ;
313
314select FnComptantOcca(curdate()) as 'Nombre de vente comptant/chèque';
315
316-- ------------------------------------------------------------
317-- Fonction pour générer des plaques de voiture
318-- ------------------------------------------------------------
319DELIMITER $$
320CREATE FUNCTION randomPlaque()
321 RETURNS VARCHAR(6)
322BEGIN
323 DECLARE plaque VARCHAR(6) DEFAULT "";
324 SELECT concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
325 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
326 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
327 substring('0123456789', rand()*9+1, 1),
328 substring('0123456789', rand()*9+1, 1),
329 substring('0123456789', rand()*9+1, 1))
330 into @idVehicule;
331
332 SET @rcount = -1;
333 SELECT COUNT(*) INTO @rcount FROM `tblVehicule` WHERE `idVehicule` = @idVehicule ;
334
335 IF @rcount = 0 THEN
336 SET plaque = @idVehicule ;
337 END IF ;
338
339 RETURN plaque ;
340END$$
341DELIMITER ;
342
343show create table tblAbonnement;
344show create table tblVehicule;
345select randomPlaque();
346
347-- -----------------------------------------------------
348-- Procédures
349-- ------------------------------------------------------------------------------
350-- Procédure pour la création des places dans tblPlace selon le nombre et le type
351-- ------------------------------------------------------------------------------
352DELIMITER $$
353CREATE PROCEDURE `InsertPlaces`(in p_nb int, in p_type varchar(25))
354BEGIN
355 DECLARE v_i int DEFAULT 1;
356
357 WHILE v_i <= p_nb DO
358 INSERT INTO tblPlace (typePlace,idAbonne) VALUES(p_type, null);
359 SET v_i = v_i + 1;
360 END WHILE;
361END $$
362DELIMITER ;
363
364-- -------------------------------------------------------------------------
365-- Procédure CompterPlaceDispo(), va retourner le nombre de place disponible
366-- en tenant compte des places utilisés par les abonnés et occasionnels
367-- -------------------------------------------------------------------------
368DELIMITER $$
369CREATE PROCEDURE `CompterPlaceDispo`()
370BEGIN
371 DECLARE v_nbOccas int;
372 DECLARE v_nbAbon int;
373 DECLARE v_nbTotal int;
374
375 SET v_nbOccas = FnNbPlaceOccas();
376 SET v_nbAbon = FnNbPlaceAbonne();
377 SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
378 SELECT v_nbTotal as 'Stationnement disponible';
379END$$
380DELIMITER ;
381
382call CompterPlaceDispo();
383-- -----------------------------------------------------------------------------------
384-- Procédure SommesPercuesParType(), va calculer les montants percus par type d'usager
385-- en se basant sur des dates fournis
386-- -----------------------------------------------------------------------------------
387DELIMITER $$
388CREATE PROCEDURE `SommesPercuesParType`(IN p_dateDebut Date, IN p_dateFin Date)
389BEGIN
390 DECLARE v_dateDebut date;
391 DECLARE v_dateFin date;
392 DECLARE v_type varchar(8);
393 DECLARE v_nbAbon int;
394 DECLARE v_nbOccas int;
395 DECLARE v_somme decimal(9,2);
396 DECLARE v_total int;
397
398 SET v_dateDebut = p_dateDebut;
399 SET v_dateFin = p_dateFin;
400
401 SELECT * FROM tblAbonne as ab
402 INNER JOIN tblAbonnement as abt
403 ON ab.idAbonne = abt.idAbonne
404 WHERE abt.dateDebutAbonne >= v_dateDebut and abt.dateFinAbonne <= v_dateFin;
405END$$
406DELIMITER ;
407
408call SommesPercuesParType('2018-01-01', curdate());
409-- -----------------------------------------------------
410-- Procédure AjoutAbonne(), va faire l'ajout d'un abonné
411-- -----------------------------------------------------
412DELIMITER $$
413CREATE PROCEDURE `AjoutAbonne`(in p_nomAbonne varchar(50), in p_prenomAbonne varchar(25), in p_codePostal varchar(7),
414 in p_telephone varchar(15), in p_courriel varchar(50), in p_typeAbonne char(1), in p_idVille int)
415BEGIN
416 DECLARE v_nomAbonne varchar(50);
417 DECLARE v_prenomAbonne varchar(25);
418 DECLARE v_codePostal varchar(7);
419 DECLARE v_telephone varchar(15);
420 DECLARE v_courriel varchar(50);
421 DECLARE v_typeAbonne char(1);
422 DECLARE v_idVille int;
423
424 SET v_nomAbonne = p_nomAbonne;
425 SET v_prenomAbonne = p_prenomAbonne;
426 SET v_codePostal = p_codePostal;
427 SET v_telephone = p_telephone;
428 SET v_courriel = p_courriel;
429 SET v_typeAbonne = p_typeAbonne;
430 SET v_idVille = p_idVille;
431
432 INSERT INTO tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille)
433 VALUES(v_nomAbonne,v_prenomAbonne,v_codePostal,v_telephone,v_courriel,v_typeAbonne,v_idVille);
434
435END$$
436DELIMITER ;
437
438select * from tblAbonne;
439
440-- -----------------------------------------------------------------------------------------------------------------------
441-- Procédure AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement et retourner l'id de celle-ci
442-- -----------------------------------------------------------------------------------------------------------------------
443DELIMITER $$
444CREATE PROCEDURE `AjoutTransactionAbonne`(in p_duree int, in p_numPaiement varchar(16), in p_idPaiement int, out LIDTRANSACABON int)
445BEGIN
446 DECLARE v_tMois int DEFAULT 240;
447 DECLARE v_tAn int DEFAULT 2500;
448 DECLARE v_mTotal int;
449 DECLARE v_montant decimal(6,2);
450 IF (p_duree > 11) THEN
451 SET v_mTotal = CEIL(p_duree / 12) * v_tAn;
452 ELSE
453 SET v_mTotal = p_duree * v_tMois;
454 END IF;
455
456 SET v_montant = v_mTotal;
457
458 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
459 VALUES(v_montant,p_numPaiement,p_idPaiement,null,null);
460
461 SET LIDTRANSACABON = LAST_INSERT_ID();
462END$$
463DELIMITER ;
464
465#call AjoutTransactionAbonne(2,null,4);
466#select * from tblMethodPaiement;
467
468-- --------------------------------------------------
469-- Procédure AjoutAbonnement(), va créer l'abonnement
470-- --------------------------------------------------
471DELIMITER $$
472CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_duree int, in p_idAbonne int, in p_idPaiement int, in p_numPaiement varchar(16)
473 , in p_idVehicule varchar(7), in p_idPlace smallint)
474BEGIN
475 DECLARE v_idTransac int;
476 DECLARE v_dateFin date;
477
478 SET v_dateFin = DATE_ADD(p_dateDebut, INTERVAL p_duree MONTH);
479 -- Appele la procedure pour générer la transaction de l'abonnement
480 call AjoutTransactionAbonne(p_duree, p_numPaiement, p_idPaiement, @LIDTRANSACABON);
481
482 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
483 VALUES(p_dateDebut,v_dateFin,p_idAbonne,p_idVehicule,p_idPlace,@LIDTRANSACABON);
484
485 UPDATE tblTransaction set idAbonnement = LAST_INSERT_ID() where idTransaction = @LIDTRANSACABON;
486
487END$$
488DELIMITER ;
489
490#SET FOREIGN_KEY_CHECKS = 1;
491
492select * from tblAbonne;
493SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1;
494select * from tblVehicule;
495select * from tblAbonnement;
496show create table tblAbonnement;
497select * from tblTransaction;
498#select @LIDABONNEMENT;
499
500-- ----------------------------------------------------------------------------------------
501-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour l'action d'occasionnel
502-- ----------------------------------------------------------------------------------------
503DELIMITER $$
504CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas int, in p_idPaiement int, in p_numPaiement varchar(16), out LIDTRANSACOCCAS int)
505BEGIN
506 DECLARE v_montant decimal(6,2);
507 DECLARE v_dateDebut datetime;
508
509 SELECT dateDebutOccas FROM tblOccasionnel AS occas
510 INNER JOIN tblTransaction as trans
511 ON occas.idOccas = trans.idOccas
512 WHERE occas.idOccas = p_idOccas
513 INTO @v_dateDebut;
514
515 SET v_montant = FnCalculeMontant(@v_dateDebut, now());
516
517 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
518 VALUES(v_montant,p_numPaiement,p_idPaiement,null,p_idOccas);
519
520 SET LIDTRANSACOCCAS = LAST_INSERT_ID();
521
522END$$
523DELIMITER ;
524
525SELECT dateDebutOccas FROM tblOccasionnel AS occas
526 INNER JOIN tblTransaction as trans
527 ON occas.idOccas = trans.idOccas
528 WHERE occas.idOccas = 201804181
529 INTO @v_dateDebut;
530
531select FnCalculeMontant(@v_dateDebut, now());
532-- ---------------------------------------------------------------
533-- Procédure AjoutOccasionnel(), va créer l'ajout d'un occasionnel
534-- ---------------------------------------------------------------
535DELIMITER $$
536CREATE PROCEDURE `AjoutOccasionnel`()
537BEGIN
538
539 DECLARE v_nbIdTotal int DEFAULT 0;
540 DECLARE v_nbIdAvant int DEFAULT 0;
541 DECLARE v_idOccas varchar(12);
542
543 SELECT count(*) into v_nbIdTotal
544 FROM tblOccasionnel;
545 SELECT count(*) into v_nbIdAvant
546 FROM tblOccasionnel WHERE TIMESTAMPDIFF(DAY, dateDebutOccas, now() != 0);
547
548 SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);
549
550 INSERT INTO tblOccasionnel VALUES(v_idOccas,now(),null,null);
551
552END$$
553DELIMITER ;
554
555-- --------------------------------------------------------------------------------------
556-- Procédure SortieOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
557-- --------------------------------------------------------------------------------------
558DELIMITER $$
559CREATE PROCEDURE `SortieOccasionnel`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16))
560BEGIN
561 call AjoutTransactionOccas(p_idOccas, p_idPaiement, p_numPaiement, @LIDTRANSACOCCAS);
562
563 UPDATE tblOccasionnel set dateFinOccas = now() AND idTransaction = @LIDTRANSACOCCAS
564 WHERE idOccas = p_idOccas;
565 #call CompterPlaceDispo();
566END$$
567DELIMITER ;
568
569select * from tblOccasionnel;
570call SorieOccasionnel('201804182', 1, '4000000000000002');
571select * from tblTransaction order by idTransaction desc limit 1;
572
573-- -------------------------------------------------------------------------
574-- Procédure DepotQuotidien(), va retourner les achats par type de paiement et
575-- par type d'utilisateur
576-- -------------------------------------------------------------------------
577drop procedure if exists DepotQuotidien;
578DROP TEMPORARY TABLE if exists TMP_depot;
579
580DELIMITER $$
581CREATE PROCEDURE `DepotQuotidien`(p_DateVerif Date)
582BEGIN
583
584 DECLARE v_CrOcca int;
585 DECLARE v_CrAbon int;
586 DECLARE v_CoOcca int;
587 DECLARE v_CoAbon int;
588 set v_CrOcca = FnCreditOcca(p_DateVerif);
589 set v_CrAbon = FnCreditAbonne(p_DateVerif);
590 set v_CoOcca = FnComptantOcca(p_DateVerif);
591 set v_CoAbon = FnComptantAbonne(p_DateVerif);
592
593
594 CREATE TEMPORARY TABLE TMP_depot (
595 type_Depot varchar(25),
596 Abonnements DECIMAL(6,2),
597 Occasionnels DECIMAL(6,2));
598
599 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
600 VALUE ('Carte de crédit', v_CrAbon, v_CrOcca);
601 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
602 VALUE ('Argent comptant', v_CoAbon, v_CoOcca);
603 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
604 VALUE ('Total', (v_CoAbon + v_CrAbon), (v_CoOcca + v_CrOcca));
605
606 SELECT * From TMP_depot;
607
608 DROP TEMPORARY TABLE TMP_depot;
609
610END$$
611DELIMITER ;
612
613call DepotQuotidien(curdate());
614#call DepotQuotidien('2018-02-02');
615#SELECT SUM(FnCreditOcca(curdate()) + FnComptantOcca(curdate()));
616
617###############################
618### TRIGGER #################
619###############################
620#DELIMITER $$
621
622#CREATE TRIGGER Check_Vehicle_beforeInsert
623# BEFORE INSERT ON `tblAbonnement`
624# FOR EACH ROW
625# BEGIN
626# SET @idVehicule = 1;
627# WHILE (@idVehicule IS NOT NULL) DO
628# SET NEW.idVehicule = RAND(6);
629# SET @idVehicule = (SELECT idVehicule FROM `tblAbonnement` WHERE `idVehicule` = NEW.idVehicule);
630# END WHILE;
631# END;$$
632#DELIMITER ;
633
634###############################
635### INSERTIONS DES DONNÉÉES ###
636###############################
637
638SET FOREIGN_KEY_CHECKS = 1;
639
640use TP3;
641
642-- appele de la procédure pour faire l'insertion des places
643call InsertPlaces(500, 'plein air');
644call InsertPlaces(2000, 'couvert');
645
646-- insertion manuelle, car pas besoin de beaucoup de ville
647INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
648
649-- insertion manuelle avec des données produite par generatedata
650INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
651 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");
652INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
653 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");
654INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
655 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");
656INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
657 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");
658INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
659 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");
660
661-- insertion manuelle, car compliquer de le faire avec generatedata
662INSERT INTO tblVehicule VALUES(randomPlaque(), "Honda", "Civic", "Bleu"),(randomPlaque(), "Mazda", "Protégé", "Gris"),(randomPlaque(), "Pontiac", "Sundance", "Gris")
663 ,(randomPlaque(), "Chevrolet", "Cavalier", "Orange"),(randomPlaque(), "Pontiac", "Sunfire", "Rose"),(randomPlaque(), "Ford", "Escort", "Bleu")
664 ,(randomPlaque(), "Suzuki", "Swift", "Vert"),(randomPlaque(), "Volskwagen", "Passat", "Jaune"),(randomPlaque(), "Honda", "Accord", "Noir")
665 ,(randomPlaque(), "Suzuki", "Grand Vitara", "Bleu");
666
667select * from tblVehicule;
668-- insertion manuelle, car pas assé de donnée à générer
669INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
670
671-- Procédure pour ajouter des occasionnels en lot selon une qte donné
672-- ------------------------------------------------------------------
673DELIMITER $$
674CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
675BEGIN
676 DECLARE v_i int DEFAULT 1;
677 WHILE v_i <= p_nb DO
678 call AjoutOccasionnel();
679 SET v_i = v_i + 1;
680 END WHILE;
681END $$
682DELIMITER ;
683
684call AjoutOccasEnLot(50);
685
686-- Procédure pour ajouter des Abonnement en lot selon une qte donné
687-- -----------------------------------------------------------------
688DELIMITER $$
689CREATE PROCEDURE `AjoutAbonEnLot`(in p_nb int)
690BEGIN
691 DECLARE v_i int DEFAULT 1;
692 DECLARE v_idAbon int;
693 DECLARE v_nbDuree int;
694 DECLARE v_idPaiement int;
695 DECLARE v_idVehicule varchar(6);
696 DECLARE v_idPlace int;
697 WHILE v_i <= p_nb DO
698 SET v_idAbon = FLOOR(RAND()*(50-1+1))+1;
699 SET v_nbDuree = FLOOR(RAND()*(12-1+1))+1;
700 SET v_idPaiement = FLOOR(RAND()*(5-1+1))+1;
701 SET v_idVehicule = (SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1);
702 SET v_idPlace = FLOOR(RAND()*(2500-1+1))+1;
703 IF (v_idPaiement = 4) THEN
704 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,null,v_idVehicule,v_idPlace);
705 ELSE
706 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,'4000000000000002',v_idVehicule,v_idPlace);
707 END IF;
708 SET v_i = v_i + 1;
709 END WHILE;
710END $$
711DELIMITER ;
712
713USE TP3;
714
715call AjoutAbonEnLot(10);
716SELECT idVehicule FROM tblVehicule;
717select * from tblAbonnement;
718delete from tblAbonnement where idAbonnement = 1;
719select * from tblTransaction;
720SELECT FLOOR(RAND()*(50-1+1))+1;
721select * from tblMethodPaiement;
722select count(*) from tblVehicule;
723show create table tblVehicule;
724SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1;
725select FnNbPlaceAbonne() as 'Nombre d\'abonnement';
726select FnNbPlaceOccas() as 'Nombre d\'occasionnel';
727#call CompterPlaceDispo();
728select curdate();
729SELECT DATE_FORMAT(curdate(), '%Y%m%d');
730#call AjoutAbonnement(curdate(), 2, 3, 4, null, 'ZYW467', 3);
731select * from tblAbonnement where idAbonnement = 16;
732select * from tblTransaction where idTransaction = 20;