with params as (
	select 
		cast($1 as bigint) as lpu_id,
		cast(to_timestamp($2,'DD.MM.YYYY') as timestamp) as begDate,
		cast(to_timestamp($3,'DD.MM.YYYY') as timestamp) as endDate,
		cast($4 as bigint) as registry_id,
		cast($5 as bigint) as is_closedata,
		cast($6 as bigint) as katnasel_reg_id
)

,registry_all as (
	select
		r.registry_id,
		r.registrytype_id,
		rd.evn_id,
		rd.evn_rid,
		rd.lpusection_id,
		rd.registrydata_itogsum,
        RD.ParentEvnClass_id,
        RD.MedStaffFact_id
	from params p 
	inner join r5.v_registry r on r.lpu_id = p.lpu_id
		and r.registry_enddate between p.begdate and p.enddate
		and r.registrytype_id in (2, 24, 6) 
		and r.registrystatus_id = 4 --Оплаченные
		and (r.registry_id = p.registry_id or p.registry_id is null) 
	inner join r5.v_registrydata rd on rd.registry_id = r.registry_id
	left join dbo.v_personstate person on person.person_id = rd.person_id
	left join dbo.v_polis polis on polis.polis_id = person.polis_id --действующий полис на дату окончания отчёта
		and polis.polis_begdate <= p.enddate
		and (polis.polis_enddate > p.enddate or polis.polis_enddate is null)
	left join dbo.v_omssprterr omster on omster.omssprterr_id = polis.omssprterr_id
	where (coalesce(p.katnasel_reg_id, -1) = 1 and omster.omssprterr_code = 1182) --ДАГЕСТАН
		or coalesce(p.katnasel_reg_id, -1) = 2 and coalesce(omster.omssprterr_code, -1) <> 1182
		or p.katnasel_reg_id is null)

,par as(
	select lpusection_id,
       MedStaffFact_id,
       count(evn_id) as par_cnt,
	   sum(coalesce(registrydata_itogsum, 0.00)::numeric) as v20
  from registry_all ra
  inner join dbo.v_EvnClass ec on ec.EvnClass_id = ra.ParentEvnClass_id
   and ec.evnclass_sysnick = 'EvnUslugaPar'
	group by 1,2)

,eps as(
	select 
		ls.lpusection_id,
		msf.medstafffact_id,
		count(eps.evnps_id) as v7,
		sum(coalesce(r.registrydata_itogsum, 0.00))::numeric as v20
	from params p
	inner join registry_all r on true 
	inner join dbo.v_evnsection es on es.evnsection_id = r.evn_id 
		and r.registrytype_id = 2 --поликлиника для приемника как раз
		and es.evnsection_ispaid = 2 --оплачен
		and es.evnsection_ispriem = 2 --приемное
	inner join dbo.v_evnps eps on eps.evnps_id = es.evnsection_pid 
		and (coalesce(p.is_closedata, 1) = 1 or coalesce(p.is_closedata, 1) = 2 and eps.evnps_disdate between p.begdate and p.enddate)
		and eps.prehospwaifrefusecause_id is not null
	inner join dbo.v_medstafffact msf on msf.medstafffact_id = es.medstafffact_id 
	inner join dbo.v_lpusection ls on ls.lpusection_id = es.lpusection_id 
	group by ls.lpusection_id, msf.medstafffact_id)

