with params as(
	select
cast ($1 as varchar) as  p_lpulist,
cast ($2 as varchar)  as  p_lpufilialslist,
cast($3 as bigint) as p_lpubuilding_id,
cast($4 as bigint) as p_lpuunit_id, 
cast($5 as bigint) as p_lpusection_id, 
cast($6 as bigint) as p_lpusectionprofile_id, 
cast($7 as bigint) as p_begdiag, 
cast($8 as bigint) as p_enddiag, 
cast($9 as bigint) as p_sex_id, 
cast($10 as bigint) as p_agefrom, 
cast($11 as bigint) as p_ageto, 
cast(to_timestamp($12,'dd.mm.yyyy') as timestamp) as p_begdate, 
cast(to_timestamp($13,'dd.mm.yyyy') as timestamp) as p_enddate, 
cast(null as time) as p_begtime,
cast($14 as bigint) as p_order413, 
cast($15 as bigint) as p_isguzonly,
coalesce(dbo.GetDiagCodeByID(cast($16 as bigint)), 'A.') as p_diagfrom,
coalesce(dbo.GetDiagCodeByID(cast($17 as bigint)), 'Z99.9') as p_diagto,
 array ['die','psumer','kslet','ksletitar', 'section','ksper','dsper',
'psreab','other','ksother','dsother','ksperitar','diepp','ksdie','ksdiepp','dsdie','dsdiepp',
'cmpdiebrig','cmpdieavt','cmpdiedo','trdiesmp','trdiesmpc','stdie']  as sysNicks,
array ['7.6.2','8.1','8.2','8.3','8.4','8.5','8.6','8.8.1','8.9',
'8.9.1','9.2.1','9.3','9.3.1','9.3.2','10.7.5','11.5','13.6','15.6','15.9','15.10',
'15.11','18.2','18.6','22.0'] as rowNums
),

dp as (
	select
		p_begdate + (1 - p_order413) * interval '1 day' + concat(case when dbo."getregion"() in (58,50) then coalesce(date_part('hour',p_begtime),8) 
																	else coalesce(date_part('hour',p_begtime),9) end,'hour')::interval 
														  + concat(coalesce(date_part('minute',p_begtime),0),'minute')::interval
			as Stac_begDT,
		p_enddate + (2 - p_order413) * interval '1 day' + concat(case when dbo."getregion"() in (58,50) then coalesce(date_part('hour',p_begtime),8)
																	else coalesce(date_part('hour',p_begtime),9) end,'hour')::interval 
														  + concat(coalesce(date_part('minute',p_begtime),0),'minute')::interval
			as Stac_endDT
--		params.*
	from params
),

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 params
	left 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 1=1
		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)
		 -- 11. Государственная собственность, 13. Собственность субъектов Российской Федерации, 14. Муниципальная собственность
),
/*
LS as(
	select 
		LS.LpuSection_id,
		LS.LpuSectionBedProfile_id
	from LpuP
		left join params on 1=1	
		inner join v_LpuSection as LS on LS.Lpu_id = LpuP.Lpu_id
		inner join v_LpuUnit as LU on LU.LpuUnit_id = LS.LpuUnit_id
		inner join v_LpuBuilding as LB on LB.LpuBuilding_id = LU.LpuBuilding_id 
		inner join v_LpuSectionProfile LSP on LSP.LpuSectionProfile_id = LS.LpuSectionProfile_id
	where (LS.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (LU.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and LU.LpuUnitType_SysNick = 'stac'
		and (LB.LpuBuilding_id=p_LpuBuilding_id or p_LpuBuilding_id is null)
		and LS.LpuSection_pid is null
		and LS.LpuSectionProfile_id != 75
		and (LSP.LpuSectionProfile_id = p_LpuSectionProfile_id or p_LpuSectionProfile_id is null)
		and (LB.LpuFilial_id in(
			select id::int8 as id from unnest(string_to_array(p_lpufilialslist, ',')) as id)
			or coalesce(p_lpufilialslist,'') = '')
),
*/
RD as(
	select 
		RD.RepDiag_Row,
		RD.RepDiag_Text1,
		RD.RepDiag_Text2,
		RD.RepDiag_Text3,
		RD.RepDiag_FromDiag,
		RD.RepDiag_ToDiag,
		RD.RepDiag_SysNick,
		RD.RepDiag_Part,
		RD.Region_id 
	from rpt.v_RepDiag as RD 
	where RD.RepDiag_SysNick = 'frm14_2020' and RepDiag_Part = 2001
),

