下面是之前做QC二次开发时实现的一个功能：在数据库中获取各种记录的变更信息，然后通过数据库发送邮件给指定人员；邮件正文以表格形式显示信息。
实现并不困难，只是我们以往发送邮件都是通过应用层实现，这次是通过数据库层实现，有点不同。
备注：QC版本为9.2；数据库为Sqlserver 2005。
– =============================================
– Author: chenxuan
– Create date: 2008-5-20
– Description: Change Info SendMail
– =============================================
ALTER PROCEDURE [dbo].[SendMail]
AS
BEGIN
DECLARE @tableHTML1 NVARCHAR(MAX) ;
DECLARE @tableHTML2 NVARCHAR(MAX) ;
DECLARE @tableHTML3 NVARCHAR(MAX) ;
DECLARE @tableHTML4 NVARCHAR(MAX) ;
DECLARE @date VARCHAR(MAX) ;
SET @tableHTML1 =
–Plan TestStep Change
N’<H1>Everest Test Plan TestStep History Daily Report</H1>’ +
N’<table border=”1″>’ +
N’<tr><th>Test_Path</th><th>Test_Name</th>’ +
N’<th>Step_Name</th><th>Change_type</th><th>Change_content</th>’ +
N’<th>Change_time</th></tr>’ +
CAST ( ( SELECT td = Test_Path, ”,
td = Test_Name, ”,
td = Step_Name, ”,
td = Change_type, ”,
td = Change_content, ”,
td = CONVERT(varchar(10), change_time, 120)
FROM lenovo_everest_db.dbo.Table_Change
where CONVERT(varchar(10), change_time, 120) = CONVERT(varchar(10), getdate()-1, 120)
ORDER BY Test_Path,Test_Name,Step_Name,Change_type
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N’</table>’ ;
if (@tableHTML1 is null)
begin
SET @tableHTML1 =
N’<H1>”Everest Test Plan TestStep History Daily Report” No Record</H1>’ +
N’<table border=”1″>’+
N’</table>’ ;
end
–Plan TestCase Change
SET @tableHTML2 =
N’<table border=”1″>’ +
N’<H1>Everest Test Plan TestCase History Daily Report</H1>’ +
N’<tr><th>Test_Path</th><th>Test_Name</th>’ +
N’<th>Change_type</th>’ +
N’<th>Change_time</th></tr>’ +
CAST ( ( SELECT td = Test_Path, ”,
td = Test_Name, ”,
td = Change_type, ”,
td = CONVERT(varchar(10), Change_Time, 120)
FROM lenovo_everest_db.dbo.Table_TestChange
where (CONVERT(varchar(10), Change_Time, 120) = CONVERT(varchar(10), GETDATE()-1, 120))
ORDER BY Test_Path, Test_Name, Change_Type
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N’</table>’ ;
if (@tableHTML2 is null)
begin
SET @tableHTML2 =
N’<H1>”Everest Test Plan TestCase History Daily Report” No Record</H1>’ +
N’<table border=”1″>’+
N’</table>’ ;
end
–Lab TestStep Change
SET @tableHTML3 =
N’<table border=”1″>’ +
N’<H1>Everest Test Lab TestStep History Daily Report</H1>’ +
N’<tr><th>TestSet_Path</th><th>TestSet_Name</th>’ +
N’<th>Test_Name</th><th>Step_Name</th>’ +
N’<th>Change_Type</th><th>Change_Content</th>’ +
N’<th>Change_time</th></tr>’ +
CAST ( ( SELECT td = TestSet_Path, ”,
td = TestSet_Name, ”,
td = Test_Name, ”,
td = Step_Name, ”,
td = Change_Type, ”,
td = Change_Content, ”,
td = CONVERT(varchar(10), Change_Time, 120)
FROM lenovo_everest_db.dbo.Table_LabChange
where CONVERT(varchar(10), change_time, 120) = CONVERT(varchar(10), getdate()-1, 120)
ORDER BY TestSet_Path,TestSet_Name,Test_Name,Step_Name,Change_type
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N’</table>’ ;
if (@tableHTML3 is null)
begin
SET @tableHTML3 =
N’<H1>”Everest Test Lab TestStep History Daily Report” No Record</H1>’ +
N’<table border=”1″>’+
N’</table>’ ;
end
–Lab TestCase Change
SET @tableHTML4 =
N’<table border=”1″>’ +
N’<H1>Everest Test Lab TestCase History Daily Report</H1>’ +
N’<tr><th>TestSet_Path</th><th>TestSet_Name</th>’ +
N’<th>Test_Name</th><th>Change_Type</th>’ +
N’<th>Change_time</th></tr>’ +
CAST ( ( SELECT td = TestSet_Path, ”,
td = TestSet_Name, ”,
td = Test_Name, ”,
td = Change_type, ”,
td = CONVERT(varchar(10), Change_Time, 120)
FROM lenovo_everest_db.dbo.Table_TestLabChange
where CONVERT(varchar(10), change_time, 120) = CONVERT(varchar(10), getdate()-1, 120)
ORDER BY TestSet_Path,TestSet_Name,Test_Name,Change_type
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N’</table>’ ;
if (@tableHTML4 is null)
begin
SET @tableHTML4 =
N’<H1>”Everest Test Lab TestCase History Daily Report” No Record</H1>’ +
N’<table border=”1″>’+
N’</table>’ ;
end
SET @tableHTML1 = @tableHTML1+@tableHTML2+@tableHTML3+@tableHTML4+’Any question,please contact the QC admin wangjza@lenovo.com’
SET @date = ‘”Everest Test Plan&Lab History Daily Report”_’+CONVERT(varchar(10), getdate()-1, 120)
–Send
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘tduser’,
@recipients = xx@163.com’,
@body_format = ‘HTML’,
@subject = @date,–’Everest Test Plan&Lab History Daily Report’,
@body = @tableHTML1
END