,get_epl as( --ТАП + ТАП стом.
	select distinct
		ls.lpusection_id,
		ls.frmpsubdivision_id,
		lu.lpuunitprofile_fid,
		epl.evnpl_id,
		epl.evnpl_isfinish,
		vizit.evnvizitpl_count,
		vizit.evnvizitpl_id,
		tc.treatmentclass_code,
		tc_last.treatmentclass_code as treatmentclass_last_code,
		msf.medstafffact_id,
		msf_last.medstafffact_id as medstafffact_last_id,
		st.servicetype_sysnick,
		coalesce(r.registrydata_itogsum, 0.00)::numeric as registrydata_itogsum,
		count(distinct case when epl.evnpl_isfinish = 2 then eu_dialysis.evnusluga_id end) as count_dialysis
	from params p 
	inner join registry_all r on 1 = 1 
	inner join dbo.v_evnvizitpl vizit on vizit.evnvizitpl_id = r.evn_id
	 and r.registrytype_id = 2 --Поликлиника 
	 and vizit.evnvizitpl_ispaid = 2 --для ТАП признак на посещении
	inner join dbo.v_evnpl epl on epl.evnpl_id = vizit.evnvizitpl_pid
	 and (coalesce(p.is_closedata, 1) = 1 or coalesce(p.is_closedata, 1) = 2 and epl.evnpl_disdate between p.begdate and p.enddate)
	inner join lateral (
		select msf.medstafffact_id 
		  from dbo.v_medstafffact msf
		where msf.medstafffact_id = vizit.medstafffact_id
		limit 1) msf on true 
	inner join dbo.v_lpusection ls on ls.lpusection_id = vizit.lpusection_id
	inner join dbo.v_lpuunit lu on lu.lpuunit_id = ls.lpuunit_id
	left join lateral (
		 select msf_last.medstafffact_id 
		 from dbo.v_medstafffact msf_last   
		 where msf_last.medstafffact_id = vizit.medstafffact_id 
		   and vizit.evnvizitpl_index = vizit.evnvizitpl_count - 1 --врач из последнего посещения
		limit 1) msf_last on true 
	left join dbo.v_treatmentclass tc on tc.treatmentclass_id = vizit.treatmentclass_id
	left join dbo.v_treatmentclass tc_last on tc_last.treatmentclass_id = vizit.treatmentclass_id 
	 and vizit.evnvizitpl_index = vizit.evnvizitpl_count - 1 --вид обращения из последнего посещения
	left join dbo.v_servicetype st on st.servicetype_id = vizit.servicetype_id
	left join lateral (
		select eu.evnusluga_id
		from dbo.v_evnusluga eu
		inner join dbo.v_uslugacomplex uc on uc.uslugacomplex_id = eu.uslugacomplex_id
		where eu.evnusluga_pid = vizit.evnvizitpl_id 
		  and uc.uslugacomplex_code in ('A18.05.002','A18.05.002.001','A18.05.002.002','A18.05.002.003','A18.05.002.004',
		                                'A18.05.002.005','A18.30.001','A18.30.001.001','A18.30.001.002','A18.30.001.003')) eu_dialysis on true 
	group by 1,2,3,4,5,6,7,8,9,10,11,12,13)

,epl as(
	select 
		get_epl.lpusection_id,
		case when get_epl.treatmentclass_code in ('1','1.1','1.2','1.3','3','3.0') or get_epl.count_dialysis > 0 then get_epl.medstafffact_last_id else get_epl.medstafffact_id end as medstafffact_id,
		count(case when get_epl.treatmentclass_code in ('3.1','2.1','2.2','2.3','2.4','2.5','2.6','3.1') then get_epl.evnvizitpl_id end) as v3,
		count(case when get_epl.evnpl_isfinish = 2 and get_epl.evnvizitpl_count > 1 and get_epl.treatmentclass_last_code in ('1','1.1','1.2','1.3','3','3.0') then get_epl.evnpl_id end) as v4,
		count(case when get_epl.servicetype_sysnick in ('home','ahome','neotl') then get_epl.evnvizitpl_id end) as v5,
		count(case when get_epl.evnpl_isfinish = 2 and get_epl.evnvizitpl_count = 1 and get_epl.treatmentclass_code in ('1','1.1','1.2','1.3','3','3.0') then get_epl.evnpl_id end) as v6,
		count(case when get_epl.evnpl_isfinish = 2 and get_epl.evnvizitpl_count = 1 and get_epl.treatmentclass_code = '1.1' then get_epl.evnpl_id end) as v7,
		count(case when get_epl.frmpsubdivision_id in (10440,10417,10150) or get_epl.lpuunitprofile_fid in (1189,1190) then get_epl.evnvizitpl_id end) as v8, --посещения в центрах здоровья
		sum(get_epl.count_dialysis) as v9,
		sum(get_epl.registrydata_itogsum)::numeric as v20
	from get_epl
	group by get_epl.lpusection_id, case when get_epl.treatmentclass_code in ('1','1.1','1.2','1.3','3','3.0') or get_epl.count_dialysis > 0 then get_epl.medstafffact_last_id else get_epl.medstafffact_id end
)