EPS as(
	select
		EPS.Lpu_id,
		EPS.Evn_id as EvnPS_id,
		EPS.EvnPS_IsCont,
		EPS.Person_id,
		EPS.PreHospType_id,
		EPS.PrehospArrive_id,
		dbo.AgeYMD(PS.Person_BirthDay, EPS.Evn_setDT, 1) as Age,
		PS.Sex_id,
		LT.LeaveType_SysNick,
		RD.ResultDesease_SysNick,
		-- Алгоритм только для круглосут. в режиме календар. за всю КВС
				rpt.Calc_KoykoDay(coalesce(coalesce(r_3.EvnPS_setDate,r_2.EvnPS_setDate,r_1.EvnPS_setDate),EPS.Evn_setDT),
						ESec.EvnSection_disDT,
						0,--По календарю
						case when Lu.LpuUnitType_SysNick = 'stac' and cast(ESec.EvnSection_setDate as date) != cast(ESec.EvnSection_disDT as date)
							then 0 else 1 end, --Тип стационара КС=0 иначе 1
						null) as KoikoDni,	
			
--		D.Diag_Code,
		EvnDie.Diag_aid,
		ES.Diag_id as ES_Diag_id,
		ES.LpuSection_id,
		EPS.Diag_id,
		EvnDie.EvnDie_IsAnatom,
		coalesce(EvnDie.Lpu_aid, -1) as Lpu_aid
	from LpuP 
		left join params on 1=1
		left join dp on 1=1
		inner join EvnPS as EPS on EPS.Lpu_id = LpuP.Lpu_id
		inner join v_Person_all as PS on PS.PersonEvn_id = EPS.PersonEvn_id 
			and EPS.Server_id = PS.Server_id
		
		inner join lateral(
			select
				ES.EvnSection_id,
				ES.LpuSection_id,
				ES.LeaveType_id,
				ES.Diag_id,
				ES.LpuSectionProfile_id
			from v_EvnSection as ES 
			left join params on 1=1
			left join dp on 1=1
			where EPS.Evn_id = ES.EvnSection_pid
				and (ES.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
				and ES.EvnSection_Index = ES.EvnSection_count - 1
				and coalesce(ES.EvnSection_IsPriem, 1) = 1
				and ES.EvnSection_disDT >=  dp.Stac_begDT 
				and ES.EvnSection_disDT < dp.Stac_endDT
			order by ES.EvnSection_disDT desc
			limit 1
		)ES on true
				inner join v_LpuSection LS_1 on LS_1.LpuSection_id = ES.LpuSection_id and LS_1.Lpu_id = LpuP.Lpu_id
                and LS_1.LpuSection_pid is null
                and LS_1.LpuSectionProfile_id != 75
				inner join v_lpuUnit LU on LU.LpuUnit_id=LS_1.LpuUnit_id
                and LU.LpuUnitType_SysNick = 'stac'
		left join v_LpuBuilding as LB on LB.LpuBuilding_id = LU.LpuBuilding_id 
		left join v_LpuSectionProfile LSP on LSP.LpuSectionProfile_id = LS_1.LpuSectionProfile_id
			
	left join lateral (select EVS_1.EvnPS_setDate
						,EVS_1.EvnPS_NumCard
						,EVS_1.EvnPS_setTime
						,EVS_1.EvnPS_id
						,EVS_1.EvnPS_disDT
						,EVS_1.EvnPS_IsCont
			from dbo.v_EvnPS as EVS_1
			left join dp on 1=1
			left join v_EvnSection ES on ES.EvnSection_pid=EVS_1.EvnPS_id
										and ES.EvnSection_Index = ES.EvnSection_COUNT - 1
										and coalesce(ES.EvnSection_IsPriem, 1) = 1
										and ES.EvnSection_disDT < dp.Stac_endDT
			left join v_LeaveType as ESLT  on ESLT.LeaveType_id = ES.LeaveType_id
			where 1 = 1
			  and EVS_1.Person_id = EPS.Person_id 
			  and EVS_1.lpu_id = EPS.lpu_id
			  and EPS.EvnPS_IsCont = 2
			  and cast(EVS_1.EvnPS_disDT as date) = cast(EPS.Evn_setDT as date)
				and ESLT.LeaveType_sysnick like '%section%'
			order by EVS_1.EvnPS_setDate limit 1
			) as r_1 on true	
			
	left join lateral (select EVS_2.EvnPS_setDate
						,EVS_2.EvnPS_NumCard
						,EVS_2.EvnPS_setTime
						,EVS_2.EvnPS_id
						,EVS_2.EvnPS_disDT
						,EVS_2.EvnPS_IsCont
			from dbo.v_EvnPS as EVS_2
			left join dp on 1=1
			left join v_EvnSection ES on ES.EvnSection_pid=EVS_2.EvnPS_id
										and ES.EvnSection_Index = ES.EvnSection_COUNT - 1
										and coalesce(ES.EvnSection_IsPriem, 1) = 1
										and ES.EvnSection_disDT < dp.Stac_endDT
			left join v_LeaveType as ESLT  on ESLT.LeaveType_id = ES.LeaveType_id
			where 1 = 1
			  and EVS_2.Person_id = EPS.Person_id 
			  and EVS_2.lpu_id = EPS.lpu_id
			  and r_1.EvnPS_IsCont = 2
			  and cast(EVS_2.EvnPS_disDT as date) = cast(r_1.EvnPS_setDate as date)
				and ESLT.LeaveType_sysnick like '%section%'
			order by EVS_2.EvnPS_setDate limit 1
			) as r_2 on true 
			
	left join lateral (select EVS_3.EvnPS_setDate
						,EVS_3.EvnPS_NumCard
						,EVS_3.EvnPS_setTime
						,EVS_3.EvnPS_id
						,EVS_3.EvnPS_disDT
			from dbo.v_EvnPS as EVS_3
			left join dp on 1=1
			left join v_EvnSection ES on ES.EvnSection_pid=EVS_3.EvnPS_id
										and ES.EvnSection_Index = ES.EvnSection_COUNT - 1
										and coalesce(ES.EvnSection_IsPriem, 1) = 1
										and ES.EvnSection_disDT < dp.Stac_endDT
			left join v_LeaveType as ESLT  on ESLT.LeaveType_id = ES.LeaveType_id

			where 1 = 1
			  and EVS_3.Person_id = EPS.Person_id 
			  and EVS_3.lpu_id = EPS.lpu_id
			  and r_2.EvnPS_IsCont = 2
			  and cast(EVS_3.EvnPS_disDT as date) = cast(r_2.EvnPS_setDate as date)
				and ESLT.LeaveType_sysnick like '%section%'
			order by EVS_3.EvnPS_setDate limit 1
			) as r_3 on true
	
				inner join dbo.v_EvnSection as ESec  on ESec.EvnSection_pid = EPS.Evn_id
  and ESec.EvnSection_disDT < dp.Stac_endDT
  and ESec.EvnSection_disDT >= dp.Stac_begDT
  and coalesce(ESec.EvnSection_IsPriem, 1) = 1
  and ESec.EvnSection_Index = ESec.EvnSection_Count - 1 
		inner join v_LeaveType as LT  on LT.LeaveType_id = ES.LeaveType_id
		left join v_EvnDie as EvnDie  on EvnDie.EvnDie_pid = ES.EvnSection_id
		left join EvnLeaveBase as ELB  on ELB.Evn_pid = ES.EvnSection_id
		left join v_ResultDesease rd  on ELB.ResultDesease_id = rd.ResultDesease_id
	where EPS.Evn_disDt >= dp.Stac_begDT
		and EPS.Evn_disDt < dp.Stac_endDT
		and dbo.AgeYMD(PS.Person_BirthDay, EPS.Evn_setDT, 1) between coalesce(p_agefrom, 0) and coalesce(p_ageto, 200)
		and (PS.Sex_id = p_sex_id or p_sex_id is null)
		and ELB.Evn_deleted = 1
		and EPS.PrehospWaifRefuseCause_id is null
		and EPS.Evn_deleted = 1
		
		and (LS_1.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (LU.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (LB.LpuBuilding_id=p_LpuBuilding_id or p_LpuBuilding_id is null)
		and (LSP.LpuSectionProfile_id = p_LpuSectionProfile_id or p_LpuSectionProfile_id is null)
		and (LB.LpuFilial_id in(
			select id::int8 as id from unnest(string_to_array(p_lpufilialslist, ',')) as id)
			or coalesce(p_lpufilialslist,'') = '')
		
),

EPSd as (
select EPS.*, D.Diag_Code
from EPS
left join params on 1=1
inner join v_Diag D  on D.Diag_id = coalesce(EPS.Diag_aid, ES_Diag_id)
where D.Diag_Code between coalesce(dbo.GetDiagCodeByID(p_begdiag), 'A00')
		and coalesce(dbo.GetDiagCodeByID(p_enddiag), 'Z99.9')
)

select 
	RD.repdiag_row  as "RepDiag_Row",
	RD.RepDiag_Text1 as "RepDiag_Text1",
	RD.RepDiag_Text2 as "RepDiag_Text2",
	RD.RepDiag_Text3 as "RepDiag_Text3",
	count(distinct
		case
			when EPS.Age < 18 
					and (EPS.LeaveType_SysNick != all(sysNicks))
					and (EPS.ResultDesease_SysNick != 'umer')
					and (EPS.LeaveType_SysNick not like '%stac')
				then EPS.EvnPS_id end) as val22,
	count(distinct
		case
			when EPS.Age < 18 
					and (EPS.LeaveType_SysNick != all(sysNicks))
					and (EPS.ResultDesease_SysNick != 'umer')
					and (EPS.LeaveType_SysNick not like '%stac')
					and coalesce(EPS.PreHospType_id, 2) <> 2 
				then EPS.EvnPS_id end) as val23,
	count(distinct
		case
			when EPS.Age < 18 
					and (EPS.LeaveType_SysNick != all(sysNicks))
					and (EPS.ResultDesease_SysNick != 'umer')
					and (EPS.LeaveType_SysNick not like '%stac')
					and coalesce(EPS.PreHospType_id, 2) <> 2 
					and EPS.PrehospArrive_id = 2 
				then EPS.EvnPS_id end) as val24,
	count(distinct
		case
			when EPS.Age < 1 
					and (EPS.LeaveType_SysNick != all(sysNicks))
					and (EPS.ResultDesease_SysNick != 'umer')
					and (EPS.LeaveType_SysNick not like '%stac')
				then EPS.EvnPS_id end) as val25, 
	coalesce(sum(
		case
			when EPS.Age < 18 
					and (EPS.LeaveType_SysNick != all(sysNicks))
					and (EPS.ResultDesease_SysNick != 'umer')
					and (EPS.LeaveType_SysNick not like '%stac')
				then EPS.KoikoDni end), 0) as val26, 
	coalesce(sum(
		case
			when EPS.Age < 1  
					and (EPS.LeaveType_SysNick != all(sysNicks))
					and (EPS.ResultDesease_SysNick != 'umer')
					and (EPS.LeaveType_SysNick not like '%stac')
				then EPS.KoikoDni end), 0) as val27, -- поле добавили в 2014 году Проведено выписанн- ыми койко-дней из них (из гр.22): в воз-расте до 1 года
	case
		when RD.RepDiag_Text2 != all(rowNums)
			then count(distinct
				case
					when EPS.Age < 18 
							and (EPS.LeaveType_SysNick in ('die','psumer','kslet','ksletitar','diepp','ksdie','ksdiepp','dsdie','dsdiepp','cmpdiebrig','cmpdieavt','cmpdiedo','trdiesmp','trdiesmpc','stdie')
								or EPS.ResultDesease_SysNick = 'umer' )
						then EPS.EvnPS_id end) end as val28,
	case
		when RD.RepDiag_Text2 != all(rowNums)
			then count(distinct
				case
					when EPS.Age < 18 
							and (EPS.LeaveType_SysNick in ('die','psumer','kslet','ksletitar','diepp','ksdie','ksdiepp','dsdie','dsdiepp','cmpdiebrig','cmpdieavt','cmpdiedo','trdiesmp','trdiesmpc','stdie')
								or EPS.ResultDesease_SysNick = 'umer' )
							and EPS.Diag_aid is not null 
							and EPS.EvnDie_IsAnatom = 2 
							and EPS.Lpu_aid <> 13002390 
						then EPS.EvnPS_id end) end as val29,
	case
		when RD.RepDiag_Text2 != all(rowNums)
			then count(distinct
				case
					when EPS.Age < 18 
							and (EPS.LeaveType_SysNick in ('die','psumer','kslet','ksletitar','diepp','ksdie','ksdiepp','dsdie','dsdiepp','cmpdiebrig','cmpdieavt','cmpdiedo','trdiesmp','trdiesmpc','stdie')
								or EPS.ResultDesease_SysNick = 'umer' )
							and EPS.Diag_aid <> EPS.Diag_id
							and EPS.Diag_aid is not null 
							and EPS.Lpu_aid <> 13002390 
						then EPS.EvnPS_id end) end as val30,

	case
		when RD.RepDiag_Text2 != all(rowNums)
			then count(distinct
				case
					when EPS.Age < 18 
							and (EPS.LeaveType_SysNick in ('die','psumer','kslet','ksletitar','diepp','ksdie','ksdiepp','dsdie','dsdiepp','cmpdiebrig','cmpdieavt','cmpdiedo','trdiesmp','trdiesmpc','stdie')
								or EPS.ResultDesease_SysNick = 'umer' )
							and EPS.Diag_aid is not null 
							and EPS.EvnDie_IsAnatom = 2 
							and EPS.Lpu_aid = 13002390 --51152 Данные берутся из КВС. Патологоанатомическая экспертиза. Организация: ГКУЗОТ "ПЕРМСКОЕ КРАЕВОЕ БЮРО СУДЕБНО-МЕДИЦИНСКОЙ ЭКСПЕРТИЗЫ"). Сделать только для Перми, по остальным на проектировщиков. (Согласовано с Галиной Сергеевной)
						then EPS.EvnPS_id end) end as val31,
	case
		when RD.RepDiag_Text2 != all(rowNums)
			then count(distinct
				case
					when EPS.Age < 18
							and (EPS.LeaveType_SysNick in ('die','psumer','kslet','ksletitar','diepp','ksdie','ksdiepp','dsdie','dsdiepp','cmpdiebrig','cmpdieavt','cmpdiedo','trdiesmp','trdiesmpc','stdie')
								or EPS.ResultDesease_SysNick = 'umer' )
							and EPS.Diag_aid <> EPS.Diag_id
							and EPS.Diag_aid is not null 
							and EPS.Lpu_aid = 13002390 --51152 Данные берутся из КВС. Патологоанатомическая экспертиза. Организация: ГКУЗОТ "ПЕРМСКОЕ КРАЕВОЕ БЮРО СУДЕБНО-МЕДИЦИНСКОЙ ЭКСПЕРТИЗЫ"). Сделать только для Перми, по остальным на проектировщиков. (Согласовано с Галиной Сергеевной)
			then EPS.EvnPS_id end) end as val32,
	case
		when RD.RepDiag_Text2 != all(rowNums)
			then count(distinct
				case
					when EPS.Age < 1 
							and (EPS.LeaveType_SysNick in ('die','psumer','kslet','ksletitar','diepp','ksdie','ksdiepp','dsdie','dsdiepp','cmpdiebrig','cmpdieavt','cmpdiedo','trdiesmp','trdiesmpc','stdie')
								or EPS.ResultDesease_SysNick = 'umer' )
						then EPS.EvnPS_id end) end as val33
from EPSd EPS
	right join RD on EPS.Diag_Code between RD.RepDiag_fromDiag and RD.RepDiag_ToDiag
	left join params on 1=1	
group by 
	RD.RepDiag_Row,
	RD.RepDiag_Text1,
	RD.RepDiag_Text2,
	RD.RepDiag_Text3,
	params.rownums
order by RD.RepDiag_Row

