[SQL Server] Compresser des fichiers en format ZIP, les mettre sur le FTP et les envoyer par mail en pièce jointe
-- =============================================
-- Author: KHARROUBI Zouhaier
-- Create date: 31/10/2012
-- Description: Déposer les commandes Mektaba sur le FTP en cas d'echec d'envoie via L''outil de Transfert
-- =============================================
ALTER PROCEDURE [dbo].[usp_CommandesMektabaToFTP]
-- Add the parameters for the stored procedure here
@MARCHE CHAR(3),
@FORMAT_MAIL AS CHAR(4) -- 'TXT' ou 'HTML'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeCommande]'))
DROP TABLE #ListeCommande
-- Insert statements for procedure here
CREATE TABLE #ListeCommande
(
Num_Commande INT IDENTITY(1,1),
Commande VARCHAR(60)
)
DECLARE @Nbre_Commande AS INT
DECLARE @NbreSessionsEchec AS INT
DECLARE @Objet_Mail AS NVARCHAR(MAX)
DECLARE @txtLine AS NVARCHAR(MAX)
DECLARE @tableHTML AS NVARCHAR(MAX) ;
SELECT @NbreSessionsEchec=0
SELECT @Nbre_Commande=0
SELECT @Objet_Mail=''
IF UPPER(RTRIM(LTRIM(@MARCHE)))='TN'
BEGIN
SELECT @NbreSessionsEchec=COUNT(*) FROM
(
SELECT [Etat_Envoie],Date_Envoie,Nom_Client FROM Historique_Envoie_Commandes
WHERE Date_Envoie>=CONVERT(CHAR(10),GETDATE(),112)
AND Nom_Client like 'CLIENT001%'
AND [Etat_Envoie] NOT LIKE 'SUCCESSFUL%'
AND [Etat_Envoie] NOT LIKE 'COMPLETED%'
AND Nom_Session LIKE 'SessionOut_02_30%'
) T
IF @NbreSessionsEchec>0
BEGIN
INSERT INTO #ListeCommande EXEC master.sys.xp_cmdshell'DIR /B "\\serveurftp_1\Commandes\MEKTABA\*.xml"'
DELETE FROM #ListeCommande WHERE Commande IS NULL OR Commande NOT LIKE 'PO%.xml'
SELECT @Nbre_Commande=COUNT(Commande) FROM #ListeCommande
IF @Nbre_Commande>0
BEGIN
--supprimer les commandes sur FTP
EXEC master.sys.xp_cmdshell'del \\serveurftp_2\mektaba_commandes\*.xml /q'
--supprimer les commandes sur D:\commandes\
EXEC master.sys.xp_cmdshell'del D:\commandes\*.* /s /q'
--copier les commandes sur FTP
EXEC master.sys.xp_cmdshell'copy \\serveurftp_1\Commandes\MEKTABA\*.xml \\serveurftp_2\mektaba_commandes'
--créer le fichier zip
EXEC [dbo].[usp_CompressFile]N'D:\commandes\mektaba_commandes\mektaba_commandes.zip', N'D:\commandes\mektaba_commandes\'
IF UPPER(LTRIM(RTRIM(@FORMAT_MAIL)))='TXT'
BEGIN
--Envoyer mail Format TXT
SELECT @txtLine = 'Bonjour, ' + CHAR(13) + CHAR(13) +'L''envoie des commandes suivantes vers Mektaba via L''outil de Transfert a échoué : ' + CHAR(13) + CHAR(13)
SELECT @Objet_Mail= @Objet_Mail + RTRIM(LTRIM(CAST(Num_Commande AS CHAR(3)))) + ') ' + Commande + CHAR(13) FROM #ListeCommande ORDER BY Num_Commande
SELECT @txtLine = @txtLine + @Objet_Mail + CHAR(13) + 'Veuillez retirer les commandes sur le FTP ou récupérer la pièce jointe sous format zip dans ce mail.' + CHAR(13) + CHAR(13) + 'Cordialement, ' + CHAR(13) + CHAR(13) +'MEKTABA'
EXEC msdb..sp_send_dbmail@profile_name='EnvoiMail',@importance='High', @recipients='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.;Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'">Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.', @subject='Anomalie Envoie Commandes Mektaba', @body= @txtLine, @file_attachments= 'D:\commandes\mektaba_commandes\mektaba_commandes.zip'
END
ELSE
BEGIN
--Envoyer mail Format HTML
SET @tableHTML =
N'<H>Bonjour<BR></BR></H>' +
N'<H>L''envoie des commandes suivantes vers Mektaba via L''outil de Transfert a échoué : <BR></BR></H>' +
N'<table border="1">' +
N'<tr><th>N°</th><th>Commande</th>' +
CAST(( SELECT td = Num_Commande, '',
td = Commande
FROM #ListeCommande ORDER BY Num_Commande
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' +
N'<H><BR></BR></H>' +
N'<H>Veuillez retirer les commandes sur le FTP ou récupérer la pièce jointe sous format zip dans ce mail. <BR></BR></H>' +
N'<H>Cordialement,<BR></BR></H>' +
N'<H>MEKTABA</H>' ;
EXEC msdb..sp_send_dbmail@profile_name='EnvoiMail',@importance='High', @recipients='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.;Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'">Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',
@subject='Anomalie Envoie Commandes Mektaba', @body= @tableHTML, @body_format = 'HTML', @file_attachments= 'D:\commandes\mektaba_commandes\mektaba_commandes.zip';
END
END
END
END
DROP TABLE #ListeCommande
END