· 8 years ago · Apr 16, 2018, 07:22 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_Date 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.idAbonne = abon.idAbonne
232 WHERE methode.typePaiement in ('V','M','X') AND idOccas is null AND abon.dateDebutAbonne = p_Date
233 INTO v_Montant;
234RETURN v_Montant;
235END $$
236DELIMITER ;
237
238#select FnCreditAbonne() 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.idAbonne = abon.idAbonne
254 WHERE methode.typePaiement in ('A','C') AND idOccas is null AND abon.dateDebutAbonne = p_Date
255 INTO v_Montant;
256RETURN v_Montant;
257END $$
258DELIMITER ;
259
260#select FnComptantAbonne() 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_Date 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.idAbonne = occa.idAbonne
277 WHERE methode.typePaiement in ('V','M','X') AND idAbonne is null AND occa.dateFinOccas = p_Date
278 INTO v_Montant;
279RETURN v_Montant;
280END $$
281DELIMITER ;
282
283#select FnCreditOcca() 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.idAbonne = occa.idAbonne
299 WHERE methode.typePaiement in ('A','C') AND idAbonne is null AND occa.dateFinOccas = p_Date
300 INTO v_Montant;
301RETURN v_Montant;
302END $$
303DELIMITER ;
304
305#select FnComptantOcca() 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
373#call 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
399#call 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
429-- -----------------------------------------------------------------------------------------------------------------------
430-- Procédure AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement et retourner l'id de celle-ci
431-- -----------------------------------------------------------------------------------------------------------------------
432DELIMITER $$
433CREATE PROCEDURE `AjoutTransactionAbonne`(in p_duree int, in p_numPaiement varchar(16), in p_idPaiement int, out LIDTRANSACABON int)
434BEGIN
435 DECLARE v_tMois int DEFAULT 240;
436 DECLARE v_tAn int DEFAULT 2500;
437 DECLARE v_mTotal int;
438 DECLARE v_montant decimal(6,2);
439 IF (p_duree > 12) THEN
440 SET v_mTotal = CEIL(p_duree / 12) * v_tAn;
441 ELSE
442 SET v_mTotal = p_duree * v_tMois;
443 END IF;
444
445 SET v_montant = v_mTotal;
446
447 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
448 VALUES(v_montant,p_numPaiement,p_idPaiement,null,null);
449
450 SET LIDTRANSACABON = LAST_INSERT_ID();
451END$$
452DELIMITER ;
453
454#call AjoutTransactionAbonne(2,null,4);
455#select * from tblMethodPaiement;
456
457-- ----------------------------------------------------------------------------------------
458-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour l'action d'occasionnel
459-- ----------------------------------------------------------------------------------------
460DELIMITER $$
461CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas int, in p_idPaiement int, in p_numPaiement int)
462BEGIN
463 #DECLARE v_idTransac int;
464 DECLARE v_montant decimal(6,2);
465 DECLARE v_numPaiement varchar(16);
466 DECLARE v_idPaiement int;
467 DECLARE v_idAbonne int;
468 DECLARE v_idOccas int;
469
470 SET v_montant = null;
471 SET v_numPaiement = p_numPaiement;
472 SET v_idPaiement = p_idPaiement;
473 SET v_idAbonne = null;
474 SET v_idOccas = p_idOccas;
475
476 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas)
477 VALUES(v_montant,v_numPaiement,v_idPaiement,v_idAbonne,v_idOccas);
478
479END$$
480DELIMITER ;
481
482-- --------------------------------------------------
483-- Procédure AjoutAbonnement(), va créer l'abonnement
484-- --------------------------------------------------
485DELIMITER $$
486CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_duree int, in p_idAbonne int, in p_idPaiement int, in p_numPaiement varchar(16)
487 , in p_idVehicule varchar(7), in p_idPlace smallint)
488BEGIN
489 DECLARE v_idTransac int;
490 DECLARE v_dateFin date;
491
492 SET v_dateFin = DATE_ADD(p_dateDebut, INTERVAL p_duree MONTH);
493 -- Appele la procedure pour générer la transaction de l'abonnement
494 call AjoutTransactionAbonne(p_duree, p_numPaiement, p_idPaiement, @LIDTRANSACABON);
495
496 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
497 VALUES(p_dateDebut,v_dateFin,p_idAbonne,p_idVehicule,p_idPlace,@LIDTRANSACABON);
498
499 UPDATE tblTransaction set idAbonnement = LAST_INSERT_ID() where idTransaction = @LIDTRANSACABON;
500
501END$$
502DELIMITER ;
503
504SET FOREIGN_KEY_CHECKS = 1;
505
506call AjoutTransactionAbonne(2,null,4);
507call AjoutAbonnement(curdate(), 2, 3, 4, null, 'ABC333', 3);
508#select * from tblAbonne;
509select * from tblAbonnement;
510select * from tblTransaction;
511select @LIDABONNEMENT;
512
513
514-- --------------------------------------------------------------------------------
515-- Procédure MajAbonnement(), va mettre à jour l'abonnement pour le rendre obselete
516-- --------------------------------------------------------------------------------
517DELIMITER $$
518CREATE PROCEDURE `MajAbonnement`()
519BEGIN
520END$$
521DELIMITER ;
522
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 DECLARE v_dateDebut datetime;
534 DECLARE v_dateFin datetime;
535 DECLARE v_idTransac int;
536
537 SELECT count(*) into v_nbIdTotal
538 FROM tblOccasionnel;
539 SELECT count(*) into v_nbIdAvant
540 FROM tblOccasionnel WHERE TIMESTAMPDIFF(DAY, dateDebutOccas, now() != 0);
541
542 SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);
543 #call AjoutTransactionOccas(v_idOccas);
544 SET v_dateDebut = now();
545 SET v_dateFin = null;
546 SET v_idTransac = null;
547
548 INSERT INTO tblOccasionnel VALUES(v_idOccas,v_dateDebut,v_dateFin,v_idTransac);
549
550END$$
551DELIMITER ;
552
553-- -------------------------------------------------------------------------
554-- MajOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
555-- -------------------------------------------------------------------------
556DELIMITER $$
557CREATE PROCEDURE `MajOccasionnel`(in p_idOccas int)
558BEGIN
559 DECLARE v_idOccas int;
560
561 SET v_idOccas = p_idOccas;
562 UPDATE tblOccasionnel set dateFinOccas = now()
563 WHERE idOccas = v_idOccas;
564
565 call CompterPlaceDispo();
566END$$
567DELIMITER ;
568
569###############################
570### TRIGGER #################
571###############################
572DELIMITER $$
573
574CREATE TRIGGER Check_Vehicle_beforeInsert
575 BEFORE INSERT ON `tblAbonnement`
576 FOR EACH ROW
577 BEGIN
578 SET @idVehicule = 1;
579 WHILE (@idVehicule IS NOT NULL) DO
580 SET NEW.idVehicule = RANDSTRING(6);
581 SET @idVehicule = (SELECT idVehicule FROM `tblAbonnement` WHERE `idVehicule` = NEW.idVehicule);
582 END WHILE;
583 END;$$
584DELIMITER ;
585
586###############################
587### INSERTIONS DES DONNÉÉES ###
588###############################
589
590SET FOREIGN_KEY_CHECKS = 0;
591
592use TP3;
593
594-- appele de la procédure pour faire l'insertion des places
595call InsertPlaces(500, 'plein air');
596call InsertPlaces(2000, 'couvert');
597
598-- insertion manuelle, car pas besoin de beaucoup de ville
599INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
600
601-- insertion manuelle avec des données produite par generatedata
602INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
603 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");
604INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
605 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");
606INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
607 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");
608INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
609 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");
610INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
611 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");
612
613-- insertion manuelle, car compliquer de le faire avec generatedata
614INSERT INTO tblVehicule VALUES(randomPlaque(), "Honda", "Civic", "Bleu"),(randomPlaque(), "Mazda", "Protégé", "Gris"),(randomPlaque(), "Pontiac", "Sundance", "Gris")
615 ,(randomPlaque(), "Chevrolet", "Cavalier", "Orange"),(randomPlaque(), "Pontiac", "Sunfire", "Rose"),(randomPlaque(), "Ford", "Escort", "Bleu")
616 ,(randomPlaque(), "Suzuki", "Swift", "Vert"),(randomPlaque(), "Volskwagen", "Passat", "Jaune"),(randomPlaque(), "Honda", "Accord", "Noir")
617 ,(randomPlaque(), "Suzuki", "Grand Vitara", "Bleu");
618
619select * from tblVehicule;
620-- insertion manuelle, car pas assé de donnée à générer
621INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
622
623-- Procédure pour ajouter des occasionnels en lot selon une qte donné
624-- ------------------------------------------------------------------
625DELIMITER $$
626CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
627BEGIN
628 DECLARE v_i int DEFAULT 1;
629 WHILE v_i <= p_nb DO
630 call AjoutOccasionnel();
631 SET v_i = v_i + 1;
632 END WHILE;
633END $$
634DELIMITER ;
635
636call AjoutOccasEnLot(50);
637
638-- Procédure pour ajouter des Abonnement en lot selon une qte donné
639-- -----------------------------------------------------------------
640DELIMITER $$
641CREATE PROCEDURE `AjoutAbonEnLot`(in p_nb int)
642BEGIN
643 DECLARE v_i int DEFAULT 1;
644 DECLARE v_idAbon int;
645 DECLARE v_nbDuree int;
646 DECLARE v_idPaiement int;
647 DECLARE v_idVehicule varchar(6);
648 DECLARE v_idPlace int;
649 WHILE v_i <= p_nb DO
650 SET v_idAbon = FLOOR(RAND()*(50-1+1))+1;
651 SET v_nbDuree = FLOOR(RAND()*(12-1+1))+1;
652 SET v_idPaiement = FLOOR(RAND()*(5-1+1))+1;
653 SET v_idVehicule = (SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1);
654 SET v_idPlace = FLOOR(RAND()*(2500-1+1))+1;
655 IF (v_idPaiement = 4) THEN
656 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,null,v_idVehicule,v_idPlace);
657 ELSE
658 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,'4000000000000002',v_idVehicule,v_idPlace);
659 END IF;
660 SET v_i = v_i + 1;
661 END WHILE;
662END $$
663DELIMITER ;
664
665call AjoutAbonEnLot(10);
666select * from tblAbonnement;
667select * from tblTransaction;
668SELECT FLOOR(RAND()*(50-1+1))+1;
669select * from tblMethodPaiement;
670select count(*) from tblVehicule;
671show create table tblVehicule;
672#INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas) VALUES('115.50','4000000000000002','1','1',null),('15.00','4000000000000002','1',NULL,'1');
673
674#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
675#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
676
677select FnNbPlaceAbonne() as 'Nombre d\'abonnement';
678select FnNbPlaceOccas() as 'Nombre d\'occasionnel';
679call CompterPlaceDispo();
680select curdate();
681SELECT DATE_FORMAT(curdate(), '%Y%m%d');call AjoutAbonnement(curdate(), 2, 3, 4, null, 'ABC333', 3)