[SQL Server] Envoyer par mail un tableau avec des sous totaux et un total général
-- =============================================
-- 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 - 1
SELECT @Annee_Fin_Ex = @Annee
END
ELSE
BEGIN
SELECT @Annee_Deb_Ex = @Annee
SELECT @Annee_Fin_Ex = @Annee + 1
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 - 1
SELECT @Annee_Fin_Ex = @Annee
END
ELSE
BEGIN
SELECT @Annee_Deb_Ex = @Annee
SELECT @Annee_Fin_Ex = @Annee + 1
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