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