· 8 years ago · Apr 11, 2018, 03:48 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
6
7-- -----------------------------------------------------
8-- Schema TP3
9-- -----------------------------------------------------
10CREATE SCHEMA IF NOT EXISTS `TP3` DEFAULT CHARACTER SET utf8 ;
11USE `TP3` ;
12
13-- -----------------------------------------------------
14-- Table `TP3`.`tblVille`
15-- -----------------------------------------------------
16CREATE TABLE IF NOT EXISTS `TP3`.`tblVille` (
17 `idVille` INT NOT NULL AUTO_INCREMENT,
18 `nomVille` VARCHAR(50) NOT NULL,
19 PRIMARY KEY (`idVille`))
20ENGINE = InnoDB;
21
22
23-- -----------------------------------------------------
24-- Table `TP3`.`tblAbonne`
25-- -----------------------------------------------------
26CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonne` (
27 `idAbonne` INT NOT NULL AUTO_INCREMENT,
28 `nomAbonne` VARCHAR(50) NOT NULL,
29 `prenomAbonne` VARCHAR(25) NULL DEFAULT NULL,
30 `codePostal` VARCHAR(7) NOT NULL,
31 `telephone` VARCHAR(15) NOT NULL,
32 `courriel` VARCHAR(50) NOT NULL,
33 `typeAbonne` CHAR(1) NOT NULL,
34 `idVille` INT NOT NULL,
35 PRIMARY KEY (`idAbonne`),
36 INDEX `FK_tblAbonne_idVille` (`idVille` ASC),
37 CONSTRAINT `FK_tblAbonne_idVille`
38 FOREIGN KEY (`idVille`)
39 REFERENCES `TP3`.`tblVille` (`idVille`))
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `TP3`.`tblVehicule`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `TP3`.`tblVehicule` (
47 `idVehicule` VARCHAR(7) NOT NULL,
48 `marque` VARCHAR(25) NOT NULL,
49 `modele` VARCHAR(25) NOT NULL,
50 `couleur` VARCHAR(15) NOT NULL,
51 `idAbonnement` INT NOT NULL,
52 PRIMARY KEY (`idVehicule`),
53 INDEX `FK_tblVehicule_idAbonne` (`idAbonnement` ASC),
54 CONSTRAINT `FK_tblVehicule_idAbonne`
55 FOREIGN KEY (`idAbonnement`)
56 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `TP3`.`tblMethodPaiement`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `TP3`.`tblMethodPaiement` (
64 `idPaiement` INT NOT NULL AUTO_INCREMENT,
65 `typePaiement` CHAR(1) NOT NULL,
66 `descPaiement` VARCHAR(25) NOT NULL,
67 PRIMARY KEY (`idPaiement`))
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `TP3`.`tblOccasionnel`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `TP3`.`tblOccasionnel` (
75 `idOccas` INT NOT NULL,
76 `dateDebutOccas` DATETIME NOT NULL,
77 `dateFinOccas` DATETIME NULL,
78 `idTransaction` INT NULL,
79 PRIMARY KEY (`idOccas`),
80 INDEX `FK_tblOccasionnel_idTransaction` (`idTransaction` ASC),
81 CONSTRAINT `FK_tblOccasionnel_idTransaction`
82 FOREIGN KEY (`idTransaction`)
83 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
84ENGINE = InnoDB;
85
86
87-- -----------------------------------------------------
88-- Table `TP3`.`tblTransaction`
89-- -----------------------------------------------------
90CREATE TABLE IF NOT EXISTS `TP3`.`tblTransaction` (
91 `idTransaction` INT NOT NULL AUTO_INCREMENT,
92 `montant` DECIMAL(5,2) NULL,
93 `numPaiement` VARCHAR(16) NULL,
94 `idPaiement` INT NULL,
95 `idAbonne` INT NULL,
96 `idOccas` INT NULL,
97 PRIMARY KEY (`idTransaction`),
98 INDEX `FK_tblTransaction_idPaiement` (`idPaiement` ASC),
99 INDEX `FK_tblTransaction_idAbonne` (`idAbonne` ASC),
100 INDEX `FK_tblTransaction_idOccas` (`idOccas` ASC),
101 CONSTRAINT `FK_tblTransaction_idPaiement`
102 FOREIGN KEY (`idPaiement`)
103 REFERENCES `TP3`.`tblMethodPaiement` (`idPaiement`),
104 CONSTRAINT `FK_tblTransaction_idAbonne`
105 FOREIGN KEY (`idAbonne`)
106 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`),
107 CONSTRAINT `FK_tblTransaction_idOccas`
108 FOREIGN KEY (`idOccas`)
109 REFERENCES `TP3`.`tblOccasionnel` (`idOccas`))
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `TP3`.`tblAbonnement`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonnement` (
117 `idAbonnement` INT NOT NULL AUTO_INCREMENT,
118 `dateDebutAbonne` DATE NOT NULL,
119 `dateFinAbonne` DATE NOT NULL,
120 `idAbonne` INT NOT NULL,
121 `idVehicule` VARCHAR(7) NOT NULL,
122 `idPlace` SMALLINT NOT NULL,
123 `idTransaction` INT NOT NULL,
124 PRIMARY KEY (`idAbonnement`),
125 INDEX `FK_tblAbonnement_idAbonne_tblAbonne` (`idAbonne` ASC),
126 INDEX `FK_tblAbonnement_idVehicule` (`idVehicule` ASC),
127 INDEX `FK_tblAbonnement_idPlace` (`idPlace` ASC),
128 INDEX `FK_tblAbonnement_idTransaction` (`idTransaction` ASC),
129 CONSTRAINT `FK_tblAbonnement_idAbonne_tblAbonne`
130 FOREIGN KEY (`idAbonne`)
131 REFERENCES `TP3`.`tblAbonne` (`idAbonne`),
132 CONSTRAINT `FK_tblAbonnement_idVehicule`
133 FOREIGN KEY (`idVehicule`)
134 REFERENCES `TP3`.`tblVehicule` (`idVehicule`),
135 CONSTRAINT `FK_tblAbonnement_idPlace`
136 FOREIGN KEY (`idPlace`)
137 REFERENCES `TP3`.`tblPlace` (`idPlace`),
138 CONSTRAINT `FK_tblAbonnement_idTransaction`
139 FOREIGN KEY (`idTransaction`)
140 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
141ENGINE = InnoDB;
142
143
144-- -----------------------------------------------------
145-- Table `TP3`.`tblPlace`
146-- -----------------------------------------------------
147CREATE TABLE IF NOT EXISTS `TP3`.`tblPlace` (
148 `idPlace` SMALLINT NOT NULL AUTO_INCREMENT,
149 `typePlace` VARCHAR(25) NOT NULL,
150 `idAbonne` INT NULL,
151 PRIMARY KEY (`idPlace`),
152 INDEX `FK_tblPlace_idAbonne` (`idAbonne` ASC),
153 CONSTRAINT `FK_tblPlace_idAbonne`
154 FOREIGN KEY (`idAbonne`)
155 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
156ENGINE = InnoDB;
157
158
159SET SQL_MODE=@OLD_SQL_MODE;
160SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
161SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
162
163SET FOREIGN_KEY_CHECKS = 0;
164
165use TP3;
166
167-- -----------------------------------------------------
168-- Fonctions
169-- ------------------------------------------------------------------------------------------
170-- Fonction FnNbPlaceAbonne() qui retourne le nombre de place utilisé par un abonnement valid
171-- ------------------------------------------------------------------------------------------
172DELIMITER $$
173CREATE FUNCTION FnNbPlaceAbonne()
174RETURNS int
175BEGIN
176 DECLARE v_nb int;
177 SELECT count(*) as v_nb
178 FROM tblAbonnement as abon
179 WHERE abon.dateFinAbonne > now()
180 into v_nb;
181RETURN v_nb;
182END $$
183DELIMITER ;
184
185-- -----------------------------------------------------------------------------------
186-- Fonction FnNbPlaceOccas() qui retourne le nombre de place utilisé par un occasionel
187-- -----------------------------------------------------------------------------------
188DELIMITER $$
189CREATE FUNCTION FnNbPlaceOccas()
190RETURNS int
191BEGIN
192 DECLARE v_nb int;
193 SELECT count(*) as v_nv
194 FROM tblOccasionnel as ocas
195 WHERE ocas.dateFinOccas is null
196 into v_nb;
197RETURN v_nb;
198END $$
199DELIMITER ;
200
201-- -----------------------------------------
202-- Fonction pour calculer le montant à payer
203-- -----------------------------------------
204DELIMITER $$
205CREATE FUNCTION FnCalculeMontant(p_dateDebut datetime, p_dateFin datetime)
206RETURNS decimal(5,2)
207BEGIN
208 DECLARE v_tHeure int DEFAULT 5;
209 DECLARE v_tJour int DEFAULT 20;
210 DECLARE v_timeDiff float;
211 DECLARE v_montant decimal(5,2) DEFAULT 5;
212
213 SET v_timeDiff = CEIL(TIMESTAMPDIFF(MINUTE, p_dateDebut, p_dateFin)/60);
214 IF (v_timeDiff > 23) THEN
215 SET v_montant = v_timeDiff * v_tJour;
216 ELSEIF (v_timeDiff > 0) THEN
217 SET v_montant = v_timeDiff * v_tHeure;
218 END IF;
219 RETURN v_montant;
220END$$
221DELIMITER ;
222
223select FnCalculeMontant("2018-04-08 21:25:00", "2018-04-08 22:25:00") as 'Montant à payer';
224select FnCalculeMontant("2018-04-08 21:25:00", "2018-04-08 22:35:00") as 'Montant à payer';
225select FnCalculeMontant("2018-04-08 21:25:00", "2018-04-08 22:15:00") as 'Montant à payer';
226select FnCalculeMontant("2018-04-08 01:25:00", "2018-04-09 01:15:00") as 'Montant à payer';
227
228-- -----------------------------------------------------------------------------------
229-- Fonction FnCredAbon() qui retourne le nombre de paiement Abonnement fait par crédit
230-- -----------------------------------------------------------------------------------
231DELIMITER $$
232CREATE FUNCTION FnCredAbon()
233RETURNS DECIMAL(5,2)
234BEGIN
235 DECLARE v_Montant DECIMAL(5,2);
236 SELECT SUM(trans.montant)
237 FROM tblTransaction as trans
238 INNER JOIN tblMethodPaiement as methode
239 ON trans.idPaiement = methode.idPaiement
240 WHERE methode.typePaiement in ('V','M','X')
241 INTO v_Montant;
242RETURN v_Montant;
243END $$
244DELIMITER ;
245
246#select FnCredAbon() as 'Nombre de vente par crédit';
247-- -----------------------------------------------------
248-- Procédures
249-- ------------------------------------------------------------------------------
250-- Procédure pour la création des places dans tblPlace selon le nombre et le type
251-- ------------------------------------------------------------------------------
252DELIMITER $$
253CREATE PROCEDURE `InsertPlaces`(in p_nb int, in p_type varchar(25))
254BEGIN
255 DECLARE v_i int DEFAULT 1;
256
257 WHILE v_i <= p_nb DO
258 INSERT INTO tblPlace (typePlace,idAbonne) VALUES(p_type, null);
259 SET v_i = v_i + 1;
260 END WHILE;
261END $$
262DELIMITER ;
263
264call InsertPlaces(500, 'plein air');
265call InsertPlaces(2000, 'couvert');
266-- -----------------------------------------------------------------
267-- CompterPlaceDispo(), va retourner le nombre de place disponible
268-- en tenant compte des places utilisé par les abonné et occasionnel
269-- -----------------------------------------------------------------
270DELIMITER $$
271CREATE PROCEDURE `CompterPlaceDispo`()
272BEGIN
273 DECLARE v_nbOccas int;
274 DECLARE v_nbAbon int;
275 DECLARE v_nbTotal int;
276
277 SET v_nbOccas = FnNbPlaceOccas();
278 SET v_nbAbon = FnNbPlaceAbonne();
279 SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
280 SELECT v_nbTotal as 'Stationnement disponible';
281END$$
282DELIMITER ;
283
284-- -------------------------------------------------------------------------
285-- SommesPercuesParType(), va calculer les montants percus par type d'usager
286-- en se basant sur des dates fournis
287-- -------------------------------------------------------------------------
288DELIMITER $$
289CREATE PROCEDURE `SommesPercuesParType`(IN p_dateDebut Date, IN p_dateFin Date)
290BEGIN
291 DECLARE v_dateDebut date;
292 DECLARE v_dateFin date;
293 DECLARE v_type varchar(8);
294 DECLARE v_nbAbon int;
295 DECLARE v_nbOccas int;
296 DECLARE v_somme decimal(9,2);
297 DECLARE v_total int;
298
299 SET v_dateDebut = p_dateDebut;
300 SET v_dateFin = p_dateFin;
301
302 SELECT * FROM tblAbonne as ab
303 INNER JOIN tblAbonnement as abt
304 ON ab.idAbonne = abt.idAbonne
305 WHERE abt.dateDebutAbonne >= v_dateDebut and abt.dateFinAbonne <= v_dateFin;
306END$$
307DELIMITER ;
308
309-- -------------------------------------------
310-- AjoutAbonne(), va faire l'ajout d'un abonné
311-- -------------------------------------------
312DELIMITER $$
313CREATE PROCEDURE `AjoutAbonne`(in p_nomAbonne varchar(50), in p_prenomAbonne varchar(25), in p_codePostal varchar(7),
314 in p_telephone varchar(15), in p_courriel varchar(50), in p_typeAbonne char(1), in p_idVille int)
315BEGIN
316 DECLARE v_nomAbonne varchar(50);
317 DECLARE v_prenomAbonne varchar(25);
318 DECLARE v_codePostal varchar(7);
319 DECLARE v_telephone varchar(15);
320 DECLARE v_courriel varchar(50);
321 DECLARE v_typeAbonne char(1);
322 DECLARE v_idVille int;
323
324 SET v_nomAbonne = p_nomAbonne;
325 SET v_prenomAbonne = p_prenomAbonne;
326 SET v_codePostal = p_codePostal;
327 SET v_telephone = p_telephone;
328 SET v_courriel = p_courriel;
329 SET v_typeAbonne = p_typeAbonne;
330 SET v_idVille = p_idVille;
331
332 INSERT INTO tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille)
333 VALUES(v_nomAbonne,v_prenomAbonne,v_codePostal,v_telephone,v_courriel,v_typeAbonne,v_idVille);
334
335END$$
336DELIMITER ;
337
338-- -----------------------------------------------------------------
339-- AjoutPlace(), va ajouter une place pour l'abonné ou l'occasionnel
340-- -----------------------------------------------------------------
341DELIMITER $$
342CREATE PROCEDURE `AjoutPlace`(in p_idPlace smallint(6), in p_typePlace varchar(25), in p_idAbonne int)
343BEGIN
344 DECLARE v_idPLace smallint(6);
345 DECLARE v_typePlace varchar(25);
346 DECLARE v_idAbonne int;
347
348 SET v_idPLace = p_idPlace;
349 SET v_typePlace = p_typePlace;
350 SET v_idAbonne = p_idAbonne;
351
352 INSERT INTO tblPlace VALUES(v_idPLace,v_typePlace,v_idAbonne);
353
354END$$
355DELIMITER ;
356
357-- ------------------------------------------------------------------------
358-- AjoutTransaction(), va ajouter la transaction pour l'action d'abonnement
359-- ------------------------------------------------------------------------
360DELIMITER $$
361CREATE PROCEDURE `AjoutTransactionAbonne`(in p_montant decimal(5,2), in p_numPaiement varchar(16),
362 in p_idPaiement int, in p_idAbonne int, in p_idOccas int)
363BEGIN
364 DECLARE v_idTransac int;
365 DECLARE v_montant decimal(5,2);
366 DECLARE v_numPaiement varchar(16);
367 DECLARE v_idPaiement int;
368 DECLARE v_idAbonne int;
369 DECLARE v_idOccas int;
370
371 SET v_montant = p_montant;
372 SET v_numPaiement = p_numPaiement;
373 SET v_idPaiement = p_idPaiement;
374 SET v_idAbonne = p_idAbonne;
375 SET v_idOccas = p_idOccas;
376
377 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas)
378 VALUES(v_montant,v_numPaiement,v_idPaiement,v_idAbonne,v_idOccas);
379
380 #SELECT idTransaction FROM tblTransaction ORDER BY idTransaction DESC LIMIT 1 into idTransac;
381
382END$$
383DELIMITER ;
384
385-- -------------------------------------------------------------------------
386-- AjoutTransaction(), va ajouter la transaction pour l'action d'occasionnel
387-- -------------------------------------------------------------------------
388DELIMITER $$
389CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas int, in p_idPaiement int, in p_numPaiement int)
390BEGIN
391 #DECLARE v_idTransac int;
392 DECLARE v_montant decimal(5,2);
393 DECLARE v_numPaiement varchar(16);
394 DECLARE v_idPaiement int;
395 DECLARE v_idAbonne int;
396 DECLARE v_idOccas int;
397
398 SET v_montant = null;
399 SET v_numPaiement = p_numPaiement;
400 SET v_idPaiement = p_idPaiement;
401 SET v_idAbonne = null;
402 SET v_idOccas = p_idOccas;
403
404 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas)
405 VALUES(v_montant,v_numPaiement,v_idPaiement,v_idAbonne,v_idOccas);
406
407 #SELECT idTransaction FROM tblTransaction WHERE idOccas = v_idOccas into idTransac;
408
409END$$
410DELIMITER ;
411
412-- ----------------------------------------
413-- AjoutAbonnement(), va créer l'abonnement
414-- ----------------------------------------
415DELIMITER $$
416CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_dateFin Date, in p_idAbonne int, in p_idVehicule int, in p_idPlace int, in p_idTransac int)
417BEGIN
418 DECLARE v_dateDebut date;
419 DECLARE v_dateFin date;
420 DECLARE v_idAbonne int;
421 DECLARE v_idVehicule int;
422 DECLARE v_idPlace int;
423 DECLARE v_idTransac int;
424
425 SET v_dateDebut = p_dateDebut;
426 SET v_dateFin = p_dateFin;
427 SET v_idAbonne = p_idAbonne;
428 SET v_idVehicule = p_idVehicule;
429 SET v_idPlace = p_idPlace;
430 #SET v_idTransac = p_idTransac;
431 #SET v_idTransac =
432
433 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
434 VALUES(v_dateDebut,v_dateFin,v_idAbonne,v_idVehicule,v_idPlace,v_idTransac);
435
436END$$
437DELIMITER ;
438
439-- ----------------------------------------------------------------------
440-- MajAbonnement(), va mettre à jour l'abonnement pour le rendre obselete
441-- ----------------------------------------------------------------------
442DELIMITER $$
443CREATE PROCEDURE `MajAbonnement`()
444BEGIN
445END$$
446DELIMITER ;
447
448-- -----------------------------------------------------
449-- AjoutOccasionnel(), va créer l'ajout d'un occasionnel
450-- -----------------------------------------------------
451DELIMITER $$
452CREATE PROCEDURE `AjoutOccasionnel`()
453
454BEGIN
455
456 DECLARE v_nbIdTotal int;
457 DECLARE v_nbIdAvant int;
458 DECLARE v_idOccas int;
459 DECLARE v_dateDebut date;
460 DECLARE v_dateFin date;
461 DECLARE v_idTransac int;
462
463 SELECT count(*) as v_nbIdTotal
464 FROM tblOccasionnel;
465 SELECT count(*) as v_nbIdAvant
466 FROM tblOccasionnel WHERE tblOccasionnel.dateDebutOccas != now();
467
468 SET v_idOccas = now()+v_nbIdTotal - v_nbIdAvant +1;
469 #call AjoutTransactionOccas(v_idOccas);
470 SET v_dateDebut = now();
471 SET v_dateFin = null;
472 SET v_idTransac = null;
473
474 INSERT INTO tblOccasionnel VALUES(v_idOccas,v_dateDebut,v_dateFin,v_idTransac);
475
476END$$
477DELIMITER ;
478
479-- -------------------------------------------------------------------------
480-- MajOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
481-- -------------------------------------------------------------------------
482DELIMITER $$
483CREATE PROCEDURE `MajOccasionnel`(in p_idOccas int)
484BEGIN
485 DECLARE v_idOccas int;
486
487 SET v_idOccas = p_idOccas;
488 UPDATE tblOccasionnel set dateFinOccas = now()
489 WHERE idOccas = v_idOccas;
490
491 call CompterPlaceDispo();
492END$$
493DELIMITER ;
494
495-- ----------------------------
496-- Déclencheur
497-- ----------------------------
498-- ----------------------------
499-- before_update_tblOccasionel
500-- ----------------------------
501DELIMITER $$
502CREATE TRIGGER before_update_tblOccasionel BEFORE UPDATE
503ON tblOccasionel FOR EACH ROW
504BEGIN
505
506END $$
507DELIMITER ;
508
509#################################################
510############## Insertion données ################
511#################################################
512INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
513
514INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
515 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");
516INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
517 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");
518INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
519 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");
520INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
521 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");
522INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
523 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");
524
525INSERT INTO tblVehicule VALUES("Q4A2O7", "Honda", "Civic", "Bleu", 1),("Q9R5F5", "Mazda", "Protégé", "Gris", 11),("K6A6V3", "Pontiac", "Sundance", "Gris", 2)
526 ,("T0V9T4", "Chevrolet", "Cavalier", "Orange", 12),("I9D6T3", "Pontiac", "Sunfire", "Rose", 21);
527
528INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
529
530INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonne,idOccas) VALUES('115.50','4000000000000002','1','1',null),('15.00','4000000000000002','1',NULL,'1');
531
532INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
533#INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction) VALUES(curdate(),curdate()+INTERVAL 1 YEAR,1,1,1,1);
534
535INSERT INTO tblPlace VALUES('1','P','1');
536
537INSERT INTO tblOccasionnel VALUES('201804061',curdate(),NULL,2);
538
539select FnNbPlaceAbonne() as 'Nombre d\'abonnement';
540select FnNbPlaceOccas() as 'Nombre d\'occasionnel';
541call CompterPlaceDispo();