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 time) as p_stactime, 
cast($10 as int8) as p_order413, 
cast($11 as int8) as p_isguzonly
  ),
LpuP as(
	select
		L.Lpu_id,
		(case when coalesce(p_Order413, 2) = 2 then - interval '1 day' else interval '0 day' end + p_begDate) + 
	  coalesce(p_StacTime, cast('09:00' as time)) as Stac_begDT,
		(case when coalesce(p_Order413, 2) = 2 then interval '0 day' else interval '1 day' end + p_endDate) + 
	  coalesce(p_StacTime, cast('09:00' as time)) as Stac_endDT
	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,'') = '')
),


EPS as(
	select
		EPS.EvnPS_setDate,
		case 
	  when (coalesce(flt.LeaveType_Code, 0) in (105,106,205,206) or RD.ResultDesease_SysNick = 'umer') then 2
	  else 1 end isDie,
		PS.Person_BirthDay,
		EUO.Person_id,
		PS.Sex_id,
		EvnUslugaOper_Index,
		case when EUO.EvnUslugaOper_Kolvo = 0 then 1 else EUO.EvnUslugaOper_Kolvo end EvnUslugaOper_Kolvo,
		EvnUslugaOper_IsVMT,
		EvnUslugaOPer_isLazer,
		EUO.EvnUslugaOper_IsRadGraf,
		EvnUslugaOPer_isKriogen,
		EvnUslugaOPer_isEndoskop,
		EvnUslugaOper_IsMicrSurg,
		D.Diag_id,
		D.Diag_Code,
		UC_G.UslugaComplex_id,
		UC_G.UslugaComplex_Code,
		lsp.LpuSectionProfile_Name,
		Newborn.ChildTermType_id,
		EUO.AnesthesiaClass_id,
		UC.UslugaKind_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_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_Person_all as PS on PS.PersonEvn_id = EPS.PersonEvn_id
			and PS.Server_id = EPS.Server_id
		inner join v_LeaveType as LT on LT.LeaveType_id = EPS.LeaveType_id
  left join fed.LeaveType flt on lt.LeaveType_fedid = flt.LeaveType_id
  left join v_EvnLeaveBase ELB on ELB.EvnLeaveBase_pid = ES.EvnSection_id
  left join v_ResultDesease rd on ELB.ResultDesease_id = rd.ResultDesease_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 v_UslugaComplex UC_G on UC_G.UslugaComplex_id = UC.UslugaComplex_2011id 
		left join v_PersonNewborn as Newborn on Newborn.Person_id = EPS.Person_id
	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 (ES.LpuSectionProfile_id=p_lpusectionprofile_id or p_lpusectionprofile_id is null)
),

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_2021'
    and strpos(r.RepTableRows_Code,'.') = 0 

)

select 
	coalesce(count(distinct EPS.Person_id), 0) as v1,--оперировано больных
	coalesce(count(distinct 
		case
			when dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 0 and 17 
				then EPS.Person_id end), 0) as v2,--из них дети до 17 лет включительно 
	coalesce(count(distinct 
		case
			when (rpt.workage(EPS.Person_BirthDay,EPS.Sex_id,EPS.EvnPS_setDate)=1) 
				then EPS.Person_id end), 0) as v13,--старше трудоспособного возраста 
	coalesce(SUM(
		case
			when EPS.EvnUslugaOPer_isLazer = 2 
				then EvnUslugaOPer_Kolvo end), 0) as v3,
				--из общего числа операций проведено операций с использованием лазерной аппаратуры
	coalesce(SUM(
		case
			when EPS.EvnUslugaOPer_isKriogen = 2 
				then EvnUslugaOPer_Kolvo end), 0) as v4,
				--из общего числа операций проведено операций с использованием криогенной аппаратуры
	coalesce(SUM(
		case
			when EPS.EvnUslugaOPer_isEndoskop = 2 
				then EvnUslugaOPer_Kolvo end), 0) as v5,
				--из общего числа операций проведено операций с использованием эндоскопической аппаратуры
	coalesce(SUM(distinct
		case
			when (dbo.GetRegion() = 2 and EPS.UslugaComplex_Code in ('A16.20.041','A16.20.041.001') and EPS.EvnUslugaOPer_isEndoskop = 2)
					or EPS.UslugaComplex_Code like 'A16.20.041.001' 
				then EvnUslugaOPer_Kolvo end), 0) as v6,--в том числе стерилизации женщин
	coalesce(SUM(
		case
			when EPS.AnesthesiaClass_id in (3,4,5,6) 
				then EvnUslugaOPer_Kolvo end), 0) as v7,--число общих анастезий оперированным
	coalesce(count(distinct 
		case
			when EPS.AnesthesiaClass_id in (3,4,5,6) 
					and EPS.isDie=2
				then EPS.Person_id end), 0) as v8, -- умерло в результате общей анестезии
	coalesce(SUM(
		case
			when EPS.UslugaKind_id = 20
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) = 0 
				then EvnUslugaOPer_Kolvo end), 0) as v12,
				--из общего числа операций@на сердце по поводу врождённых пороков-детям до 1 года
	coalesce(SUM(
		case
			when (dbo.GetRegion() = 2 and EPS.UslugaComplex_Code in (
					'A16.12.003','A16.12.026.012','A16.12.004.009','A16.12.026','A16.12.026.001',
					'A16.12.026.002','A16.12.026.003','A16.12.026.004','A16.12.026.005',
					'A16.12.026.006','A16.12.026.007','A16.12.026.008','A16.12.026.009',
					'A16.12.026.010','A16.12.026.013','A16.12.027','A16.12.028','A16.12.028.001',
					'A16.12.028.002','A16.12.028.004','A16.12.028.005','A16.12.028.006',
					'A16.12.028.007','A16.12.028.008','A16.12.028.009','A16.12.028.010',
					'A16.12.028.011','A16.12.028.012','A16.12.028.013','A16.12.028.014',
					'A16.12.029','A16.12.030','A16.12.031','A16.12.032','A16.12.034.001',
					'A16.12.035.001','A16.12.037','A16.12.041','A16.12.041.001','A16.12.041.002',
					'A16.12.041.004','A16.12.041.006','A16.12.042','A16.12.050','A16.12.051',
					'A16.12.051.001','A16.12.051.002','A16.12.054.002','A16.12.059','A16.12.062',
					'A16.12.063','A16.14.020','A16.14.020.001','A16.14.020.002','A16.14.020.006',
					'A16.14.024.001','A16.14.032','A16.15.022'))
					or EPS.EvnUslugaOper_IsRadGraf = 2
				then EvnUslugaOper_Kolvo end), 0) as v14 --из общего числа операций с ренгеноскопией
from EPS 
	inner join UK on UK.RepTableLinks_UslCode = EPS.UslugaComplex_Code
   and (EPS.Diag_Code between UK.RepTableLinks_FromDiag and UK.RepTableLinks_ToDiag or UK.RepTableLinks_FromDiag is null) 
		and (dbo.GetRegion() = 2
			or (UK.RepTableRows_Code not in ('2','8','15')
				or EPS.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 (EPS.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(EPS.LpuSectionProfile_Name, '') like '%нейрохир%'
						or UK.RepTableRows_Code in ('8','15')
							and coalesce(EPS.LpuSectionProfile_Name, '') not like '%нейрохир%'
					)
				)
			)
		)

