TP3-BD

#####################################
### CRÉATION DE LA BASE DE DONNÉE ###
#####################################

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema TP3
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS TP3;

CREATE SCHEMA IF NOT EXISTS `TP3` DEFAULT CHARACTER SET utf8 ;
USE `TP3` ;

-- -----------------------------------------------------
-- Table `TP3`.`tblVille`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblVille` (
  `idVille` INT NOT NULL AUTO_INCREMENT,
  `nomVille` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`idVille`),
  INDEX `idx_nomVille` (`nomVille` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TP3`.`tblAbonne`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonne` (
  `idAbonne` INT NOT NULL AUTO_INCREMENT,
  `nomAbonne` VARCHAR(50) NOT NULL,
  `prenomAbonne` VARCHAR(25) NULL DEFAULT NULL,
  `codePostal` VARCHAR(7) NOT NULL,
  `telephone` VARCHAR(15) NOT NULL,
  `courriel` VARCHAR(50) NOT NULL,
  `typeAbonne` CHAR(1) NOT NULL,
  `idVille` INT NOT NULL,
  PRIMARY KEY (`idAbonne`),
  INDEX `idx_nomAbonne` (`nomAbonne` ASC),
  INDEX `FK_tblAbonne_idVille` (`idVille` ASC),
  CONSTRAINT `FK_tblAbonne_idVille`
    FOREIGN KEY (`idVille`)
    REFERENCES `TP3`.`tblVille` (`idVille`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TP3`.`tblVehicule`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblVehicule` (
  `idVehicule` VARCHAR(7) NOT NULL,
  `marque` VARCHAR(25) NOT NULL,
  `modele` VARCHAR(25) NOT NULL,
  `couleur` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`idVehicule`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TP3`.`tblMethodPaiement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblMethodPaiement` (
  `idPaiement` INT NOT NULL AUTO_INCREMENT,
  `typePaiement` CHAR(1) NOT NULL,
  `descPaiement` VARCHAR(25) NOT NULL,
  PRIMARY KEY (`idPaiement`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TP3`.`tblOccasionnel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblOccasionnel` (
  `idOccas` VARCHAR(12) NOT NULL,
  `dateDebutOccas` DATETIME NOT NULL,
  `dateFinOccas` DATETIME NULL,
  `idTransaction` INT NULL,
  PRIMARY KEY (`idOccas`),
  INDEX `FK_tblOccasionnel_idTransaction` (`idTransaction` ASC),
  CONSTRAINT `FK_tblOccasionnel_idTransaction`
    FOREIGN KEY (`idTransaction`)
    REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TP3`.`tblTransaction`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblTransaction` (
  `idTransaction` INT NOT NULL AUTO_INCREMENT,
  `montant` DECIMAL(6,2) NULL,
  `numPaiement` VARCHAR(16) NULL,
  `idPaiement` INT NULL,
  `idAbonnement` INT NULL,
  `idOccas` VARCHAR(12) NULL,
  PRIMARY KEY (`idTransaction`),
  INDEX `FK_tblTransaction_idPaiement` (`idPaiement` ASC),
  INDEX `FK_tblTransaction_idAbonnement` (`idAbonnement` ASC),
  INDEX `FK_tblTransaction_idOccas` (`idOccas` ASC),
  CONSTRAINT `FK_tblTransaction_idPaiement`
    FOREIGN KEY (`idPaiement`)
    REFERENCES `TP3`.`tblMethodPaiement` (`idPaiement`),
  CONSTRAINT `FK_tblTransaction_idAbonne`
    FOREIGN KEY (`idAbonnement`)
    REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`),
  CONSTRAINT `FK_tblTransaction_idOccas`
    FOREIGN KEY (`idOccas`)
    REFERENCES `TP3`.`tblOccasionnel` (`idOccas`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TP3`.`tblAbonnement`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblAbonnement` (
  `idAbonnement` INT NOT NULL AUTO_INCREMENT,
  `dateDebutAbonne` DATE NOT NULL,
  `dateFinAbonne` DATE NOT NULL,
  `idAbonne` INT NOT NULL,
  `idVehicule` VARCHAR(7) NOT NULL,
  `idPlace` SMALLINT NOT NULL,
  `idTransaction` INT NOT NULL,
  PRIMARY KEY (`idAbonnement`),
  INDEX `FK_tblAbonnement_idAbonne_tblAbonne` (`idAbonne` ASC),
  INDEX `FK_tblAbonnement_idVehicule` (`idVehicule` ASC),
  INDEX `FK_tblAbonnement_idPlace` (`idPlace` ASC),
  INDEX `FK_tblAbonnement_idTransaction` (`idTransaction` ASC),
  CONSTRAINT `FK_tblAbonnement_idAbonne_tblAbonne`
    FOREIGN KEY (`idAbonne`)
    REFERENCES `TP3`.`tblAbonne` (`idAbonne`),
  CONSTRAINT `FK_tblAbonnement_idVehicule`
    FOREIGN KEY (`idVehicule`)
    REFERENCES `TP3`.`tblVehicule` (`idVehicule`),
  CONSTRAINT `FK_tblAbonnement_idPlace`
    FOREIGN KEY (`idPlace`)
    REFERENCES `TP3`.`tblPlace` (`idPlace`),
  CONSTRAINT `FK_tblAbonnement_idTransaction`
    FOREIGN KEY (`idTransaction`)
    REFERENCES `TP3`.`tblTransaction` (`idTransaction`))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `TP3`.`tblPlace`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `TP3`.`tblPlace` (
  `idPlace` SMALLINT NOT NULL AUTO_INCREMENT,
  `typePlace` VARCHAR(25) NOT NULL,
  `idAbonne` INT NULL,
  PRIMARY KEY (`idPlace`),
  INDEX `FK_tblPlace_idAbonne` (`idAbonne` ASC),
  CONSTRAINT `FK_tblPlace_idAbonne`
    FOREIGN KEY (`idAbonne`)
    REFERENCES `TP3`.`tblAbonnement` (`idAbonnement`))
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

###############################
### FONCTIONS ET PROCÉDURES ###
###############################

-- -----------------------------------------------------
-- Fonctions -------------------------------------------
-- -----------------------------------------------------
-- --------------------------------------------------------------------------------------------
-- Fonction FnNbPlaceAbonne() qui retourne le nombre de place utilisé par un abonnement valide
-- --------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS FnNbPlaceAbonne;

DELIMITER $$
CREATE FUNCTION FnNbPlaceAbonne()
RETURNS int
BEGIN
	DECLARE v_nb int;
    SELECT count(*) as v_nb
    FROM tblAbonnement as abon
    WHERE abon.dateFinAbonne > now()
    into v_nb;
RETURN v_nb;
END $$
DELIMITER ;

-- ------------------------------------------------------------------------------------------
-- Fonction FnNbPlaceOccas() qui retourne le nombre de place utilisé par un occasionel actif
-- ------------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS FnNbPlaceOccas;

DELIMITER $$
CREATE FUNCTION FnNbPlaceOccas()
RETURNS int
BEGIN
	DECLARE v_nb int;
    SELECT count(*) as v_nv
    FROM tblOccasionnel as ocas
    WHERE ocas.dateFinOccas is null
    into v_nb;
RETURN v_nb;
END $$
DELIMITER ;

-- --------------------------------------------------------------
-- Fonction pour calculer le montant à payer pour un occasionnel
-- --------------------------------------------------------------
DROP FUNCTION IF EXISTS FnCalculerMontant;

DELIMITER $$
CREATE FUNCTION FnCalculerMontant(p_idOccas varchar(12))
RETURNS DECIMAL(6, 2)
BEGIN
	DECLARE v_tHeure int DEFAULT 5;
    DECLARE v_tJour int DEFAULT 20;
    DECLARE v_mJour DECIMAL(6, 2);
    DECLARE v_mHeure DECIMAL(6, 2);
    DECLARE v_dateDebut DATETIME;
    DECLARE v_dTemps TIME;
	DECLARE v_nbJour INT;

    SELECT dateDebutOccas INTO v_dateDebut FROM tblOccasionnel
    	WHERE idOccas = p_idOccas;
    SET v_dTemps = TIMEDIFF(CURRENT_TIME(), TIME(v_dateDebut));

    IF (TIME(v_dateDebut) > CURRENT_TIME()) THEN
		SET v_dTemps = TIMEDIFF('23:59:59', v_dTemps);
    END IF;

    SET v_nbJour = DATEDIFF(NOW(), v_dateDebut);
    SET v_mJour = v_nbJour * v_tJour;
    SET v_mHeure = CEIL(time_to_sec(v_dtemps) / (60 * 60)) * v_tHeure;

    IF (v_mHeure > v_tJour)
		THEN SET v_mHeure = v_tJour;
	END IF;
    RETURN (v_mJour + v_mHeure);
END $$
DELIMITER ;

-- ---------------------------------------------------------------------------------------
-- Fonction FnCreditAbonne() qui retourne le nombre de paiement Abonnement fait par crédit
-- ---------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS FnCreditAbonne;

DELIMITER $$
CREATE FUNCTION FnCreditAbonne(p_dateTrans DATE)
RETURNS DECIMAL(9,2)
BEGIN
    DECLARE v_Montant DECIMAL(9,2) default 0;
    SELECT SUM(trans.montant)
    FROM tblTransaction as trans
        INNER JOIN tblMethodPaiement as methode
        ON trans.idPaiement = methode.idPaiement
        INNER JOIN tblAbonnement as abon
        ON trans.idAbonnement = abon.idAbonnement
    WHERE methode.typePaiement in ('V','M','X') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
    INTO v_Montant;
    if (v_Montant is null) then
		set v_Montant = 0;
    end if;
RETURN v_Montant;
END $$
DELIMITER ;

-- ---------------------------------------------------------------------------------------
-- Fonction FnComptantAbonne() qui retourne le nombre de paiement Abonnement fait comptant
-- ---------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS FnComptantAbonne;

DELIMITER $$
CREATE FUNCTION FnComptantAbonne(p_dateTrans DATE)
RETURNS DECIMAL(9,2)
BEGIN
    DECLARE v_Montant DECIMAL(9,2);
    SELECT SUM(trans.montant)
    FROM tblTransaction as trans
        INNER JOIN tblMethodPaiement as methode
        ON trans.idPaiement = methode.idPaiement
        INNER JOIN tblAbonnement as abon
        ON trans.idAbonnement = abon.idAbonnement
    WHERE methode.typePaiement in ('A','C') AND idOccas is null AND abon.dateDebutAbonne = p_dateTrans
    INTO v_Montant;
    if (v_Montant is null) then
		set v_Montant = 0;
    end if;
RETURN v_Montant;
END $$
DELIMITER ;

 -- ---------------------------------------------------------------------------------------
-- Fonction FnCreditOcca() qui retourne le nombre de paiement Occasionnel fait par crédit
-- ---------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS FnCreditOcca;

DELIMITER $$
CREATE FUNCTION FnCreditOcca(p_dateTrans DATE)
RETURNS DECIMAL(9,2)
BEGIN
    DECLARE v_Montant DECIMAL(9,2);
    SELECT SUM(trans.montant)
    FROM tblTransaction as trans
        INNER JOIN tblMethodPaiement as methode
        ON trans.idPaiement = methode.idPaiement
        INNER JOIN tblOccasionnel as occa
        ON trans.idOccas = occa.idOccas
    WHERE methode.typePaiement in ('V','M','X') AND idAbonnement is null AND cast(occa.dateFinOccas as date) = p_dateTrans
    INTO v_Montant;
    if (v_Montant is null) then
		set v_Montant = 0;
    end if;
RETURN v_Montant;
END $$
DELIMITER ;

-- ---------------------------------------------------------------------------------------
-- Fonction FnComptantOcca() qui retourne le nombre de paiement Occasionnel fait comptant
-- ---------------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS FnComptantOcca;

DELIMITER $$
CREATE FUNCTION FnComptantOcca(p_dateTrans DATE)
RETURNS DECIMAL(9,2)
BEGIN
    DECLARE v_Montant DECIMAL(9,2);
    SELECT SUM(trans.montant)
    FROM tblTransaction as trans
        INNER JOIN tblMethodPaiement as methode
        ON trans.idPaiement = methode.idPaiement
        INNER JOIN tblOccasionnel as occa
        ON trans.idOccas = occa.idOccas
    WHERE methode.typePaiement in ('A','C') AND idAbonnement is null AND cast(occa.dateFinOccas as DATE) = p_dateTrans
    INTO v_Montant;
    if (v_Montant is null) then
		set v_Montant = 0;
    end if;
RETURN v_Montant;
END $$
DELIMITER ;

-- ------------------------------------------------------------
-- Fonction pour générer des plaques de voiture
-- ------------------------------------------------------------
DROP FUNCTION IF EXISTS FnRandomPlaque;

DELIMITER $$
CREATE FUNCTION FnRandomPlaque()
    RETURNS VARCHAR(6)
BEGIN
    DECLARE plaque VARCHAR(6) DEFAULT "";
	SELECT concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
			substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
			substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*25+1, 1),
			substring('0123456789', rand()*9+1, 1),
			substring('0123456789', rand()*9+1, 1),
			substring('0123456789', rand()*9+1, 1))
			into @idVehicule;

	SET @rcount = -1;
	SELECT COUNT(*) INTO @rcount FROM `tblVehicule` WHERE `idVehicule` = @idVehicule ;

	IF @rcount = 0 THEN
		SET plaque = @idVehicule ;
	END IF ;

    RETURN plaque ;
