MOBILE_API/DB/SPs/proc_validateTransaction.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_validateTransaction]    Script Date: 9/27/2019 1:30:14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_validateTransaction] 
@User				VARCHAR(100),
@SenderId			INT,
@sIpAddress			VARCHAR(20),
           
@ReceiverId			INT = NULL,
@rFirstName			VARCHAR(50),
@rMiddleName		VARCHAR(50) = NULL,
@rLastName			VARCHAR(50),
@rIdType			VARCHAR(50) = NULL,
@rIdNo				VARCHAR(30) = NULL,
@rIdIssue			VARCHAR(10) = NULL,
@rIdExpiry			VARCHAR(10) = NULL,
@rDob				VARCHAR(10) = NULL,
@rMobileNo			VARCHAR(20),
@rNativeCountry		VARCHAR(50) = NULL,
@rStateId			INT = NULL,
@rDistrictId		INT = NULL,
@rAddress			VARCHAR(100),
@rCity				VARCHAR(50),
@rEmail				VARCHAR(50) = NULL,
@rAccountNo			VARCHAR(50) = NULL,
           
@sCountryId			INT,
@pCountryId			INT,
@deliveryMethodId	INT,
@pBankId			BIGINT = NULL,
@pBranchId			BIGINT = NULL,
@collCurr			VARCHAR(3),
@payoutCurr			VARCHAR(3),
@collAmt			MONEY,
@payoutAmt			MONEY,
@transferAmt		MONEY,
@exRate				DECIMAL(12,9),
@calBy				CHAR(1),
@tpExRate			DECIMAL(12,9),
@payOutPartnerId	BIGINT,
@forexSessionId		VARCHAR(40),
@kftcLogId			VARCHAR(20) = NULL,
@paymentType		VARCHAR(20),
           
