--EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

with params as(
	select
		cast($1	as varchar) as p_lpulist,
		cast($2 as varchar) 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
),
Lpu_W as(
	select Lpu_id
	from dbo.v_Lpu as L
	left join params on 1=1
		inner join dbo.Org as O on O.Org_id = L.Org_id
	where true
		and(L.Lpu_id in(select id::int8 as id from unnest(string_to_array( p_LpuList, ',')) as id)
			or L.Lpu_pid in(select id::int8 as id from unnest(string_to_array( p_LpuList, ',')) as id)
			or (coalesce(rtrim( p_LpuList), '') = '' and coalesce(L.Lpu_IsTest, 1) = 1)
		)
		and L.Lpu_begDate <  p_EndDate::date + interval '1year'
		and coalesce(L.Lpu_endDate,date'infinity') >=  p_BegDate
		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)
), 
LpuSection_W as(
	select L.Lpu_id,
		LS.LpuSection_id,
		LS.LpuSection_Code,
		LS.LpuSection_Name,
		LS.LpuSectionType_id,
		LSProf.LpuSectionProfile_Code,
		LUT.LpuUnitType_SysNick,
		LU.LpuUnit_Name,
		LU.LpuUnitProfile_fid,
		LS.PalliativeType_id
	from Lpu_W as L 
	left join params on 1=1
	inner join dbo.LpuBuilding as LB on LB.Lpu_id = L.Lpu_id
	left join dbo.LpuUnit as LU on LU.LpuBuilding_id = LB.LpuBuilding_id
	left join dbo.LpuSection as LS  on LS.LpuUnit_id = LU.LpuUnit_id
	left join dbo.LpuUnitType as LUT on LUT.LpuUnitType_id = LU.LpuUnitType_id
	left join dbo.LpuSectionProfile as LSProf on LSProf.LpuSectionProfile_id = LS.LpuSectionProfile_id
	
	where true
		and coalesce(LS.LpuSection_Deleted, 1) = 1
		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 in(
			select id::int8 as id from unnest(string_to_array( p_LpuFilialList, ',')) as id)
			or coalesce(rtrim( p_LpuFilialList), '') = '')
		and L.Lpu_id > 0
),

EPL as(
	select
		EPL.EvnPL_id as Evn_id,
		EPL.EvnPL_disDT as EvnDate,
		EVPL.PersonEvn_id,
		EVPL.Server_id,
		LS.LpuSectionProfile_Code,
		MSF.Post_id
	from Lpu_W as L
		left join params on 1=1
		inner join v_EvnVizitPL as EVPL on EVPL.Lpu_id = L.Lpu_id
		inner join v_EvnPL as EPL on EVPL.EvnVizitPL_pid = EPL.EvnPL_id	
		inner join LpuSection_W as LS on EVPL.LpuSection_id = LS.LpuSection_id
		
		inner join lateral
		(
			select MSF.Post_id
			from dbo.medstafffactcache as MSF
			--from v_medstafffact as MSF
			left join persis.Post as p on p.id = MSF.Post_id
			where MSF.MedStaffFact_id = EVPL.MedStaffFact_id
			and MSF.Post_id not in (115,142) and p.PostKind_id=1 limit 1
		) msf on true
		
		where (1=1)		and EVPL.TreatmentClass_id between 1 and 4
		and EPL.EvnPL_disDT between p_BegDate and  p_EndDate + interval '1 day' - interval '1 minute'
		and Epl.EvnPL_IsFinish = 2
		and  EVPL.EvnVizitPL_Index =  EVPL.EvnVizitPL_count - 1
		and EVPL.EvnVizitPL_setDate between p_BegDate and  p_EndDate + interval '1 day' - interval '1 minute'
		and (EVPL.PayType_id =  p_PayType_id or (EVPL.PayType_id is null and p_PayType_id = -2) or p_PayType_id is null)
		and (EVPL.MedPersonal_id =  p_MedPersonal_id or  p_MedPersonal_id is null)
		and (EVPL.TreatmentClass_id =  p_TreatmentClass_id or (EVPL.TreatmentClass_id is null and p_TreatmentClass_id = -2) or p_TreatmentClass_id is null)
),