END$$
DELIMITER ;

-- ---------------------------------------------------------------
-- Fonction pour retourner le NOMBRE ABONNEMENTS PERSONNE/SOCIETE
-- ---------------------------------------------------------------
DROP FUNCTION IF EXISTS FnNbAbon;

DELIMITER $$
CREATE FUNCTION FnNbAbon(p_DateDebut Date, p_DateFin Date, p_TypeAbonne char)
    RETURNS int
BEGIN
    DECLARE v_nb int;
SELECT count(*) FROM tblTransaction as trans
        INNER JOIN tblAbonnement as abonnem
        ON trans.idTransaction = abonnem.idTransaction
        INNER JOIN tblAbonne as abon
        ON abonnem.idAbonne = abon.idAbonne
    WHERE abon.typeAbonne = p_TypeAbonne AND abonnem.dateDebutAbonne <= p_DateFin AND abonnem.dateDebutAbonne > p_DateDebut
    INTO v_nb;

    IF (v_nb is null) THEN
		SET v_nb = 0;
    END IF ;

    RETURN v_nb ;
END$$
DELIMITER ;

-- -----------------------------------------------------------
-- Fonction pour connaitre SOMME ABONNEMENTS PERSONNE/SOCIETE
-- -----------------------------------------------------------
DROP FUNCTION IF EXISTS FnSommeAbon;

