MOBILE_API/DB/SPs/PROC_SCHEDULAR_PUSH_TXN_DBBL.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[PROC_SCHEDULAR_PUSH_TXN_DBBL]    Script Date: 9/27/2019 1:30:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PROC_SCHEDULAR_PUSH_TXN_DBBL](
	 @flag VARCHAR(100) = NULL
	,@id		VARCHAR(100)= NULL
	,@ControlNo	VARCHAR(100)=NULL
)AS
BEGIN
	DECLARE @dbblpAgent INT = 393864
	
	IF @flag='push-list-Dbbl'
	BEGIN
		SELECT TOP 10
			paymentMode               = CASE WHEN rt.paymentMethod = 'CASH PAYMENT' THEN 'DCP'
											 WHEN rt.paymentMethod = 'BANK DEPOSIT' AND rt.pBank IN(393940,404518) THEN 'DAC' --## DUTCH-BANGLA BANK LTD / ROCKET Wallet
											 ELSE 'OAC'
										END
			,partnerTxnId			  = dbo.FNADecryptString(rt.controlNo)
			,partnerTxnDate			  = FORMAT(rt.approvedDate,'yyyyMMddHHmmss')
			,amountInBDT			  = rt.pAmt
			,originCountryCode		  = cm.countryCode
			,originCurrencyCode		  = rt.collCurr
			,amountInOriginCurrency   = rt.tAmt
			,remName				  = rt.senderName
			,remAddress				  = tsen.address
			,remCity				  = tsen.city
			,remState				  = tsen.city
			,remZipCode				  = tsen.zipCode
			,remContactNo			  = tsen.mobile
			,benName				  = rt.receiverName
			,benAddress				  = trec.address
			,benCity				  = trec.city
			,benState				  = trec.state
			,benZipCode				  = trec.zipCode
			,benContactNo			  = trec.mobile
			,purpose				  = rt.purposeOfRemit
			,benAccountNo			  = CASE WHEN rt.paymentMethod <> 'CASH PAYMENT' THEN rt.accountNo ELSE '' END
			,benBankName			  = rt.pBankName
			,benBranchName			  = CASE WHEN rt.paymentMethod = 'BANK DEPOSIT' AND rt.pBank IN(393940,404518) THEN '' ELSE rt.pBankBranchName END 
			,benBranchRoutingNo       = CASE WHEN rt.paymentMethod = 'BANK DEPOSIT' AND rt.pBank IN(393940,404518) THEN '' ELSE am.agentCode END  --##provided by dbbl api provider
			,rt.tranStatus
		FROM dbo.remitTran(NOLOCK) rt
		INNER JOIN dbo.tranSenders(NOLOCK) tsen ON rt.id = tsen.tranId
		INNER JOIN dbo.tranReceivers(NOLOCK) trec ON rt.id = trec.tranId
		INNER JOIN dbo.countryMaster(NOLOCK) cm ON cm.countryName = rt.sCountry
		LEFT JOIN agentMaster(NOLOCK) am ON am.agentId = rt.pBankBranch
		WHERE rt.approvedBy IS NOT NULL AND rt.payStatus='Unpaid'
		and rt.pCountry='BANGLADESH' AND tsen.city IS NOT NULL
		AND rt.tranStatus = 'payment' AND rt.pAgent = 393864  --need to change the pAgent acc to the set up for the payoutPartner
		--ORDER BY rt.id DESC
		RETURN
	END

	ELSE IF @flag='modification-request'
	BEGIN
		SELECT TOP 1
			paymentMode               = CASE WHEN rt.paymentMethod = 'CASH PAYMENT' THEN 'DCP'
											 WHEN rt.paymentMethod = 'BANK DEPOSIT' AND rt.pBank IN(393940,404518) THEN 'DAC' --## DUTCH-BANGLA BANK LTD / ROCKET Wallet
											 ELSE 'OAC'
										END
			,partnerTxnId			  = dbo.FNADecryptString(rt.controlNo)
			,partnerTxnDate			  = FORMAT(rt.approvedDate,'yyyyMMddHHmmss')			
			,originCountryCode		  = cm.countryCode		
			,remName				  = rt.senderName
			,remAddress				  = tsen.address
			,remCity				  = tsen.city
			,remState				  = tsen.city
			,remZipCode				  = tsen.zipCode
			,remContactNo			  = tsen.mobile
			,benName				  = rt.receiverName
			,benAddress				  = trec.address
			,benCity				  = trec.city
			,benState				  = trec.state
			,benZipCode				  = trec.zipCode
			,benContactNo			  = trec.mobile
			,purpose				  = rt.purposeOfRemit
			,benAccountNo			  = CASE WHEN rt.paymentMethod <> 'CASH PAYMENT' THEN rt.accountNo ELSE '' END
			,benBankName			  = rt.pBankName
			,benBranchName			  = CASE WHEN rt.paymentMethod = 'BANK DEPOSIT' AND rt.pBank IN(393940,404518) THEN '' ELSE rt.pBankBranchName END 
			,benBranchRoutingNo       = CASE WHEN rt.paymentMethod = 'BANK DEPOSIT' AND rt.pBank IN(393940,404518) THEN '' ELSE am.agentCode END  --##provided by dbbl api provider
			,providerName			  = 'DBBL'
			,rt.payStatus
			,GmeControlNo			  = dbo.FNADecryptString(rt.controlNo)
		FROM dbo.remitTran(NOLOCK) rt
		INNER JOIN dbo.tranSenders(NOLOCK) tsen ON rt.id = tsen.tranId
		INNER JOIN dbo.tranReceivers(NOLOCK) trec ON rt.id = trec.tranId
		INNER JOIN dbo.countryMaster(NOLOCK) cm ON cm.countryName = rt.sCountry
		LEFT JOIN agentMaster(NOLOCK) am ON am.agentId = rt.pBankBranch
		WHERE rt.approvedBy IS NOT NULL AND rt.payStatus = 'Post'
		and rt.pCountry = 'BANGLADESH' AND tsen.city IS NOT NULL
		AND rt.tranStatus = 'ModificationRequest' 
		AND rt.pAgent = 393864 AND RT.controlNo = DBO.FNAEncryptString(@ControlNo)
		RETURN
		RETURN
	END

	ELSE IF @flag='sync-list-Dbbl'
	BEGIN
		SELECT RT.id AS TranId,DBO.FNADecryptString( RT.controlNo) AS PartnerTxnId --PartnerTxnId used for transationinquiry call which is controlno generated in our side
		FROM dbo.remitTran AS RT(NOLOCK) 
		WHERE RT.pAgent = @dbblpAgent
		AND RT.tranStatus = 'Payment' and RT.payStatus = 'Post'
		AND Approveddate < DATEADD(DAY,-1,GETDATE())
	END

	ELSE IF @flag='mark-paid-Dbbl'
	BEGIN
		UPDATE remitTran 
			SET payStatus	=	'Paid'
			,tranStatus		=	'Paid' 
			,paidDate		=	getdate()
			,paidDateLocal	=	GETUTCDATE()
			,paidBy			=	'Scheduler'
		WHERE id = @id AND payStatus = 'Post'
		AND tranStatus = 'payment' AND pAgent = @dbblpAgent
		
		SELECT '0' ErrorCode,'Update success' Msg, NULL Id
	END

	ELSE IF @flag='mark-post-Dbbl'
	BEGIN
		IF EXISTS(SELECT TOP 1 'X' FROM remitTran(NOLOCK) WHERE controlNo =	Dbo.FNAEncryptString(@id) AND pAgent = @dbblpAgent AND tranStatus = 'Modification')
		BEGIN 
			UPDATE remitTran SET 
				 tranStatus		=	'Payment'
			WHERE controlNo		=	Dbo.FNAEncryptString(@id) 
			AND pAgent = @dbblpAgent AND tranStatus = 'Modification' AND payStatus = 'Post'
		END
		ELSE
		BEGIN
			UPDATE remitTran SET 
				 payStatus		=	'Post'
				,postedBy		=	'system'
				,postedDate		=	GETDATE()
				,postedDateLocal=	GETUTCDATE()
				,controlNo2		=	Dbo.FNAEncryptString(@ControlNo) 
				,ContNo			=	@ControlNo
			WHERE controlNo		=	dbo.FNAEncryptString(@id)
			AND pAgent = @dbblpAgent
		END
		SELECT '0' ErrorCode,'Update success' Msg, NULL Id
	END

	ELSE IF @flag='mark-cancel-Dbbl'
	BEGIN
		UPDATE dbo.remitTran SET
			tranStatus='Cancel'
		WHERE id = @id AND payStatus = 'Post'
		AND tranStatus = 'payment' AND pAgent = @dbblpAgent 
    END
END




GO