DB_Script/Deployment/#34182-registration-tracker/JsonRx_Proc_UserRegistration_V2.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[JsonRx_Proc_UserRegistration_V2]    Script Date: 8/26/2024 10:33:17 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[JsonRx_Proc_UserRegistration_V2](      
	 @language  VARCHAR(100) = 'en'      
	,@customerId VARCHAR(100) = NULL      
	,@username  VARCHAR(100) = NULL      
	,@flag   VARCHAR(100) = NULL      
	,@password  VARCHAR(100) = NULL      
	,@txnPin  VARCHAR(100) = NULL      
	,@idNumber  VARCHAR(100) = NULL      
	,@clientId  VARCHAR(100) = NULL      
	,@IMEI   VARCHAR(100) = NULL      
	,@appVersion VARCHAR(100) = NULL      
	,@phoneBrand VARCHAR(100) = NULL      
	,@phoneOs  VARCHAR(100) = NULL      
	,@osVersion  VARCHAR(100) = NULL      
	,@deviceId  VARCHAR(100) = NULL      
	,@dob   VARCHAR(100) = NULL      
	,@uuid   VARCHAR(100) = NULL      
	,@fcmId   VARCHAR(250) = NULL      
	,@mobile  VARCHAR(100) = NULL        
	,@nativeCountry VARCHAR(5)  = NULL  
	,@referralCode VARCHAR(100)  = NULL  
	,@referenceId BIGINT = NULL  
	,@fullName VARCHAR(50) = NULL
    ,@FirstName VARCHAR(100) = NULL
	,@LastName VARCHAR(100) = NULL
	,@MiddleName VARCHAR(100) = NULL
	,@postalCode VARCHAR(100) = NULL
	,@address1 NVARCHAR(200) = NULL
	,@address2 NVARCHAR(200) = NULL
	,@city VARCHAR(100) = NULL
	,@gender VARCHAR(10) = NULL
	,@tempCustId INT = NULL
)AS  
---------------------------------------------------
-- #101 - Mobile Changes  , #361 - Multi-Language
-- Remove customerAgreeDocumentTbl
-- SEt mobileUser='Y' for  @flag='sign-up-v2-old-cust'
--#684 Referral code not working for Existing Customer
-- #11394 - Addition of DOB & Full name in New registration Page
-- #24439 - Addition of Duplicate Mobile Number Validation in @flag='sign-up-v2-old-cust' and @flag='sign-up-v2-new-cust'
-- #31930 - removed referal Code Validation 
-- #32535 - Addition of mobile number length validation in @flag='sign-up-v2-new-cust'
-- #34182 - Added new @flag = 'sign-up-temp-cust' for registration tracker
---------------------------------------------------