DELIMITER $$
CREATE FUNCTION FnSommeAbon(p_DateDebut Date, p_DateFin Date, p_TypeAbonne char)
    RETURNS int
BEGIN
    DECLARE v_somme int;
SELECT sum(trans.montant) FROM tblTransaction as trans
        INNER JOIN tblAbonnement as abonnem
        ON trans.idTransaction = abonnem.idTransaction
        INNER JOIN tblAbonne as abon
        ON abonnem.idAbonne = abon.idAbonne
    WHERE abon.typeAbonne = p_TypeAbonne AND abonnem.dateDebutAbonne <= p_DateFin AND abonnem.dateDebutAbonne > p_DateDebut
    INTO v_somme;

    IF (v_somme is null) THEN
		SET v_somme = 0;
    END IF ;

    RETURN v_somme ;
END$$
DELIMITER ;

-- --------------------------------------------
-- Fonction pour connaitre NOMBRE OCCASIONNEL
-- --------------------------------------------
DROP FUNCTION IF EXISTS FnNbOcca;

DELIMITER $$
CREATE FUNCTION FnNbOcca(p_DateDebut Date, p_DateFin Date)
    RETURNS int
BEGIN
    DECLARE v_nb int;
SELECT count(*) FROM tblTransaction as trans
        INNER JOIN tblOccasionnel as occa
        ON trans.idOccas = occa.idOccas
    WHERE CAST(occa.dateFinOccas as Date) <= p_DateFin AND CAST(occa.dateDebutOccas as Date) >= p_DateDebut
    INTO v_nb;

    IF (v_nb is null) THEN
		SET v_nb = 0;
    END IF ;

    RETURN v_nb ;