,d_prof as( --ПОВН
	select 
		ls.lpusection_id,
		dispvizit_ter.medstafffact_id,
		count(case when ls.frmpsubdivision_id in (10440,10417,10150) or lu.lpuunitprofile_fid in (1189,1190) then dispprof.evnpldispprof_id end) as v8, --карты в центрах здоровья
		count(dispprof.evnpldispprof_id) as v10,
		sum(coalesce(r.registrydata_itogsum, 0.00)::numeric) as v20
	from params p 
	inner join registry_all r on r.registrytype_id = 24 --Диспансеризация и осмотры
	inner join dbo.v_evnpldispprof dispprof on dispprof.evnpldispprof_id = r.evn_id
	 and (coalesce(p.is_closedata, 1) = 1 or coalesce(p.is_closedata, 1) = 2 and dispprof.evnpldispprof_disdate between p.begdate and p.enddate)
	 and dispprof.evnpldispprof_isfinish = 2 
	 and dispprof.evnpldispprof_ispaid = 2 
	inner join lateral ( -- подгружается посещение - прием врача-терапевта для ДВН/ПОВН
		select vdd.lpusection_id, 
		       vdd.medstafffact_id
		  from dbo.v_evnvizitdispdop vdd
		  inner join dbo.v_evnuslugadispdop eu  on eu.evnuslugadispdop_pid = vdd.evnvizitdispdop_id
		  inner join dbo.v_uslugacomplex uc  on uc.uslugacomplex_id = eu.uslugacomplex_id
		   and ((uc.uslugacomplex_name ~* 'прием' and uc.uslugacomplex_name ~* 'терап') or 
		        (uc.uslugacomplex_name ~* 'прием' and uc.uslugacomplex_name ~* 'общ') or 
				(uc.uslugacomplex_name ~* 'посещ' and uc.uslugacomplex_name ~* 'профилакт'))
		where vdd.evnvizitdispdop_pid = dispprof.evnpldispprof_id
		order by vdd.evnvizitdispdop_index desc limit 1 ) dispvizit_ter on true
	inner join dbo.v_lpusection ls on ls.lpusection_id = dispvizit_ter.lpusection_id
	inner join dbo.v_lpuunit lu on lu.lpuunit_id = ls.lpuunit_id
	group by ls.lpusection_id, dispvizit_ter.medstafffact_id)
	
