· 8 years ago · Apr 15, 2018, 08:02 PM
1#####################################
2### CRÉATION DE LA BASE DE DONNÉE ###
3#####################################
4
5-- MySQL Workbench Forward Engineering
6
7SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
8SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
9SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
10
11-- -----------------------------------------------------
12-- Schema TP3
13-- -----------------------------------------------------
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 AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement et retourner l'id de celle-ci
349-- -----------------------------------------------------------------------------------------------------------------------
350DELIMITER $$
351CREATE PROCEDURE `AjoutTransactionAbonne`(in p_duree int, in p_numPaiement varchar(16), in p_idPaiement int, in p_idAbonne int)
352BEGIN
353 DECLARE v_tMois int DEFAULT 240;
354 DECLARE v_tAn int DEFAULT 2500$;
355 DECLARE v_mTotal int;
356 DECLARE v_montant decimal(6,2);
357 IF (p_duree > 12) THEN
358 SET v_mTotal = CEIL(p_duree / 12) * v_tAn;
359 ELSE
360 SET v_mTotal = p_duree * v_tMois;
361 END IF;
362
363 SET v_montant = v_mTotal;
364
365 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas)
366 VALUES(v_montant,p_numPaiement,p_idPaiement,p_idAbonne,null);
367 #SELECT idTransaction FROM tblTransaction WHERE idAbonne = p_idAbonne INTO idTransac;
368END$$
369DELIMITER ;
370
371-- ----------------------------------------------------------------------------------------
372-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour l'action d'occasionnel
373-- ----------------------------------------------------------------------------------------
374DELIMITER $$
375CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas int, in p_idPaiement int, in p_numPaiement int)
376BEGIN
377 #DECLARE v_idTransac int;
378 DECLARE v_montant decimal(6,2);
379 DECLARE v_numPaiement varchar(16);
380 DECLARE v_idPaiement int;
381 DECLARE v_idAbonne int;
382 DECLARE v_idOccas int;
383
384 SET v_montant = null;
385 SET v_numPaiement = p_numPaiement;
386 SET v_idPaiement = p_idPaiement;
387 SET v_idAbonne = null;
388 SET v_idOccas = p_idOccas;
389
390 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas)
391 VALUES(v_montant,v_numPaiement,v_idPaiement,v_idAbonne,v_idOccas);
392
393END$$
394DELIMITER ;
395
396-- --------------------------------------------------
397-- Procédure AjoutAbonnement(), va créer l'abonnement
398-- --------------------------------------------------
399DELIMITER $$
400CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_duree int, in p_idAbonne int, in p_idPaiement int, in p_numPaiement varchar(16)
401 , in p_idVehicule varchar(7), in p_idPlace smallint)
402BEGIN
403 DECLARE v_idTransac int;
404 DECLARE v_dateFin date;
405
406 SET v_dateFin = DATE_ADD(p_dateDebut, INTERVAL p_duree MONTH);
407 -- Appele la procedure pour générer la transaction de l'abonnement
408 call AjoutTransactionAbonne(p_duree, p_numPaiement, p_idPaiement, p_idAbonne);
409
410 SELECT idTransaction from tblTransaction
411 WHERE idAbonne = p_idAbonne
412 INTO v_idTransac;
413
414 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
415 VALUES(p_dateDebut,v_dateFin,p_idAbonne,p_idVehicule,p_idPlace,v_idTransac);
416
417END$$
418DELIMITER ;
419
420call AjoutAbonnement(curdate(), 2, 3, 4, null, 'ABC333', 3);
421select * from tblAbonnement;
422select * from tblTransaction;
423-- --------------------------------------------------------------------------------
424-- Procédure MajAbonnement(), va mettre à jour l'abonnement pour le rendre obselete
425-- --------------------------------------------------------------------------------
426DELIMITER $$
427CREATE PROCEDURE `MajAbonnement`()
428BEGIN
429END$$
430DELIMITER ;
431
432-- ---------------------------------------------------------------
433-- Procédure AjoutOccasionnel(), va créer l'ajout d'un occasionnel
434-- ---------------------------------------------------------------
435DELIMITER $$
436CREATE PROCEDURE `AjoutOccasionnel`()
437BEGIN
438
439 DECLARE v_nbIdTotal int DEFAULT 0;
440 DECLARE v_nbIdAvant int DEFAULT 0;
441 DECLARE v_idOccas varchar(12);
442 DECLARE v_dateDebut datetime;
443 DECLARE v_dateFin datetime;
444 DECLARE v_idTransac int;
445
446 SELECT count(*) into v_nbIdTotal
447 FROM tblOccasionnel;
448 SELECT count(*) into v_nbIdAvant
449 FROM tblOccasionnel WHERE TIMESTAMPDIFF(DAY, dateDebutOccas, now() != 0);
450
451 SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);
452 #call AjoutTransactionOccas(v_idOccas);
453 SET v_dateDebut = now();
454 SET v_dateFin = null;
455 SET v_idTransac = null;
456
457 INSERT INTO tblOccasionnel VALUES(v_idOccas,v_dateDebut,v_dateFin,v_idTransac);
458
459END$$
460DELIMITER ;
461
462-- -------------------------------------------------------------------------
463-- MajOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
464-- -------------------------------------------------------------------------
465DELIMITER $$
466CREATE PROCEDURE `MajOccasionnel`(in p_idOccas int)
467BEGIN
468 DECLARE v_idOccas int;
469
470 SET v_idOccas = p_idOccas;
471 UPDATE tblOccasionnel set dateFinOccas = now()
472 WHERE idOccas = v_idOccas;
473
474 call CompterPlaceDispo();
475END$$
476DELIMITER ;
477
478###############################
479### INSERTIONS DES DONNÉÉES ###
480###############################
481
482SET FOREIGN_KEY_CHECKS = 0;
483
484use TP3;
485
486-- appele de la procédure pour faire l'insertion des places
487call InsertPlaces(500, 'plein air');
488call InsertPlaces(2000, 'couvert');
489
490-- insertion manuelle, car pas besoin de beaucoup de ville
491INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
492
493-- insertion manuelle avec des données produite par generatedata
494INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
495 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");
496INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
497 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");
498INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
499 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");
500INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
501 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");
502INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
503 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");
504
505-- insertion manuelle, car compliquer de le faire avec generatedata
506INSERT INTO tblVehicule VALUES("ABC111", "Honda", "Civic", "Bleu"),("ABC222", "Mazda", "Protégé", "Gris"),("ABC333", "Pontiac", "Sundance", "Gris")
507 ,("ABC444", "Chevrolet", "Cavalier", "Orange"),("ABC555", "Pontiac", "Sunfire", "Rose"),("ABC666", "Ford", "Escort", "Bleu")
508 ,("ABC777", "Suzuki", "Swift", "Vert"),("ABC888", "Volskwagen", "Passat", "Jaune"),("ABC999", "Honda", "Accord", "Noir")
509 ,("ABB111", "Suzuki", "Grand Vitara", "Bleu");
510
511-- insertion manuelle, car pas assé de donnée à générer
512INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
513
514-- Procédure pour ajouter des occasionnels en lot selon une qte donné
515-- ------------------------------------------------------------------
516DELIMITER $$
517CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
518BEGIN
519 DECLARE v_i int DEFAULT 1;
520 WHILE v_i <= p_nb DO
521 call AjoutOccasionnel();
522 SET v_i = v_i + 1;
523 END WHILE;
524END $$
525DELIMITER ;
526
527call AjoutOccasEnLot(50);
528#INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas) VALUES('115.50','4000000000000002','1','1',null),('15.00','4000000000000002','1',NULL,'1');
529
530#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
531#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
532
533select FnNbPlaceAbonne() as 'Nombre d\'abonnement';
534select FnNbPlaceOccas() as 'Nombre d\'occasionnel';
535call CompterPlaceDispo();
536select curdate();
537SELECT DATE_FORMAT(curdate(), '%Y%m%d');