USE [20417CDR];
GO
/*Declare Variables for DML*/
DECLARE @ReportingPeriodStart DATE;
DECLARE @ReportingPeriodEnd DATE;
/*Declare Variables for HTML*/
DECLARE @Style NVARCHAR(MAX)= '';
DECLARE @tableHTML NVARCHAR(MAX)= '';
DECLARE @variablechk INT='';
/*Define CSS for html to use*/
SET @Style += +N'<style type="text/css">' + N'.tg {border-collapse:collapse;border-spacing:0;border-color:#aaa;}'
+ N'.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#333;background-color:#fff;}'
+ N'.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:#aaa;color:#fff;background-color:#0F3870;}'
+ N'.tg .tg-9ajh{font-weight:bold;background-color:lightgrey}' + N'.tg .tg-hgcj{font-weight:bold;text-align:center}'
+ N'</style>';
SET @tableHTML = @Style + @tableHTML + N'<H2>CDR Summary</H2>'
+ N'<table class="tg">' --DEFINE TABLE
/*Define Column Headers and Column Span for each Header Column*/
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="4">CDR</th>'
+ N'</tr>'
+ N'</tr>'
/*Define Column Sub-Headers*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
/*Define data for table and cast to xml*/
+ CAST(( SELECT td = CONVERT(CHAR(8), count(id), 112),
'',
td = CAST(SUM(DurationInSeconds) / 60 AS NUMERIC(18,2)) ,
''
FROM [TOneWhS_CDR].[CDR]
FOR
XML PATH('tr') ,
TYPE
) AS NVARCHAR(MAX))
+ N'</table>'
+ N'</br>'
+ N'</br>'
+ N'<table class="tg">' --DEFINE TABLE
/*Define Column Headers and Column Span for each Header Column*/
+ N'<tr>'
+ N'<th class="tg-hgcj" colspan="2">CDR Main</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR Invalid</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR Failed</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR Partial</th>'
+ N'<th class="tg-hgcj" colspan="2">CDR interconnect</th>'
+ N'</tr>'
/*Define Column Sub-Headers*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
+ N'<td class="tg-9ajh">Total Attempt</td>'
+ N'<td class="tg-9ajh">Total Duration in Min</td>'
/*Define data for table and cast to xml*/
+ CAST(( SELECT td = CONVERT(CHAR(8), count(a.CDRId), 112),
'',
td = CAST(SUM(a.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(b.CDRId), 112),
'',
td = CAST(SUM(b.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(c.CDRId), 112),
'',
td = CAST(SUM(c.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(d.CDRId), 112),
'',
td = CAST(SUM(d.DurationInSeconds) / 60 AS NUMERIC(18,2)),
'',
td = CONVERT(CHAR(8), count(e.CDRId), 112),
'',
td = CAST(SUM(e.DurationInSeconds) / 60 AS NUMERIC(18,2)),
''
from [TOneWhS_CDR].[BillingCDR_Main] a
full join [TOneWhS_CDR].[BillingCDR_Invalid] b
on a.CDRId = b.CDRId
full join [TOneWhS_CDR].[BillingCDR_Failed] c
on a.CDRId = c.CDRId
full join [TOneWhS_CDR].[BillingCDR_PartialPriced] d
on a.CDRId = d.CDRId
full join [TOneWhS_CDR].[BillingCDR_Interconnect] e
on a.CDRId = e.CDRId
FOR
XML PATH('tr') ,
TYPE
) AS NVARCHAR(MAX))
+ N'</table>'
+ N'</br>'
+ N'</br>'
+ N'<table class="tg">' --DEFINE TABLE
/*Define Column Sub-Headers 2 RESULT SECTION!!!!!!*/
+ N'<tr>'
+ N'<td class="tg-9ajh">Result</td>'
+ N'<td class="tg-9ajh" style="background-color:red;"></td>'
/*Define data for table and cast to xml*/
+ 'MISSING CODE'
+ N'</table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mahmoud El Khatib',
@recipients = 'my mail',
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'CDR';I'm writing this code to send a table with specific data in a table with html and CSS code imbedded, in the result section I'm trying to write a store procedure that return true or false, if it's true the output will be "All good! (with green background) and if it's false the output will be "All Bad! (with red background).
I could not find the write code to put my store procedure in the sql code, can anyone help me out?
DROP procedure IF EXISTS spchecktables;
GO
create procedure spchecktables
as
declare @cdr int
declare @maincdrtable int
declare @invalidcdrtable int
declare @failedcdrtable int
declare @interconnectcdrtable int
declare @partialcdrtable int
select @cdr = count(id) from [TOneWhS_CDR].[CDR]
select @maincdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Main]
select @invalidcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Invalid]
select @failedcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Failed]
select @interconnectcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_Interconnect]
select @partialcdrtable = count(CDRId) from [TOneWhS_CDR].[BillingCDR_PartialPriced]
if (@cdr = @maincdrtable + @invalidcdrtable + @failedcdrtable + @interconnectcdrtable + @partialcdrtable )
print 'All is good!'
else
print 'all is bad'
