--Rec_FactVizit_pg.rptdesign
with param as (
	select
		cast($1 as int8) as p_lpu_id,
		cast($2 as int8) as p_lpusection_id,
		cast($3 as int8) as p_lpusectionprofile_id,
		cast($4 as int8) as p_medpersonal_id,
		cast($5 as int8) as p_terr_code,
		cast($6 as int8) as p_terr_code_person,
		cast(to_timestamp($7, 'dd.mm.yyyy') as timestamp) as p_begdate,
		cast(to_timestamp($8, 'dd.mm.yyyy') as timestamp) as p_enddate 
),

Lpu_W as (
	select
		l.Lpu_id,
		L.Lpu_Nick,
		L.PAddress_id,
		-- факт. адрес МО
 		L.UAddress_id,
		-- юрид. адрес МО
 		p.*
	from param p
	inner join v_Lpu L on true
	where (L.Lpu_id = p_lpu_id	or (p_lpu_id is null and coalesce(L.Lpu_IsTest, 1) = 1))
		and L.Lpu_begDate<= p_enddate + interval '1 year'
		and coalesce(L.Lpu_endDate, p_begdate) >= p_begdate
),

Birki as (
	select 
		lpu.Lpu_Nick,
		lpusection.LpuSection_id,
		lpusection.LpuSection_FullName as LpuSection_Name,
		MSF.MedPersonal_id,
		rtrim(coalesce(MSF.Person_SurName, '')) || rtrim(' ' || coalesce(MSF.Person_FirName, '')) || rtrim(' ' || coalesce(MSF.Person_SecName, '')) as MedPersonal_FIO,
		PM.PostMed_Name,
		person.person_id,
		ttg.TimeTableGraf_id,
		ttg.TimeTableGraf_begTime,
		ttg.TimeTableGraf_factTime,
		ttg.TimeTableType_id,
		ttg.MedStaffFact_id,
		vizited.isvizited,
		vizited.vizitpolka_id,
		vizited.VizitHome_id
	from	TimeTableGraf ttg
	inner join v_Medstafffact as MSF on	MSF.MedStaffFact_id = TTG.MedStaffFact_id
	inner join lpu_w lpu on	lpu.lpu_id = MSF.Lpu_id
		and	coalesce(TTG.TimeTableGraf_begTime, TTG.TimeTableGraf_factTime) between p_begdate and p_enddate + interval '1 day' - interval '1 minute'
		and (TTG.TimetableGraf_deleted = 1 or TTG.TimetableGraf_deleted is null) 
	inner join v_PostMed as PM on	MSF.Post_id = PM.PostMed_id
	left join v_lpusection lpusection on lpusection.lpusection_id = msf.lpusection_id
	left join Address as Address_lpu on Address_lpu.Address_id = coalesce(lpu.PAddress_id, lpu.UAddress_id)
	left join KLAreaStat KLAreaStat_lpu on KLAreaStat_lpu.KLAreaStat_id = Address_lpu.KLAreaStat_id
	left join lateral(
		--пациент, учитывая фильтр по территории адреса
		select
			person_id
		from v_personstate person
		left join address on address.address_id = coalesce(person.PAddress_id, person.UAddress_id)
		left join klareastat area on area.klareastat_id = address.klareastat_id
		where	person.person_id = ttg.person_id
			and (area.KLAreaStat_Code = p_terr_code_person or p_terr_code_person is null or (p_terr_code_person = - 2	and coalesce(area.KLRGN_id, - 1) <> dbo.getregion()))
			-- иные территории
	) person on	true
	left join lateral(
	-- ищется посещение для бирки по пациенту, отделению, врачу и дате(без времени)
		select
			vizits.evn_id as isVizited,
			case when ST.ServiceType_SysNick in ('polka', 'polnmp', 'other', 'mobmedteam') then vizits.Evn_id end as VizitPolka_id,
			case when ST.ServiceType_SysNick in ('home', 'ahome', 'neotl') then vizits.Evn_id end as VizitHome_id		
		from EvnVizitPL as vizits
		inner join v_ServiceType as ST on ST.ServiceType_id = vizits.ServiceType_id
			and (vizits.evn_deleted = 1 or vizits.evn_deleted is null)
			and	vizits.person_id = ttg.person_id
			and vizits.LpuSection_id = lpusection.LpuSection_id
			and vizits.MedPersonal_id = msf.MedPersonal_id
			and cast(vizits.evn_setDT as date) = cast(coalesce(TTG.TimeTableGraf_begTime, TTG.TimeTableGraf_factTime) as date)
		limit 1 
	) vizited on true
	where	(MSF.MedPersonal_id = p_medpersonal_id or p_medpersonal_id is null)
		and (MSF.LpuSection_id = p_lpusection_id or p_lpusection_id is null)
		and (lpusection.LpuSectionProfile_id = p_lpusectionprofile_id	or p_lpusectionprofile_id is null)
		and (KLAreaStat_lpu.KLAreaStat_Code = p_terr_code	or p_terr_code is null)
),

Vizits as (
	select 
		evpl.evn_id as evnvizitpl_id,
		Lpu.Lpu_Nick,
		lpusection.LpuSection_id,
		lpusection.LpuSection_FullName as LpuSection_Name,
		MSF.MedPersonal_id,
		rtrim(coalesce(MSF.Person_SurName, '')) || rtrim(' ' || coalesce(MSF.Person_FirName, '')) || rtrim(' ' || coalesce(MSF.Person_SecName, '')) as MedPersonal_FIO,
		PM.PostMed_Name,
		case when ST.ServiceType_SysNick in ('polka', 'polnmp', 'other', 'mobmedteam') then EVPL.Evn_id end as VizitPolka_id,
		case when ST.ServiceType_SysNick in ('home', 'ahome', 'neotl') then EVPL.Evn_id end as VizitHome_id,
		EVPL.TimeTableGraf_id,
		cast (EVPL.Evn_setDT as date) as VizitDate,
		EVPL.Person_id
	from EvnVizitPL as EVPL
	inner join Lpu_W as Lpu on Lpu.Lpu_id = EVPL.Lpu_id 
		and EVPL.Evn_setDT between p_begdate and p_enddate + interval '1 day' - interval '1 minute' 
		and (evpl.evn_deleted = 1 or evpl.evn_deleted is null)
		and (EVPL.LpuSection_id = p_lpusection_id	or p_lpusection_id is null)
		and (EVPL.MedPersonal_id = p_medpersonal_id	or p_medpersonal_id is null)
	inner join v_MedStaffFact as MSF on	MSF.MedStaffFact_id = EVPL.MedStaffFact_id
	inner join v_PostMed as PM on	MSF.Post_id = PM.PostMed_id
	left join Address as Address_lpu on	Address_lpu.Address_id = coalesce(Lpu.PAddress_id, Lpu.UAddress_id)
	left join KLAreaStat as KLAreaStat on	KLAreaStat.KLAreaStat_id = Address_lpu.KLAreaStat_id
	left join v_ServiceType as ST on ST.ServiceType_id = EVPL.ServiceType_id
	left join v_personstate person on	person.person_id = EVPL.person_id
	left join address on address.address_id = coalesce(person.PAddress_id, person.UAddress_id)
	left join klareastat KLAreaStatP on KLAreaStatP.klareastat_id = address.klareastat_id
	left join v_lpusection lpusection on lpusection.lpusection_id = evpl.LpuSection_id
	where	(KLAreaStat.KLAreaStat_Code = p_terr_code	or p_terr_code is null)
		and (lpusection.LpuSection_id = p_lpusection_id	or p_lpusection_id is null)
		and (lpusection.LpuSectionProfile_id = p_lpusectionprofile_id	or p_lpusectionprofile_id is null)
		-- иные территории
		and (KLAreaStatP.KLAreaStat_Code = p_terr_code_person	or p_terr_code_person is null	or (p_terr_code_person = - 2 and coalesce(KLAreaStatP.KLRGN_id, - 1) <> dbo.getregion()))
),

