1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165
| CREATE PROCEDURE IMPRESSON_NB_VENTE_PAR_VENDEUR()
BEGIN
DECLARE v_Nom VARCHAR(100);
DECLARE nb_VN_Renault INT;
DECLARE nb_VN_Dacia INT;
DECLARE nb_VO_Renault INT;
DECLARE nb_VO_Dacia INT;
DECLARE liste_nom_vendeur CURSOR
FOR
SELECT Vendeurs_Nom
FROM Vendeurs
WHERE `Vendeurs_Actif` = 1;
OPEN liste_nom_vendeur;
LOOP
FETCH liste_nom_vendeur INTO v_Nom;
SELECT Count( * ) INTO nb_VN_Renault
FROM `Commandes`
WHERE `Commandes_Vendeur` = v_Nom
AND `Commandes_Marque` = 'RENAULT'
AND `Commandes_VO_VN` = 'VN'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
BEGIN
DECLARE fin BOOLEAN DEFAULT FALSE;
DECLARE v_id1 INT;
DECLARE v_nom1 VARCHAR(100);
DECLARE v_id2 INT;
DECLARE v_nom2 VARCHAR(100);
DECLARE v_id3 INT;
DECLARE v_nom3 VARCHAR(100);
DECLARE v_id4 INT;
DECLARE v_nom4 VARCHAR(100);
DECLARE liste_commande_vendeur_vn_Renault CURSOR
FOR
Select Commandes_ID, Commandes_Vendeur
FROM Commandes
where Commandes_Vendeur = v_Nom
AND `Commandes_Marque` = 'RENAULT'
AND `Commandes_VO_VN` = 'VN'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
DECLARE liste_commande_vendeur_vn_Dacia CURSOR
FOR
Select Commandes_ID, Commandes_Vendeur
FROM Commandes
where Commandes_Vendeur = v_Nom
AND `Commandes_Marque` = 'Dacia'
AND `Commandes_VO_VN` = 'VN'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
DECLARE liste_commande_vendeur_vo_Renault CURSOR
FOR
Select Commandes_ID, Commandes_Vendeur
FROM Commandes
where Commandes_Vendeur = v_Nom
AND `Commandes_Marque` = 'RENAULT'
AND `Commandes_VO_VN` = 'VO'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
DECLARE liste_commande_vendeur_vo_Dacia CURSOR
FOR
Select Commandes_ID, Commandes_Vendeur
FROM Commandes
where Commandes_Vendeur = v_Nom
AND `Commandes_Marque` = 'Dacia'
AND `Commandes_VO_VN` = 'VO'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = 1;
END//
OPEN liste_commande_vendeur_vn_Renault;
OPEN liste_commande_vendeur_vn_Dacia;
OPEN liste_commande_vendeur_vo_Renault;
OPEN liste_commande_vendeur_vo_Dacia;
LOOP
FETCH liste_commande_vendeur_vn_Renault INTO v_id1, v_nom1;
Select Count(*)
From livraisons
Where Livraisons_ID_Commandes = v_id1 ;
END LOOP;
SELECT Count( * ) INTO nb_VN_Dacia
FROM `Commandes`
WHERE `Commandes_Vendeur` = v_Nom
AND `Commandes_Marque` = 'Dacia'
AND `Commandes_VO_VN` = 'VN'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
LOOP
FETCH liste_commande_vendeur_vn_Dacia INTO v_id2, v_nom2;
Select Count(*)
From livraisons
Where Livraisons_ID_Commandes = v_id2 ;
END LOOP;
SELECT Count( * ) INTO nb_VO_Renault number
FROM `Commandes`
WHERE `Commandes_Vendeur` = v_Nom
AND `Commandes_Marque` = 'RENAULT'
AND `Commandes_VO_VN` = 'VO'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
LOOP
FETCH liste_commande_vendeur_vo_Renault INTO v_id3, v_nom3;
Select Count(*)
From livraisons
Where Livraisons_ID_Commandes = v_id3 ;
END LOOP;
SELECT Count( * ) INTO nb_VO_Dacia
FROM `Commandes`
WHERE `Commandes_Vendeur` = v_Nom
AND `Commandes_Marque` = 'Dacia'
AND `Commandes_VO_VN` = 'VO'
AND Livraisons_Date = MONTH(CURDATE()) -1 ;
LOOP
FETCH liste_commande_vendeur_vo_Dacia INTO v_id4, v_nom4;
Select Count(*)
From livraisons
Where Livraisons_ID_Commandes = v_id4 ;
END LOOP;
CLOSE liste_commande_vendeur_vn_Renault;
CLOSEliste_commande_vendeur_vn_Dacia;
CLOSE liste_commande_vendeur_vo_Renault;
CLOSE liste_commande_vendeur_vo_Dacia;
END LOOP;
CLOSE liste_nom_vendeur;
END// |
Partager