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.
330 lines
26 KiB
330 lines
26 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[ws_proc_PayTXNConfirm] Script Date: 9/27/2019 1:30:14 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
CREATE PROC [dbo].[ws_proc_PayTXNConfirm] (
|
|
@ACCESSCODE VARCHAR(50)
|
|
,@USERNAME VARCHAR(50)
|
|
,@PASSWORD VARCHAR(50)
|
|
,@REFNO VARCHAR(20)
|
|
,@AGENT_SESSION_ID VARCHAR(150)
|
|
,@PAY_TOKEN_ID BIGINT = NULL
|
|
,@RECEIVER_ID_TYPE VARCHAR(20) = NULL
|
|
,@RECEIVER_ID_NO VARCHAR(20) = NULL
|
|
,@RECEIVER_ID_ISSUE_PLACE VARCHAR(20) = NULL
|
|
,@RECEIVER_ID_ISSUE_DATE VARCHAR(20) = NULL
|
|
,@RECEIVER_DOB VARCHAR(20) = NULL
|
|
,@REMARKS VARCHAR(20) = ''
|
|
,@RECEIVER_OCCUPATION VARCHAR(20) = NULL
|
|
)
|
|
|
|
AS
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
BEGIN TRY
|
|
DECLARE @apiRequestId BIGINT
|
|
|
|
INSERT INTO apiRequestLogPay(
|
|
ACCESSCODE
|
|
,USERNAME
|
|
,PASSWORD
|
|
,REFNO
|
|
,AGENT_SESSION_ID
|
|
,PAY_TOKEN_ID
|
|
,requestedDate
|
|
)
|
|
SELECT
|
|
@ACCESSCODE
|
|
,@USERNAME
|
|
,@PASSWORD
|
|
,@REFNO
|
|
,@AGENT_SESSION_ID
|
|
,@PAY_TOKEN_ID
|
|
,GETDATE()
|
|
|
|
SET @apiRequestId = SCOPE_IDENTITY()
|
|
|
|
DECLARE @errCode INT, @controlNoEnc VARCHAR(50) = dbo.FNAENcryptString(@REFNO)
|
|
DECLARE @autMsg VARCHAR(500), @errorCode VARCHAR(10), @errorMsg VARCHAR(MAX)
|
|
EXEC ws_proc_checkAuthntication @USERNAME, @PASSWORD, @ACCESSCODE, @errCode OUT, @autMsg OUT
|
|
|
|
IF @errCode = '1'
|
|
BEGIN
|
|
SELECT @errorCode = '1002', @errorMsg = ISNULL(@autMsg,'Authentication Fail')
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
IF EXISTS(SELECT 'X' FROM applicationUsers WITH (NOLOCK) WHERE userName = @USERNAME AND forceChangePwd = 'Y')
|
|
BEGIN
|
|
SELECT @errorCode = '1002', @errorMsg = 'You are required to change your password'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
------------------VALIDATION-------------------------------
|
|
|
|
IF @AGENT_SESSION_ID IS NULL
|
|
BEGIN
|
|
SELECT '1001' CODE,'AGENT SESSION ID Field is Required' MESSAGE
|
|
,@AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
IF @REFNO IS NULL
|
|
BEGIN
|
|
SELECT @errorCode = '1001', @errorMsg = 'REFNO Field is Required'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
IF ISNUMERIC(@REFNO) = 0 AND @REFNO IS NOT NULL
|
|
BEGIN
|
|
SELECT @errorCode = '2003', @errorMsg = 'Technical Error: REFNO must be numeric'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
IF @PAY_TOKEN_ID IS NULL
|
|
BEGIN
|
|
SELECT @errorCode = '1001', @errorMsg = 'PAY TOKEN ID Field is Required'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN;
|
|
END
|
|
IF @RECEIVER_ID_TYPE IS NULL
|
|
BEGIN
|
|
SELECT @errorCode = '1005', @errorMsg = 'RECEIVER ID Field is Required'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
IF @RECEIVER_ID_NO IS NULL
|
|
BEGIN
|
|
SELECT @errorCode = '1005', @errorMsg = 'RECEIVER ID NUMBER Field is Required'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
IF @RECEIVER_DOB IS NOT NULL AND ISDATE(@RECEIVER_DOB) = 0
|
|
BEGIN
|
|
SELECT @errorCode = '1005', @errorMsg = 'RECEIVER DOB Field is not a valid date'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
DECLARE
|
|
@pAgent INT,
|
|
@pAgentName VARCHAR(200),
|
|
@pSuperAgent INT,
|
|
@pSuperAgentName VARCHAR(100),
|
|
@tranId BIGINT,
|
|
@tranStatus VARCHAR(30),
|
|
@tokenId VARCHAR(40),
|
|
@paymentMethod VARCHAR(100),
|
|
@lock_status VARCHAR(50),
|
|
@lock_by VARCHAR(50),
|
|
@status VARCHAR(50),
|
|
@pAgentComm MONEY
|
|
|
|
-- PICK AGENTID ,COUNTRY FROM USER
|
|
|
|
SELECT @pAgent = agentId FROM applicationUsers WITH(NOLOCK)
|
|
WHERE userName = @USERNAME
|
|
AND ISNULL(isDeleted, 'N') = 'N' AND isActive = 'Y'
|
|
|
|
SELECT @pAgentName = sAgentName,@pSuperAgent = sSuperAgent,@pSuperAgentName = sSuperAgentName FROM dbo.FNAGetBranchFullDetails(@pAgent)
|
|
|
|
SELECT
|
|
@tranId = rt.id
|
|
,@tranStatus = rt.tranStatus
|
|
,@tokenId = rt.payTokenId
|
|
,@paymentMethod = rt.paymentMethod
|
|
,@lock_status = rt.lockStatus
|
|
,@lock_by = rt.lockedBy
|
|
,@status = rt.payStatus
|
|
,@pAgentComm = rt.pAgentComm
|
|
FROM RemitTran rt WITH(NOLOCK)
|
|
INNER JOIN TranSenders sen WITH(NOLOCK) ON rt.id = sen.tranId
|
|
INNER JOIN TranReceivers rec WITH(NOLOCK) ON rt.id = rec.tranId
|
|
WHERE controlNo = @controlNoEnc AND pAgent = @pAgent
|
|
AND tranStatus = 'Payment' AND payStatus = 'Unpaid'
|
|
AND paymentMethod = 'Cash Payment' AND lockStatus = 'locked'
|
|
|
|
IF @tranStatus IS NULL
|
|
BEGIN
|
|
SELECT @errorCode = '2003', @errorMsg = 'Transaction does not exist. Please check your GME No(GME NUMBER).'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
IF (@lock_by <> @USERNAME)
|
|
BEGIN
|
|
SELECT @errorCode = '2003', @errorMsg = 'You are not authorized to pay: ' + @REFNO + ''
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
IF (@status = 'Paid')
|
|
BEGIN
|
|
SELECT @errorCode = '2001', @errorMsg = 'Transaction ' + @REFNO + ' is already PAID'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
--IF @paymentMethod <> 'Cash Payment'
|
|
--BEGIN
|
|
-- SELECT @errorCode = '2005', @errorMsg = 'This transaction is not Cash Pay Transaction'
|
|
-- EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
-- SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
-- RETURN;
|
|
--END
|
|
|
|
IF ISNULL(@PAY_TOKEN_ID, 0) <> ISNULL(@tokenId,1)
|
|
BEGIN
|
|
SELECT @errorCode = '1004', @errorMsg = 'Invalid Token ID'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN;
|
|
END
|
|
|
|
|
|
--IF (@pAgent IS NOT NULL AND @expected_payoutagentid <> @pAgent)
|
|
--BEGIN
|
|
-- SELECT @errorCode = '2003', @errorMsg = 'This transaction belongs to : ' + @pAgentName
|
|
-- EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
-- SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
-- RETURN
|
|
--END
|
|
|
|
IF (@tranStatus LIKE '%Hold%')
|
|
BEGIN
|
|
SELECT @errorCode = '2006', @errorMsg = 'The Transaction is not approved. Kindly contact info@gmeremit.com to approve the transction.'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
IF (ISNULL(@tranStatus,'') <> 'Payment')
|
|
BEGIN
|
|
SELECT @errorCode = '2006', @errorMsg = 'This transaction is not currently available for payment. Kindly contact info@gmeremit.com.np for the details.'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
IF (ISNULL(@Status,'') <> 'Unpaid')
|
|
BEGIN
|
|
SELECT @errorCode = '2006', @errorMsg = 'This transaction is not currently available for payment. Kindly contact info@gmeremit.com.np for the details.'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
SELECT @errorCode CODE, @errorMsg MESSAGE, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, PAYOUT_COMM = NULL, REFNO = @REFNO
|
|
RETURN
|
|
END
|
|
|
|
--IF (@lock_status <> 'locked')
|
|
--BEGIN
|
|
-- SELECT '2004' CODE
|
|
-- ,'Transaction ' + @REFNO + ' is not in LOCK Stage' MESSAGE
|
|
-- ,@AGENT_SESSION_ID AGENT_SESSION_ID
|
|
-- ,Confirm_ID = NULL, REFNO = @REFNO
|
|
-- RETURN
|
|
--END
|
|
|
|
DECLARE @Confirm_ID VARCHAR(40)
|
|
SET @Confirm_ID = LEFT(CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10)) + '0000000', 7)
|
|
|
|
BEGIN TRANSACTION
|
|
|
|
UPDATE remitTran SET
|
|
pAgent = @pAgent
|
|
,pAgentName = @pAgentName
|
|
,pSuperAgent = @pSuperAgent
|
|
,pSuperAgentName = @pSuperAgentName
|
|
,lockStatus = 'unlocked'
|
|
,tranStatus = 'Paid'
|
|
,payStatus = 'Paid'
|
|
,paidDate = GETDATE()
|
|
,paidDateLocal = GETDATE()
|
|
,paidBy = @USERNAME
|
|
,payTokenId = @Confirm_ID
|
|
,postedBy = @USERNAME
|
|
,postedDate = GETDATE()
|
|
WHERE controlNo = @controlNoEnc
|
|
|
|
UPDATE tranReceivers SET
|
|
idType = @RECEIVER_ID_TYPE
|
|
,idNumber = @RECEIVER_ID_NO
|
|
,idPlaceOfIssue = @RECEIVER_ID_ISSUE_PLACE
|
|
,issuedDate = @RECEIVER_ID_ISSUE_DATE
|
|
,occupation = @RECEIVER_OCCUPATION
|
|
,dob = @RECEIVER_DOB
|
|
WHERE tranId = @tranId
|
|
|
|
IF @@TRANCOUNT > 0
|
|
COMMIT TRANSACTION
|
|
|
|
|
|
SELECT
|
|
CODE = '0'
|
|
,MESSAGE = 'Transaction Paid Successfully Success'
|
|
,AGENT_SESSION_ID = @AGENT_SESSION_ID
|
|
,Confirm_ID = @Confirm_ID
|
|
,REFNO = @REFNO
|
|
,PAYOUT_COMM = @pAgentComm
|
|
|
|
SELECT @errorCode = '0', @errorMsg = 'Pay Success'
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
---- pay voucher----
|
|
EXEC FastMoneyPro_Account.dbo.proc_remittanceVoucher_Paid @controlNo = @REFNO,@pAgent = @pAgent ,@USER = @USERNAME
|
|
RETURN
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRAN
|
|
|
|
DECLARE @errorLogId BIGINT
|
|
INSERT INTO Logs (errorPage, errorMsg, errorDetails, createdBy, createdDate)
|
|
SELECT 'API SP Error',@errorMsg MESSAGE, 'ws_proc_PayTXNConfirm', @USERNAME, GETDATE()
|
|
SET @errorLogId = SCOPE_IDENTITY()
|
|
|
|
SELECT @errorCode = '9001', @errorMsg = 'Technical Error : ' + ERROR_MESSAGE() + ', Error Log Id : ' + CAST(@errorLogId AS VARCHAR)
|
|
EXEC ws_proc_responseLog @flag = 'p', @requestId = @apiRequestId, @errorCode = @errorCode, @errorMsg = @errorMsg, @remarks = @remarks
|
|
|
|
SELECT @errorCode CODE, 'Technical Error occurred, Error Log Id : ' + CAST(@errorLogId AS VARCHAR) MESSAGE, PAYOUT_COMM = NULL, @AGENT_SESSION_ID AGENT_SESSION_ID, Confirm_ID = NULL, REFNO = @REFNO
|
|
|
|
END CATCH
|
|
|
|
|
|
/*
|
|
|
|
EXEC ws_proc_PayTXNConfirm @ACCESSCODE='GMKOR1037',
|
|
@USERNAME='IMENepal',@PASSWORD='123456',
|
|
@AGENT_SESSION_ID='1234567',
|
|
@REFNO='80147248605',
|
|
@PAY_TOKEN_ID='16829397'
|
|
|
|
|
|
*/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
GO
|