· 8 years ago · Apr 13, 2018, 05:52 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 `idAbonne` INT NULL,
90 `idOccas` VARCHAR(12) NULL,
91 PRIMARY KEY (`idTransaction`),
92 INDEX `FK_tblTransaction_idPaiement` (`idPaiement` ASC),
93 INDEX `FK_tblTransaction_idAbonne` (`idAbonne` 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 (`idAbonne`)
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
216select 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()
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 WHERE methode.typePaiement in ('V','M','X')
231 INTO v_Montant;
232RETURN v_Montant;
233END $$
234DELIMITER ;
235
236select FnCreditAbonne() as 'Nombre de vente par crédit';
237
238-- ---------------------------------------------------------------------------------------
239-- Fonction FnComptantAbonne() qui retourne le nombre de paiement Abonnement fait comptant
240-- ---------------------------------------------------------------------------------------
241DELIMITER $$
242CREATE FUNCTION FnComptantAbonne()
243RETURNS DECIMAL(6,2)
244BEGIN
245 DECLARE v_Montant DECIMAL(6,2);
246 SELECT SUM(trans.montant)
247 FROM tblTransaction as trans
248 INNER JOIN tblMethodPaiement as methode
249 ON trans.idPaiement = methode.idPaiement
250 WHERE methode.typePaiement in ('A','C')
251 INTO v_Montant;
252RETURN v_Montant;
253END $$
254DELIMITER ;
255
256select FnComptantAbonne() as 'Nombre de vente comptant/chèque';
257
258-- -----------------------------------------------------
259-- Procédures
260-- ------------------------------------------------------------------------------
261-- Procédure pour la création des places dans tblPlace selon le nombre et le type
262-- ------------------------------------------------------------------------------
263DELIMITER $$
264CREATE PROCEDURE `InsertPlaces`(in p_nb int, in p_type varchar(25))
265BEGIN
266 DECLARE v_i int DEFAULT 1;
267
268 WHILE v_i <= p_nb DO
269 INSERT INTO tblPlace (typePlace,idAbonne) VALUES(p_type, null);
270 SET v_i = v_i + 1;
271 END WHILE;
272END $$
273DELIMITER ;
274
275-- -------------------------------------------------------------------------
276-- Procédure CompterPlaceDispo(), va retourner le nombre de place disponible
277-- en tenant compte des places utilisés par les abonnés et occasionnels
278-- -------------------------------------------------------------------------
279DELIMITER $$
280CREATE PROCEDURE `CompterPlaceDispo`()
281BEGIN
282 DECLARE v_nbOccas int;
283 DECLARE v_nbAbon int;
284 DECLARE v_nbTotal int;
285
286 SET v_nbOccas = FnNbPlaceOccas();
287 SET v_nbAbon = FnNbPlaceAbonne();
288 SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
289 SELECT v_nbTotal as 'Stationnement disponible';
290END$$
291DELIMITER ;
292
293-- -----------------------------------------------------------------------------------
294-- Procédure SommesPercuesParType(), va calculer les montants percus par type d'usager
295-- en se basant sur des dates fournis
296-- -----------------------------------------------------------------------------------
297DELIMITER $$
298CREATE PROCEDURE `SommesPercuesParType`(IN p_dateDebut Date, IN p_dateFin Date)
299BEGIN
300 DECLARE v_dateDebut date;
301 DECLARE v_dateFin date;
302 DECLARE v_type varchar(8);
303 DECLARE v_nbAbon int;
304 DECLARE v_nbOccas int;
305 DECLARE v_somme decimal(9,2);
306 DECLARE v_total int;
307
308 SET v_dateDebut = p_dateDebut;
309 SET v_dateFin = p_dateFin;
310
311 SELECT * FROM tblAbonne as ab
312 INNER JOIN tblAbonnement as abt
313 ON ab.idAbonne = abt.idAbonne
314 WHERE abt.dateDebutAbonne >= v_dateDebut and abt.dateFinAbonne <= v_dateFin;
315END$$
316DELIMITER ;
317
318-- -----------------------------------------------------
319-- Procédure AjoutAbonne(), va faire l'ajout d'un abonné
320-- -----------------------------------------------------
321DELIMITER $$
322CREATE PROCEDURE `AjoutAbonne`(in p_nomAbonne varchar(50), in p_prenomAbonne varchar(25), in p_codePostal varchar(7),
323 in p_telephone varchar(15), in p_courriel varchar(50), in p_typeAbonne char(1), in p_idVille int)
324BEGIN
325 DECLARE v_nomAbonne varchar(50);
326 DECLARE v_prenomAbonne varchar(25);
327 DECLARE v_codePostal varchar(7);
328 DECLARE v_telephone varchar(15);
329 DECLARE v_courriel varchar(50);
330 DECLARE v_typeAbonne char(1);
331 DECLARE v_idVille int;
332
333 SET v_nomAbonne = p_nomAbonne;
334 SET v_prenomAbonne = p_prenomAbonne;
335 SET v_codePostal = p_codePostal;
336 SET v_telephone = p_telephone;
337 SET v_courriel = p_courriel;
338 SET v_typeAbonne = p_typeAbonne;
339 SET v_idVille = p_idVille;
340
341 INSERT INTO tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille)
342 VALUES(v_nomAbonne,v_prenomAbonne,v_codePostal,v_telephone,v_courriel,v_typeAbonne,v_idVille);
343
344END$$
345DELIMITER ;
346
347-- ---------------------------------------------------------------------------
348-- Procédure AjoutPlace(), va ajouter une place pour l'abonné ou l'occasionnel
349-- ---------------------------------------------------------------------------
350#DELIMITER $$
351#CREATE PROCEDURE `AjoutPlace`(in p_idPlace smallint(6), in p_typePlace varchar(25), in p_idAbonne int)
352#BEGIN
353# DECLARE v_idPLace smallint(6);
354# DECLARE v_typePlace varchar(25);
355# DECLARE v_idAbonne int;
356#
357# SET v_idPLace = p_idPlace;
358# SET v_typePlace = p_typePlace;
359# SET v_idAbonne = p_idAbonne;
360#
361# INSERT INTO tblPlace VALUES(v_idPLace,v_typePlace,v_idAbonne);
362#
363#END$$
364#DELIMITER ;
365
366-- ----------------------------------------------------------------------------------------
367-- Procédure AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement
368-- ----------------------------------------------------------------------------------------
369DELIMITER $$
370CREATE PROCEDURE `AjoutTransactionAbonne`(in p_montant decimal(6,2), in p_numPaiement varchar(16),
371 in p_idPaiement int, in p_idAbonne int, in p_idOccas int)
372BEGIN
373 DECLARE v_idTransac int;
374 DECLARE v_montant decimal(6,2);
375 DECLARE v_numPaiement varchar(16);
376 DECLARE v_idPaiement int;
377 DECLARE v_idAbonne int;
378 DECLARE v_idOccas int;
379
380 SET v_montant = p_montant;
381 SET v_numPaiement = p_numPaiement;
382 SET v_idPaiement = p_idPaiement;
383 SET v_idAbonne = p_idAbonne;
384 SET v_idOccas = p_idOccas;
385
386 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas)
387 VALUES(v_montant,v_numPaiement,v_idPaiement,v_idAbonne,v_idOccas);
388
389 #SELECT idTransaction FROM tblTransaction ORDER BY idTransaction DESC LIMIT 1 into idTransac;
390
391END$$
392DELIMITER ;
393
394-- ----------------------------------------------------------------------------------------
395-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour l'action d'occasionnel
396-- ----------------------------------------------------------------------------------------
397DELIMITER $$
398CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas int, in p_idPaiement int, in p_numPaiement int)
399BEGIN
400 #DECLARE v_idTransac int;
401 DECLARE v_montant decimal(5,2);
402 DECLARE v_numPaiement varchar(16);
403 DECLARE v_idPaiement int;
404 DECLARE v_idAbonne int;
405 DECLARE v_idOccas int;
406
407 SET v_montant = null;
408 SET v_numPaiement = p_numPaiement;
409 SET v_idPaiement = p_idPaiement;
410 SET v_idAbonne = null;
411 SET v_idOccas = p_idOccas;
412
413 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas)
414 VALUES(v_montant,v_numPaiement,v_idPaiement,v_idAbonne,v_idOccas);
415
416 #SELECT idTransaction FROM tblTransaction WHERE idOccas = v_idOccas into idTransac;
417
418END$$
419DELIMITER ;
420
421-- --------------------------------------------------
422-- Procédure AjoutAbonnement(), va créer l'abonnement
423-- --------------------------------------------------
424DELIMITER $$
425CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_dateFin Date, in p_idAbonne int, in p_idVehicule varchar(7))
426BEGIN
427 DECLARE v_idPlace int; -- Par idAbonne
428 DECLARE v_idTransac int; -- Par idAbonne
429
430 SET v_idVehicule = p_idAbonne.idVehicule;
431 SET v_idPlace = p_idAbonne.idPlace;
432
433 SELECT idTransaction from tblTransaction as trans
434 INNER JOIN tblAbonne as abon
435 ON trans.idAbonne = abon.idAbonne
436 WHERE abon.idAbonne = p_idAbonne
437 INTO v_idTransac;
438
439 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
440 VALUES(p_dateDebut,p_dateFin,p_idAbonne,v_idVehicule,v_idPlace,v_idTransac);
441END$$
442DELIMITER ;
443
444-- --------------------------------------------------------------------------------
445-- Procédure MajAbonnement(), va mettre à jour l'abonnement pour le rendre obselete
446-- --------------------------------------------------------------------------------
447DELIMITER $$
448CREATE PROCEDURE `MajAbonnement`()
449BEGIN
450END$$
451DELIMITER ;
452
453-- ---------------------------------------------------------------
454-- Procédure AjoutOccasionnel(), va créer l'ajout d'un occasionnel
455-- ---------------------------------------------------------------
456DELIMITER $$
457CREATE PROCEDURE `AjoutOccasionnel`()
458BEGIN
459
460 DECLARE v_nbIdTotal int DEFAULT 0;
461 DECLARE v_nbIdAvant int DEFAULT 0;
462 DECLARE v_idOccas varchar(12);
463 DECLARE v_dateDebut datetime;
464 DECLARE v_dateFin datetime;
465 DECLARE v_idTransac int;
466
467 SELECT count(*) into v_nbIdTotal
468 FROM tblOccasionnel;
469 SELECT count(*) into v_nbIdAvant
470 FROM tblOccasionnel WHERE TIMESTAMPDIFF(DAY, dateDebutOccas, now() != 0);
471
472 SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);
473 #call AjoutTransactionOccas(v_idOccas);
474 SET v_dateDebut = now();
475 SET v_dateFin = null;
476 SET v_idTransac = null;
477
478 INSERT INTO tblOccasionnel VALUES(v_idOccas,v_dateDebut,v_dateFin,v_idTransac);
479
480END$$
481DELIMITER ;
482
483-- -------------------------------------------------------------------------
484-- MajOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
485-- -------------------------------------------------------------------------
486DELIMITER $$
487CREATE PROCEDURE `MajOccasionnel`(in p_idOccas int)
488BEGIN
489 DECLARE v_idOccas int;
490
491 SET v_idOccas = p_idOccas;
492 UPDATE tblOccasionnel set dateFinOccas = now()
493 WHERE idOccas = v_idOccas;
494
495 call CompterPlaceDispo();
496END$$
497DELIMITER ;
498
499###############################
500### INSERTIONS DES DONNÉÉES ###
501###############################
502
503SET FOREIGN_KEY_CHECKS = 0;
504
505use TP3;
506
507-- appele de la procédure pour faire l'insertion des places
508call InsertPlaces(500, 'plein air');
509call InsertPlaces(2000, 'couvert');
510
511-- insertion manuelle, car pas besoin de beaucoup de ville
512INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
513
514-- insertion manuelle avec des données produite par generatedata
515INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
516 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");
517INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
518 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");
519INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
520 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");
521INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
522 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");
523INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
524 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");
525
526-- insertion manuelle, car compliquer de le faire avec generatedata
527INSERT INTO tblVehicule VALUES("ABC111", "Honda", "Civic", "Bleu"),("ABC222", "Mazda", "Protégé", "Gris"),("ABC333", "Pontiac", "Sundance", "Gris")
528 ,("ABC444", "Chevrolet", "Cavalier", "Orange"),("ABC555", "Pontiac", "Sunfire", "Rose"),("ABC666", "Ford", "Escort", "Bleu")
529 ,("ABC777", "Suzuki", "Swift", "Vert"),("ABC888", "Volskwagen", "Passat", "Jaune"),("ABC999", "Honda", "Accord", "Noir")
530 ,("ABB111", "Suzuki", "Grand Vitara", "Bleu");
531
532-- insertion manuelle, car pas assé de donnée à générer
533INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
534
535-- Procédure pour ajouter des occasionnels en lot selon une qte donné
536-- ------------------------------------------------------------------
537DELIMITER $$
538CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
539BEGIN
540 DECLARE v_i int DEFAULT 1;
541 WHILE v_i <= p_nb DO
542 call AjoutOccasionnel();
543 SET v_i = v_i + 1;
544 END WHILE;
545END $$
546DELIMITER ;
547
548call AjoutOccasEnLot(50);
549#INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas) VALUES('115.50','4000000000000002','1','1',null),('15.00','4000000000000002','1',NULL,'1');
550
551#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
552#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
553
554select FnNbPlaceAbonne() as 'Nombre d\'abonnement';
555select FnNbPlaceOccas() as 'Nombre d\'occasionnel';
556call CompterPlaceDispo();