MEKTABA - [SQL Server] Génération d'un fichier XML

[SQL Server] Génération d'un fichier XML

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

-- Author: Zouhaier KHARROUBI

-- Create date: 20/03/2018

-- Description: Génération d'un fichier XML

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

Le code source

--Le fichier résultat de génération

<?xml version="1.0"?>

-<Factures MsgID="4" NumClient="100000">

-<Facture Currency="EUR">

-<Head>

-<CreationMessage>

</CreationMessage>

<Type>Facture</Type>

-<Reference>

</Reference>

</Head>

-<Body>

-<LINE ID="120">

<ElementID Type="Interne">ALCTA8055S</ElementID>

<ElementID Type="Manufacturer">8055-2BALWE1</ElementID>

<ElementText>TABLETTE ALCATEL 8055 7" 8GB WIFI SILVER</ElementText>

-<ElementCharges Type="ECOTAXE">

</ElementCharges>

-<ElementCharges Type="SORECOP">

</ElementCharges>

-<ElementReference>

</ElementReference>

</LINE>

-<LINE ID="90">

<ElementID Type="Interne">EFQG950CSILVER</ElementID>

<ElementID Type="Manufacturer">EF-QG950CSEGWW</ElementID>

<ElementText>COQUE TRANSPARENTE D'ORIGINE SAMSUNG POUR GALAXY S8 -SILVER</ElementText>

-<ElementCharges Type="ECOTAXE">

</ElementCharges>

-<ElementCharges Type="SORECOP">

</ElementCharges>

-<ElementReference>

</ElementReference>

</LINE>

-<LINE ID="130">

<ElementID Type="Interne">FUETUI00010</ElementID>

<ElementID Type="Manufacturer">FUETUI00010</ElementID>

<ElementText>*EOL*FOLLOW UP PARISIAN FOLIO SAMSUNG GALAXY A5 2016 GRIS MÉTAL</ElementText>

-<ElementCharges Type="ECOTAXE">

</ElementCharges>

-<ElementCharges Type="SORECOP">

</ElementCharges>

-<ElementReference>

</ElementReference>

</LINE>

-<LINE ID="110">

<ElementID Type="Interne">LENOVOEGRAY</ElementID>

<ElementText>GSM MOTOROLA MOTO E4 XT1762FR 16GO 5" 4G METTALIC IRON GRAY</ElementText>

-<ElementCharges Type="ECOTAXE">

</ElementCharges>

-<ElementCharges Type="SORECOP">

</ElementCharges>

-<ElementReference>

</ElementReference>

</LINE>

-<LINE ID="100">

<ElementID Type="Interne">SAMGALJ52017BLAC</ElementID>

<ElementID Type="Manufacturer">SM-J530FZKAXEF</ElementID>

<ElementText>GSM SAMSUNG J530F GALAXY J5 2017 5,2" HD 16GO BLACK</ElementText>

-<ElementCharges Type="ECOTAXE">

</ElementCharges>

-<ElementCharges Type="SORECOP">

</ElementCharges>

-<ElementReference>

</ElementReference>

</LINE>

</Body>

-<Summary>

-<Charges>

</Charges>

-<FactureMontants>

-<Taxes TaxRate="20.00">

</Taxes>

</FactureMontants>

</Summary>

</Facture>

</Factures>

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

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

BEGIN

DROP TABLE #Factures

END

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

BEGIN

DROP TABLE #Journal

END

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

BEGIN

DROP TABLE #NumeroSerie

END

CREATE TABLE #Factures

(

Num_Lig bigint IDENTITY(1,1) NOT NULL,

DO_Piece VARCHAR(9) NULL,

DO_Ref VARCHAR(17) NULL,

DO_Date DATE NULL,

DO_Tiers VARCHAR(17) NULL,

CT_IntituleVARCHAR(35) NULL

)

CREATE TABLE #Journal

