
ALTER PROCEDURE [sSecurity].[pMobValidateUser]

    @rOrgId int = 0,
    @rUserTitle nvarchar(100)='',
    @rUserMobileNumber nvarchar(50)=''
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
		DECLARE @rUserSourceId bigint=0
		DECLARE @rUserId bigint=0
		DECLARE @rUserSourceTypeId smallint=0
		DECLARE @newpwd VARCHAR(100)
		SELECT @rUserId=Id,@rUserSourceId = SourceId,@rUserSourceTypeId=SourceTypeId FROM sSecurity.tUser WHERE UPPER(LoginTitle) = UPPER(@rUserTitle) AND OrgId = @rOrgId
		IF(@rUserId = 0 AND @rUserSourceTypeId=0)
		BEGIN
			SELECT -11 AS Result, 'User does not exist' AS [Message], '' AS OTP
			Return
		END
		IF(@rUserSourceTypeId = 95)
		BEGIN
			IF EXISTS (SELECT 1 FROM sHrm.tEmployeeCommunications WHERE MobileNumber = @rUserMobileNumber and EmployeeId = @rUserSourceId)
			BEGIN
				IF EXISTS (SELECT 1 FROM sSecurity.tUser WHERE Id = @rUserId AND OrgId = @rOrgId)
				BEGIN				--Changes for Mantis Id : 33613 4 digit OTP --changes for Mantis Id: 0035793 get 6 digit OTP
					EXEC [sSecurity].[pNumericOTPGenerate] @len=4, @min=48, @range=9, @exclude='', @output=@newpwd OUT
					UPDATE sSecurity.tUser
					SET	TempPasswordCode=@newpwd,CreatedDateTime=sCommon.fGetDate(),OTPExpiryDate=DATEADD(SECOND,120,GETDATE())				
					WHERE Id = @rUserId AND OrgId = @rOrgId

					SELECT 1 AS Result,'' AS [Message],TempPasswordCode AS OTP FROM sSecurity.tUser WHERE Id = @rUserId AND OrgId = @rOrgId					
				END
				ELSE
				BEGIN
					SELECT -101 AS Result,'User does not exist' AS [Message],'' AS OTP
				END
			END
			ELSE
			BEGIN
				SELECT -102 AS Result,'Mobile number is wrong' AS [Message],'' AS OTP
			END	
		END
		IF(@rUserSourceTypeId = 2003)
		BEGIN
			IF EXISTS (SELECT 1 FROM sStudent.tStudentCommunication WHERE ParentContactNo = @rUserMobileNumber and StudentId = @rUserSourceId)
			BEGIN
				IF EXISTS (SELECT 1 FROM sSecurity.tUser WHERE Id = @rUserId AND OrgId = @rOrgId)
				BEGIN --Changes for Mantis Id : 33613 4 digit OTP --changes for Mantis Id: 0035793 get 6 digit OTP
					EXEC [sSecurity].[pNumericOTPGenerate] @len=4, @min=48, @range=9, @exclude='', @output=@newpwd OUT
					UPDATE sSecurity.tUser
					SET	TempPasswordCode=@newpwd,CreatedDateTime=sCommon.fGetDate(),OTPExpiryDate=DATEADD(SECOND,120,GETDATE())				
					WHERE Id = @rUserId AND OrgId = @rOrgId
					SELECT 1 AS Result,'' AS [Message],TempPasswordCode AS OTP FROM sSecurity.tUser WHERE Id = @rUserId  AND OrgId = @rOrgId					
				END
				ELSE
				BEGIN
					SELECT -101 AS Result,'User does not exist' AS [Message],'' AS OTP
				END
			END
			ELSE
			BEGIN
				SELECT -102 AS Result,'Mobile number is wrong' AS [Message],'' AS OTP
			END		
		END
END
