authen.public

Parameters

Name	Type	Mode
prop_code	citext	IN
login_name	citext	IN
login_password	text	IN
device_name	text	IN (DEFAULT '')
renew_session	boolean	IN (DEFAULT false)
access_token	text	IN (DEFAULT '')

Definition

declare 
    res_code int;
    res_msg text;
    _prop_id int;
    _prop_code text;
    login_data jsonb;   
    _user_lang text;
    v_print_forms jsonb;
    v_pass_policy jsonb;
    v_sys_params jsonb;
    v_expire_date date;
    v_remaining_days int;
    v_license jsonb;
    v_expired bool;
    v_license_msg text; 
    v_lang text;
begin
    select id, code from property where code = $1
    into _prop_id, _prop_code; 

    if _prop_id <> 1 then
        v_print_forms := (sp_get_printform_list_js($1)->0->'print_form')::jsonb;
        v_pass_policy := fn_get_password_json(_prop_id);
        v_sys_params  := fn_prop_params_js(_prop_id);
    end if;
     
    with sys_config as (
      select 
         v_print_forms as printform_info,
         FN_GET_CONFIG(_prop_id, 'SESSION_TIMEOUT', '@SYSTEM') as session_timeout,
         v_pass_policy as pass_policy,
         v_sys_params as system_params
    ), login_result as (
      select u.res_code as result_code
            , u.res_msg as result_msg
            --, $6 as access_token
            , log_id
            , user_id
            , user_name
            , user_group_id
            , user_group_name
            , user_group_type
            , full_name
            , job_position
            , email 
            , mobile
            , telephone
            , (select lang from users A where A.id = u.user_id) as "language"
            , current_shift
            , logintime as login_time
            , image_url as profile_pic
            , fn_dashboard_url(_prop_id, user_id) as dashboard_url
            , prop_id
            , _prop_code as prop_code
            , prop_name
            , prop_group_id
            , prop_group_name
            , permissions as user_permission
            , row_to_json(sys_config) as system_config
-- {"id": "26175", "iat": 1755079071, "pid": 56, "ugt": "S", "uid": 148, "name": "NS-A", "pgid": null, "pcode": "tarnnam"}
            , sys.jwt_sign(
                  jsonb_build_object(
                    'id'   , u.log_id,
                    'uid'  , u.user_id, 
                    'pid'  , u.prop_id,
                    'pgid' , u.prop_group_id, 
                    'pcode', lower(_prop_code), 
                    'name' , u.user_name,
                    'ugt'  , u.user_group_type
                  ),    
                  fn_get_config(null, 'jwt-secret')
              ) as access_token
      from fn_login($1, login_name, login_password, device_name, renew_session) u, sys_config
    )
    select case result_code
             when 0 then row_to_json(login_result)::jsonb 
--                case _prop_id 
--                     when 1 then
--                        jsonb_build_object( 
--                     else  
--                        row_to_json(login_result)::jsonb 
--                  end
--                r
             else jsonb_build_object('result_code', result_code, 'result_msg', result_msg)
           end 
    from login_result 
    into login_data;

    if (_prop_id <> 1) and (login_data->>'result_code' = '0') then
        v_expire_date := sys.get_subscription_expire(_prop_id);
        v_remaining_days := (v_expire_date - current_date);
        v_expired := current_date > v_expire_date; 
        v_lang := coalesce(login_data->>'language', v_sys_params->'SYSTEM'->>'LANGUAGE', 'EN');
        
        if login_data->>'language' is null then
            login_data := login_data || jsonb_build_object('language', v_lang);
        end if; 
                          
        --v_lang := (Select lang from users where id = (login_data->>'user_id')::int);
        v_license_msg := case
           when v_remaining_days < -7 then sys_msg('82003', null, v_lang) -- Expired after grace period 
           when v_remaining_days < 0  then sys_msg('82002', null, v_lang, to_char(v_expire_date + interval'7 days', 'DD-MM-YYYY')) -- Expired before grace period 
           when v_remaining_days < 7  then sys_msg('82001', null, v_lang, to_char(v_expire_date, 'DD-MM-YYYY')) -- 7 days before expiration date
           else '' -- Normal
        end;

        v_license := jsonb_build_object(
            'active',          v_remaining_days >= -7,
            'expire_date',     v_expire_date,
            'is_expire',       v_expired,
            'warning',         v_license_msg <> '',
            'warning_message', v_license_msg,
            'warning_title',   case when v_license_msg <> '' then sys_msg('82004', null, v_lang) else '' end,
            'renew_link_url',  '',--case when v_license_msg <> '' then 'https://newsoft.co.th' else '' end,
            'renew_link_text', '' /*case 
                                   when v_expired then sys_msg('82006', null, v_lang)     
                                   when v_license_msg <> '' then sys_msg('82005', null, v_lang)   
                                   else '' 
                               end  */                   
        );
        login_data := login_data || jsonb_build_object('license_info', v_license);
    end if;

    if (login_data->>'result_code' = '0') then
       update work_log w 
       set login_token = login_data->>'access_token' --$6
       where w.id = (login_data->>'log_id')::bigint; 
    end if;
    
    return login_data;
end

sp_login

Parameters

Definition