(

Num_Lig BIGINT IDENTITY(1,1) NOT NULL,

DO_Piece VARCHAR(9) NULL,

DO_Ref VARCHAR(17) NULL,

DO_Date DATE NULL,

Statut_FACTURE VARCHAR(MAX) NULL

)

CREATE TABLE #NumeroSerie

(

Num_Lig BIGINT IDENTITY(1,1) NOT NULL

,AR_Ref VARCHAR(19) NULL

,SERIE VARCHAR(MAX) NULL

)

DECLARE @Message_EDI VARCHAR(MAX)

,@Message_EDI_XML XML

,@IdDocXML INT

,@DO_Tiers VARCHAR(17)='100000'

,@Nbre_Factures INT

,@Num_Facture INT

,@Statut_FACTURE VARCHAR(MAX)

,@DO_Piece VARCHAR(9)

,@DL_PieceBL VARCHAR(9)

,@DO_Ref VARCHAR(17)

,@DO_Date DATE

,@Nom_Fichier_INVOIC NVARCHAR(50)

,@CommandeShell NVARCHAR(2500)

,@emailSubject VARCHAR(100)

,@tableHTML NVARCHAR(MAX)

,@Detail VARCHAR(MAX)

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

,@Numero_Tracking VARCHAR(MAX)

,@Numero_Serie VARCHAR(MAX)

,@Nbre_Lig INT

,@Num_Lig INT

,@AR_Ref VARCHAR(19)

,@Indice INT

INSERT INTO #Factures(DO_Piece,DO_Ref,DO_Date,DO_Tiers,CT_Intitule)

SELECT T1.DO_Piece,DO_Ref = CASE WHEN ISNULL(T1.DO_NoWeb,'')<>'' THEN T1.DO_NoWeb ELSE T1.DO_Ref END

,T1.DO_Date,T1.DO_Tiers,T3.CT_Intitule

FROM FACTURE_ENTETE T1

LEFT JOIN SAS_FACTURE T2 ON T2.DO_Piece=T1.DO_Piece AND T2.Type_Lig='XML' AND T2.DO_Tiers=T1.DO_Tiers

JOIN CLIENTS T3 ON T3.CT_Num=T1.DO_Tiers

WHERE T1.DO_Domaine=0 AND T1.DO_Type=7 AND T1.DO_Statut=2

AND T1.DO_Piece LIKE 'FA%'

AND T2.DO_Piece IS NULL

AND T1.DO_Tiers=@DO_Tiers

SELECT @Nbre_Factures = COUNT(*) FROM #Factures

SELECT @Num_Facture = 1

WHILE @Num_Facture <= @Nbre_Factures

BEGIN

BEGIN TRY

SELECT @Message_EDI = ''

,@Numero_Serie = ''

,@Numero_Tracking= ''

,@Nbre_Lig = 0

,@Num_Lig = 1

,@AR_Ref = NULL

,@Message_EDI_XML= NULL

,@DL_PieceBL = NULL

SELECT @DO_Piece=DO_Piece,@DO_Ref=DO_Ref,@DO_Date=DO_Date,@Numero_Tracking='' FROM #Factures WHERE Num_Lig=@Num_Facture

SELECT @DL_PieceBL = DL_PieceBL FROM F_DOCLIGNE WHERE DO_Piece=@DO_Piece AND ISNULL(DL_PieceBL,'')<>''

TRUNCATE TABLE #NumeroSerie

INSERT INTO #NumeroSerie(AR_Ref)

SELECT DISTINCT REFERENCE FROM [LISTE_IMEI] WHERE COMMANDE=@DL_PieceBL AND ISNULL(SERIE,'')<>''

SELECT @Nbre_Lig = COUNT(AR_Ref) FROM #NumeroSerie

WHILE @Num_Lig <= @Nbre_Lig

BEGIN

SELECT @AR_Ref = AR_Ref FROM #NumeroSerie WHERE Num_Lig = @Num_Lig

SELECT @Numero_Serie = @Numero_Serie + ' | ' + CAST(SERIE AS VARCHAR(13)) FROM [dbo].[LISTE_IMEI] WHERE COMMANDE=@DL_PieceBL AND REFERENCE=@AR_Ref

