with params as(
	select
		$1 as p_LpuList,
		$2 as p_LpuFilialList,
		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_MedPersonal_id,
		cast($7 as timestamp) as p_BegDate,
		cast($8 as timestamp) as p_EndDate,
		cast($9 as bigint) as p_IncludeDopDisp,
		cast($10 as bigint) as p_IncludeProfOsm,
		cast($11 as bigint) as p_IncludeDispOrp,
		cast($12 as bigint) as p_IncludeTeenDisp,
		cast($13 as bigint) as p_TreatmentClass_id,
		cast($14 as bigint) as p_PayType_id,
		cast($15 as bigint) as p_IsGUZOnly,
		cast($16 as bigint) as p_region_id
),

rd as(
	select
		rd.row_num,
		rd.is_selo,
		rd.atr,
		rd.age_from,
		rd.age_to,
		rd.row_num_text,
		rd.row_text,
		frmp.frmp_id,
		post.post_id
	from(values
		(1 ,null,null,null,null,'1','Посещения среднего медицинского персонала, всего'),
		(2 ,null,null,null,null,'2','из них:  на ФАПах (включая посещения на дому)'),
		(3 ,null,6   ,null,null,'2.1','из них на передвижных'),
		(4 ,null,null,null,null,'2.2','из стр. 2: акушерки'),
		(5 ,null,null,null,null,'3','на фельдшерских пунктах'),
		(6 ,null,7   ,null,null,'3.1','из них на передвижных'),
		(7 ,null,null,null,null,'4','на пунктах (отделениях, кабинетах) неотложной медицинской помощи'),
		(8 ,2   ,null,null,null,'4.1','из них сельскому населению, всего'),
		(9 ,null,null,18  ,200 ,'4.2','взрослому населению'),
		(10,null,null,0   ,17  ,'4.3','детскому населению'),
		(11,null,null,null,null,'5','в амбулаториях (из стр. 1)'),
		(12,null,1   ,null,null,'5.1','из них в передвижных'),
		(13,null,null,null,null,'5.2','из стр. 5: акушерки')
	)as rd(row_num, is_selo, atr, age_from, age_to,  row_num_text, row_text)
	left join (
		select
			post.row_num,
			post.post_id
		from(values
			(4,120),(4,10209),(4,116),
			(13,120),(13,10209),(13,116)
		)as post(row_num, post_id)
	)post on post.row_num = rd.row_num
	left join (
		select
			frmp.row_num,
			frmp.frmp_id
		from(values
			(2,10149),(2,10409),(2,10408),
			(3,10149),(3,10409),(3,10408),
			(4,10149),(4,10409),(4,10408),
			(5,10407),
			(6,10407),
			(7,10342),(7,10351),(7,10352),(7,10485),
			(8,10342),(8,10351),(8,10352),(8,10485),
			(9,10342),(9,10351),(9,10352),(9,10485),
			(10,10342),(10,10351),(10,10352),(10,10485),
			(11,10208),(11,10207),(11,10459),(11,10286),
			(12,10208),(12,10207),(12,10459),(12,10286),
			(13,10208),(13,10207),(13,10459),(13,10286)
		)as frmp(row_num, frmp_id)
	)frmp on frmp.row_num = rd.row_num
),

LpuP as(
	select
		L.Lpu_id,
		params.*
	from params
		left join lateral(
			select
				L.Lpu_id,
				L.Org_id
			from rpt.GetLpu_successionL_pg(
				p_LpuList,
				p_BegDate,
				p_EndDate) as L
		)L on true
		inner join dbo.Org as O on O.Org_id = L.Org_id
	where 
		case
			when p_region_id = 59 and L.Lpu_id in (13002556,10011362) or O.Okfs_id in (2,4,5)
				then 2
			else 1
		end >= p_IsGUZOnly
),

