select zsl.id,
       zsl.period,
       zsl.manual_upload,
       zsl.family,
       zsl.firstname,
       zsl.patronymic,
       zsl.born_date,
       zsl.policy_number,
       zsl.usl_ok, -- net
       zsl.usl_ok_name,
       zsl.lpu_code,
       zsl.lpu_name,
       zsl.begin_date,
       zsl.end_date,
       zsl.kd_z,
       zsl.rslt,
       zsl.rslt_name,
       zsl.ishod,
       zsl.ishod_name,
       zsl.disp_code,
       zsl.disp_type,
       zsl.vidpom,
       zsl.vidpom_name,
       zsl.sumv,
       zsl.otd_names,
       zsl.category_ids,
       max(sluch.id) filter (where sluch.is_final_record = true) as final_medical_case_id,
       sluch.idzsl,
       coalesce(jsonb_agg(distinct v002.name_profil) filter (where v002.name_profil is not null),'[]' :: jsonb) as mp_profiles,
       coalesce(jsonb_agg(distinct v020.kp_prname) filter (where v020.kp_prname is not null), '[]' :: jsonb) as bed_profiles,
       jsonb_agg(distinct sluch.nhistory) as med_history_numbers,
       coalesce(jsonb_agg(distinct v018.name_vid_pom) filter (where v018.name_vid_pom is not null), '[]' :: jsonb) as vid_hmp_names,
       coalesce(jsonb_agg(distinct v019.name_vid_hpom) filter (where v019.name_vid_hpom is not null),'[]' :: jsonb) as method_hmp_names,
       coalesce(jsonb_agg(distinct jsonb_build_object('pPer', sluch.other ->> 'P_PER', 'pPerName', p_per_dict.pname)) filter (where sluch.other ->> 'P_PER' is not null), '[]' :: jsonb) as p_pers,
       max(sluch.diag ->> 'DS1') as ds1,
       max(mkb_d.mkb_name) as ds1_name,
       coalesce(
           jsonb_agg(distinct spec.name) filter (where spec.name <> '' :: text),
           jsonb_agg(distinct v021.name_spec) filter (where v021.name_spec <> '' :: text),
           '[]' :: jsonb
       ) as special_names,
       coalesce(jsonb_agg(distinct jsonb_build_object('ksg', sluch.ksg_kpg ->> 'N_KSG', 'ksgName', case when :regionCode = 74 then ksg.name else v023.name_ksg end)) filter (where(sluch.ksg_kpg ->> 'N_KSG') is not null), '[]' :: jsonb) as ksgs,
       coalesce(jsonb_agg(distinct sluch_onk.onk ->> 'DS1_T') filter (where sluch_onk.onk ->> 'DS1_T' is not null), '[]' :: jsonb) as ds_onks,
       count(distinct sluch.id) as med_case_count,
       coalesce(array_agg(distinct v002.code_profil) filter (where v002.code_profil is not null), '{}'::smallint[]) as mp_profile_codes,
       coalesce((array_agg(distinct sluch.other -> 'PROFIL_K') filter (where sluch.other ->> 'PROFIL_K' is not null))::smallint[], '{}'::smallint[]) as bed_profile_codes,
       coalesce(jsonb_agg(distinct sluch.hmp ->> 'VID_HMP') filter (where sluch.hmp ->> 'VID_HMP' is not null), '[]'::jsonb) as vid_hmp_codes,
       coalesce((array_agg(distinct sluch.hmp ->> 'METOD_HMP') filter (where sluch.hmp ->> 'METOD_HMP' is not null))::smallint[], '{}'::smallint[]) as method_hmp_codes,
       coalesce(jsonb_agg(distinct sluch.diag ->> 'DS2') filter (where sluch.diag ->> 'DS2' is not null), '[]'::jsonb) as ds2,
       coalesce(jsonb_agg(distinct sluch.diag ->> 'DS3') filter (where sluch.diag ->> 'DS3' is not null), '[]'::jsonb) as ds3,
       coalesce(array_agg(distinct sluch.ksg_kpg ->> 'N_KSG') filter (where (sluch.ksg_kpg ->> 'N_KSG') is not null), '{}'::text[]) as ksg_codes,
       coalesce(array_agg(distinct sluch.ksg_kpg ->> 'N_KPG') filter (where (sluch.ksg_kpg ->> 'N_KPG') is not null), '{}'::text[]) as kpg_codes,
       coalesce((array_agg(distinct sluch.med ->> 'PRVS') filter (where sluch.med ->> 'PRVS' is not null))::integer[], '{}'::integer[]) as special_codes,
       podr_name