BEGIN TRY      
	DECLARE @dobDB VARCHAR(200),@code VARCHAR(100),@_errorMsg VARCHAR(300),@verifiedDate DATETIME, @customerIdNo VARCHAR(50), @customerIdOld BIGINT = NULL,
			@userNameRandom VARCHAR(100), @mobileNumberCompare VARCHAR(30)
    
	IF @flag = 'sign-up-pre-old-cust'
	BEGIN
		SET @mobileNumberCompare = substring(@mobile, 4, len(@mobile)-3)

		IF (SELECT COUNT(0) 
				FROM CUSTOMERMASTER (NOLOCK) 
				WHERE IDNUMBER = @idNumber 
				AND mobile LIKE '%' + @mobileNumberCompare 
				AND CAST(dob AS DATE) = @dob 
				AND ISNULL(isDeleted, 'N') = 'N'
				AND ISNULL(ISACTIVE, 'Y') = 'Y') > 1
		BEGIN
			SELECT 1 ErrorCode,'Duplicate data found, please contact JME Support' Msg, NULL Id  
			RETURN
		END

		SELECT @customerIdOld = CUSTOMERID, @firstName = ISNULL(firstName, '')
		FROM CUSTOMERMASTER (NOLOCK)
		WHERE IDNUMBER = @idNumber
		AND mobile LIKE '%' + @mobileNumberCompare
		AND CAST(dob AS DATE) = @dob
		AND ISNULL(isDeleted, 'N') = 'N'
		AND ISNULL(ISACTIVE, 'Y') = 'Y'
		AND isExistingCustomer = 1
		AND USERNAME IS NULL

		IF @customerIdOld IS NULL 
		BEGIN
			SELECT 2 ErrorCode,'No records match with provided details, please contact JME Support' Msg, NULL Id  
			RETURN
		END
		
		--already registered      
		IF EXISTS(SELECT 'x' FROM dbo.mobile_userRegistration(NOLOCK) AS MUR WHERE customerId = @customerIdOld)      
		BEGIN      
			SELECT 3 ErrorCode,'You are already registered with JME!' Msg, NULL Id      
			RETURN      
		END

		IF EXISTS (SELECT * FROM CustomerMasterTemp (NOLOCK) WHERE customerId = @customerIdOld AND createdBy IS NOT NULL)
		BEGIN
			SELECT 3 ErrorCode,'You are already registered with JME!' Msg, NULL Id  
			RETURN
		END
		IF EXISTS (SELECT * FROM CustomerMaster (NOLOCK) WHERE customerId = @customerIdOld AND username IS NOT NULL)
		BEGIN
			SELECT 3 ErrorCode,'You are already registered with JME!' Msg, NULL Id  
			RETURN
		END
		
		DELETE FROM dbo.CustomerMasterTemp WHERE OldCustomerId = @customerIdOld

		SELECT @userNameRandom = DBO.GetRandomUsername(@firstName)

		IF EXISTS(SELECT TOP 1 1 FROM CustomerMasterTemp (NOLOCK) WHERE USERNAME = @userNameRandom)
		BEGIN
			SET @userNameRandom = DBO.GetRandomUsername(@firstName)
		END 

		INSERT INTO dbo.CustomerMasterTemp(      
						OldCustomerId,dob,mobile,isActive, idNumber    
					)      
		SELECT      
						@customerIdOld,@dob,@mobile,'N', @idNumber

		SET @customerId=SCOPE_IDENTITY()      

		SELECT 0 ErrorCode, 'Success' Msg, @customerId Id, '' userName
	END
	ELSE IF @flag = 'sign-up-v2-old-cust'
	BEGIN
		IF ISNULL(@username, '') = ''
		BEGIN
			SELECT 1 ErrorCode,'Username can not be blank!' Msg, NULL Id      
			RETURN  
		END
		--user already registered      
		IF EXISTS(SELECT 'x' FROM dbo.customerMaster AS CM(NOLOCK) WHERE CM.username = @username)      
		BEGIN      
			SELECT 2 ErrorCode,'Customer already registered, with this username!' Msg, NULL Id      
			RETURN      
		END 

		--email already registered      
		IF EXISTS(SELECT 'x' FROM dbo.customerMaster AS CM(NOLOCK) WHERE CM.email = @username)      
		BEGIN      
			SELECT 2 ErrorCode,'Customer already registered, with this email!' Msg, NULL Id      
			RETURN      
		END 

		-- duplicate mobile number
		IF EXISTS (
		    SELECT 1
		    FROM customermaster
		    WHERE 
		        (LEN(@mobile) = 10 AND (
		            mobile = @mobile OR
		            '+44' + mobile = @mobile
		        ))
		        OR
		        (LEN(@mobile) = 13 AND (
		            mobile = @mobile OR
		            SUBSTRING(@mobile, 4, 10) = mobile
		        ))
		)
		BEGIN
		    SELECT '1' AS ErrorCode
		        ,'Mobile No. ''' + @mobile + ''' is already used' AS Msg
		        ,@mobile AS id;
		
		    RETURN
		END

		--invalid referenceid
		IF NOT EXISTS(SELECT 'x' FROM dbo.CustomerMasterTemp AS CM(NOLOCK) WHERE customerId = @referenceId)      
		BEGIN      
			SELECT 3 ErrorCode,'You can not modify email once created, please contact JME!' Msg, NULL Id      
			RETURN      
		END  

		--invalid referenceid
		IF NOT EXISTS(SELECT 'x' FROM dbo.CustomerMasterTemp AS CM(NOLOCK) WHERE customerId = @referenceId AND createdBy IS NULL)      
		BEGIN      
			SELECT 4 ErrorCode,'Invalid data!' Msg, NULL Id      
			RETURN      
		END    
		
		--user already registered      
		IF EXISTS(SELECT 'x' FROM dbo.customerMasterTemp AS CM(NOLOCK) WHERE username = @username)      
		BEGIN      
			SELECT 5 ErrorCode,'User already registered!' Msg, NULL Id      
			RETURN      
		END      
		
		--Username already taken      
		IF EXISTS(SELECT 'x' FROM dbo.mobile_userRegistration(NOLOCK) AS MUR WHERE username = @username)      
		BEGIN      
			SELECT 6 ErrorCode,'Username already taken!' Msg, NULL Id      
			RETURN      
		END

		BEGIN TRAN
			SELECT @customerIdOld = OldCustomerId
			FROM CustomerMasterTemp (NOLOCK)
			WHERE customerId = @referenceId

			UPDATE CustomerMaster SET username = @username, customerPassword = dbo.FNAEncryptString(@password)
											,MODIFIEDBY = @username, MODIFIEDDate = GETDATE(), isActive = 'Y', txnPin = @txnPin
											,email = @username, customerEmail = @username, createdFrom = 'C', mobileUser='Y', referelCode=@referralCode
			WHERE customerId = @customerIdOld

			INSERT INTO dbo.mobile_userRegistration(      
								clientId,username,createdDate,IMEI,appVersion,phoneBrand,phoneOs,osVersion,deviceId,customerId,cmRegistrationId      
						)      
			SELECT      
								@clientId,@username,GETDATE(),@IMEI,@appVersion,@phoneBrand,@phoneOs,@osVersion,@fcmId,@customerIdOld,@uuid   

			DELETE FROM CustomerMasterTemp WHERE customerId = @referenceId
			COMMIT TRAN

		IF @@TRANCOUNT=0      
		BEGIN   
			--successful registered      
			SELECT 0 ErrorCode,'Thank you for your registration. Please complete your profile or you can skip and start sending money using IME London.' Msg, NULL Id
			--rowId, PdfName, AgreePdfPath, *       
			--FROM customerAgreeDocumentTbl        
			--WHERE targetObj = 'STAGING'      
			RETURN      
		END      
			  
	END
	IF @flag = 'sign-up-v2-new-cust'
	BEGIN
	    DECLARE @COUNTRYID INT;
	
	    -- User already registered
	    IF EXISTS(SELECT 'x' FROM dbo.customerMaster AS CM(NOLOCK) WHERE CM.userName = @username)
	    BEGIN
	        SELECT 1 AS ErrorCode, 'Customer already registered, with this username!' AS Msg, NULL AS Id;
	        RETURN;
	    END
	
	    IF EXISTS (
	        SELECT 1
	        FROM customermaster
	        WHERE 
	            (LEN(@mobile) = 10 AND (
	                mobile = @mobile OR
	                '+44' + mobile = @mobile
	            ))
	            OR
	            (LEN(@mobile) = 13 AND (
	                mobile = @mobile OR
	                SUBSTRING(@mobile, 4, 10) = mobile
	            ))
	    )
	    BEGIN
	        SELECT 9 AS ErrorCode,
	            'Mobile No. ''' + @mobile + ''' is already used' AS Msg,
	            @mobile AS Id;
	        RETURN;
	    END
	
	    DECLARE @mobileLen INT;
	    SET @mobileLen = LEN(@mobile);
	
	    IF((@mobile LIKE '+44%' AND @mobileLen != 13) OR (@mobile NOT LIKE '+44%' AND @mobileLen != 10))
	    BEGIN
	        SELECT 9 AS ErrorCode,
	            'The mobile number must be 13 digits long if it starts with ''+44'', or 10 digits if it does not' AS Msg,
	            @mobile AS Id;
	        RETURN;
	    END
	
	    -- User already registered in temp table
	    IF EXISTS(SELECT 'x' FROM dbo.customerMasterTemp AS CM(NOLOCK) WHERE username = @username)
	    BEGIN
	        SELECT 2 AS ErrorCode, 'User already registered!' AS Msg, NULL AS Id;
	        RETURN;
	    END
	
	    -- Username already taken
	    IF EXISTS(SELECT 'x' FROM dbo.mobile_userRegistration(NOLOCK) AS MUR WHERE username = @username)
	    BEGIN
	        SELECT 3 AS ErrorCode, 'Username already taken!' AS Msg, NULL AS Id;
	        RETURN;
	    END
	
	    IF @referralCode IS NOT NULL
	    BEGIN
	        IF NOT EXISTS (SELECT 1 FROM dbo.customerMaster(NOLOCK) WHERE membershipId = @referralCode)
	        AND NOT EXISTS (SELECT 1 FROM dbo.REFERRAL_AGENT_WISE(NOLOCK) WHERE REFERRAL_CODE = @referralCode)
	        BEGIN
	            SET @referralCode = NULL;
	        END
	    END
	
	    SELECT @COUNTRYID = countryId
	    FROM countryMaster (NOLOCK)
	    WHERE COUNTRYCODE = @nativeCountry;
	
	    IF @COUNTRYID IS NULL
	    BEGIN
	        SET @COUNTRYID = @nativeCountry;
	    END
	
	    BEGIN TRAN;
	    BEGIN TRY
	        INSERT INTO dbo.CustomerMasterTemp(
	            username, customerPassword, createdBy, createdDate, isActive, nativeCountry, referelCode, idNumber, mobile, email, customerEmail, mobileUser, fullName, dob, firstName, middleName, lastName1,
	            city, address, gender, isEmailVerified, zipCode, ADDITIONALADDRESS
	        )
	        SELECT
	            @username, dbo.FNAEncryptString(@password), @username, GETDATE(), 'Y', @COUNTRYID, @referralCode, @idNumber, @mobile, @username, @username, 'Y', @fullName, @dob, @FirstName, @MiddleName, @LastName,
	            @city, @address1, @gender, '0', @postalCode, @address2
	        
	        SET @customerId = SCOPE_IDENTITY();
	        
	        INSERT INTO dbo.mobile_userRegistration(
	            clientId, username, createdDate, appVersion, phoneBrand, phoneOs, osVersion, deviceId, customerId, cmRegistrationId
	        )
	        SELECT
	            @clientId, @username, GETDATE(), @appVersion, @phoneBrand, @phoneOs, @osVersion, @fcmId, @customerId, @uuid;
	        
	        IF @tempCustId IS NOT NULL
	        BEGIN
	            UPDATE TempUserRegister SET isActive = 'N' WHERE tempCustId = @tempCustId;
	        END
	
	        COMMIT TRAN;
	        SELECT 0 AS ErrorCode, 'Thank you for your registration. Please complete your profile or you can skip and start sending money using IME London.' AS Msg, NULL AS Id;
	    END TRY
	    BEGIN CATCH
	        ROLLBACK TRAN;
	        SELECT -1 AS ErrorCode, ERROR_MESSAGE() AS Msg, NULL AS Id;
	    END CATCH;
	END
	IF @flag = 'chk-valid-referralCode'
	BEGIN
		--IF NOT EXISTS (SELECT 1 FROM dbo.customerMaster(NOLOCK) WHERE membershipId = @referralCode)
		--	AND NOT EXISTS (SELECT 1 FROM dbo.REFERRAL_AGENT_WISE(NOLOCK) WHERE REFERRAL_CODE = @referralCode)      
		--BEGIN      
		--	SELECT 1 ErrorCode,'Invalid Referral Code!' Msg, @referralCode Id      
		--	RETURN       
		--END   
		--ELSE 
		BEGIN
			SELECT 0 ErrorCode,'SUCCESS' Msg, @referralCode Id      
		END
	END
	IF @flag = 'sign-up-temp-cust'
	BEGIN
		--IF EXISTS (
		--    SELECT 1
		--    FROM TempUserRegister
		--    WHERE 
		--        (LEN(@mobile) = 10 AND (
		--            mobile = @mobile OR
		--            '+44' + mobile = @mobile
		--        ))
		--        OR
		--        (LEN(@mobile) = 13 AND (
		--            mobile = @mobile OR
		--            SUBSTRING(@mobile, 4, 10) = mobile
		--        ))
		--)
		--BEGIN
		--    SELECT '9' AS ErrorCode
		--        ,'Mobile No. ''' + @mobile + ''' is already used' AS Msg
		--        ,@mobile AS id;
		
		--    RETURN
		--END

		--SET @mobileLen = LEN(@mobile)

		--IF((@mobile LIKE '+44%' AND @mobileLen != 13) OR (@mobile NOT LIKE '+44%' AND @mobileLen != 10))
		--BEGIN
		--	SELECT '9' AS ErrorCode
		--        ,'The mobile number must be 13 digits long if it starts with ''+44'', or 10 digits if it does not' AS Msg
		--        ,@mobile AS id;
		--	RETURN
		--END

		BEGIN TRAN  		
			INSERT INTO dbo.TempUserRegister(      
						tempCustId,fullName, mobile, email, appVersion, phoneBrand, phoneOs, fcmId, osVersion, createdDate, isOtpVerified, isActive
						)      
			SELECT   @tempCustId, @fullName, @mobile, @username, @appVersion, @phoneBrand, @phoneOs, @fcmId, @osVersion, GETDATE(), NULL, 'Y'

			SET @TempCustId = SCOPE_IDENTITY();

		COMMIT TRAN      
		IF @@TRANCOUNT=0      
		BEGIN      
			SELECT 0 ErrorCode,'Customer data inserted successfully.' Msg, @TempCustId Id
			
		END      
	END
	IF @flag = 'update-temp-cust'
	BEGIN
		UPDATE TempUserRegister SET email = @username WHERE tempCustId = @tempCustId

		SELECT 0 ErrorCode,'Customer data updated successfully.' Msg, @TempCustId Id
	END

	IF @flag = 'get-id'
	BEGIN
		SELECT TOP 1 tempCustId  FROM TempUserRegister ORDER BY tempCustId DESC
		RETURN
	END
END TRY      
BEGIN CATCH      
	IF @@TRANCOUNT<>0      
		ROLLBACK TRAN   
		   
	--Execption      
	SELECT 1 ErrorCode,dbo.GetMessage(@language,'9999')+CONVERT(VARCHAR,ERROR_LINE())+ERROR_MESSAGE() Msg, NULL Id      
	RETURN      
END CATCH