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

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

--================================================================================
--撰寫日期 :
--撰寫人員 :
--程式說明 :
--使用範例 :
--規　　格 :
--異動記錄 : 檢核CRM帳號是否有使用權限
-- EXEC usp_GetAuthCRM '','20160816142742405.001','105835','SysAuth'
--================================================================================
ALTER PROCEDURE [dbo].[usp_GetAuthCRM]
( 
     @Prog VARCHAR(50),   --程式名
  @CaseNo VARCHAR(48), --案件編號
  @LoginID VARCHAR(10), --登入員編
  @ProgID VARCHAR(50)   --程式編號
)
AS
BEGIN
SET NOCOUNT OFF;

DECLARE @ERROR int
DECLARE @ERROR_MESSAGE nvarchar(4000)
DECLARE @RETURN_MESSAGE_TITLE char(1)
DECLARE @RETURN_MESSAGE_RoleName varchar(200)
DECLARE @SQL nvarchar(max)
DECLARE @DATA nvarchar(200)
DECLARE @IsUserExist varchar(50)

SET @SQL = ''
SET @RETURN_MESSAGE_TITLE = ''
SET @RETURN_MESSAGE_RoleName =''
SET @DATA = ''
SET @IsUserExist = ''
 
SET @SQL = 'exec  usp_GetAuthCRM ' + @Prog  + ',' +  @CaseNo  + ',' +  @LoginID  + ','  + @ProgID

INSERT  Sys_Log   ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
VALUES (GETDATE(),'','usp_GetAuthCRM',@Prog,'查詢權限','Start',@ProgID,'','','',@SQL,@LoginID)
 
BEGIN
    --判斷登入者是否有權限登入Exist
 SELECT @IsUserExist = Case When count(*) > 0 then 'Y' else 'N' end 
 FROM   [TempDB].[dbo].[Tbl_CaseSingLog] --order by caseno,LogCrDateTime  
    WHERE  CaseNo =@CaseNo and EmpID =@LoginID and  FlowStepID not in ('B10','B20')

 if (@IsUserExist = 'Y')
 BEGIN
     --先到FZSRV產生Table
  
  BEGIN TRY 
     declare @ParmDefinition  nvarchar(200)
     set @ParmDefinition = N'@RET varchar(200) OUTPUT '
     SET @SQL =N' SELECT @RET =  ''查詢人員:''  + EmpID + ''-'' + EmpName + ''('' + FlowStepID + ''-'' + FlowStepName + '')  覆核時間: '' + Convert(char(19),LogCrDateTime,120) '
     SET @SQL = @SQL + N' FROM   [TempDB].[dbo].[Tbl_CaseSingLog] '       
     SET @SQL = @SQL + N' WHERE  CaseNo =''' + @CaseNo + ''' and EmpID =''' + @LoginID + ''' and  FlowStepID not in (''B10'',''B20'')  '
     SET @SQL = @SQL + N' ORDER BY LogCrDateTime DESC '
     --EXEC (@SQL)
     EXEC SP_EXECUTESQL @SQL ,@ParmDefinition ,@RET = @DATA OUTPUT
     --SELECT @DATA

     SET @ERROR = 0
     SET @RETURN_MESSAGE_TITLE ='Y'    
     SET @RETURN_MESSAGE_RoleName = '查詢權限成功'
     GOTO END_PROG
  END TRY
     BEGIN CATCH
     SET @ERROR = @@ERROR
     SET @RETURN_MESSAGE_TITLE ='N'
     SET @RETURN_MESSAGE_RoleName = '查詢權限失敗'
     SET @ERROR_MESSAGE = ERROR_MESSAGE()
     GOTO END_PROG
     END CATCH

 END
 ElSE
 BEGIN
     GOTO END_UnAuth
 END
END 
-----------------------------------------------------------
END_UnAuth:
SET @ERROR = 0
SET @RETURN_MESSAGE_TITLE = 'N'
SET @RETURN_MESSAGE_RoleName = '非案件處理人員，無法查詢案件內容'
SET @ERROR_MESSAGE = '非案件處理人員，無法查詢案件內容'
print 'usp_GetAuthCRM 無權限'
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(),'','usp_GetAuthCRM',@Prog,@RETURN_MESSAGE_RoleName,'Error',@ProgID,'', '',@ERROR_MESSAGE,@SQL + ',' +  @IsUserExist,@LoginID)
    
     print 'usp_GetAuthCRM 程式發生錯誤：' +  @ERROR_MESSAGE

     RAISERROR(@ERROR_MESSAGE , 18, 11)
 END
 ELSE
 BEGIN   
     INSERT  Sys_Log  ([LogTime],[CaseNo],[JOB],[CallProg],[Action],[Step],[Memo],[ERRCNT],[ERRNO],[ERRMSG],[SQL],[CreEmpID])
     VALUES (GETDATE(),'','usp_GetAuthCRM',@Prog,@RETURN_MESSAGE_RoleName,'End',@ProgID,'', '',@RETURN_MESSAGE_TITLE + ','  +  @DATA + ',' + @RETURN_MESSAGE_RoleName,@SQL + ',' +  @IsUserExist ,@LoginID)
   
  print 'usp_GetAuthCRM 執行完成：'+ @RETURN_MESSAGE_TITLE + ','+ @RETURN_MESSAGE_RoleName
    
  --Y,1,人員授權
  SELECT  @RETURN_MESSAGE_TITLE + ',' +  @DATA + ',' + @RETURN_MESSAGE_RoleName
 END 
END