DB_Script/StoredProc/mobile_proc_OTPRequest.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[mobile_proc_OTPRequest]    Script Date: 10/20/2023 11:35:50 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[mobile_proc_OTPRequest] (
	@FLAG VARCHAR(20)
	,@OTP_CODE VARCHAR(6)
	,@USER_ID VARCHAR(150)
	,@REQUEST_FOR VARCHAR(15) = NULL
	,@RECEIVER_ID BIGINT = NULL
	)
AS
	;

----------------------------------------------
-- #101 , #361 - CHANGES FOR MOBILE MULTI LINGUAL
-- #501,500 -Prompt for OTP when editing receiver
-- #597- Change OPT valid for 15 mins
-- #17825 changes for email template 
-- #20532 otp email for user reg from customer portal
----------------------------------------------
SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN
	DECLARE @ROW_ID BIGINT = NULL
		,@IS_EXPIRED BIT = 0
		,@IS_USED BIT = 0
		,@MOBILE VARCHAR(30)
		,@EXPIRE_MINUTES TINYINT = 15
		,@CURRENT_OTP VARCHAR(10)
		,@REQUEST_MINUTES INT
		,@fcmid VARCHAR(250)
		,@custName VARCHAR(200)
		,@mobileNo VARCHAR(500)

	IF @FLAG = 'REQUEST'
	BEGIN
		IF @USER_ID IS NULL
			OR (
				NOT EXISTS (
					SELECT *
					FROM CUSTOMERMASTER(NOLOCK)
					WHERE USERNAME = @USER_ID
					)
				AND NOT EXISTS (
					SELECT *
					FROM CUSTOMERMASTERTEMP(NOLOCK)
					WHERE USERNAME = @USER_ID
					)
				)
		BEGIN
			SELECT 1 Code
				,'Invalid user!' Msg
				,NULL Id

			RETURN
		END

		IF @REQUEST_FOR = 'REGISTER'
		BEGIN
			IF EXISTS (
					SELECT *
					FROM TBL_MOBILE_OTP_REQUEST(NOLOCK)
					WHERE [USER_ID] = @USER_ID
						AND IS_SUCCESS = 1
						AND REQUEST_FOR = 'REGISTER'
					)
			BEGIN
				SELECT 2 Code
					,'User already verified!' Msg
					,NULL Id

				RETURN
			END

			--SELECT @custName = CONCAT(ISNULL(firstName,''), ' ', ISNULL(middleName,''), ' ', ISNULL(lastName1,'')) FROM customerMaster WHERE email = @USER_ID
			SELECT @CURRENT_OTP = OTP_CODE
				,@custName = CONCAT (
					ISNULL(cm.firstName, '')
					,' '
					,ISNULL(cm.middleName, '')
					,' '
					,ISNULL(cm.lastName1, '')
					)
				,@REQUEST_MINUTES = DATEDIFF(MINUTE, REQUESTED_DATE, GETDATE())
				,@mobileNo = cm.mobile
			FROM TBL_MOBILE_OTP_REQUEST otp
			LEFT JOIN CustomerMasterTemp cm ON otp.USER_ID = cm.email
			WHERE [USER_ID] = @USER_ID
				AND REQUEST_FOR = 'REGISTER'
				AND IS_EXPIRED = 0
		END
				--ELSE IF @REQUEST_FOR = 'RECEIVER_EDIT'
				--BEGIN
				--	IF EXISTS(SELECT * FROM TBL_MOBILE_OTP_REQUEST(NOLOCK) WHERE [USER_ID] = @USER_ID AND IS_SUCCESS = 1 AND REQUEST_FOR = 'RECEIVER_EDIT')
				--	BEGIN
				--		SELECT 5 Code, 'Receiver Edit already verified!' Msg, NULL Id
				--		RETURN
				--	END
				--	SELECT @CURRENT_OTP = OTP_CODE,  @REQUEST_MINUTES = DATEDIFF(MINUTE, REQUESTED_DATE, GETDATE())
				--	FROM TBL_MOBILE_OTP_REQUEST
				--	WHERE [USER_ID] = @USER_ID 
				--	AND REQUEST_FOR = 'RECEIVER_EDIT'
				--	AND IS_EXPIRED = 0
				--END
		--ELSE IF @REQUEST_FOR = 'RECEIVER'
		--BEGIN
		--	IF @RECEIVER_ID IS NULL
		--	BEGIN
		--		SELECT 3 Code
		--			,'Receiver ID can not be null for Receiver OTP Request!' Msg
		--			,NULL Id

		--		RETURN
		--	END

		--	IF EXISTS (
		--			SELECT *
		--			FROM TBL_MOBILE_OTP_REQUEST(NOLOCK)
		--			WHERE [USER_ID] = @USER_ID
		--				AND IS_SUCCESS = 1
		--				AND REQUEST_FOR = 'RECEIVER'
		--				AND RECEIVER_ID = @RECEIVER_ID
		--			)
		--	BEGIN
		--		SELECT 4 Code
		--			,'Receiver already verified!' Msg
		--			,NULL Id

		--		RETURN
		--	END

		--	SELECT @CURRENT_OTP = OTP_CODE
		--		,@custName = CONCAT (
		--			ISNULL(cm.firstName, '')
		--			,' '
		--			,ISNULL(cm.middleName, '')
		--			,' '
		--			,ISNULL(cm.lastName1, '')
		--			)
		--		,@REQUEST_MINUTES = DATEDIFF(MINUTE, REQUESTED_DATE, GETDATE())
		--	FROM TBL_MOBILE_OTP_REQUEST otp
		--	LEFT JOIN customerMaster cm ON otp.USER_ID = cm.email
		--	WHERE [USER_ID] = @USER_ID
		--		AND RECEIVER_ID = @RECEIVER_ID
		--		AND REQUEST_FOR = 'RECEIVER'
		--		AND IS_EXPIRED = 0
		--END

		IF @REQUEST_FOR = 'REGISTER'
			OR @REQUEST_FOR = 'RECEIVER'
		BEGIN
			IF @CURRENT_OTP IS NOT NULL
				AND @REQUEST_MINUTES <= @EXPIRE_MINUTES
			BEGIN
			SELECT @mobileNo = mobile from customerMaster where email = @USER_ID
				SELECT 103 Code
					,'Success, Old OTP used' Msg
					,@CURRENT_OTP Id
					,@custName Extra
					,@mobileNo Extra2
				RETURN
			END

			IF @CURRENT_OTP IS NOT NULL
				AND @REQUEST_MINUTES > @EXPIRE_MINUTES
			BEGIN
				UPDATE TBL_MOBILE_OTP_REQUEST
				SET IS_EXPIRED = 1
					,MODIFIED_DATE = GETDATE()
				WHERE [USER_ID] = @USER_ID
					AND REQUEST_FOR = @REQUEST_FOR
					AND ISNULL(RECEIVER_ID, 1) = CASE 
						WHEN @REQUEST_FOR = 'RECEIVER'
							THEN @RECEIVER_ID
						ELSE 1
						END
					AND OTP_CODE = @CURRENT_OTP
			END
		END

		IF (
				@custName IS NULL
				OR @custName = ''
				)
		BEGIN
			SELECT @custName = TRIM(REPLACE(CONCAT (
							FirstName + ' '
							,MiddleName + ' '
							,LastName1 + ' '
							), '  ', ' '))
			FROM CustomerMasterTemp
			WHERE email = @USER_ID
		END

		IF (
				@mobileNo IS NULL
				OR @mobileNo = ''
				)
		BEGIN
			--SELECT @mobileNo = mobile
			--FROM CustomerMasterTemp
			--WHERE email = @USER_ID
			SELECT @mobileNo = (
        SELECT TOP 1 mobile
        FROM CustomerMasterTemp
        WHERE email = @USER_ID
        UNION ALL
        SELECT TOP 1 mobile
        FROM customerMaster
        WHERE email = @USER_ID
    )
		END

		INSERT INTO TBL_MOBILE_OTP_REQUEST (
			[USER_ID]
			,OTP_CODE
			,REQUESTED_DATE
			,IS_EXPIRED
			,IS_SUCCESS
			,REQUEST_FOR
			,RECEIVER_ID
			)
		SELECT @USER_ID
			,@OTP_CODE
			,GETDATE()
			,0
			,0
			,@REQUEST_FOR
			,@RECEIVER_ID

		SELECT 100 Code
			,'Success' Msg
			,@OTP_CODE Id
			,@custName Extra
			,@mobileNo Extra2
	END
	ELSE IF @FLAG = 'SUBMIT'
	BEGIN
		IF @USER_ID IS NULL
			OR (
				NOT EXISTS (
					SELECT *
					FROM CUSTOMERMASTER(NOLOCK)
					WHERE USERNAME = @USER_ID
					)
				AND NOT EXISTS (
					SELECT *
					FROM CUSTOMERMASTERTEMP(NOLOCK)
					WHERE USERNAME = @USER_ID
					)
				)
		BEGIN
			SELECT 1 Code
				,'Invalid user!' Msg
				,NULL Id

			RETURN
		END

		IF @REQUEST_FOR = 'RECEIVER'
			OR @REQUEST_FOR = 'RECEIVER_EDIT'
		BEGIN
			SELECT @ROW_ID = ROW_ID
				,@IS_EXPIRED = IS_EXPIRED
				,@IS_USED = IS_SUCCESS
				,@custName = CONCAT (
					ISNULL(cm.firstName, '')
					,' '
					,ISNULL(cm.middleName, '')
					,' '
					,ISNULL(cm.lastName1, '')
					)
			FROM TBL_MOBILE_OTP_REQUEST(NOLOCK) otp
			LEFT JOIN customerMaster cm ON otp.USER_ID = cm.email
			WHERE [USER_ID] = @USER_ID
				AND OTP_CODE = @OTP_CODE
				AND REQUEST_FOR = @REQUEST_FOR
				AND RECEIVER_ID = @RECEIVER_ID
		END
		ELSE
		BEGIN
			SELECT @ROW_ID = ROW_ID
				,@IS_EXPIRED = IS_EXPIRED
				,@IS_USED = IS_SUCCESS
				,@fcmid = ISNULL(mu.deviceId, '')
				,@custName = CONCAT (
					ISNULL(cm.firstName, '')
					,' '
					,ISNULL(cm.middleName, '')
					,' '
					,ISNULL(cm.lastName1, '')
					)
			FROM TBL_MOBILE_OTP_REQUEST t(NOLOCK)
			LEFT JOIN mobile_userRegistration mu ON t.[USER_ID] = mu.username
			LEFT JOIN customerMaster cm ON t.USER_ID = cm.email
			WHERE [USER_ID] = @USER_ID
				AND OTP_CODE = @OTP_CODE
				AND REQUEST_FOR = @REQUEST_FOR
		END

		IF @ROW_ID IS NULL
		BEGIN
			SELECT 2 Code
				,'Invalid OTP!' Msg
				,NULL Id

			RETURN
		END

		IF @IS_EXPIRED = 1
		BEGIN
			SELECT 3 Code
				,'OTP is already expired!' Msg
				,NULL Id

			RETURN
		END

		IF @IS_USED = 1
		BEGIN
			SELECT 4 Code
				,'OTP is already used!' Msg
				,NULL Id

			RETURN
		END

		IF EXISTS (
				SELECT TOP 1 1
				FROM TBL_MOBILE_OTP_REQUEST(NOLOCK)
				WHERE ROW_ID = @ROW_ID
					AND DATEDIFF(MINUTE, REQUESTED_DATE, GETDATE()) > @EXPIRE_MINUTES
				)
		BEGIN
			UPDATE TBL_MOBILE_OTP_REQUEST
			SET IS_EXPIRED = 1
			WHERE ROW_ID = @ROW_ID

			SELECT 3 Code
				,'OTP is already expired!' Msg
				,NULL Id

			RETURN
		END

		UPDATE TBL_MOBILE_OTP_REQUEST
		SET VERIFIED_DATE = GETDATE()
			,IS_SUCCESS = 1
		WHERE ROW_ID = @ROW_ID

		IF @REQUEST_FOR = 'RECEIVER'
			UPDATE RECEIVERINFORMATION
			SET isOTPVerified = 1
			WHERE RECEIVERID = @RECEIVER_ID
		ELSE
		BEGIN
			IF EXISTS (
					SELECT *
					FROM CUSTOMERMASTER(NOLOCK)
					WHERE username = @USER_ID
					)
				UPDATE CUSTOMERMASTER
				SET isEmailVerified = 1
				WHERE username = @USER_ID
			ELSE
				UPDATE CUSTOMERMASTERTEMP
				SET isEmailVerified = 1
				WHERE username = @USER_ID

			IF EXISTS (
					SELECT *
					FROM CUSTOMERMASTER(NOLOCK)
					WHERE email = @USER_ID
					)
				UPDATE CUSTOMERMASTER
				SET isEmailVerified = 1
				WHERE email = @USER_ID
			ELSE
				UPDATE CUSTOMERMASTERTEMP
				SET isEmailVerified = 1
				WHERE email = @USER_ID
		END

		SELECT 0 Code
			,'OTP verified successfully.' Msg
			,@ROW_ID Id
			,@EXPIRE_MINUTES Extra
			,@fcmid Extra2
	END
END