Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- $$;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement