with params as(
	select
		cast($1 as bigint) as p_Lpu_id,
		$2 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_LpuUnitType_id,
		cast($7 as bigint) as p_PrehospType_id,
		$8 as p_LeaveTypeList,
		cast($9 as bigint) as p_hasOper,
		cast($10 as bigint) as p_hasDeath,
		cast($11 as bigint) as p_EvnDie_IsAnatom,
		cast($12 as bigint) as p_IsDiagMismatch,
		dbo.GetDiagCodeByID(cast($13 as bigint)) as p_BegDiag_Code,
		dbo.GetDiagCodeByID(cast($14 as bigint)) as p_EndDiag_Code,
		cast($15 as bigint) as p_Sex_id,
		cast($16 as bigint) as p_begAge,
		cast($17 as bigint) as p_endAge,
		cast($18 as bigint) as p_onlyPens,
		cast($19 as bigint) as p_ForeignNationality,
		cast($20 as bigint) as p_CategoryStrah,
		cast($21 as bigint) as p_KLAreaStat_Code,
		cast($22 as timestamp) as p_begdate,
		cast($23 as timestamp) as p_enddate,
		cast($24 as time) as p_begtime,
		cast($25 as bigint) as p_Order413,
		cast($26 as bigint) as p_region
),

Stac_DT as(
	select
		/*stac_leave_lpu_cnt_pg.rptdesign*/
		p_begdate
			+ '1day'::interval * (1 - coalesce(p_Order413, 2))
			+ '1hour'::interval * case when p_region in (58,50) then 8 else 9 end
			 as p_begDate_s,
		p_enddate
			+ '1day'::interval * (2 - coalesce(p_Order413, 2))
			+ '1hour'::interval * case when p_region in (58,50) then 8 else 9 end
			as p_endDate_s,
		params.*
	from params
),

getLpu as(
	select
		L.Lpu_id,
		L.Lpu_Nick,
		Stac_DT.*
	from Stac_DT
	left join lateral(
		select * from rpt.GetLpu_succession_pg(p_Lpu_id, p_endDate_s, p_endDate_s)
	)L on true
)

select 
	case
		when grouping(L.Lpu_Nick) = 1
			then 'Итого'
		else L.Lpu_Nick
		end as v1,
	count(distinct EvnPS_id) as v2,
	count(distinct case when Person_Age between 0 and 4 then EvnPS_id end) as v3,
	count(distinct case when Person_Age between 5 and 9 then EvnPS_id end) as v4,
	count(distinct case when Person_Age between 10 and 14 then EvnPS_id end) as v5,
	count(distinct case when Person_Age between 15 and 19 then EvnPS_id end) as v6,
	count(distinct case when Person_Age between 20 and 24 then EvnPS_id end) as v7,
	count(distinct case when Person_Age between 25 and 29 then EvnPS_id end) as v8,
	count(distinct case when Person_Age between 30 and 34 then EvnPS_id end) as v9,
	count(distinct case when Person_Age between 35 and 39 then EvnPS_id end) as v10,
	count(distinct case when Person_Age between 40 and 44 then EvnPS_id end) as v11,
	count(distinct case when Person_Age between 45 and 49 then EvnPS_id end) as v12,
	count(distinct case when Person_Age between 50 and 54 then EvnPS_id end) as v13,
	count(distinct case when Person_Age between 55 and 59 then EvnPS_id end) as v14,
	count(distinct case when Person_Age between 60 and 64 then EvnPS_id end) as v15,
	count(distinct case when Person_Age >= 65 then EvnPS_id end) as v16
from getLpu as L
	inner join v_EvnPS as eps on eps.Lpu_id = L.Lpu_id
	inner join v_EvnSection as es on eps.EvnPS_id = es.EvnSection_pid
		and es.EvnSection_Index = es.EvnSection_Count - 1
	inner join v_Leavetype as lt on es.LeaveType_id = lt.LeaveType_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_LpuSection as ls on ls.LpuSection_id = es.LpuSection_id
	inner join v_LpuBuilding as lb on lb.LpuBuilding_id = ls.LpuBuilding_id
	inner join v_LpuUnit as lu on lu.LpuUnit_id = ls.LpuUnit_id
	left join v_EvnLeaveBase as elb on elb.EvnLeaveBase_pid = es.EvnSection_id
	left join ResultDesease as rsd on rsd.ResultDesease_id = elb.ResultDesease_id
	left Join v_EvnDie as die on die.EvnDie_rid = eps.EvnPS_id
	left join v_Diag as d on d.Diag_id = es.Diag_id
	left join v_Diag as a_d on a_d.Diag_id = eps.Diag_aid
		and lt.LeaveType_SysNick like '%die%' or rsd.ResultDesease_SysNick = 'umer'
	left join v_NationalityStatus as ns
		on ns.NationalityStatus_id = ps.NationalityStatus_id
	left join v_Polis as pol on pol.Polis_id = ps.Polis_id
		and pol.Polis_begDate < eps.EvnPS_disDate + interval'1day'
		and (pol.Polis_endDate is null or pol.Polis_endDate > eps.EvnPS_disDate)
	left join v_OrgSMO as smo on smo.OrgSMO_id = pol.OrgSmo_id
	left join v_Address as adr on adr.Address_id = coalesce(ps.PAddress_id,ps.UAddress_id)
	left join v_KLAreaStat as area on area.KLAreaStat_id = adr.KLAreaStat_id
	left join lateral(
		select
			case
				when ps.Sex_id = 1 then 60
				when ps.Sex_id = 2 then 55
				end
			+ case
				when eps.EvnPS_setDT < '2020-01-01' then 0
				when eps.EvnPS_setDT < '2022-01-01' then 1
				when eps.EvnPS_setDT < '2024-01-01' then 2
				when eps.EvnPS_setDT < '2026-01-01' then 3
				when eps.EvnPS_setDT < '2028-01-01' then 4
				else 5
				end as age
	)pensAge on true
