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.
162 lines
12 KiB
162 lines
12 KiB
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
|