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

USE [TempDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetMailContent]    Script Date: 2018/1/30 上午 10:15:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--================================================================================
--撰寫日期 :
--撰寫人員 :
--程式說明 :
--使用範例 :
--規　　格 :
--異動記錄 :  找出使用的的mail body內容
--EXEC usp_GetMailContent  'CaseFlowVerify','B41','B41_BtnAssignBoss','','N','107006'
--================================================================================
ALTER PROCEDURE [dbo].[usp_GetMailContent]
( 
     @CallProg VARCHAR(20),     
  @CaseNO VARCHAR(48),       --案件編號
  @FlowID     VARCHAR(10),   --主簽核/子簽核   
  @MailStepID VARCHAR(10),   --流程關卡
  @MailBtnID VARCHAR(20),    --關卡按鍵
  @MailType VARCHAR(10),     --轉介類別
  @IsClosed VARCHAR(10),     --結案記號 Y/N/C/R 未結案/結案/取消/退回
  @EmpID VARCHAR(10)         --處理人員
)
AS
BEGIN
SET NOCOUNT OFF;

DECLARE @ERROR int
DECLARE @ERROR_MESSAGE nvarchar(4000)
DECLARE @SQL varchar(max)
DECLARE @MailTo varchar(max)
DECLARE @MailCC varchar(max)
DECLARE @MailBCC varchar(max)
DECLARE @MailAttach varchar 
DECLARE @MailAdvice varchar 
DECLARE @Subject varchar(200)
DECLARE @Body varchar(max)          
DECLARE @Footer varchar(200)

SET @SQL = ''
SET @MailTo =  ''
SET @MailCC =  ''
SET @MailBCC =  ''
SET @MailAttach =  ''
SET @Subject =  ''
SET @Body =  ''
SET @Footer =  ''
 
SET @SQL = 'exec  usp_GetMailContent '+ @CallProg   + ',' + @MailStepID  + ',' +  @MailBtnID  + ',' +  @MailType  + ',' + @IsClosed + ',' + @EmpID

INSERT  Sys_Log   ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
VALUES (GETDATE(),@CaseNO,'usp_GetMailContent',@CallProg,'Get MailContent','Start',@MailStepID + ' ' + @MailBtnID ,'','','',@SQL,@EmpID)
  
IF (@IsClosed = 'N')
BEGIN
 SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC,@MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer =Footer
 FROM Sys_MailContent
 WHERE FlowID=@FlowID AND MailStepID = @MailStepID  and  MailBtnID = @MailBtnID  and  MailType = @MailType
 
 if (@Body  <>  '') --主案件
 BEGIN   
  GOTO END_DATA
 END 
 ELSE
 BEGIN

     SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC, @MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer=Footer
     FROM Sys_MailContent WHERE MailStepID = 'Default' 
 
  GOTO END_DATA
 END
END

IF (@IsClosed = 'Y') --結案/知悉並結案
BEGIN 
        --SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC, @MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer=Footer
     --FROM Sys_MailContent WHERE MailStepID = 'Closed'   
 
  if (@FlowID  <>  '' AND @MailStepID <> '' AND @MailBtnID <> '') --主案件
  BEGIN  
      SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC,@MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer =Footer
   FROM Sys_MailContent
   WHERE FlowID=@FlowID AND MailStepID = @MailStepID  and  MailBtnID = @MailBtnID  and  MailType = @MailType    
  END

  if (@Body  <>  '') --主案件
  BEGIN   
    GOTO END_DATA
  END 
  ELSE   
  BEGIN  
      SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC, @MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer=Footer
         FROM Sys_MailContent WHERE MailStepID = 'Closed'
  END
END

IF (@IsClosed = 'R') --退回
BEGIN 
        SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC, @MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer=Footer
     FROM Sys_MailContent WHERE MailStepID = 'Return'
END

IF (@IsClosed = 'C') --取消
BEGIN 
        SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC, @MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer=Footer
     FROM Sys_MailContent WHERE MailStepID = 'Cancel'
END

IF (@IsClosed = 'E') --逾期
BEGIN 
        SELECT  @MailTo=MailTo, @MailCC=MailCC,@MailBCC=MailBCC, @MailAttach=MailAttach, @Subject=Subject,@Body =Body,@Footer=Footer
     FROM Sys_MailContent WHERE MailStepID = 'Expire'
END
-----------------------------------------------------------
END_NoDATA:
SET @ERROR = 0
SET @ERROR_MESSAGE = '無資料'
print 'usp_GetMailContent 無資料'
GOTO END_PROG

END_DATA:
SET @ERROR = 0
SET @ERROR_MESSAGE = '有資料'
print 'usp_GetMailContent 有資料'

GOTO END_PROG
---------------------------------------------------------- 
END_PROG:
 IF @ERROR <> 0
 BEGIN
  INSERT  Sys_Log   ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
  VALUES (GETDATE(),@CaseNO,'usp_GetMailContent',@CallProg,'Get MailContent','Error',@MailStepID + ' ' + @MailBtnID,'','',@ERROR_MESSAGE,'',@EmpID)
    
  print 'usp_GetMailContent 程式發生錯誤：' +  @ERROR_MESSAGE

     RAISERROR(@ERROR_MESSAGE , 18, 11)
 END
 ELSE
 BEGIN 
 
     SET @SQL =  @MailTo   + ',' + @MailCC  + ',' +  @MailBCC  + ',' +  @MailAttach  + ',' + @Subject + ',' + @Body + ',' + @Footer 

     INSERT  Sys_Log   ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
  VALUES (GETDATE(),@CaseNO,'usp_GetMailContent',@CallProg,'Get MailContent','End',@MailStepID + ' ' + @MailBtnID,'','',@ERROR_MESSAGE,@SQL,@EmpID)
  
  print 'usp_GetMailContent 執行完成：' +   convert(char(23),getdate(),21)
       
  SELECT   @MailTo MailTo, @MailCC MailCC,@MailBCC MailBCC, @MailAttach MailAttach, @Subject Subject,@Body Body,@Footer Footer
 END 
END