You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
207 lines
6.9 KiB
207 lines
6.9 KiB
|
|
|
|
GO
|
|
use FastMoneyPro_Remit;
|
|
|
|
GO
|
|
CREATE OR ALTER PROC PROC_REMIT_INBOUND_LOGIN
|
|
(
|
|
@Flag VARCHAR(20)
|
|
, @UserName VARCHAR(80) = NULL
|
|
, @Password VARCHAR(120) = NULL
|
|
, @AgentId INT = NULL
|
|
, @IpAddress VARCHAR(20) = NULL
|
|
, @SessionIdentifier VARCHAR(40) = NULL
|
|
, @ExpiresIn INT = NULL
|
|
)
|
|
AS;
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
BEGIN TRY
|
|
IF @Flag = 'LOGIN'
|
|
BEGIN
|
|
DECLARE @ErrorMsg VARCHAR(MAX) = NULL
|
|
DECLARE @UserId INT, @UserActive CHAR(1), @LoginPwd VARCHAR(120), @LoginUserAgtId INT, @EmpId INT, @IsLocked CHAR(1), @LoginTime DATETIME
|
|
, @LastLoginTs DATETIME, @AgentActiveStatus CHAR(1), @ForceChangePwd CHAR(1), @InvalidPwdCount TINYINT, @LockUserDays INT
|
|
|
|
DECLARE @AttemptsCount INT
|
|
SELECT TOP 1 @AttemptsCount = loginAttemptCount
|
|
, @LockUserDays = ISNULL(lockUserDays,30)
|
|
FROM passwordFormat WITH(NOLOCK)
|
|
|
|
SELECT
|
|
@UserId = userId
|
|
,@UserActive = ISNULL(au.isActive, 'N')
|
|
,@LoginPwd = pwd
|
|
,@LoginUserAgtId = au.agentId
|
|
,@EmpId = employeeId
|
|
,@IsLocked = ISNULL(isLocked, 'N')
|
|
,@LoginTime = loginTime
|
|
,@LastLoginTs = lastLoginTs
|
|
,@AgentActiveStatus = ISNULL(am.isActive, 'N')
|
|
,@ForceChangePwd = au.forceChangePwd
|
|
,@InvalidPwdCount = ISNULL(AU.wrongPwdCount, 0)
|
|
FROM applicationUsers au WITH(NOLOCK)
|
|
INNER JOIN agentMaster am WITH(NOLOCK) ON au.agentId = am.agentId
|
|
WHERE userName = @UserName and ISNULL(au.isDeleted, 'N') = 'N'
|
|
|
|
IF ISNULL(@InvalidPwdCount, 0) > @AttemptsCount
|
|
BEGIN
|
|
UPDATE applicationUsers SET
|
|
wrongPwdCount = ISNULL(wrongPwdCount, 0) + 1
|
|
WHERE userId = @userId
|
|
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'You are locked due to Continious Invalid login attempts. Please, contact your administrator.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @UserId IS NULL
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Login fails, Incorrect user name or password.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @UserActive = 'N'
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'User is not active.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @AgentActiveStatus = 'N'
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Your account is blocked.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF (@LoginPwd <> ISNULL(dbo.FNAEncryptString(@Password), ''))
|
|
BEGIN
|
|
UPDATE applicationUsers SET
|
|
wrongPwdCount = ISNULL(wrongPwdCount, 0) + 1
|
|
WHERE userId = @userId
|
|
|
|
SET @InvalidPwdCount += 1
|
|
IF @InvalidPwdCount < @AttemptsCount
|
|
BEGIN
|
|
SET @ErrorMsg = 'Login fails, Incorrect user name or password, attempts left: ' + CAST((@AttemptsCount - @InvalidPwdCount) AS VARCHAR)
|
|
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Your account is blocked.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
END
|
|
IF ISNULL(@InvalidPwdCount, 0) >= @AttemptsCount
|
|
BEGIN
|
|
UPDATE applicationUsers SET
|
|
isLocked = 'Y'
|
|
,lastLoginTs = GETDATE()
|
|
,wrongPwdCount = ISNULL(wrongPwdCount, 0) + 1
|
|
WHERE userId = @userId
|
|
|
|
INSERT INTO userLockHistory(username, lockReason, createdBy, createdDate)
|
|
SELECT @userName, 'Your account has been locked due to Invalid login attempts', 'system', GETDATE()
|
|
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Your account is blocked.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF (@AgentId <> ISNULL(@LoginUserAgtId, -1))
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Agent Id.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF(@isLocked = 'Y')
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Your account has been locked. Please, contact your administrator.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF(DATEDIFF(DAY, @LastLoginTs, GETDATE()) >= @LockUserDays)
|
|
BEGIN
|
|
UPDATE applicationUsers SET
|
|
isLocked = 'Y'
|
|
,lastLoginTs = GETDATE()
|
|
WHERE userId = @userId
|
|
|
|
INSERT INTO userLockHistory(username, lockReason, createdBy, createdDate)
|
|
SELECT @userName, 'Your account has been locked due to not login for fix period.', 'system', GETDATE()
|
|
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Your account has been locked due to not login for fix period.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
UPDATE applicationUsers SET
|
|
lastLoginTs = GETDATE()
|
|
WHERE userId = @userId
|
|
|
|
UPDATE TBL_USER_SESSION SET IsExpired = 1 WHERE UserId = @userId
|
|
|
|
INSERT INTO TBL_USER_SESSION(UserId, SessionIdentifier, IpAddress, ExpiryDate)
|
|
SELECT @userId, @SessionIdentifier, @IpAddress, DATEADD(MINUTE, @ExpiresIn, GETDATE())
|
|
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 100, @ErrorMessage = 'Success.', @Id = @userId, @Extra = NULL;
|
|
END
|
|
ELSE IF @Flag = 'APPID'
|
|
BEGIN
|
|
DECLARE @AppId VARCHAR(50), @SecretKey VARCHAR(50)
|
|
SELECT @AppId = AppId, @SecretKey = APISecretKey
|
|
FROM TBL_API_SECRET_KEY AK(NOLOCK)
|
|
INNER JOIN applicationUsers AU(NOLOCK) ON AU.userId = AK.UserId
|
|
WHERE au.UserName = @UserName
|
|
AND Status = 1
|
|
|
|
IF ISNULL(@AppId, '') = ''
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid auth key, contact HO.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 100, @ErrorMessage = 'Success.', @Id = @AppId, @Extra = @SecretKey;
|
|
END
|
|
ELSE IF @Flag = 'CHECK-AUTH'
|
|
BEGIN
|
|
SELECT
|
|
@UserId = userId
|
|
,@UserActive = ISNULL(au.isActive, 'N')
|
|
,@LoginUserAgtId = au.agentId
|
|
,@EmpId = employeeId
|
|
,@IsLocked = ISNULL(isLocked, 'N')
|
|
,@AgentActiveStatus = ISNULL(am.isActive, 'N')
|
|
FROM applicationUsers au WITH(NOLOCK)
|
|
INNER JOIN agentMaster am WITH(NOLOCK) ON au.agentId = am.agentId
|
|
WHERE userName = @UserName and ISNULL(au.isDeleted, 'N') = 'N'
|
|
|
|
IF @UserId IS NULL
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Login fails, Incorrect user name or password.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @UserActive = 'N'
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'User is not active.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF @AgentActiveStatus = 'N'
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Your account is blocked.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF (@AgentId <> ISNULL(@LoginUserAgtId, -1))
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid Agent Id.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
IF(@isLocked = 'Y')
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Your account has been locked. Please, contact your administrator.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
IF NOT EXISTS(SELECT 'X' FROM TBL_USER_SESSION (NOLOCK) WHERE UserId = @UserId AND IsExpired = 0 AND SessionIdentifier = @SessionIdentifier)
|
|
BEGIN
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 101, @ErrorMessage = 'Invalid or inactive Session.', @Id = NULL;
|
|
RETURN;
|
|
END
|
|
|
|
EXEC SW_PROC_ERROR_HANDLER @ErrorCode = 100, @ErrorMessage = 'Success.', @Id = @userId, @Extra = NULL;
|
|
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
|