dbo.user_login_test

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE OR ALTER PROCEDURE dbo.user_login_test
@username varchar(60),
@password varchar(100),
@expiration_period tinyint,
@qty_password tinyint,
@session_timeout tinyint,
@simul_login tinyint,
@dbaction varchar(10),
@NewPassword varchar(100)

AS


BEGIN
       SET NOCOUNT ON;


/*STORED PROCEDURE DE LOGIN DE USUARIOS Y CAMBIO DE CLAVES
TIENE DOS ACCIONES POSIBLES
1--> LOGIN
2--> CHANGEPWD
3--> RETRPWD
4--> LOGOUT

PARAMETROS

@username = NOMBRE DE USUARIO
@password = CLAVE ACTUAL DEL USUARIO
@expiration_period = CANTIDAD DE DÍAS DE VIGENCIA DE LA CLAVE
@qty_password = CANTIDAD DE CLAVES HISTÓRICAS QUE SE MANEJAN PARA NO PODER REPETIR LAS MISMAS,
@session_timeout = TIEMPO DE DURACIÓN DE LA SESSION PARA CHEQUEAR LOS LOGIN SIMULTANEOS,
@simul_login = 0 PARA PERMITIR SIMULTANEIDAD / 1 PARA NO PERMITIR SIMULTANEIDAD 
@dbaction = LOGIN / CHANGEPWD
@NewPassword = LA NUEVA CLAVE DEL USUARIO EN EL CASO DE SER UN CHANGEPWD (EN EL CASO DE LOGIN ESTE VALOR DEBE SER NULL)


*/


DECLARE @UserID int
DECLARE @EncPassword varchar(100)
DECLARE @passwordDB varchar(100)
DECLARE @usernamedDB varchar(60)
DECLARE @VDO3_PWD varchar(90)
DECLARE @id_enc_pwdDB int
DECLARE @enc_pwdDB varchar(90)
DECLARE @exp_dateDB datetime
DECLARE @status INT
DECLARE @statusMessage varchar(100)
DECLARE @first_name varchar(150)
DECLARE @last_name varchar(150)
DECLARE @email varchar(150)
DECLARE @login_state varchar(1)
DECLARE @UserSessionTimeoutDB DATETIME
DECLARE @ActivePWD bit


IF (UPPER(@dbaction) = 'LOGOUT')
BEGIN


 set @statusMessage = 'Usuario deslogueado'
    set @status = 0
    update users set login_state='' where username = @username 

    set @login_state = ''

END


/* ENCRIPTAMOS EL PASSWORD ENVIADO*/
BEGIN
       EXEC VDO3_CRYPT_SP @PasswordStr = @password,@SaltStr = 'Los humanos llevan tiempo afectando el medio ambiente, pero recientemente se ha producido una rápida propagación mundial de nuevos materiales como el aluminio, el concreto y los plásticos, que están dejando su huella en los sedimentos", dijo en el estudio el profesor Colin Waters, del Instituto Geológico Británico',@IterationCount = 14,@Outputbytes = 90,@VDO3_PWD = @VDO3_PWD OUTPUT 
       SET @EncPassword = @VDO3_PWD

       if ((@NewPassword is not null AND upper(@dbaction) = 'CHANGEPWD') OR (upper(@dbaction) = 'RETRPWD'))
       BEGIN
             EXEC VDO3_CRYPT_SP @PasswordStr = @NewPassword,@SaltStr = 'Los humanos llevan tiempo afectando el medio ambiente, pero recientemente se ha producido una rápida propagación mundial de nuevos materiales como el aluminio, el concreto y los plásticos, que están dejando su huella en los sedimentos", dijo en el estudio el profesor Colin Waters, del Instituto Geológico Británico',@IterationCount = 14,@Outputbytes = 90,@VDO3_PWD = @VDO3_PWD OUTPUT 
             SET @NewPassword = @VDO3_PWD
       END

END
/*
select @password as Spassword ,@EncPassword as EncPassword,@NewPassword as NewPassword,@VDO3_PWD as VDO3_PWD


 select @username AS US ,@password AS PW,@expiration_period AS EP,@qty_password AS QP,@session_timeout AS ST,@simul_login AS SL,@dbaction AS DA,@NewPassword AS NP
 */
