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,'') = '')
),
EPS as(
	select
		EPS.EvnPS_setDate,
		LT.LeaveType_SysNick,
		PS.Person_BirthDay,
		PS.Person_id,
		PS.Sex_id,
		EDH.EvnDirectionHistologic_id,
		EvnUslugaOper_Index,
		EvnUslugaOper_Kolvo,
		EvnUslugaOper_IsVMT,
		evnagg.EvnAgg_pid,
		D.Diag_id,
		D.Diag_Code,
		UC.UslugaComplex_id,
		UC.UslugaComplex_Code,
		lsp.LpuSectionProfile_Name,
		OpD.OperDiff_Code,
		BSS.BirthSpecStac_OutcomPeriod
	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 EvnSection_pid = EvnPS_id
			and ES.EvnSection_Index = ES.EvnSection_count - 1
    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 = ES.LeaveType_id
		inner join v_Diag as D on D.Diag_id = ES.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 
		left join OperDiff as OpD on EUO.OperDiff_id = OpD.OperDiff_id 

		left join lateral(
			select EDH.EvnDirectionHistologic_id
			from v_EvnDirectionHistologic as EDH 
			where EDH.EvnPS_id = EPS.EvnPS_id
				and EUO.UslugaComplex_id = EDH.UslugaComplex_id
		)EDH on true

		left join lateral(
			select EvnAgg_pid 
			from v_evnagg as evnagg 
			where EUO.evnuslugaoper_id = evnagg.evnagg_pid
			limit 1
		)evnagg on true

		left join lateral(
			select BSS.BirthSpecStac_OutcomPeriod
			from v_BirthSpecStac as BSS 
			where BSS.EvnSection_id = ES.EvnSection_id
			limit 1
		)BSS on true
	where EPS.EvnPS_disDT >=  LpuP.Stac_begDT 
		and EPS.EvnPS_disDT < LpuP.Stac_endDT
		and ES.EvnSection_disDT >=  LpuP.Stac_begDT 
		and ES.EvnSection_disDT < LpuP.Stac_endDT
		and EvnSection_Index = EvnSection_count - 1
    and (ES.LpuSectionProfile_id=p_lpusectionprofile_id or p_lpusectionprofile_id is null)
),

UK AS(
select  r.RepTableRows_id
           ,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'
  union all 
  select  0 as RepTableRows_id
           ,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 
),

RD AS(
  select  r.RepTableRows_id
          ,r.RepTableRows_Row 
           ,r.RepTableRows_Code
           ,r.RepTableRows_Text1
  from rpt.v_reptablerows r
  where r.RepTableRows_SysNick = 'frm14_4000_2020'
  union all
  select 0 as RepTableRows_id
       ,0 as RepTableRows_Row
       ,'1' as RepTableRows_Code 
	   ,'Всего операций' as RepTableRows_Text1 
)

