MOBILE_API/DB/SPs/PROC_KFTC_DOMESTIC_TRAN_HISTORY.sql

----------------------------------------------------------------------------------  
-- Procedure ID   : PROC_KFTC_DOMESTIC_TRAN_HISTORY  
-- Procedure Name : Domestic Remittance 로그 테이블  
-- Dev  Date      : 2019.09.17  
-- Developer      : 이다혜  
-- Desc           :   
----------------------------------------------------------------------------------  
  
ALTER   PROC [dbo].[PROC_KFTC_DOMESTIC_TRAN_HISTORY]          
(          
 @flag        VARCHAR(20),    
 @customerId     VARCHAR(20),  
 @fromDate   VARCHAR(50)   = NULL,  
 @toDate   VARCHAR(50)   = NULL,  
 @bankCode  VARCHAR(5) = NULL,  
 @accountNumber VARCHAR(50)   = NULL,  
 @userId   VARCHAR(50)   = NULL,  
 @tranId   VARCHAR(50)   = NULL  
)          
AS  
  
DECLARE @sql    VARCHAR(MAX)  
  
SET  NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
----------------------------------------------------------------------------------  
-- Procedure Start  
  
-- exec [dbo].[PROC_KFTC_DOMESTIC_TRAN_HISTORY] @flag = 'RECENTLIST', @customerId = '40154'  
-- exec [dbo].[PROC_KFTC_DOMESTIC_TRAN_HISTORY] @flag = 'RECENTLIST', @customerId = '40154',@fromDate = '2019-08-25' ,@toDate = '2019-09-25'  
-- exec [dbo].[PROC_KFTC_DOMESTIC_TRAN_HISTORY] @flag = 'domestictranhis', @customerId = 'temp',@userId = 'maxkim@gmeremit.com'  
-- exec [dbo].[PROC_KFTC_DOMESTIC_TRAN_HISTORY] @flag = 'domestictranhis', @customerId = '40154',@fromDate = '2019-08-25' ,@toDate = '2019-09-25'  
-- exec [dbo].[PROC_KFTC_DOMESTIC_TRAN_HISTORY] @flag = 'domestictranhis', @customerId = 'temp',@tranId = '20190919144446711'  
--  EXEC PROC_KFTC_DOMESTIC_TRAN_HISTORY @flag='domestictranhis', @customerId='temp', @tranId='20190919144446711'  
  
