with param as (select 
$1::text as p_lpulist, 
$2::text as p_lpufilialslist, 
cast($3 as int8) as p_lpubuilding_id, 
cast($4 as int8) as p_lpuunit_id, 
cast($5 as int8) as p_lpusection_id, 
cast($6 as int8) as p_lpusectionprofile_id, 
cast(to_timestamp($7,'dd.mm.yyyy') as timestamp) as p_begdate, 
cast(to_timestamp($8,'dd.mm.yyyy') as timestamp) as p_enddate, 
cast($9 as int8) as p_order413, 
cast($10 as int8) as p_isguzonly
  ),
LpuP as(
	select
		L.Lpu_id,
		dateadd('hour',
			case when dbo.GetRegion() in (58,50) then 8 else 9 end,
			dateadd('day', (1 - coalesce(p_Order413, 2)), p_begdate)) as Stac_begDT,
		dateadd('hour', 
			case when dbo.GetRegion() in (58,50) then 8 else 9 end,
			dateadd('day', (2 - coalesce(p_Order413, 2)), p_enddate)) as Stac_endDT,
		cast(case when dbo.GetRegion() in (58,50) then '08:00' else '09:00' end as time) as StacTime
	from param 
   inner join rpt.getlpu_successionl_pg(p_LpuList,
		dateadd('day', case when coalesce(p_Order413, 2) = 2 then -1 else 0 end, p_begdate), 
		dateadd('day', case when coalesce(p_Order413, 2) = 1 then 1 else 0 end, p_enddate)
	) as L on true
		inner join Org as O on L.Org_id = O.Org_id
			and case
					when dbo.GetRegion() = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5)
						then 2 else 1 end >=  coalesce(p_IsGUZOnly,1)
),

