with _Params as (
select $1::bigint as p_lpu_id,
       $2::text as p_lpufiliallist,
       $3::bigint as p_lpubuilding_id,
       $4::bigint as p_lpuunit_id,
       $5::bigint as p_lpusection_id,
       $6::bigint as p_medpersonal_id,
       $7::bigint as p_for_medpersonal,
      to_date($8,'dd.mm.yyyy') as p_begdate,
      to_date($9,'dd.mm.yyyy') as p_enddate
      )
	
, deps as (
select s.lpu_id, 
       s.lpubuilding_id,       
       s.lpusection_id,
       lsp.lpusectionprofile_id as prof_id,
       case when coalesce(p_for_medpersonal, 1) = 1 then s.lpusectionprofile_id end as lpusectionprofile_id,
	   case when coalesce(p_for_medpersonal, 1) = 1 then s.lpusectionprofile_name end as lpusectionprofile_name            
  from _Params
  join v_lpusection s on (p_lpu_id is null or s.lpu_id = p_lpu_id)
    and (p_lpusection_id is null or s.lpusection_id = p_lpusection_id)
    and (p_lpubuilding_id is null or s.lpubuilding_id = p_lpubuilding_id)
    and (p_lpuunit_id is null or s.lpuunit_id = p_lpuunit_id)
  join v_lpubuilding lb on lb.lpubuilding_id = s.lpubuilding_id 
  left join v_lpusectionprofile lsp on lsp.lpusectionprofile_id = s.lpusectionprofile_id  and lsp.lpusectionprofile_code = '160' --НМП
where (lb.lpufilial_id in(select id::int8 as id from unnest(string_to_array(p_lpufiliallist, ',')) as id) or coalesce(rtrim(p_lpufiliallist), '') = ''))

, Params as (  
select _Params.*,
       (select array_agg(lpusection_id) as agg_sec from deps) as agg_sec       
  from _Params)

, ps as (
select 2 as evntype, 
	   eps.person_id,
	   eps.evnps_id as evn_id,
	   es.lpusection_id,
       es.medpersonal_id,
	   es.evnsection_id as evn_p_id,
	   null::integer as treatmentclass_id ,
       eps.lpu_id
  from Params as pa 
  join v_evnps eps on (pa.p_lpu_id is null or eps.lpu_id = pa.p_lpu_id) and eps.evnps_disdate between pa.p_begdate and pa.p_enddate
    and eps.evnps_prehospwaifrefusedt is null--исключены отказы
  join v_evnsection es on es.evnsection_pid = eps.evnps_id 
    and (p_lpusection_id is null or es.lpusection_id = any(agg_sec)) 
where (p_medpersonal_id is null or es.medpersonal_id = p_medpersonal_id))

, pl as (
select 1 as evntype, 
	   epl.person_id,
	   epl.evnpl_id as evn_id,
	   vizit.lpusection_id,
       vizit.medpersonal_id,
	   vizit.evnvizitpl_id as evn_p_id,
	   vizit.treatmentclass_id,
       epl.lpu_id
  from Params as pa
  join v_evnpl epl on (pa.p_lpu_id is null or epl.lpu_id = pa.p_lpu_id) and epl.evnpl_disdate between pa.p_begdate and pa.p_enddate
    and epl.medpersonal_id is not null    
  join v_evnvizitpl vizit on vizit.evnvizitpl_pid = epl.evnpl_id and vizit.evnvizitpl_setdate between pa.p_begdate and pa.p_enddate
    and (p_lpusection_id is null or vizit.lpusection_id = any(agg_sec))
where (p_medpersonal_id is null or vizit.medpersonal_id = p_medpersonal_id))
    
, e as (  
select * from pl union all  
select * from ps) 

, t_1 as (
select e.lpu_id,
       deps.lpubuilding_id,
	   deps.lpusection_id, 
       coalesce(deps.lpusectionprofile_name, l_m.person_fio) as v3,               
       count(distinct case when e.evntype = 1 then ed.evndirection_id end) as v10,
	   count(distinct case when e.evntype = 1 and evnxml_eu_dir then eu_dir.evnusluga_id end) as v11, 		
		--КВС	
		count(distinct case when e.evntype = 2 then ed.evndirection_id end) as v19,
		count(distinct case when e.evntype = 2 and evnxml_eu_dir is not null then eu_dir.evnusluga_id end) as v20,
		--НМП	
		count(distinct case when e.evntype = 1 and coalesce(tc.treatmentclass_id,deps.prof_id) is not null then ed.evndirection_id end) as v28,
		count(distinct case when e.evntype = 1 and coalesce(tc.treatmentclass_id,deps.prof_id) is not null and evnxml_eu_dir then eu_dir.evnusluga_id end) as v29             
  from e  
  join v_evndirection_all ed on ed.evndirection_pid = e.evn_p_id
  join v_dirtype dt on dt.dirtype_id = ed.dirtype_id and dt.dirtype_code = 9 --на исследование (лабораторная и инстр. диагностика)                    
  left join v_evnusluga eu_dir on eu_dir.evndirection_id = ed.evndirection_id
  left join lateral (
       select 1::boolean as evnxml_eu_dir
         from v_evnxml evnxml_eu_dir where evnxml_eu_dir.evn_id = eu_dir.evnusluga_id and evnxml_eu_dir.xmltype_id in (4, 7) limit 1) as l_evnxml_eu_dir on true  --протокол осмотра у услуги из направления  
  left join deps on deps.lpusection_id = e.lpusection_id
  left join v_treatmentclass tc on tc.treatmentclass_id = e.treatmentclass_id and tc.treatmentclass_code = '1.1' --НМП       
  left join lateral (
       select mp.person_fio 
         from _Params 
         join v_medpersonal mp on coalesce(p_for_medpersonal, 1) != 1 and mp.medpersonal_id = e.medpersonal_id limit 1) as l_m on true  
group by e.lpu_id,deps.lpubuilding_id,coalesce(deps.lpusectionprofile_name, l_m.person_fio),deps.lpusection_id 
) 

