DB_Script/StoredProc/PROC_TRANSACTION_REPORT.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[PROC_TRANSACTION_REPORT]    Script Date: 6/3/2024 5:22:03 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
	,@sCountry 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
	,@remitTYpe VARCHAR(30) = NULL
AS
--------------------------------------
-- #509 - Add transactionType column
-- #19331 - Change for transaction report
-- selected tranType in @rptType = 's'
-- #25999 added postcode filter in @rptType = 's'
-- selected receiving country column in txn report
--------------------------------------
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 @dateFrom = CONVERT(DATE, @dateFrom, 105)
		--SET @dateTo = CONVERT(DATE, @dateTo, 105)
		 
		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
			,[Partner Id] = DBO.FNADECRYPTSTRING(CONTROLNO2)
			,[Serial No]  = RT.id
			,[TRUST_PAYMENT_ID]	= RT.transactionreference
			,[Sending Country] = RT.sCountry
			,[Sending Agent] = RT.sSuperAgentName
			,[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
			   ,[RECEIVING_COUNTRY] = RT.pCountry
	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
			,[Partner Id] = DBO.FNADECRYPTSTRING(CONTROLNO2)
			,[Serial No]  = RT.holdtranid
			,[TRUST_PAYMENT_ID]	= RT.transactionreference
			,[Sending Country] = RT.sCountry
			,[Sending Agent] = RT.sSuperAgentName
			,[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
			   ,[RECEIVING_COUNTRY] = RT.pCountry
	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(@sCountry,'') <> ''
		BEGIN
			SET @sql1 = @sql1 + ' and sCountry = ''' + @sCountry + ''''
			SET @sql2 = @sql2 + ' and sCountry = ''' + @sCountry + ''''
		END
			IF isnull(@remitTYpe,'') <> ''
		BEGIN
			SET @sql1 = @sql1 + ' and srouteid = ''' + @remitTYpe + ''''
			SET @sql2 = @sql2 + ' and srouteid = ''' + @remitTYpe + ''''
		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],
		[Partner Id],
        [Serial No],
        [TRUST_PAYMENT_ID],
		[Sending Country],
		[Sending Agent],
        [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],
		[RECEIVING_COUNTRY],
        COUNT(*) OVER() AS TotalCount
    FROM (
        SELECT 
            [IME_NO],
			[Partner Id],
            [Serial No],
            [TRUST_PAYMENT_ID],
			[Sending Country],
		    [Sending Agent],
            [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],
			[RECEIVING_COUNTRY]
        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