authen.public

Parameters

Name	Type	Mode
action_type	text	IN
user_name	text	IN (DEFAULT NULL)
email	text	IN (DEFAULT NULL)
res_code	integer	OUT
res_msg	text	OUT

Definition

declare
    v_user_name text;  
    v_prop_code text; 
    v_user_id int;  
    v_email text;
    v_prop_id int;
    v_props int[];
    v_email_type text;
    v_msg_id bigint;
    v_active boolean;
    is_activated boolean;
begin
    -- action_type: 'reset-password' or 'activate-user' 

    res_code := 0;
    
    if user_name is not null then
        v_user_name := split_part(user_name, '@', '1');
        v_prop_code := split_part(user_name, '@', '2');
        --raise notice 'user_name: %, prop_code: %', v_user_name, v_prop_code;
        if v_prop_code is null or v_prop_code = '' then
            res_code := 1; -- Invalid user_name or prop_code    
        else
            select u.id, u.user_name, coalesce($3, u.email), u.active 
            from users u 
            where u.user_name = v_user_name::citext 
            order by active desc, id limit 1
            into v_user_id, v_user_name, v_email, v_active; 
            
            raise notice 'user_id: %, email: %', v_user_id, v_email;
            
            v_prop_id := (select id from property p where p.code = v_prop_code::citext);
            
            if v_user_id is null or v_prop_id is null then 
                res_code := 1;
            end if;     
    
            v_props := array[v_prop_id]; 

            if (res_code = 0) and not exists(select * from user_property p where p.user_id = v_user_id and p.prop_id = v_prop_id) then
                res_code := 3; -- User not exists in specified property            
            elsif not v_active then
                res_code := 4; -- User is disabled    
            end if;
        end if;
    elsif (email is not null) then -- and user_name is null) then
        select u.id, u.user_name, u.email, u.active  
        from users u where lower(u.email) = lower($3) 
        order by active desc, id limit 1
        into v_user_id, v_user_name, v_email, v_active;

        if v_user_id is null then      
            res_code := 2; -- Invalid email address
        elsif not v_active then
            res_code := 4; -- User is disabled    
        else    
            v_props := (select array_agg(p.prop_id) from user_property p where p.user_id = v_user_id);
        end if;
    end if;

    /* Check user is activated */
    if (res_code = 0) then
        is_activated := (select "password" is not null from users where id = v_user_id);
    
        if (action_type = 'activate-user') and is_activated then 
            res_code := 5; -- User already activated
        elsif (action_type = 'reset-password') and not is_activated then
            res_code := 6; -- User does not activate
        end if;    
    end if;


    if (res_code = 0) then
        v_email_type := case action_type when 'activate-user' then 'activation' else 'password reset' end;

        v_msg_id := msg.create_message(action_type,
            json_build_object(
                'APP_NAME', 'Fromas Cloud',
                'USER_NAME', v_user_name,
                'ACTIVATE_URL', fn_gen_activate_url(action_type, v_user_id, v_props)  
            ),
            array[v_email]    
        );

        if v_msg_id > 0 then
            v_email := regexp_replace(v_email, '^(.)([^@]*)(.)@(.)([^\.]*)(.)\.(.*)$', '\1***\3@\4***\6.\7');
        else
            res_code := 7;
        end if;
    end if;

    res_msg = case res_code
        when 0 then format('The %s email has been sent to %s', v_email_type, v_email)
        when 1 then 'Invalid user name or property code'
        when 2 then 'Invalid email address'
        when 3 then format('User %s does not exists in property %s', v_user_name, v_prop_code)
        when 4 then 'Your user account is disabled, please contact administrator.'
        when 5 then 'Your user account already activated'
        when 6 then 'Your user account is not activated, please activate account first.'
        when 7 then format('Failed to send $s email.', v_email_type)
        else 'Unknown error'
    end;
end;

sp_request_password_reset

Parameters

Definition