
with Params as( 
select
	cast($1 as bigint) as p_lpu_id,
	cast($2 as bigint) as p_lpubuilding_id,
	cast($3 as bigint) as p_lpuunit_id,
	cast($4 as bigint) as p_lpusection_id,
	cast($5 as bigint) as p_medpersonal_id,
	cast($6 as bigint) as p_uslugaclass_id,
	cast($7 as bigint) as p_isdvn,
	cast($8 as bigint) as p_isprof,
	cast($9 as bigint) as p_isdds,
	cast($10 as bigint) as p_ischild,
	cast($11 as timestamp) as p_begdate, 
	cast($12 as timestamp) as p_enddate,
	cast(coalesce($13,1) as bigint) as p_yesno,
    cast($14 as bigint) as p_MedStaffSisterPolka
)

, polka as (
select eu.MedStaffFact_id, 
       eu.medstafffact_sid,
       ev.Lpusection_id,
       case when epl.EvnPL_id is not null 
              or (epld.EvnPLDisp_id is not null and dc.DispClass_id in (1,2) and p.p_isdvn = 2)
              or (epld.EvnPLDisp_id is not null and dc.DispClass_id = 5 and p.p_isprof = 2)
              or (epld.EvnPLDisp_id is not null and dc.DispClass_id in (3,4,7,8) and p.p_isdds = 2)
              or (epld.EvnPLDisp_id is not null and dc.DispClass_id in (6,9,10,11,12) and p.p_ischild = 2)
            then eu.EvnUsluga_id end as polka_usl,
       case when EvnXML_polka.Evn_id is not null then eu.EvnUsluga_id end as polka_prot,
       null::bigint as stac_usl,
       null::bigint as stac_prot,
       p_yesno,
       p_MedStaffSisterPolka,
       eu.Medpersonal_id,
       p_medpersonal_id
  from v_EvnUsluga eu
  join params p on (eu.Lpu_id=p.p_lpu_id or p.p_lpu_id is null) and cast(eu.EvnUsluga_setDT as timestamp) between p.p_begdate and p.p_enddate + interval '1 day'
  join v_EvnVizit ev on ev.EvnVizit_id=eu.EvnUsluga_pid
  left join v_EvnPLDisp epld on epld.EvnPLDisp_id=ev.EvnVizit_pid
  left join v_EvnPL epl on epl.EvnPL_id=ev.EvnVizit_pid
  left join v_DispClass dc on dc.DispClass_id=epld.DispClass_id
  join v_Lpusection ls on ls.LpuSection_id=ev.LpuSection_id
  left join lateral (
       select Evn_id, 
              EvnXml_insDT, 
              EvnXml_updDT, 
              XmlType_id
          from v_EvnXml EvnXML
        where  eu.EvnUsluga_id = EvnXml.Evn_id and EvnXml.XmlType_id != 3) EvnXML_polka on true
where /*(eu.Medpersonal_id= p.p_medpersonal_id or p.p_medpersonal_id is null)
  and */(ls.Lpusection_id=p.p_lpusection_id or p.p_lpusection_id is null)
  and (ls.LpuUnit_id=p.p_lpuunit_id or p.p_lpuunit_id is null)
  and (ls.LpuBuilding_id=p.p_lpubuilding_id or p.p_lpubuilding_id is null)
  and (p_yesno = 1 or p_yesno = 2 and eu.medstafffact_sid is not null)

union all

select eu.MedStaffFact_id, 
       eu.medstafffact_sid,
       es.Lpusection_id,
       null::bigint as polka_usl,
       null::bigint as polka_prot,
       case when es.EvnSection_id is not null then eu.EvnUsluga_id end as stac_usl,
       case when EvnXML_stac.Evn_id is not null then eu.EvnUsluga_id end as stac_prot,
       p_yesno,
       p_MedStaffSisterPolka,
       es.Medpersonal_id,
       p_medpersonal_id
  from v_EvnUsluga eu
  join params p on (eu.Lpu_id=p.p_lpu_id or p.p_lpu_id is null) and cast(eu.EvnUsluga_setDT as timestamp) between p.p_begdate and p.p_enddate + interval '1 day'
  join v_EvnSection es on es.EvnSection_id=eu.EvnUsluga_pid   
  join v_Lpusection ls on ls.LpuSection_id=es.LpuSection_id
  left join lateral (  
       select Evn_id,
              EvnXml_insDT,
              EvnXml_updDT,
              XmlType_id
         from v_EvnXml EvnXML
       where eu.EvnUsluga_id = EvnXml.Evn_id and EvnXml.XmlType_id != 8) EvnXML_stac on true
where /*(es.Medpersonal_id=p.p_medpersonal_id or p.p_medpersonal_id is null)
  and */(ls.Lpusection_id=p.p_lpusection_id or p.p_lpusection_id is null)
  and (ls.LpuUnit_id=p.p_lpuunit_id or p.p_lpuunit_id is null)
  and (ls.LpuBuilding_id=p.p_lpubuilding_id or p.p_lpubuilding_id is null)
  and (p_yesno = 1 or p_yesno = 2 and eu.medstafffact_sid is not null)
  )


select l.lpu_nick,
       mp.Person_fio AS med_fio,
       p.Name as med_post,
       mp_mid.Person_fio AS mid_fio,
       p_mid.Name as mid_post,       
       count(distinct a.polka_usl)+count(distinct a.stac_usl) as total,
       count(distinct a.polka_usl) as polka,
       count(distinct a.stac_usl) as stac,
       count(distinct a.polka_prot)+count(distinct a.stac_prot) as total_prot,
       count(distinct a.polka_prot) as polka_prot,
       count(distinct a.stac_prot) as stac_prot,
       case when (count(distinct a.polka_usl)+count(distinct a.stac_usl)) <> 0 
            then count(distinct a.polka_prot)+count(distinct a.stac_prot)::numeric(10,2)*100/(count(distinct a.polka_usl)+count(distinct a.stac_usl))::numeric(10,2) else 0 end::numeric(10,2) as prc_total,
       case when count(distinct a.polka_usl) <> 0 
            then count(distinct a.polka_prot)::numeric(10,2)*100/count(distinct a.polka_usl)::numeric(10,2) else 0 end::numeric(10,2) as prc_polka,
       case when count(distinct a.stac_usl) <> 0 
            then count(distinct a.stac_prot)::numeric(10,2)*100/count(distinct a.stac_usl)::numeric(10,2) else 0 end::numeric(10,2) as prc_stac
  from polka a
  join v_MedStaffFact msf on msf.MedStaffFact_id=a.MedStaffFact_id
  join v_medpersonal mp on mp.MedPersonal_id=msf.MedPersonal_id
  join persis.Post p on p.id=msf.Post_id
  join v_lpu l on l.lpu_id = msf.lpu_id  
  left join v_MedStaffFact msf_mid on msf_mid.MedStaffFact_id = a.MedStaffFact_sid
  left join v_medpersonal mp_mid on mp_mid.MedPersonal_id = msf_mid.MedPersonal_id
  left join persis.Post p_mid on p_mid.id = msf_mid.Post_id  
where (polka_usl is not null or stac_usl is not null)
  and (p_medpersonal_id is null or a.Medpersonal_id = p_medpersonal_id)
  and (p_MedStaffSisterPolka is null or p_MedStaffSisterPolka = msf_mid.MedPersonal_id)
group by 1,2,3,4,5