IF (UPPER(@dbaction) = 'RETRPWD')
 BEGIN
  SELECT   top 1  @UserID = users.user_id, 
  @usernamedDB = users.username, 
  @passwordDB = users.password,
  @id_enc_pwdDB = '',
  @enc_pwdDB = '',
  @exp_dateDB = '',
  @first_name = users.first_name,
  @last_name = users.last_name,
  @email = users.email,
  @UserSessionTimeoutDB = DATEADD(mi,+(@session_timeout),users.last_access),
  @ActivePWD  = 0
  FROM  users 
  where username = @username
  and (users.active_user = 'Y' OR users.active_user = 1)
 END
ELSE
 BEGIN
 
  SELECT   top 1  @UserID = users.user_id, 
  @usernamedDB = users.username, 
  @passwordDB = users.password,
  @id_enc_pwdDB = users_enc_pwd.id_enc_pwd,
  @enc_pwdDB = users_enc_pwd.enc_pwd,
  @exp_dateDB = users_enc_pwd.expiration_date,
  @first_name = users.first_name,
  @last_name = users.last_name,
  @email = users.email,
  @UserSessionTimeoutDB = DATEADD(mi,+(@session_timeout),users.last_access),
  @ActivePWD  = users_enc_pwd.active

  FROM            users LEFT JOIN
  users_enc_pwd ON  (users.user_id = users_enc_pwd.user_id and users_enc_pwd.active = 1)
  /*
   join users.user_id = (select max(users_enc_pwd) where id = users.user_id
   */
  /*users_enc_pwd ON  (users.user_id = users_enc_pwd.user_id /*and users_enc_pwd.active = 1*/)*/
  where username = @username
  /*
   and users.login_expiration > getdate()
   and users_enc_pwd.expiration_date > getdate()
   */
  and (password = @password or users_enc_pwd.enc_pwd = @EncPassword)
  and (users.active_user = 'Y' OR users.active_user = 1)
  order by users_enc_pwd.created desc
 END


 
if (@UserID is null) /* NO SE ENCONTRÓ USUARIO O CONTRASEÑA*/
BEGIN
       set @statusMessage = 'Usuario o contraseña no válido'
       set @status = 2
END

select @UserID as BBBB,@passwordDB AS dbb,@password AS PWD
select @password as Spassword ,@EncPassword as EncPassword,@NewPassword as NewPassword,@VDO3_PWD as VDO3_PWD



if (@passwordDB = @password) /* CLAVE NO ENCRIPTADA DARLA POR VENCIDA Y GENERAR CAMBIO DE LA MISMA*/
BEGIN
       set @statusMessage = 'Clave expirada. Por favor cambie la misma'
       set @status = 1

       /* update users set login_expiration = getdate()-1000,login_state='',last_login=GETDATE(),last_access=GETDATE() where user_id = @UserID */
      /* begin
                   update users set password = '@encripted',login_expiration = getdate() -0.221 ,login_state=''
                   /*
                   ,last_access= getdate()-1,last_login=getdate()-1 
                   */
                   where user_id = @UserID
             end
             begin
                   INSERT INTO users_enc_pwd (user_id, enc_pwd, created, expiration_date,active)
                   VALUES     (@UserID,@EncPassword,getdate(),getdate()+@expiration_period,0)
             end*/

END


if (@passwordDB = '@encripted' and UPPER(@dbaction) <> 'RETRPWD' ) /* CLAVE y USUARIO OK */
BEGIN
       if (@simul_login = 1) /* NO SE PERMITEN SESIONES SIMULTANEAS*/
             BEGIN
             /* SI EL VALOR DE SESSION TIMEOUT DESDE EL LAST ACCES ES MENOR AL GETDATE() LO PASAMOS COMO LOGUEADO*/
             if (@UserSessionTimeoutDB < getdate())
                    BEGIN
                           set @statusMessage = 'Usuario y clave OK'
                           set @status = 0
                           update users set login_state='L',last_login=GETDATE(),last_access=GETDATE() where user_id = @UserID
                           set @login_state = 'L'
                    END
             ELSE
                    BEGIN
                           set @statusMessage = 'Login simultáneo no permitido'
                           set @status = 3
                           set @login_state = ''
                    END
             END
       ELSE
             BEGIN
                    set @statusMessage = 'Usuario y clave OK'
                           set @status = 0
                           update users set login_state='L',last_login=GETDATE(),last_access=GETDATE() where user_id = @UserID
                           set @login_state = 'L'
             END
