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.
450 lines
29 KiB
450 lines
29 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[PROC_TRANSACTION_REPORT] Script Date: 3/7/2024 6:09:36 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROC [dbo].[PROC_TRANSACTION_REPORT] @flag VARCHAR(10) = NULL
|
|
,@user VARCHAR(30) = NULL
|
|
,@pCountry VARCHAR(100) = NULL
|
|
,@pAgent VARCHAR(40) = NULL
|
|
,@pBranch VARCHAR(40) = NULL
|
|
,@sBranch VARCHAR(40) = NULL
|
|
,@depositType VARCHAR(40) = NULL
|
|
,@searchBy VARCHAR(40) = NULL
|
|
,@searchByValue VARCHAR(40) = NULL
|
|
,@orderBy VARCHAR(40) = NULL
|
|
,@status VARCHAR(40) = NULL
|
|
,@paymentType VARCHAR(40) = NULL
|
|
,@dateField VARCHAR(50) = NULL
|
|
,@dateFrom VARCHAR(20) = NULL
|
|
,@dateTo VARCHAR(20) = NULL
|
|
,@transType VARCHAR(40) = NULL
|
|
,@displayTranNo CHAR(1) = NULL
|
|
,@pageNumber INT = NULL
|
|
,@pageSize INT = NULL
|
|
,@rptType CHAR(1) = NULL
|
|
,@transactionFrom CHAR(1) = NULL
|
|
,@postCode VARCHAR(200) = NULL
|
|
,@promoCode VARCHAR(100) = NULL
|
|
AS
|
|
--------------------------------------
|
|
-- #509 - Add transactionType column
|
|
-- #19331 - Change for transaction report
|
|
-- selected tranType in @rptType = 's'
|
|
-- #25999 added postcode filter in @rptType = 's'
|
|
--------------------------------------
|
|
SET NOCOUNT ON;
|
|
SET CONCAT_NULL_YIELDS_NULL OFF;
|
|
|
|
BEGIN
|
|
DECLARE @FilterList TABLE (
|
|
head VARCHAR(50)
|
|
,value VARCHAR(5000)
|
|
)
|
|
DECLARE @SQL VARCHAR(MAX)
|
|
,@usertype VARCHAR(5)
|
|
,@depositTypeText VARCHAR(50)
|
|
,@pAgentFilter VARCHAR(100)
|
|
,@depositTypeFilter VARCHAR(100)
|
|
|
|
IF @sBranch IS NOT NULL
|
|
INSERT INTO @FilterList
|
|
SELECT 'Branch Name'
|
|
,agentName
|
|
FROM agentMaster WITH (NOLOCK)
|
|
WHERE agentId = @sBranch
|
|
|
|
IF @searchByValue IS NOT NULL
|
|
AND @searchBy IS NOT NULL
|
|
BEGIN
|
|
INSERT INTO @FilterList
|
|
SELECT CASE @searchBy
|
|
WHEN 'cid'
|
|
THEN 'Customer ID'
|
|
WHEN 'sName'
|
|
THEN 'Sender Name'
|
|
WHEN 'rName'
|
|
THEN 'Receiver Name'
|
|
WHEN 'icn'
|
|
THEN 'Control No'
|
|
END
|
|
,@searchByValue
|
|
END
|
|
|
|
SET @paymentType = CASE
|
|
WHEN @paymentType = '1'
|
|
THEN 'CASH PAYMENT'
|
|
WHEN @paymentType = '2'
|
|
THEN 'BANK DEPOSIT'
|
|
WHEN @paymentType IS NULL
|
|
THEN ''
|
|
ELSE @paymentType
|
|
END
|
|
|
|
--ISNULL(@paymentType,'')
|
|
--SELECT @depositTypeText = detailTitle FROM staticDataValue WHERE typeID = '8109' and detailDesc = @depositType
|
|
--SET @depositType = @depositTypeText
|
|
SELECT @usertype = usertype
|
|
FROM applicationusers
|
|
WHERE username = @user
|
|
|
|
IF @rptType = 's'
|
|
BEGIN
|
|
DECLARE @sql1 VARCHAR(max)
|
|
,@sql2 VARCHAR(max)
|
|
DECLARE @TotalCount INT
|
|
|
|
SET @sql1 = '
|
|
SELECT
|
|
[IME_NO] = ''"''+ CASE WHEN ''' + ISNULL(@usertype, '') +
|
|
'''=''A'' THEN ''<a href="../SearchTxnReport/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>''
|
|
ELSE ''<a href="/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>'' END
|
|
,[Serial No] = RT.id
|
|
,[TRUST_PAYMENT_ID] = RT.transactionreference
|
|
,[TXN_CHANNEL] = CASE WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''M'') THEN ''Mobile''
|
|
WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''O'') THEN ''Web Online''
|
|
WHEN RT.tranType = ''I'' THEN ''CR Panel''
|
|
ELSE RT.tranType END
|
|
,[DATE_SEND] =CONVERT(VARCHAR,RT.createdDate,111)
|
|
,[DATE_PAID] = CONVERT(VARCHAR,RT.paidDate,111)
|
|
--,[MEMBERSHIPID] = ISNULL(TS.membershipId, CM.MEMBERSHIPID)
|
|
,[MEMBERSHIPID] = ''<a href="/Remit/Administration/CustomerSetup/CustomerDetails.aspx?customerId=''+ CAST(CM.CUSTOMERID AS VARCHAR) + ''">'' + ISNULL(TS.membershipId, CM.MEMBERSHIPID) +''</a>''
|
|
-- ''</a>''
|
|
,[PAYOUT_PARTNER] = RT.pAgentName
|
|
,[SENDER_NAME] = senderName
|
|
,[SENDER_MOBILE] = ''"''+ TS.MOBILE
|
|
,[RECEIVER_NAME]= receiverName
|
|
,[POST_CODE] = TS.zipCode
|
|
,[EX_RATE] = Cast(Round(customerRate,2,1) as decimal(18,2))
|
|
,[PAYMENT_TYPE] = paymentMethod
|
|
,[DEPOSIT_TYPE] = RT.depositType
|
|
,[COLL_AMT] = Cast(Round(cAmt,2,1) as decimal(18,2))
|
|
,[COLL_CURR] = collCurr
|
|
,[REWARD_P] = RT.rewardPoints
|
|
,[RATE_MARGIN] = RT.customerPremium
|
|
,[TRANSFER_AMT] = Cast(Round(tAmt,2,1) as decimal(18,2))
|
|
,[PAYOUT_CURR] = payoutCurr
|
|
,[SEND_CURR] = collCurr
|
|
,[SC_FEE] = serviceCharge
|
|
,[CHARGE_CURR] = collCurr
|
|
,[RECEIVED_AMT] = Cast(Round(pAmt,2,1) as decimal(18,2))
|
|
,[RECEIVED_CURR]= payoutCurr
|
|
,[APPROVED_BY] = rt.approvedBy
|
|
,TRANSTATUS = transtatus
|
|
,PAYSTATUS = CASE
|
|
WHEN RT.PAYSTATUS = ''Paid'' THEN ''PAID''
|
|
WHEN RT.PAYSTATUS = ''Post'' THEN ''POST''
|
|
WHEN RT.PAYSTATUS = ''Cancel'' THEN ''CANCEL''
|
|
ELSE RT.PAYSTATUS
|
|
END
|
|
,[CREATED_DATE] = rt.createddate
|
|
,RT.sBranch
|
|
|
|
,[USD_AMT] = CASE
|
|
WHEN pDateCostRate IS NOT NULL AND pDateCostRate <> 0 THEN Cast(Round(pAmt / pDateCostRate,2,1) as decimal(18,2))
|
|
ELSE 0
|
|
END
|
|
,[COST_RATE]=pDateCostRate
|
|
,[PROMO_TYPE] = RT.rewardType
|
|
FROM REMITTRANTEMP RT(NOLOCK)
|
|
LEFT JOIN TRANSENDERSTEMP TS (NOLOCK) ON TS.TRANID = RT.ID
|
|
LEFT JOIN CUSTOMERMASTER CM (NOLOCK) ON CM.CUSTOMERID = TS.CUSTOMERID
|
|
WHERE 1=1 AND RT.PAYSTATUS<>''REJECT'''
|
|
SET @sql2 = '
|
|
SELECT
|
|
[IME_NO] = ''"''+ CASE WHEN ''' + ISNULL(@usertype, '') +
|
|
''' = ''A'' THEN ''<a href="../SearchTxnReport/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>''
|
|
ELSE ''<a href="/Remit/Transaction/Reports/SearchTransaction.aspx?commentFlag=N&showBankDetail=N&tranId=''+CAST(RT.ID AS VARCHAR)+''">''+DBO.FNADECRYPTSTRING(CONTROLNO)+''</a>'' END
|
|
,[Serial No] = RT.holdtranid
|
|
,[TRUST_PAYMENT_ID] = RT.transactionreference
|
|
,[TXN_CHANNEL] = CASE WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''M'') THEN ''Mobile''
|
|
WHEN (RT.tranType = ''M'' AND RT.isOnlineTxn = ''O'') THEN ''Web Online''
|
|
WHEN RT.tranType = ''I'' THEN ''CR Panel''
|
|
ELSE RT.tranType END
|
|
,[DATE_SEND] =CONVERT(VARCHAR,RT.approvedDate,111)
|
|
,[DATE_PAID] = CONVERT(VARCHAR,RT.paidDate,111)
|
|
--,[MEMBERSHIPID] = ISNULL(TS.membershipId, CM.MEMBERSHIPID)
|
|
,[MEMBERSHIPID] = ''<a href="/Remit/Administration/CustomerSetup/CustomerDetails.aspx?customerId='' +CAST(CM.CUSTOMERID AS VARCHAR) + ''">'' +ISNULL(TS.membershipId, CM.MEMBERSHIPID) + ''</a>''
|
|
-- +CAST(CM.CUSTOMERID AS VARCHAR) +
|
|
-- ''>''
|
|
-- + ISNULL(TS.membershipId, CM.MEMBERSHIPID) +
|
|
-- ''</a>''
|
|
,[PAYOUT_PARTNER] = RT.pAgentName
|
|
,[SENDER_NAME] = senderName
|
|
,[SENDER_MOBILE] = ''"''+ TS.MOBILE
|
|
,[RECEIVER_NAME]= receiverName
|
|
,[POST_CODE] = TS.zipCode
|
|
,[EX_RATE] = Cast(Round(customerRate,2,1) as decimal(18,2))
|
|
,[PAYMENT_TYPE] = paymentMethod
|
|
,[DEPOSIT_TYPE] = RT.depositType
|
|
,[COLL_AMT] = Cast(Round(cAmt,2,1) as decimal(18,2))
|
|
,[COLL_CURR] = collCurr
|
|
,[REWARD_P] = RT.rewardPoints
|
|
,[RATE_MARGIN] = RT.customerPremium
|
|
,[TRANSFER_AMT] = Cast(Round(tAmt,2,1) as decimal(18,2))
|
|
,[PAYOUT_CURR] = payoutCurr
|
|
,[SEND_CURR] = collCurr
|
|
,[SC_FEE] = serviceCharge
|
|
,[CHARGE_CURR] = collCurr
|
|
,[RECEIVED_AMT] = Cast(Round(pAmt,2,1) as decimal(18,2))
|
|
,[RECEIVED_CURR]= payoutCurr
|
|
,[APPROVED_BY] = rt.approvedBy
|
|
,TRANSTATUS = transtatus
|
|
,PAYSTATUS = CASE
|
|
WHEN RT.PAYSTATUS = ''Paid'' THEN ''PAID''
|
|
WHEN RT.PAYSTATUS = ''Post'' THEN ''POST''
|
|
WHEN RT.PAYSTATUS = ''Cancel'' THEN ''CANCEL''
|
|
ELSE RT.PAYSTATUS
|
|
END
|
|
,[CREATED_DATE] = rt.createddate
|
|
,RT.sBranch
|
|
,[USD_AMT] = CASE
|
|
WHEN pDateCostRate IS NOT NULL AND pDateCostRate <> 0 THEN Cast(Round(pAmt / pDateCostRate,2,1) as decimal(18,2))
|
|
ELSE 0
|
|
END
|
|
,[COST_RATE]=pDateCostRate
|
|
,[PROMO_TYPE] = RT.rewardType
|
|
FROM REMITTRAN RT(NOLOCK)
|
|
LEFT JOIN TRANSENDERS TS (NOLOCK) ON TS.TRANID = RT.ID
|
|
LEFT JOIN CUSTOMERMASTER CM (NOLOCK) ON CM.CUSTOMERID = TS.CUSTOMERID
|
|
where 1=1 '
|
|
|
|
IF isnull(@dateFrom, '') <> ''
|
|
AND isnull(@dateTo, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and rt.createddate BETWEEN ''' + @dateFrom + ''' AND ''' + @dateTo + ' 23:59:59'''
|
|
SET @sql2 = @sql2 + ' and rt.createddate BETWEEN ''' + @dateFrom + ''' AND ''' + @dateTo + ' 23:59:59'''
|
|
END
|
|
|
|
IF isnull(@transactionFrom, '') <> ''
|
|
BEGIN
|
|
IF (@transactionFrom = 'I')
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and tranType = ''' + @transactionFrom + ''''
|
|
SET @sql2 = @sql2 + ' and tranType = ''' + @transactionFrom + ''''
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and isOnlineTxn = ''' + @transactionFrom + ''''
|
|
SET @sql2 = @sql2 + ' and isOnlineTxn = ''' + @transactionFrom + ''''
|
|
END
|
|
END
|
|
|
|
IF isnull(@paymentType, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and paymentMethod = ''' + @paymentType + ''''
|
|
SET @sql2 = @sql2 + ' and paymentMethod =''' + @paymentType + ''''
|
|
END
|
|
|
|
IF isnull(@depositType, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and depositType =''' + @depositType + ''''
|
|
SET @sql2 = @sql2 + ' and depositType = ''' + @depositType + ''''
|
|
END
|
|
|
|
IF isnull(@pCountry, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and pCountry = ''' + @pCountry + ''''
|
|
SET @sql2 = @sql2 + ' and pCountry = ''' + @pCountry + ''''
|
|
END
|
|
|
|
IF isnull(@status, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and payStatus = ''' + @status + ''''
|
|
SET @sql2 = @sql2 + ' and payStatus = ''' + @status + ''''
|
|
END
|
|
|
|
IF isnull(@transType, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and transtatus = ''' + @transType + ''''
|
|
SET @sql2 = @sql2 + ' and transtatus = ''' + @transType + ''''
|
|
END
|
|
|
|
IF isnull(@sBranch, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and sBranch = ''' + @sBranch + ''''
|
|
SET @sql2 = @sql2 + ' and sBranch = ''' + @sBranch + ''''
|
|
END
|
|
|
|
IF isnull(@pAgent, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and pAgent = ''' + @pAgent + ''''
|
|
SET @sql2 = @sql2 + ' and pAgent = ''' + @pAgent + ''''
|
|
END
|
|
|
|
IF ISNULL(@postCode, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' AND LEFT(TS.zipCode, 3) = LEFT(''' + @postCode + ''', 3)'
|
|
SET @sql2 = @sql2 + ' AND LEFT(TS.zipCode, 3) = LEFT(''' + @postCode + ''', 3)'
|
|
END
|
|
|
|
IF isnull(@promoCode, '') <> ''
|
|
BEGIN
|
|
SET @sql1 = @sql1 + ' and rewardType = ''' + @promoCode + ''''
|
|
SET @sql2 = @sql2 + ' and rewardType = ''' + @promoCode + ''''
|
|
END
|
|
|
|
SET @SQL =
|
|
'
|
|
SELECT
|
|
[IME_NO],
|
|
[Serial No],
|
|
[TRUST_PAYMENT_ID],
|
|
[TXN_CHANNEL],
|
|
[DATE_SEND],
|
|
[DATE_PAID],
|
|
[MEMBERSHIPID],
|
|
[PAYOUT_PARTNER],
|
|
[SENDER_NAME],
|
|
[SENDER_MOBILE],
|
|
[RECEIVER_NAME],
|
|
[POST_CODE],
|
|
[PAYMENT_TYPE],
|
|
[DEPOSIT_TYPE],
|
|
[COLL_AMT],
|
|
[COLL_CURR],
|
|
[SC_FEE],
|
|
[REWARD_P],
|
|
[TRANSFER_AMT],
|
|
[PAYOUT_CURR],
|
|
[EX_RATE],
|
|
[RATE_MARGIN],
|
|
[COST_RATE],
|
|
[USD_AMT],
|
|
[RECEIVED_AMT],
|
|
[RECEIVED_CURR],
|
|
[APPROVED_BY],
|
|
[TRANSTATUS],
|
|
[PAYSTATUS],
|
|
[CREATED_DATE],
|
|
[PROMO_TYPE],
|
|
COUNT(*) OVER() AS TotalCount
|
|
FROM (
|
|
SELECT
|
|
[IME_NO],
|
|
[Serial No],
|
|
[TRUST_PAYMENT_ID],
|
|
[TXN_CHANNEL],
|
|
[DATE_SEND],
|
|
[DATE_PAID],
|
|
[MEMBERSHIPID],
|
|
[PAYOUT_PARTNER],
|
|
[SENDER_NAME],
|
|
[SENDER_MOBILE],
|
|
[RECEIVER_NAME],
|
|
[POST_CODE],
|
|
[PAYMENT_TYPE],
|
|
[DEPOSIT_TYPE],
|
|
[COLL_AMT],
|
|
[COLL_CURR],
|
|
[SC_FEE],
|
|
[REWARD_P],
|
|
[TRANSFER_AMT],
|
|
[PAYOUT_CURR],
|
|
[EX_RATE],
|
|
[RATE_MARGIN],
|
|
[COST_RATE],
|
|
[USD_AMT],
|
|
[RECEIVED_AMT],
|
|
[RECEIVED_CURR],
|
|
[APPROVED_BY],
|
|
[TRANSTATUS],
|
|
[PAYSTATUS],
|
|
[CREATED_DATE],
|
|
[PROMO_TYPE]
|
|
FROM (
|
|
'
|
|
+ @sql1 + ' UNION ALL ' + @sql2 + '
|
|
) xyz
|
|
) Result '
|
|
|
|
IF @searchByValue IS NOT NULL
|
|
AND @searchBy IS NOT NULL
|
|
BEGIN
|
|
IF @searchBy = 'sName'
|
|
SET @SQL = @SQL + 'AND SENDER_NAME like ''%' + @searchByValue + '%'''
|
|
|
|
IF @searchBy = 'rName'
|
|
SET @SQL = @SQL + 'AND RECEIVER_NAME like ''%' + @searchByValue + '%'''
|
|
|
|
IF @searchBy = 'cid'
|
|
SET @SQL = @SQL + 'AND CUSTOMER_ID = ''' + @searchByValue + ''''
|
|
|
|
IF @searchBy = 'cAmt'
|
|
SET @SQL = @SQL + 'AND COLL_AMT = ''' + @searchByValue + ''''
|
|
|
|
IF @searchBy = 'icn'
|
|
SET @SQL = @SQL + 'AND CONTROL_NO = ''' + @searchByValue + ''''
|
|
END
|
|
|
|
SET @SQL = @SQL + 'order by Result.[CREATED_DATE] desc'
|
|
|
|
PRINT (@SQL)
|
|
|
|
EXEC (@SQL)
|
|
|
|
EXEC proc_errorHandler '0'
|
|
,'Report has been prepared successfully.'
|
|
,NULL
|
|
|
|
SELECT *
|
|
,NULL
|
|
FROM @FilterList
|
|
|
|
SELECT 'TXN Report' title
|
|
END
|
|
|
|
IF @flag = 'summary'
|
|
BEGIN
|
|
SET @sql = 'SELECT
|
|
pAgent = pAgent
|
|
,pAgentName = pAgentName
|
|
,txnCount = COUNT(*)
|
|
,amt = SUM(ISNULL(pAmt,0))
|
|
FROM REMITTRANTEMP rt WITH(NOLOCK)
|
|
WHERE 1=1'
|
|
SET @sql = @sql + '
|
|
AND rt.paymentMethod = ''Bank Deposit''
|
|
AND rt.tranStatus = ''Payment''
|
|
AND rt.payStatus = ''Unpaid''
|
|
AND rt.tranType = ''I''
|
|
--AND rt.sAgent not IN (''16106'',''16106'') --to prevent remitly agent only
|
|
AND tmp.tranStatus = ''Invalid''
|
|
GROUP BY rt.pAgent, rt.pAgentName'
|
|
|
|
PRINT (@sql)
|
|
|
|
EXEC (@sql)
|
|
|
|
SET @sql = 'SELECT
|
|
pAgent = rt.pBank
|
|
,pAgentName = rt.pBankName
|
|
,txnCount = COUNT(*)
|
|
,amt = SUM(ISNULL(rt.pAmt,0))
|
|
FROM remitTran rt WITH(NOLOCK)
|
|
INNER JOIN agentMaster am on rt.pBank=am.agentId
|
|
INNER JOIN InvalidBankDepositTxn tmp (NOLOCK) ON rt.id = tmp.tranId
|
|
WHERE 1=1
|
|
AND (am.agentGrp =''4301'' OR am.agentGrp IS NULL)
|
|
AND (am.agentType=''2903'' OR am.agentType=''2905'')
|
|
'
|
|
SET @sql = @sql + '
|
|
AND rt.paymentMethod = ''Bank Deposit''
|
|
AND rt.tranStatus = ''Payment''
|
|
AND rt.payStatus = ''Unpaid''
|
|
AND rt.tranType = ''D''
|
|
AND tmp.tranStatus = ''Invalid''
|
|
GROUP BY rt.pBank, rt.pBankName'
|
|
|
|
PRINT (@sql)
|
|
|
|
EXEC (@sql)
|
|
|
|
RETURN
|
|
END
|
|
END
|