with recursive params as(
	select
		cast($1 as bigint) as p_Lpu_id,
		cast($2 as bigint) as p_LpuBuilding_id,
		cast($3 as bigint) as p_LpuUnit_id,
		cast($4 as bigint) as p_LpuSection_id,
		cast($5 as bigint) as p_LpuSectionBedProfile_id,
		cast($6 as bigint) as p_ReceptFinance_id,
		$7 as p_PrivilegeType_list,
		cast($8 as timestamp) as p_begdate,
		cast($9 as timestamp) as p_enddate,
		cast($10 as bigint) as p_obj_to_cnt_type
),

getLpu as(
	select
		L.Lpu_id,
		params.*
	from params
		inner join lateral(
			select * from rpt.GetLpu_succession(p_Lpu_id, p_begDate, p_endDate)L
	)L on true
),

eps_r as(
	select
		1 as search_level,
		eps.EvnPS_id,
		eps.EvnPS_id as p_EvnPS_id,
		eps.EvnPS_IsCont,
		eps.EvnPS_setDT,
		eps.EvnPS_setDate,
		eps.EvnPS_disDate,
		eps.Person_Age as age,
		eps.PreHospDirect_id,
		eps.PreHospType_id,
		eps.Diag_id,
		eps.Diag_did,
		eps.Diag_aid,
		eps.Lpu_id,
		eps.LpuSection_id,
		ls.LpuSectionProfile_id,
		ls.LpuSection_Code||'. '||ls.LpuSection_Name as ls_full_name,
		flt.LeaveType_Code,
		rd.ResultDesease_SysNick::text,
		es.EvnSection_disDT,
		es.Person_id,
		es.PersonEvn_id,
		es.Server_id,
		p_PrivilegeType_List,
		p_ReceptFinance_id,
		p_Begdate,
		p_EndDate,
		p_obj_to_cnt_type
	from getLpu as L
		join v_EvnPS as eps on eps.Lpu_id = L.Lpu_id
		join v_EvnSection as es on eps.EvnPS_id = es.EvnSection_rid
		join v_LpuSection as ls on ls.LpuSection_id = es.LpuSection_id
		join dbo.LpuSectionBedProfile as lsbp
			on lsbp.LpuSectionBedProfile_id = es.LpuSectionBedProfile_id
		left join dbo.LeaveType as lt on eps.LeaveType_id = lt.LeaveType_id
		left join fed.LeaveType as flt on lt.LeaveType_fedid = flt.LeaveType_id
		left join v_EvnLeaveBase as elb on elb.EvnLeaveBase_pid = es.EvnSection_id
		left join dbo.ResultDesease as rd on elb.ResultDesease_id = rd.ResultDesease_id
	where eps.EvnPS_disdate between p_Begdate and p_EndDate
		and eps.PrehospWaifRefuseCause_id is null
		and es.EvnSection_Index = es.EvnSection_Count -1
		and coalesce(es.EvnSection_IsPriem, 1) = 1
		and (flt.LeaveType_Code not in (104,204) or rd.ResultDesease_SysNick = 'umer')
		and (ls.LpuUnit_id = p_LpuUnit_id or p_LpuUnit_id is null)
		and (ls.LpuSection_id = p_LpuSection_id or p_LpuSection_id is null)
		and (ls.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
		and (lsbp.LpuSectionBedProfile_id = p_LpuSectionBedProfile_id
			or p_LpuSectionBedProfile_id is null)

	union all select
		search_level + 1 as search_level,
		eps.EvnPS_id,
		eps_r.EvnPS_id as p_EvnPS_id,
		eps.EvnPS_IsCont,
		eps.EvnPS_setDT,
		eps.EvnPS_setDate,
		null::timestamp as EvnPS_disDate,
		null::int as age,
		null::int8 as PreHospDirect_id,
		null::int8 as PreHospType_id,
		null::int8 as Diag_id,
		null::int8 as Diag_did,
		null::int8 as Diag_aid,
		eps.Lpu_id,
		null::int8 as LpuSection_id,
		null::int8 as LpuSectionProfile_id,
		null as ls_full_name,
		null as LeaveType_Code,
		null as ResultDesease_SysNick,
		null::timestamp as EvnSection_disDT,
		eps.Person_id,
		null::int8 as PersonEvn_id,
		null::int8 as Server_id,
		null as p_PrivilegeType_List,
		null::int8 as p_ReceptFinance_id,
		null::timestamp as p_Begdate,
		null::timestamp as p_EndDate,
		null::int8 as p_obj_to_cnt_type
	from eps_r
		join v_EvnPS as eps on eps.Person_id = eps_r.Person_id
		join v_EvnSection as es on eps.EvnPS_id = es.EvnSection_rid
		join dbo.LeaveType as lt on eps.LeaveType_id = lt.LeaveType_id
	where eps_r.EvnPS_IsCont = 2
		and eps.Lpu_id = eps_r.Lpu_id
		and eps.EvnPS_disDate = eps_r.EvnPS_setDate
		and es.evnSection_Index = es.EvnSection_Count - 1
		and coalesce(es.EvnSection_IsPriem, 1) = 1
		and lt.LeaveType_sysnick ilike any(array['%section%', 'dsper', 'ksper'])
),

eps as(
	select
		eps_r.search_level,
		eps_r.EvnPS_id,
		eps_r.EvnPS_setDT,
		eps_r.EvnPS_setDate,
		eps_r.EvnPS_disDate,
		eps_r.age,
		eps_r.PreHospDirect_id,
		eps_r.PreHospType_id,
		eps_r.Diag_id,
		eps_r.Diag_did,
		eps_r.Diag_aid,
		eps_r.LpuSection_id,
		eps_r.LpuSectionProfile_id,
		eps_r.ls_full_name,
		eps_r.LeaveType_Code,
		eps_r.ResultDesease_SysNick,
		eps_r.EvnSection_disDT,
		eps_r.Person_id,
		eps_r.PersonEvn_id,
		eps_r.Server_id,
		p_PrivilegeType_List,
		p_ReceptFinance_id,
		p_Begdate,
		p_EndDate,
		p_obj_to_cnt_type,
		min(eps_r.EvnPS_setDT) over(partition by p_EvnPS_id) as first_EvnPS_setDT
	from eps_r
),

rep_data as(
	select
		eps.ls_full_name,
		case p_obj_to_cnt_type
			when 1 then eps.EvnPS_id
			when 2 then eps.Person_id
			end as obj_to_cnt,
		pp.is_vov,
		pp.is_inv_vov,
		pp.is_war,
		pp.is_inv,
		sub.pens_age,
		sub.has_repeat,
		eps.age,
		ps.Sex_id,
		eps.PreHospDirect_id,
		eps.PreHospType_id,
		sub.has_agg,
		eps.LeaveType_Code in (105,106,205,206) or eps.ResultDesease_SysNick = 'umer' as is_die,
		eps.LeaveType_Code in (101,201) as is_leave,
		case 
			when eps.LeaveType_Code in (105,106,205,206) or eps.ResultDesease_SysNick = 'umer'
				then datediff('minute', eps.first_EvnPS_setDT,eps.EvnSection_disDT)
			end as death_diff,
		eup.EvnUslugaOper_id,
		eps.Diag_id,
		eps.Diag_did,
		coalesce(
			eps.Diag_aid,
			case
				when die.EvnDie_isAnatom = 2
					then coalesce(ds.Diag_mid, ds.Diag_tid, ds.Diag_iid)
				end) as Diag_aid,
		die.EvnDie_isAnatom,
		lsp.LpuSectionProfile_Name,
		eup.OperType_id,
		case 
			when eup.EvnUSlugaOper_Index = 0
				then datediff('day', eps.first_EvnPS_setDT, eup.EvnUslugaOper_setDate)
			end as oper_diff,
		case
			when coalesce(eup.EvnUslugaOper_Kolvo,0) = 0 and eup.EvnUslugaOper_id is not null
				then 1
			else eup.EvnUslugaOper_Kolvo
			end as EvnUslugaOper_Kolvo
	from eps
		join dbo.LpuSectionProfile as lsp
			on lsp.LpuSectionProfile_id = eps.LpuSectionProfile_id
		join v_Person_all as ps on ps.PersonEvn_id = eps.PersonEvn_id 
			and ps.Server_id = eps.Server_id
		left join v_EvnUslugaOper as eup on eup.EvnUslugaOper_rid = eps.EvnPS_id
		left join v_EvnDie as die on eps.EvnPS_id = die.EvnDie_rid
			and die.EvnDie_setDate between p_Begdate and p_EndDate
		left join v_DeathSvid as ds on ds.Person_id = eps.Person_id
			and ds.DeathSvid_IsActual = 2

		left join lateral(
			select
				bool_or(pp.PrivilegeType_Code = 20) as is_vov,
				bool_or(pp.PrivilegeType_Code in (10,11)) as is_inv_vov,
				bool_or(pp.PrivilegeType_Code = 30) as is_war,
				bool_or(pp.PrivilegeType_Code in (81,82,83)) as is_inv,
				bool_or(pp.PrivilegeType_Code = any(('{'||p_PrivilegeType_list||'}')::int8[])) as pp_is_filtred,
				bool_or(pt.ReceptFinance_id = p_ReceptFinance_id) as fin_is_filtred
			from v_PersonPrivilege as pp
				join v_PrivilegeType as pt on pt.PrivilegeType_id = pp.PrivilegeType_id
			where eps.Person_id = pp.Person_id
				and pp.PersonPrivilege_begDate <= eps.EvnPS_disDate
				and (pp.PersonPrivilege_endDate >= eps.EvnPS_disDate
					or pp.PersonPrivilege_endDate is null)
		)pp on true
		
		left join lateral(
			select 
				exists(
					select 1
					from v_EvnPS as p_eps
					where p_eps.Person_id = eps.Person_id
						and year(p_eps.EvnPS_disDate) = year(eps.EvnPS_disDate)
						and p_eps.Diag_id = eps.Diag_id
						and p_eps.EvnPS_id != eps.EvnPS_id
						and p_eps.LpuSection_id = eps.LpuSection_id
					) as has_repeat, 
				exists(
					select 1
					from v_EvnAgg as agg
					where agg.EvnAgg_pid = eup.EvnUslugaOper_id
					) as has_agg,
				case
					when ps.Sex_id = 1 then 60
					when ps.Sex_id = 2 then 55
					end
					+ case
						when eps.EvnPS_setDate < '2020-01-01' then 0
						when eps.EvnPS_setDate < '2022-01-01' then 1
						when eps.EvnPS_setDate < '2024-01-01' then 2
						when eps.EvnPS_setDate < '2026-01-01' then 3
						when eps.EvnPS_setDate < '2028-01-01' then 4
						else 5
						end as pens_age
		)sub on true
	where eps.search_level = 1
		and (coalesce(p_PrivilegeType_List,'') = '' or pp.pp_is_filtred)
		and (coalesce(p_ReceptFinance_id,3) = 3 or pp.fin_is_filtred)
)

select
	case
		when grouping(rep_data.ls_full_name) = 0
			then rep_data.ls_full_name
		else 'ИТОГО:'
		end as v2,--отделение
	count(distinct rep_data.obj_to_cnt) as v3,--всего пролечено больных
	count(distinct case when rep_data.is_vov then rep_data.obj_to_cnt end) as v4,--участники ВОВ
	count(distinct case when rep_data.is_inv_vov then rep_data.obj_to_cnt end) as v5,--Инвалиды ВОВ
	count(distinct case when rep_data.is_war then rep_data.obj_to_cnt end) as v6,--воины-авганцы
	count(distinct case when rep_data.is_inv then rep_data.obj_to_cnt end) as v7,--инвалиды 1-3 группы
	count(distinct case when rep_data.age >= rep_data.pens_age then rep_data.obj_to_cnt end) as v8,--пенсионеры
	count(distinct case when rep_data.age between 15 and 17 then rep_data.obj_to_cnt end) as v9,--подростки всего
	count(distinct case when rep_data.age between 15 and 17 and rep_data.Sex_id = 1 then rep_data.obj_to_cnt end) as v10, --подростки юноши
	count(distinct case when rep_data.age between 15 and 49 and rep_data.Sex_id = 2 then rep_data.obj_to_cnt end) as v11  ,--женщины детородного возраста
	-- скорой мед.помощи
	coalesce(count(distinct case when rep_data.PreHospDirect_id = 5 then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct rep_data.obj_to_cnt) as float),0),
		0) * 100 as v12,
	-- повторно
	coalesce(count(distinct case when rep_data.has_repeat then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct rep_data.obj_to_cnt) as float),0),
		0) * 100 as v13,
	--14 экстренно
	coalesce(count(distinct case when rep_data.PreHospType_id in (1,3) then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct rep_data.obj_to_cnt) as float),0),
		0) * 100 as v14,
	-- при операциях	
	coalesce(sum(case when rep_data.has_agg then rep_data.EvnUslugaOper_Kolvo end)
		/nullif(cast(sum(rep_data.EvnUslugaOper_Kolvo) as float),0),
		0) * 100 as v15,
	--Летальность всего
	coalesce(count(distinct case when rep_data.is_die then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct rep_data.obj_to_cnt) as float),0),
		0) * 100 as v16,
	--летальность в 1-е сутки
	coalesce(count(distinct
		case when rep_data.death_diff < 24*60 then rep_data.obj_to_cnt end) 
		/nullif(cast(count(distinct
			case when rep_data.is_die or rep_data.is_leave then rep_data.obj_to_cnt end) as float),0),
		0) * 100 as v17,
	-- летатьность послеоперационная
	coalesce(count(distinct
		case when rep_data.is_die and rep_data.EvnUslugaOper_id is not null then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct case when rep_data.EvnUslugaOper_id is not null then rep_data.obj_to_cnt end) as float),0),
		0) * 100 as v18,
	--гр.19 Расхождения диагнозов стац.и направ. в ЛПУ
	coalesce(count(distinct case when rep_data.Diag_id != rep_data.Diag_did then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct case when rep_data.Diag_did is not null then rep_data.obj_to_cnt end) as float),0),
		0) * 100 as v19,
	--гр.20 Расхождения диагнозов стац. и паталогоанат
	coalesce(count(distinct 
		case 
			when rep_data.Diag_id != rep_data.Diag_aid and rep_data.Diag_aid is not null
				then rep_data.obj_to_cnt end)
			/nullif(cast(count(distinct case when rep_data.EvnDie_isAnatom = 2 then rep_data.obj_to_cnt end) as float),0),
		0) * 100 as v20,
	--Гр. 21 Хирургическая активность
	coalesce(count(distinct 
		case 
			when rep_data.EvnUslugaOper_id is not null and rep_data.LpuSectionProfile_Name ilike '%хирург%'
				then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct 
			case when  rep_data.LpuSectionProfile_Name ilike '%хирург%' then rep_data.obj_to_cnt end) as float),0),
		0) * 100 as v21,
	--Гр. 22 Плановых операций
	coalesce(count(distinct case when rep_data.OperType_id = 1 then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct 
			case when rep_data.EvnUslugaOper_id is not null then rep_data.obj_to_cnt end) as float), 0),
		0) * 100 as v22,
	/*Гр.23 % Средняя длит-ть предопер. периода плановых больных
	 разность между датой движения (EvnSection_setDate)
	 и датой операции (EvnUslugaOper_setDate) 
	 деленная на количество операций для пациентов с плановым типом госпитализации*/
	coalesce(sum(case when rep_data.PreHospType_id = 2 then rep_data.oper_diff end)
		/nullif(cast(count(distinct 
			case 
				when rep_data.PreHospType_id = 2 and rep_data.EvnUslugaOper_id is not null 
					then rep_data.obj_to_cnt
				end) as float),0), 
		0) as v23,
	--Гр. 24 «% вскрытий»
	coalesce(count(distinct case when rep_data.EvnDie_isAnatom = 2 then rep_data.obj_to_cnt end)
		/nullif(cast(count(distinct case when rep_data.is_die then rep_data.obj_to_cnt end)as float),0),
		0) * 100 as v24
from rep_data
group by rollup(rep_data.ls_full_name)
order by
	grouping(rep_data.ls_full_name),
	rep_data.ls_full_name