, t_2 as (
select e.lpu_id,
	   deps.lpubuilding_id,
	   coalesce(deps.lpusectionprofile_name, l_m.person_fio) as v3,  --фильтр "по сотрудникам"	
		deps.lpusection_id, 
		--ТАП
		count(distinct case when e.evntype = 1 then e.evn_p_id end) as v4,
		count(distinct case when e.evntype = 1 and evnxml_e is not null then e.evn_p_id end) as v5,	
		count(distinct case when e.evntype = 1 then eu.evnusluga_id end) as v7,
		count(distinct case when e.evntype = 1 and evnxml_eu is not null then eu.evnusluga_id end) as v8,		
		--КВС
		count(distinct case when e.evntype = 2 then e.evn_id end) as v13,
		count(distinct case when e.evntype = 2 and evnxml_e is not null then e.evn_id end) as v14,		
		count(distinct case when e.evntype = 2 then eu.evnusluga_id end) as v16,
		count(distinct case when e.evntype = 2 and evnxml_eu is not null then eu.evnusluga_id end) as v17,		
		--НМП
		count(distinct case when e.evntype = 1 and coalesce(tc.treatmentclass_id,deps.prof_id) is not null then e.evn_p_id end) as v22,
		count(distinct case when e.evntype = 1 and coalesce(tc.treatmentclass_id,deps.prof_id) is not null and evnxml_e is not null then e.evn_p_id end) as v23,		
		count(distinct case when e.evntype = 1 and coalesce(tc.treatmentclass_id,deps.prof_id) is not null then eu.evnusluga_id end) as v25,
		count(distinct case when e.evntype = 1 and coalesce(tc.treatmentclass_id,deps.prof_id) is not null and evnxml_eu is not null then eu.evnusluga_id end) as v26
  from e
  left join deps on deps.lpusection_id = e.lpusection_id
  left join v_evnusluga eu on eu.evnusluga_pid = e.evn_p_id  
  left join v_treatmentclass tc on tc.treatmentclass_id = e.treatmentclass_id and tc.treatmentclass_code = '1.1' --НМП  
  left join lateral (
       select mp.person_fio 
         from _Params 
         join v_medpersonal mp on coalesce(p_for_medpersonal, 1) != 1 and mp.medpersonal_id = e.medpersonal_id limit 1) as l_m on true
  left join lateral (
       select evnxml_eu.evnxml_id as evnxml_eu
         from v_evnxml evnxml_eu where evnxml_eu.evn_id = eu.evnusluga_id and evnxml_eu.xmltype_id in (4, 7) limit 1) as l_evnxml_eu on true --протокол осмотра - услуга/специфика  
  left join lateral (
       select evnxml_e.evnxml_id as evnxml_e
         from v_evnxml evnxml_e 
       where evnxml_e.evn_id = e.evn_p_id 
		 and evnxml_e.xmltype_id in (3, 8, 9, 10)--протокол осмотра в ТАП/КВС
		 and (EvnXml_insDT<>EvnXml_updDT and evnxml_e.xmltype_id=3 or evnxml_e.xmltype_id!=3) limit 1) as l_evnxml_e on true --автомат.созданные  
group by e.lpu_id,deps.lpubuilding_id,coalesce(deps.lpusectionprofile_name, l_m.person_fio),deps.lpusection_id 
) 

, t as (
select lpu_id,lpubuilding_id,lpusection_id, v3 from t_1 union
select lpu_id,lpubuilding_id,lpusection_id, v3 from t_2)

