with params as(
	select
		cast($1 as bigint) as p_lpu_id,
		$2 as p_lpufilialslist,
		cast($3 as timestamp) as p_begdate,
		cast($4 as timestamp) as p_enddate
),

Lpu_W as(
	select
		L.Lpu_id,
		case
			when Org.Okfs_id between 1 and 4 then 'GOS' -- 10. РОССИЙСКАЯ СОБСТВЕННОСТЬ, 11. Государственная собственность, 12. Федеральная собственность, 13. Собственность субъектов Российской Федерации
			when Org.Okfs_id = 5 then 'MUN' -- 14. Муниципальная собственность
			end as lpu_type,
		params.*
	from params
		inner join v_Lpu as L on L.Lpu_id = p_Lpu_id 
			or p_Lpu_id is null and coalesce(L.Lpu_Istest, 1) = 1
			or L.Lpu_pid = p_Lpu_id
		inner join dbo.Org on Org.Org_id = L.Org_id
	where L.Lpu_begDate <= p_EndDate + interval '1 day'
		and coalesce(L.Lpu_endDate, p_BegDate) >= p_BegDate
)

select 
	count(distinct
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id = 1
				then eps.EvnPS_id end) as v2001_1,
	count(distinct
		case
			when sub.is_not_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
				then eps.EvnPS_id end) as v2001_2,
	count(distinct
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id = 1 and L.lpu_type = 'GOS'
				then eps.EvnPS_id end) as v2001_3,
	count(distinct
		case
			when sub.is_not_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
					and L.lpu_type = 'GOS'
				then eps.EvnPS_id end) as v2001_4,
	count(distinct
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id = 1 and L.lpu_type = 'MUN'
				then eps.EvnPS_id end) as v2001_5,
	count(distinct
		case
			when sub.is_not_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
					and L.lpu_type = 'MUN'
				then eps.EvnPS_id end) as v2001_6,
	count(distinct
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8)
				then eps.Person_id end) as v2001_7,
	count(distinct
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8) and L.lpu_type = 'GOS'
				then eps.Person_id end) as v2001_8,
	count(distinct
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8) and L.lpu_type = 'MUN'
				then eps.Person_id end) as v2001_9,
	coalesce(sum(
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id = 1
				then sub.day_cnt end),0) as v2002_1,
	 coalesce(sum(
		case
			when sub.is_not_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
				then sub.day_cnt end),0) as v2002_2,
	coalesce(sum(
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id = 1 and L.lpu_type = 'GOS'
				then sub.day_cnt end),0) as v2002_3,
	coalesce(sum(
		case
			when sub.age between 0 and 17 and sub.is_not_die = 2 and ns.LegalStatusVZN_id = 1
					and L.lpu_type = 'GOS'
				then sub.day_cnt end),0) as v2002_4,
	coalesce(sum(
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id = 1 and L.lpu_type = 'MUN'
				then sub.day_cnt end),0) as v2002_5,
	coalesce(sum(
		case
			when sub.is_not_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
					and L.lpu_type = 'MUN'
				then sub.day_cnt end),0) as v2002_6,
	coalesce(sum(
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8)
				then sub.day_cnt end),0) as v2002_7,
	coalesce(sum(
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8) and L.lpu_type = 'GOS'
				then sub.day_cnt end),0) as v2002_8,
	coalesce(sum(
		case
			when sub.is_not_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8) and L.lpu_type = 'MUN'
				then sub.day_cnt end),0) as v2002_9,
	count(distinct
		case
			when sub.is_die = 2 and ns.LegalStatusVZN_id = 1
				then eps.EvnPS_id end) as v2003_1,
	count(distinct
		case
			when sub.is_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
				then eps.EvnPS_id end) as v2003_2,
	count(distinct
		case
			when sub.is_die = 2 and ns.LegalStatusVZN_id = 1 and L.lpu_type = 'GOS' 
				then eps.EvnPS_id end) as v2003_3,
	count(distinct
		case
			when sub.is_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
					and L.lpu_type = 'GOS'
				then eps.EvnPS_id end) as v2003_4,
	count(distinct
		case
			when sub.is_die = 2 and L.lpu_type = 'MUN' and ns.LegalStatusVZN_id = 1
				then eps.EvnPS_id end) as v2003_5,
	count(distinct
		case
			when sub.is_die = 2 and sub.age between 0 and 17 and ns.LegalStatusVZN_id = 1
					and L.lpu_type = 'MUN'
				then eps.EvnPS_id end) as v2003_6,
	count(distinct
		case
			when sub.is_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8)
				then eps.Person_id end) as v2003_7,
	count(distinct
		case
			when sub.is_die = 2 and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8) and L.lpu_type = 'GOS' 
				then eps.Person_id end) as v2003_8,
	count(distinct
		case
			when sub.is_die = 2 and L.lpu_type = 'MUN' and ns.LegalStatusVZN_id in (2, 3, 4, 5, 6, 7, 8)
				then eps.Person_id end) as v2003_9
from lpu_w L
	inner join v_EvnSection as es on es.Lpu_id = L.Lpu_id
	inner join v_EvnPS as eps on eps.EvnPS_id = es.EvnSection_pid
	inner join LeaveType as lt on lt.LeaveType_id = es.LeaveType_id
	inner join v_LpuSection as ls on ls.LpuSection_id = es.LpuSection_id
	inner join v_LpuUnit as lu on lu.LpuUnit_id = ls.LpuUnit_id
	inner join v_LpuBuilding as lb on lb.LpuBuilding_id = ls.LpuBuilding_id
	inner join v_Personstate as ps on ps.Person_id = eps.Person_id
	inner join v_Nationalitystatus as ns on ns.Nationalitystatus_id = ps.Nationalitystatus_id
	inner join Diag as d on d.Diag_id = es.Diag_id 
	left join v_EvnDie as ed on ed.EvnDie_rid = eps.EvnPS_id
	left join Diag as da on da.Diag_id = ed.Diag_aid
		left join lateral(
			select
				date_part('year', age(eps.EvnPS_setDate, ps.Person_BirthDay)) as age,
				case
					when lt.LeaveType_sysnIck in ('die','psumer','ksdie','dsdie','kslet','ksletitar','die')
						then 2
					end as is_die,
				case
					when lt.LeaveType_sysnIck in ('leave','ksleave','dsleave','pszav','psiniclpu','ksiniclpu',
							'dsiniclpu','psinicpac','dsinicpac','ksprerv','kskar','dskar','ksprot','ksinicpac',
							'dsprerv','leave','dstac','kstac','iniclpu','inicpac','prerv')
						then 2
					end as is_not_die,
				datediff('day', eps.EvnPS_setDate, eps.EvnPS_disDate)
					+ abs(sign(datediff('day', eps.EvnPS_setDate, eps.EvnPS_disDate)) - 1) -- круглосуточные 
					as day_cnt
		)sub on true
where true
	and es.EvnSection_disDt between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
	and es.EvnSection_Index = es.EvnSection_Count - 1
	and coalesce(es.EvnSection_IsPriem, 1) = 1
	and eps.EvnPS_disDate is not null 
	and lu.LpuUnitType_Sysnick = 'stac'
	and(lb.LpuFilial_id = any(('{'||p_LpuFilialsList||'')::int8[])
		or coalesce(rtrim(p_LpuFilialsList), '') = '')
	and coalesce(ps.Person_deadDT, ps.Person_closeDT, p_BegDate) >= p_BegDate
	and ns.KLCountry_id = 112 -- Беларусь