result as (
	select 
		coalesce(Birki.Lpu_Nick, Vizits.Lpu_Nick) as lpu_nick,
		coalesce(Birki.LpuSection_id, Vizits.LpuSection_id) as lpusection_id,
		coalesce(Birki.LpuSection_Name, Vizits.LpuSection_Name,'') as lpusection,
		coalesce(Birki.MedPersonal_FIO, Vizits.MedPersonal_FIO) as medpers,
		coalesce(Birki.PostMed_Name, Vizits.PostMed_Name) as post,
		--записи
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null 
 			then Birki.TimeTableGraf_id end) as rec4,	--всего
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and birki.person_id is not null 
 			then Birki.TimeTableGraf_id end) as rec5,		--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 1 
 			then Birki.TimeTableGraf_id end) as rec6, 	--обычная
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 1 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec7,		--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 2 
 			then Birki.TimeTableGraf_id end) as rec8,		--резерв
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 2 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec9,		--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 4 
 			then Birki.TimeTableGraf_id end) as rec10,	--для Центра записи
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 4 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec11,	--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 5 
 			then Birki.TimeTableGraf_id end) as rec12,	--По направлению
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 5 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec13,	--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 8 
 			then Birki.TimeTableGraf_id end) as rec14,	--Для врачей своей МО
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 8 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec15,	--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 11 
 			then Birki.TimeTableGraf_id end) as rec16, 	--Для интернета
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 11 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec17,	--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 9 
 			then Birki.TimeTableGraf_id end) as rec18, 	--Запись через инфомат
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 9 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec19,	--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 3
 			then Birki.TimeTableGraf_id end) as rec20,	--Платная
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id = 3 and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec21,	--занятых
 		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id not in (1, 2, 4, 5, 8, 11, 9, 3)
 			then Birki.TimeTableGraf_id end) as rec22,	--Прочие
		count(distinct case when Birki.TimeTableGraf_id > 0 and Birki.TimeTableGraf_begTime is not null and Birki.TimeTableType_id not in (1, 2, 4, 5, 8, 11, 9, 3) and birki.person_id is not null 
			then Birki.TimeTableGraf_id end) as rec23,	--занятых
		--посещения
 		count(distinct case when Vizits.VizitPolka_id > 0 then Vizits.VizitPolka_id end) as vizkol,
		--v24
 		count(distinct case when birki.person_id is not null and Birki.TimeTableGraf_begTime is not null then birki.VizitPolka_id end) as rec_vizkol,
		--явка (в т.ч. записанных) v25
 		count(distinct case when birki.person_id is not null and Birki.TimeTableGraf_begTime is not null then birki.VizitHome_id end) as rec_vizkol_home,
		--посещение записанных на дому
 		count(distinct case when Vizits.VizitHome_id > 0 then Vizits.VizitHome_id end) as vizkol_home
		-- v28

	from param
	inner join Birki on	true
	full join Vizits on	Birki.TimeTableGraf_id = Vizits.TimeTableGraf_id
	/*left join lateral(
		--пациент, учитывая фильтр по территории адреса
		select
			person_id
		from v_personstate person
		left join address on address.address_id = coalesce(person.PAddress_id, person.UAddress_id)
		left join klareastat area on area.klareastat_id = address.klareastat_id
		where	person.person_id = birki.person_id
			and (area.KLAreaStat_Code = p_terr_code_person or p_terr_code_person is null or (p_terr_code_person = - 2	and coalesce(area.KLRGN_id, - 1) <> dbo.getregion()))
			-- иные территории
	) person on	true
	left join lateral(
	-- ищется посещение для бирки по пациенту, отделению, врачу и дате(без времени)
		select
			vizits.evnvizitpl_id as isVizited,
			vizits.VizitPolka_id,
			vizits.VizitHome_id
		from vizits
		where	vizits.person_id = birki.person_id
			and vizits.LpuSection_id = birki.LpuSection_id
			and vizits.MedPersonal_id = birki.MedPersonal_id
			--and vizits.VizitDate = birki.RecordDate
		limit 1 
	) vizited on true*/
	group by
		coalesce(Birki.Lpu_Nick, Vizits.Lpu_Nick),
		coalesce(Birki.LpuSection_id, Vizits.LpuSection_id),
		coalesce(Birki.LpuSection_Name, Vizits.LpuSection_Name,''),
		coalesce(Birki.MedPersonal_FIO, Vizits.MedPersonal_FIO),
		coalesce(Birki.PostMed_Name, Vizits.PostMed_Name)
)
select
	result.lpu_nick,
	result.lpusection_id,
	result.lpusection,
	result.medpers,
	result.post,
	result.rec4,
	result.rec5,
	result.rec6,
	result.rec7,
	result.rec8,
	result.rec9,
	result.rec10,
	result.rec11,
	result.rec12,
	result.rec13,
	result.rec14,
	result.rec15,
	result.rec16,
	result.rec17,
	result.rec18,
	result.rec19,
	result.rec20,
	result.rec21,
	result.rec22,
	result.rec23,
	result.vizkol,
	result.rec_vizkol,
	case
		when (result.vizkol - result.rec_vizkol) > 0 then (result.vizkol - result.rec_vizkol)
		else 0
	end as withoutrec_vizkol,
	case
		when (result.rec5 - result.rec_vizkol - result.rec_vizkol_home) > 0 then (result.rec5 - result.rec_vizkol - result.rec_vizkol_home)
		else 0
	end as abskol,
	result.vizkol_home
from result