SELECT @Numero_Serie = SUBSTRING(@Numero_Serie, CHARINDEX('|',@Numero_Serie) + 1 , LEN(@Numero_Serie))

UPDATE #NumeroSerie

SET

SERIE = LTRIM(RTRIM(@Numero_Serie))

WHERE Num_Lig = @Num_Lig

SELECT @Num_Lig = @Num_Lig + 1

END

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

BEGIN

DROP TABLE #Commande_XML

END

SELECT @Message_EDI=Ligne_Commande FROM SAS_COMMANDE_EDI WHERE DO_Tiers=@DO_Tiers AND Num_Commande=@DO_Ref AND Type_Lig='ENT'

SELECT @Numero_Tracking= @Numero_Tracking + ' | ' + CAST(NUM_LT_CHRONOPOSTE AS VARCHAR(13)) FROM (SELECT DISTINCT NUM_LT_CHRONOPOSTE FROM dbo.CHRONOPOSTE_TRACKING WHERE REF_DESTINATAIRE=@DL_PieceBL) T1

SELECT @Numero_Tracking= SUBSTRING(@Numero_Tracking, CHARINDEX('|',@Numero_Tracking) + 1 , LEN(@Numero_Tracking))

EXEC sp_xml_preparedocument@IdDocXML OUTPUT, @Message_EDI

SELECT * INTO #Commande_XML FROM OPENXML(@Id_Doc,'/Commandes/Commande/Detail/Ligne',2)

WITH (NumClient varchar(40) '//@NumClient',

MsgID varchar(30) '//@MsgID',

Devise varchar(3) '//@Devise',

Titre varchar(30) '//Titre',

DateCommande varchar(8) '//DateCommande',

DateLivraison varchar(8) '//DateLivraison',

Nom1varchar(35) '//Nom1',

Nom2varchar(35) '//Nom2',

Nom3varchar(35) '//Nom3',

Nom4varchar(35) '//Nom4',

Nom5varchar(35) '//Nom5',

CodePostalvarchar(9) '//CodePostal',

Ville varchar(35) '//Ville',

Pays varchar(250) '//Pays',

NomContact varchar(250) '//NomContact',

TelephoneContact varchar(250) '//TelephoneContact',

MailContact varchar(250) '//MailContact',

Livraison_Type varchar(250) '//Livraison/@Type',

Livraison_Full varchar(250) '//Livraison/@Full',

Livraison_Desc varchar(250) '//Livraison',

CommandeOrigine_Entete varchar(250) '//CommandeOrigine',

TexteLibre_Type varchar(250) '//TexteLibre/@Type',

TexteLibre_Desc varchar(250) '//TexteLibre',

ShipTxt varchar(250) '//ShipTxt',

Ligne_ID varchar(250) '@ID',

Code_Article_Vendeur varchar(250) './ElementID',

Code_Article_Acheteur varchar(250) '//AddElementID/@Buyer',

Code_EAN_Article varchar(250) '//AddElementID/@EAN',

Quantite_Commandee varchar(250) './Quantite',

Prix varchar(250) './Prix',

CommandeOrigine_Ligne varchar(250) './CommandeOrigine'

)

EXEC sp_xml_removedocument@IdDocXML

SELECT * from #Commande_XML

