# Exercices sur les vues 
# Définir une vue Vcommandes, qui augmente la table commande 
# avec le nom du client, le nb de lignes, les champs quantite et montant
CREATE OR REPLACE VIEW Vcommandes AS
SELECT C.numcommande AS No, C.idclient, DATE(C.date) AS date, Cl.Nom, Cl.Prenom, 
       MAX(numordre) AS Lignes, SUM(quantite) AS Qte, SUM(quantite*prix) AS Montant
  FROM commandes C
  JOIN details USING (numcommande)
  JOIN clients Cl USING (idclient)
GROUP BY numcommande ;

# Définir une vue Vfactures, qui donne les mêmes informations, plus les détails :
# une ligne par détail, plus une ligne de totalisation 
# astuce : GROUP BY WITH ROLLUP 
CREATE OR REPLACE VIEW Vfactures AS
SELECT C.numcommande AS NF, IFNULL(D.numordre,'TOTAL') AS NO, C.idclient, DATE(C.date) AS date, Cl.Nom, Cl.Prenom, 
       COUNT(numordre) AS Lignes, SUM(quantite) AS Qte, SUM(quantite*prix) AS Montant
  FROM commandes C
  JOIN details D USING (numcommande)
  JOIN clients Cl USING (idclient)
GROUP BY numcommande, numordre WITH ROLLUP ;




# Définir une fonction qui prend une chaîne et retourne
# cette chaîne avec la première lettre en capitale, 
# et les suivantes en minuscules
# Cf : left(), mid(), right(), upper(), lower(), concat()

