CREATE OR REPLACE TRIGGER CLIENTS_NOM_MAJ
BEFORE INSERT OR UPDATE ON CLIENTS
FOR EACH ROW
BEGIN
   :NEW.nom := UPPER(:NEW.nom);
END;
/

CREATE OR REPLACE TRIGGER CLIENTS_PRENOM_UCF
BEFORE INSERT OR UPDATE ON CLIENTS
FOR EACH ROW
BEGIN
   :NEW.prenom := INITCAP(:NEW.prenom);
END;
/

CREATE OR REPLACE TRIGGER LOCATION_INIT
BEFORE INSERT OR UPDATE ON LOCATION
FOR EACH ROW
BEGIN
IF INSERTING THEN
   :NEW.DATEDEB := TO_CHAR(SYSDATE, 'DD/MM/YYYY');
   :NEW.DATEFIN := NULL;
ELSE
   IF :OLD.DATEFIN IS NULL THEN
       :NEW.DATEFIN := TO_CHAR(SYSDATE, 'DD/MM/YYYY');
   ELSE
    RAISE_APPLICATION_ERROR(-20000, 'La date est déjà renseignée');
   END IF;
END IF;
END;
/

CREATE OR REPLACE TRIGGER LOCATION_UPD_FACTURE
BEFORE INSERT OR DELETE ON LOCATION
FOR EACH ROW
DECLARE
   nbLocDay NUMBER;
   prixLoca NUMBER;
   totalP NUMBER;
   nbrecap NUMBER;
BEGIN
   IF INSERTING THEN
    SELECT prix INTO prixLoca FROM PRIXLOC WHERE PRIXLOC.TypeV = :NEW.TypeV;
    SELECT COUNT(*) INTO nbLocDay FROM FACTURE_LOC WHERE FACTURE_LOC.Numcli = :NEW.Numcli AND TO_CHAR(FACTURE_LOC.datedeb, 'DD/MM/YYYY') = TO_CHAR(:NEW.datedeb,'DD/MM/YYYY');
    IF nbLocDay = 1 THEN
        UPDATE FACTURE_LOC SET PRIXF = PRIXF + prixLoca WHERE FACTURE_LOC.Numcli = :NEW.Numcli AND TO_CHAR(FACTURE_LOC.datedeb, 'DD/MM/YYYY') = TO_CHAR(:NEW.datedeb,'DD/MM/YYYY');
    ELSE
        INSERT INTO FACTURE_LOC VALUES(:NEW.Numcli, :NEW.datedeb, prixLoca);
    END IF;

    SELECT COUNT(*) INTO nbrecap FROM RECAP WHERE ANNEE = TO_CHAR(:NEW.datedeb, 'YYYY') AND MOIS = TO_CHAR(:NEW.datedeb, 'MM') AND TypeV = :NEW.typev;
    IF nbrecap > 0 THEN
        UPDATE RECAP SET NBLOC = NBLOC + 1 WHERE ANNEE = TO_CHAR(:NEW.datedeb, 'YYYY') AND MOIS = TO_CHAR(:NEW.datedeb, 'MM') AND TypeV = :NEW.typev;
    ELSE
        INSERT INTO RECAP(annee,mois,typev,nbloc) VALUES(TO_CHAR(:NEW.datedeb, 'YYYY'), TO_CHAR(:NEW.datedeb, 'MM'), :NEW.typeV, 1);
    END IF;

   END IF;


   IF DELETING THEN
    SELECT prix INTO prixLoca FROM PRIXLOC WHERE PRIXLOC.TypeV = :OLD.TypeV;
    SELECT prixf INTO totalP FROM FACTURE_LOC WHERE FACTURE_LOC.Numcli = :OLD.Numcli AND TO_CHAR(FACTURE_LOC.datedeb, 'DD/MM/YYYY') = TO_CHAR(:OLD.datedeb,'DD/MM/YYYY');
    IF totalP - prixLoca > 0 THEN
        UPDATE FACTURE_LOC SET PRIXF = PRIXF - prixLoca WHERE FACTURE_LOC.Numcli = :OLD.Numcli AND TO_CHAR(FACTURE_LOC.datedeb, 'DD/MM/YYYY') = TO_CHAR(:OLD.datedeb,'DD/MM/YYYY');
    ELSE
        DELETE FROM FACTURE_LOC WHERE Numcli = :OLD.Numcli AND TO_CHAR(datedeb, 'DD/MM/YYYY') = TO_CHAR(:OLD.datedeb,'DD/MM/YYYY');
    END IF;

    SELECT nbloc INTO nbrecap FROM RECAP WHERE ANNEE = TO_CHAR(:OLD.datedeb, 'YYYY') AND MOIS = TO_CHAR(:OLD.datedeb, 'MM') AND TypeV = :OLD.typev;
    IF nbrecap > 1 THEN
        UPDATE RECAP SET NBLOC = NBLOC - 1 WHERE ANNEE = TO_CHAR(:OLD.datedeb, 'YYYY') AND MOIS = TO_CHAR(:OLD.datedeb, 'MM') AND TypeV = :OLD.typev;
    ELSE
        DELETE FROM RECAP WHERE ANNEE = TO_CHAR(:OLD.datedeb, 'YYYY') AND MOIS = TO_CHAR(:OLD.datedeb, 'MM') AND TypeV = :OLD.typev;
    END IF;

   END IF;

