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
| USE [GPAO]
GO
/****** Object: StoredProcedure [dbo].[rebuildECHEANCIER] Script Date: 08/11/2019 10:11:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rebuildECHEANCIER]
-- Add the parameters for the stored procedure here
@site integer
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET DATEFIRST 1;
DECLARE @aid int;
DECLARE @aujourdhui date;
DECLARE @semaine smallint;
DECLARE @annee smallint;
DECLARE @lundi date;
DECLARE @dimanche date;
DECLARE @fin_periode date;
DECLARE @i int;
DECLARE @first_article bit;
DECLARE @ref_client varchar(100);
DECLARE @retard decimal(19, 6);
DECLARE @solde decimal(19, 6);
DECLARE @s1 decimal(19, 6);
DECLARE @s2 decimal(19, 6);
DECLARE @s3 decimal(19, 6);
DECLARE @s4 decimal(19, 6);
DECLARE @s5 decimal(19, 6);
DECLARE @s6 decimal(19, 6);
DECLARE @s7 decimal(19, 6);
DECLARE @s8 decimal(19, 6);
DECLARE @s9 decimal(19, 6);
DECLARE @s10 decimal(19, 6);
DECLARE @s11 decimal(19, 6);
DECLARE @s12 decimal(19, 6);
DECLARE @livrees_semaine decimal(19, 6);
DECLARE @chiffre_semaine decimal(19, 6);
DECLARE @volume_periode decimal(19, 6);
DECLARE @chiffre_periode decimal(19, 6);
SET @first_article = 1;
SET @aujourdhui = GETDATE();
SET @lundi = DATEADD(dd, -(DATEPART(dw, @aujourdhui)-1), @aujourdhui);
SET @dimanche = DATEADD(dd, 6, @lundi);
-- SUPPRIMER LES DONNES QUI VONT ETRE REGENEREES
DELETE FROM ech WHERE ech_site = @site
-- PARCOURS DE LA TABLE VDF (qui contient les articles pour lesquels on veut l'échéancier)
DECLARE MyCursor CURSOR
FOR
SELECT vdf_article FROM vdf WHERE vdf_site = @site
OPEN MyCursor
FETCH MyCursor INTO @aid;
WHILE @@fetch_Status = 0
BEGIN
-- initialisation de la semaine de départ
-- pour chaque article analysé
SET @semaine = DATEPART(wk,@aujourdhui);
SET @annee = year(@aujourdhui);
-- recherche de la ref client
SET @ref_client = (SELECT TOP 1 trfv_article_client FROM trfv WHERE trfv_site = @site AND trfv_article = @aid AND trfv_client = 0);
IF @ref_client is null
SET @ref_client = (SELECT TOP 1 trfv_article_client FROM trfv WHERE trfv_site = @site AND trfv_article = @aid);
-- recherche du retard
SET @retard = (SELECT coalesce(sum(ral_qte), 0) FROM ral
WHERE ral_site = @site AND ral_article = @aid
AND ((ral_annee = @annee AND ral_semaine < @semaine) OR ral_annee < @annee));
-- recherche des RAL de l'article pour les 12 semaines à venir
SET @i = 1;
WHILE @i <= 12
BEGIN
SET @solde = (SELECT coalesce(sum(ral_qte), 0) FROM ral
WHERE ral_site = @site AND ral_article = @aid AND ral_annee = @annee AND ral_semaine = @semaine);
IF @i = 1 SET @s1 = @solde;
ELSE IF @i = 2 SET @s2 = @solde;
ELSE IF @i = 3 SET @s3 = @solde;
ELSE IF @i = 4 SET @s4 = @solde;
ELSE IF @i = 5 SET @s5 = @solde;
ELSE IF @i = 6 SET @s6 = @solde;
ELSE IF @i = 7 SET @s7 = @solde;
ELSE IF @i = 8 SET @s8 = @solde;
ELSE IF @i = 9 SET @s9 = @solde;
ELSE IF @i = 10 SET @s10 = @solde;
ELSE IF @i = 11 SET @s11 = @solde;
ELSE IF @i = 12 SET @s12 = @solde;
SET @i = @i + 1;
SET @semaine = @semaine + 1;
if @semaine > 52
BEGIN
SET @semaine = 1;
SET @annee = @annee + 1;
END
END
IF @first_article > 0 -- ce traitement ne se fait qu'une seule fois, pour calculer la date du dimanche qui termine la 12ème semaine (à partir de @annee et @semaine)
BEGIN
-- mémorise la date de fin de période
SET @fin_periode = dateadd(dd, 6, dateadd(week, @semaine-1, dateadd (year, @annee-1900, 0)) - 4 - datepart(dw, dateadd (week, @semaine-1, dateadd (year, @annee-1900, 0)) - 4) + 1)
SET @first_article = 0
END
-- requete volume en commande et chiffre total (jusqu'à @fin_periode)
SET @volume_periode = 0;
SET @chiffre_periode = 0;
SELECT @volume_periode = coalesce(sum(ccl_qte_cde - ccl_qte_livree), 0), @chiffre_periode = coalesce(sum((ccl_qte_cde - ccl_qte_livree) * ccl_prix), 0) FROM ccl
INNER JOIN cc ON ccl_cc_id = cc_id
WHERE cc_site = @site AND ccl_article_id = @aid AND cc_confirme = 1 AND ccl_qte_cde > ccl_qte_livree AND ccl_fermee = 0
AND ccl_delai_confirme <= @fin_periode
-- requete volume et chiffre d'affaire pour la semaine en cours (à partir de @lundi et @dimanche calculés en début de procédure)
SET @livrees_semaine = 0;
SET @chiffre_semaine = 0;
SELECT @livrees_semaine = coalesce(sum(bl_qte_livree), 0), @chiffre_semaine = coalesce(sum(bl_qte_livree * bl_prix), 0) FROM bl
WHERE bl_site = @site AND bl_article_id = @aid AND bl_date >= @lundi AND bl_date <= @dimanche
-- pour chaque article, insertion dans la table ech
INSERT INTO ech (ech_site, ech_aid, ech_ref_client, ech_retard, ech_s1, ech_s2, ech_s3, ech_s4, ech_s5, ech_s6, ech_s7, ech_s8, ech_s9, ech_s10, ech_s11, ech_s12, ech_volume_periode, ech_chiffre_periode, ech_volume_semaine, ech_chiffre_semaine)
VALUES (@site, @aid, @ref_client, @retard, @s1, @s2, @s3, @s4, @s5, @s6, @s7, @s8, @s9, @s10, @s11, @s12, @volume_periode, @chiffre_periode, @livrees_semaine, @chiffre_semaine);
FETCH MyCursor INTO @aid;
END
CLOSE myCursor
DEALLOCATE myCursor
END |
Partager