LpuSection_W as(
	select 
		LS.LpuSection_id
	from param 
   inner join LpuP on true
		inner join v_LpuSection LS on LS.Lpu_id = LpuP.Lpu_id
		inner join v_LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
		inner join v_LpuBuilding LB on LB.LpuBuilding_id = LU.LpuBuilding_id 
		left join v_LpuSectionProfile LSProf on LS.LpuSectionProfile_id = LSProf.LpuSectionProfile_id
	where (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (LS.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (LS.LpuBuilding_id = p_lpubuilding_id or p_lpubuilding_id is null)
		and LS.LpuSectionProfile_id != 75
		and LU.LpuUnitType_SysNick = 'stac'
		and (LB.LpuFilial_id in(
			select id::int8 as id from unnest(string_to_array(p_lpufilialslist, ',')) as id)
			or coalesce(p_lpufilialslist,'') = '')
),

st as(
	select
		t_table.v0,
		t_table.v1,
		t_table.v2,
		t_table.v3,
		t_table.DiagFrom,
		t_table.DiagTo,
		t_table.ChildTermType_id
	from(values
		(1,'Операций при врожденных пороках развития (ВПТ) - всего:','1','','Q00.','Q79.9',-2),
		(1,'Операций при врожденных пороках развития (ВПТ) - всего:','1','','H35.1','H35.1',-2),
		(2,'из них: ВПР системы кровообращения','1.1','Q20-Q28','Q20.','Q28.9',-2),
		(3,'   из них: у родившихся в сроки 22-37 недель беременности','1.1.1','','Q20.','Q28.9',2),
		(4,'ВПР мочеполовой системы','1.2','Q50-Q64','Q50.','Q64.9',-2),
		(5,'   из них: у родившихся в сроки 22-37 недель беременности','1.2.1','','Q50.','Q64.9',2),
		(6,'ВПР нервной системы','1.3','Q00-Q07','Q00.','Q07.9',-2),
		(7,'   из них: у родившихся в сроки 22-37 недель беременности','1.3.1','','Q00.','Q07.9',2),
		(8,'ВПР органов зрения','1.4','Q10-Q15','Q10.','Q15.9',-2),
		(9,'   из них: у родившихся в сроки 22-37 недель беременности','1.4.1','','Q10.','Q15.9',2),
		(10,'ВПР органов дыхания','1.5','Q30-Q34','Q30.','Q34.9',-2),
		(11,'   из них: у родившихся в сроки 22-37 недель беременности','1.5.1','','Q30.','Q34.9',2),
		(12,'расщелина губы и неба','1.6','Q35-Q37','Q35.','Q37.9',-2),
		(13,'   из них: у родившихся в сроки 22-37 недель беременности','1.6.1','','Q35.','Q37.9',2),
		(14,'ретинопатия недоношенных (родившихся в сроки 22-37 недель беременности)','1.7','H35.1','H35.1','H35.1',2)
	)as t_table(v0,v1,v2,v3,DiagFrom,DiagTo,ChildTermType_id)
),

UK AS(
  select  distinct
           r.RepTableRows_Code    
           ,rl.RepTableLinks_UslCode
           ,rl.RepTableLinks_FromDiag
           ,rl.RepTableLinks_ToDiag 
  from rpt.v_reptablerows r
  inner join rpt.v_reptablelinks rl on r.RepTableRows_id = rl.RepTableRows_id
  where r.RepTableRows_SysNick = 'frm14_4000_2020'
      and strpos(r.RepTableRows_Code,'.') = 0 

),

EPS as(
	select 
		EUO.EvnUslugaOper_Kolvo,
		EUO.EvnUslugaOper_IsVMT,
		EUO.EvnUslugaOper_Index,
		evnagg.EvnAgg_pid,
		LT.LeaveType_SysNick,
		D.Diag_Code,
		Newborn.ChildTermType_id
	from param 
   inner join LpuP on true
		inner join v_EvnPS as EPS on EPS.Lpu_id = LpuP.Lpu_id
			and EPS.PrehospWaifRefuseCause_id is null
		inner join v_Person_all as PS on PS.PersonEvn_id = EPS.PersonEvn_id 
			and EPS.Server_id = PS.Server_id
  inner join v_EvnSection as ES on ES.EvnSection_pid = EPS.EvnPS_id
  left join v_LpuSectionProfile lsp on lsp.LpuSectionProfile_id=es.LpuSectionProfile_id
  	inner join LpuSection_W as LS on LS.LpuSection_id = ES.LpuSection_id
		inner join v_LeaveType as LT on LT.LeaveType_id = EPS.LeaveType_id
		inner join v_Diag as D on D.Diag_id = EPS.Diag_id
		inner join v_EvnUslugaOper as EUO on EUO.EvnUslugaOper_rid = EPS.EvnPS_id
			and EUO.Lpu_id = LpuP.Lpu_id 
		inner join v_UslugaComplex as UC on EUO.UslugaComplex_id = UC.UslugaComplex_id
		inner join UK on UK.RepTableLinks_UslCode = UC.UslugaComplex_Code
		left join v_PersonNewborn as Newborn on Newborn.Person_id = EPS.Person_id

		left join lateral(
			select EvnAgg_pid 
			from v_evnagg as evnagg 
			where EUO.evnuslugaoper_id = evnagg.evnagg_pid
			limit 1
		)evnagg on true
	where true
		and EPS.EvnPS_disDT >=  LpuP.Stac_begDT 
		and EPS.EvnPS_disDT < LpuP.Stac_endDT
		and PS.Person_BirthDay < dateadd('day', 2, p_enddate)
		and coalesce(PS.Person_deadDT, PS.Person_closeDT, p_enddate) >=  dateadd('day', -1, p_begdate)
		and dbo.AgeYMD(PS.Person_BirthDay, EPS.EvnPS_setDate, 1) < 1
		and (D.Diag_Code between UK.RepTableLinks_FromDiag and UK.RepTableLinks_ToDiag or UK.RepTableLinks_FromDiag is NULL) 
    and (ES.LpuSectionProfile_id=p_lpusectionprofile_id or p_lpusectionprofile_id is null)
		and (dbo.GetRegion() = 2
			or (UK.RepTableRows_Code not in ('2','8','15')
				or UC.UslugaComplex_Code not in(
					'A16.12.008.001','A16.12.008.002','A16.12.008.003','A16.12.028.007',
					'A16.12.041.004','A16.03.035.001','A16.03.051.003','A16.04.008','A16.04.008.001',
					'A16.04.010','A16.04.010.001','A16.04.029','A16.04.032','A16.04.032.001',
					'A16.04.040','A16.04.041','A16.04.043','A16.12.008.004','A16.12.011.001',
					'A16.12.011.005','A16.12.011.006','A16.12.011.009','A16.12.026.005','A16.12.026.006',
					'A16.12.026.007','A16.12.028.007','A16.12.038.008','A16.12.038.009','A16.12.041.004',
					'A16.12.048','A16.12.054.001','A22.04.005','A22.04.007','A16.23.085',
					'A16.23.085.001','A16.12.026.015','A16.12.026.017','A16.12.026.020',
					'A16.12.026.021')
				or (UC.UslugaComplex_Code in(
					'A16.12.008.001','A16.12.008.002','A16.12.008.003','A16.12.028.007',
					'A16.12.041.004','A16.03.035.001','A16.03.051.003','A16.04.008','A16.04.008.001',
					'A16.04.010','A16.04.010.001','A16.04.029','A16.04.032','A16.04.032.001',
					'A16.04.040','A16.04.041','A16.04.043','A16.12.008.004','A16.12.011.001',
					'A16.12.011.005','A16.12.011.006','A16.12.011.009','A16.12.026.005','A16.12.026.006',
					'A16.12.026.007','A16.12.028.007','A16.12.038.008','A16.12.038.009','A16.12.041.004',
					'A16.12.048','A16.12.054.001','A22.04.005','A22.04.007','A16.23.085',
					'A16.23.085.001','A16.12.026.015','A16.12.026.017','A16.12.026.020',
					'A16.12.026.021')
					and (UK.RepTableRows_Code in ('2')
							and coalesce(lsp.LpuSectionProfile_Name, '') like '%нейрохир%'
						or UK.RepTableRows_Code in ('8','15') 
							and coalesce(lsp.LpuSectionProfile_Name, '') not like '%нейрохир%')
				)
			)
		)
)

select
	st.v0,
	st.v1,
	st.v2,
	st.v3,
	coalesce(SUM(EvnUslugaOper_Kolvo), 0) as val4, --Число операций
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
				then EvnUslugaOper_Kolvo end), 0) as val5, --с ВМП
	coalesce(SUM(
		case
			when EPS.EvnAgg_pid > 0 
				then EvnUslugaOper_Kolvo end), 0) as val6, --осложнения
	coalesce(SUM(
		case
			when EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar') 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.EvnUslugaOper_Kolvo end), 0) as val7 --умерли
from EPS 
	right join st on EPS.Diag_Code between st.DiagFrom and st.DiagTo
		and (EPS.ChildTermType_id = st.ChildTermType_id or st.ChildTermType_id = -2)
group by 1,2,3,4
order by 1

