INBOUND_SEND_API/DB/SP's/PROC_REMIT_INBOUND_TRANSACTION_STATUS.sql

GO
use FastMoneyPro_Remit;

GO
CREATE OR ALTER PROC  PROC_REMIT_INBOUND_TRANSACTION_STATUS
(
	@Flag VARCHAR(20)
	, @UserName VARCHAR(80) = NULL
	, @AgentId INT = NULL
	, @PartnerTransactionId VARCHAR(40) = NULL
	, @IMEControlNo VARCHAR(40) = NULL
)
AS;
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
	BEGIN
		DECLARE @ErrorMsg VARCHAR(MAX), @TransactionId BIGINT = NULL, @Status VARCHAR(40), @PayStatus VARCHAR(40), @FinalStatus VARCHAR(40), @StatusMessage VARCHAR(200)
		IF @Flag = 'STATUS'
		BEGIN
			SELECT @TransactionId = id
					, @Status = tranStatus
					, @PayStatus = payStatus
					, @IMEControlNo = dbo.decryptDb(controlNo)
			FROM remitTran (NOLOCK)
			WHERE controlNo = dbo.FNAEncryptString(@IMEControlNo)
			OR controlNo2 = dbo.FNAEncryptString(@PartnerTransactionId)

			IF @TransactionId IS NULL
			BEGIN
				SELECT @TransactionId = id
						, @Status = tranStatus
						, @PayStatus = payStatus
						, @IMEControlNo = dbo.decryptDb(controlNo)
				FROM remitTranTemp (NOLOCK)
				WHERE controlNo = dbo.FNAEncryptString(@IMEControlNo)
				OR controlNo2 = dbo.FNAEncryptString(@PartnerTransactionId)

				IF @TransactionId IS NULL
				BEGIN
					EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid transaction details!', @Id = NULL;
					RETURN;
				END

				IF @Status = 'Hold' AND @PayStatus = 'UNPAID'
					SELECT @FinalStatus = 'Hold', @StatusMessage = 'Transaction is waiting for approval!'
				ELSE
					SELECT @FinalStatus = @Status, @StatusMessage = 'Transaction is in '+@Status+' and is waiting for approval!'

				EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 100, @ErrorMessage = 'Success', @Id = @IMEControlNo, @Extra = @FinalStatus, @Extra2 = @StatusMessage;
				RETURN;
			END

			IF @Status = 'CANCEL' OR @PayStatus = 'CANCEL'
				SELECT @FinalStatus = 'Cancel', @StatusMessage = 'Transaction is cancelled!'

			IF @PayStatus = 'Paid' AND @Status <> 'Cancel'
				SELECT @FinalStatus = 'Paid', @StatusMessage = 'Transaction is paid!'

			IF @Status = 'CancelRequest'
				SELECT @FinalStatus = 'CancelRequest', @StatusMessage = 'Transaction is paid!'

			IF @Status = 'Payment' AND @PayStatus = 'UNPAID'
				SELECT @FinalStatus = 'Unpaid', @StatusMessage = 'Transaction is in unpaid status!'

			IF @Status = 'Payment' AND @PayStatus = 'Post'
				SELECT @FinalStatus = 'Post', @StatusMessage = 'Transaction is in post status and waiting from thirdparty for final status!'

			EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 100, @ErrorMessage = 'Success', @Id = @IMEControlNo, @Extra = @FinalStatus, @Extra2 = @StatusMessage;
			RETURN;
		END
	END
END TRY
BEGIN CATCH
	IF @@TRANCOUNT>0    
		ROLLBACK TRANSACTION
	
	SET @ErrorMsg = 'Exception executing SP: ' + ERROR_MESSAGE()
	EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = @ErrorMsg, @Id = NULL;
END CATCH