END

if (@passwordDB = '@encripted' AND @exp_dateDB < GETDATE() and UPPER(@dbaction) <> 'RETRPWD') /* CLAVE y USUARIO OK PERO EXPIRADO */
BEGIN
       set @statusMessage = 'Clave expirada. Por favor cambie la misma'
       set @status = 1
       /*
              update users set login_expiration = getdate()-1000,login_state='',last_login=GETDATE(),last_access=GETDATE() where user_id = @UserID
              */
END


if ((upper(@dbaction) = 'CHANGEPWD' and (@status = 1 or @status = 0)) OR (UPPER(@dbaction) = 'RETRPWD')) /*SI PEDIMOS UN CAMBIO DE CLAVE Y EL LOGIN ES OK O EXPIRADO O ES UN RECUPERO DE CLAVE*/
BEGIN
             
             /* AVERIGUAMOS SI LA CLAVE USADA SE REPITE ENTRE LAS ÚLTIMAS DEL PARAMETRO QTY USADAS */
             /*
                          SET ROWCOUNT @qty_password
                          */
             DECLARE @CanChangePWD int = 1
             DECLARE @OLDenc_pwd VARCHAR(100), @OLDcreated DATETIME,@OLDexpiration_date DATETIME

             DECLARE VDO3ChangeLoginPWD Cursor
             FOR
                    select top (@qty_password) enc_pwd,created,expiration_date  from users_enc_pwd where user_id = @UserID 
                    /*
                                        and enc_pwd = @NewPassword 
                                        */
                    order by users_enc_pwd.created desc
             Open VDO3ChangeLoginPWD

             Fetch NEXT FROM VDO3ChangeLoginPWD INTO @OLDenc_pwd,@OLDcreated,@OLDexpiration_date
             While (@@FETCH_STATUS <> -1)
             BEGIN
                    /* COMPARO EL PWD ENCRIPTADO CON LOS ÚTIMOS N DEL USUARIO. SI COINCIDE NO LO PUEDE CAMBIAR*/
                    if(@NewPassword = @OLDenc_pwd)
                    BEGIN
                           set @CanChangePWD = 0
                    END
             Fetch NEXT FROM VDO3ChangeLoginPWD INTO @OLDenc_pwd,@OLDcreated,@OLDexpiration_date
             END

             CLOSE VDO3ChangeLoginPWD
             DEALLOCATE VDO3ChangeLoginPWD
             DECLARE @safelastacces datetime /* al cambiar los PWD dejo como vencidas las sesiones por las dudas */
             SELECT @safelastacces= DATEADD(mi,-(@session_timeout + (10*10/100)),GETDATE())

             if (@CanChangePWD = 1 )
             BEGIN
                    begin
                           update users set password = '@encripted',login_expiration = getdate() + @expiration_period,login_state='',last_access= @safelastacces,last_login=@safelastacces 
                           where user_id = @UserID
                    end
                    begin
                           update users_enc_pwd set active = 'false' where user_id = @UserID
                    end
                    begin
                           INSERT INTO users_enc_pwd (user_id, enc_pwd, created, expiration_date,active)
                           VALUES     (@UserID,@NewPassword,getdate(),getdate()+@expiration_period,1)
                    end
                    SET @status = 20
                    SET @statusMessage = 'Clave modificada en forma exitosa'
                    set @first_name = ''
                    set @last_name = ''
                    set @email = ''
                    set @login_state = ''
             END

             if (@@ROWCOUNT = 0)
             BEGIN
                    SET @status = 21
                    SET @statusMessage = 'Clave ya utilizada. No se puede cambiar la clave. Intente nuevamente'
             END




END
       /* devolvemos los datos */
       SELECT   @UserID as user_id, @usernamedDB as username, @first_name as first_name, @last_name as last_name, @email as email, @login_state as login_state, 
       GETDATE() as last_login,GETDATE() as last_access,@exp_dateDB as login_expiration, @status as status, @statusMessage as status_message,@UserSessionTimeoutDB as UserSessionTimeoutDB

END

GO