with _prm as (
select to_date($1,'dd.mm.yyyy') as paramBegDate,
       to_date($2,'dd.mm.yyyy') as paramEndDate,
       $3::bigint as paramLpu,
       $4::bigint as paramLpuBuilding,
       $5::bigint as paramLpuUnit,
       $6::bigint as paramLpuSection,
       $7::bigint as paramMedSpecClass,
       $8::bigint as paramMedStaffFact)
       
/*with _prm as (
select to_date('29.06.2021','dd.mm.yyyy') as paramBegDate,
       to_date('10.08.2021','dd.mm.yyyy') as paramEndDate,
       10101000000001::bigint as paramLpu,
       null::bigint as paramLpuBuilding,
       null::bigint as paramLpuUnit,
       null::bigint as paramLpuSection,
       null::bigint as paramMedSpecClass,
       null::bigint as paramMedStaffFact)*/

, st as (
select st.servicetype_id,
       case when st.servicetype_name = 'На дому: НМП' then 1 
            when st.servicetype_name in ('Поликлиника: НМП','Травмпункт','Приемный покой (без динамического наблюдения)','Приемный покой (динамическое наблюдение)') then 0 
            when st.servicetype_name in ('в Поезде здоровья','Поликлиника','Мобильная медицинская бригада') then 2 
            when st.servicetype_name = 'На дому' then 3 
            when st.servicetype_name = 'На дому: Актив' then 4 end as flg
    from v_servicetype st where st.servicetype_name in ('На дому: Актив','На дому','На дому: НМП','Поликлиника: НМП','Травмпункт','Приемный покой (без динамического наблюдения)','Приемный покой (динамическое наблюдение)','в Поезде здоровья','Поликлиника','Мобильная медицинская бригада')) 
    
, main as (
select pl.evnpl_id as case_id,
       viz.evnvizitpl_id as step_id,       
       (viz.evnvizitpl_index = viz.evnvizitpl_count - 1) as is_finish_step,
       --row_number() over(partition by pl.evnpl_id order by viz.medstafffact_id) rn,
       viz.evnvizitpl_count as cnt_step,
       tr.treatmentclass_code as tr_code,
       st.flg,
       
       med.medstafffact_id,
       med.person_fio as doc_fio,
       mss.medspecclass_name as post_name,
       
       pl.person_id,
       extract(year from age (pl.evnpl_disdt,p.person_birthday)) as age,
       case when pl.evnpl_disdt < '2020-01-01' then 0
            when pl.evnpl_disdt < '2022-01-01' then 1
            when pl.evnpl_disdt < '2024-01-01' then 2
            when pl.evnpl_disdt < '2026-01-01' then 3
            when pl.evnpl_disdt < '2028-01-01' then 4 else 5 end as delta,
       case when p.sex_id = 1 then 60
            when p.sex_id = 2 then 55 end as grand_age,
       padr.klareatype_id as is_city       
  from _prm 
  join v_evnpl pl on pl.lpu_id = paramLpu 
   and pl.evnpl_disdt between paramBegDate and paramEndDate 
   and pl.evnpl_isfinish = 2
  join v_evnvizitpl viz on viz.evnvizitpl_pid = pl.evnpl_id
  join v_lpusection ls on ls.lpusection_id = viz.lpusection_id
  
  join v_medstafffact med on med.medstafffact_id = viz.medstafffact_id
  join v_medspecoms mso on mso.medspecoms_id = med.medspecoms_id
  join fed.v_medspecclass mss on mss.medspecclass_id = mso.medspecclass_id
  
  join v_personstate p on p.person_id = pl.person_id
  left join v_address padr on padr.address_id = coalesce(p.paddress_id,p.uaddress_id)
  left join v_treatmentclass tr on tr.treatmentclass_id = viz.treatmentclass_id
  left join st on st.servicetype_id = viz.servicetype_id
  
where (paramMedStaffFact is null or viz.medpersonal_id = paramMedStaffFact)
  and (paramMedSpecClass is null or mso.medspecclass_id = paramMedSpecClass)
  and (paramLpuSection is null or viz.lpusection_id = paramLpuSection)
  and (paramLpuBuilding is null or ls.lpubuilding_id = paramLpuBuilding)
  and (paramLpuUnit is null or ls.lpuunit_id = paramLpuUnit))