from ( (
    select zsl.id                               as id,
           schet_period.period_date             as period,
           zsl.manual_upload,
           person.fam                           as family,
           person.im                            as firstname,
           person.ot                            as patronymic,
           person.dr                            as born_date,
           person.npolis                        as policy_number,
           v006.code_type_usl                        as usl_ok,
           v006.name_type_usl                   as usl_ok_name,
           f003.mcode                           as lpu_code,
           f003.nam_mok                         as lpu_name,
           zsl.date_begin                       as begin_date,
           zsl.date_end                         as end_date,
           (zsl.other ->> 'KD_Z')::smallint     as kd_z,
           CASE WHEN (schet.iddisp is null) THEN v009.code_resl ELSE v017.code_disp_resl END as rslt,
           CASE WHEN (schet.iddisp is null) THEN v009.name_resl::text ELSE v017.name_disp_resl::text END as rslt_name,
           v012.code_ishod::smallint            as ishod,
           v012.name_ishod                      as ishod_name,
           v016.code_disp                       as disp_code,
           v016.name_disp                       as disp_type,
           (zsl.med_pom ->> 'VIDPOM')::smallint as vidpom,
           v008.vmpname                         as vidpom_name,
           (zsl.sum ->> 'SUMV')::numeric        as sumv,
           '[]'::jsonb                          as otd_names,
           (select coalesce(jsonb_agg(distinct selected_zsl.category_id), '[]'::jsonb) from bills.selected_zsl where selected_zsl.zsl_id = zsl.id ) as category_ids,
           coalesce(podr_omsk.nam_mop, podr_tula.name) as podr_name
    from bills.schet
             join bills.schet_period on schet_period.id = schet.idperiod
             join bills.zsl on zsl.idschet = schet.id
             join bills.sluch sl on sl.idzsl = zsl.id and sl.is_final_record = true
             join pacients.person on person.id = zsl.idperson
             join directory.f003 on f003.id = zsl.idmedorg
             left join directory.podr_omsk podr_omsk on schet.idfilial = directory.getfilialidbycode(55)
                                                and podr_omsk.lpu_1::varchar = sl.lpu ->> 'LPU_1'
                                                and zsl.date_end between podr_omsk.date_begin and coalesce(podr_omsk.date_end, '9999-01-01')
             left join directory.t_podr_tula podr_tula on schet.idfilial = directory.getfilialidbycode(71)
                                                            and podr_tula.podr_id = sl.lpu ->> 'LPU_1'
             left join directory.v006 v006 on v006.id = zsl.idtypeusl
             left join directory.v008 v008 on v008.idvmp::text = zsl.med_pom ->> 'VIDPOM'
             left join directory.v009 on v009.id = zsl.idresult
             left join directory.v017 on v017.id = zsl.idresult
             left join directory.v012 on v012.id = zsl.idishod
             left join directory.v016 on v016.id = schet.iddisp
             left join bills.not_visible_zsl nvz on zsl.id=nvz.zsl_id
    where schet.idfilial = directory.getfilialidbycode(:regionCode)
          and nvz.id is null
      and schet_period.period_date between :periodBeginDate and :periodEndDate
      and exists(
        select 1 from bills.sluch
        left join directory.v002 on v002.id = sluch.idprofil
        where sluch.idzsl = zsl.id %sluchFilters% ) %filters%
    order by %order% lower(person.fam), lower(person.im), lower(person.ot), zsl.date_begin, zsl.date_end, zsl.id
    limit :limit offset :offset)) zsl
         join bills.sluch on sluch.idzsl = zsl.id
         left join bills.usl on usl.idsluch = sluch.id
         left join directory.v002 on v002.id = sluch.idprofil
         left join directory.v020 v020 on v020.idk_pr::text = sluch.other ->> 'PROFIL_K'
             and sluch.date_end between v020.date_begin and v020.date_end
         left join directory.p_per p_per_dict on p_per_dict.p_per_id::text = sluch.other ->> 'P_PER'
         left join directory.v018 v018 on v018.code_vid_pom = sluch.hmp ->> 'VID_HMP'
             and sluch.date_end between  v018.begin_date and v018.end_date
         left join directory.v019 v019 on v019.code_vid_hpom::text = sluch.hmp ->> 'METOD_HMP'
             and sluch.date_end between v019.begin_date and v019.end_date
         left join directory.mkb mkb_d on mkb_d.mkb_code = sluch.diag ->> 'DS1'
         left join directory.special spec on (sluch.other -> 'COD_SPEC')::int = spec.npp and :regionCode = 74
         left join directory.v021 v021 on v021.code_spec :: text = sluch.med ->> 'PRVS' and :regionCode != 74
             and sluch.date_end >= v021.begin_date
             and sluch.date_begin <= v021.end_date
         left join directory.v023 v023 on v023.idump = zsl.usl_ok
             and v023.code_ksg = (sluch.ksg_kpg ->> 'N_KSG') and sluch.date_end >= v023.begin_date and (v023.end_date is null or sluch.date_end <= v023.end_date)
         left join directory.ksg on ksg.usl_ok = zsl.usl_ok
             and ksg.ksg_num2 = (sluch.ksg_kpg ->> 'N_KSG') and sluch.date_end >= date(to_timestamp(ksg.date_beg2 / 1000) at time zone 'Europe/Moscow')
                                        and (ksg.date_end2 is null or sluch.date_end <= date(to_timestamp(ksg.date_end2 / 1000) at time zone 'Europe/Moscow'))
         left join bills.sluch_onk on sluch_onk.idsluch = sluch.id
group by sluch.idzsl,
         zsl.id,
         zsl.period,
         zsl.family,
         zsl.firstname,
         zsl.patronymic,
         zsl.born_date,
         zsl.policy_number,
         zsl.usl_ok,
         zsl.usl_ok_name,
         zsl.lpu_code,
         zsl.lpu_name,
         zsl.begin_date,
         zsl.end_date,
         zsl.kd_z,
         zsl.rslt,
         zsl.rslt_name,
         zsl.ishod,
         zsl.ishod_name,
         zsl.disp_code,
         zsl.disp_type,
         zsl.vidpom,
         zsl.vidpom_name,
         zsl.sumv,
         zsl.otd_names,
         zsl.category_ids,
         zsl.manual_upload,
         zsl.podr_name
order by %order% lower(zsl.family), lower(zsl.firstname), lower(zsl.patronymic), zsl.begin_date, zsl.end_date, zsl.id