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.
392 lines
22 KiB
392 lines
22 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_TP_TXN_PUSH] Script Date: 2/16/2024 5:04:00 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
--EXEC PROC_TP_TXN_PUSH @FLAG = 'GET-LIST'
|
|
ALTER PROC [dbo].[PROC_TP_TXN_PUSH] (
|
|
@FLAG VARCHAR(30)
|
|
,@TRAN_ID BIGINT = NULL
|
|
,@PROVIDER VARCHAR(30) = NULL
|
|
,@doSyncAll CHAR(1) = NULL
|
|
,@statusDate DATETIME = NULL
|
|
,@message VARCHAR(500) = NULL
|
|
,@status VARCHAR(100) = NULL
|
|
,@user VARCHAR(20) = NULL
|
|
,@pCurrCostRate float =NULL
|
|
,@control2 VARCHAR(30) = NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
-- =============================================
|
|
--Modified on: 2021/08/03 -> status date for Dongav2
|
|
-- remove NEW_PAID_TRACK
|
|
--Change Request #394 ->Remove Instant Pay for during ProcessBank Deposit
|
|
-- Add BOC
|
|
-- Brac
|
|
--EXEC PROC_TP_TXN_PUSH @flag='add-api-comment',@PROVIDER = 'bracBank',@TRANID = '100644605',@ControlNo = '211298008',@message = 'Cancellation Completed',@status = '407'
|
|
--Added new @Flag = 'INSTANT-PAID_V2' for manual pay
|
|
-- =============================================
|
|
BEGIN
|
|
IF ISDATE(@statusDate) = 0
|
|
SET @statusDate = NULL
|
|
DECLARE @CONTROLNO VARCHAR(30)
|
|
,@TRANDATE VARCHAR(30)
|
|
,@customerId VARCHAR(200)
|
|
|
|
IF @FLAG = 'GET-LIST'
|
|
BEGIN
|
|
|
|
SELECT ID
|
|
,CONTROLNO = DBO.DECRYPTDB(rt.CONTROLNO) --+ DATEPART(SS,GETDATE())
|
|
INTO #REMIT_TT
|
|
FROM REMITTRAN(NOLOCK) rt
|
|
LEFT JOIN tranModifyLog (NOLOCK) tml ON tml.tranId = rt.id
|
|
WHERE 1 = 1
|
|
AND payStatus = 'unpaid'
|
|
AND tranStatus = 'Payment'
|
|
AND rt.CREATEDDATE >= '2020-02-15'
|
|
AND PSUPERAGENT IN (
|
|
394397,394449
|
|
)
|
|
AND (tml.status <> 'Resolved' AND tml.fieldName ='STATUS_SYNC')
|
|
|
|
|
|
SELECT ID
|
|
,CONTROLNO = DBO.DECRYPTDB(rt.CONTROLNO) --+ DATEPART(SS,GETDATE())
|
|
INTO #remit_unpaid
|
|
FROM REMITTRAN(NOLOCK) rt
|
|
WHERE 1 = 1
|
|
AND payStatus = 'unpaid'
|
|
AND tranStatus = 'Payment'
|
|
AND rt.CREATEDDATE >= '2020-02-15'
|
|
AND PSUPERAGENT IN (
|
|
394397,394449
|
|
)
|
|
|
|
|
|
DELETE FROM #remit_unpaid WHERE id in(
|
|
select id FROM #REMIT_TT)
|
|
|
|
SELECT rt.ID
|
|
,CONTROLNO = DBO.DECRYPTDB(rt.CONTROLNO) --+ DATEPART(SS,GETDATE())
|
|
,PAYMENTMETHOD
|
|
,PCOUNTRY
|
|
,rt.CREATEDDATE
|
|
,pbankname
|
|
,pbank
|
|
,pAmt
|
|
FROM REMITTRAN(NOLOCK) rt
|
|
INNER JOIN #remit_unpaid u on rt.id = u.id
|
|
ORDER BY rt.ID DESC
|
|
-- update remitTran set tranStatus='Payment', payStatus='unpaid' where controlNo = dbo.FNAEncryptString('211744466')
|
|
END
|
|
ELSE IF @FLAG = 'UPDATE-POST'
|
|
BEGIN
|
|
UPDATE dbo.remitTran
|
|
SET payStatus = 'POST'
|
|
,postedBy = 'Scheduler'
|
|
,postedDate = GETDATE()
|
|
,postedDateLocal = GETDATE()
|
|
,pDateCostRate=@pCurrCostRate
|
|
,controlno2= CASE WHEN @control2 IS NOT NULL THEN dbo.encryptdb(@control2) ELSE controlno2 END
|
|
WHERE id = @TRAN_ID
|
|
END
|
|
ELSE IF @FLAG = 'UPDATE-POST_RELEASE'
|
|
BEGIN
|
|
UPDATE dbo.remitTran
|
|
|
|
SET
|
|
--controlno2= CASE WHEN @control2 IS NOT NULL THEN dbo.encryptdb(@control2) ELSE controlno2 END
|
|
downloadedDate=GETDATE()
|
|
WHERE id = @TRAN_ID
|
|
END
|
|
ELSE IF @FLAG = 'mark-paid'
|
|
BEGIN
|
|
DECLARE @statusDateOld DATETIME = CAST(@statusDate AS DATETIME)
|
|
|
|
--IF @PROVIDER = 'jmenepal'
|
|
-- AND @statusDate IS NOT NULL
|
|
-- SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
IF @PROVIDER = 'gmenepal'
|
|
AND @statusDate IS NOT NULL
|
|
SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
|
|
|
|
ELSE IF @PROVIDER = 'gccremit'
|
|
AND @statusDate IS NOT NULL
|
|
SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
|
|
|
|
UPDATE dbo.remitTran
|
|
SET tranStatus = 'Paid'
|
|
,payStatus = 'Paid'
|
|
,paidBy = 'SchedulerJob'
|
|
,paidDate = GETDATE()
|
|
,paidDateLocal = ISNULL(@statusDate, DATEADD(HOUR, - 9, GETDATE())) -- GMT FORMAT
|
|
WHERE id = @TRAN_ID
|
|
|
|
SELECT @CONTROLNO = DBO.DECRYPTDB(CONTROLNO)
|
|
,@TRANDATE = PAIDDATE
|
|
FROM dbo.remitTran(NOLOCK)
|
|
WHERE id = @TRAN_ID
|
|
|
|
UPDATE tranModifyLog set resolvedBy='Scheduler', resolvedDate=GETDATE(), status='Resolved' WHERE tranId=@TRAN_ID and fieldname='STATUS_SYNC'
|
|
|
|
DECLARE @senderId INT;
|
|
SELECT @senderId=customerId FROM dbo.transenders (NOLOCK)
|
|
WHERE tranId = @TRAN_ID
|
|
|
|
PRINT @senderId
|
|
PRINT @TRAN_ID
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION', @CustomerId = @senderId, @TranId = @TRAN_ID
|
|
|
|
EXEC FASTMONEYPRO_ACCOUNT.DBO.PROC_TRANSACTION_PAID_VOUCHER_ENTRY @controlNo = @CONTROLNO
|
|
,@tranDate = @TRANDATE
|
|
--IF EXISTS(SELECT 1 FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER (NOLOCK) WHERE FIELD1 = @controlNo AND tran_type = 'j' AND FIELD2 = 'Remittance Voucher'
|
|
-- AND ISNULL(ACCT_TYPE_CODE, 'Send') = 'Paid')
|
|
--BEGIN
|
|
-- UPDATE FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER SET TRAN_DATE = @TRANDATE
|
|
-- WHERE FIELD1 = @controlNo
|
|
-- AND tran_type = 'j'
|
|
-- AND FIELD2 = 'Remittance Voucher'
|
|
-- AND ISNULL(ACCT_TYPE_CODE, 'Send') = 'Paid'
|
|
--END
|
|
END
|
|
ELSE IF @FLAG = 'sync-list'
|
|
BEGIN
|
|
IF ISNULL(@doSyncAll, 'N') = 'N'
|
|
BEGIN
|
|
|
|
|
|
IF @PROVIDER = 'gccremit'
|
|
BEGIN
|
|
SELECT TOP 75 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO2)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394449
|
|
ORDER BY ID DESC
|
|
--AND id=dbo.fnaencryptstring('211517849')
|
|
END
|
|
ELSE IF @PROVIDER = 'transfast'
|
|
BEGIN
|
|
SELECT TOP 50 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
--AND PCOUNTRY NOT IN ('NEPAL','VIETNAM')
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394130
|
|
--AND ID NOT IN (SELECT TRAN_ID FROM NEW_PAID_TRACK_NEW)
|
|
--AND id = 100466789
|
|
--AND CONTROLNO = DBO.FNAENCRYPTSTRING('33TF001950961')
|
|
ORDER BY ID DESC
|
|
END
|
|
ELSE IF @PROVIDER = 'gmenepal'
|
|
BEGIN
|
|
SELECT TOP 75 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394397
|
|
ORDER BY ID DESC
|
|
END
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
|
|
IF @PROVIDER = 'transfast'
|
|
BEGIN
|
|
SELECT id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-03-31'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394130
|
|
END
|
|
ELSE IF @PROVIDER = 'gmenepal'
|
|
BEGIN
|
|
SELECT id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394397
|
|
ORDER BY ID DESC
|
|
END
|
|
|
|
|
|
END
|
|
END
|
|
ELSE IF @Flag = 'GET-RELEASE-LIST'
|
|
BEGIN
|
|
SELECT ID
|
|
,CONTROLNO = DBO.DECRYPTDB(CONTROLNO)
|
|
,CONTROLNO2 = DBO.DECRYPTDB(CONTROLNO2)
|
|
,PAYMENTMETHOD
|
|
,PCOUNTRY
|
|
,CREATEDDATE
|
|
,pbankname
|
|
,pbank
|
|
,sSuperAgent = pSuperAgent
|
|
,[partner] = 'gccremit'
|
|
,pagentcomm
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-03-31'
|
|
AND payStatus = 'POST'
|
|
AND tranStatus = 'Payment'
|
|
AND PSUPERAGENT = 394449
|
|
and downloadedDate IS NULL
|
|
ORDER BY ID DESC
|
|
END
|
|
ELSE IF @Flag = 'INSTANT-PAID'
|
|
BEGIN
|
|
--UPDATE dbo.remitTran SET tranStatus = 'Paid',
|
|
-- payStatus = 'Paid',
|
|
-- paidBy = 'Scheduler',
|
|
-- paidDate = GETDATE(),
|
|
-- paidDateLocal = DATEADD(HOUR, -9, GETDATE()) -- GMT FORMAT
|
|
--WHERE id = @TRAN_ID
|
|
--SELECT @CONTROLNO = DBO.DECRYPTDB(CONTROLNO),
|
|
-- @TRANDATE = PAIDDATE
|
|
--FROM dbo.remitTran (NOLOCK)
|
|
--WHERE id = @TRAN_ID
|
|
--EXEC FASTMONEYPRO_ACCOUNT.DBO.PROC_TRANSACTION_PAID_VOUCHER_ENTRY @controlNo = @CONTROLNO, @tranDate = @TRANDATE
|
|
UPDATE dbo.remitTran
|
|
SET payStatus = 'POST'
|
|
,postedBy = 'Scheduler'
|
|
,postedDate = GETDATE()
|
|
,postedDateLocal = GETDATE()
|
|
WHERE id = @TRAN_ID
|
|
END
|
|
--ELSE IF @Flag = 'INSTANT-PAID'
|
|
--BEGIN
|
|
-- --UPDATE dbo.remitTran SET tranStatus = 'Paid',
|
|
-- UPDATE dbo.remitTran
|
|
-- SET payStatus = 'POST'
|
|
-- ,postedBy = 'Scheduler'
|
|
-- ,postedDate = GETDATE()
|
|
-- ,postedDateLocal = GETDATE()
|
|
-- WHERE id = @TRAN_ID
|
|
--END
|
|
ELSE IF @Flag = 'add-api-comment'
|
|
BEGIN
|
|
SELECT @CONTROLNO = (CONTROLNO)
|
|
----,@TRANDATE = PAIDDATE
|
|
FROM dbo.remitTran(NOLOCK)
|
|
WHERE id = @TRAN_ID
|
|
|
|
IF NOT EXISTS (
|
|
SELECT *
|
|
FROM tranModifyLog
|
|
WHERE tranId = @TRAN_ID
|
|
AND fieldName = 'STATUS_SYNC'
|
|
AND ISNULL(oldValue,'') = @status
|
|
)
|
|
BEGIN
|
|
INSERT INTO tranModifyLog (
|
|
tranId
|
|
,controlNo
|
|
,message
|
|
,createdBy
|
|
,createdDate
|
|
,MsgType
|
|
,STATUS
|
|
,needToSync
|
|
,fieldName
|
|
,oldValue
|
|
)
|
|
SELECT @TRAN_ID
|
|
,@CONTROLNO
|
|
,@message
|
|
,ISNULL(@user,'syncApi')
|
|
,GETDATE()
|
|
,'C'
|
|
,'Not Resolved'
|
|
,1
|
|
,'STATUS_SYNC'
|
|
,@status
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE tranModifyLog set message= @message, createdDate= GETDATE() WHERE tranId = @TRAN_ID AND fieldName = 'STATUS_SYNC'
|
|
AND ISNULL(oldValue,'') = @status
|
|
END
|
|
SELECT '0' ErrorCode, 'Comment Added' Msg, 0 Id
|
|
END
|
|
ELSE IF @Flag = 'INSTANT-PAID_V2'
|
|
BEGIN
|
|
IF EXISTS ( SELECT 'X' FROM remitTran where id = @TRAN_ID and tranStatus in ('Cancel','Paid'))
|
|
BEGIN
|
|
SELECT '0' ErrorCode, 'Invalid Transaction Status!' Msg, 0 Id
|
|
RETURN
|
|
END
|
|
SELECT @customerId = customerId FROM tranSenders WHERE tranId = @TRAN_ID
|
|
|
|
UPDATE dbo.remitTran SET tranStatus = 'Paid',
|
|
payStatus = 'Paid',
|
|
paidBy = 'Manual',
|
|
paidDate =GETDATE(),
|
|
paidDateLocal = DATEADD(HOUR, 1, GETDATE()) -- GMT FORMAT
|
|
WHERE id = @TRAN_ID and (tranStatus <> 'CANCEL' OR payStatus <> 'PAID')
|
|
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION', @CustomerId = @customerId, @TranId = @TRAN_ID
|
|
|
|
SELECT '0' ErrorCode, 'Transaction paid successfully' Msg, 0 Id
|
|
END
|
|
END
|
|
--CREATE TABLE NEW_PAID_TRACK
|
|
--(
|
|
-- TRAN_ID BIGINT
|
|
-- ,PARTNER_NAME VARCHAR(50)
|
|
-- ,PAIDDATE DATETIME
|
|
-- ,PAIDDATE_LOCAL DATETIME
|
|
-- ,ORIGINAL_DATE DATETIME
|
|
--)
|
|
|