,d_dop as( --ДВН
	select 
		ls.lpusection_id,
		dispvizit_ter.medstafffact_id,
		count(case when ls.frmpsubdivision_id in (10440,10417,10150) or lu.lpuunitprofile_fid in (1189,1190) then dispdop.evnpldispdop13_id end) as v8, --карты в центрах здоровья
		count(distinct case when dispdop.dispclass_id = 1 then dispdop.evnpldispdop13_id end) as v11,
		count(distinct case when dispdop.dispclass_id = 2 then dispdop.evnpldispdop13_id end) as v12,
    count(distinct case when dispdop.dispclass_id = 31 then dispdop.evnpldispdop13_id end) as udvn_1,
		count(distinct case when dispdop.dispclass_id = 32 then dispdop.evnpldispdop13_id end) as udvn_2,
		sum(coalesce(r.registrydata_itogsum, 0.00)::numeric) as v20
	from params p 
	inner join registry_all r on r.registrytype_id = 24 --Диспансеризация и осмотры
	inner join dbo.v_evnpldispdop13 dispdop on dispdop.evnpldispdop13_id = r.evn_id
	 and (coalesce(p.is_closedata, 1) = 1 or coalesce(p.is_closedata, 1) = 2 and dispdop.evnpldispdop13_disdate between p.begdate and p.enddate)
	 and dispdop.evnpldispdop13_isfinish = 2 
	 and dispdop.evnpldispdop13_ispaid = 2
	inner join lateral ( -- подгружается посещение - прием врача-терапевта для ДВН/ПОВН
		select vdd.lpusection_id, vdd.medstafffact_id
		from dbo.v_evnvizitdispdop vdd
		inner join dbo.v_evnuslugadispdop eu  on eu.evnuslugadispdop_pid = vdd.evnvizitdispdop_id
		inner join dbo.v_uslugacomplex uc  on uc.uslugacomplex_id = eu.uslugacomplex_id
		where vdd.evnvizitdispdop_pid = dispdop.evnpldispdop13_id
		  and 1 = case when ((uc.uslugacomplex_name ~* 'прием' and uc.uslugacomplex_name ~* 'терап') or 
		                     (uc.uslugacomplex_name ~* 'прием' and uc.uslugacomplex_name ~* 'общ') or 
							 (uc.uslugacomplex_name ~* 'посещ' and uc.uslugacomplex_name ~* 'профилакт')) then 1
					   when dispclass_id = 2 and uc.uslugacomplex_code = 'B04.070.005' then 1 end
		order by vdd.evnvizitdispdop_index desc limit 1 ) dispvizit_ter on true
	inner join dbo.v_lpusection ls on ls.lpusection_id = dispvizit_ter.lpusection_id
	inner join dbo.v_lpuunit lu on lu.lpuunit_id = ls.lpuunit_id
	group by ls.lpusection_id, dispvizit_ter.medstafffact_id
),

d_teen as( --МОН
	select
		ls.lpusection_id,
		dispvizit_ped.medstafffact_id,
		count(case when ls.frmpsubdivision_id in (10440,10417,10150) or lu.lpuunitprofile_fid in (1189,1190) then dispteen.evnpldispteeninspection_id end) as v8, --карты в центрах здоровья
		count(case when dispteen.dispclass_id in (9,10) then dispteen.evnpldispteeninspection_id end) as v13,
		count(case when dispteen.dispclass_id in (11,12) then dispteen.evnpldispteeninspection_id end) as v14,
		sum(coalesce(r.registrydata_itogsum, 0.00)::numeric) as v20
	from params p 
	inner join registry_all r on r.registrytype_id = 24 --Диспансеризация и осмотры
	inner join dbo.v_EvnPLDispTeenInspection dispteen on dispteen.evnpldispteeninspection_id = r.evn_id
		and (coalesce(p.is_closedata, 1) = 1 or coalesce(p.is_closedata, 1) = 2 and dispteen.evnpldispteeninspection_disdate between p.begdate and p.enddate)
		and dispteen.evnpldispteeninspection_isfinish = 2 
		and dispteen.evnpldispteeninspection_ispaid = 2
	inner join lateral ( -- подгружается посещение - прием врача-педиатра
		select vdd.lpusection_id, vdd.medstafffact_id
		from dbo.v_evnvizitdispdop vdd
		inner join dbo.v_evnuslugadispdop eu  on eu.evnuslugadispdop_pid = vdd.evnvizitdispdop_id
		inner join dbo.v_uslugacomplex uc  on uc.uslugacomplex_id = eu.uslugacomplex_id
		where vdd.evnvizitdispdop_pid = dispteen.evnpldispteeninspection_id
			and 1 = case when uc.uslugacomplex_code in ('B04.031.002','B04.031.004','B04.026.002') then 1
						 --when uc.uslugacomplex_code in ('B04.031.002','B04.031.004') and dispteen.dispclass_id in (11,12) then 1
					end
		order by vdd.evnvizitdispdop_index desc
		limit 1 
	) dispvizit_ped on true
	inner join dbo.v_lpusection ls on ls.lpusection_id = dispvizit_ped.lpusection_id
	inner join dbo.v_lpuunit lu on lu.lpuunit_id = ls.lpuunit_id
	group by ls.lpusection_id, dispvizit_ped.medstafffact_id
)

