MOBILE_API/DB/SPs/TXN_CALLBACK_STATUS_SYNC.sql

USE [FastMoneyPro_Remit]
GO

--EXEC TXN_CALLBACK_STATUS_SYNC @ControlNo = '80192524052',@RefundCharge='D',@PayoutStatus='Cancel'

ALTER PROCEDURE TXN_CALLBACK_STATUS_SYNC(
   @Flag			VARCHAR(20)	= '',
   @PartnerPin		VARCHAR(30) = NULL,
   @ControlNo		VARCHAR(30) = NULL,
   @TranId			VARCHAR(30) = NULL,
   @PartnerId		VARCHAR(30) = NULL,
   @PayoutStatus	VARCHAR(30) = NULL,
   @Provider		VARCHAR(30) = NULL,
   @RefundCharge	VARCHAR(10) = NULL
   	
)
AS
SET NOCOUNT ON;
BEGIN
	----@RefundCharge CASE WHEN 'N' THEN REFUND CHARGE WHEN 'D' THEN DIDUCT(NO NEED TO REFUND) CHARGE
	IF @RefundCharge IS NULL
		SET @RefundCharge='D'
	DECLARE @tranStatus VARCHAR(30), @payStatus VARCHAR(30), @ctrlNo VARCHAR(30),@tranType varchar(10)

	DECLARE @TEMPTBL TABLE (ERRORCODE VARCHAR(5), MSG VARCHAR(MAX), ID VARCHAR(50))

	SELECT @PartnerPin = dbo.FNAEncryptString(@PartnerPin),@ControlNo = dbo.FNAEncryptString(@ControlNo)

	IF @TranId IS NULL
	BEGIN
		SELECT @TranId = id, @ctrlNo = controlNo, @tranStatus = tranStatus, @payStatus = payStatus ,@tranType = sRouteId
		FROM dbo.remitTran(nolock) 
		WHERE controlNo = @PartnerPin AND payStatus = 'Post'
	END
	IF @TranId IS NULL
	BEGIN
		SELECT @TranId = id, @ctrlNo = controlNo, @tranStatus = tranStatus, @payStatus = payStatus ,@tranType = sRouteId 
		FROM dbo.remitTran(nolock) 
		WHERE controlNo2 = @ControlNo AND payStatus = 'Post'
	END
	IF @TranId IS NULL
	BEGIN
		SELECT @TranId = id, @ctrlNo = controlNo, @tranStatus = tranStatus, @payStatus = payStatus ,@tranType = sRouteId 
		FROM dbo.remitTran(nolock) 
		WHERE ContNo = @PartnerId AND payStatus = 'Post'
	END

	SET @ctrlNo = dbo.FNADecryptString(@ctrlNo)
	
	IF @TranId IS NULL
	BEGIN
	   SELECT '1' ,'Transaction Not found', NULL
	   RETURN
	END

	IF @payStatus = 'PAID'
	BEGIN
	   SELECT '1' ,'Transaction already paid', NULL
	   RETURN
	END

	IF @payStatus = 'CANCEL'
	BEGIN
	   SELECT '1' ,'Transaction already canceled', NULL
	   RETURN
	END

	--IF @PayoutStatus = 'POST' AND @payStatus = 'Unpaid'
	--BEGIN
	--	UPDATE remitTran SET payStatus = 'Post', tranStatus = 'Payment', postedBy = 'system', postedDate = GETDATE(), postedDateLocal = GETUTCDATE()
	--	WHERE id = @TranId 
	--	EXEC proc_errorHandler 0, 'Transaction status synced as post successfully', NULL
	--END

	IF @PayoutStatus = 'Cancel' AND @payStatus = 'Post' AND @tranStatus = 'Payment'
	BEGIN
		IF NOT EXISTS(SELECT TOP 1 'A' FROM FastMoneyPro_Account.DBO.tran_master(NOLOCK) WHERE field1 = @ctrlNo)
		BEGIN
			INSERT INTO @TEMPTBL
			EXEC FastMoneyPro_Account.dbo.proc_transactionVoucherEntry @ctrlNo
		END
		EXEC [proc_errorHandler] 0, 'Transaction cancelled successfully', @TranId

		EXEC proc_cancelTran @flag = 'cancel', @user = 'system', @controlNo = @ctrlNo, @cancelReason = 'Transaction Rejected from partner', @refund = @RefundCharge	
		
		RETURN
	END

	IF @PayoutStatus = 'Paid' AND @payStatus = 'POST'
	BEGIN
		UPDATE remitTran SET payStatus = 'Paid', tranStatus = 'Paid', paidBy = 'system', paidDate = GETDATE()
		WHERE id = @TranId 
		EXEC proc_errorHandler 0, 'Transaction status synced as paid successfully', NULL
		RETURN
	END

	EXEC proc_errorHandler 1,'Transaction Not found For detail', NULL
	RETURN
END