where true
	and es.EvnSection_disDT between p_begdate_s and p_enddate_s
	and (lb.LpuFilial_id = any(('{'||p_LpuFilialsList||'}')::int8[])
		or coalesce(rtrim(p_LpuFilialsList), '') = '')
	and (ls.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
	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 (lu.LpuUnitType_id = p_LpuUnitType_id or p_LpuUnitType_id is null)
	and (eps.PrehospType_id = p_PrehospType_id or p_PrehospType_id is null)
	and (lt.LeaveType_id = any(('{'||p_LeaveTypeList||'}')::int8[])
		or coalesce(rtrim(p_LeaveTypeList), '') = '')
	and lt.LeaveType_SysNick not in ('section','ksper','dsper')
	and (p_hasOper is null
		or p_hasOper = 
			case
				when exists(
						select 1
						from v_EvnUslugaOper as euo
						where euo.EvnUslugaOper_rid = eps.EvnPS_id)
					then 2
				else 1 end)
	and (p_hasDeath is null
		or p_hasDeath = 
			case
				when (lt.LeaveType_SysNick like '%die%' or rsd.ResultDesease_SysNick = 'umer')
						and date_part('minute',age(die.EvnDie_SetDT, eps.EvnPS_setDT)) < 24*60
					then 1
				when (lt.LeaveType_SysNick like '%die%' or rsd.ResultDesease_SysNick = 'umer')
						and date_part('minute',age(die.EvnDie_SetDT, eps.EvnPS_setDT)) >= 24*60
					then 2
				end)
	and (p_EvnDie_IsAnatom is null
		or p_EvnDie_IsAnatom = 
			case
				when lt.LeaveType_SysNick like '%die%' or rsd.ResultDesease_SysNick = 'umer'
					then coalesce(die.EvnDie_IsAnatom,1)
				end)
	
	and (p_IsDiagMismatch is null
		or p_IsDiagMismatch = 
			case
				when (lt.LeaveType_SysNick like '%die%' or rsd.ResultDesease_SysNick = 'umer')
						and es.Diag_id != eps.Diag_aid
						and eps.Diag_aid is not null
					then 2
				when (lt.LeaveType_SysNick like '%die%' or rsd.ResultDesease_SysNick = 'umer')
						and es.Diag_id = eps.Diag_aid
					then 1
				end)
	and (p_BegDiag_Code is null or coalesce(a_d.Diag_Code, d.Diag_Code) >= p_BegDiag_Code)
	and (p_EndDiag_Code is null or coalesce(a_d.Diag_Code, d.Diag_Code) <= p_EndDiag_Code)
	and (p_Sex_id is null or ps.Sex_id = p_Sex_id)
	and eps.Person_Age between coalesce(p_begAge,0) and coalesce(p_endAge,200)
	and (coalesce(p_onlyPens,1) = 1 or eps.Person_Age >= pensAge.age)
	and (p_ForeignNationality is null
		or case
			when coalesce(ns.KlCountry_id, 643) = 643 then 1
			else 2 end = p_ForeignNationality)
	and (p_CategoryStrah is null or p_CategoryStrah = -1 
		or (p_CategoryStrah = 1 and smo.KLRgn_id is not null)
		--Застрахованные на территории региона и других субъектов РФ
		or (p_CategoryStrah = 2 and smo.KLRgn_id = p_region)
		--Застрахованные на территории региона
		or (p_CategoryStrah = 3 and smo.KLRgn_id <> p_region)
		--Застрахованные на территории других субъектов РФ
		or (p_CategoryStrah = 4 and pol.Polis_id is null)) --Незастрахованные
	and (p_KLAreaStat_Code is null or area.KLAreaStat_Code = p_KLAreaStat_Code)
group by rollup (L.Lpu_Nick)
order by 
	grouping(L.Lpu_Nick),
	L.Lpu_Nick
