· 8 years ago · Apr 21, 2018, 07:00 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`),
24 INDEX `idx_nomVille` (`nomVille` ASC))
25ENGINE = InnoDB;
26
27-- -----------------------------------------------------
28-- Table `TP3`.`tblAbonne`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonne` (
31 `idAbonne` INT NOT NULL AUTO_INCREMENT,
32 `nomAbonne` VARCHAR(50) NOT NULL,
33 `prenomAbonne` VARCHAR(25) NULL DEFAULT NULL,
34 `codePostal` VARCHAR(7) NOT NULL,
35 `telephone` VARCHAR(15) NOT NULL,
36 `courriel` VARCHAR(50) NOT NULL,
37 `typeAbonne` CHAR(1) NOT NULL,
38 `idVille` INT NOT NULL,
39 PRIMARY KEY (`idAbonne`),
40 INDEX `idx_nomAbonne` (`nomAbonne` ASC),
41 INDEX `FK_tblAbonne_idVille` (`idVille` ASC),
42 CONSTRAINT `FK_tblAbonne_idVille`
43 FOREIGN KEY (`idVille`)
44 REFERENCES `TP3`.`tblVille` (`idVille`))
45ENGINE = InnoDB;
46
47-- -----------------------------------------------------
48-- Table `TP3`.`tblVehicule`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `TP3`.`tblVehicule` (
51 `idVehicule` VARCHAR(7) NOT NULL,
52 `marque` VARCHAR(25) NOT NULL,
53 `modele` VARCHAR(25) NOT NULL,
54 `couleur` VARCHAR(15) NOT NULL,
55 PRIMARY KEY (`idVehicule`))
56ENGINE = InnoDB;
57
58-- -----------------------------------------------------
59-- Table `TP3`.`tblMethodPaiement`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `TP3`.`tblMethodPaiement` (
62 `idPaiement` INT NOT NULL AUTO_INCREMENT,
63 `typePaiement` CHAR(1) NOT NULL,
64 `descPaiement` VARCHAR(25) NOT NULL,
65 PRIMARY KEY (`idPaiement`))
66ENGINE = InnoDB;
67
68-- -----------------------------------------------------
69-- Table `TP3`.`tblOccasionnel`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `TP3`.`tblOccasionnel` (
72 `idOccas` VARCHAR(12) NOT NULL,
73 `dateDebutOccas` DATETIME NOT NULL,
74 `dateFinOccas` DATETIME NULL,
75 `idTransaction` INT NULL,
76 PRIMARY KEY (`idOccas`),
77 INDEX `FK_tblOccasionnel_idTransaction` (`idTransaction` ASC),
78 CONSTRAINT `FK_tblOccasionnel_idTransaction`
79 FOREIGN KEY (`idTransaction`)
80 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
81ENGINE = InnoDB;
82
83-- -----------------------------------------------------
84-- Table `TP3`.`tblTransaction`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `TP3`.`tblTransaction` (
87 `idTransaction` INT NOT NULL AUTO_INCREMENT,
88 `montant` DECIMAL(6,2) NULL,
89 `numPaiement` VARCHAR(16) NULL,
90 `idPaiement` INT NULL,
91 `idAbonnement` INT NULL,
92 `idOccas` VARCHAR(12) NULL,
93 PRIMARY KEY (`idTransaction`),
94 INDEX `FK_tblTransaction_idPaiement` (`idPaiement` ASC),
95 INDEX `FK_tblTransaction_idAbonnement` (`idAbonnement` ASC),
96 INDEX `FK_tblTransaction_idOccas` (`idOccas` ASC),
97 CONSTRAINT `FK_tblTransaction_idPaiement`
98 FOREIGN KEY (`idPaiement`)
99 REFERENCES `TP3`.`tblMethodPaiement` (`idPaiement`),
100 CONSTRAINT `FK_tblTransaction_idAbonne`
101 FOREIGN KEY (`idAbonnement`)
102 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`),
103 CONSTRAINT `FK_tblTransaction_idOccas`
104 FOREIGN KEY (`idOccas`)
105 REFERENCES `TP3`.`tblOccasionnel` (`idOccas`))
106ENGINE = InnoDB;
107
108-- -----------------------------------------------------
109-- Table `TP3`.`tblAbonnement`
110-- -----------------------------------------------------
111CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonnement` (
112 `idAbonnement` INT NOT NULL AUTO_INCREMENT,
113 `dateDebutAbonne` DATE NOT NULL,
114 `dateFinAbonne` DATE NOT NULL,
115 `idAbonne` INT NOT NULL,
116 `idVehicule` VARCHAR(7) NOT NULL,
117 `idPlace` SMALLINT NOT NULL,
118 `idTransaction` INT NOT NULL,
119 PRIMARY KEY (`idAbonnement`),
120 INDEX `FK_tblAbonnement_idAbonne_tblAbonne` (`idAbonne` ASC),
121 INDEX `FK_tblAbonnement_idVehicule` (`idVehicule` ASC),
122 INDEX `FK_tblAbonnement_idPlace` (`idPlace` ASC),
123 INDEX `FK_tblAbonnement_idTransaction` (`idTransaction` ASC),
124 CONSTRAINT `FK_tblAbonnement_idAbonne_tblAbonne`
125 FOREIGN KEY (`idAbonne`)
126 REFERENCES `TP3`.`tblAbonne` (`idAbonne`),
127 CONSTRAINT `FK_tblAbonnement_idVehicule`
128 FOREIGN KEY (`idVehicule`)
129 REFERENCES `TP3`.`tblVehicule` (`idVehicule`),
130 CONSTRAINT `FK_tblAbonnement_idPlace`
131 FOREIGN KEY (`idPlace`)
132 REFERENCES `TP3`.`tblPlace` (`idPlace`),
133 CONSTRAINT `FK_tblAbonnement_idTransaction`
134 FOREIGN KEY (`idTransaction`)
135 REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
136ENGINE = InnoDB;
137
138-- -----------------------------------------------------
139-- Table `TP3`.`tblPlace`
140-- -----------------------------------------------------
141CREATE TABLE IF NOT EXISTS `TP3`.`tblPlace` (
142 `idPlace` SMALLINT NOT NULL AUTO_INCREMENT,
143 `typePlace` VARCHAR(25) NOT NULL,
144 `idAbonne` INT NULL,
145 PRIMARY KEY (`idPlace`),
146 INDEX `FK_tblPlace_idAbonne` (`idAbonne` ASC),
147 CONSTRAINT `FK_tblPlace_idAbonne`
148 FOREIGN KEY (`idAbonne`)
149 REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
150ENGINE = InnoDB;
151
152SET SQL_MODE=@OLD_SQL_MODE;
153SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
154SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
155
156###############################
157### FONCTIONS ET PROCÉDURES ###
158###############################
159
160-- -----------------------------------------------------
161-- Fonctions
162-- -------------------------------------------------------------------------------------------
163-- Fonction FnNbPlaceAbonne() qui retourne le nombre de place utilisé par un abonnement valide
164-- -------------------------------------------------------------------------------------------
165DELIMITER $$
166CREATE FUNCTION FnNbPlaceAbonne()
167RETURNS int
168BEGIN
169 DECLARE v_nb int;
170 SELECT count(*) as v_nb
171 FROM tblAbonnement as abon
172 WHERE abon.dateFinAbonne > now()
173 into v_nb;
174RETURN v_nb;
175END $$
176DELIMITER ;
177
178-- -----------------------------------------------------------------------------------------
179-- Fonction FnNbPlaceOccas() qui retourne le nombre de place utilisé par un occasionel actif
180-- -----------------------------------------------------------------------------------------
181DELIMITER $$
182CREATE FUNCTION FnNbPlaceOccas()
183RETURNS int
184BEGIN
185 DECLARE v_nb int;
186 SELECT count(*) as v_nv
187 FROM tblOccasionnel as ocas
188 WHERE ocas.dateFinOccas is null
189 into v_nb;
190RETURN v_nb;
191END $$
192DELIMITER ;
193
194-- -------------------------------------------------------------
195-- Fonction pour calculer le montant à payer pour un occasionnel
196-- -------------------------------------------------------------
197DELIMITER $$
198CREATE FUNCTION FnCalculeMontant(p_dateDebut datetime, p_dateFin datetime)
199RETURNS decimal(6,2)
200BEGIN
201 DECLARE v_tHeure int DEFAULT 5;
202 DECLARE v_tJour int DEFAULT 20;
203 DECLARE v_timeDiff float;
204 DECLARE v_montant decimal(6,2);
205
206 SET v_timeDiff = CEIL(TIMESTAMPDIFF(MINUTE, p_dateDebut, p_dateFin)/60);
207 IF (v_timeDiff > 24) THEN
208 SET v_montant = CEIL(v_timeDiff/24 * v_tJour);
209 ELSEIF (v_timeDiff > 23) THEN
210 SET v_montant = v_tJour;
211 ELSE
212 SET v_montant = v_timeDiff * v_tHeure;
213 END IF;
214 RETURN v_montant;
215END$$
216DELIMITER ;
217
218select FnCalculeMontant("2018-04-08 21:25:00", "2018-04-08 22:25:00") as 'Montant à payer';
219select montant from tblTransaction;
220-- ---------------------------------------------------------------------------------------
221-- Fonction FnCreditAbonne() qui retourne le nombre de paiement Abonnement fait par crédit
222-- ---------------------------------------------------------------------------------------
223DELIMITER $$
224CREATE FUNCTION FnCreditAbonne(p_dateTrans DATE)
225RETURNS DECIMAL(9,2)
226BEGIN
227 DECLARE v_Montant DECIMAL(9,2) default 0;
228 SELECT SUM(trans.montant)
229 FROM tblTransaction as trans
230 INNER JOIN tblMethodPaiement as methode
231 ON trans.idPaiement = methode.idPaiement
232 INNER JOIN tblAbonnement as abon
233 ON trans.idAbonnement = abon.idAbonnement
234 WHERE methode.typePaiement in ('V','M','X') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
235 INTO v_Montant;
236 if (v_Montant is null) then
237 set v_Montant = 0;
238 end if;
239RETURN v_Montant;
240END $$
241DELIMITER ;
242
243#select FnCreditAbonne(curdate()) as 'Nombre de vente abonne par crédit';
244
245-- ---------------------------------------------------------------------------------------
246-- Fonction FnComptantAbonne() qui retourne le nombre de paiement Abonnement fait comptant
247-- ---------------------------------------------------------------------------------------
248DELIMITER $$
249CREATE FUNCTION FnComptantAbonne(p_dateTrans DATE)
250RETURNS DECIMAL(9,2)
251BEGIN
252 DECLARE v_Montant DECIMAL(9,2);
253 SELECT SUM(trans.montant)
254 FROM tblTransaction as trans
255 INNER JOIN tblMethodPaiement as methode
256 ON trans.idPaiement = methode.idPaiement
257 INNER JOIN tblAbonnement as abon
258 ON trans.idAbonnement = abon.idAbonnement
259 WHERE methode.typePaiement in ('A','C') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
260 INTO v_Montant;
261 if (v_Montant is null) then
262 set v_Montant = 0;
263 end if;
264RETURN v_Montant;
265END $$
266DELIMITER ;
267
268#select FnComptantAbonne(curdate()) as 'Nombre de vente abonne comptant/chèque';
269
270 -- ---------------------------------------------------------------------------------------
271-- Fonction FnCreditOcca() qui retourne le nombre de paiement Occasionnel fait par crédit
272-- ---------------------------------------------------------------------------------------
273DELIMITER $$
274CREATE FUNCTION FnCreditOcca(p_dateTrans DATE)
275RETURNS DECIMAL(9,2)
276BEGIN
277 DECLARE v_Montant DECIMAL(9,2);
278 SELECT SUM(trans.montant)
279 FROM tblTransaction as trans
280 INNER JOIN tblMethodPaiement as methode
281 ON trans.idPaiement = methode.idPaiement
282 INNER JOIN tblOccasionnel as occa
283 ON trans.idOccas = occa.idOccas
284 WHERE methode.typePaiement in ('V','M','X') AND idAbonnement is null AND cast(occa.dateFinOccas as date) = p_dateTrans
285 INTO v_Montant;
286 if (v_Montant is null) then
287 set v_Montant = 0;
288 end if;
289RETURN v_Montant;
290END $$
291DELIMITER ;
292
293#select FnCreditOcca(curdate()) as 'Nombre de vente occas. par crédit';
294
295-- ---------------------------------------------------------------------------------------
296-- Fonction FnComptantOcca() qui retourne le nombre de paiement Occasionnel fait comptant
297-- ---------------------------------------------------------------------------------------
298DELIMITER $$
299CREATE FUNCTION FnComptantOcca(p_dateTrans DATE)
300RETURNS DECIMAL(9,2)
301BEGIN
302 DECLARE v_Montant DECIMAL(9,2);
303 SELECT SUM(trans.montant)
304 FROM tblTransaction as trans
305 INNER JOIN tblMethodPaiement as methode
306 ON trans.idPaiement = methode.idPaiement
307 INNER JOIN tblOccasionnel as occa
308 ON trans.idOccas = occa.idOccas
309 WHERE methode.typePaiement in ('A','C') AND idAbonnement is null AND cast(occa.dateFinOccas as DATE) = p_dateTrans
310 INTO v_Montant;
311 if (v_Montant is null) then
312 set v_Montant = 0;
313 end if;
314RETURN v_Montant;
315END $$
316DELIMITER ;
317
318#select FnComptantOcca(curdate()) as 'Nombre de vente occas. comptant/chèque';
319
320-- ------------------------------------------------------------
321-- Fonction pour générer des plaques de voiture
322-- ------------------------------------------------------------
323DELIMITER $$
324CREATE FUNCTION FnRandomPlaque()
325 RETURNS VARCHAR(6)
326BEGIN
327 DECLARE plaque VARCHAR(6) DEFAULT "";
328 SELECT concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
329 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
330 substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
331 substring('0123456789', rand()*9+1, 1),
332 substring('0123456789', rand()*9+1, 1),
333 substring('0123456789', rand()*9+1, 1))
334 into @idVehicule;
335
336 SET @rcount = -1;
337 SELECT COUNT(*) INTO @rcount FROM `tblVehicule` WHERE `idVehicule` = @idVehicule ;
338
339 IF @rcount = 0 THEN
340 SET plaque = @idVehicule ;
341 END IF ;
342
343 RETURN plaque ;
344END$$
345DELIMITER ;
346
347#select randomPlaque();
348
349-- ------------------------------------------------------------
350-- Fonction pour retourner le NOMBRE ABONNEMENTS PERSONNE/SOCIETE
351-- ------------------------------------------------------------
352DELIMITER $$
353CREATE FUNCTION FnNbAbon(p_DateDebut Date, p_DateFin Date, p_TypeAbonne char)
354 RETURNS int
355BEGIN
356 DECLARE v_nb int;
357SELECT count(*) FROM tblTransaction as trans
358 INNER JOIN tblAbonnement as abonnem
359 ON trans.idTransaction = abonnem.idTransaction
360 INNER JOIN tblAbonne as abon
361 ON abonnem.idAbonne = abon.idAbonne
362 WHERE abon.typeAbonne = p_TypeAbonne AND abonnem.dateDebutAbonne <= p_DateFin AND abonnem.dateDebutAbonne > p_DateDebut
363 INTO v_nb;
364
365 IF (v_nb is null) THEN
366 SET v_nb = 0;
367 END IF ;
368
369 RETURN v_nb ;
370END$$
371DELIMITER ;
372
373#select FnNbAbon(curdate(),curdate(),'s');
374
375 -- ------------------------------------------------------------
376-- Fonction pour connaitre SOMME ABONNEMENTS PERSONNE/SOCIETE
377-- ------------------------------------------------------------
378DELIMITER $$
379CREATE FUNCTION FnSommeAbon(p_DateDebut Date, p_DateFin Date, p_TypeAbonne char)
380 RETURNS int
381BEGIN
382 DECLARE v_somme int;
383SELECT sum(trans.montant) FROM tblTransaction as trans
384 INNER JOIN tblAbonnement as abonnem
385 ON trans.idTransaction = abonnem.idTransaction
386 INNER JOIN tblAbonne as abon
387 ON abonnem.idAbonne = abon.idAbonne
388 WHERE abon.typeAbonne = p_TypeAbonne AND abonnem.dateDebutAbonne <= p_DateFin AND abonnem.dateDebutAbonne > p_DateDebut
389 INTO v_somme;
390
391 IF (v_somme is null) THEN
392 SET v_somme = 0;
393 END IF ;
394
395 RETURN v_somme ;
396END$$
397DELIMITER ;
398
399#select count(*) from tblAbonnement where dateFinAbonne >= curdate();
400#select FnSommeAbon(curdate()-1,curdate(),'s');
401-- --------------------------------------------
402-- Fonction pour connaitre NOMBRE OCCASIONNEL
403-- --------------------------------------------
404DELIMITER $$
405CREATE FUNCTION FnNbOcca(p_DateDebut Date, p_DateFin Date)
406 RETURNS int
407BEGIN
408 DECLARE v_nb int;
409SELECT count(*) FROM tblTransaction as trans
410 INNER JOIN tblAbonnement as abonnem
411 ON trans.idTransaction = abonnem.idTransaction
412 INNER JOIN tblOccasionnel as occa
413 ON trans.idOccas = occa.idOccas
414 WHERE trans.idAbonnement is null AND occa.dateFinOccas <= p_DateFin AND occa.dateFinOccas > p_DateDebut
415 INTO v_nb;
416
417 IF (v_nb is null) THEN
418 SET v_nb = 0;
419 END IF ;
420
421 RETURN v_nb ;
422END$$
423DELIMITER ;
424
425#select FnNbOcca(curdate()-1, curdate());
426-- --------------------------------------------
427-- Fonction pour connaitre SOMME OCCASIONNEL
428-- --------------------------------------------
429DELIMITER $$
430CREATE FUNCTION FnSommeOcca(p_DateDebut Date, p_DateFin Date)
431 RETURNS int
432BEGIN
433 DECLARE v_nb int;
434SELECT sum(trans.montant) FROM tblTransaction as trans
435 INNER JOIN tblAbonnement as abonnem
436 ON trans.idTransaction = abonnem.idTransaction
437 INNER JOIN tblOccasionnel as occa
438 ON trans.idOccas = occa.idOccas
439 WHERE trans.idAbonnement is null AND occa.dateFinOccas <= p_DateFin AND occa.dateFinOccas > p_DateDebut
440 INTO v_nb;
441
442 IF (v_nb is null) THEN
443 SET v_nb = 0;
444 END IF ;
445
446 RETURN v_nb ;
447END$$
448DELIMITER ;
449
450#select FnSommeOcca(curdate()-1, curdate());
451-- -----------------------------------------------------
452-- Procédures
453-- ------------------------------------------------------------------------------
454-- Procédure pour la création des places dans tblPlace selon le nombre et le type
455-- ------------------------------------------------------------------------------
456DELIMITER $$
457CREATE PROCEDURE `InsertPlaces`(in p_nb int, in p_type varchar(25))
458BEGIN
459 DECLARE v_i int DEFAULT 1;
460
461 WHILE v_i <= p_nb DO
462 INSERT INTO tblPlace (typePlace,idAbonne) VALUES(p_type, null);
463 SET v_i = v_i + 1;
464 END WHILE;
465END $$
466DELIMITER ;
467
468-- -------------------------------------------------------------------------
469-- Procédure CompterPlaceDispo(), va retourner le nombre de place disponible
470-- en tenant compte des places utilisés par les abonnés et occasionnels
471-- -------------------------------------------------------------------------
472DELIMITER $$
473CREATE PROCEDURE `CompterPlaceDispo`()
474BEGIN
475 DECLARE v_nbOccas int;
476 DECLARE v_nbAbon int;
477 DECLARE v_nbTotal int;
478
479 SET v_nbOccas = FnNbPlaceOccas();
480 SET v_nbAbon = FnNbPlaceAbonne();
481 SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
482 SELECT v_nbTotal as 'Stationnement disponible';
483END$$
484DELIMITER ;
485
486#call CompterPlaceDispo();
487
488-- -----------------------------------------------------------------------------------
489-- Procédure SommesPercuesParType(), va calculer les montants percus par type d'usager
490-- en se basant sur des dates fournis
491-- -----------------------------------------------------------------------------------
492DELIMITER $$
493CREATE PROCEDURE `SommesPercuesParType`(IN p_dateDebut Date, IN p_dateFin Date)
494BEGIN
495 DECLARE v_type varchar(8);
496 DECLARE v_nbAbon int;
497 DECLARE v_nbOccas int;
498 DECLARE v_somme decimal(9,2);
499 DECLARE v_total int;
500
501 SELECT * FROM tblAbonne as ab
502 INNER JOIN tblAbonnement as abt
503 ON ab.idAbonne = abt.idAbonne
504 WHERE abt.dateDebutAbonne >= p_dateDebut and abt.dateFinAbonne <= p_dateFin;
505END$$
506DELIMITER ;
507
508#call SommesPercuesParType('2018-01-01', curdate());
509
510-- -----------------------------------------------------
511-- Procédure AjoutAbonne(), va faire l'ajout d'un abonné
512-- -----------------------------------------------------
513drop procedure if exists AjoutAbonne;
514
515DELIMITER $$
516CREATE PROCEDURE `AjoutAbonne`(in p_nomAbonne varchar(50), in p_prenomAbonne varchar(25), in p_codePostal varchar(7),
517 in p_telephone varchar(15), in p_courriel varchar(50), in p_typeAbonne char(1), in p_idVille int)
518BEGIN
519 SET @abCount = -1;
520 SELECT COUNT(*) INTO @abCount FROM `tblAbonne` WHERE `nomAbonne` = p_nomAbonne and `prenomAbonne` = p_prenomAbonne
521 or `nomAbonne` = p_nomAbonne and `prenomAbonne` is null;
522
523 IF @abCount = 0 THEN
524 INSERT INTO tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille)
525 VALUES(p_nomAbonne,p_prenomAbonne,p_codePostal,p_telephone,p_courriel,p_typeAbonne,p_idVille);
526 ELSE
527 SELECT 'Abonné déjà existant';
528 END IF ;
529END$$
530DELIMITER ;
531
532use TP3;
533
534#select * from tblAbonne order by nomAbonne desc;
535#call AjoutAbonne('Leblanc', 'Robby', 'Q4A2O7', '1-195-388-6491', 'nec@Nulla.co.uk', 'S', 3);
536
537-- -----------------------------------------------------------------------------------------------------------------------
538-- Procédure AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement et retourner l'id de celle-ci
539-- -----------------------------------------------------------------------------------------------------------------------
540DELIMITER $$
541CREATE PROCEDURE `AjoutTransactionAbonne`(in p_duree int, in p_numPaiement varchar(16), in p_idPaiement int, out LIDTRANSACABON int)
542BEGIN
543 DECLARE v_tMois int DEFAULT 240;
544 DECLARE v_tAn int DEFAULT 2500;
545 DECLARE v_mTotal int;
546 DECLARE v_montant decimal(6,2);
547 IF (p_duree > 11) THEN
548 SET v_mTotal = CEIL(p_duree / 12) * v_tAn;
549 ELSE
550 SET v_mTotal = p_duree * v_tMois;
551 END IF;
552
553 SET v_montant = v_mTotal;
554
555 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
556 VALUES(v_montant,p_numPaiement,p_idPaiement,null,null);
557
558 SET LIDTRANSACABON = LAST_INSERT_ID();
559END$$
560DELIMITER ;
561
562-- --------------------------------------------------
563-- Procédure AjoutAbonnement(), va créer l'abonnement
564-- --------------------------------------------------
565DELIMITER $$
566CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_duree int, in p_idAbonne int, in p_idPaiement int, in p_numPaiement varchar(16)
567 , in p_idVehicule varchar(7), in p_idPlace smallint)
568BEGIN
569 DECLARE v_idTransac int;
570 DECLARE v_dateFin date;
571
572 SET @abtCount = -1;
573 SELECT COUNT(*) INTO @abtCount FROM tblAbonnement where idVehicule = p_idVehicule and dateFinAbonne >= p_dateDebut
574 or idPlace = p_idPlace and dateFinAbonne >= p_dateDebut;
575
576 IF (@abtCount = 0) THEN
577 SET v_dateFin = DATE_ADD(p_dateDebut, INTERVAL p_duree MONTH);
578 -- Appele la procedure pour générer la transaction de l'abonnement
579 call AjoutTransactionAbonne(p_duree, p_numPaiement, p_idPaiement, @LIDTRANSACABON);
580
581 INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
582 VALUES(p_dateDebut,v_dateFin,p_idAbonne,p_idVehicule,p_idPlace,@LIDTRANSACABON);
583
584 UPDATE tblTransaction set idAbonnement = LAST_INSERT_ID() where idTransaction = @LIDTRANSACABON;
585 ELSE
586 SELECT 'Un abonnement avec la même plaque ou place est toujours actif!';
587 END IF;
588END$$
589DELIMITER ;
590
591#select 'ALLO';
592#SELECT COUNT(*) INTO @abtCount FROM tblAbonnement where idVehicule = 'XJA029' and dateFinAbonne >= '2018-10-20' or idPlace = 526 and dateFinAbonne >= '2018-10-20';
593#SELECT * FROM tblAbonnement where idVehicule = 'XJA028' and dateFinAbonne >= '2018-10-20';
594#SELECT @abtCount;
595#SELECT * from tblAbonnement where idVehicule = 'XJA028';
596#call AjoutAbonnement(curdate(), 10, 1, 4, null, 'XJA028', 414);
597#update tblAbonnement set dateFinAbonne = curdate()-1 where idAbonnement = 15;
598#SELECT * from tblAbonnement where idPlace = 1996;
599
600-- ----------------------------------------------------------------------------------------
601-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour l'action d'occasionnel
602-- ----------------------------------------------------------------------------------------
603DELIMITER $$
604CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16), out LIDTRANSACOCCAS int)
605BEGIN
606 DECLARE v_montantOccas decimal(6,2);
607 DECLARE v_dateDebut datetime;
608
609 SELECT dateDebutOccas FROM tblOccasionnel AS occas
610 INNER JOIN tblTransaction as trans
611 ON occas.idOccas = trans.idOccas
612 WHERE occas.idOccas = p_idOccas
613 INTO @v_dateDebut;
614
615 SET v_montantOccas = FnCalculeMontant(@v_dateDebut, now());
616
617 INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
618 VALUES(v_montantOccas,p_numPaiement,p_idPaiement,null,p_idOccas);
619
620 SET LIDTRANSACOCCAS = LAST_INSERT_ID();
621END$$
622DELIMITER ;
623select now() into @cdate;
624select 'est' = FnCalculeMontant(@cdate, now());
625-- ---------------------------------------------------------------
626-- Procédure AjoutOccasionnel(), va créer l'ajout d'un occasionnel
627-- ---------------------------------------------------------------
628DELIMITER $$
629CREATE PROCEDURE `AjoutOccasionnel`()
630BEGIN
631
632 DECLARE v_nbIdTotal int DEFAULT 0;
633 DECLARE v_nbIdAvant int DEFAULT 0;
634 DECLARE v_idOccas varchar(12);
635
636 SELECT count(*) into v_nbIdTotal
637 FROM tblOccasionnel;
638 SELECT count(*) into v_nbIdAvant
639 FROM tblOccasionnel WHERE TIMESTAMPDIFF(DAY, dateDebutOccas, now() != 0);
640
641 SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);
642
643 INSERT INTO tblOccasionnel VALUES(v_idOccas,now(),null,null);
644
645END$$
646DELIMITER ;
647
648-- --------------------------------------------------------------------------------------
649-- Procédure SortieOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
650-- --------------------------------------------------------------------------------------
651drop procedure if exists SortieOccasionnel;
652
653DELIMITER $$
654CREATE PROCEDURE `SortieOccasionnel`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16))
655BEGIN
656 SET @occasCount = -1;
657 SELECT COUNT(*) INTO @occasCount FROM tblOccasionnel where idOccas = p_idOccas and dateFinOccas is not null;
658 IF (@occasCount = 0) THEN
659 call AjoutTransactionOccas(p_idOccas, p_idPaiement, p_numPaiement, @LIDTRANSACOCCAS);
660
661 UPDATE tblOccasionnel set dateFinOccas = now(), idTransaction = @LIDTRANSACOCCAS WHERE idOccas = p_idOccas;
662 ELSE
663 SELECT 'Cet occasionel a déjà quitté';
664 END IF;
665END$$
666DELIMITER ;
667
668update tblOccasionnel set dateDebutOccas = '2018-04-18 10:00:00' where idOccas = '201804216';
669call SortieOccasionnel('201804218', 4, null);
670#call SortieOccasionnel('2018042115', 4, null);
671select * from tblTransaction where idTransaction = 69;
672select * from tblOccasionnel where idOccas = '201804218';
673select * from tblOccasionnel;
674
675-- -------------------------------------------------------------------------
676-- Procédure DepotQuotidien(), va retourner les achats par type de paiement et
677-- par type d'utilisateur
678-- -------------------------------------------------------------------------
679drop procedure if exists DepotQuotidien;
680DROP TEMPORARY TABLE if exists TMP_depot;
681
682DELIMITER $$
683CREATE PROCEDURE `DepotQuotidien`(p_DateVerif Date)
684BEGIN
685 DECLARE v_CrOcca int;
686 DECLARE v_CrAbon int;
687 DECLARE v_CoOcca int;
688 DECLARE v_CoAbon int;
689 set v_CrOcca = FnCreditOcca(p_DateVerif);
690 set v_CrAbon = FnCreditAbonne(p_DateVerif);
691 set v_CoOcca = FnComptantOcca(p_DateVerif);
692 set v_CoAbon = FnComptantAbonne(p_DateVerif);
693
694
695 CREATE TEMPORARY TABLE TMP_depot (
696 type_Depot varchar(25),
697 Abonnements DECIMAL(9,2),
698 Occasionnels DECIMAL(9,2));
699
700 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
701 VALUE ('Carte de crédit', v_CrAbon, v_CrOcca);
702 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
703 VALUE ('Argent comptant', v_CoAbon, v_CoOcca);
704 INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
705 VALUE ('Total', (v_CoAbon + v_CrAbon), (v_CoOcca + v_CrOcca));
706
707 SELECT * From TMP_depot;
708
709 DROP TEMPORARY TABLE TMP_depot;
710
711END$$
712DELIMITER ;
713
714call DepotQuotidien(curdate());
715#SELECT SUM(FnCreditOcca(curdate()) + FnComptantOcca(curdate()));
716
717-- --------------------------------------------------------------------------------
718-- Procédure ventilation donne la ventilation dans un tableau des sommes
719-- perçues par type de personne (physique ou société), les véhicules occasionnels.
720-- --------------------------------------------------------------------------------
721drop procedure if exists ventilation;
722DROP TEMPORARY TABLE if exists TMP_ventilation;
723
724DELIMITER $$
725CREATE PROCEDURE `ventilation`(p_DateDebut Date, p_DateFin Date)
726BEGIN
727 DECLARE v_NnOcca int;
728 DECLARE v_NbAbonSoc int;
729 DECLARE v_NbAbonPers int;
730 DECLARE v_SommeOcca DEC(9,2);
731 DECLARE v_SommeAbonSoc DEC(9,2);
732 DECLARE v_SommeAbonPers DEC(9,2);
733
734 SET v_NnOcca = FnNbOcca(p_DateDebut, p_DateFin);
735 SET v_NbAbonSoc = FnNbAbon(p_DateDebut, p_DateFin, 's');
736 SET v_NbAbonPers = FnNbAbon(p_DateDebut, p_DateFin, 'p');
737 SET v_SommeOcca = FnSommeOcca(p_DateDebut, p_DateFin);
738 SET v_SommeAbonSoc = FnSommeAbon(p_DateDebut, p_DateFin, 's');
739 SET v_SommeAbonPers = FnSommeAbon(p_DateDebut, p_DateFin, 'p');
740
741 CREATE TEMPORARY TABLE TMP_ventilation (
742 TypeDeClient varchar(25),
743 NbAbonnements INT,
744 SommeAbonnements DECIMAL(9,2),
745 NbOccasionnels INT,
746 SommeOccasionnels DECIMAL(9,2));
747
748 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
749 VALUE ('Personne', v_NbAbonPers, v_SommeAbonPers, v_NnOcca, v_SommeOcca);
750 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
751 VALUE ('Société', v_NbAbonSoc, v_SommeAbonSoc, null, null);
752 INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
753 VALUE ('Total', (v_NbAbonPers+v_NbAbonSoc), (v_SommeAbonPers+v_SommeAbonSoc), v_NnOcca, v_SommeOcca);
754
755 SELECT TypeDeClient as '', NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels From TMP_ventilation;
756
757 DROP TEMPORARY TABLE TMP_ventilation;
758
759END$$
760DELIMITER ;
761
762call ventilation(curdate()-1, curdate()+1);
763
764###############################
765### INSERTIONS DES DONNÉÉES ###
766###############################
767
768SET FOREIGN_KEY_CHECKS = 1;
769
770use TP3;
771
772-- appele de la procédure pour faire l'insertion des places
773call InsertPlaces(500, 'plein air');
774call InsertPlaces(2000, 'couvert');
775
776-- insertion manuelle, car pas besoin de beaucoup de ville
777INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");
778
779-- insertion manuelle avec des données produite par generatedata
780INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
781 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");
782INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
783 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");
784INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
785 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");
786INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
787 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");
788INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
789 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");
790
791-- insertion manuelle, car compliquer de le faire avec generatedata
792#INSERT INTO tblVehicule VALUES(FnRandomPlaque(), "Honda", "Civic", "Bleu"),(FnRandomPlaque(), "Mazda", "Protégé", "Gris"),(FnRandomPlaque(), "Pontiac", "Sundance", "Gris")
793# ,(FnRandomPlaque(), "Chevrolet", "Cavalier", "Orange"),(FnRandomPlaque(), "Pontiac", "Sunfire", "Rose"),(FnRandomPlaque(), "Ford", "Escort", "Bleu")
794# ,(FnRandomPlaque(), "Suzuki", "Swift", "Vert"),(FnRandomPlaque(), "Volskwagen", "Passat", "Jaune"),(FnRandomPlaque(), "Honda", "Accord", "Noir")
795# ,(FnRandomPlaque(), "Suzuki", "Grand Vitara", "Bleu");
796
797INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Lexus","Multi-Segment","violet"),(FnRandomPlaque(),"Fiat","Pickup","grey"),(FnRandomPlaque(),"Lexus","Utilitaire","black"),(FnRandomPlaque(),"Ford","VUS","yellow"),(FnRandomPlaque(),"BMW","Utilitaire","blue"),(FnRandomPlaque(),"Acura","Utilitaire","blue"),(FnRandomPlaque(),"Lexus","Berline","red"),(FnRandomPlaque(),"Dacia","Multi-Segment","violet"),(FnRandomPlaque(),"Peugeot","Multi-Segment","violet"),(FnRandomPlaque(),"General Motors","Berline","blue");
798INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Kia Motors","Hatchback","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Hatchback","yellow"),(FnRandomPlaque(),"Audi","Berline","green"),(FnRandomPlaque(),"Dacia","Hatchback","black"),(FnRandomPlaque(),"Seat","Pickup","indigo"),(FnRandomPlaque(),"Audi","Sport","orange"),(FnRandomPlaque(),"Mitsubishi Motors","Sport","orange"),(FnRandomPlaque(),"Kia Motors","Hatchback","blue"),(FnRandomPlaque(),"Subaru","Hatchback","grey"),(FnRandomPlaque(),"Mahindra and Mahindra","Berline","indigo");
799INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Daihatsu","Utilitaire","indigo"),(FnRandomPlaque(),"Mercedes-Benz","Hatchback","grey"),(FnRandomPlaque(),"Infiniti","Pickup","blue"),(FnRandomPlaque(),"BMW","Pickup","indigo"),(FnRandomPlaque(),"Chrysler","Pickup","blue"),(FnRandomPlaque(),"Lincoln","Utilitaire","grey"),(FnRandomPlaque(),"Suzuki","Berline","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Hatchback","blue"),(FnRandomPlaque(),"BMW","Multi-Segment","blue"),(FnRandomPlaque(),"Dongfeng Motor","Pickup","orange");
800INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Nissan","VUS","violet"),(FnRandomPlaque(),"Subaru","Berline","yellow"),(FnRandomPlaque(),"Daimler","Berline","indigo"),(FnRandomPlaque(),"Daihatsu","Pickup","red"),(FnRandomPlaque(),"Honda","Multi-Segment","violet"),(FnRandomPlaque(),"BMW","Hatchback","yellow"),(FnRandomPlaque(),"Mazda","Pickup","violet"),(FnRandomPlaque(),"Kia Motors","Sport","green"),(FnRandomPlaque(),"Skoda","Sport","violet"),(FnRandomPlaque(),"Skoda","VUS","orange");
801INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Daimler","Berline","blue"),(FnRandomPlaque(),"Toyota","Utilitaire","grey"),(FnRandomPlaque(),"Renault","Multi-Segment","blue"),(FnRandomPlaque(),"Cadillac","Hatchback","red"),(FnRandomPlaque(),"Dodge","Pickup","red"),(FnRandomPlaque(),"Lexus","Berline","red"),(FnRandomPlaque(),"RAM Trucks","Multi-Segment","yellow"),(FnRandomPlaque(),"Daimler","Berline","orange"),(FnRandomPlaque(),"Chevrolet","Berline","green"),(FnRandomPlaque(),"Subaru","Sport","violet");
802INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Renault","Berline","blue"),(FnRandomPlaque(),"Mazda","Hatchback","indigo"),(FnRandomPlaque(),"Fiat","Utilitaire","indigo"),(FnRandomPlaque(),"Chrysler","Sport","blue"),(FnRandomPlaque(),"Mitsubishi Motors","VUS","violet"),(FnRandomPlaque(),"Lexus","Hatchback","grey"),(FnRandomPlaque(),"Chevrolet","VUS","indigo"),(FnRandomPlaque(),"Skoda","Hatchback","red"),(FnRandomPlaque(),"GMC","Utilitaire","orange"),(FnRandomPlaque(),"Subaru","VUS","yellow");
803INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Vauxhall","Sport","grey"),(FnRandomPlaque(),"Jeep","Pickup","yellow"),(FnRandomPlaque(),"Kenworth","VUS","grey"),(FnRandomPlaque(),"Chevrolet","Pickup","black"),(FnRandomPlaque(),"Isuzu","VUS","blue"),(FnRandomPlaque(),"JLR","Multi-Segment","violet"),(FnRandomPlaque(),"Dacia","VUS","indigo"),(FnRandomPlaque(),"Smart","Sport","blue"),(FnRandomPlaque(),"Lexus","Pickup","yellow"),(FnRandomPlaque(),"Mitsubishi Motors","Utilitaire","red");
804INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"GMC","Hatchback","red"),(FnRandomPlaque(),"Cadillac","Multi-Segment","green"),(FnRandomPlaque(),"Kia Motors","Pickup","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Pickup","blue"),(FnRandomPlaque(),"Citroën","VUS","indigo"),(FnRandomPlaque(),"Vauxhall","Hatchback","green"),(FnRandomPlaque(),"Fiat","Pickup","violet"),(FnRandomPlaque(),"Ferrari","Berline","black"),(FnRandomPlaque(),"Smart","Multi-Segment","indigo"),(FnRandomPlaque(),"Volkswagen","Utilitaire","grey");
805INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Volvo","Multi-Segment","orange"),(FnRandomPlaque(),"Hyundai Motors","Berline","black"),(FnRandomPlaque(),"Hyundai Motors","Multi-Segment","green"),(FnRandomPlaque(),"GMC","Utilitaire","orange"),(FnRandomPlaque(),"General Motors","Utilitaire","yellow"),(FnRandomPlaque(),"BMW","Sport","grey"),(FnRandomPlaque(),"Peugeot","Sport","black"),(FnRandomPlaque(),"Seat","Multi-Segment","red"),(FnRandomPlaque(),"Mahindra and Mahindra","Berline","red"),(FnRandomPlaque(),"Lincoln","Pickup","black");
806INSERT INTO `tblVehicule` VALUES (FnRandomPlaque(),"Toyota","Pickup","grey"),(FnRandomPlaque(),"Lincoln","Berline","orange"),(FnRandomPlaque(),"Seat","Utilitaire","yellow"),(FnRandomPlaque(),"Lexus","Multi-Segment","red"),(FnRandomPlaque(),"Mahindra and Mahindra","VUS","grey"),(FnRandomPlaque(),"Subaru","Berline","blue"),(FnRandomPlaque(),"Daimler","Hatchback","indigo"),(FnRandomPlaque(),"MINI","Sport","yellow"),(FnRandomPlaque(),"Peugeot","Berline","yellow"),(FnRandomPlaque(),"Smart","Berline","yellow");
807
808select count(*) from tblVehicule;
809
810-- insertion manuelle, car pas assé de donnée à générer
811INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");
812
813-- -------------------------------------------------------------------
814-- Procédure pour ajouter des occasionnels en lot selon une qte donnée
815-- -------------------------------------------------------------------
816DELIMITER $$
817CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
818BEGIN
819 DECLARE v_i int DEFAULT 1;
820 WHILE v_i <= p_nb DO
821 call AjoutOccasionnel();
822 SET v_i = v_i + 1;
823 END WHILE;
824END $$
825DELIMITER ;
826
827call AjoutOccasEnLot(150);
828
829#select count(*) from tblOccasionnel;
830
831-- -----------------------------------------------------------------
832-- Procédure pour ajouter des Abonnement en lot selon une qte donnée
833-- -----------------------------------------------------------------
834DELIMITER $$
835CREATE PROCEDURE `AjoutAbonEnLot`(in p_nb int)
836BEGIN
837 DECLARE v_i int DEFAULT 1;
838 DECLARE v_idAbon int;
839 DECLARE v_nbDuree int;
840 DECLARE v_idPaiement int;
841 DECLARE v_idVehicule varchar(6);
842 DECLARE v_idPlace int;
843 WHILE v_i <= p_nb DO
844 SET v_idAbon = FLOOR(RAND()*(50-1+1))+1;
845 SET v_nbDuree = FLOOR(RAND()*(12-1+1))+1;
846 SET v_idPaiement = FLOOR(RAND()*(5-1+1))+1;
847 SET v_idVehicule = (SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1);
848 SET v_idPlace = FLOOR(RAND()*(2500-1+1))+1;
849 IF (v_idPaiement = 4) THEN
850 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,null,v_idVehicule,v_idPlace);
851 ELSE
852 call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,'4000000000000002',v_idVehicule,v_idPlace);
853 END IF;
854 SET v_i = v_i + 1;
855 END WHILE;
856END $$
857DELIMITER ;
858
859call AjoutAbonEnLot(100);
860
861select COUNT(*) from tblAbonnement;
862############################
863########## TESTS ###########
864############################
865
866#select * from tblAbonne where nomAbonne = 'Harveys';
867#insert into tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille) values('Harveys', null, 'Q4A2O7', '1-195-388-6491', 'nec@Nulla.co.uk', 'S', 3, @UnErreur);