----------------------------------------------------------------------------------  
BEGIN   
 IF @flag = 'RECENTLIST' --송금계좌 조회,  
 BEGIN  
  /* 중복해서 계좌가 나오지 않도록 <계좌별 가장최근이체날짜>를 구해서 최근순으로 추출*/  
  SET @sql='SELECT B.CUSTOMERID,B.REQACCOUNTNUM RECIPIENT_ACCOUNT,B.REQACCOUNTHOLDERNAME RECIPIENT_NAME,B.ROWID,B.BANKCODE,B.BANKNAME  
     FROM (  
      SELECT A.CUSTOMERID,A.REQACCOUNTNUM,A.REQACCOUNTHOLDERNAME,A.ROWID,A.BANKCODE,A.BANKNAME,A.TRAN_DATE,rank() over(partition by A.CUSTOMERID order by A.TRAN_DATE desc) as Rank  
      FROM (  
        SELECT HIS.CUSTOMERID,HIS.REQACCOUNTNUM,HIS.REQACCOUNTHOLDERNAME,KFTCBANK.ROWID,KFTCBANK.BANKCODE,KFTCBANK.BANKNAME,MAX(HIS.REQTRANSFERDTIME) TRAN_DATE  
        FROM KFTC_DOMESTIC_HISTORY HIS  
        INNER JOIN ( SELECT  rowId, bankCode, bankName        
            FROM KoreanBankList(nolock)        
            WHERE IsActive=''1'' AND (bankCode < ''040'' OR bankCode >''080'' ) ) KFTCBANK ON HIS.REQBANKCODESTD = KFTCBANK.bankCode  
        WHERE HIS.transferType = ''deposit''  
        AND HIS.CUSTOMERID = ' + @customerId   
   IF ISNULL(@fromDate,'') <> ''  AND ISNULL(@toDate,'') <> ''  
   BEGIN   
    SET @sql=@sql+' AND Convert(varchar(10),reqTransferDtime,121) BETWEEN ''' +@fromDate+ ''' AND ''' +@toDate+ ''''  
   END  
      
    SET @sql=@sql+' GROUP BY HIS.CUSTOMERID,HIS.REQACCOUNTNUM,HIS.REQACCOUNTHOLDERNAME,KFTCBANK.ROWID,KFTCBANK.BANKCODE,KFTCBANK.BANKNAME  
       ) A  
      ) B'  
  
   IF ISNULL(@fromDate,'') = ''  AND ISNULL(@toDate,'') = '' -- 날짜값이 없다면 최근 5건만 추출  
   BEGIN   
     SET @sql=@sql+' WHERE B.Rank <= 5'  
   END  
  
   SET @sql=@sql+' ORDER BY B.Rank'  
   
 -- PRINT(@sql)  
 EXEC(@sql)  
 END  
 ELSE IF @flag = 'RecipientMobileNo' --송금계좌의 전화번호 조회  
 BEGIN  
  DECLARE @reqMobileNo VARCHAR(20)   
  SET @reqMobileNo = (  
       SELECT TOP 1 HIS.reqMobileNo  
       FROM KFTC_DOMESTIC_HISTORY HIS  
       WHERE HIS.transferType = 'deposit'   
       AND HIS.customerId = @customerId  
       AND HIS.reqBankCodeStd = @bankCode  
       AND HIS.reqAccountNum = @accountNumber  
       ORDER BY HIS.reqTransferDtime DESC  
       )  
  
  SELECT '0' errorCode,@customerId customerId,@reqMobileNo Id  
 END  
 ELSE IF @flag='domestictranhis'  
 BEGIN  
  IF @customerId = 'temp'  
   BEGIN  
    SELECT @customerId = customerId  
    FROM dbo.customerMaster(NOLOCK) cm  
    WHERE cm.mobile=@userId OR cm.email=@userId  
   END  
  
  SET @sql=  
  'SELECT_FIRST withdraw.customerId,withdraw.processId,CONVERT(VARCHAR(10),withdraw.reqTransferDtime,121) tran_date  
      ,withdraw.reqBankCodeStd sendBandCode,WKFTCBANK.bankName sendBankName,withdraw.reqAccountNum sendAccountNum  
      ,deposit.reqBankCodeStd recipientBandCode,DKFTCBANK.bankName recipientBankName,deposit.reqAccountNum recipientAccountNum,deposit.reqAccountHolderName recipientName,deposit.reqMobileNo recipientPhone  
      ,convert(varchar,deposit.reqAmt) sendAmount,convert(varchar,withdraw.serviceFee) serviceFee  
      ,case when deposit.resRspCode = ''A0000'' then ''SUCCESS'' else ''FAIL'' end depositStatus  
   FROM  
    (  
     SELECT customerId,processId,reqTransferDtime,reqBankCodeStd,reqAccountNum,resRspCode,reqMobileNo,serviceFee  
     FROM KFTC_DOMESTIC_HISTORY  
     WHERE transferType = ''withdraw''  
     AND resRspCode = ''A0000'''  
    +' AND customerId = '+ @customerId   
    +' ) withdraw  
    INNER JOIN (  
     SELECT customerId,processId,reqBankCodeStd,reqAccountNum,reqAccountHolderName,reqAmt,reqMobileNo,resRspCode  
     FROM KFTC_DOMESTIC_HISTORY  
     WHERE transferType = ''deposit''  
     AND resRspCode = ''A0000'''  
    +' AND customerId = '+ @customerId     
    +' ) deposit ON withdraw.customerId = deposit.customerId AND withdraw.processId = deposit.processId  
    LEFT OUTER  JOIN (   
     SELECT  rowId, bankCode, bankName        
     FROM KoreanBankList(nolock)        
     WHERE IsActive=''1'' AND (bankCode < ''040'' OR bankCode >''080'' ) ) WKFTCBANK ON withdraw.reqBankCodeStd = WKFTCBANK.bankCode  
    LEFT OUTER  JOIN (   
     SELECT  rowId, bankCode, bankName        
     FROM KoreanBankList(nolock)        
     WHERE IsActive=''1'' AND (bankCode < ''040'' OR bankCode >''080'' ) ) DKFTCBANK ON deposit.reqBankCodeStd = DKFTCBANK.bankCode'  
  
  IF ISNULL(@tranId,'') <> '' --@tranId가 있는 경우는 1건만 추출  
  BEGIN  
    SET @sql=REPLACE(REPLACE(@sql,'AND customerId = temp',''),'SELECT_FIRST','SELECT') + ' WHERE withdraw.processId = '''+@tranId+''''  
   END  
  ELSE IF ISNULL(@fromDate,'') <> ''  AND ISNULL(@toDate,'') <> '' -- 날짜값이 있다면 전체추출   
   BEGIN  
    SET @sql=REPLACE(@sql,'SELECT_FIRST','SELECT') + ' AND CONVERT(VARCHAR(10),withdraw.reqTransferDtime,121) BETWEEN '''+@fromDate+''' AND '''+ @toDate +' 23:59:59'''  
   END  
  ELSE --모두 없다면 최근 7건만 추출  
   BEGIN  
    SET @sql = REPLACE(@sql,'SELECT_FIRST','SELECT TOP 7 ')   
   END  
  
   SET @sql=@sql+ ' ORDER BY withdraw.reqTransferDtime DESC'  
  
  PRINT(@sql)  
  EXEC(@sql)  
 END  
END  
----------------------------------------------------------------------------------  
-- Procedure End  
----------------------------------------------------------------------------------  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
SET  NOCOUNT OFF