authen.public

Parameters

Name	Type	Mode
prop_code	citext	IN
login_name	citext	IN
login_password	text	IN
device_name	text	IN (DEFAULT NULL)
renew_session	boolean	IN (DEFAULT false)
res_code	smallint	OUT
res_msg	text	OUT
log_id	bigint	OUT
user_id	integer	OUT
user_name	citext	OUT
user_group_id	integer	OUT
user_group_name	text	OUT
user_group_type	character	OUT
full_name	citext	OUT
job_position	text	OUT
email	text	OUT
mobile	text	OUT
telephone	text	OUT
current_shift	character	OUT
logintime	timestamp with time zone	OUT
prop_id	integer	OUT
prop_name	citext	OUT
prop_group_id	integer	OUT
prop_group_name	text	OUT
permissions	json[]	OUT
image_url	text	OUT

Definition

DECLARE
  last_log_id int;
  v_prop_id ALIAS FOR prop_id;
  v_user_id ALIAS FOR user_id;
  system_date date;
  v_login_time timestamptz;
  v_user_lang text;
BEGIN
  res_code = 0;

  v_prop_id = (select id from property p where p.code = prop_code and (p.enabled or p.code = 'NEWSOFT'));

  if (v_prop_id is null) then
            res_code = 1; -- Invalid property code
  end if;

  if (res_code = 0) then
      select case
                when (u.id is null) then 6 -- Invalid user name
                when (u.password is null) then 5 -- User account not activate
                --when (u.password is null and login_password = u.password) then 0
                when (u.password <> crypt(login_password, u.password)) then 3 -- Invalid password
                when (not u.active) then 4  -- User account is disabled
                else 0
             end, up.user_id
      from users u inner join user_property up on up.user_id = u.id
      where upper(u.user_name) = upper(login_name)  and up.prop_id = v_prop_id
      into res_code, v_user_id;

      --if (coalesce(res_code,0) = 0) and (v_user_id is null) then
            if (v_user_id is null) then
                    res_code = 2;  -- Invalid user name
            end if;
  end if;

  if (res_code = 0) then
        system_date = coalesce(fn_system_date(prop_id), current_date);
        --online_shift = (select param_value from fn_prop_params(prop_id, 'SYSTEM', 'ONLINE_SHIFT'));

        select u.user_name, u.user_group_id, u.user_group_name, u.user_group_type, u.full_name, u.job_position,
            u.email, u.mobile, u.telephone, u.work_shift, u.work_log_id, u.prop_name, u.prop_group_id,
            u.prop_group_name, u.permissions, u.image_url
        from fn_get_user_info(prop_id, user_id) u
        into user_name, user_group_id, user_group_name, user_group_type, full_name, job_position,
            email, mobile, telephone, current_shift, last_log_id, prop_name, prop_group_id, prop_group_name,
            permissions, image_url;

        --if (last_log_id is not null) THEN
        -- Log out all other sessions
           UPDATE work_log w
           SET logout_time = current_timestamp, last_seen = current_timestamp
           WHERE w.prop_id = v_prop_id AND w.user_id = v_user_id AND v_prop_id <> 1;

           UPDATE user_property w
           SET work_log_id = null
           WHERE w.prop_id = v_prop_id AND w.user_id = v_user_id;
            --PERFORM fn_logout(last_log_id);
        --end if;

        insert into work_log (prop_id, user_id, user_name, device_name, login_date, shift, reactivate)
        values (prop_id, user_id, user_name, device_name, system_date, current_shift, renew_session)
        returning id, login_time into log_id, logintime;
        --login_time = v_login_time;

        update user_property up
        set work_log_id = log_id, work_shift = current_shift
        where up.user_id = v_user_id and up.prop_id = v_prop_id AND v_prop_id <> 1;

        Select array_agg(json_build_object('code',p.code, 'name',p.name))
        from fn_get_user_permissions(prop_id, user_id) p
        where is_granted = true
        into permissions;

      /* clear old record locking */
     --delete from recordlock where computer = :computer and userno = :userno;
      --  perform sp_system_notify('Login success', format(E'User: %s\nProperty: %s', user_name, upper(prop_code)));
  end if;

  res_msg = case res_code
                when 0 then 'Success'
                when 1 then 'Invalid property code'
                when 2 then 'Invalid user name or password'
                when 3 then 'Invalid user name or password'
                when 4 then 'Your user account is disabled'
                when 5 then 'Please activate your user account'
                when 6 then 'Invalid user name or password'
            end;

  if res_code = 0 then
     PERFORM sys.log_info('Login', format(E'User: %s, Prop: %s, Success', user_name, upper(prop_code)));
     --perform sp_system_notify('['||upper(prop_code)||'] Login success', format(E'User: %s', user_name));
  else
     --PERFORM sys.log_warn(format(E'User: %s, Prop: %s, Failed:', user_name, upper(prop_code), res_msg), 'Login');
     PERFORM sys.log_warn('Login', format(E'User: %s, Prop: %s, %s', login_name, upper(prop_code), res_msg));
     --perform sp_system_notify('['||upper(prop_code)||'] Login failed, '||res_msg, format(E'User: %s', login_name));
  end if;

END

fn_login

Parameters

Definition