USE [tempDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_SendAlert]
AS
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @empno CHAR(4) , @name NVARCHAR(5), @num VARCHAR(3), @email VARCHAR(50);
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT EmpNo,Employees.Name_C,COUNT(tablename) Total,Employees.e_mail INTO #tmp FROM ut_MailAlertRecord
LEFT JOIN Employees ON ut_MailAlertRecord.EmpNo=Employees.Employee_id
WHERE ut_MailAlertRecord.Sent='N'
GROUP BY ut_MailAlertRecord.EmpNo,Employees.Name_C,Employees.e_mail
DECLARE cursor1 CURSOR FOR
SELECT * FROM #tmp
OPEN cursor1
FETCH next FROM cursor1 INTO @empno,@name,@num,@email
-- PRINT 'AFTER CURSOR'
PRINT @EMPNO
PRINT @NUM
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- PRINT @name
PRINT @email
SET @tableHTML =
N'<table border="0">' +
-- N'<tr><p><img id="MailBanner" src="\\FileSever\MailBanner.png"></p></tr>' +
N'<tr><p><img id="MailBanner" src="http://eip.net:8088/SiteAssets/MailBanner.png"></p></tr>' +
N'<tr><b><p align=center>待簽核通知</p></b></tr>' +
N'<tr><p>申請資訊</p></tr>' +
@name + N'您好：' +
N'<tr>目前逾限三天的表單共有</tr>' + @num +
N'張需要您簽核，謝謝。' +
N'<tr><p>系統超連結: <a href="http://eip.net:8088/ESignFlow/EIP0041/Forms/AllItems.aspx">ESignFlow</a></p></tr>' +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ESignFlow',@recipients = @email,
@subject = '[電子簽核]待簽核通知',
@body = @tableHTML,@body_format = 'HTML' ;
UPDATE ut_MailAlertRecord SET sent='Y' WHERE sent='N' AND EmpNo=@empno;
FETCH next FROM cursor1 INTO @empno,@name,@num,@email
-- PRINT 'NEXT CURSOR'
PRINT @empno
PRINT @num
PRINT @email
PRINT '----------------------------'
END
CLOSE cursor1
DEALLOCATE cursor1
DROP TABLE #tmp;
END