,d_orp as(
	select 
		ls.lpusection_id,
		vizitorp.medstafffact_id,
		count(case when ls.frmpsubdivision_id in (10440,10417,10150) or lu.lpuunitprofile_fid in (1189,1190) then disporp.evnpldisporp_id end) as v8, --карты в центрах здоровья
		count(case when disporp.dispclass_id = 7 and persondisporp.categorychildtype_id in (5,6,7) and disporp.childstatustype_id in (2,3,4,5,6,7) then disporp.evnpldisporp_id end) as v15,
		count(case when disporp.dispclass_id = 8 and persondisporp.categorychildtype_id in (5,6,7) and disporp.childstatustype_id in (2,3,4,5,6,7) then disporp.evnpldisporp_id end) as v16,
		count(case when disporp.dispclass_id = 3 and persondisporp.categorychildtype_id in (1,2,3,4) and disporp.childstatustype_id = 1 then disporp.evnpldisporp_id end) as v17,
		count(case when disporp.dispclass_id = 4 and persondisporp.categorychildtype_id in (1,2,3,4) and disporp.childstatustype_id = 1 then disporp.evnpldisporp_id end) as v18,
		sum(coalesce(r.registrydata_itogsum, 0.00)::numeric) as v20
	from params p 
	inner join registry_all r on r.registrytype_id = 24 --Диспансеризация и осмотры
	inner join dbo.v_evnpldisporp disporp on disporp.evnpldisporp_id = r.evn_id 
	 and (coalesce(p.is_closedata, 1) = 1 or coalesce(p.is_closedata, 1) = 2 and disporp.evnpldisporp_disdate between p.begdate and p.enddate)
	 and disporp.evnpldisporp_isfinish = 2 
	 and disporp.evnpldisporp_ispaid = 2
	inner join lateral (
		select vdo.evnvizitdisporp_id,
			   vdo.lpusection_id,
			   vdo.medstafffact_id
		from dbo.v_EvnVizitDisporp vdo 
		inner join dbo.v_evnuslugadisporp eup on eup.evnuslugadisporp_pid = vdo.evnvizitdisporp_id
		inner join dbo.v_uslugacomplex uc on uc.uslugacomplex_id = eup.uslugacomplex_id
		where vdo.evnvizitdisporp_pid = disporp.evnpldisporp_id
		  and 1 = case when disporp.dispclass_id in (3,7) and vdo.orpdispspec_id = 1 then 1 --Педиатрия
					   when disporp.dispclass_id in (4,8) and uc.uslugacomplex_code in ('B04.031.002','B04.031.004','B04.026.002') then 1 end
	order by vdo.evnvizitdisporp_index desc limit 1) vizitorp on true 
	left join lateral (
		select pdo.persondisporp_id,
			   pdo.categorychildtype_id
		  from dbo.v_persondisporp pdo 
		  join params p on 1 = 1 
		where pdo.person_id = disporp.person_id		  
		  and (pdo.persondisporp_setdate <= p.enddate or pdo.persondisporp_setdate is null)
		  and (pdo.persondisporp_disposdate > p.enddate or pdo.persondisporp_disposdate is null)
		  and categorychildtype_id in (1,2,3,4,5,6,7)
		order by pdo.persondisporp_setdate desc limit 1) persondisporp on true
	inner join dbo.v_lpusection ls on ls.lpusection_id = vizitorp.lpusection_id
	inner join dbo.v_lpuunit lu on lu.lpuunit_id = ls.lpuunit_id
	group by ls.lpusection_id, vizitorp.medstafffact_id
)

,cmp as( --СМП
	select 
		coalesce(cclc.lpusection_id, ccc.lpusection_id, r.lpusection_id) as lpusection_id,
		coalesce(cclc.medstafffact_id, ccc.medstafffact_id) as medstafffact_id,
		count(ccc.cmpcallcard_id) as v19,
		sum(coalesce(r.registrydata_itogsum, 0.00)::numeric) as v20
	  from params p 
	  inner join registry_all r on r.registrytype_id = 6 --Скорая помощь
	  inner join dbo.v_cmpclosecard cclc on cclc.cmpclosecard_id = r.evn_rid
	  inner join dbo.v_cmpcallcard ccc on ccc.cmpcallcard_id = cclc.cmpcallcard_id
	   and (coalesce(p.is_closedata, 1) = 1 or coalesce(p.is_closedata, 1) = 2 and cast(ccc.cmpcallcard_prmdt as date) between p.begdate and p.enddate)
	   and ccc.cmpcallcard_ispaid = 2
	group by 1,2
)


