-- On gère des classes dans un collège. Une classe est composée de collégiens. Un collégien est décrit
-- par la relation Collegien (idEtudiant,Nom,Prenom)

CREATE TABLE Collegien
-- Création de table
(
idEtudiant number,
nom varchar2(20),
prenom varchar2(20),
CONSTRAINT pk_collegien PRIMARY KEY(idEtudiant)
);


-- Ecrire une procédure qui reçoit un triplet idEtudiant,Nom,Prenom et qui
--  insère un nouveau collégien si la clé idEtudiant n'existe pas dans la table ;
--  met à jour le nom et lé prénom d'un nouveau collégien si la clé idEtudiant existe dans la table.

CREATE PROCEDURE pcdMajCollegien(pidEtudiant number, pnom varchar2, pprenom
varchar2) IS
BEGIN
  INSERT INTO collegien VALUES(pidEtudiant, pnom, pprenom);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
      UPDATE collegien SET nom = pnom, prenom = pprenom WHERE idEtudiant =
pidEtudiant;
END;
/

---- Le TRIGGER maj_lignecde
CREATE OR REPLACE TRIGGER maj_lignecde
before INSERT on ligne_commande
FOR each row

declare
  ok number ;
  wdelta_stock produit.stockprod%type ;
  wprixprod produit.prixprod%type ;
  wstockprod produit.stockprod%type ;
  erreur_stock exception ;
BEGIN
  SELECT stockprod,stockprod-:new.quantiteprod ,prixprod
     INTO wstockprod,wdelta_stock, wprixprod FROM produit
     WHERE idproduit = :new.idproduit ;
  
  IF wdelta_stock < 0 THEN raise erreur_stock ;
  END IF ;

  UPDATE commande SET montanttotalcom=montanttotalcom
+(wprixprod*:new.quantiteprod)
  WHERE idcommande=:new.idcommande ;

  UPDATE produit SET stockprod = stockprod-:new.quantiteprod WHERE
    idproduit=:new.idproduit ;

	exception
when no_data_found THEN
    dbms_output.put_line('Echec produit');
    raise_application_error(-20001,'erreur prod') ;
when erreur_stock THEN
    dbms_output.put_line('Echec');
    ok:=ecriture_erreur_commande(:new.idcommande,:new.idlig,:new.idproduit,:new.quantiteprod,wstockprod);
    raise_application_error(-20001,'erreur stock') ;
when others THEN null ;
END;
/

CREATE OR REPLACE FUNCTION ecriture_erreur_commande(pidcommande
varchar2,plig varchar2,pidproduit varchar2,pquantiteprod
number,pstockprod number)
return number
is
PRAGMA AUTONOMOUS_TRANSACTION; -- transaction indépENDante
BEGIN
INSERT INTO ligne_erreur VALUES
(pidcommande,plig,pidproduit,pquantiteprod,pstockprod) ;
commit;
return 1;
END;
/

-- Ecrire la procédure qui acceptera en paramètre le numéro de la commande et qui permettra d'afficher
-- la facture.

CREATE OR REPLACE PROCEDURE reporting_facture(pidcommande varchar2)
is
-- déclaration des variables utiles
   vidcommande commande.idcommande%type ;
   vdatecde commande.datecde%type ;
   vmontanttot commande.montanttotalcom%type ;
   vmontant commande.montanttotalcom%type ;

 -- déclaration du curseur qui contiENDra les lignes de commande
 -- pour une commande (vidcommande) concernée

   CURSOR cur_lignecde(vidcommande varchar2) is
     SELECT idlig,quantiteprod,idproduit
     FROM ligne_commande
     WHERE idcommande = vidcommande ;
	 
BEGIN
-- on initialise la variable vidcommande avec la valeur passée en paramètre

   vidcommande := pidcommande ;

-- on récupère les valeurs de la TABLE commande pour la commande -- --

   SELECT datecde,montanttotalcom INTO vdatecde,vmontanttot
   FROM commande
   WHERE idcommande = vidcommande ;

-- on affiche l entête de la facture
   dbms_output.put_line('FACTURE client numéro : XXXXX') ;
   dbms_output.put_line('Commande numéro : '|| vidcommande);
   dbms_output.put_line('Date de la commande : '|| vdatecde);
   dbms_output.put_line('---------------------------------------------') ;
   dbms_output.put_line('') ;

-- on gère les lines de commande à l aide du curseur
   FOR vcur IN cur_lignecde(pidcommande) LOOP
-- pour chaque ligne on récupère le prix et on calcul le montan pour le produit commandé
   SELECT prixprod*vcur.quantiteprod INTO vmontant FROM produit WHERE
      idproduit=vcur.idproduit ;
-- on affiche les info demandés pour chaque produit commandé
   dbms_output.put_line(vcur.idlig|| ' ' ||vcur.idproduit||' '||vcur.quantiteprod||' '||vmontant);
   END LOOP ;
-- on affiche le pied de la facture
     dbms_output.put_line('---------------------------------------------') ;
     dbms_output.put_line('Montant total de la commande : '|| vmontanttot );
   exception
     when no_data_found THEN
     dbms_output.put_line('erreur dans le numéro de la commande') ;
END ;
/


