USE [TempDB]
GO
/****** Object: StoredProcedure [dbo].[JOB_ExpireMail_Manual] Script Date: 2018/1/30 上午 10:01:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description: 人工發送mail
-- 異動記錄
-- 執行時間：Web 人工發送mail
-- exec [dbo].[JOB_ExpireMail_Manual] '20160815091514823.001','CFPMainSub','20160815.00001','107006'
-- exec [dbo].[JOB_ExpireMail_Manual] '20160815091514823.001','CFPMainSub','20160815.00002','107006'
-- CFPUser 結構描述 'dbo'，資料庫 'msdb'，物件 'sp_send_dbmail' 沒有 EXECUTE 權限
-- EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = 'CFPUser';
-- =============================================
ALTER PROCEDURE [dbo].[JOB_ExpireMail_Manual]
(
@CaseNo varchar(48),
@FlowID varchar(20),
@FlowCaseID varchar(20),
@CreateEmpID varchar(30)
)
AS
Declare
@delrowcount int,
@UpdateCnt int,
@SystemDate8 char(08), /*系統日*/
@SystemDate10 char(10), /*系統日*/
@ExecuteTime datetime, /*Batch執行時間*/
@ErrorSave int,
@ErrMsg char(50) /*記錄錯誤訊息*/
Select @SystemDate8 = (Select Convert(char(08),getdate(),112) as SystemDate)
Select @SystemDate10 = (Select Convert(char(10),getdate(),111) as SystemDate)
Select @ExecuteTime = (Select Convert(char(19),getdate(),20) as ExecuteTime)
print '********************************************************'
print '** JOB_ExpireMail_Manual 執行開始：' + convert(char(23),getdate(),21)
print '********************************************************'
Begin
Declare @FlowLogID varchar(20)
Declare @FlowStepID varchar(10)
Declare @FlowStepName nvarchar(30)
Declare @AssignTo varchar(10)
Declare @AssignToName nvarchar(20)
Declare @PriorityName varchar(20)
Declare @EMail varchar(60)
Declare @Subject varchar(200)
Declare @ExpireDate varchar(20)
Declare @TypeName varchar(20)
Declare @CategoryName varchar(20)
Declare @CustName nvarchar(50)
Declare @LogCrDateTime varchar(20)
Declare @TmpFlowLogID varchar(20)
Declare @TotAssignTo varchar(max)
Declare @TotAssignToName varchar(max)
Declare @TotEMail varchar(max)
Declare @Body VARCHAR(MAX)
Declare @MailTo VARCHAR(MAX)
Declare @MailCC VARCHAR(MAX)
Declare @MailBCC VARCHAR(MAX)
Declare @MailAttach VARCHAR(MAX)
Declare @MailSubject VARCHAR(200)
Declare @MailBody VARCHAR(MAX)
Declare @MailFooter VARCHAR(200)
DECLARE @SystemDat varchar(19)
DECLARE @SQL varchar(max)
SET @TotEMail = ''
Begin Transaction
SET @SQL = + '@CaseNo=' + @CaseNo + ' @FlowID=' + @FlowID + ' @FlowCaseID=' + @FlowCaseID + ' @CreateEmpID=' + @CreateEmpID
INSERT Sys_Log -- ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[EmpID])
VALUES (GETDATE(),@CaseNo,'JOB_ExpireMail_Manual','','SELECT','Start','','','','',@SQL,'')
--取得逾期通知的Mail資訊
CREATE TABLE #TempMail
( MailTo VARCHAR(MAX)
, MailCC VARCHAR(MAX)
, MailBCC VARCHAR(MAX)
, MailAttach VARCHAR(MAX)
, Subject VARCHAR(200)
, Body TEXT
, Footer VARCHAR(200)
);
INSERT INTO #TempMail
EXEC usp_GetMailContent 'CFPMainSub','Expire','','','','E','SYSTEM'
SELECT @MailTo=MailTo,@MailCC=MailCC,@MailBCC=MailBCC,@MailAttach=MailAttach,@MailSubject=Subject,@MailBody=Body,@MailFooter=Footer,@SystemDat=Convert(char(19),getdate(),120)
from #TempMail
SET @MailSubject = @MailSubject + '[提醒]'
--取得發送逾期通知的MailList
Declare cur_MailList Cursor for
--子流程已逾期，且尚未發送mail的案件
SELECT b.FlowLogID,a.FlowCurrStepID ,a.FlowCurrStepName,c.AssignTo,c.AssignToName
,isnull(Para1.ParaText,'') PriorityName,EmpShare.EMail ,d.Subject
,CONVERT(CHAR(10),Dateadd(D,cast(Para1.ParaDefine as int),c.LogCrDateTime),111) ExpireDate
,isnull(Sys_Type.TypeName,'') TypeName,isnull(CategoryName,'') CategoryName
,CustName,CONVERT(CHAR(8),c.LogCrDateTime ,112) LogCrDateTime
FROM [CFPFlow].[dbo].[CFPMainSubFlowCase] a
INNER JOIN [CFPFlow].[dbo].[CFPMainSubFlowFullLog] b ON a.FlowCaseID = b.FlowCaseID
INNER JOIN [CFPFlow].[dbo].[CFPMainSubFlowOpenLog] c ON b.FlowLogID = c.FlowLogID
INNER JOIN [TempDB].[dbo].[Tbl_CaseMain] d ON d.CaseNo = TempDB.dbo.SplitStr(a.FlowKeyValueList,',',1,'N')
INNER JOIN [TempDB].[dbo].[Sys_Parameter] Para1 ON d.Priority=Para1.ParaValue and Para1.ParaKind = 'Priority'
INNER JOIN [TempDB].[dbo].[EmpShare] EmpShare ON EmpShare.EmpID = c.AssignTo
LEFT JOIN (SELECT * FROM [TempDB].[dbo].[Tbl_ExpireMail] WHERE CaseNo + FlowID + FlowCaseID + cast(Seq as varchar) in ( SELECT CaseNo + FlowID + FlowCaseID + cast(Max(Seq) as varchar)
FROM [TempDB].[dbo].[Tbl_ExpireMail] GROUP BY CaseNo,FlowID,FlowCaseID) ) e
ON e.CaseNo = d.CaseNo and e.FlowCaseID = c.FlowCaseID
LEFT JOIN Sys_Type on d.Type = Sys_Type.TypeID
LEFT JOIN Sys_Category on d.Category = Sys_Category.CategoryID
WHERE LEFT(a.FlowCurrStepID,1) <> 'Z' and LEFT(b.FlowStepID,1) <> 'Z'
AND d.CaseNo = @CaseNo
AND a.FlowID = @FlowID
AND b.FlowCaseID = @FlowCaseID
ORDER BY d.CaseNo,c.FlowCaseID,CONVERT(CHAR(10),Dateadd(D,cast(Para1.ParaDefine as int),c.LogCrDateTime),112)
Open cur_MailList
Fetch next from cur_MailList
Into @FlowLogID,@FlowStepID,@FlowStepName,@AssignTo,@AssignToName,@PriorityName,@EMail,@Subject
,@ExpireDate,@TypeName,@CategoryName,@CustName,@LogCrDateTime
while (@@fetch_status = 0)
BEGIN
if @TotAssignTo <> ''
BEGIN
SET @TotAssignTo = @TotAssignTo + '|' + @AssignTo
SET @TotAssignToName = @TotAssignToName + '|' + @AssignToName
SET @TotEMail = @TotEMail + ';' + @EMail
END
ELSE
BEGIN
SET @Body = @MailBody
--案件優先順序
SELECT @Body = REPLACE(@Body, '[Priority]', @PriorityName)
--案件類型
SELECT @Body = REPLACE(@Body, '[Type]', @TypeName)
--案件編號
SELECT @Body = REPLACE(@Body, '[CaseNo]', @CaseNO)
--到期日
SELECT @Body = REPLACE(@Body, '[ExpireDate]', @ExpireDate)
--主旨
SELECT @Body = REPLACE(@Body, '[Subject]', @Subject)
--系統日
SELECT @Body = REPLACE(@Body, '[SystemDat]', @SystemDat)
SET @TotAssignTo = @AssignTo
SET @TotAssignToName = @AssignToName
SET @TotEMail = @EMail
SET @TmpFlowLogID = @FlowLogID
END
Fetch next from cur_MailList
Into @FlowLogID,@FlowStepID,@FlowStepName,@AssignTo,@AssignToName,@PriorityName,@EMail,@Subject
,@ExpireDate,@TypeName,@CategoryName,@CustName,@LogCrDateTime
END
If (@TotEMail <> '')
Begin
--收件者名稱
SELECT @Body = REPLACE(@Body, '[Title]', @TotAssignToName)
--先發送Mail後，再更新新的資訊
EXEC msdb.dbo.sp_send_dbmail
@profile_name='CFPMail', --設定檔
@recipients=@TotEMail, --收件者
@copy_recipients = @MailCC, --副本
@blind_copy_recipients = @MailBCC, --密件副本
@subject = @MailSubject, --主旨
@body=@Body, --內文
@body_format=HTML --也可以使用HTML格式
--記錄一下發送記錄檔
INSERT [dbo].[Tbl_ExpireMail]
SELECT @CaseNo,@FlowID,@FlowCaseID,isnull(max(SEQ),'') + 1 SEQ,@TmpFlowLogID,@FlowStepID,@FlowStepName,@TotAssignTo,@TotAssignToName,@PriorityName,@TotEMail,@MailCC,@MailBCC,@MailSubject,@Body
,'Y','1',getdate(),'',@CreateEmpID
FROM [TempDB].[dbo].[Tbl_ExpireMail] WHERE CaseNo=@CaseNo AND FlowID=@FlowID AND FlowCaseID = @FlowCaseID
End
Commit Transaction
close cur_MailList
deallocate cur_MailList
End
print '** 處理完成 **'
print '** JOB_ExpireMail_Manual 執行結束：' + convert(char(23),getdate(),21)
print '********************************************************'
Set NoCount off
Return 0
err_Control:
Set NoCount off
Rollback Transaction
print '********************************************************'
print '*** 處理失敗-- JOB_ExpireMail_Manual Fail ***'
print '*** SystemDate = ' + cast(@SystemDate10 as char) + ',' + 'ExecuteTime = ' + cast(@ExecuteTime as char)
print '********************************************************'
Return 1
留言列表