, tb1 as (  
select post_name,
       doc_fio,
       count(distinct case when cnt_step >=2 then case_id end) as s2,
       count(distinct case when cnt_step >=2 and is_city=2 then case_id end) as s3,
       count(distinct case when cnt_step >=2 and age between 0 and 17 then case_id end) as s4,
       count(distinct case when cnt_step >=2 and age >= grand_age+delta then case_id end) as s5,
       sum(case when cnt_step >=2 then cnt_step end) as s6,
       count(distinct case when cnt_step >=2 then person_id end) as s7,
       count(distinct case when cnt_step >=2 and age between 0 and 17 then person_id end) as s8,
       count(distinct case when cnt_step >=2 and age=0 then person_id end) as s9,
       count(distinct case when cnt_step >=2 and age >= grand_age+delta then person_id end) as s10,
       
       count(distinct case when cnt_step =1 and tr_code != '1.1' then case_id end) as s11,
       count(distinct case when cnt_step =1 and tr_code != '1.1' and is_city=2 then case_id end) as s12,
       count(distinct case when cnt_step =1 and tr_code != '1.1' and age between 0 and 17 then case_id end) as s13,
       count(distinct case when cnt_step =1 and tr_code != '1.1' and age >= grand_age+delta then case_id end) as s14,
       count(distinct case when cnt_step =1 and tr_code in ('1','1.2','1.3','3.0') then case_id end) as s15,
       count(distinct case when cnt_step =1 and tr_code in ('1','1.2','1.3','3.0') and age between 0 and 17 then case_id end) as s16,
       count(distinct case when cnt_step =1 and tr_code in ('2.1','2.2','2.3','2.5','2.6','3.1') then case_id end) as s17,       
       count(distinct case when cnt_step =1 and tr_code != '1.1' then person_id end) as s18,       
       count(distinct case when cnt_step =1 and tr_code != '1.1' and age between 0 and 17 then person_id end) as s19,
       count(distinct case when cnt_step =1 and tr_code != '1.1' and age=0 then person_id end) as s20,
       count(distinct case when cnt_step =1 and tr_code = '1.2' and age=0 then person_id end) as s21,       
       count(distinct case when cnt_step =1 and tr_code != '1.1' and age >= grand_age+delta then person_id end) as s22,
       
       count(distinct case when cnt_step =1 and tr_code = '1.1' then case_id end) as s23,
       count(distinct case when cnt_step =1 and tr_code = '1.1' and flg = 0 then case_id end) as s24,
       count(distinct case when cnt_step =1 and tr_code = '1.1' and flg = 1 then case_id end) as s25,
       count(distinct case when cnt_step =1 and tr_code = '1.1' then person_id end) as s26
  from main m
where is_finish_step
group by rollup (1,2))

, tb2 as (
select post_name,
       doc_fio,       
       count(distinct step_id) as s2,
       count(distinct case when flg = 2 then step_id end) as s3,
       count(distinct case when flg = 2 and age between 0 and 17 then step_id end) as s4,
       count(distinct case when flg in (3,4) then step_id end) as s5,
       count(distinct case when flg in (3,4) and age between 0 and 17 then step_id end) as s6,
       count(distinct case when flg = 4 and age between 0 and 17 then step_id end) as s7,
       count(distinct case when tr_code = '1.1' then step_id end) as s8,
       count(distinct case when tr_code = '1.1' and flg = 1 then step_id end) as s9
  from main m
group by rollup (1,2))

, t as (
select 1 as gr,coalesce(post_name,'ЯBсего') as post_name,
               coalesce(doc_fio,'ZBсего') as doc_fio,               
               coalesce(s2,0) as s2,
               coalesce(s3,0) as s3,
               coalesce(s4,0) as s4,
               coalesce(s5,0) as s5,
               coalesce(s6,0) as s6,
               coalesce(s7,0) as s7,
               coalesce(s8,0) as s8,
               coalesce(s9,0) as s9,
               coalesce(s10,0) as s10,
               coalesce(s11,0) as s11,
               coalesce(s12,0) as s12,
               coalesce(s13,0) as s13,
               coalesce(s14,0) as s14,
               coalesce(s15,0) as s15,
               coalesce(s16,0) as s16,
               coalesce(s17,0) as s17,
               coalesce(s18,0) as s18,
               coalesce(s19,0) as s19,
               coalesce(s20,0) as s20,
               coalesce(s21,0) as s21,
               coalesce(s22,0) as s22,
               coalesce(s23,0) as s23,
               coalesce(s24,0) as s24,
               coalesce(s25,0) as s25,
               coalesce(s26,0) as s26
  from tb1 union all 
select 2 as gr,coalesce(post_name,'ЯBсего') as post_name,
               coalesce(doc_fio,'ZBсего') as doc_fio,               
               coalesce(s2,0) as s2,
               coalesce(s3,0) as s3,
               coalesce(s4,0) as s4,
               coalesce(s5,0) as s5,
               coalesce(s6,0) as s6,
               coalesce(s7,0) as s7,
               coalesce(s8,0) as s8,
               coalesce(s9,0) as s9,
               null s10,null s11,null s12,null s13,null s14,null s15,
               null s16,null s17,null s18,null s19,null s20,null s21,
               null s22,null s23,null s24,null s25,null s26 from tb2)

select * 
  from t               
order by 3

