[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