ls as(
	select
		ls.LpuSection_id,
		FRMPSubdivision_id
	from LpuP
		inner join dbo.v_LpuSection as ls on ls.Lpu_id = LpuP.Lpu_id
		inner join dbo.LpuUnit as lu on ls.LpuUnit_id = lu.LpuUnit_id
		inner join dbo.LpuUnitType as lut on lut.LpuUnitType_id = lu.LpuUnitType_id
		inner join dbo.LpuBuilding as lb on lu.LpuBuilding_id = lb.LpuBuilding_id
		left join dbo.LpuSectionProfile as lsp
			on lsp.LpuSectionProfile_id = ls.LpuSectionProfile_id
	where true
		and (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 (lb.LpuBuilding_id = p_LpuBuilding_id or p_LpuBuilding_id is null)
		and (lb.LpuFilial_id = any(('{'||p_LpuFilialList||'}')::int8[])
			or coalesce(rtrim(p_LpuFilialList), '') = '')
),

evns as (
	-- ТАП
	select
		evpl.EvnVizitPL_id as Evn_id,
		ls.FRMPSubdivision_id,
		ls.LpuSection_id,
		msf.Post_id,
		evpl.EvnVizitPL_setDate as EvnDate,
		evpl.PersonEvn_id,
		evpl.Server_id
	from LpuP as L
		inner join v_EvnPL as epl on epl.Lpu_id = L.Lpu_id
		inner join v_EvnVizitPL as evpl on evpl.EvnVizitPL_pid = epl.EvnPL_id
		inner join dbo.v_MedstaffFact as msf on msf.MedstaffFact_id = evpl.MedstaffFact_id
		inner join persis.Post as p on p.id = msf.Post_id
		inner join ls on evpl.LpuSection_id = ls.LpuSection_id
		left join dbo.v_UslugaComplex as uc on uc.UslugaComplex_id = evpl.UslugaComplex_id 
		left join dbo.v_ServiceType as st on st.ServiceType_id = evpl.ServiceType_id
	where true
		and epl.EvnPL_setDate <= p_EndDate
		and coalesce(epl.EvnPL_disDate, p_BegDate) >= p_BegDate
		and evpl.EvnVizitPL_setDate between p_BegDate and p_EndDate
		and p.PostKind_id = 6
		and msf.Post_id not in (115,142) -- исключены "115 Зубной врач", "142 Гигиенист стоматологический" - идут в таб 2700
		and (coalesce(evpl.PayType_id, -2) = p_PayType_id or p_PayType_id is null)
		and (evpl.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
		and (coalesce(evpl.TreatmentClass_id, -2) = p_TreatmentClass_id or p_TreatmentClass_id is null)

	-- ДВН, ДДС, ПОВН, МОН
	union all select
		evd.EvnVizitDisp_id as Evn_id,
		ls.FRMPSubdivision_id,
		ls.LpuSection_id,
		msf.Post_id,
		evd.EvnVizitDisp_setDate as EvnDate,
		evd.PersonEvn_id,
		evd.Server_id
	from LpuP as L
		inner join v_EvnPLDisp as epld on epld.Lpu_id = L.Lpu_id
		inner join v_EvnVizitDisp as evd on epld.EvnPLDisp_id = evd.EvnVizitDisp_pid 
		inner join dbo.v_MedstaffFact as msf on msf.MedstaffFact_id = evd.MedstaffFact_id
		inner join persis.Post as p on p.id = msf.Post_id
		inner join ls on evd.LpuSection_id = ls.LpuSection_id
		left join dbo.v_DopDispInfoConsent as ddic on ddic.DopDispInfoConsent_id = evd.DopDispInfoConsent_id
		left join dbo.v_SurveyTypeLink as stl on stl.SurveyTypeLink_id = ddic.SurveyTypeLink_id
		left join dbo.v_SurveyType as st on st.SurveyType_id = stl.SurveyType_id
	where true
		and epld.EvnPLDisp_setDate <= p_EndDate
		and coalesce(epld.EvnPLDisp_disDate, p_BegDate) >= p_BegDate
		and evd.EvnVizitDisp_setDate between p_BegDate and p_EndDate
		and evd.EvnVizitDisp_setDate >= coalesce(cast(epld.EvnPLDisp_consDT as date), p_BegDate)
		and p.PostKind_id = 6
		and msf.Post_id not in (115,142) -- исключены "115 Зубной врач", "142 Гигиенист стоматологический" - идут в таб 2700
		and (evd.MedPersonal_id = p_MedPersonal_id or p_MedPersonal_id is null)
		and (p_IncludeDopDisp = 2 and epld.DispClass_id in (1,2) and coalesce(p_TreatmentClass_id, 7) = 7
			or p_IncludeProfOsm = 2 and epld.DispClass_id = 5 and coalesce(p_TreatmentClass_id, 6) = 6
			or p_IncludeTeenDisp = 2 and epld.DispClass_id in (6,9,10,11,12) and coalesce(p_TreatmentClass_id, 6) = 6
			or p_IncludeDispOrp = 2 and epld.DispClass_id in (3,4,7,8) and coalesce(p_TreatmentClass_id, 7) = 7)
		and (coalesce(epld.PayType_id, -2) = p_PayType_id or p_PayType_id is null)
		and (st.SurveyType_Name like '%осмотр%врач%' or epld.DispClass_id in (4,8)
			or evd.Diag_id is not null and epld.DispClass_id between 11 and 12)
),

rep_data as(
	select
		rd.row_num,
		count(distinct evns.Evn_id) as v3,
		count(case when adr.KLAreaType_id = 2 then evns.Evn_id end) as v4
	from evns
		inner join dbo.v_Person_all as ps on evns.PersonEvn_id = ps.PersonEvn_id
			and ps.Server_id = evns.Server_id
		left join dbo.Address as adr on adr.Address_id = coalesce(ps.PAddress_id, ps.UAddress_id)
		left join lateral(
			select mob.AttributeValue_Value
			from v_AttributeSignValue as asv
					inner join v_AttributeSign as ats on ats.AttributeSign_id = asv.AttributeSign_id
					left join lateral(
						select
							av.AttributeSignValue_id,
							av.AttributeValue_id,
							a.Attribute_SysNick,
							av.AttributeValue_ValueIdent as AttributeValue_Value,
							AttributeValue_ValueText
						from v_AttributeValue as av
							inner join v_Attribute as a on a.Attribute_id = av.Attribute_id
						where av.AttributeSignValue_id = asv.AttributeSignValue_id
						and a.Attribute_SysNick = 'MobileUnitType'
					)mob on true
			where ats.AttributeSign_TableName like 'dbo.LpuSection'
				and asv.AttributeSignValue_TablePKey = evns.LpuSection_id
				and ats.AttributeSign_id = 14
				and evns.EvnDate between  AttributeSignValue_begDate
					and coalesce(AttributeSignValue_endDate,'infinity'::date)
				and mob.AttributeValue_Value in (1,6,7)
		)mob on true
		inner join rd on (rd.is_selo = adr.KLAreaType_id or rd.is_selo is null)
			and (rd.atr = mob.AttributeValue_Value or rd.atr is null)
			and (dbo.AgeYMD(ps.Person_BirthDay, evns.EvnDate, 1) between rd.age_from and rd.age_to
				or rd.age_from is null)
			and(rd.frmp_id = evns.FRMPSubdivision_id or rd.frmp_id is null)
			and(rd.post_id = evns.post_id or rd.post_id is null)
	group by rd.row_num
)

select
	rd.row_num,
	rd.row_text as v1,
	rd.row_num_text as v2,
	coalesce(rep_data.v3,0) as v3,
	coalesce(rep_data.v4,0) as v4
from(
	select distinct
		rd.row_num,
		rd.row_num_text,
		rd.row_text
	from rd
)rd
	left join rep_data on rep_data.row_num = rd.row_num
order by rd.row_num
