You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
384 lines
36 KiB
384 lines
36 KiB
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
|
|
|