select 
	RD.RepTableRows_Row as "RepTableRows_Row",
	RD.RepTableRows_Code as "RepTableRows_Code",
	RD.RepTableRows_Text1 as "RepTableRows_Text1",
	coalesce(SUM(EvnUslugaOper_Kolvo), 0) as val3,
	coalesce(SUM(
		case
			when dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 0 and 14 
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val4,
	coalesce(SUM(
		case
			when dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) < 1 
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val41,
	coalesce(SUM(
		case
			when dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 15 and 17 
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val42,
	coalesce(SUM(
		case
			when (rpt.workage(EPS.Person_BirthDay,EPS.Sex_id,EPS.EvnPS_setDate)=1) 
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val43,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
				then EvnUslugaOper_Kolvo else 0 end), 0) as val5,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 0 and 14 
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val6,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and dbo.AgeYMD( EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) < 1 
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val61,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and dbo.AgeYMD( EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 15 and 17 
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val62,
	coalesce(SUM(
		case
			when (rpt.workage(EPS.Person_BirthDay,EPS.Sex_id,EPS.EvnPS_setDate)=1)
					and EPS.EvnUslugaOper_IsVMT = 2  
				then EvnUslugaOPer_Kolvo else 0 end), 0) as val63,
	coalesce(SUM(
		case
			when EPS.EvnAgg_pid > 0 
				then EvnUslugaOper_Kolvo end), 0) as val7,
	coalesce(SUM(
		case
			when EPS.EvnAgg_pid > 0 
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 0 and 14 
				then EvnUslugaOper_Kolvo end), 0) as val8,
	coalesce(SUM(
		case
			when EPS.EvnAgg_pid > 0
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) < 1 
				then EvnUslugaOper_Kolvo end), 0) as val81,
	coalesce(SUM(
		case
			when EPS.EvnAgg_pid > 0
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 15 and 17 
				then EvnUslugaOper_Kolvo end), 0) as val82,
	coalesce(SUM(
		case
			when EPS.EvnAgg_pid > 0 
					and (rpt.workage(EPS.Person_BirthDay,EPS.Sex_id,EPS.EvnPS_setDate)=1) 
				then EvnUslugaOper_Kolvo end), 0) as val83,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.EvnAgg_pid > 0 
				then EvnUslugaOper_Kolvo end), 0) as val9,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.EvnAgg_pid > 0 
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 0 and 14 
				then EvnUslugaOper_Kolvo end), 0) as val10,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.EvnAgg_pid > 0 
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) < 1 
				then EvnUslugaOper_Kolvo end), 0) as val101,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.EvnAgg_pid > 0 
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 15 and 17 
				then EvnUslugaOper_Kolvo end), 0) as val102,
	coalesce(SUM(
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.evnagg_pid > 0 
					and (rpt.workage(EPS.Person_BirthDay,EPS.Sex_id,EPS.EvnPS_setDate)=1) 
				then EvnUslugaOper_Kolvo end), 0) as val103,
	count(distinct 
		case
			when EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar') 
					and EPS.Person_id > 0
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val11,
	count(distinct 
		case
			when EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar') 
					and EPS.Person_id > 0
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 0 and 14 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val12,
	count(distinct 
		case
			when EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar')
					and EPS.Person_id > 0
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) < 1 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val121,
	count(distinct 
		case
			when EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar') 
					and EPS.Person_id > 0
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 15 and 17 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val122,
	count(distinct 
		case
			when EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar')
					and EPS.Person_id > 0
					and (rpt.workage(EPS.Person_BirthDay,EPS.Sex_id,EPS.EvnPS_setDate)=1) 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val123,
	count(distinct 
		case
			when EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar')
					and EPS.EvnUslugaOper_Index = 0 
					and EPS.Person_id > 0
					and EPS.EvnUslugaOper_IsVMT = 2 ---96772
				then EPS.Person_id end) as val13,
	count(distinct 
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar')
					and EPS.Person_id > 0
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 0 and 14 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val14,
	count(distinct 
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar')
					and EPS.Person_id > 0
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) < 1 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val141,
	count(distinct 
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar')
					and EPS.Person_id is not null
					and dbo.AgeYMD(EPS.Person_BirthDay, EPS.EvnPS_setDate, 1) between 15 and 17 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val142,
	count(distinct 
		case
			when EPS.EvnUslugaOper_IsVMT = 2 
					and EPS.LeaveType_SysNick in ('die','psumer','ksdie','dsdie','kslet','ksletitar')
					and EPS.Person_id is not null
					and (rpt.workage(EPS.Person_BirthDay,EPS.Sex_id,EPS.EvnPS_setDate)=1) 
					and EPS.EvnUslugaOper_Index = 0 
				then EPS.Person_id end) as val143,
	coalesce(SUM(
		case
			when EPS.Diag_Code between 'C00%' and 'D09.%' --'C00%' and 'D48.%'
				then EvnUslugaOper_Kolvo else 0 end), 0) as val144,
	count(distinct 
		case
			when EPS.EvnDirectionHistologic_id is not null 
				then EPS.EvnDirectionHistologic_id end) as val145
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) 
  right join RD on RD.RepTableRows_id = UK.RepTableRows_id
		and (dbo.GetRegion() = 2
			or (RD.RepTableRows_Code not in(
			'2','2.2','2.2.1','2.2.1.1','2.4','2.4.1','2.4.1.1','2.4.1.2','2.4.2','2.4.2.1',
			'2.4.2.2','2.7','2.8','8','8.1','8.1.1','8.1.1.1','8.1.1.2','8.1.1.3','8.1.1.3.1','15',
			'15.5','15.8','7.2','7.3') --#106438
				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','A16.10.003.028', 'A16.10.003.029','A16.10.003.030',
					'A16.10.003.031','A16.10.003.032','A16.10.035.001','A16.10.037','A16.10.038','A16.12.042.001',
					'A16.12.042.002','A16.10.028.002')
				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','A16.10.003.028', 'A16.10.003.029','A16.10.003.030',
					'A16.10.003.031','A16.10.003.032','A16.10.035.001','A16.10.037','A16.10.038','A16.12.042.001',
					'A16.12.042.002','A16.10.028.002')
					and ((RD.RepTableRows_Code in(
						'2','2.2','2.2.1','2.2.1.1','2.4','2.4.1','2.4.1.1','2.4.1.2','2.4.2','2.4.2.1','2.4.2.2','2.7','2.8') 
						and coalesce(EPS.LpuSectionProfile_Name, '') like '%нейрохир%')
						or (RD.RepTableRows_Code in (
							'8','8.1','8.1.1','8.1.1.1','8.1.1.2','8.1.1.3','8.1.1.3.1','15','15.5','15.8')
							and coalesce(EPS.LpuSectionProfile_Name, '') not like '%нейрохир%')
					or (RD.RepTableRows_Code in ('7.2') and EPS.Diag_Code between 'Q20' and 'Q25.9' ) --#106438
					or (RD.RepTableRows_Code in ('7.3') and EPS.Diag_Code not between 'Q20' and 'Q25.9' ) --#106438
					)
				) 
			)
		)
		and (left(RD.RepTableRows_Code, 4) not in ('14.4','14.5','14.8','14.9')
			or left(RD.RepTableRows_Code, 4) in ('14.4','14.8') and coalesce(EPS.BirthSpecStac_OutcomPeriod, 1) >=  22
			or left(RD.RepTableRows_Code, 4) in ('14.5','14.9') and coalesce(EPS.BirthSpecStac_OutcomPeriod, 1) < 22) 
		and (EPS.UslugaComplex_Code <> 'A16.26.086.001' 
			or EPS.UslugaComplex_Code = 'A16.26.086.001'
			and (RD.RepTableRows_Code <> '4.8' or coalesce(EPS.OperDiff_Code, 0) in (5,6)))
		and (EPS.UslugaComplex_Code not in (
			'A16.23.034','A16.23.034.001','A16.23.034.002','A16.23.034.003','A16.23.034.004',
			'A16.23.034.005','A16.23.034.006','A16.23.034.007','A16.23.036','A16.23.036.001',
			'A16.23.036.002','A16.23.036.003','A16.23.036.004')
			or (EPS.UslugaComplex_Code in(
				'A16.23.034','A16.23.034.001','A16.23.034.002','A16.23.034.003','A16.23.034.004','A16.23.034.005',
				'A16.23.034.006','A16.23.034.007','A16.23.036','A16.23.036.001','A16.23.036.002','A16.23.036.003',
				'A16.23.036.004')
				and ((RD.RepTableRows_Code in ('2.2','2.2.1','2.2.1.1') and EPS.Diag_Code not between 'I60' and 'I60.9')
					or RD.RepTableRows_Code not in ('2.2','2.2.1','2.2.1.1')
				)
			)
		)
		-- #101407 убираем старые условия - рассчитываем что все по диагнозам и кодам услуг есть в таблицах     
group by 
  RD.RepTableRows_Row,
  RD.RepTableRows_Code,
  RD.RepTableRows_Text1
order by RD.RepTableRows_Row

