-------------------------------------------------------------------------
--Fonction permettant de récuperer la liste des salles ayant une capacité
--supérieure à une certaine valeur
-------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION GetSalleCapaciteSuperieurA(int) RETURNS SETOF Salle AS '
    SELECT * FROM Salle WHERE Capacite > $1;
' LANGUAGE SQL;

SELECT * FROM GetSalleCapaciteSuperieurA(300) ;




------------------------------------------------------------------------
--Fonction permettant de récuperer l'identificateur du département à partir
--de son nom
-------------------------------------------------------------------------


CREATE OR REPLACE FUNCTION GetDepartement_ID(text) RETURNS integer AS
'SELECT Departement_ID FROM Departement WHERE Nom_Departement = $1'
LANGUAGE SQL;

SELECT Nom, Prenom 
 FROM Enseignant 
  WHERE Departement_ID IN (SELECT * FROM GetDepartement_ID('INFO'));


----------------------------------------------------------------------------
--Fonction retournant la ou les identificateurs de reservations existantes
--ne permettant pas de reserver une salle.
--On donne en paramètre de la fonction, la salle (batiment et numéro de salle)
--la date de reservation et l'heure de debut (representée par $4 dans la requête) 
--et de fin de reservation (representée par $5 dans la requête).
--La fonction vérifie que le créneau horaire choisit n'est pas
--contenu dans le créneau horaire d'une reservation existante
--ou ne chevauche pas une le créneau horaire d'une reservation existante
----------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION PossibiliteResa(text,text,date,time,time) RETURNS integer AS
'SELECT Reservation_ID
  FROM Reservation 
   WHERE (Batiment = $1 AND Numero_Salle = $2 AND Date_Resa = $3 AND Heure_Debut < $4  AND $4 < Heure_Fin) 
     OR  (Batiment = $1 AND Numero_Salle = $2 AND Date_Resa = $3 AND $4 < Heure_Debut AND Heure_Debut < $5 AND Heure_Fin > $5)
     OR  (Batiment = $1 AND Numero_Salle = $2 AND Date_Resa = $3 AND Heure_Debut < $4 AND $4 < Heure_Fin AND Heure_Fin < $5)
     OR  (Batiment = $1 AND Numero_Salle = $2 AND Date_Resa = $3 AND Heure_Debut > $4 AND Heure_Fin < $5)
     OR  (Batiment = $1 AND Numero_Salle = $2 AND Date_Resa = $3 AND Heure_Debut = $4 AND Heure_Fin = $5)
     OR  (Batiment = $1 AND Numero_Salle = $2 AND Date_Resa = $3 AND Heure_Debut = $4)
     OR  (Batiment = $1 AND Numero_Salle = $2 AND Date_Resa = $3 AND Heure_Fin = $5)'
LANGUAGE SQL;


SELECT PossibiliteResa('B','022','04/11/2003','09:00:00','18:00:00');



------------------------------------------------------------------------
--Fonction trigger et TRIGGER permettant d'inserer une reservation dans 
--la base, en verifiant que la reservation est possible
-------------------------------------------------------------------------

--**************************************************************************
--Pour activer le langage PL/pgSQL il faut executer la commande
--createlang plpgsql nom_base
--**************************************************************************


CREATE OR REPLACE FUNCTION FunctionTriggerReservation() RETURNS trigger AS
' DECLARE 
  resa Reservation.Reservation_ID%TYPE;
  
  BEGIN
   SELECT INTO resa Reservation_ID
    FROM Reservation 
     WHERE (Batiment = NEW.Batiment AND Numero_Salle = NEW.Numero_Salle AND Date_Resa = NEW.Date_Resa AND Heure_Debut < NEW.Heure_Debut AND NEW.Heure_Debut < Heure_Fin) 
      OR  (Batiment = NEW.Batiment AND Numero_Salle = NEW.Numero_Salle AND Date_Resa = NEW.Date_Resa AND NEW.Heure_Debut < Heure_Debut AND Heure_Debut < NEW.Heure_Fin AND Heure_Fin > NEW.Heure_Fin)
      OR  (Batiment = NEW.Batiment AND Numero_Salle = NEW.Numero_Salle AND Date_Resa = NEW.Date_Resa AND Heure_Debut < NEW.Heure_Debut  AND NEW.Heure_Debut < Heure_Fin AND Heure_Fin < NEW.Heure_Fin)
      OR  (Batiment = NEW.Batiment AND Numero_Salle = NEW.Numero_Salle AND Date_Resa = NEW.Date_Resa AND Heure_Debut = NEW.Heure_Debut  AND Heure_Fin = NEW.Heure_Fin)
      OR  (Batiment = NEW.Batiment AND Numero_Salle = NEW.Numero_Salle AND Date_Resa = NEW.Date_Resa AND Heure_Debut > NEW.Heure_Debut  AND Heure_Fin < NEW.Heure_Fin)
      OR  (Batiment = NEW.Batiment AND Numero_Salle = NEW.Numero_Salle AND Date_Resa = NEW.Date_Resa AND Heure_Debut = NEW.Heure_Debut)
      OR  (Batiment = NEW.Batiment AND Numero_Salle = NEW.Numero_Salle AND Date_Resa = NEW.Date_Resa AND Heure_Fin = NEW.Heure_Fin);

  IF FOUND THEN
    RAISE EXCEPTION ''Reservation impossible, salle occupée à la date et aux horaires demandés'';
  ELSE RETURN NEW;
  END IF;

 END;' 
LANGUAGE 'plpgsql';


--DROP TRIGGER InsertionReservation ON Reservation;

CREATE TRIGGER InsertionReservation
 BEFORE INSERT ON Reservation
  FOR EACH ROW
   EXECUTE PROCEDURE FunctionTriggerReservation();
