select
	ps.Person_fio as fio, 
	ps.Person_BirthDay as b_day,
	padr.Address_Address as paddr,
	uadr.Address_Address as uaddr,
	concat_ws(' ', orgjob.Org_Name, post.Post_Name) as job,
	depk.deputy,
	eps.EvnPS_setDT as set_dt,
	eps.EvnPS_disDT as dis_dt,
	Lpu.Lpu_Nick as lpu_nick,
	Lpu.Lpu_Name as lpu_name,
	Lpu.Lpu_OGRN as ogrn,
	Lpu.Lpu_f003mcod as lpu_code,
	concat_ws(' ', Lpu.UAddress_Address, Lpu.Lpu_Phone) as lpu_adr,
	d.Diag_FullName as diag,
	xml_data.complaint,
	xml_data.anmn_text,
	oper.oper_list,
	usl.lab_list,
	usl.func_list,
	usl.proc_list,
	usl.cons_list,
	drug.drug_list,
	dsp1.DiagSetPhase_name as phase_in,
	dsp2.DiagSetPhase_name as phase_out,
	xml_data.recommendations,
	yn.YesNo_Name as is_active,
	pwrc.PrehospWaifRefuseCause_Name as cause,
	eps.EvnPS_OutcomeDT as out_dt,
	mp1.Person_Fin as doc1,
	mp2.Person_Fin as doc2
