[SQL Server] Envoyer par mail un tableau avec des sous totaux et un total général

  • Imprimer

-- =============================================

-- Author   :   Zouhaier KHARROUBI

-- Create date: 11/04/2017

-- Description:   Envoyer par mail un tableau avec des sous totaux

-- =============================================

Le résultat de la requête :

COMPARAISON CA ENSEIGNE EXERCICE N/N-1

N°   Ligne

Enseigne

Numéro   Magasin

Nom   Magasin

CA   N-1

CA   N

Taux   Evolution (%)

1

ENSEIGNE 1

Magasin   1

Nom Magasin 1

0.00

0.00

0.00

 

ENSEIGNE 1

TOTAL

0.00

0.00

0.00

1

ENSEIGNE 2

Magasin   1

Nom Magasin 1

0.00

0.00

0.00

2

 

Magasin   2

Nom Magasin 2

0.00

0.00

0.00

 

ENSEIGNE 2

TOTAL

0.00

0.00

0.00

1

ENSEIGNE 3

Magasin   1

Nom Magasin 1

0.00

0.00

0.00

 

ENSEIGNE 3

TOTAL

0.00

0.00

0.00

1

ENSEIGNE 4

Magasin   1

Nom Magasin 2

0.00

0.00

0.00

2

 

Magasin   2

Nom Magasin 2

0.00

0.00

0.00

 

ENSEIGNE 4

TOTAL

0.00

0.00

0.00

TOTAL GENERAL

0.00

0.00

0.00

BEGIN

      SET NOCOUNT ON;

           

      IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))

            BEGIN

                  DROP TABLE #Resultat

            END        

      IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ResultatByEnseigne]'))

            BEGIN

                  DROP TABLE #ResultatByEnseigne

            END  

           

      IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Enseigne]'))

            BEGIN

                  DROP TABLE #Enseigne

            END  

                 

      CREATE TABLE #Resultat

      (

      Enseigne                                      VARCHAR(17)       NULL

      ,Num_Magasin                                   VARCHAR(17)       NULL

      ,Nom_Magasin                                   VARCHAR(35)       NULL

      ,MontantHT_N                                   NUMERIC(24,2)     NULL

      ,MontantHT_N_1                                 NUMERIC(24,2)     NULL

      ,TauxEvolution                                 NUMERIC(24,2)     DEFAULT 0

      ,MontantHT_N_ByEnseigne                        NUMERIC(24,2)     DEFAULT 0

      ,MontantHT_N_1_ByEnseigne                      NUMERIC(24,2)     DEFAULT 0

      ,TauxEvolution_ByEnseigne                      NUMERIC(24,2)     DEFAULT 0

      )

     

      CREATE TABLE #ResultatByEnseigne

      (

      Num_Lig                                       INT IDENTITY(1,1)

      ,Enseigne                                      VARCHAR(17)       NULL

      ,Num_Magasin                                   VARCHAR(17)       NULL

      ,Nom_Magasin                                   VARCHAR(35)      NULL

      ,MontantHT_N                                   VARCHAR(26)       NULL

      ,MontantHT_N_1                                 VARCHAR(26)       NULL

      ,TauxEvolution                                 VARCHAR(26)       NULL

      ,MontantHT_N_ByEnseigne                        VARCHAR(26)       NULL

      ,MontantHT_N_1_ByEnseigne                      VARCHAR(26)       NULL

      ,TauxEvolution_ByEnseigne                      VARCHAR(26)      NULL

      )

      CREATE TABLE #Enseigne

      (

      Num_Lig                          INT IDENTITY(1,1)

      ,Enseigne                         VARCHAR(17)       NULL

      )

     

   -- Insert statements for procedure here

      DECLARE @Annee                          INT

                  ,@Mois                             INT

                  ,@Annee_Deb_Ex               CHAR(4)

                  ,@Annee_Fin_Ex               CHAR(4)

                  ,@DateDebutExEncours          DATE

                  ,@DateFinExEncours           DATE

                  ,@DateDebutExPrecedent        DATE

                  ,@DateFinExPrecedent          DATE

                  ,@Nbre_Lig                   INT

                  ,@Num_Lig                    INT

                  ,@Nbre_Lig_Mail              INT

                  ,@Num_Lig_Mail               INT

                  ,@Enseigne                    VARCHAR(17)

                  ,@emailSubject               VARCHAR(100)

                  ,@tableHTML                  NVARCHAR(MAX)

                  ,@body                       VARCHAR(MAX)=''

                  ,@bodyTemp                   VARCHAR(MAX)

                  ,@recipients                 VARCHAR(MAX)

                  ,@Nom_Magasin                VARCHAR(35)