END$$
DELIMITER ;

select FnNbOcca(curdate(),curdate());
-- --------------------------------------------
-- Fonction pour connaitre SOMME OCCASIONNEL
-- --------------------------------------------
DROP FUNCTION IF EXISTS FnSommeOcca;

DELIMITER $$
CREATE FUNCTION FnSommeOcca(p_DateDebut Date, p_DateFin Date)
    RETURNS int
BEGIN
    DECLARE v_nb int;
SELECT sum(trans.montant) FROM tblTransaction as trans
        INNER JOIN tblOccasionnel as occa
        ON trans.idOccas = occa.idOccas
    WHERE  CAST(occa.dateFinOccas as Date) <= p_DateFin AND CAST(occa.dateDebutOccas as Date) >= p_DateDebut
    INTO v_nb;

    IF (v_nb is null) THEN
		SET v_nb = 0;
    END IF ;

    RETURN v_nb ;
END$$
DELIMITER ;

select FnSommeOcca(curdate(), curdate());
call SortieOccasionnel('2018042225', 1, '4000000000000002');
update tblOccasionnel set dateFinOccas = '2018-04-24 10:10:10' where idOccas = '2018042225';
-- -----------------------------------------------------
-- Procédures ------------------------------------------
-- -----------------------------------------------------
-- ------------------------------------------------------------------------------
-- Procédure pour la création des places dans tblPlace selon le nombre et le type
-- ------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS InsertPlaces;

DELIMITER $$
CREATE PROCEDURE `InsertPlaces`(in p_nb int, in p_type varchar(25))
BEGIN
	DECLARE v_i int DEFAULT 1;

    WHILE v_i <= p_nb DO
		INSERT INTO tblPlace (typePlace,idAbonne) VALUES(p_type, null);
        SET v_i = v_i + 1;
	END WHILE;
END $$
DELIMITER ;

-- -------------------------------------------------------------------------
-- Procédure CompterPlaceDispo(), va retourner le nombre de place disponible
-- en tenant compte des places utilisés par les abonnés et occasionnels
-- -------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS CompterPlaceDispo;

DELIMITER $$
CREATE PROCEDURE `CompterPlaceDispo`()
BEGIN
	DECLARE v_nbOccas int;
	DECLARE v_nbAbon int;
	DECLARE v_nbTotal int;

	SET v_nbOccas = FnNbPlaceOccas();
	SET v_nbAbon = FnNbPlaceAbonne();
    SET v_nbTotal = 2500 - v_nbOccas -v_nbAbon;
    SELECT v_nbTotal as 'Stationnement disponible';