, get_result as (
select t.lpu_id,
       t.lpubuilding_id, 
       t.lpusection_id, 
       t.v3,v4,v5,v7,v8,v10,v11,v13,v14,v16,v17,v19,v20,v22,v23,v25,v26,v28,v29
  from t 
  left join t_1 on t.lpu_id = t_1.lpu_id and t.lpubuilding_id = t_1.lpubuilding_id and  t.v3 = t_1.v3 and t.lpusection_id = t_1.lpusection_id  
  left join t_2 on t.lpu_id = t_2.lpu_id and t.lpubuilding_id = t_2.lpubuilding_id and  t.v3 = t_2.v3 and t.lpusection_id = t_2.lpusection_id) 
  
, t1 as (
select 
	1 as row_s,
    r.lpu_id,
	r.lpubuilding_id,
	r.lpusection_id, 
	r.v3,
	r.v4,
	r.v5,
	case when r.v4 > 0 then trunc(r.v5::numeric /r.v4::numeric *100, 2) else 0 end as v6,	
	r.v7,
	r.v8,
	case when r.v7 > 0 then trunc(r.v8::numeric /r.v7::numeric *100, 2) else 0 end as v9,	
	r.v10,
	r.v11,
	case when r.v10 > 0 then trunc(r.v11::numeric /r.v10::numeric *100, 2) else 0 end as v12,	
	r.v13,
	r.v14,
	case when r.v13 > 0 then trunc(r.v14::numeric /r.v13::numeric *100, 2) else 0 end as v15,	
	r.v16,
	r.v17,
	case when r.v16 > 0 then trunc(r.v17::numeric /r.v16::numeric *100, 2) else 0 end as v18,	
	r.v19,
	r.v20,
	case when r.v19 > 0 then trunc(r.v20::numeric /r.v19::numeric *100, 2) else 0 end as v21,	
	r.v22,
	r.v23,
	case when r.v22 > 0 then trunc(r.v23::numeric /r.v22::numeric *100, 2) else 0 end as v24,	
	r.v25,
	r.v26,
	case when r.v25 > 0 then trunc(r.v26::numeric /r.v25::numeric *100, 2) else 0 end as v27,	
	r.v28,
	r.v29,
	case when r.v28 > 0 then trunc(r.v29::numeric /r.v28::numeric *100, 2) else 0 end as v30	
from get_result as r)


, t2 as (
select 
	2 as row_s,
	null::bigint as lpu_id,
	null::bigint as lpubuilding_id, 
	null::bigint as lpusection_id,  
	'Итого: ' as v3,	
	sum(r.v4) as v4,
	sum(r.v5) as v5,
	case when sum(r.v4) > 0 then trunc(sum(r.v5)::numeric /sum(r.v4)::numeric *100, 2) else 0 end as v6,	
	sum(r.v7) as v7,
	sum(r.v8) as v8,
	case when sum(r.v7) > 0 then trunc(sum(r.v8)::numeric /sum(r.v7)::numeric *100, 2) else 0 end as v9,	
	sum(r.v10) as v10,
	sum(r.v11) as v11,
	case when sum(r.v10) > 0 then trunc(sum(r.v11)::numeric /sum(r.v10)::numeric *100, 2) else 0 end as v12,	
	sum(r.v13) as v13,
	sum(r.v14) as v14,
	case when sum(r.v13) > 0 then trunc(sum(r.v14)::numeric /sum(r.v13)::numeric *100, 2) else 0 end as v15,	
	sum(r.v16) as v16,
	sum(r.v17) as v17,
	case when sum(r.v16) > 0 then trunc(sum(r.v17)::numeric /sum(r.v16)::numeric *100, 2) else 0 end as v18,	
	sum(r.v19) as v19,
	sum(r.v20) as v20,
	case when sum(r.v19) > 0 then trunc(sum(r.v20)::numeric /sum(r.v19)::numeric *100, 2) else 0 end as v21,	
	sum(r.v22) as v22,
	sum(r.v23) as v23,
	case when sum(r.v22) > 0 then trunc(sum(r.v23)::numeric /sum(r.v22)::numeric *100, 2) else 0 end as v24,	
	sum(r.v25) as v25,
	sum(r.v26) as v26,
	case when sum(r.v25) > 0 then trunc(sum(r.v26)::numeric /sum(r.v25)::numeric *100, 2) else 0 end as v27,	
	sum(r.v28) as v28,
	sum(r.v29) as v29,
	case when sum(r.v28) > 0 then trunc(sum(r.v29)::numeric /sum(r.v28)::numeric *100, 2) else 0 end as v30	
from get_result as r)	

, main as (  
select * from t1 union all  
select * from t2)

select row_s,
       l.lpu_nick,
       b.lpubuilding_name,
       s.lpusection_name,
       v3,v4,v5,v6,v7,v8,v9,v10,
       v11,v12,v13,v14,v15,v16,
       v17,v18,v19,v20,v21,v22,
       v23,v24,v25,v26,v27,v28,v29,v30
  from main as m
  left join v_lpu as l on l.lpu_id = m.lpu_id
  left join v_lpubuilding as b on b.lpubuilding_id = m.lpubuilding_id
  left join v_lpusection as s on s.lpusection_id = m.lpusection_id 