EPS as(
	select
		EPS.Evn_id as Evn_id,
		EPS.EvnPS_OutcomeDt as EvnDate,
		EPS.PersonEvn_id,
		EPS.Server_id,
		LS.LpuSectionProfile_Code,
		MSF.Post_id
	from Lpu_W as L
	left join params on 1=1
		inner join dbo.EvnPS as EPS on EPS.Lpu_id = L.Lpu_id
			
		inner join LpuSection_W as LS on EPS.LpuSection_pid = LS.LpuSection_id
		inner join lateral
		(
			select MSF.Post_id
			from dbo.medstafffactcache as MSF 
			left join persis.Post as p on p.id = MSF.Post_id
			where MSF.MedStaffFact_id = EPS.MedStaffFact_pid
			and MSF.Post_id not in (115,142) and p.PostKind_id=1 
			limit 1
		) msf on true
		
		left join dbo.EvnSection as ES on ES.Evn_pid = EPS.Evn_id
	where   (ES.LeaveType_prmid in (358,463) or EPS.PrehospWaifRefuseCause_id > 0)
	and coalesce(p_TreatmentClass_id, 2) = 2
	and EPS.EvnPS_OutcomeDt between p_BegDate and p_EndDate + interval '1 day' - interval '1 minute'
	and (EPS.PayType_id =  p_PayType_id or (EPS.PayType_id is null and p_PayType_id = -2) or p_PayType_id is null)
	
	and (EPS.MedPersonal_pid =  p_MedPersonal_id or  p_MedPersonal_id is null)
	and eps.evn_deleted = 1
	and es.evn_deleted = 1
),

dvn as
(
select --ST.SurveyType_Name,
epld.Evn_id as evn_id,EPLD.Evn_disDT as EvnDate,EVD.PersonEvn_id,Evd.Server_id,
LS.LpuSectionProfile_Code, MSF.Post_id
from Lpu_W as L 
left join params on 1=1
inner join dbo.EvnPLDisp as EPLD  on EPLD.Lpu_id = L.Lpu_id
  
inner join dbo.EvnVizitDisp as EVD  on  EPLD.Evn_id = EVD.Evn_pid
  
inner join LpuSection_W as LS  on EVD.LpuSection_id = LS.LpuSection_id
inner join lateral
	(
		select MSF.Post_id
		from dbo.medstafffactcache as MSF 
		left join persis.Post as p on p.id = MSF.Post_id
		where MSF.MedStaffFact_id = EVD.MedStaffFact_id
		and p.PostKind_id=1 
		limit 1
	) msf on true

left join dbo.v_DopDispInfoConsent DDIC on DDIC.DopDispInfoConsent_id = EVD.DopDispInfoConsent_id
left join dbo.v_SurveyTypeLink STL  on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
left join dbo.v_SurveyType ST  on ST.SurveyType_id = STL.SurveyType_id
where EVD.evn_deleted = 1
	and EPLD.Evn_deleted = 1
  and (ST.SurveyType_Name like '%осмотр%врач%' or EPLD.DispClass_id in (4,8) or EVD.Diag_id > 0 and EPLD.DispClass_id between 11 and 12) 
  and EPLD.Evn_setDT <=  p_EndDate
	and coalesce(EPLD.Evn_disDT,  p_BegDate) >=  p_BegDate  
  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 (EPLD.PayType_id =  p_PayType_id or (EPLD.PayType_id is null and p_PayType_id = -2) or p_PayType_id is null)
 and EVD.Evn_setDT between  p_BegDate and  p_EndDate
	and EVD.Evn_setDT >=  coalesce(EPLD.EvnPLDisp_consDt,  p_BegDate)
	and (EVD.MedPersonal_id =  p_MedPersonal_id or  p_MedPersonal_id is null)
 

)

select
	coalesce(count(Evn_id), 0) as val1,
	coalesce(count(
		case
			when coalesce(PersonAddress1.KlareaType_id, PersonAddress2.KlareaType_id) = 2 
				then Evn_id end), 0) as val1_selo, -- сельские
	coalesce(count(
		case
			when dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17 
				then Evn_id end), 0) as val1_child, -- детьми 0-17 лет 
	coalesce(count(
		case
			when dbo.AgeYMD(Person.Person_BirthDay, E.EvnDate, 1) between 0 and 17 
					and coalesce(PersonAddress1.KlareaType_id, PersonAddress2.KlareaType_id) = 2 -- сельские
				then Evn_id end), 0) as val1_child_Selo -- детьми 0-17 лет selo
from(
	select
		Evn_id,
		EvnDate,
		PersonEvn_id,
		Server_id,
		LpuSectionProfile_Code,
		Post_id
	from EPL 

	union all 
select
		Evn_id,
		EvnDate,
		PersonEvn_id,
		Server_id,
		LpuSectionProfile_Code,
		Post_id
	from EPS
	union all select 
		Evn_id,
		EvnDate,
		PersonEvn_id,
		Server_id,
		LpuSectionProfile_Code, 
		Post_id
	  from dvn
)E
	inner join dbo.v_Person_all as Person on E.PersonEvn_id = Person.PersonEvn_id
		and Person.Server_id = E.Server_id
	left join dbo.Address PersonAddress1 on PersonAddress1.Address_id = Person.PAddress_id
left join dbo.Address PersonAddress2 on PersonAddress2.Address_id = Person.UAddress_id