SELECT @emailSubject ='COMPARAISON CA ENSEIGNE EXERCICE N / N-1'

SELECT @recipients      ='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'

     

--Calculer les date debut et fin pour l'exercice encours

SELECT @Annee = DATEPART(YEAR,GETDATE())

SELECT @Mois = DATEPART(MONTH,GETDATE())

IF @Mois>=1 AND @Mois<=8

      BEGIN

            SELECT @Annee_Deb_Ex = @Annee -

            SELECT @Annee_Fin_Ex = @Annee

      END

ELSE

      BEGIN

            SELECT @Annee_Deb_Ex = @Annee

            SELECT @Annee_Fin_Ex = @Annee +

      END

SELECT @DateDebutExEncours = @Annee_Deb_Ex + '0901'

SELECT @DateFinExEncours   = GETDATE()

--Calculer les date debut et fin pour l'exercice précédent

SELECT @Annee = DATEPART(YEAR,DATEADD(YEAR,-1,GETDATE()))

SELECT @Mois = DATEPART(MONTH,DATEADD(YEAR,-1,GETDATE()))

IF @Mois>=1 AND @Mois<=8

      BEGIN

            SELECT @Annee_Deb_Ex = @Annee -

            SELECT @Annee_Fin_Ex = @Annee

      END

ELSE

      BEGIN

            SELECT @Annee_Deb_Ex = @Annee

            SELECT @Annee_Fin_Ex = @Annee +

      END

SELECT @DateDebutExPrecedent = @Annee_Deb_Ex + '0901'

SELECT @DateFinExPrecedent   = DATEADD(YEAR,-1,GETDATE())

INSERT INTO #Resultat(Enseigne,Num_Magasin,Nom_Magasin,MontantHT_N,MontantHT_N_1)

SELECT Enseigne = ISNULL(R1.Enseigne,R2.Enseigne), Num_Magasin= ISNULL(R1.Num_Magasin,R2.Num_Magasin), Nom_Magasin = ISNULL(R1.Nom_Magasin,R2.Nom_Magasin)

,ISNULL(R1.MontantHT_N,0) MontantHT_N,ISNULL(R2.MontantHT_N_1,0) MontantHT_N_1

FROM (

SELECT T2.Enseigne,T1.Num_Magasin,T2.Nom_Magasin,SUM(T1.DL_MontantHT) MontantHT_N FROM Audim_i7..F_DOCLIGNE T1

JOIN Clients T2 ON T2.Num_Magasin=T1.Num_Magasin

WHERE T1.DO_Type>=6 AND T1.DO_Type<=8

AND T1.DL_Valorise = 1

AND T1.DO_Date>=@DateDebutExEncours   AND T1.DO_Date<=@DateFinExEncours

AND T2.CT_Sommeil=0

AND ISNULL(T2.Enseigne,'')<>''

GROUP BY T2.Enseigne,T1.Num_Magasin,T2.Nom_Magasin

) R1

FULL JOIN(

SELECT T2.Enseigne,T1.Num_Magasin,T2.Nom_Magasin,SUM(T1.DL_MontantHT) MontantHT_N_1 FROM Audim_i7..F_DOCLIGNE T1

JOIN Clients T2 ON T2.Num_Magasin=T1.Num_Magasin

WHERE T1.DO_Type>=6 AND T1.DO_Type<=8

AND T1.DL_Valorise = 1

AND T1.DO_Date>=@DateDebutExPrecedent   AND T1.DO_Date<=@DateFinExPrecedent

AND T2.CT_Sommeil=0

AND ISNULL(T2.Enseigne,'')<>''

GROUP BY T2.Enseigne,T1.Num_Magasin,T2.Nom_Magasin

) R2 ON R2.Num_Magasin=R1.Num_Magasin