@PurposeOfRemittance VARCHAR(100),
@SourceOfFund		VARCHAR(100),
@RelWithSender		VARCHAR(100),
@processId			VARCHAR(40)
AS
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	BEGIN TRY 

	DECLARE @complianceRuleId	INT
			,@cAmtUSD					MONEY
			,@complienceMessage			VARCHAR(1000)   = NULL 
			,@shortMsg					VARCHAR(100)    = NULL 
			,@complienceErrorCode		TINYINT			= NULL
			,@compErrorCode				INT 
			,@customerType 				INT
	
	IF EXISTS(	SELECT 'A' FROM customerMaster(NOLOCK) WHERE email = @user AND customerType = '11048')
	BEGIN
		SELECT  '1' ErrorCode ,'Service is temporary blocked!' Msg ,NULL ID
		RETURN
	END

	IF @paymentType IS NULL
		SET @paymentType = 'WALLET'
     
		IF @paymentType = 'autodebit'
		BEGIN
			DECLARE @DATE DATETIME, @NextDate datetime
			SELECT @DATE = CAST(GETDATE() AS DATE)
			SELECT @NextDate = DATEADD(DAY,1,@DATE)

			SELECT @DATE = @DATE+' 23:20:00',@NextDate = @NextDate+' 00:40:00'

			IF GETDATE() BETWEEN @DATE AND @NextDate
			BEGIN
				SELECT  '1' ErrorCode ,'KFTC service is not available between 11:30 PM to 12:30 AM' Msg ,NULL ID
				RETURN
			END
		END
	   
            DECLARE @sCurrCostRate FLOAT ,@sCurrHoMargin FLOAT ,@pCurrCostRate FLOAT ,@customerRate FLOAT ,@agentCrossSettRate FLOAT,@serviceCharge MONEY
		
            DECLARE @iServiceCharge MONEY ,@iTAmt MONEY ,@iPAmt MONEY ,@iCAmt MONEY ,@iCustomerRate FLOAT 
            DECLARE @place INT ,@currDecimal INT;
	
            DECLARE @agentAvlLimit MONEY--,@serviceCharge MONEY

			DECLARE @sAgent BIGINT,@sAgentName VARCHAR(100),@sBranch INT,@sBranchName VARCHAR(100),@sSuperAgent INT,@sSuperAgentName VARCHAR(100)

			DECLARE @sBirthDate DATE,@sIdIssueDate DATE,@sIdExpiryDate DATE,@senderName VARCHAR(100),@sIdNo VARCHAR(50),@sIdType VARCHAR(50),@sMobile VARCHAR(15),@sOccupation VARCHAR(50)

			DECLARE @pAgent BIGINT,@pSuperAgent BIGINT,@pAgentName VARCHAR(100),@receiverName VARCHAR(100)

			SELECT @sCountryId = 118,@sBranch = 2080

			SELECT @sAgent = sAgent, @sAgentName = sAgentName, @sBranch = sBranch, @sBranchName = sBranchName,
					@sSuperAgent = sSuperAgent, @sSuperAgentName = sSuperAgentName 
			FROM dbo.FNAGetBranchFullDetails(@sBranch)

			----SELECT @pCountry = COUNTRYNAME FROM COUNTRYMASTER (NOLOCK) WHERE COUNTRYID = @pCountryId
	
		SELECT TOP 1 @pAgent = AM.agentId
		FROM agentMaster AM(NOLOCK) 
		WHERE AM.parentId = @payOutPartnerId AND agentType = 2903 
		AND AM.isSettlingAgent = 'Y' AND AM.isApiPartner = 1
		
		SELECT @pSuperAgent = sSuperAgent,@pAgent = sAgent,@pAgentName = sAgentName FROM dbo.FNAGetBranchFullDetails(@pAgent)
		
		IF @receiverId IS NOT NULL
		BEGIN
			SELECT @receiverName = ISNULL(firstName,'')+ISNULL(' '+middleName,'')+ISNULL(' '+lastName1,'') +ISNULL(' '+lastName2,'') 
					,@rIdNo = idNumber
			FROM dbo.receiverInformation(NOLOCK)
			WHERE receiverId = @receiverId
		END
		ELSE
			SET @receiverName = ISNULL(@rFirstName,'')+ISNULL(' '+@rMiddleName,'')+ISNULL(' '+@rLastName,'')

		IF @rFirstName IS NULL AND @receiverId IS NULL
		BEGIN
			EXEC proc_errorHandler '1','Receiver name cannot be empty', NULL;
            RETURN;
		END

		IF NOT EXISTS (SELECT '' FROM TblPartnerwiseCountry(NOLOCK) 
				WHERE AgentId = @payOutPartnerId AND CountryId = @pCountryId 
				AND ISNULL(PaymentMethod,@deliveryMethodId) = @deliveryMethodId and IsActive = 1
				)
		BEGIN
			EXEC proc_errorHandler  '1','Oops, something went wrong.Please perform the transaction again' ,null
			RETURN;
		END

		IF @payOutPartnerId = 585209 AND @pCountryId = 33 AND @deliveryMethodId  = 2 AND @rIdNo IS NULL
		BEGIN
			EXEC proc_errorHandler '1','Receiver id number is missing.', NULL;
            RETURN;
		END

		IF ISNULL(@exRate,0) = 0
		BEGIN		
			EXEC proc_errorHandler '1', 'Transaction cannot be proceed.Exchange Rate not defined', NULL
			RETURN
		END

		IF @pAgent IS NULL
		BEGIN
			EXEC proc_errorHandler  '1','Oops, something went wrong.Please perform the transaction again' ,null
			RETURN;
		END

		IF NOT EXISTS (SELECT 'X' FROM dbo.customerMaster(nolock) WHERE email = @user AND approvedDate IS NOT NULL)
		BEGIN
			EXEC proc_errorHandler '1','You are not authorized to perform transaction :(', NULL;
            RETURN;
		END

		IF ISNULL(@paymentType,'') NOT IN ('wallet', 'autodebit')
		BEGIN
			EXEC proc_errorHandler '1','Invalid payment method.Please perform the transaction again!', NULL;
            RETURN;
		END

		SELECT  @sIdType = idType,
				@sIdNo = idNumber ,
				@sMobile = cm.mobile,
				@senderName = isnull(fullName, firstname),
				@agentAvlLimit = dbo.FNAGetCustomerACBal(email),
				@sOccupation = V.detailTitle,	
				@sIdIssueDate = cm.idIssueDate,	
				@sIdExpiryDate = cm.idExpiryDate,	
				@sBirthDate = cm.dob,
				@customerType = customerType
		FROM    customerMaster(NOLOCK) cm
		LEFT JOIN staticDataValue V(NOLOCK) ON V.valueId = CM.occupation
		WHERE   customerId = @senderId AND cm.approvedDate IS NOT NULL

		IF ISNULL(@sIdNo,'') = ''
		BEGIN
			EXEC proc_errorHandler '1','Invalid request found from customer.', NULL;
			RETURN
		END
		--IF @sIdIssueDate IS NULL OR @sIdIssueDate > CAST(GETDATE() AS DATE)
		--BEGIN
		--	EXEC proc_errorHandler '1','Enter ID Issue Date.Call:15886864', NULL;
		--	RETURN
		--END
		--IF @sBirthDate IS NULL OR @sBirthDate > CAST(GETDATE() AS DATE)
		--BEGIN
		--	EXEC proc_errorHandler '1','Enter valid DOB.Call:15886864', NULL;
		--	RETURN
		--END
		IF ISNULL(@sOccupation,'')=''
		BEGIN
			EXEC proc_errorHandler '1','Update Customer Occupation.Call:15886864', NULL;
			RETURN
		END
		
		/* Easy Remit limit check */
		IF @customerType = '11048'
		BEGIN
			IF ISNULL(@collAmt, 0) > 1000000
			BEGIN
				EXEC proc_errorHandler '1','You cannot remit more than one million(KRW)!', NULL
				RETURN
			END
		END

		IF @paymentType = 'WALLET'
		BEGIN
			IF ISNULL(@agentAvlLimit, 1) < ISNULL(@collAmt, 0)
			BEGIN
				EXEC proc_errorHandler '1','You donot have sufficient balance to do the transaction!', NULL;
				RETURN;
			END;
		END

		--IF (SELECT COUNT(1) FROM dbo.Split(' ',@rFirstName)) < 2
		IF @rFirstName IS NULL
		BEGIN
			EXEC proc_errorHandler '1', 'Receiver First Name is missing', NULL
			RETURN
		END
		IF @rLastName IS NULL
		BEGIN
			EXEC proc_errorHandler '1', 'Receiver Last Name is missing', NULL
			RETURN
		END
		IF isnull(@rMobileNo, '') = ''
		BEGIN
			EXEC proc_errorHandler '1', 'Receiver mobile number is required!', NULL
			RETURN
		END
		IF @rAddress IS NULL
        BEGIN
            EXEC proc_errorHandler '1','Receiver Address missing', NULL;
            RETURN;
        END;
		IF ISNULL(@deliveryMethodId, '') = ''
        BEGIN
            EXEC proc_errorHandler '1','Please choose payment mode', NULL;
            RETURN;
        END;
		
		IF ISNULL(@transferAmt, 0) = 0
        BEGIN
            EXEC proc_errorHandler '1','Transfer Amount missing', NULL;
            RETURN;
		END;
		IF ISNULL(@exRate, 0) = 0
        BEGIN
            EXEC proc_errorHandler '1', 'Exchange Rate missing',NULL;
            RETURN;
        END;
		if isnull(@PurposeOfRemittance,'') = ''
		BEGIN
			EXEC proc_errorHandler '1', 'Purpose of Remittance is required!', NULL
			RETURN
		END
		if isnull(@SourceOfFund,'') = ''
		BEGIN
			EXEC proc_errorHandler '1', 'Source of Fund is required!', NULL
			RETURN
		END
        IF ISNULL(@collAmt, 0) = 0
        BEGIN
           EXEC proc_errorHandler '1','Collection Amount is missing. Cannot send transaction',NULL;
            RETURN;	
		END;

        IF @deliveryMethodId = 2 -- FOR BANK DEPOSIT
        BEGIN    
			IF NOT EXISTS(SELECT 'A' FROM agentMaster(NOLOCK) WHERE agentId = @pBankId AND agenttype = 2903 AND isActive='Y' AND agentRole = '2')
			BEGIN	
				EXEC proc_errorHandler '1', 'Invalid bank selected', NULL
				RETURN
			END	
				
			IF @pBankId IS NULL
			BEGIN
				EXEC proc_errorHandler '1', 'Please select bank', NULL
				RETURN
			END
			IF @pAgent IS NULL 
			BEGIN
				EXEC proc_errorHandler '1', 'Please select bank', NULL
				RETURN
			END
			IF @rAccountNo IS NULL
			BEGIN
				EXEC proc_errorHandler '1', 'Account number cannot be blank', NULL
				RETURN
			END
        END;

		SELECT @iServiceCharge = ISNULL(amount, -1) 
		FROM [dbo].FNAGetServiceCharge(
							@sCountryId, @sSuperAgent, @sAgent, @sBranch, 
							@pCountryId, @pSuperAgent, @pAgent, @pBranchId, 
							@deliveryMethodId, @collAmt, @collCurr
						)
																					
		IF @iServiceCharge = -1
		BEGIN
			EXEC proc_errorHandler '1', 'Transaction cannot be proceed. Service Charge is not defined', NULL
			RETURN
		END
			
		SELECT 
			 @customerRate			= customerRate
			,@sCurrCostRate			= sCurrCostRate
			,@sCurrHoMargin			= sCurrHoMargin
			,@pCurrCostRate			= pCurrCostRate
			,@agentCrossSettRate	= agentCrossSettRate
			,@serviceCharge			= serviceCharge
		FROM exRateCalcHistory(NOLOCK) 
		WHERE FOREX_SESSION_ID = @forexSessionId 
		AND [USER_ID] = @User	AND isExpired = 0

		IF ISNULL(@serviceCharge,0) > @collAmt
        BEGIN
            SELECT  '1' ErrorCode ,'Collection amount should be more than service charge' Msg ,NULL
            RETURN;
        END;
		IF ISNULL(@serviceCharge,0) <> @iServiceCharge
        BEGIN
            SELECT  '1' ErrorCode ,'Please re calculate rate and try again' Msg ,NULL
            RETURN;
        END;

		IF @customerRate IS NULL
		BEGIN
			EXEC proc_errorHandler 1, 'Transaction cannot be proceed.Please re-calculate the amount again.', NULL
			RETURN
		END
		
		IF ISNULL(@exRate,0) <> ISNULL(@customerRate,1)
		BEGIN
			EXEC proc_errorHandler 1, 'Amount detail not match. Please re-calculate the amount again', NULL
			RETURN
		END
		
		SELECT @iCustomerRate = @exRate, @iTAmt = @collAmt - @iServiceCharge

		SELECT @place = place, @currDecimal = currDecimal
		FROM currencyPayoutRound WITH(NOLOCK) 
		WHERE ISNULL(isDeleted, 'N') = 'N' 
		AND currency = @payoutCurr AND (tranType IS NULL OR tranType = @deliveryMethodId)
		
		SET @currDecimal = ISNULL(@currDecimal, 0)
		SET @place = ISNULL(@place, 0)
			
		SET @iPAmt = ROUND(@iTAmt * @iCustomerRate, @currDecimal)

		IF @payoutCurr = 'USD'
			SET @iPAmt = ROUND(@iPAmt, 2)
		ELSE 
			SET @iPAmt = ROUND(@iPAmt, 0)
		
		IF @payoutCurr IN('MNT','MMK') AND ISNULL(@iPAmt,0) < ISNULL(@payoutAmt,1)+50
		BEGIN
			SET @payoutAmt = ISNULL(@iPAmt,0)
		END
		IF ISNULL(@iPAmt,0) <> ISNULL(@payoutAmt,1)
		BEGIN
			declare @iMsg VARCHAR(500) = 'Amount detail not match. Please re-calculate the amount again.'
			--+cast(@iPAmt as varchar(10))+cast(@payoutAmt as varchar(10))
			EXEC proc_errorHandler 1, @iMsg, NULL
			RETURN
		END

		SET @cAmtUSD = @transferAmt / (@sCurrCostRate + ISNULL(@sCurrHoMargin, 0))

		--Compliance Checking		 
		EXEC [proc_complianceRuleDetail]
			@flag			= 'sender-limit'
			,@user			= @user
			,@sIdType		= @sIdType
			,@sIdNo			= @sIdNo
			,@cAmt			= @transferAmt
			,@cAmtUSD		= @cAmtUSD
			,@customerId	= @senderId
			,@pCountryId	= @pCountryId
			,@deliveryMethod= @deliveryMethodId
			,@message		= @complienceMessage OUTPUT
			,@shortMessage  = @shortMsg    OUTPUT
			,@errCode		= @complienceErrorCode OUTPUT
			,@ruleId		= @complianceRuleId  OUTPUT
   
		IF(@complienceErrorCode <> 0)
		BEGIN  
			IF(@complienceErrorCode = 1)
			BEGIN
				SET @compErrorCode=101
				--SELECT 101 errorCode,@msg msg, @complienceErrorCode id, @complienceMessage compApproveRemark,'compliance' vtype
			END 
			ELSE 
			BEGIN
				SET @compErrorCode=102
				INSERT remitTranComplianceTemp(csDetailTranId, matchTranId, agentRefId)
				SELECT @complianceRuleId, NULL, @processId

				--SELECT 102 errorCode,@msg msg, @complienceErrorCode id, @complienceMessage compApproveRemark,'compliance' vtype
			END
				
			INSERT INTO ComplianceLog(senderName, senderCountry, senderIdType, senderIdNumber, senderMobile, receiverName
			, receiverCountry,payOutAmt,complianceId,complianceReason,complainceDetailMessage,createdBy,createdDate,logType)

			SELECT @senderName, @sCountryId, @sIdType, @sIdNo, @sMobile, @receiverName
			, @pCountryId, @collAmt, @complianceRuleId, @shortMsg, @complienceMessage, @user, GETDATE(),'sender-limit'
		END	   
			
		IF @complienceErrorCode = 1
		BEGIN
			EXEC proc_errorHandler 1, @complienceMessage, NULL
			RETURN;
		END;

		EXEC [proc_complianceRuleDetail]
				@flag				= 'receiver-limit'
				,@user				= @user
				,@sIdType			= @sIdType
				,@sIdNo				= @sIdNo
				,@receiverName		= @receiverName
				,@cAmt				= @transferAmt
				,@cAmtUSD			= @cAmtUSD
				,@customerId		= @senderId
				,@pCountryId		= @pCountryId
				,@receiverMobile	= @rMobileNo
				,@deliveryMethod	= @deliveryMethodId
				,@message			= @complienceMessage OUTPUT
				,@shortMessage		= @shortMsg    OUTPUT
				,@errCode			= @complienceErrorCode OUTPUT
				,@ruleId			= @complianceRuleId  OUTPUT
   
			IF(@complienceErrorCode <> 0)
			BEGIN  
				IF(@complienceErrorCode = 1)
				BEGIN
					SET @compErrorCode=101
					--SELECT 101 errorCode,@msg msg, @complienceErrorCode id, @complienceMessage compApproveRemark,'compliance' vtype
				END 
				ELSE 
				BEGIN
					SET @compErrorCode=102
					INSERT remitTranComplianceTemp(csDetailTranId, matchTranId, agentRefId)
					SELECT @complianceRuleId, NULL, @processId

					--SELECT 102 errorCode,@msg msg, @complienceErrorCode id, @complienceMessage compApproveRemark,'compliance' vtype
				END
				
				INSERT INTO ComplianceLog(senderName, senderCountry, senderIdType, senderIdNumber, senderMobile, receiverName
				, receiverCountry,payOutAmt,complianceId,complianceReason,complainceDetailMessage,createdBy,createdDate,logType)

				SELECT @senderName, @sCountryId, @sIdType, @sIdNo, @sMobile, @receiverName
				, @pCountryId, @collAmt, @complianceRuleId, @shortMsg, @complienceMessage, @user, GETDATE(),'receiver-limit'
			END	  
			
			IF @complienceErrorCode = 1
			BEGIN
				EXEC proc_errorHandler 1, @shortMsg, NULL
				RETURN;
			END;
		--Compliance checking end

		DECLARE @remitTranTemp TABLE (
						tranId BIGINT,controlNo VARCHAR(20),cAmt MONEY,receiverName VARCHAR(200) ,
						receiverIdType VARCHAR(100),receiverIdNumber VARCHAR(50),dot DATETIME
					);

		INSERT INTO @remitTranTemp( tranId ,controlNo ,cAmt ,receiverName ,receiverIdType ,receiverIdNumber ,dot )
		SELECT  rt.id ,rt.controlNo ,rt.cAmt ,rt.receiverName ,rec.idType ,rec.idNumber ,rt.createdDateLocal
		FROM vwRemitTran rt WITH ( NOLOCK )
		INNER JOIN vwTranSenders sen WITH ( NOLOCK ) ON rt.id = sen.tranId
		INNER JOIN vwTranReceivers rec WITH ( NOLOCK ) ON rt.id = rec.tranId
		WHERE sen.customerId = @senderId
		AND ( rt.approvedDate BETWEEN CONVERT(VARCHAR,GETDATE(),101) AND CONVERT(VARCHAR,GETDATE(),101)+ ' 23:59:59'
				OR ( approvedBy IS NULL AND cancelApprovedBy IS NULL )
			);
		
		IF EXISTS ( SELECT  'X' FROM @remitTranTemp
					WHERE   cAmt = @collAmt
					AND ( receiverName = @rFirstName ) AND DATEDIFF(MI, dot, GETDATE()) <= 2 
					)
		BEGIN
			EXEC proc_errorHandler 1,'Similar transaction found', NULL;
			RETURN;
		END;
		
		IF @user = 'demo.gme@gmeremit.com'
		BEGIN
			EXEC proc_errorHandler 1,'You can not send money through test GME acocunt :(', NULL;
			RETURN;
		END

		SELECT 0 ErrorCode,'Transaction is valid' Msg, null id

		RETURN 
		
END TRY
BEGIN CATCH
	IF @@TRANCOUNT<>0
		ROLLBACK TRANSACTION
	DECLARE @errorMessage VARCHAR(MAX)
	SET @errorMessage = ERROR_MESSAGE()

	EXEC proc_errorHandler 1, @errorMessage, @user
END CATCH


GO