我也来添砖加瓦。
下面是之前做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
转载于:https://www.cnblogs.com/universsky/p/4256293.html