END$$
DELIMITER ;

#call CompterPlaceDispo();

-- -----------------------------------------------------------------------------------
-- Procédure SommesPercuesParType(), va calculer les montants percus par type d'usager
-- en se basant sur des dates fournis
-- -----------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS SommesPercuesParType;

DELIMITER $$
CREATE PROCEDURE `SommesPercuesParType`(IN p_dateDebut Date, IN p_dateFin Date)
BEGIN
    DECLARE v_type varchar(8);
    DECLARE v_nbAbon int;
    DECLARE v_nbOccas int;
    DECLARE v_somme decimal(9,2);
    DECLARE v_total int;

    SELECT * FROM tblAbonne as ab
		INNER JOIN tblAbonnement as abt
        ON ab.idAbonne = abt.idAbonne
        WHERE abt.dateDebutAbonne >= p_dateDebut and abt.dateFinAbonne <= p_dateFin;
END$$
DELIMITER ;

#call SommesPercuesParType('2018-01-01', curdate());

-- -----------------------------------------------------
-- Procédure AjoutAbonne(), va faire l'ajout d'un abonné
-- -----------------------------------------------------
DROP PROCEDURE IF EXISTS AjoutAbonne;

DELIMITER $$
CREATE PROCEDURE `AjoutAbonne`(in p_nomAbonne varchar(50), in p_prenomAbonne varchar(25), in p_codePostal varchar(7),
								in p_telephone varchar(15), in p_courriel varchar(50), in p_typeAbonne char(1), in p_idVille int)
BEGIN
    SET @abCount = -1;
	SELECT COUNT(*) INTO @abCount FROM `tblAbonne` WHERE `nomAbonne` = p_nomAbonne and `prenomAbonne` = p_prenomAbonne
														or `nomAbonne` = p_nomAbonne and `prenomAbonne` is null;
     SELECT COUNT(*) INTO @abCount FROM tblAbonne as abon WHERE abon.nomAbonne = p_nomAbonne and abon.prenomAbonne = p_prenomAbonne and abon.telephone = p_telephone
                                                        or abon.nomAbonne = p_nomAbonne and abon.prenomAbonne is null and abon.telephone = p_telephone;
	IF @abCount = 0 THEN
		INSERT INTO tblAbonne (nomAbonne,prenomAbonne,codePostal,telephone,courriel,typeAbonne,idVille)
		VALUES(p_nomAbonne,p_prenomAbonne,p_codePostal,p_telephone,p_courriel,p_typeAbonne,p_idVille);
	ELSE
		SELECT 'Abonné déjà existant';
	END IF ;
END$$
DELIMITER ;

use TP3;

#select * from tblAbonne order by nomAbonne desc;
#call AjoutAbonne('Leblanc', 'Robby', 'Q4A2O7', '1-195-388-6491', 'nec@Nulla.co.uk', 'S', 3);

-- -----------------------------------------------------------------------------------------------------------------------
-- Procédure AjoutTransactionAbonne(), va ajouter la transaction pour l'action d'abonnement  et retourner l'id de celle-ci
-- -----------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS AjoutTransactionAbonne;

DELIMITER $$
CREATE PROCEDURE `AjoutTransactionAbonne`(in p_duree int, in p_numPaiement varchar(16), in p_idPaiement int, out LIDTRANSACABON int)
BEGIN
	DECLARE v_tMois int DEFAULT 240;
    DECLARE v_tAn int DEFAULT 2500;
    DECLARE v_mTotal int;
    DECLARE v_montant decimal(6,2);
    IF (p_duree > 11) THEN
        SET v_mTotal = CEIL(p_duree / 12) * v_tAn;
	ELSE
		SET v_mTotal = p_duree * v_tMois;
	END IF;

    SET v_montant = v_mTotal;

    INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
    VALUES(v_montant,p_numPaiement,p_idPaiement,null,null);

    SET LIDTRANSACABON = LAST_INSERT_ID();
END$$
DELIMITER ;

-- --------------------------------------------------
-- Procédure AjoutAbonnement(), va créer l'abonnement
-- --------------------------------------------------
DROP PROCEDURE IF EXISTS AjoutAbonnement;

DELIMITER $$
CREATE PROCEDURE `AjoutAbonnement`(in p_dateDebut Date, in p_duree int, in p_idAbonne int, in p_idPaiement int, in p_numPaiement varchar(16)
									, in p_idVehicule varchar(7), in p_idPlace smallint)
