DB_Script/StoredProc/mobile_proc_WalletStatement.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[mobile_proc_WalletStatement]    Script Date: 10/6/2023 1:02:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC [mobile_proc_WalletStatement] @UserID = 'mary@yopmail.com',@startDate='2011-01-01',@endDate='2023-11-01'
ALTER  PROC [dbo].[mobile_proc_WalletStatement]
	@startDate	VARCHAR(10)	= NULL,
	@endDate	VARCHAR(10)	= NULL,
	@UserID		VARCHAR(50)
AS
SET NOCOUNT ON;
--------------------------------------------------------
	-- #101 - Mobile Changes 
--------------------------------------------------------
DECLARE @acnum VARCHAR(20)
SELECT @acnum = walletAccountNo FROM customerMaster(nolock)
WHERE userName = @UserID 
----OR mobile =@UserID  FUTURE USE

DECLARE @SQL VARCHAR(MAX)

IF @endDate IS NULL
	SELECT @endDate = CAST(GETDATE() AS DATE), @startDate= CAST(DATEADD(M,-3,GETDATE()) AS DATE)
DECLARE @RESULT TABLE(errorCode  VARCHAR(10),userId  BIGINT,tranId BIGINT ,
controlNo varchar(20), collAmount decimal (18,2) ,collcur varchar(3),  payoutAmt decimal (18,2),pCurr varchar(3),
payStatus varchar(20)  , payoutMode varchar(20),  sendDate  varchar(10), paidDate  varchar(10),  PayoutAgent  varchar(30), DisplayActions  varchar(30))
 

--INSERT INTO @RESULT
EXEC [mobile_proc_TranHistory] @flag='tran-history', @userId=@UserID,@fromDate=@startDate,@toDate=@endDate

--SELECT * FROM @RESULTa

RETURN;
--INSERT INTO @RESULT
--Exec FastMoneyPro_Account.dbo.spa_branchstatement @flag='S' ,@acnum=@acnum,@startDate=@startDate,@endDate=@endDate,@company_id='1'

--UPDATE @RESULT SET tran_rmks = CASE 
--				WHEN field2='Send Voucher' THEN 'Control No :'+field1 
--				WHEN field2='Fund Deposit' THEN 'Amount Deposited' 
--			ELSE tran_rmks 
--			END  

--SELECT * FROM @RESULT

--SET @SQL = '
--SELECT CONVERT(VARCHAR,TRNDATE,102) AS TRNDATE,TRAN_RMKS,DRTOTAL,CRTOTAL,END_CLR_BALANCE,ref_num
--FROM ( 
--	SELECT 0 SN, '''+@startDate+''' TRNDATE, ''BALANCE BROUGHT FORWARD'' TRAN_RMKS, 0 DRTOTAL,0 CRTOTAL,ISNULL(END_CLR_BALANCE,0) END_CLR_BALANCE,'''' ref_num
--	FROM (
--		SELECT SUM (CASE WHEN PART_TRAN_TYPE=''DR'' THEN TRAN_AMT*-1 ELSE TRAN_AMT END) END_CLR_BALANCE
--		FROM FastMoneyPro_Account.DBO.VW_PostedAccountDetail  WITH (NOLOCK) 
--		WHERE ACC_NUM = '''+@acnum+''' AND TRAN_DATE < '''+@startDate+'''
		
--		GROUP BY ACC_NUM
--	) CA '	 

--IF @endDate IS NOT NULL
--BEGIN
--SET @SQL = @SQL+' UNION ALL 
--	SELECT TOP(1000) 1 SN,TRAN_DATE AS TRNDATE
--	,TRAN_RMKS = CASE 
--					WHEN field2=''Send Voucher'' THEN CASE WHEN acct_type_code IS NULL THEN ''Control No :''+field1 ELSE '' Cancellation of Control No :''+field1 END 
--					WHEN field2=''Customer Deposit'' THEN ''Amount Deposited'' 
--					WHEN field2=''Withdraw Voucher'' THEN ''Wallet Withdraw'' 
--					WHEN field2=''Paid Voucher'' THEN ''Wallet Redeem'' 
--					WHEN field2=''Refund Voucher'' THEN ''Amount Refunded'' 
--					ELSE field2
--				END 
--	,CASE WHEN PART_TRAN_TYPE = ''DR'' THEN TRAN_AMT ELSE 0 END AS DRTOTAL
--	,CASE WHEN PART_TRAN_TYPE = ''CR'' THEN TRAN_AMT ELSE 0 END AS CRTOTAL
--	,0 BALANCE,ref_num
--	FROM FastMoneyPro_Account.DBO.VW_PostedAccountDetail T WITH (NOLOCK)
--	WHERE ACC_NUM = '''+@acnum +''' 
--	AND T.COMPANY_ID=1  
--	AND TRAN_DATE BETWEEN '''+@startDate+''' AND '''+@endDate+' 23:59:59''
--	ORDER BY CREATED_DATE
--) 
--A ORDER BY TRNDATE'

--END
--ELSE 
--BEGIN
--SET @SQL = @SQL+' UNION ALL 
--	SELECT TOP(7) 1 SN,TRAN_DATE AS TRNDATE
--	,TRAN_RMKS = CASE 
--					WHEN field2=''Send Voucher'' THEN CASE WHEN acct_type_code IS NULL THEN ''Control No :''+field1 ELSE '' Cancellation of Control No :''+field1 END 
--					WHEN field2=''Customer Deposit'' THEN ''Amount Deposited''  
--					WHEN field2=''Withdraw Voucher'' THEN ''Wallet Withdraw'' 
--					WHEN field2=''Paid Voucher'' THEN ''Wallet Redeem'' 
--					WHEN field2=''Refund Voucher'' THEN ''Amount Refunded'' 
--					ELSE field2
--				END 
--	,CASE WHEN PART_TRAN_TYPE = ''DR'' THEN TRAN_AMT ELSE 0 END AS DRTOTAL
--	,CASE WHEN PART_TRAN_TYPE = ''CR'' THEN TRAN_AMT ELSE 0 END AS CRTOTAL
--	,0 BALANCE,ref_num
--	FROM FastMoneyPro_Account.DBO.VW_PostedAccountDetail T WITH (NOLOCK)
--	WHERE ACC_NUM = '''+@acnum +'''
--	AND T.COMPANY_ID=1 
--	AND TRAN_DATE BETWEEN '''+@startDate+''' AND '''+@endDate+' 23:59:59''
--	ORDER BY CREATED_DATE
--) 
--A ORDER BY TRNDATE desc'

--END

--PRINT @SQL
--INSERT INTO @RESULT
--EXEC(@SQL)


--SELECT * FROM @RESULT