聽說幸福…在山的那邊 (浪機子)

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