BEGIN
    DECLARE v_idTransac int;
    DECLARE v_dateFin date;

    SET @abtCount = -1;
    SELECT COUNT(*) INTO @abtCount FROM tblAbonnement where idVehicule = p_idVehicule and dateFinAbonne >= p_dateDebut
									or idPlace = p_idPlace and dateFinAbonne >= p_dateDebut;

	IF (@abtCount = 0) THEN
		SET v_dateFin = DATE_ADD(p_dateDebut, INTERVAL p_duree MONTH);
		-- Appele la procedure pour générer la transaction de l'abonnement
		call AjoutTransactionAbonne(p_duree, p_numPaiement, p_idPaiement, @LIDTRANSACABON);

		INSERT INTO tblAbonnement (dateDebutAbonne,dateFinAbonne,idAbonne,idVehicule,idPlace,idTransaction)
		VALUES(p_dateDebut,v_dateFin,p_idAbonne,p_idVehicule,p_idPlace,@LIDTRANSACABON);

		UPDATE tblTransaction set idAbonnement = LAST_INSERT_ID() where idTransaction = @LIDTRANSACABON;
	ELSE
		SELECT 'Un abonnement avec la même plaque ou place est toujours actif!';
	END IF;
END$$
DELIMITER ;

#select 'ALLO';
#SELECT COUNT(*) INTO @abtCount FROM tblAbonnement where idVehicule = 'XJA029' and dateFinAbonne >= '2018-10-20' or idPlace = 526 and dateFinAbonne >= '2018-10-20';
#SELECT * FROM tblAbonnement where idVehicule = 'XJA028' and dateFinAbonne >= '2018-10-20';
#SELECT @abtCount;
#SELECT * from tblAbonnement where idVehicule = 'XJA028';
#call AjoutAbonnement(curdate(), 10, 1, 4, null, 'XJA028', 414);
#update tblAbonnement set dateFinAbonne = curdate()-1 where idAbonnement = 15;
#SELECT * from tblAbonnement where idPlace = 1996;

-- ----------------------------------------------------------------------------------------
-- Procédure AjoutTransactionOcaas(), va ajouter la transaction pour l'action d'occasionnel
-- ----------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS AjoutTransactionOccas;

DELIMITER $$
CREATE PROCEDURE `AjoutTransactionOccas`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16), out LIDTRANSACOCCAS int)
BEGIN
    DECLARE v_montantOccas decimal(6,2);
    DECLARE v_dateDebut datetime;

    SELECT dateDebutOccas FROM tblOccasionnel AS occas
		INNER JOIN tblTransaction as trans
		ON occas.idOccas = trans.idOccas
		WHERE occas.idOccas = p_idOccas
    INTO @v_dateDebut;

    #SET v_montantOccas = FnCalculeMontant(@v_dateDebut, now());
    SET v_montantOccas = FnCalculerMontant(p_idOccas);

    INSERT INTO tblTransaction (montant,numPaiement,idPaiement,idAbonnement,idOccas)
    VALUES(v_montantOccas,p_numPaiement,p_idPaiement,null,p_idOccas);

    SET LIDTRANSACOCCAS = LAST_INSERT_ID();
END$$
DELIMITER ;

-- ---------------------------------------------------------------
-- Procédure AjoutOccasionnel(), va créer l'ajout d'un occasionnel
-- ---------------------------------------------------------------
DROP PROCEDURE IF EXISTS AjoutOccasionnel;

DELIMITER $$
CREATE PROCEDURE `AjoutOccasionnel`()
BEGIN

	DECLARE v_nbIdTotal int DEFAULT 0;
	DECLARE v_nbIdAvant int DEFAULT 0;
    DECLARE v_idOccas varchar(12);

    SELECT count(*) into v_nbIdTotal
    FROM tblOccasionnel;
    SELECT count(*) into v_nbIdAvant
    FROM tblOccasionnel WHERE TIMESTAMPDIFF(DAY, dateDebutOccas, now() != 0);

    SET v_idOccas = concat(DATE_FORMAT(curdate(), '%Y%m%d'), v_nbIdTotal - v_nbIdAvant + 1);

    INSERT INTO tblOccasionnel VALUES(v_idOccas,now(),null,null);

END$$
DELIMITER ;

-- --------------------------------------------------------------------------------------
-- Procédure SortieOccasionnel(), va mettre à jour l'occasionel lorsque la voiture quitte
-- --------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS SortieOccasionnel;

DELIMITER $$
CREATE PROCEDURE `SortieOccasionnel`(in p_idOccas varchar(12), in p_idPaiement int, in p_numPaiement varchar(16))
BEGIN
	SET @occasCount = -1;
    SELECT COUNT(*) INTO @occasCount FROM tblOccasionnel where idOccas = p_idOccas and dateFinOccas is not null;
    IF (@occasCount = 0) THEN
		call AjoutTransactionOccas(p_idOccas, p_idPaiement, p_numPaiement, @LIDTRANSACOCCAS);

		UPDATE tblOccasionnel set dateFinOccas = now(), idTransaction = @LIDTRANSACOCCAS WHERE idOccas = p_idOccas;
	ELSE
		SELECT 'Cet occasionel a déjà quitté';
	END IF;
