DB_Script/mobile_proc_TranHistory.sql

USE [FastMoneyPro_Remit]
GO

/****** Object:  StoredProcedure [dbo].[mobile_proc_TranHistory]    Script Date: 11/8/2023 8:03:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--Exec [mobile_proc_TranHistory] @flag='tran-history-v2', @userId='kamal@imelondon.co.uk'
/****** Object:  StoredProcedure [dbo].[mobile_proc_paidTranHistory]    Script Date: 9/6/2018 11:41:36 AM ******/
ALTER PROCEDURE [dbo].[mobile_proc_TranHistory] (@flag VARCHAR(50) = NULL
, @customerId INT = NULL
, @userId VARCHAR(100) = NULL
, @fromDate VARCHAR(50) = NULL
, @toDate VARCHAR(50) = NULL
, @pageSize VARCHAR(50) = 25 --NULL -- add default 5 
, @pageNumber VARCHAR(50) = NULL
, @sortBy VARCHAR(50) = NULL
, @sortOrder VARCHAR(50) = NULL)
AS
	--#101 mobile changes
	-- #710 - Change Paystatus to Transtatus in Transaction Report
	-- 713 Show Pending Txn in Transaction Report with Status *PENDING*
	-- Direct cancellation for PENDING Status
	-- #808 changes for reject transaction
	-- #10903 - tran report not showing 
	-- #12605 - pagination for transaction history
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRY
		DECLARE @email VARCHAR(100)
			   ,@mobile VARCHAR(25)
				--,@customerId BIGINT
			   ,@sql VARCHAR(MAX)
			   ,@table VARCHAR(MAX)
			   ,@select_field_list VARCHAR(MAX) = ''
			   ,@extra_field_list VARCHAR(MAX) = ''
			   ,@sql_filter VARCHAR(MAX) = ''

		BEGIN
			IF @flag = 'tran-history'
			BEGIN
				IF EXISTS (
						SELECT TOP 1 1
						FROM customerMaster(NOLOCK)
						WHERE customerId = @customerId
						)
				BEGIN
					SELECT @email = cm.email
						,@mobile = cm.mobile
					FROM dbo.customerMaster(NOLOCK) cm
					WHERE cm.customerId  = @userId
				END
				ELSE
				BEGIN
					SELECT @email = cm.email
						,@mobile = cm.mobile
						,@customerId = cm.customerId
					FROM dbo.customerMaster(NOLOCK) cm
					WHERE cm.username = @userId
						OR cm.email = @userId
				END

				IF @fromDate IS NULL
					AND @toDate IS NULL
					SET @sql = 'SELECT TOP 7 * FROM (';
				ELSE
					SET @sql = 'SELECT  * FROM (';
		--	END

			SET @sql = @sql + 
				'SELECT 
				 errorCode      = ''0''
				,ReceiverName=ReceiverName
				,userId         = ReceiverName
				,tranId         = rt.id
				,controlNo      = dbo.FNADecryptString(rt.controlNo)
				,collAmount     = Cast(rt.cAmt as decimal) 
				,TranAmount     = Cast(rt.tAmt as decimal) 
				,collCurr       = rt.collCurr
				,payoutAmt      = rt.pAmt 
				,pCurr          = rt.payoutCurr
				,payStatus      = CASE WHEN rt.tranStatus=''Cancel'' then ''CANCELLED'' 
				                    WHEN rt.tranStatus=''Payment'' AND rt.payStatus=''Unpaid'' AND rt.paymentMethod=''Bank Deposit'' THEN ''PENDING''
				                    WHEN rt.tranStatus=''Payment'' AND rt.payStatus=''Unpaid'' AND rt.paymentMethod=''Cash Payment'' THEN ''READY TO COLLECT''
									WHEN rt.tranStatus=''Payment'' AND rt.payStatus=''Post'' AND rt.paymentMethod=''Cash Payment'' THEN ''READY TO COLLECT''
									WHEN rt.tranStatus=''Payment'' AND rt.payStatus=''Post'' AND rt.paymentMethod=''Bank Deposit'' THEN ''PROCESSING'' 								
									 else UPPER(rt.tranStatus) end
				,payoutMode     = rt.paymentMethod
				,sendDate       = CONVERT(varchar(10), rt.createdDate, 120)
				,paidDate       = CONVERT(varchar(10), rt.paidDate, 120)
				,PayoutAgent	= rt.pBankName
				,PayoutCountry	= rt.pcountry
				
				,DisplayActions =  CASE WHEN rt.tranStatus=''Payment'' THEN ''CANCEL''+'',''+''AMEND'' ELSE '''' END
			FROM dbo.remitTran(NOLOCK) rt
			INNER JOIN dbo.tranSenders s(NOLOCK) on s.tranid = rt.id
			WHERE s.customerId=''' 
				+ CAST((ISNULL(@customerId,@userId)) AS VARCHAR) + ''''

			IF ISNULL(@fromDate, '') <> ''
				AND ISNULL(@toDate, '') <> ''
			BEGIN
				SET @sql = @sql + ' AND rt.createdDate BETWEEN ''' + @fromDate + ''' AND ''' + @toDate + ' 23:59:59'''
			END
				SET @sql = @sql + ')x ORDER BY x.sendDate desc'

			PRINT @userId
			PRINT(@sql)
			EXEC (@sql)
		END
		ELSE IF @flag = 'tran-history-v2'
			BEGIN
				IF EXISTS (SELECT TOP 1
							1
						FROM customerMaster(NOLOCK)
						WHERE customerId = @customerId)
				BEGIN
					SELECT
						@email = cm.email
					   ,@mobile = cm.mobile
					FROM dbo.customerMaster(NOLOCK) cm
					WHERE cm.customerId = @userId
				END
				ELSE
				BEGIN
					SELECT
						@email = cm.email
					   ,@mobile = cm.mobile
					   ,@customerId = cm.customerId
					FROM dbo.customerMaster(NOLOCK) cm
					WHERE cm.username = @userId
					OR cm.email = @userId
				END

			--IF @fromDate IS NULL
			--	AND @toDate IS NULL
			--	SET @table = 'SELECT TOP 7 * FROM (';
			--ELSE
			--	SET @table = 'SELECT *  FROM (';

		--	END
		
			SET @sortBy = 'createdDate'
			SET @sortOrder = 'DESC'
			SET @table = 
				'( SELECT 
			 errorCode      = ''0''
			,userId         = ReceiverName
			,tranId         = rtt.id
			,controlNo      = ''''
			,collAmount     = Cast(rtt.cAmt as decimal) 
			,collCurr       = rtt.collCurr
			,payoutAmt      = rtt.pAmt 
			,pCurr          = rtt.payoutCurr
			,payStatus      = CASE WHEN rtt.tranStatus=''Reject'' then ''DECLINED'' 
			                       WHEN rtt.tranStatus=''Cancel'' then ''CANCELLED'' 
				                   WHEN depositType =''ONLINE'' AND rtt.verifiedDate IS  NULL THEN ''PENDING''						
								   WHEN depositType =''DEBIT_CARD'' AND rtt.verifiedDate IS NOT NULL THEN ''PROCESSING''	
								else UPPER(payStatus) end
			,payoutMode     = rtt.paymentMethod
			,sendDate       = CONVERT(varchar(10), rtt.createdDate, 120)
			,paidDate       = CONVERT(varchar(10), rtt.paidDate, 120)
			,PayoutAgent	= rtt.pBankName
			,rtt.createdDate
		   ,DisplayActions = CASE WHEN rtt.tranStatus=''Reject'' THEN '''' WHEN rtt.verifiedDate IS  NULL THEN '''' WHEN  rtt.approvedDate IS NOT NULL THEN ''CANCEL''+'',''+''AMEND'' ELSE '''' END
		FROM dbo.remitTranTemp(NOLOCK) rtt
		INNER JOIN dbo.tranSendersTemp ts(NOLOCK) on ts.tranid = rtt.id
		WHERE ts.customerId=''' + CAST(@customerId AS VARCHAR) + ''''
			IF ISNULL(@fromDate, '') <> ''
			AND ISNULL(@toDate, '') <> ''
		BEGIN
			SET @table = @table + ' AND  rtt.createdDate  BETWEEN ''' + @fromDate + ''' AND ''' + @toDate + ' 23:59:59'''
		END
		

		SET @table = @table + ' UNION ALL ';
		SET @table = @table + 'SELECT 
				 errorCode      = ''0''
				,userId         = ReceiverName
				,tranId         = rt.id
				,controlNo      = dbo.FNADecryptString(rt.controlNo)
				,collAmount     = Cast(rt.cAmt as decimal) 
				,collCurr       = rt.collCurr
				,payoutAmt      = rt.pAmt 
				,pCurr          = rt.payoutCurr
				,payStatus      = CASE WHEN rt.tranStatus=''Reject'' then ''DECLINED'' 
				                       WHEN rt.tranStatus=''Cancel'' then ''CANCELLED''   
				                       WHEN rt.tranStatus=''paid'' then ''PAID'' 
				                    WHEN depositType =  ''ONLINE'' AND rt.verifiedDate IS  NULL THEN ''PENDING''	
									WHEN depositType in(''ONLINE'',''DEBIT_CARD'') AND rt.verifiedDate IS NOT NULL THEN ''PROCESSING''
									 else UPPER(payStatus) end
				,payoutMode     = rt.paymentMethod
				,sendDate       = CONVERT(varchar(10), rt.createdDate, 120)
				,paidDate       = CONVERT(varchar(10), rt.paidDate, 120)
				,PayoutAgent	= rt.pBankName
				,rt.createdDate
				,DisplayActions =   CASE  WHEN rt.tranStatus=''Cancel'' THEN ''''
				                          WHEN rt.tranStatus=''paid'' THEN ''''
				                          WHEN  rt.approvedDate IS NOT NULL THEN ''CANCEL''+'',''+''AMEND'' 										
										  ELSE '''' END
			FROM dbo.remitTran(NOLOCK) rt
			INNER JOIN dbo.tranSenders s(NOLOCK) on s.tranid = rt.id
			WHERE s.customerId=''' + CAST(@customerId AS VARCHAR) + ''''
			IF ISNULL(@fromDate, '') <> ''
			AND ISNULL(@toDate, '') <> ''
		BEGIN
			SET @table = @table + ' AND   rt.createdDate BETWEEN ''' + @fromDate + ''' AND ''' + @toDate + ' 23:59:59'''
		END
		
		
			SET @table = @table + ')x'
			SET @sql_filter = ''
			
			SET @select_field_list = 'errorCode,userId,tranId,controlNo,collAmount,collCurr,payoutAmt,pCurr,payStatus,payoutMode,
										sendDate, paidDate, PayoutAgent, DisplayActions'
   
			--PRINT (@table)

			EXEC dbo.proc_paging @table
								,@sql_filter
								,@select_field_list
								,@extra_field_list
								,@sortBy
								,@sortOrder
								,@pageSize
								,@pageNumber 

			RETURN
		END
		END
	END TRY

	BEGIN CATCH
		IF @@trancount > 0
			ROLLBACK TRANSACTION

		DECLARE @errorMessage VARCHAR(MAX)

		SET @errorMessage = ERROR_MESSAGE();

		SELECT
			'1' ErrorCode
		   ,@errorMessage Msg
		   ,NULL ID
	END CATCH
GO