--
-- Déclencheurs `reservation`
--
DROP TRIGGER IF EXISTS `UpdateFactureOnInsert`;
DELIMITER //
CREATE TRIGGER `UpdateFactureOnInsert` AFTER INSERT ON `reservation`
 FOR EACH ROW BEGIN
    DECLARE quota, montant, tarif INT;
    DECLARE nombreHeure INT DEFAULT (SELECT COUNT(heure.numero) FROM heure WHERE     heure.code = NEW.code);
    DECLARE mois INT DEFAULT (SELECT heure.mois FROM heure WHERE heure.code = NEW.code     LIMIT 1);
    DECLARE annee INT DEFAULT (SELECT heure.annee FROM heure WHERE heure.code = NEW.code     LIMIT 1);
    DECLARE identifiant INT DEFAULT (SELECT facture.identifiant FROM facture WHERE     facture.association = NEW.association
        AND facture.mois = mois
        AND facture.annee = annee LIMIT 1);  

    IF (identifiant = null) THEN
        SET identifiant = ((SELECT MAX(facture.identifiant) FROM facture) +1);
        INSERT INTO facture (facture.association, facture.annee, facture.mois,     facture.identifiant, facture.quota, facture.montant)
            VALUES (NEW.association, annee, mois, identifiant, 20, 0);
    END IF;

    SET quota = (SELECT facture.quota FROM facture WHERE facture.identifiant =     identifiant 
        AND facture.association = NEW.association
        AND facture.mois = mois
        AND facture.annee = annee);
    SET montant = (SELECT facture.montant FROM facture WHERE facture.identifiant = identifiant 
        AND facture.association = NEW.association
        AND facture.mois = mois
        AND facture.annee = annee);

    IF (nombreHeure >= quota) THEN
        SET quota = quota - nombreHeure;
    ELSE
        SET tarif = (SELECT salle.tarif FROM salle WHERE salle.numero = NEW.numero);
        SET montant = montant + (nombreHeure - quota) * tarif;
        SET quota = 0;
    END IF;

    UPDATE facture SET facture.quota = quota, facture.montant = montant WHERE     facture.association = NEW.association 
        AND facture.mois = mois
        AND facture.annee = annee
        AND facture.identifiant = identifiant;


END
//
DELIMITER ;
	
-- phpMyAdmin SQL Dump
-- version 4.0.4
-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le: Mer 02 Avril 2014 à 17:00
-- Version du serveur: 5.6.12-log
-- Version de PHP: 5.4.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Base de données: `reserv`
--
CREATE DATABASE IF NOT EXISTS `reserv` DEFAULT CHARACTER SET latin1 COLLATE     latin1_swedish_ci;
USE `reserv`;

-- --------------------------------------------------------

--
-- Structure de la table `association`
--

CREATE TABLE IF NOT EXISTS `association` (
  `association` int(11) NOT NULL AUTO_INCREMENT,
  `libelle` char(32) NOT NULL,
  PRIMARY KEY (`association`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

--
-- Structure de la table `facture`
--

CREATE TABLE IF NOT EXISTS `facture` (
  `association` int(11) NOT NULL,
  `annee` int(11) NOT NULL,
  `mois` int(11) NOT NULL,
  `identifiant` int(11) NOT NULL,
  `quota` int(11) NOT NULL,
  `montant` int(11) NOT NULL,
  PRIMARY KEY (`association`,`mois`,`annee`,`identifiant`),
  KEY `i_fk_facture_mois` (`mois`,`annee`),
  KEY `i_fk_facture_association` (`association`),
  KEY `facture_ibfk_1` (`annee`,`mois`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `heure`
--

CREATE TABLE IF NOT EXISTS `heure` (
  `numero` int(11) NOT NULL,
  `association` int(11) NOT NULL,
  `code` int(11) NOT NULL,
  `annee` int(11) NOT NULL,
  `mois` int(11) NOT NULL,
  `jour` int(11) NOT NULL,
  `heure` int(11) NOT NULL,
  PRIMARY KEY (`numero`,`association`,`code`,`annee`,`mois`,`jour`,`heure`),
  KEY `i_fk_heure_mois` (`annee`,`mois`),
  KEY `i_fk_heure_reservation` (`numero`,`association`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `mois`
--

CREATE TABLE IF NOT EXISTS `mois` (
  `mois` int(11) NOT NULL,
  `annee` int(11) NOT NULL,
  PRIMARY KEY (`annee`,`mois`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `reservation`
--

CREATE TABLE IF NOT EXISTS `reservation` (
  `numero` int(11) NOT NULL,
  `association` int(11) NOT NULL,
  `code` int(11) NOT NULL,
  PRIMARY KEY (`numero`,`association`,`code`),
  KEY `i_fk_reservation_association` (`association`),
  KEY `i_fk_reservation_salle` (`numero`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

--
-- Structure de la table `salle`
--

CREATE TABLE IF NOT EXISTS `salle` (
  `numero` int(11) NOT NULL AUTO_INCREMENT,
  `capacite` int(11) NOT NULL,
  `tarif` int(11) NOT NULL,
  PRIMARY KEY (`numero`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `facture`
--
ALTER TABLE `facture`
  ADD CONSTRAINT `facture_ibfk_1` FOREIGN KEY (`annee`, `mois`) REFERENCES `mois`     (`annee`, `mois`),
  ADD CONSTRAINT `facture_ibfk_2` FOREIGN KEY (`association`) REFERENCES `association`     (`association`);

--
-- Contraintes pour la table `reservation`
--
ALTER TABLE `reservation`
  ADD CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`association`) REFERENCES     `association` (`association`),
  ADD CONSTRAINT `reservation_ibfk_2` FOREIGN KEY (`numero`) REFERENCES `salle`     (`numero`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
	
IF (identifiant = null)
	
IF (identifiant IS NULL)
	
DECLARE nombreHeure INT;
SELECT COUNT(heure.numero) INTO nombreHeure
  FROM heure
 WHERE heure.code = NEW.code;
	
AND facture.mois = mois
	
AND facture.mois = fracture.mois
	
AND facture.mois IS NOT NULL