from v_EvnPS as eps
	left join v_Person_all as ps on ps.PersonEvn_id = eps.PersonEvn_id
		and ps.Server_id = eps.Server_id
	left join v_Address as padr on padr.Address_id = ps.PAddress_id
	left join v_Address as uadr on uadr.Address_id = ps.UAddress_id
	left join v_Job as job on job.Job_id = ps.Job_id
	left join v_Org as orgjob on job.Org_id = orgjob.Org_id
	left join v_Post as post on post.post_id = job.post_id
	left join v_Lpu as Lpu on Lpu.Lpu_id = eps.Lpu_id
	left join v_Diag as d on d.Diag_id = eps.Diag_pid
	left join v_DiagSetPhase as dsp1 on dsp1.DiagSetPhase_id = eps.DiagSetPhase_pid
	left join v_DiagSetPhase as dsp2 on dsp2.DiagSetPhase_id = eps.DiagSetPhase_aid
	left join dbo.YesNo as yn on yn.YesNo_id = eps.EvnPS_IsTransfCall
	left join dbo.PrehospWaifRefuseCause as pwrc
		on pwrc.PrehospWaifRefuseCause_id = eps.PrehospWaifRefuseCause_id
	left join lateral(
		select
			(xpath('data/complaint/text()',evnxml.EvnXml_Data::xml))[1]::varchar as complaint,
			(xpath('data/anamnesmorbi/text()',evnxml.EvnXml_Data::xml))[1]::varchar as anmn_text,
			(xpath('data/recommendations/text()',evnxml.EvnXml_Data::xml))[1]::varchar as recommendations
		from Evn 
			left join EvnXml on EvnXml.Evn_id = Evn.Evn_id
		where Evn.Evn_pid = eps.EvnPS_id and XmlType_id = 8
		order by EvnXml_id
		limit 1
	)xml_data on true
	left join lateral(
		select string_agg(
			to_char(euo.EvnUslugaOper_setDate, 'dd.mm.yyyy, ')
				||uc.UslugaComplex_Code||' '||uc.UslugaComplex_Name,
			'; ') as oper_list
		from v_EvnUslugaOper as euo
			inner join v_UslugaComplex as uc
				on euo.UslugaComplex_id = uc.UslugaComplex_id
		where euo.EvnUslugaOper_pid = eps.EvnPS_id
	)oper on true
	left join lateral(
		select
			string_agg(distinct
					concat_ws(' ',
						to_char(eu.EvnUsluga_setDate, 'dd.mm.yyyy,'),
						uc.UslugaComplex_Code,
						uc.UslugaComplex_Name),
					'; ') 
				filter(where uca.UslugaComplexAttributeType_id = 8) as lab_list,
			string_agg(distinct
					concat_ws(' ',
						to_char(eu.EvnUsluga_setDate, 'dd.mm.yyyy,'),
						uc.UslugaComplex_Code,
						uc.UslugaComplex_Name),
					'; ') 
				filter(where uca.UslugaComplexAttributeType_id = 9) as func_list,
			string_agg(distinct
					concat_ws(' ',
						to_char(eu.EvnUsluga_setDate, 'dd.mm.yyyy,'),
						uc.UslugaComplex_Code,
						uc.UslugaComplex_Name),
					'; ') 
				filter(where uca.UslugaComplexAttributeType_id = 16) as proc_list,
			string_agg(distinct
					concat_ws(' ',
						to_char(eu.EvnUsluga_setDate, 'dd.mm.yyyy,'),
						uc.UslugaComplex_Code,
						uc.UslugaComplex_Name),
					'; ') 
				filter(where uca.UslugaComplexAttributeType_id = 13) as cons_list
		from v_EvnUsluga as eu
			inner join v_UslugaComplex as uc
				on uc.UslugaComplex_id = eu.UslugaComplex_id
			left join v_UslugaComplexAttribute as uca
				on uca.UslugaComplex_id = uc.UslugaComplex_id
		where eu.EvnUsluga_rid = eps.EvnPS_id
			and (uca.UslugaComplexAttribute_begDate::date <= eu.EvnUsluga_setDate
				or uca.UslugaComplexAttribute_begDate is null)
			and (uca.UslugaComplexAttribute_endDate::date > eu.EvnUsluga_setDate
				or uca.UslugaComplexAttribute_endDate is null)
			and uca.UslugaComplexAttributeType_id in (8,9,13,16)
	)usl on true
	left join lateral(
		select string_agg(
			coalesce(mnn.DrugComplexMnn_RusName,dr.Drug_Name,'')
				|| coalesce('Доза разовая - '
					|| cast(cast(EvnCourseTreatDrug_Kolvo as decimal (10,2)) as varchar) || ' '
					|| coalesce(CUBICUNITS.SHORTNAME,MASSUNITS.SHORTNAME),
					'')
				|| '; '
				|| 'дневная - '
					|| coalesce(EvnCourseTreatDrug_MinDoseDay,'')
					|| case
						when (EvnCourseTreatDrug_MinDoseDay is not null) and (EvnCourseTreatDrug_MaxDoseDay is not null)
							then '-'
						else '' end
					|| coalesce(EvnCourseTreatDrug_MaxDoseDay,'')
					|| '; '
				|| coalesce('курсовая - ' || EvnCourseTreatDrug_PrescrDose,'')
				|| '. '
				|| coalesce(to_char(course_progress.course_start,'С dd.mm.yyyy')
					|| ' продолжительность '
					|| cast(ec.EvnCourse_Duration as varchar) 
					|| coalesce(' '||dt.DurationType_Nick || '.',''),'')
				|| coalesce(' выполнено '|| cast(course_progress.duration as varchar) 
					|| coalesce(' '||dt.DurationType_Nick || '.',''),''),
			'; ') as drug_list
		from v_EvnCourse as ec
			inner join v_EvnCourseTreat as ect on ect.EvnCourseTreat_id = ec.EvnCourse_id
			inner join v_EvnCourseTreatDrug as ectd on ectd.EvnCourseTreat_id = ect.EvnCourseTreat_id
			left join rls.MASSUNITS on MASSUNITS.MASSUNITS_ID = ectd.MASSUNITS_id
			left join rls.CUBICUNITS on CUBICUNITS.CUBICUNITS_ID = ectd.CUBICUNITS_id
			left join DurationType as dt on dt.DurationType_id = ec.DurationType_id
			left join rls.v_Drug as dr on dr.Drug_id = ectd.Drug_id
			left join rls.v_DrugComplexMnn as mnn on mnn.DrugComplexMnn_id = ectd.DrugComplexMnn_id
			left join lateral(
				select
					min(EvnPrescr_setDate) as course_start,
					count(*) as duration
				from v_EvnPrescr as epr where epr.EvnCourse_id = ec.EvnCourse_id
					and epr.PrescriptionType_id = 5
			)course_progress on true
		where ec.EvnCourse_rid = eps.EvnPS_id
			and course_progress.course_start is not null
	)drug on true
	left join lateral(
		select concat(
			dps.Person_Fio,
			' '||dk.DeputyKind_Name,
			', Паспорт: Серия '||dps.Document_Ser,
			' Номер '||dps.Document_Num) as deputy
		from PersonDeputy as pd
			inner join DeputyKind as dk on pd.DeputyKind_id = dk.DeputyKind_id 
				and pd.Person_id = ps.Person_id	
			left join lateral(
				select
					Person_Fio,
					doc.Document_Num,
					doc.Document_Ser
				from v_Person_all as psa
					left join v_Document as doc
						on psa.Document_id = doc.Document_id
				where psa.Person_id = pd.Person_pid
					and psa.PersonEvn_insDT <= eps.EvnPS_setDate
				order by psa.PersonEvn_insDT desc
				limit 1
			)dps on true
		limit 1
	)depk on true
	left join lateral(
		select mp.Person_Fin
		from v_MedPersonal as mp
		where mp.MedPersonal_id = coalesce(eps.MedPersonal_sid, eps.MedPersonal_pid)
		limit 1
	)mp1 on true
	left join lateral(
		select mp.Person_Fin
		from v_MedPersonal as mp
		where mp.MedPersonal_id = eps.MedPersonal_tid
		limit 1
	)mp2 on true
where eps.EvnPS_id = cast($1 as bigint)
limit 1