END$$
DELIMITER ;

#update tblOccasionnel set dateDebutOccas = '2018-04-21 15:00:00' where idOccas = '201804212';
#call SortieOccasionnel('201804216', 4, null);
#call SortieOccasionnel('201804212', 1, '4000000000000002');
#select * from tblTransaction where idTransaction = 67;
#select * from tblOccasionnel where idOccas = '201804216';
#select * from tblOccasionnel;

-- -------------------------------------------------------------------------
-- Procédure DepotQuotidien(), va retourner les achats par type de paiement et
-- par type d'utilisateur
-- -------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS DepotQuotidien;
DROP TEMPORARY TABLE IF EXISTS TMP_depot;

DELIMITER $$
CREATE PROCEDURE `DepotQuotidien`(p_DateVerif Date)
BEGIN
    DECLARE v_CrOcca int;
    DECLARE v_CrAbon int;
    DECLARE v_CoOcca int;
    DECLARE v_CoAbon int;
	set v_CrOcca = FnCreditOcca(p_DateVerif);
    set v_CrAbon = FnCreditAbonne(p_DateVerif);
    set v_CoOcca = FnComptantOcca(p_DateVerif);
    set v_CoAbon = FnComptantAbonne(p_DateVerif);


    CREATE TEMPORARY TABLE TMP_depot (
    type_Depot varchar(25),
	Abonnements  DECIMAL(9,2),
	Occasionnels DECIMAL(9,2));

    INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
		VALUE ('Carte de crédit', v_CrAbon, v_CrOcca);
    INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
		VALUE ('Argent comptant', v_CoAbon, v_CoOcca);
    INSERT INTO TMP_depot (type_Depot, Abonnements, Occasionnels)
		VALUE ('Total', (v_CoAbon + v_CrAbon), (v_CoOcca + v_CrOcca));

	SELECT * From TMP_depot;

    DROP TEMPORARY TABLE TMP_depot;

END$$
DELIMITER ;

call DepotQuotidien(curdate());
#SELECT SUM(FnCreditOcca(curdate()) + FnComptantOcca(curdate()));

 -- --------------------------------------------------------------------------------
-- Procédure PlacesAbonnes() donne les places occupées par des abonnements à tout
-- moment. Ce moment doit être déterminé par une date passé en paramètre.
-- --------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS PlacesAbonnes;

DELIMITER $$
CREATE PROCEDURE `PlacesAbonnes`(p_Moment Datetime)
BEGIN
    SELECT place.idPlace as 'Place',
		concat(voiture.marque, ' ',voiture.modele, ', ',voiture.couleur, ', ',voiture.idVehicule) as 'Véhicule',
        abon.dateDebutAbonne as 'Date de début', abon.dateFinAbonne as 'Date de fin'
        FROM tblAbonnement as abon
        INNER join tblVehicule as voiture
        ON abon.idVehicule = voiture.idVehicule
        INNER join tblPlace as place
        ON abon.idPlace = place.idPlace
    UNION
    SELECT COUNT(*) as 'Total'
    FROM tblPlace as place
        INNER join tblAbonnement as abon
        ON abon.idPlace = place.idPlace
        INNER join tblVehicule as voiture
        ON abon.idVehicule = voiture.idVehicule;

END$$
DELIMITER ;

call PlacesAbonnes(now());
-- --------------------------------------------------------------------------------
-- Procédure ventilation donne la ventilation dans un tableau des sommes
-- perçues par type de personne (physique ou société), les véhicules occasionnels.
-- --------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS ventilation;
DROP TEMPORARY TABLE IF EXISTS TMP_ventilation;

