get_act_lists

create or replace function expert.get_act_lists(aregion_code integer, astatus expert.act_list_status[]
, aold_id character varying[], amedical_organization_codes character varying[], alist_number character varying[]
, anid uuid[], auser_id character varying[], aassigned_expert_id bigint[], alist_name character varying
, create_date_begin timestamp with time zone, create_date_end timestamp with time zone, upload_date_begin timestamp with time zone
, upload_date_end timestamp with time zone, sign_date_begin date, sign_date_end date, send_date_begin date, send_date_end date
, print_date_begin date, print_date_end date, amed_work_type_name character varying[], azpmd_id uuid[]
, order_column text, order_direction text, alimit integer, aoffset integer)
    returns TABLE(id uuid, list_number character varying, list_name character varying, date_create timestamp with time zone
        , region_code text, medical_organization_name text, medical_organization_code text, user_id character varying
        , date_upload timestamp with time zone, zpmd_id uuid, has_cover_letter_date boolean, date_print date
        , date_send date, date_sign date, old_id text, theme_category_id uuid, med_work_type_id bigint
        , med_work_type_name character varying, assigned_expert_id bigint, status expert.act_list_status
        , region expert.region)
    parallel safe
    language plpgsql
as
$$
declare
    _order_by text = coalesce(case when  regexp_split_to_array(order_column, ',') <@ array['region_code','medical_organization_code'
        ,'id','list_number','status', 'old_id','user_id','list_name','date_create','date_upload','date_sign','date_send','date_print', 'med_work_type_name']
                                       then ' order by ' || order_column || ' ' || order_direction || ' , id '|| format('limit %L offset %L', alimit, aoffset) end, '');
    _where text = concat_ws(' and '
        , case when cardinality(anid) > 0 then format('al.id = any(%L)', anid) end
        , case when cardinality(alist_number) > 0 then format('al.list_number = any(%L)', alist_number) end
        , case when cardinality(astatus) > 0 then format('al.status = any(%L)', astatus) end
        , case when cardinality(aold_id) > 0 then format('al.old_id = any(%L)', aold_id) end
        , case when cardinality(auser_id) > 0 then format('al.user_id = any(%L)', auser_id) end
        , case when cardinality(azpmd_id) > 0 then format('al.zpmd_id = any(%L)', azpmd_id) end
        , case when cardinality(aassigned_expert_id) > 0 then format('al.assigned_expert_id = any(%L)',aassigned_expert_id) end
        , case when alist_name is not null then format('al.list_name like ''%%%s%%''',alist_name) end
        , case when create_date_begin is not null then format('al.date_create >= %L',create_date_begin) end
        , case when create_date_end is not null then format('al.date_create <= %L', create_date_end) end
        , case when upload_date_begin is not null then format('al.date_upload >= %L', upload_date_begin) end
        , case when upload_date_end is not null then format('al.date_upload <= %L', upload_date_end) end
        , case when sign_date_begin is not null then format('al.date_sign >= %L', sign_date_begin) end
        , case when sign_date_end is not null then format('al.date_sign <= %L', sign_date_end) end
        , case when send_date_begin is not null then format('al.date_send >= %L', send_date_begin) end
        , case when send_date_end is not null then format('al.date_send <= %L', send_date_end) end
        , case when print_date_begin is not null then format('al.date_print >= %L', print_date_begin) end
        , case when print_date_end is not null then format('al.date_print <= %L', print_date_end) end
        , case when cardinality(amed_work_type_name) > 0 then format('v006d.name_type_usl = any(%L)', amed_work_type_name) end);
    _sql text = 'select al.id,
           al.list_number,
           al.list_name,
           al.date_create,
           f003d.region_code::text as region_code,
           case
                when f003d.region_code = ''TULA''::expert.region then mo_tula.short_name::text
                else f003d.nam_mok::text
                end                                                              as medical_organization_name,
           case
                when f003d.region_code  = ''TULA''::expert.region then mo_tula.mo_code::text
                else f003d.mcode::text
                end                                                              as medical_organization_code,
           al.user_id,
           al.date_upload,
           al.zpmd_id,
           al.has_cover_letter_date,
           al.date_print,
           al.date_send,
           al.date_sign,
           al.old_id::text as old_id,
           al.theme_category_id,
           al.med_work_type_id,
           v006d.name_type_usl as med_work_type_name,
           al.assigned_expert_id,
           al.status,
           al.region
    from expert.acts_list al
             join directory.f003 f003d on f003d.mcode = al.medical_organization_code '|| concat_ws(' and '
        , case when aregion_code is not null
            then concat(format(' and f003d.region_code = %s ', aregion_code),
                case when aregion_code = 71
                    then 'join directory.mo_tula mo_tula on mo_tula.mo_code = al.medical_organization_code ' end)
            end
        , case when cardinality(amedical_organization_codes) > 0 then
                case when aregion_code = 71
                    then format('mo_tula.mo_code = any(''%s'')', amedical_organization_codes)
                    else format('f003d.mcode = any(''%s'')', amedical_organization_codes) end end) || '
              join directory.v006 v006d on v006d.code_type_usl = al.med_work_type_id';
begin
    return query execute _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by;
--return _sql || (case when _where <> '' then ' where '|| _where else '' end) || _order_by; -- debug mode
end
$$;