END;
/


CREATE OR REPLACE TRIGGER LOCATION_NB_CLIENTS_NBLOC
BEFORE INSERT OR UPDATE OR DELETE ON LOCATION
FOR EACH ROW
BEGIN
IF INSERTING THEN
 UPDATE CLIENTS SET Nblocation = Nblocation + 1
 WHERE Numcli = :NEW.Numcli;
 END IF;
IF DELETING THEN
 IF :OLD.DATEFIN IS NULL THEN
    UPDATE CLIENTS SET NBLOCATION = NBLOCATION-1
    WHERE NUMCLI=:OLD.NUMCLI;
 END IF;
END IF;
IF UPDATING THEN
  IF :OLD.DATEFIN IS NULL AND :NEW.DATEFIN IS NOT NULL THEN
    UPDATE CLIENTS SET NBLOCATION = NBLOCATION-1
    WHERE NUMCLI=:OLD.NUMCLI;
  END IF;
END IF;
END;
/

/*CREATE OR REPLACE PROCEDURE INSERTION_CLIENT
(NOM VARCHAR, PRENOM VARCHAR)
IS
NUMERO CLIENTS.NUMCLI%TYPE;
NB NUMBER;
VAL NUMBER;
LONGUEUR NUMBER;
BEGIN
    SELECT COUNT(*) INTO NB FROM CLIENTS;
    IF NB = 0 THEN
        NUMERO :='C0001';
    ELSE
        SELECT MAX(TO_NUMBER(SUBSTR(NUMCLI,2,4))) INTO VAL
        FROM CLIENTS;
        VAL := VAL+1;
        LONGUEUR:=LENGTH(VAL);
        CASE LONGUEUR
        WHEN 1 THEN NUMERO:='C000' || VAL;
        WHEN 2 THEN NUMERO:='C00' || VAL;
        WHEN 3 THEN NUMERO:='C0' || VAL;
        WHEN 4 THEN NUMERO:='C' || VAL;
        END CASE;
    END IF;
    INSERT INTO CLIENTS VALUES(NUMERO, NOM, PRENOM,0);
END INSERTION_CLIENT;
/
*/

CREATE OR REPLACE PROCEDURE INSERTION_CLIENT
(NOM VARCHAR, PRENOM VARCHAR)
IS
LASTID CLIENTS.NUMCLI%TYPE;
BEGIN
  SELECT NVL(MAX(NUMCLI), 0) INTO LASTID FROM CLIENTS;
  LASTID := LASTID + 1;
  INSERT INTO CLIENTS VALUES(LASTID, NOM, PRENOM, 0);
END;
/

CREATE OR REPLACE PROCEDURE INSERTION_LOCATION
(NUMEROCLI NUMBER, TYPEVIDEO VARCHAR, NUMEROVIDEO NUMBER)
IS
NB NUMBER;
BEGIN
SELECT COUNT(*) INTO NB FROM LOCATION
WHERE TYPEV = TYPEVIDEO AND NUMERO = NUMEROVIDEO
AND DATEFIN IS NULL;
IF NB = 0 THEN
    INSERT INTO LOCATION (NUMCLI, TYPEV, NUMERO)
    VALUES (NUMEROCLI,TYPEVIDEO,NUMEROVIDEO);
ELSE
    DBMS_OUTPUT.PUT_LINE('ERROR : VIDEO DEJA LOUEE');
END IF;
END;
/

CREATE OR REPLACE FUNCTION GET_MONTH_LETTER
(date_input DATE) RETURN VARCHAR
IS
BEGIN
    RETURN(TO_CHAR(date_input, 'Month'));
END;
/

SELECT GET_MONTH_LETTER('26/11/2017') FROM dual;

CREATE OR REPLACE FUNCTION JOURLETTRE
(DATECHOIX DATE) RETURN VARCHAR
IS

BEGIN
RETURN(TO_CHAR(DATECHOIX,'Day'));
END;
/

SELECT JOURLETTRE('26/11/2017') FROM dual;

/*
CREATE OR REPLACE FUNCTION CALCUL(DATEF DATE)
RETURN NUMBER
IS
NB NUMBER;
BEGIN
    SELECT COUNT(*) INTO NB FROM FACTURE
    WHERE DATEFACT=DATEF;
    RETURN NB;
END CALCUL;
/*/
 SELECT * FROM DUAL;