,query_group as(
	select
		lb.lpubuilding_name,
		ls.lpusection_name,
		msf.medpersonal_code as v1,
		msf.person_fio as v2,
		coalesce(epl.v3, 0) as v3,
		coalesce(epl.v4, 0) as v4,
		coalesce(epl.v5, 0) as v5,
		coalesce(epl.v6, 0) as v6,
		coalesce(epl.v7, 0) + coalesce(eps.v7, 0) as v7,
		coalesce(epl.v8, 0) + coalesce(d_prof.v8, 0) + coalesce(d_dop.v8, 0) + coalesce(d_teen.v8, 0) + coalesce(d_orp.v8, 0) as v8,
		coalesce(epl.v9, 0) as v9,
    coalesce(par.par_cnt, 0) as par_cnt,
		coalesce(d_prof.v10, 0) as v10,
		coalesce(d_dop.v11, 0) as v11,
		coalesce(d_dop.v12, 0) as v12,
    coalesce(d_dop.udvn_1, 0) as udvn_1,
		coalesce(d_dop.udvn_2, 0) as udvn_2,
		coalesce(d_teen.v13, 0) as v13,
		coalesce(d_teen.v14, 0) as v14,
		coalesce(d_orp.v15, 0) as v15,
		coalesce(d_orp.v16, 0) as v16,
		coalesce(d_orp.v17, 0) as v17,
		coalesce(d_orp.v18, 0) as v18,
		coalesce(cmp.v19, 0) as v19,
		(coalesce(eps.v20, 0.00)::numeric +
				 coalesce(epl.v20, 0.00)::numeric + 
  		   coalesce(d_prof.v20, 0.00)::numeric + 
         coalesce(d_dop.v20, 0.00)::numeric + 
         coalesce(d_teen.v20, 0.00)::numeric + 
         coalesce(d_orp.v20, 0.00)::numeric + 
         coalesce(cmp.v20, 0.00)::numeric +
         coalesce(par.v20, 0.00)::numeric)::numeric as v20
	from params p 
	inner join dbo.v_medstafffact msf on p.lpu_id = msf.lpu_id
	inner join dbo.v_lpusection ls on ls.lpusection_id = msf.lpusection_id
	inner join dbo.v_lpubuilding lb on lb.lpubuilding_id = ls.lpubuilding_id
	left join eps on eps.medstafffact_id = msf.medstafffact_id and eps.lpusection_id = ls.lpusection_id
	left join epl on epl.medstafffact_id = msf.medstafffact_id and epl.lpusection_id = ls.lpusection_id
	left join d_prof on d_prof.medstafffact_id = msf.medstafffact_id and d_prof.lpusection_id = ls.lpusection_id
	left join d_dop on d_dop.medstafffact_id = msf.medstafffact_id and d_dop.lpusection_id = ls.lpusection_id 
	left join d_teen on d_teen.medstafffact_id = msf.medstafffact_id and d_teen.lpusection_id = ls.lpusection_id
	left join d_orp on d_orp.medstafffact_id = msf.medstafffact_id and d_orp.lpusection_id = ls.lpusection_id
	left join cmp on cmp.medstafffact_id = msf.medstafffact_id and msf.lpusection_id = cmp.lpusection_id
  left join par on par.medstafffact_id = msf.medstafffact_id and msf.lpusection_id = par.lpusection_id
)

select 
	q. *

from query_group q

where q.v3+q.v4+q.v3+q.v4+q.v5+q.v6+q.v7+q.v8+q.v9+q.v10+q.v11+q.v12+q.v13+q.v14+q.v15+q.v16+q.v17+q.v18+q.v19+udvn_1+udvn_2+par_cnt > 0 
	
order by 
	q.lpubuilding_name, 
	q.lpusection_name, 
	q.v2