SELECT @Message_EDI=(SELECT (SELECT TOP 1 Numclient FROM #Commande_XML) AS '@Numclient'

,(SELECT COUNT(*) NbreINVOIC FROM (SELECT DISTINCT DO_Piece FROM SAS_FACTURE WHERE DO_Tiers=@DO_Tiers ) S1) + 1 AS '@MsgID' ,(

SELECT 'EUR' '@Devise' ,(SELECT CONVERT(VARCHAR(8),GETDATE(),112) AS 'CreationMessage/Date'

,CAST(REPLACE(CAST(GETDATE() AS Heure(7)),':','') AS CHAR(6)) AS 'CreationMessage/Heure'

,'Facture' AS 'Type'

,S1.DO_Piece AS 'Reference/NumeroCommande'

,CONVERT(VARCHAR(8),S1.DO_Date ,112) AS 'Reference/DateCommande'

,'-' AS 'Reference/DeliveryNote'

,CASE WHEN ISNULL(S1.DO_NoWeb,'')<>'' THEN S1.DO_NoWeb WHEN ISNULL(S1.DO_Ref,'')<>'' THEN S1.DO_Ref ELSE '-' END AS 'Reference/CustPO'

,(SELECT TOP 1 OrigPO_Entete FROM #Commande_XML ) AS 'Reference/OrigPO'

FROM FACTURE_ENTETE S1

WHERE DO_Piece=@DO_Piece

FOR XML PATH ('Head'),TYPE

)

,(SELECT ISNULL((SELECT TOP 1 Ligne_ID FROM #Commande_XML WHERE ElementID=S1.AR_Ref),ROW_NUMBER() OVER(Commande BY S1.AR_Ref)) AS '@ID'

,'Interne' AS 'ElementID/@Type'

,S1.AR_Ref AS 'ElementID'

,'Manufacturer' AS 'ElementID_2/@Type'

, ISNULL(S2.AF_RefFourniss,'-') AS 'ElementID_2'

,'EAN' AS 'ElementID_3/@Type'

,ISNULL(LTRIM(RTRIM(S2.AF_CodeBarre)),'-') AS 'ElementID_3'

,S3.AR_Design AS 'ElementText'

,CAST(S1.DL_Qte AS NUMERIC(24,0)) AS 'ElementQty'

,ISNULL((SELECT TOP 1 SERIE FROM #NumeroSerie SR WHERE SR.AR_Ref=S1.AR_Ref),'-') AS 'SerialNo'

,CAST(S1.DL_PrixUnitaire AS NUMERIC(24,2)) AS 'ElementPrix'

,CAST(S1.DL_PrixUnitaire * S1.DL_Qte AS NUMERIC(24,2)) AS 'ElementTotalPrix'

,'ECOTAXE' AS 'ElementCharges_1/@Type'

,CAST(S1.ECOTAXE AS NUMERIC(24,2)) AS 'ElementCharges_1/ChargeMontant'

,CAST(S1.DL_Taxe2 AS NUMERIC(24,2)) AS 'ElementCharges_1/ChargeParUnit'

,'SORECOP' AS 'ElementCharges_2/@Type'

,CAST(S1.SORECOP AS NUMERIC(24,2)) AS 'ElementCharges_2/ChargeMontant'

,CAST(S1.DL_Taxe3 AS NUMERIC(24,2)) AS 'ElementCharges_2/ChargeParUnit'

,CAST(S1.DL_PrixUnitaire + S1.ECOTAXE + S1.SORECOP AS NUMERIC(24,2)) AS 'ElementMontant'

,CAST((S1.DL_PrixUnitaire + S1.ECOTAXE + S1.SORECOP) * S1.DL_Qte AS NUMERIC(24,2)) AS 'ElementTotalMontant'

,CAST(S1.DL_Taxe1 AS NUMERIC(24,2)) AS 'ElementTaxableMontant/@TaxRate'

,CAST(((S1.DL_PrixUnitaire + S1.ECOTAXE + S1.SORECOP) * S1.DL_Qte) +(((S1.DL_PrixUnitaire + S1.ECOTAXE + S1.SORECOP) * S1.DL_Qte) * S1.DL_Taxe1/100) AS NUMERIC(24,2)) AS 'ElementTaxableMontant'

,ISNULL(S4.Ligne_ID,'-') AS 'ElementReference/CustLigne'

,ISNULL(S4.OrigPO_Ligne,'-') AS 'ElementReference/OrigPO'

,ISNULL(S4.OrigPO_Ligne,'-') AS 'TexteLibre'

FROM F_DOCLIGNE S1

LEFT JOIN F_ARTFOURNISS S2 ON S2.AR_Ref=S1.AR_Ref AND S2.AF_Principal=1

LEFT JOIN F_ARTICLE S3 ON S3.AR_Ref=S1.AR_Ref

LEFT JOIN #Commande_XML S4 ON S4.ItemID=S1.AR_Ref

WHERE S1.DO_Piece=@DO_Piece

FOR XML PATH ('Ligne'),ROOT ('Detail'),TYPE

)

SELECT 'FREIGHT' AS 'Charges/Montant/@Type',CAST((SELECT SUM(ISNULL(DO_ValFrais,0)) DO_ValFrais FROM FACTURE_ENTETE WHERE DO_Piece=@DO_Piece) AS NUMERIC(24,2)) AS 'Charges/Montant'

,'ECOTAXE' AS 'Charges/Montant_1/@Type', S1.ECOTAXE AS 'Charges/Montant_1'

,'SORECOP' AS 'Charges/Montant_2/@Type', S1.SORECOP AS 'Charges/Montant_2'

,'TotalItems' AS 'FactureMontants/Montant_3/@Type', TotalItems AS 'FactureMontants/Montant_3'

,'NetTotal' AS 'FactureMontants/Montant_4/@Type',CAST(TotalItems +(SELECT SUM(ISNULL(DO_ValFrais,0)) DO_ValFrais FROM FACTURE_ENTETE WHERE DO_Piece=@DO_Piece) AS NUMERIC(24,2)) AS 'FactureMontants/Montant_4'

,'EndTotal' AS 'FactureMontants/Montant_5/@Type',EndTotal AS 'FactureMontants/Montant_5'

,'TotalTaxes' AS 'FactureMontants/Montant_6/@Type', TotalTaxes AS 'FactureMontants/Montant_6'

,CAST(ISNULL(S2.DL_Taxe1,0) AS NUMERIC(24,2)) AS 'FactureMontants/Taxes/@TaxRate'

,EndTotal AS 'FactureMontants/Taxes/TaxableMontant'

,CAST(EndTotal *(ISNULL(S2.DL_Taxe1,0)/100) AS NUMERIC(24,2)) AS 'FactureMontants/Taxes/TaxMontant'

FROM (SELECT DO_Piece,CAST(SUM(ECOTAXE) AS NUMERIC(24,2)) ECOTAXE,CAST(SUM(SORECOP) AS NUMERIC(24,2)) SORECOP

,CAST(SUM(DL_PrixUnitaire * DL_Qte) AS NUMERIC(24,2)) AS TotalItems

,CAST(SUM(((DL_PrixUnitaire + ECOTAXE + SORECOP) * DL_Qte)) AS NUMERIC(24,2)) AS EndTotal

,CAST(SUM(((DL_PrixUnitaire + ECOTAXE + SORECOP) * DL_Qte *(DL_Taxe1/100) )) AS NUMERIC(24,2)) AS TotalTaxes

FROM F_DOCLIGNE WHERE DO_Piece=@DO_Piece GROUP BY DO_Piece) S1

LEFT JOIN(SELECT TOP 1 DL_Taxe1,DO_Piece FROM F_DOCLIGNE WHERE DO_Piece=@DO_Piece AND ISNULL(DL_Taxe1,0)<>0) S2 ON S2.DO_Piece=S1.DO_Piece

FOR XML PATH ('Summary'),TYPE

)

FOR XML PATH ('Facture'),TYPE

) FOR XML PATH ('Factures')

)

INSERT INTO #Journal(DO_Piece,DO_Ref,DO_Date,Statut_FACTURE) VALUES (@DO_Piece,@DO_Ref,@DO_Date,'Y')

END TRY

BEGIN CATCH

IF @@ERROR<>0

BEGIN

SELECT @Statut_FACTURE = @Statut_FACTURE + ' DO_Piece : ' + ISNULL(@DO_Piece,'') + ' DO_Ref : ' + ISNULL(@DO_Ref, '') + ERROR_MESSAGE()

INSERT INTO #Journal(DO_Piece,DO_Ref,DO_Date,Statut_FACTURE) VALUES (@DO_Piece,@DO_Ref,@DO_Date,@Statut_FACTURE)

END

ELSE

BEGIN

INSERT INTO #Journal(DO_Piece,DO_Ref,Statut_FACTURE) VALUES (@DO_Piece,@DO_Ref,'E')

END

END CATCH

SELECT @Indice = 1

WHILE @Indice<=6

BEGIN

SELECT @Message_EDI = REPLACE(@Message_EDI,'ElementID_' + CAST(@Indice AS CHAR(1)),'ElementID')

SELECT @Message_EDI = REPLACE(@Message_EDI,'Montant_' + CAST(@Indice AS CHAR(1)),'Montant')

SELECT @Message_EDI = REPLACE(@Message_EDI,'ElementCharges_' + CAST(@Indice AS CHAR(1)),'ElementCharges')

SELECT @Indice = @Indice + 1

END

SELECT @Message_EDI_XML = @Message_EDI

--SELECT @Message_EDI_XML

TRUNCATE TABLE dbo.SAS_FACTURE_TEMP

INSERT INTO dbo.SAS_FACTURE_TEMP(Num_FACTURE,Type_Lig,Date_Export,DO_Piece,DO_Ref,Do_Date,DO_Tiers,CT_Intitule,Ligne_FACTURE)

SELECT 1,'XML',GETDATE(),DO_Piece,DO_Ref,DO_Date,DO_Tiers,CT_Intitule,@Message_EDI FROM #Factures WHERE Num_Lig=@Num_Facture

IF EXISTS(SELECT Ligne_FACTURE FROM dbo.SAS_FACTURE_TEMP) AND

NOT EXISTS(SELECT Ligne_FACTURE FROM dbo.SAS_FACTURE_TEMP T1

JOIN #Journal T2 ON T2.DO_Piece=T1.DO_Piece AND T2.DO_Ref=T1.DO_Ref AND T2.Statut_FACTURE='E')

BEGIN

SELECT @Nom_Fichier_INVOIC = 'FACTURE_MEKTABA' + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + CONVERT(VARCHAR(12), GETDATE(), 114),':','_') + '.xml'

SELECT @CommandeShell = 'bcp "Select Ligne_FACTURE from SAS_FACTURE_TEMP" queryout "D:\MES_FICHIERS\FACTURE\' + @Nom_Fichier_INVOIC + '" -T -w'

EXEC Master..xp_cmdshell@CommandeShell

--Envoyer le fichier généré sur FTP

EXEC [dbo].[usp_FTP_Send]@ConnectionStringFTP ='ftp.mektaba.info'

,@ServerUserNameFTP='LoginFTP'

,@PasswordFTP='PassWordFTP'

,@PathFTP='/FACTURES/'

,@FichierFTP='FACTURE*.xml'

,@CheminFichiersTransferes='D:\MES_FICHIERS\FACTURE\'

,@CheminFichiersArchives='D:\MES_FICHIERS\FACTURE\ARCHIVE\'

--Sauvegarder dans la BD le message FACTURE généré

INSERT INTO dbo.SAS_FACTURE(Num_FACTURE,Type_Lig,Date_Export,DO_Piece,DO_Ref,Do_Date,DO_Tiers,CT_Intitule,Ligne_FACTURE,Statut_FACTURE)

SELECT Num_FACTURE,Type_Lig,Date_Export,DO_Piece,DO_Ref,Do_Date,DO_Tiers,CT_Intitule,Ligne_FACTURE,'Y' FROM dbo.SAS_FACTURE_TEMP

END

SELECT @Num_Facture = @Num_Facture + 1

END

IF EXISTS( SELECT DISTINCT DO_Piece,Do_Date,Statut_FACTURE FROM #Journal WHERE Statut_Invoice='Y' )

BEGIN

SELECT @emailSubject ='FACTURE CLIENT SUCCÈS D''ENVOIE'

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

N'<H>Veuillez trouver ci-dessous la liste des FACTURES qui ont été envoyés vers LE CLIENT : </H>' +

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

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

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

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;"><table cellpadding=0 cellspacing=0 bCommande=0>' +

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

N'<td align=center>N°</td>' +

N'<td align=center>PIECE</td>' +

N'<td align=center>REFERENCE</td>' +

N'<td align=center>DATE FACTURE</td>' +

N'<td align=center>STATUT ENVOIE</td></tr>'

SELECT @Detail =

(

SELECT td=ROW_NUMBER() OVER(Commande BY DO_Piece) ,

td = DO_Piece,

td = DO_Ref,

td = CONVERT(VARCHAR(10),DO_Date,103),

td = Statut_Invoice

FROM ( SELECT DISTINCT DO_Piece,DO_Ref,Do_Date,Statut_Invoice FROM #Journal WHERE Statut_FACTURE='Y' ) T

Commande BY DO_Piece

FOR XML RAW('tr'), ELEMENTS

)

SET @Detail = REPLACE(@Detail, '<td>', '<td align=left>')

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

SET @Detail = REPLACE(@Detail, '_x0020_', SPACE(1))

SET @Detail = Replace(@Detail, '_x003D_', '=')

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

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

SET @tableHTML = @tableHTML + N'<H>Cordialement, </H>'

SET @tableHTML = @tableHTML + N'<H>Service Informatique</H>'

SELECT @tableHTML = ISNULL(@tableHTML,@DO_Tiers)

, @emailSubject = ISNULL(@emailSubject,@DO_Tiers)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'MektabaProfilMail',

@recipients = @recipients,

@Detail = @tableHTML,

@subject = @emailSubject,

@Detail_format = 'HTML'

END

IF EXISTS( SELECT DISTINCT DO_Piece,Do_Date,Statut_FACTURE FROM #Journal WHERE Statut_FACTURE<>'Y' )

BEGIN

SELECT @emailSubject ='FACTURE INMAC ECHEC D''ENVOIE'

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

N'<H>Veuillez trouver ci-dessous la liste des FACTURE qui sont en échec et n''ont pas pu être envoyés vers le CLIENT : </H>' +

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

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

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

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;"><table cellpadding=0 cellspacing=0 bCommande=0>' +

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

N'<td align=center>N°</td>' +

N'<td align=center>PIECE</td>' +

N'<td align=center>REFERENCE</td>' +

N'<td align=center>DATE COMMANDE</td>' +

N'<td align=center>STATUT ENVOIE</td></tr>'

SELECT @Detail =

(

SELECT td=ROW_NUMBER() OVER(Commande BY DO_Piece) ,

td = DO_Piece,

td = DO_Ref,

td = CONVERT(VARCHAR(10),DO_Date,103),

td = Statut_FACTURE

FROM ( SELECT DISTINCT DO_Piece,DO_Ref,Do_Date,Statut_FACTURE FROM #Journal WHERE Statut_FACTURE<>'Y' ) T

Commande BY DO_Piece

FOR XML RAW('tr'), ELEMENTS

)

SET @Detail = REPLACE(@Detail, '<td>', '<td align=left>')

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

SET @Detail = REPLACE(@Detail, '_x0020_', SPACE(1))

SET @Detail = Replace(@Detail, '_x003D_', '=')

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

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

SET @tableHTML = @tableHTML + N'<H>Cordialement, </H>'

SET @tableHTML = @tableHTML + N'<H>Service Informatique</H>'

SELECT @tableHTML = ISNULL(@tableHTML,@DO_Tiers)

, @emailSubject = ISNULL(@emailSubject,@DO_Tiers)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'MektabaProfilMail',

@recipients = @recipients,

@Detail = @tableHTML,

@subject = @emailSubject,

@Detail_format = 'HTML'

END

Suivant >

ZOUHAIER KHARROUBI

Navigation de recherche

Navigation

Recherche

.Net

SQL Server

[SQL Server] Génération d'un fichier XML