DELIMITER $$
CREATE PROCEDURE `ventilation`(p_DateDebut Date, p_DateFin Date)
BEGIN
    DECLARE v_NnOcca int;
    DECLARE v_NbAbonSoc int;
    DECLARE v_NbAbonPers int;
    DECLARE v_SommeOcca DEC(9,2);
    DECLARE v_SommeAbonSoc DEC(9,2);
    DECLARE v_SommeAbonPers DEC(9,2);

    SET v_NnOcca = FnNbOcca(p_DateDebut, p_DateFin);
    SET v_NbAbonSoc = FnNbAbon(p_DateDebut, p_DateFin, 's');
    SET v_NbAbonPers = FnNbAbon(p_DateDebut, p_DateFin, 'p');
    SET v_SommeOcca = FnSommeOcca(p_DateDebut, p_DateFin);
    SET v_SommeAbonSoc = FnSommeAbon(p_DateDebut, p_DateFin, 's');
    SET v_SommeAbonPers = FnSommeAbon(p_DateDebut, p_DateFin, 'p');

    CREATE TEMPORARY TABLE TMP_ventilation (
	TypeDeClient varchar(25),
    NbAbonnements INT,
    SommeAbonnements  DECIMAL(9,2),
    NbOccasionnels INT,
    SommeOccasionnels DECIMAL(9,2));

    INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
        VALUE ('Personne', v_NbAbonPers, v_SommeAbonPers, v_NnOcca, v_SommeOcca);
    INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
        VALUE ('Société', v_NbAbonSoc, v_SommeAbonSoc, null, null);
    INSERT INTO TMP_ventilation (TypeDeClient, NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels)
        VALUE ('Total', (v_NbAbonPers+v_NbAbonSoc), (v_SommeAbonPers+v_SommeAbonSoc), v_NnOcca, v_SommeOcca);

    SELECT TypeDeClient as '', NbAbonnements, SommeAbonnements, NbOccasionnels, SommeOccasionnels From TMP_ventilation;

    DROP TEMPORARY TABLE TMP_ventilation;

END$$
DELIMITER ;

call ventilation(curdate()-1, curdate());

###############################
### INSERTIONS DES DONNÉÉES ###
###############################

SET FOREIGN_KEY_CHECKS = 1;

use TP3;

-- appele de la procédure pour faire l'insertion des places
call InsertPlaces(500, 'plein air');
call InsertPlaces(2000, 'couvert');

-- insertion manuelle, car pas besoin de beaucoup de ville
INSERT INTO tblVille (nomville) VALUES("Québec"),("Montréal"),("Laval"),("Shannon"),("St-Gabriel-de-Valcartier"),("Val-Bélair"),("Loretteville"),("Charlesbourg"),("Beauport");

-- insertion manuelle avec des données produite par generatedata
INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
	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");
INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
	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");
INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
	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");
INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
	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");
INSERT INTO tblAbonne (`nomAbonne`,`prenomAbonne`,`codePostal`,`telephone`,`courriel`,`typeAbonne`,`idVille`)
	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");

INSERT 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");
INSERT 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");
INSERT 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");
INSERT 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");
INSERT 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");
INSERT 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");
INSERT 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");
INSERT 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");
INSERT 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");
INSERT 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");

select count(*) from tblVehicule;

-- insertion manuelle, car pas assé de donnée à générer
INSERT INTO tblMethodPaiement (typePaiement,descPaiement) VALUES("V","Visa"),("M","MasterCard"),("X","Amex"),("A","Comptant"),("C","Chèque");

-- -------------------------------------------------------------------
-- Procédure pour ajouter des occasionnels en lot selon une qte donnée
-- -------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `AjoutOccasEnLot`(in p_nb int)
BEGIN
	DECLARE v_i int DEFAULT 1;
	WHILE v_i <= p_nb DO
		call AjoutOccasionnel();
        SET v_i = v_i + 1;
	END WHILE;
END $$
DELIMITER ;

call AjoutOccasEnLot(150);

#select count(*) from tblOccasionnel;

-- -----------------------------------------------------------------
-- Procédure pour ajouter des Abonnement en lot selon une qte donnée
-- -----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `AjoutAbonEnLot`(in p_nb int)
BEGIN
	DECLARE v_i int DEFAULT 1;
    DECLARE v_idAbon int;
    DECLARE v_nbDuree int;
    DECLARE v_idPaiement int;
    DECLARE v_idVehicule varchar(6);
    DECLARE v_idPlace int;
    WHILE v_i <= p_nb DO
		SET v_idAbon = FLOOR(RAND()*(50-1+1))+1;
        SET v_nbDuree = FLOOR(RAND()*(12-1+1))+1;
        SET v_idPaiement = FLOOR(RAND()*(5-1+1))+1;
        SET v_idVehicule = (SELECT idVehicule FROM tblVehicule ORDER BY RAND() LIMIT 1);
        SET v_idPlace = FLOOR(RAND()*(2500-1+1))+1;
        IF (v_idPaiement = 4) THEN
			call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,null,v_idVehicule,v_idPlace);
		ELSE
			call AjoutAbonnement(curdate(),v_nbDuree,v_idAbon,v_idPaiement,'4000000000000002',v_idVehicule,v_idPlace);
		END IF;
        SET v_i = v_i + 1;
	END WHILE;
END $$
DELIMITER ;

call AjoutAbonEnLot(100);

############################
########## TESTS ###########
############################

SELECT COUNT(*) as `Nombre d'abonnement` from tblAbonnement;
SELECT COUNT(*) as `Nombre d'occasionel` from tblOccasionnel;
call CompterPlaceDispo();
select FnNbAbon(curdate(), curdate(), 'P');
#select * from tblAbonne where nomAbonne = 'Harveys';
#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);