declare @emailSubject varchar(100),
@textTitle varchar(100),
@tableHTML nvarchar(max)
select @emailSubject = 'My Test Email',
@textTitle = 'EmailTable'
set @tableHTML = '<html><head><style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' + '</style></head><body>' +
'<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +
@textTitle + '</div>' +
'<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#4b6c9e>' +
'<td align=center><font face="calibri" color=White><b>Cliente</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>PO</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>Factura</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>Moneda</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>Vencimiento</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>Fecha Factura</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>Condiciones</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>Vendedor</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>Actual</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>1 a 30</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>31 a 60</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>61 a 90</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>91 a 120</b></font></td>' + -- Manually type headers
'<td align=center><font face="calibri" color=White><b>as de 120</b></font></td></tr>' -- Manually type headers
declare @body varchar(max)
select @body =
(
SELECT
--td = v.EMailAddr,
td = Rtrim(d.CustID)+' '+Rtrim(c.Name),
td = SOShipHeader.CustOrdNbr,
td = d.RefNbr,
td = d.CuryID,
td = d.DueDate,
td = d.DocDate,
td = ISNULL(t.Descr, ''),
td = Rtrim(c.SlsperId)+' '+Rtrim(v.Name),
--Actual
td = CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1
ELSE -1
END * CASE WHEN d.DocType NOT IN ('CM', 'PA', 'PP') AND CONVERT(VarChar(50), GETDATE(), 102) <= d.DueDate OR d.DocType IN ('CM', 'PA', 'PP') AND (CONVERT(VarChar(50), GETDATE(), 102)<=d.DocDate OR ARSetup.S4Future09=0) THEN d.DocBal
ELSE 0
END,
--1 a 30
--'Past00(1 a 30)' =
td=CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1
ELSE -1
END * CASE WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate
ELSE d.DueDate
END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays00 AND
DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate
ELSE d.DueDate
END, CONVERT(VarChar(50), GETDATE(), 102)) >= 1 AND
(d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal
ELSE 0 END,
--31 a 60
--'Past01 (31 a 60)' =
td=CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays01 AND--menor 60 DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays00 AND--mayor 30 (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal ELSE 0 END,
--61 a 90
--'Past02 (61 a 90)' =
td=CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays02 AND --menor 90 DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays01 AND --mayor 60 (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal --91 a 120
ELSE 0 END,
--'Over02 (91 a 120)' =
td=CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= 120 AND --menor 120 DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays02 AND --mayor 90 (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal ELSE 0 END,
--mas de 120
--'Over03 (mas 120)' =
td=CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END * CASE WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > 120 AND (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) THEN d.DocBal ELSE 0 END
--
FROM --RptRuntime r INNER JOIN RptCompany y
-- ON y.RI_ID=r.RI_ID
--INNER JOIN
ARDoc d
left JOIN SOShipHeader ON D.RefNbr=SOShipHeader.InvcNbr
-- ON d.CpnyID=y.CpnyID
INNER JOIN AR_Balances b
ON b.CustID=d.CustID
INNER JOIN Customer c
ON c.CustID=d.CustID
inner join Salesperson v
on v.SlsperId=c.SlsperId
INNER JOIN (SELECT StmtCycleID, AgeDays00 = CONVERT(INT,AgeDays00),
AgeDays01 = CONVERT(INT,AgeDays01), AgeDays02 = CONVERT(INT,AgeDays02)
FROM ARStmt) s
ON s.StmtCycleID=c.StmtCycleID
LEFT JOIN Terms t
ON d.Terms <> '' AND t.TermsID=d.Terms
CROSS JOIN ARSetup (NOLOCK)
WHERE d.Rlsed=1 AND d.DocBal<>0 and d.CustId='00336'
for XML raw('tr'), elements)
set @body = REPLACE(@body, '<td>', '<td align=center><font face="calibri">')
set @body = REPLACE(@body, '</td>', '</font></td>')
set @body = REPLACE(@body, '_x0020_', space(1))
set @body = Replace(@body, '_x003D_', '=')
set @body = Replace(@body, '<tr><TRRow>0</TRRow>', '<tr bgcolor=#F8F8FD>')
set @body = Replace(@body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#EEEEF4>')
set @body = Replace(@body, '<TRRow>0</TRRow>', '')
set @tableHTML = @tableHTML + @body + '</table></div></body></html>'
set @tableHTML = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;">' + @tableHTML + '</div>'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Mail',
@recipients = 'usuario@correo.com.mx',
@body = @tableHTML,
@subject = @emailSubject,
@body_format = 'HTML'