UPDATE T1

SET

      T1.MontantHT_N_ByEnseigne=T2.MontantHT_N_ByEnseigne

FROM #Resultat T1

JOIN(SELECT Enseigne,SUM(MontantHT_N) MontantHT_N_ByEnseigne FROM #Resultat GROUP BY Enseigne) T2 ON T2.Enseigne=T1.Enseigne

UPDATE T1

SET

      T1.MontantHT_N_1_ByEnseigne=T2.MontantHT_N_1_ByEnseigne

FROM #Resultat T1

JOIN(SELECT Enseigne,SUM(MontantHT_N_1) MontantHT_N_1_ByEnseigne FROM #Resultat GROUP BY Enseigne) T2 ON T2.Enseigne=T1.Enseigne

INSERT INTO #Resultat(Enseigne,Num_Magasin,Nom_Magasin,MontantHT_N,MontantHT_N_1)

SELECT Enseigne,Num_Magasin,Nom_Magasin,SUM(MontantHT_N) MontantHT_N,SUM(MontantHT_N_1) MontantHT_N_1 FROM (

SELECT '' Enseigne,'' Num_Magasin,'TOTAL GENERAL' Nom_Magasin,MontantHT_N,MontantHT_N_1 FROM #Resultat WHERE ISNULL(Num_Magasin,'')<>''

) T GROUP BY Enseigne,Num_Magasin,Nom_Magasin

UPDATE #Resultat

SET

      TauxEvolution=(MontantHT_N - MontantHT_N_1) / MontantHT_N_1 * 100

WHERE ISNULL(MontantHT_N_1,0)>0

UPDATE #Resultat

SET

      TauxEvolution_ByEnseigne =(MontantHT_N_ByEnseigne - MontantHT_N_1_ByEnseigne) / MontantHT_N_1_ByEnseigne * 100

WHERE ISNULL(MontantHT_N_1_ByEnseigne,0)>0

INSERT INTO #Enseigne(Enseigne)

SELECT DISTINCT Enseigne FROM #Resultat 

WHERE Nom_Magasin<>'TOTAL GENERAL'

ORDER BY Enseigne

SELECT @Nbre_Lig = COUNT(Enseigne) FROM #Enseigne

SELECT @Num_Lig   = 1

SET @tableHTML = N'<H><font size="4">Bonjour</font><BR></BR><BR></BR></H>' +

   N'<H><font size="4">Veuillez trouver ci-dessous,LA COMPARAISON CA ENSEIGNE EXERCICE N / N-1.</font><BR></BR></H>' +

   N'<html><head><style>' +

   N'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

   N'</style></head><body>' +

   N'<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +

   N'<div style="margin-left:50px; font-family:Calibri;">' +

   N'<style>.MonTableau tbody tr:nth-child(odd) {background-color: #999690;color:#ffffff;}

   .MonTableau tbody tr:nth-child(even) {background-color: #999690;color:#ffffff;}</style>' +

   N'<table cellpadding=0 cellspacing=0 border=0 class="MonTableau">' +

   N'<tr bgcolor=PaleTurquoise>' +

   N'<td align=center colspan="7"><font face="calibri" color=blak size=5><b>COMPARAISON CA ENSEIGNE EXERCICE N/N-1</b></font></td></tr>' +

   N'<tr bgcolor=#4b6c9e>' +

   N'<td align=center><font face="calibri" color=White><b>N° Ligne</b></font></td>' +  

   N'<td align=center><font face="calibri" color=White><b>Enseigne</b></font></td>' +  

   N'<td align=center><font face="calibri" color=White><b>Numéro Client</b></font></td>' +

   N'<td align=center><font face="calibri" color=White><b>Nom Client</b></font></td>'   +

   N'<td align=center><font face="calibri" color=White><b>CA N-1</b></font></td>'   +

   N'<td align=center><font face="calibri" color=White><b>CA N</b></font></td>'   +

   N'<td align=center><font face="calibri" color=White><b>Taux Evolution (%)</b></font></td></tr>'

  

WHILE @Num_Lig <= @Nbre_Lig

      BEGIN

           

            SELECT @Enseigne = Enseigne FROM #Enseigne WHERE Num_Lig=@Num_Lig

           

            TRUNCATE TABLE #ResultatByEnseigne

           

            INSERT INTO #ResultatByEnseigne(Enseigne,Num_Magasin,Nom_Magasin,MontantHT_N,MontantHT_N_1,TauxEvolution  

            ,MontantHT_N_ByEnseigne,MontantHT_N_1_ByEnseigne,TauxEvolution_ByEnseigne)

            SELECT Enseigne,Num_Magasin,Nom_Magasin,MontantHT_N,MontantHT_N_1,TauxEvolution   

            ,MontantHT_N_ByEnseigne,MontantHT_N_1_ByEnseigne,TauxEvolution_ByEnseigne FROM #Resultat WHERE Enseigne = @Enseigne

           

           

            UPDATE #ResultatByEnseigne

            SET

                  Enseigne=''

            WHERE Num_Lig > 1

           

            --Insérer une ligne pour traiter les totaux

            INSERT INTO #ResultatByEnseigne(Enseigne,Num_Magasin,Nom_Magasin,MontantHT_N,MontantHT_N_1,TauxEvolution  

            ,MontantHT_N_ByEnseigne,MontantHT_N_1_ByEnseigne,TauxEvolution_ByEnseigne)

            SELECT DISTINCT Enseigne,'' Num_Magasin, 'TOTAL' Nom_Magasin, '' MontantHT_N, '' MontantHT_N_1, '' TauxEvolution     

            ,MontantHT_N_ByEnseigne,MontantHT_N_1_ByEnseigne,TauxEvolution_ByEnseigne FROM #ResultatByEnseigne WHERE Enseigne = @Enseigne 

           

            SELECT @Nbre_Lig_Mail = COUNT(Num_Lig) FROM #ResultatByEnseigne

           

            SELECT @Num_Lig_Mail = 1

           

            WHILE @Num_Lig_Mail <= @Nbre_Lig_Mail

                  BEGIN

                        SELECT @bodyTemp =     

                        (  

                        SELECT td_center = CASE Nom_Magasin WHEN 'TOTAL' THEN '' ELSE CAST(Num_Lig AS VARCHAR(26)) END ,

                        td = Enseigne,

                        td_center = CASE Nom_Magasin WHEN 'TOTAL' THEN Nom_Magasin ELSE Num_Magasin END,

                        td = CASE Nom_Magasin WHEN 'TOTAL' THEN '' ELSE Nom_Magasin END,

                        td_right= dbo.ufs_montantFormat(CASE Nom_Magasin WHEN 'TOTAL' THEN MontantHT_N_1_ByEnseigne ELSE MontantHT_N_1 END),

                        td_right = dbo.ufs_montantFormat(CASE Nom_Magasin WHEN 'TOTAL' THEN MontantHT_N_ByEnseigne ELSE MontantHT_N END) ,

                        td_right = dbo.ufs_montantFormat(CASE Nom_Magasin WHEN 'TOTAL' THEN TauxEvolution_ByEnseigne ELSE TauxEvolution END)

                        FROM #ResultatByEnseigne WHERE Num_Lig= @Num_Lig_Mail                 

                        FOR XML RAW('tr'), ELEMENTS

                        )

                       

                        SELECT @Nom_Magasin = Nom_Magasin FROM #ResultatByEnseigne WHERE Num_Lig= @Num_Lig_Mail

                       

                        IF @Num_Lig_Mail % 2 = 0

                             IF @Nom_Magasin='TOTAL'

                                   BEGIN

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<tr>', '<b><tr style="background-color:red;color:blue;">')

                                         SET @bodyTemp = REPLACE(@bodyTemp, '</tr>', '</tr></b>')

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<td_center>TOTAL</td_center>','<td align=right colspan="2">TOTAL</td>')

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<td></td>','')

                                   END

                             ELSE

                                   BEGIN                                   

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<tr>', '<tr style="background-color:LemonChiffon;color:blue;">')

                                   END

                        ELSE

                             IF @Nom_Magasin='TOTAL'

                                   BEGIN

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<tr>', '<b><tr style="background-color:red;color:blue;">')

                                         SET @bodyTemp = REPLACE(@bodyTemp, '</tr>', '</tr></b>')

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<td_center>TOTAL</td_center>','<td align=right colspan="2">TOTAL</td>')

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<td></td>','')

                                   END

                             ELSE

                                   BEGIN

                                         SET @bodyTemp = REPLACE(@bodyTemp, '<tr>', '<tr style="background-color:white;color:blue;">')

                                   END

                       

                       

     

                                  

                        SELECT @body =    ISNULL(@body,'') + ISNULL(@bodyTemp,'')

                       

                        SELECT @Num_Lig_Mail = @Num_Lig_Mail + 1

                  END

           

            SELECT @Num_Lig   = @Num_Lig + 1

      END

     

      --Insertion Total Général

      SELECT @bodyTemp =     

      (  

      SELECT td_right = 'TOTAL GENERAL' ,

      td = '',

      td = '',

      td = '',

      td_right= dbo.ufs_montantFormat( MontantHT_N_1 ),

      td_right = dbo.ufs_montantFormat( MontantHT_N ) ,

      td_right = dbo.ufs_montantFormat(TauxEvolution )

      FROM #Resultat WHERE Nom_Magasin='TOTAL GENERAL'          

      FOR XML RAW('tr'), ELEMENTS

      )

      SET @bodyTemp = REPLACE(@bodyTemp, '<tr>', '<b><tr style="background-color:green;color:black;">')

      SET @bodyTemp = REPLACE(@bodyTemp, '</tr>', '</tr></b>')

      SET @bodyTemp = REPLACE(@bodyTemp, '<td_center></td_center>', '')

      SET @bodyTemp = REPLACE(@bodyTemp, '<td_right>TOTAL GENERAL</td_right>','<td align=right colspan=4>TOTAL GENERAL<font face="calibri"></td>') 

      SET @bodyTemp = REPLACE(@bodyTemp, '<td></td>','')

      SELECT @body = ISNULL(@body,'') + @bodyTemp   

      --Fin insertion Total Général

      SET @body = REPLACE(@body, '<td_right>', '<td align=right><font face="calibri">')

      SET @body = REPLACE(@body, '</td_right>', '</td>')

     

      SET @body = REPLACE(@body, '<td_center>', '<td align=center><font face="calibri">')

      SET @body = REPLACE(@body, '</td_center>', '</td>')

     

      SET @body = REPLACE(@body, '<td>', '<td align=left><font face="calibri">')

      SET @body = REPLACE(@body, '</td>', '</font></td>')

      SET @tableHTML = @tableHTML + @body + '</table></div></body></html>'

      SET @tableHTML = @tableHTML + N'<H><BR></BR></H>'

      SET @tableHTML = @tableHTML + N'<H><font size="4">Cordialement,</font><BR></BR><BR></BR></H>'

      SET @tableHTML = @tableHTML + N'<H><font size="4">Mektaba.info</font></H>'

      EXEC msdb.dbo.sp_send_dbmail

         @profile_name = 'MonProfilMail',

         @recipients = @recipients,

         @body = @tableHTML,

         @subject = @emailSubject,

         @body_format = 'HTML'

        

END

-- =============================================

-- Author:        Zouhaier KHARROUBI

-- Create date: 06/04/2017

-- Description:   Formater les montants en rajoutant des épaces entre chaque trois chiffres

-- Exemple SELECT [dbo].[ufs_MontantFormat]('457892567.25') => Resultat : 457 892 567.25

-- =============================================

CREATE FUNCTION [dbo].[ufs_MontantFormat]

(

   @Montant NVARCHAR(25)

)

RETURNS NVARCHAR(26)

AS

BEGIN

DECLARE @Retour AS NVARCHAR(25)

SET @Retour= REPLACE(CONVERT(VARCHAR(26),CONVERT(MONEY,@Montant),1),',',SPACE(1))

RETURN @Retour

          

END