DB_Script/Deployment/28122-SuperReferral/PROC_CASH_STATUS_REPORT_REFERRAL.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[PROC_CASH_STATUS_REPORT_REFERRAL]    Script Date: 4/3/2024 8:51:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  
ALTER PROC [dbo].[PROC_CASH_STATUS_REPORT_REFERRAL]  
(  
 @FLAG     VARCHAR(30)  
 ,@DATE        VARCHAR(25)  = NULL  
 ,@USER     VARCHAR(50)  = NULL  
 ,@AGENT_ID    INT    = NULL  
 ,@FROMDATE    VARCHAR(25)  = NULL  
 ,@TODATE    VARCHAR(25)  = NULL  
 ,@referralAcctNum  BIGINT   = NULL  
)  
AS  
SET NOCOUNT ON;  
SET XACT_ABORT ON;  
BEGIN  
 IF @FLAG = 'cash-rpt'  
 BEGIN  
  CREATE TABLE #TEMP_RPT_DRILL_DOWN(BRANCH_NAME VARCHAR(100), BRANCH_ID INT, OPENING_BALANCE MONEY, IN_AMOUNT MONEY  
           , OUT_AMOUNT MONEY, CLOSING_BALANCE MONEY, ACCT_NAME VARCHAR(100), ACCT_NUM VARCHAR(30)  
           , TOTAL_SENT INT, TOTAL_CANCEL INT)  
  
  --CREATE TABLE #ALL_AGENT(AGENT_ID INT, AGENT_NAME VARCHAR(100), ACCT_TYPE CHAR(1))  
  INSERT INTO #TEMP_RPT_DRILL_DOWN(BRANCH_ID, BRANCH_NAME, ACCT_NAME, ACCT_NUM)  
  SELECT R.BRANCH_ID, R.REFERRAL_NAME, AC.ACCT_NAME, AC.ACCT_NUM   
  FROM REFERRAL_AGENT_WISE R(NOLOCK)  
  INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.AC_MASTER AC(NOLOCK) ON AC.AGENT_ID = R.ROW_ID AND AC.ACCT_RPT_CODE = 'RA'  
  WHERE R.AGENT_ID = 0  
  AND R.BRANCH_ID = ISNULL(@AGENT_ID, R.BRANCH_ID)  
    
  CREATE TABLE #TMP(ACCT_NUM VARCHAR(30), AMOUNT MONEY)  
    
  INSERT INTO #TMP(ACCT_NUM, AMOUNT)  
  SELECT ACCT_NUM = T.ACC_NUM, OPENING_BALANCE = ISNULL(SUM (CASE WHEN part_tran_type='CR'   
           THEN tran_amt*-1 ELSE tran_amt END) ,0)  
  FROM #TEMP_RPT_DRILL_DOWN A(NOLOCK)  
  INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK) ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date < @DATE  
  GROUP BY ACC_NUM  
  
  UPDATE TM SET TM.OPENING_BALANCE = T.AMOUNT  
  FROM #TMP T  
  INNER JOIN #TEMP_RPT_DRILL_DOWN TM ON TM.ACCT_NUM = T.ACCT_NUM  
  
  --select * FROM #TMP
 -- select * FROM #TEMP_RPT_DRILL_DOWN
  --RETURN;

  SELECT CAST(CAST(REF_NUM AS NUMERIC) AS BIGINT) REF_NUM  
  INTO #REF_NUMBER  
  FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK)   
  INNER JOIN #TEMP_RPT_DRILL_DOWN A ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date BETWEEN @DATE AND @DATE + ' 23:59:59'  
  GROUP BY CAST(CAST(REF_NUM AS NUMERIC) AS BIGINT)  
  HAVING COUNT(1) = 1  
  
  SELECT part_tran_type, tran_amt, ACC_NUM, ACCT_TYPE_CODE, field2  
  INTO #TRAN_MASTER  
  FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK)   
  INNER JOIN #REF_NUMBER R ON R.REF_NUM = CAST(CAST(T.REF_NUM AS NUMERIC) AS BIGINT)  
  INNER JOIN #TEMP_RPT_DRILL_DOWN A ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date BETWEEN @DATE AND @DATE + ' 23:59:59'  
    
  SELECT IN_AMOUNT = SUM(CASE WHEN part_tran_type = 'dr' THEN tran_amt ELSE 0 END) ,   
    OUT_AMOUNT = SUM(CASE WHEN part_tran_type = 'cr' THEN tran_amt ELSE 0 END),   
    ACCT_NUM = ACC_NUM,  
    TOTAL_SENT = SUM(CASE WHEN part_tran_type = 'dr' AND field2 = 'Remittance Voucher' THEN 1 ELSE 0 END) ,   
    TOTAL_CANCEL = SUM(CASE WHEN part_tran_type = 'cr' AND field2 = 'Remittance Voucher' THEN 1 ELSE 0 END)  
  INTO #TT_TOTAL  
  FROM #TEMP_RPT_DRILL_DOWN A(NOLOCK)  
  INNER JOIN #TRAN_MASTER T(NOLOCK) ON A.ACCT_NUM = T.ACC_NUM  
  GROUP BY ACC_NUM  
  
  UPDATE R SET R.IN_AMOUNT = T.IN_AMOUNT, R.OUT_AMOUNT = T.OUT_AMOUNT, R.TOTAL_SENT = ISNULL(T.TOTAL_SENT, 0), R.TOTAL_CANCEL = ISNULL(T.TOTAL_CANCEL, 0)  
  FROM #TEMP_RPT_DRILL_DOWN R  
  INNER JOIN #TT_TOTAL T ON T.ACCT_NUM = R.ACCT_NUM  
  
  UPDATE #TEMP_RPT_DRILL_DOWN SET CLOSING_BALANCE = ISNULL(OPENING_BALANCE, 0) + ISNULL(IN_AMOUNT, 0) - ISNULL(OUT_AMOUNT, 0)  
  
 -- DELETE FROM #TEMP_RPT_DRILL_DOWN WHERE ISNULL(CLOSING_BALANCE, 0) = 0 AND ISNULL(OPENING_BALANCE, 0) = 0 AND ISNULL(IN_AMOUNT, 0) = 0 AND ISNULL(OUT_AMOUNT, 0) = 0  
  
   
  SELECT AGENTID, AGENTNAME  
  FROM AGENTMASTER AM(NOLOCK)  
  INNER JOIN (SELECT DISTINCT BRANCH_ID FROM #TEMP_RPT_DRILL_DOWN)X ON X.BRANCH_ID = AM.AGENTID  
    
  SELECT AGENT_NAME, OPENING_BALANCE, IN_AMOUNT, OUT_AMOUNT, CLOSING_BALANCE, BRANCH_ID, ADD_BRANCH  
  FROM (  
   --AccountReport/AccountStatement/StatementDetails.aspx?startDate=2019-07-06&endDate=2019-07-06&acNum=101003966&acName=101003966%20|%20Transit%20Charges%20(Intermediary%20Charge)&curr=&type=a  
   SELECT AGENT_NAME = '<a href=''/AccountReport/AccountStatement/StatementDetails.aspx?endDate='+@DATE+'&type=a&startDate='+@DATE+'&acNum='+ACCT_NUM+'&acName='+BRANCH_NAME+'''>'+BRANCH_NAME+' ('+CAST(ISNULL(TOTAL_SENT, 0) AS VARCHAR)+' - '+CAST(ISNULL(TOTAL_CANCEL, 0) AS VARCHAR)+')</a>',  
       OPENING_BALANCE,   
     IN_AMOUNT,   
     OUT_AMOUNT,   
     CLOSING_BALANCE,   
     BRANCH_ID,  
     RPT_TYPE = 'A',  
     BRANCH_NAME ,  
     ADD_BRANCH = 'Y'  
   FROM #TEMP_RPT_DRILL_DOWN  
  
   UNION ALL  
    
   SELECT AGENT_NAME = REFERRAL_NAME + ' (' + CAST(COUNT(1) AS VARCHAR) +')'  
     , OPENING_BALANCE = 0  
     , IN_AMOUNT = SUM(CAMT)  
     , OUT_AMOUNT = 0  
     , CLOSING_BALANCE = 0  
     , R.BRANCH_ID  
     , RPT_TYPE = 'B'  
     , BRANCH_NAME = 'Z'  
     , ADD_BRANCH = 'N'  
   FROM REFERRAL_AGENT_WISE R(NOLOCK)  
   INNER JOIN remitTran RT(NOLOCK) ON RT.promotionCode = R.REFERRAL_CODE  
   WHERE REFERRAL_TYPE_CODE = 'RB'  
   AND RT.createdDate BETWEEN @DATE AND @DATE + ' 23:59:59'  
   AND RT.tranStatus <> 'CANCEL'  
   AND RT.COLLMODE = 'CASH COLLECT'  
   AND R.BRANCH_ID = ISNULL(@AGENT_ID, R.BRANCH_ID)  
   GROUP BY R.REFERRAL_NAME, R.BRANCH_ID  
  )X ORDER BY RPT_TYPE, BRANCH_NAME ASC   
   --EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL  
   --SELECT  'As Of Date' head, @DATE   
  
  --SELECT  'Cash Status Report - Referral' title  
 END  
  IF @FLAG = 'cash-rpt-new'  
 BEGIN  
  CREATE TABLE #TEMP_RPT_DRILL_DOWN_NEW(BRANCH_NAME VARCHAR(100), BRANCH_ID INT, OPENING_BALANCE MONEY, IN_AMOUNT MONEY  
           , OUT_AMOUNT MONEY, CLOSING_BALANCE MONEY, ACCT_NAME VARCHAR(100), ACCT_NUM VARCHAR(30)  
           , TOTAL_SENT INT, TOTAL_CANCEL INT)  
  
  --CREATE TABLE #ALL_AGENT(AGENT_ID INT, AGENT_NAME VARCHAR(100), ACCT_TYPE CHAR(1))  
  INSERT INTO #TEMP_RPT_DRILL_DOWN_NEW(BRANCH_ID, BRANCH_NAME, ACCT_NAME, ACCT_NUM)  
  SELECT R.BRANCH_ID, R.REFERRAL_NAME, AC.ACCT_NAME, AC.ACCT_NUM   
  FROM REFERRAL_AGENT_WISE R(NOLOCK)  
  INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.AC_MASTER AC(NOLOCK) ON AC.AGENT_ID = R.ROW_ID AND AC.ACCT_RPT_CODE = 'RA'  
  WHERE R.AGENT_ID = 0  
  AND R.BRANCH_ID = ISNULL(@AGENT_ID, R.BRANCH_ID)  
  AND AC.ACCT_NUM  = ISNULL(@referralAcctNum,AC.ACCT_NUM)  
  AND R.IS_ACTIVE = 1
    
  CREATE TABLE #TMP_NEW(ACCT_NUM VARCHAR(30), AMOUNT MONEY)  
    
  INSERT INTO #TMP_NEW(ACCT_NUM, AMOUNT)  
  SELECT ACCT_NUM = T.ACC_NUM, OPENING_BALANCE = ISNULL(SUM (CASE WHEN part_tran_type='CR'   
           THEN tran_amt*-1 ELSE tran_amt END) ,0)  
  FROM #TEMP_RPT_DRILL_DOWN_NEW A(NOLOCK)  
  INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK) ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date < @FROMDATE   
  GROUP BY ACC_NUM  
  
  UPDATE TM SET TM.OPENING_BALANCE = T.AMOUNT  
  FROM #TMP_NEW T  
  INNER JOIN #TEMP_RPT_DRILL_DOWN_NEW TM ON TM.ACCT_NUM = T.ACCT_NUM  
  
  SELECT REF_NUM   
  INTO #REF_NUMBER_NEW  
  FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK)   
  INNER JOIN #TEMP_RPT_DRILL_DOWN_NEW A ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date BETWEEN @FROMDATE AND @TODATE + ' 23:59:59'  
  GROUP BY REF_NUM   
  HAVING COUNT(1) = 1  
  
  SELECT part_tran_type, tran_amt, ACC_NUM, ACCT_TYPE_CODE, field2  
  INTO #TRAN_MASTER_NEW  
  FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK)   
  INNER JOIN #REF_NUMBER_NEW R ON R.REF_NUM = T.REF_NUM  
  INNER JOIN #TEMP_RPT_DRILL_DOWN_NEW A ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date BETWEEN @FROMDATE AND @TODATE + ' 23:59:59'  
    
  SELECT IN_AMOUNT = SUM(CASE WHEN part_tran_type = 'dr' THEN tran_amt ELSE 0 END) ,   
    OUT_AMOUNT = SUM(CASE WHEN part_tran_type = 'cr' THEN tran_amt ELSE 0 END),   
    ACCT_NUM = ACC_NUM,  
    TOTAL_SENT = SUM(CASE WHEN part_tran_type = 'dr' AND field2 = 'Remittance Voucher' THEN 1 ELSE 0 END) ,   
    TOTAL_CANCEL = SUM(CASE WHEN part_tran_type = 'cr' AND field2 = 'Remittance Voucher' THEN 1 ELSE 0 END)  
  INTO #TT_TOTAL_NEW  
  FROM #TEMP_RPT_DRILL_DOWN_NEW A(NOLOCK)  
  INNER JOIN #TRAN_MASTER_NEW T(NOLOCK) ON A.ACCT_NUM = T.ACC_NUM  
  GROUP BY ACC_NUM  
  
  UPDATE R SET R.IN_AMOUNT = T.IN_AMOUNT, R.OUT_AMOUNT = T.OUT_AMOUNT, R.TOTAL_SENT = ISNULL(T.TOTAL_SENT, 0), R.TOTAL_CANCEL = ISNULL(T.TOTAL_CANCEL, 0)  
  FROM #TEMP_RPT_DRILL_DOWN_NEW R  
  INNER JOIN #TT_TOTAL_NEW T ON T.ACCT_NUM = R.ACCT_NUM  
  
  UPDATE #TEMP_RPT_DRILL_DOWN_NEW SET CLOSING_BALANCE = ISNULL(OPENING_BALANCE, 0) + ISNULL(IN_AMOUNT, 0) - ISNULL(OUT_AMOUNT, 0)  
  
  DELETE FROM #TEMP_RPT_DRILL_DOWN_NEW WHERE ISNULL(CLOSING_BALANCE, 0) = 0 AND ISNULL(OPENING_BALANCE, 0) = 0 AND ISNULL(IN_AMOUNT, 0) = 0 AND ISNULL(OUT_AMOUNT, 0) = 0  
  
   
  SELECT AGENTID, AGENTNAME  
  FROM AGENTMASTER AM(NOLOCK)  
  INNER JOIN (SELECT DISTINCT BRANCH_ID FROM #TEMP_RPT_DRILL_DOWN_NEW)X ON X.BRANCH_ID = AM.AGENTID  
    
  SELECT AGENT_NAME, OPENING_BALANCE, IN_AMOUNT, OUT_AMOUNT, CLOSING_BALANCE, BRANCH_ID, ADD_BRANCH  
  FROM (  
   --AccountReport/AccountStatement/StatementDetails.aspx?startDate=2019-07-06&endDate=2019-07-06&acNum=101003966&acName=101003966%20|%20Transit%20Charges%20(Intermediary%20Charge)&curr=&type=a  
   SELECT AGENT_NAME = '<a href=''/AccountReport/AccountStatement/StatementDetails.aspx?endDate='+@TODATE+'&type=a&startDate='+@FROMDATE+'&acNum='+ACCT_NUM+'&acName='+BRANCH_NAME+'''>'+BRANCH_NAME+' ('+CAST(ISNULL(TOTAL_SENT, 0) AS VARCHAR)+' - '+CAST(ISNULL(TOTAL_CANCEL, 0) AS VARCHAR)+')</a>',  
       OPENING_BALANCE,   
     IN_AMOUNT,   
     OUT_AMOUNT,   
     CLOSING_BALANCE,   
     BRANCH_ID,  
     RPT_TYPE = 'A',  
     BRANCH_NAME ,  
     ADD_BRANCH = 'Y'  
   FROM #TEMP_RPT_DRILL_DOWN_NEW  
  
   UNION ALL  
    
   SELECT AGENT_NAME = REFERRAL_NAME + ' (' + CAST(COUNT(1) AS VARCHAR) +')'  
     , OPENING_BALANCE = 0  
     , IN_AMOUNT = SUM(CAMT)  
     , OUT_AMOUNT = 0  
     , CLOSING_BALANCE = 0  
     , R.BRANCH_ID  
     , RPT_TYPE = 'B'  
     , BRANCH_NAME = 'Z'  
     , ADD_BRANCH = 'N'  
   FROM REFERRAL_AGENT_WISE R(NOLOCK)  
   INNER JOIN remitTran RT(NOLOCK) ON RT.promotionCode = R.REFERRAL_CODE  
   WHERE REFERRAL_TYPE_CODE = 'RB'  
   AND RT.createdDate BETWEEN @FROMDATE AND @TODATE + ' 23:59:59'  
   AND RT.tranStatus <> 'CANCEL'  
   AND RT.COLLMODE = 'CASH COLLECT'  
   AND R.BRANCH_ID = ISNULL(@AGENT_ID, R.BRANCH_ID)  
   GROUP BY R.REFERRAL_NAME, R.BRANCH_ID  
  )X ORDER BY RPT_TYPE, BRANCH_NAME ASC   
   --EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL  
   --SELECT  'As Of Date' head, @DATE   
  
  --SELECT  'Cash Status Report - Referral' title  
 END  
  IF @FLAG = 'staff-visit-cash-rpt'  
 BEGIN  
  CREATE TABLE #TEMP_RPT_DRILL_DOWN_NEW1(BRANCH_NAME VARCHAR(100), BRANCH_ID INT, OPENING_BALANCE MONEY, IN_AMOUNT MONEY  
           , OUT_AMOUNT MONEY, CLOSING_BALANCE MONEY, ACCT_NAME VARCHAR(100), ACCT_NUM VARCHAR(30)  
           , TOTAL_SENT INT, TOTAL_CANCEL INT)  
  
  --CREATE TABLE #ALL_AGENT(AGENT_ID INT, AGENT_NAME VARCHAR(100), ACCT_TYPE CHAR(1))  
  INSERT INTO #TEMP_RPT_DRILL_DOWN_NEW1(BRANCH_ID, BRANCH_NAME, ACCT_NAME, ACCT_NUM)  
  SELECT R.BRANCH_ID, R.REFERRAL_NAME, AC.ACCT_NAME, AC.ACCT_NUM   
  FROM REFERRAL_AGENT_WISE R(NOLOCK)  
  INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.AC_MASTER AC(NOLOCK) ON AC.AGENT_ID = R.ROW_ID AND AC.ACCT_RPT_CODE = 'RA'  
  WHERE R.AGENT_ID = 0  
  AND R.BRANCH_ID = ISNULL(@AGENT_ID, R.BRANCH_ID) 
  AND STAFF_VISIT = 1 
   
   
  IF (@DATE <='2022-02-02')
	BEGIN
	INSERT INTO #TEMP_RPT_DRILL_DOWN_NEW1(BRANCH_ID, BRANCH_NAME, ACCT_NAME, ACCT_NUM)  
	  SELECT R.BRANCH_ID, R.REFERRAL_NAME, AC.ACCT_NAME, AC.ACCT_NUM   
	  FROM REFERRAL_AGENT_WISE R(NOLOCK)  
	  INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.AC_MASTER AC(NOLOCK) ON AC.AGENT_ID = R.ROW_ID AND AC.ACCT_RPT_CODE = 'RA'  
	  WHERE	   STAFF_VISIT = 0 and ROW_ID in(25,123,38)
	END

  CREATE TABLE #TMP_NEW1(ACCT_NUM VARCHAR(30), AMOUNT MONEY)  
    
  INSERT INTO #TMP_NEW1(ACCT_NUM, AMOUNT)  
  SELECT ACCT_NUM = T.ACC_NUM, OPENING_BALANCE = ISNULL(SUM (CASE WHEN part_tran_type='CR'   
           THEN tran_amt*-1 ELSE tran_amt END) ,0)  
  FROM #TEMP_RPT_DRILL_DOWN_NEW1 A(NOLOCK)  
  INNER JOIN FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK) ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date < @DATE  
  GROUP BY ACC_NUM  
  
  UPDATE TM SET TM.OPENING_BALANCE = T.AMOUNT  
  FROM #TMP_NEW1 T  
  INNER JOIN #TEMP_RPT_DRILL_DOWN_NEW1 TM ON TM.ACCT_NUM = T.ACCT_NUM  
  
  SELECT CAST(CAST(REF_NUM AS NUMERIC) AS BIGINT) REF_NUM  
  INTO #REF_NUMBER_NEW1 
  FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK)   
  INNER JOIN #TEMP_RPT_DRILL_DOWN_NEW1 A ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date BETWEEN @DATE AND @DATE + ' 23:59:59'  
  GROUP BY CAST(CAST(REF_NUM AS NUMERIC) AS BIGINT)  
  HAVING COUNT(1) = 1  
  
  SELECT part_tran_type, tran_amt, ACC_NUM, ACCT_TYPE_CODE, field2  
  INTO #TRAN_MASTER_NEW1
  FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER T(NOLOCK)   
  INNER JOIN #REF_NUMBER_NEW1 R ON R.REF_NUM = CAST(CAST(T.REF_NUM AS NUMERIC) AS BIGINT)  
  INNER JOIN #TEMP_RPT_DRILL_DOWN_NEW1 A ON A.ACCT_NUM = T.ACC_NUM  
  WHERE T.tran_date BETWEEN @DATE AND @DATE + ' 23:59:59'  
    
  SELECT IN_AMOUNT = SUM(CASE WHEN part_tran_type = 'dr' THEN tran_amt ELSE 0 END) ,   
    OUT_AMOUNT = SUM(CASE WHEN part_tran_type = 'cr' THEN tran_amt ELSE 0 END),   
    ACCT_NUM = ACC_NUM,  
    TOTAL_SENT = SUM(CASE WHEN part_tran_type = 'dr' AND field2 = 'Remittance Voucher' THEN 1 ELSE 0 END) ,   
    TOTAL_CANCEL = SUM(CASE WHEN part_tran_type = 'cr' AND field2 = 'Remittance Voucher' THEN 1 ELSE 0 END)  
  INTO #TT_TOTAL_NEW1 
  FROM #TEMP_RPT_DRILL_DOWN_NEW1 A(NOLOCK)  
  INNER JOIN #TRAN_MASTER_NEW1 T(NOLOCK) ON A.ACCT_NUM = T.ACC_NUM  
  GROUP BY ACC_NUM  
  
  UPDATE R SET R.IN_AMOUNT = T.IN_AMOUNT, R.OUT_AMOUNT = T.OUT_AMOUNT, R.TOTAL_SENT = ISNULL(T.TOTAL_SENT, 0), R.TOTAL_CANCEL = ISNULL(T.TOTAL_CANCEL, 0)  
  FROM #TEMP_RPT_DRILL_DOWN_NEW1 R  
  INNER JOIN #TT_TOTAL_NEW1 T ON T.ACCT_NUM = R.ACCT_NUM  
  
  UPDATE #TEMP_RPT_DRILL_DOWN_NEW1 SET CLOSING_BALANCE = ISNULL(OPENING_BALANCE, 0) + ISNULL(IN_AMOUNT, 0) - ISNULL(OUT_AMOUNT, 0)  
  
  DELETE FROM #TEMP_RPT_DRILL_DOWN_NEW1 WHERE ISNULL(CLOSING_BALANCE, 0) = 0 AND ISNULL(OPENING_BALANCE, 0) = 0 AND ISNULL(IN_AMOUNT, 0) = 0 AND ISNULL(OUT_AMOUNT, 0) = 0  
  
   
  SELECT AGENTID, AGENTNAME  
  FROM AGENTMASTER AM(NOLOCK)  
  INNER JOIN (SELECT DISTINCT BRANCH_ID FROM #TEMP_RPT_DRILL_DOWN_NEW1)X ON X.BRANCH_ID = AM.AGENTID  
 
  SELECT AGENT_NAME, OPENING_BALANCE, IN_AMOUNT, OUT_AMOUNT, CLOSING_BALANCE, BRANCH_ID, ADD_BRANCH  
  FROM (  
   --AccountReport/AccountStatement/StatementDetails.aspx?startDate=2019-07-06&endDate=2019-07-06&acNum=101003966&acName=101003966%20|%20Transit%20Charges%20(Intermediary%20Charge)&curr=&type=a  
   SELECT AGENT_NAME = '<a href=''/AccountReport/AccountStatement/StatementDetails.aspx?endDate='+@DATE+'&type=a&startDate='+@DATE+'&acNum='+ACCT_NUM+'&acName='+BRANCH_NAME+'''>'+BRANCH_NAME+' ('+CAST(ISNULL(TOTAL_SENT, 0) AS VARCHAR)+' - '+CAST(ISNULL(TOTAL_CANCEL, 0) AS VARCHAR)+')</a>',  
       OPENING_BALANCE,   
     IN_AMOUNT,   
     OUT_AMOUNT,   
     CLOSING_BALANCE,   
     BRANCH_ID,  
     RPT_TYPE = 'A',  
     BRANCH_NAME ,  
     ADD_BRANCH = 'Y'  
   FROM #TEMP_RPT_DRILL_DOWN_NEW1  
  
   UNION ALL  
    
   SELECT AGENT_NAME = REFERRAL_NAME + ' (' + CAST(COUNT(1) AS VARCHAR) +')'  
     , OPENING_BALANCE = 0  
     , IN_AMOUNT = SUM(CAMT)  
     , OUT_AMOUNT = 0  
     , CLOSING_BALANCE = 0  
     , R.BRANCH_ID  
     , RPT_TYPE = 'B'  
     , BRANCH_NAME = 'Z'  
     , ADD_BRANCH = 'N'  
   FROM REFERRAL_AGENT_WISE R(NOLOCK)  
   INNER JOIN remitTran RT(NOLOCK) ON RT.promotionCode = R.REFERRAL_CODE  
   WHERE STAFF_VISIT = 1  
   AND RT.createdDate BETWEEN @DATE AND @DATE + ' 23:59:59'  
   AND RT.tranStatus <> 'CANCEL'  
   AND RT.COLLMODE = 'CASH COLLECT'  
   AND R.BRANCH_ID = ISNULL(@AGENT_ID, R.BRANCH_ID)  
   GROUP BY R.REFERRAL_NAME, R.BRANCH_ID  
  )X 
   WHERE x.add_branch = 'Y' 
  ORDER BY RPT_TYPE, BRANCH_NAME ASC  
 
   --EXEC proc_errorHandler '0', 'Report has been prepared successfully.', NULL  
   --SELECT  'As Of Date' head, @DATE   
  
  --SELECT  'Cash Status Report - Referral' title  
 END 
 IF @flag = 'summary'
 BEGIN
	DECLARE @rowId INT
	SELECT @rowId = ROW_ID FROM REFERRAL_AGENT_WISE WHERE BRANCH_ID = @AGENT_ID
	SELECT R.REFERRAL_NAME, ISNULL(RT.SENDERNAME,cm.fullName) SENDERNAME, PARTNER_ID, COMMISSION_PCNT, PAID_COMMISSION, FX_PCNT, PAID_FX, FLAT_RATE,ISNULL(RT.serviceCharge,0) serviceCharge,ISNULL(T.TAX_AMOUNT,0) TAX_AMOUNT
				, CONTROLNO = '<a href="javascript:void(0)" onclick="OpenInNewWindow(''/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId='+CAST(RT.ID AS VARCHAR)+''')">'+dbo.FNADecryptString(controlNo) +'</a>'
				, PAID_FLAT, PAID_NEW_CUSTOMER_RATE, PAID_NEW_CUSTOMER, ISNULL(CONVERT(CHAR(10), T.TXN_DATE,126), CONVERT( CHAR(10), cm.createdDate, 126)) [DATE]
				, ISNULL(RT.CAMT, 0) CAMT
				, T.REFERRAL_TYPE
		INTO #STAFF_VISIT_REPORT_SUMMARY
		FROM REFERRAL_INCENTIVE_TRANSACTION_WISE T(NOLOCK)
		INNER JOIN REFERRAL_AGENT_WISE R(NOLOCK) ON R.REFERRAL_CODE = T.REFERRAL_ID
		LEFT JOIN customerMaster cm(NOLOCK) ON cm.customerId = T.CUSTOMER_ID
		LEFT JOIN REMITTRAN RT(NOLOCK) ON RT.ID = T.TRAN_ID AND T.TXN_DATE BETWEEN @FROMDATE AND @TODATE + ' 23:59:59'
               -- AND RT.COLLMODE = 'CASH COLLECT'
		WHERE 
		 R.ROW_ID = ISNULL(@rowId, R.ROW_ID)
		AND R.IS_ACTIVE = 1
		
		UPDATE #STAFF_VISIT_REPORT_SUMMARY SET PARTNER_ID = 393880 WHERE PARTNER_ID = 394397

		SELECT * FROM #STAFF_VISIT_REPORT_SUMMARY
		ORDER BY [DATE], REFERRAL_NAME 
 END
END