CREATE FUNCTION majuscule (s VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN CONCAT(UPPER(LEFT(s,1)),LOWER(MID(s,2))) ;


# Définir une fonction PreNom qui prend deux chaînes (prénom, nom)
# et sort les deux concaténés et bien stylés (initiales en majuscule)

CREATE FUNCTION PreNom (prenom VARCHAR(255), nom VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN CONCAT(majuscule(prenom),' ', majuscule(nom)) ;



# Dans facsys, une fonction qui retourne le montant total commandé
# par un client

DROP FUNCTION IF EXISTS MontantCumule ;
DELIMITER //
CREATE FUNCTION MontantCumule(cclient CHAR(6))
RETURNS DECIMAL(10,2) 
NOT DETERMINISTIC  READS SQL DATA
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(prix*quantite) INTO total FROM commandes 
      JOIN details USING (numcommande) 
     WHERE idclient=cclient ;
    RETURN total;
END
//
DELIMITER ;


#  À partir du nom d'un nouveau client, retourner un nouveau idclient unique 
# (rappel : 3 premiers caractères du nom, suivis d'un numéro, par ex. DUR005).
DROP FUNCTION IF EXISTS NouvelId ;
DELIMITER //
CREATE FUNCTION NouvelId(p_idc VARCHAR(30))
RETURNS CHAR(6)
NOT DETERMINISTIC  READS SQL DATA
BEGIN
    DECLARE pref, suff VARCHAR(3);
    DECLARE res INT DEFAULT 0;

    SET pref:= UPPER(LEFT(p_idc,3)) ;
    SELECT COUNT(*) INTO res FROM clients WHERE LEFT(idclient,3)=pref ;
    IF (res = 0) THEN
        SET suff:='000';
    ELSE
	SELECT MAX(MID(idclient,4)) INTO suff FROM clients WHERE LEFT(idclient,3) LIKE pref ;
	SET suff:=LPAD(suff+1,3,'0') ;
    END IF ;
    RETURN CONCAT(pref,suff) ;
END
//
DELIMITER ;



# Pour poursuivre le montant total : une procédure qui retourne en plus
# le nombre total d''articles commandés

DROP PROCEDURE IF EXISTS Cumul ;
DELIMITER //
CREATE PROCEDURE Cumul(IN cclient CHAR(6), OUT montant DECIMAL(10,2), OUT nb INT)
NOT DETERMINISTIC READS SQL DATA
BEGIN
    SELECT SUM(prix*quantite), SUM(quantite) INTO montant, nb FROM commandes 
      JOIN details USING (numcommande) 
     WHERE idclient=cclient ;
END
//
DELIMITER ;


# Procédure Pfacture : retourne d'abord l'en-tête (Client, adresse)
# puis la liste des détails, puis le total, pour une commande donnée.
DROP PROCEDURE IF EXISTS Pfacture;
DELIMITER //
CREATE PROCEDURE Pfacture(IN numero INT)
BEGIN
-- l'en-tete 
SELECT C.numcommande AS No, C.idclient, DATE(C.date) AS date, 
       Cl.Nom, Cl.Prenom, Cl.adresse, Cl.codepostal AS CP, Cl.ville
  FROM commandes C
  JOIN clients Cl USING (idclient)
  WHERE numcommande=numero ;
-- le détail
SELECT numordre AS Ligne, quantite AS Qte, prix AS "P.U.", quantite*prix AS Montant
  FROM commandes C
  JOIN details USING (numcommande)
  WHERE numcommande=numero 
UNION 
-- le total
SELECT "TOTAL" AS Ligne, SUM(quantite) AS Qte, "-" AS "P.U.", SUM(quantite*prix) AS Montant
  FROM commandes C
  JOIN details USING (numcommande)
  WHERE numcommande=numero ;
END
//
DELIMITER ;



# Définir une routine qui affiche le mode (valeur la plus fréquente)
# de la colonne prix de la table articles

DELIMITER //
CREATE PROCEDURE modes()
BEGIN
    DECLARE mfreq INT;
    SELECT MAX(freq) INTO mfreq FROM 
	(SELECT prix, COUNT(prix) AS freq FROM articles GROUP BY prix) AS TFreq ;
    SELECT prix, freq FROM 
	(SELECT prix, COUNT(prix) AS freq from articles GROUP BY prix) AS TFreq WHERE freq=mfreq ;
END
//
DELIMITER ;

DELIMITER //
CREATE PROCEDURE modes2(OUT nbmodes INT)
BEGIN
    CREATE OR REPLACE VIEW VFreq AS 
	SELECT prix, COUNT(prix) AS freq FROM articles GROUP BY prix ;
    SELECT prix, freq FROM VFreq WHERE freq=(SELECT MAX(freq) FROM VFreq) ;
    SET nbmodes := FOUND_ROWS();
END
//
DELIMITER ;



# Définir une routine qui affiche la médiane d''une liste de valeurs
# Méthode 1 : PREPARE et LIMIT 
DROP PROCEDURE IF EXISTS mediane2;
DELIMITER //
CREATE PROCEDURE mediane2(OUT med DECIMAL(10,2))
BEGIN
    DECLARE nlig, moitie INT;
    DECLARE v1, v2 DECIMAL(10,2);
     PREPARE SL FROM 'SELECT @valeur:=MAX(prix) AS val FROM (SELECT prix FROM articles ORDER BY prix LIMIT ? ) T' ;
    SELECT COUNT(*) INTO nlig FROM articles;
    SET moitie := nlig DIV 2;
    IF (nlig MOD 2 = 1) THEN  -- cas simple : impair
        SET @l:=moitie +1;
        EXECUTE SL USING @l ;
        SET med:=@valeur;
    ELSE  -- cas compliqué : pair
        SET @l:=moitie ;
        EXECUTE SL USING @l ;
        SET v1:=@valeur ;
        SET @l:=moitie+1 ;
        EXECUTE SL USING @l ;
        SET v2:=@valeur ;
	SET med:=(v1+v2)/2 ;
    END IF ;
END
//
DELIMITER ;


# Définir une routine qui affiche la médiane d''une liste de valeurs
# Méthode 2 : fonction et rang
DROP FUNCTION IF EXISTS medianerang;
DELIMITER //
CREATE FUNCTION medianerang()
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  DECLARE  nlig, moitie INT;
  DECLARE  ret DECIMAL(10,2);

  SELECT COUNT(*) INTO nlig FROM articles;
  SET moitie := nlig DIV 2;
  SET @k:=0;
  IF (nlig MOD 2 = 1) THEN -- cas simple : impair
     SELECT prix INTO ret FROM (
	SELECT @k:=@k+1 AS rg, prix  FROM articles ORDER BY prix DESC
	) T WHERE rg=moitie+1;
     RETURN ret;
  ELSE -- cas compliqué : impair
     SELECT AVG(prix) INTO ret FROM (
	SELECT @k:=@k+1 AS rg, prix  FROM articles ORDER BY prix DESC
	) T WHERE rg IN (moitie, moitie+1);
     RETURN ret;
  END IF ;
END
//
DELIMITER ;


# Définir une routine qui affiche la médiane d''une liste de valeurs
# Méthode 3 : fonction et curseur
DROP FUNCTION IF EXISTS medianecur;
DELIMITER //
CREATE FUNCTION medianecur()
RETURNS DECIMAL(10,2)
BEGIN
  DECLARE cpt INT DEFAULT 0;
  DECLARE nlig, moitie INT;
  DECLARE p, p1 DECIMAL(10,2) DEFAULT 0.0;
  DECLARE cur1 CURSOR FOR SELECT prix FROM facsys.articles ORDER BY prix ASC;

  SELECT COUNT(*) INTO nlig FROM articles;
  SET moitie := nlig DIV 2;
  OPEN cur1;
  REPEAT
    FETCH cur1 INTO p;
    SET cpt := cpt+1;
  UNTIL (cpt=moitie) END REPEAT;

  IF (nlig MOD 2 = 1) THEN -- cas simple : impair
     FETCH cur1 INTO p ;
     CLOSE cur1;
     RETURN p;
  ELSE -- cas compliqué : impair
     SET p1 := p;
     FETCH cur1 INTO p;
     CLOSE cur1;
     RETURN (p1+p)/2;
  END IF ;
END
//
DELIMITER ;



# Définir une routine qui affiche la somme des montants des N articles les plus chers
# et la somme totale du stock correspondant
#
DROP PROCEDURE IF EXISTS ValeurStock;
DELIMITER //
CREATE PROCEDURE ValeurStock(IN limite INT)
BEGIN
  DECLARE p, sump, sums DECIMAL(10,2) DEFAULT 0.0;
  DECLARE k, s INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR SELECT prix, stock FROM facsys.articles ORDER BY prix DESC;

  OPEN cur1;
  REPEAT
    FETCH cur1 INTO p, s;
    SET k := k+1;
    SET sump := sump + p;
    SET sums := sums + s*p;
  UNTIL (k=limite) END REPEAT;
SELECT sump, sums;
END
//
DELIMITER ;



# Implémenter une fonction maximum avec plafond sur les prix des articles :
# ne prend pas en compte les valeurs supérieures au plafond
# avec un curseur et un handler ; 
# 
DROP FUNCTION IF EXISTS maxiplaf ;
DELIMITER //
CREATE FUNCTION maxiplaf(plafond FLOAT)
RETURNS FLOAT
BEGIN
  DECLARE fini INT DEFAULT 0;
  DECLARE maxtmp, p DECIMAL(10,2) DEFAULT 0.0;
  DECLARE cur1 CURSOR FOR SELECT prix FROM facsys.articles;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fini = 1;

  OPEN cur1;
  REPEAT
    FETCH cur1 INTO p;
    IF NOT fini THEN
       IF ( p > maxtmp AND p <= plafond ) THEN
          SET maxtmp := p ;
       END IF;
    END IF;
  UNTIL fini END REPEAT;

  CLOSE cur1;
  RETURN maxtmp;
END
//
DELIMITER ;



# TRIGGERS 
# Mettre à jour le stock quand une commande arrive
#
CREATE TRIGGER majstock AFTER INSERT ON facsys.details
FOR EACH ROW 
    UPDATE articles SET stock := stock - NEW.quantite
    WHERE codearticle=NEW.codearticle ;

# Ajouter un compteur des commandes et des montants du jour

SET @totCommandes := 0;
SET @totMontant := 0;
CREATE TRIGGER totCom AFTER INSERT ON facsys.commandes
FOR EACH ROW 
    SET @totCommandes := @totCommandes + 1;

DROP TRIGGER IF EXISTS totMon;
DELIMITER //
CREATE TRIGGER totMon AFTER INSERT ON facsys.details
FOR EACH ROW
    BEGIN 
    SET @totMontant := @totMontant + NEW.quantite*NEW.prix ;
    UPDATE articles SET stock := stock - NEW.quantite
    WHERE codearticle=NEW.codearticle ;
    END
//
DELIMITER ;
SELECT  @totCommandes, @totMontant;

# Si on commande des balles de squash : 5 minimum !
DROP TRIGGER IF EXISTS CommandeMini ;
DELIMITER //
CREATE TRIGGER CommandeMini BEFORE INSERT ON facsys.details
FOR EACH ROW 
    BEGIN
    IF ( LEFT(NEW.codearticle,3)='BAL' ) THEN
	SET NEW.quantite := GREATEST(5, NEW.quantite) ;
    END IF;
    END
//
DELIMITER ;


