select
	/*printEvnPSPrehospWaifRefuseCause_pg.rptdesign*/
	ps.Person_SurName as "Person_SurName", 
	ps.Person_FirName as "Person_FirName", 
	ps.Person_SecName as "Person_SecName", 
	to_char(ps.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay",
	addr.Address_Address as "Person_PAddress",-- дом.адрес или место регистрации
	coalesce(orgjob.Org_Name,'______________________________') as "Person_Job",
	coalesce(post.Post_Name,'______________________________') as "Person_JobPost",
	to_char(eps.EvnPS_setDT,'DD.MM.YYYY') as "EvnPS_setDate",
	eps.EvnPS_setTime as "EvnPS_setTime",
	to_char(eps.EvnPS_disDT,'DD.MM.YYYY') as "EvnPS_disDate",
	eps.EvnPS_disTime as "EvnPS_disTime",
	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,
	Diag.Diag_Code || ' ' || Diag.Diag_Name as "Diag_Name",
	pwrc.PrehospWaifRefuseCause_Name as "PrehospWaifRefuseCause_Name",
	MP.Person_Fin as "MedPersonal_Fio",
	replace(PersonMedHistory.PersonMedHistory_Text,'&nbsp;','') PersonMedHistory_Text,
	concat_ws(' ',
		depk.Person_Surname,
		depk.Person_Firname,
		depk.Person_Secname) as "EvnPS_DeputyFIO",
	depk.DeputyKind_Name,
	concat(
		depk.DeputyKind_Name||', ',
		'Паспорт: Серия '||depk.Document_Ser,
		' Номер '||depk.Document_Num) as "Pasport_Deputy",
	diagIssl.diagIssl,
	dsp1.DiagSetPhase_name as "setphaseNapr",
	dsp2.DiagSetPhase_name as "setphaseLeave",
	xml_data.anmn_text, --анамнез заболевания
	xml_data.diagnos,
	xml_data.recommendations,
	xml_data.treatment
from v_EvnPS as eps 
	left join v_PersonState as ps  on ps.Person_id = eps.Person_id
	left join v_Address as addr on addr.Address_id = coalesce(ps.PAddress_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 Diag on Diag.Diag_id = coalesce(eps.Diag_pid, eps.Diag_id, eps.Diag_pid, eps.Diag_spid)
	left join PrehospWaifRefuseCause as pwrc  on pwrc.PrehospWaifRefuseCause_id = eps.PrehospWaifRefuseCause_id
	left join v_MedPersonal as MP on MP.MedPersonal_id = eps.MedPersonal_pid and MP.Lpu_id = eps.Lpu_id
	left join PersonMedHistory on ps.Person_id = PersonMedHistory.Person_id
	left join v_DiagSetPhase as dsp1 on dsp1.DiagSetPhase_id = eps.DiagSetPhase_did
	left join v_DiagSetPhase as dsp2 on dsp2.DiagSetPhase_id = eps.DiagSetPhase_aid
	left join lateral(
		select
			(xpath('data/anamnesmorbi/text()',evnxml.EvnXml_Data::xml))[1]::varchar as anmn_text,
			(xpath('data/diagnos/text()',evnxml.EvnXml_Data::xml))[1]::varchar as diagnos,
			(xpath('data/recommendations/text()',evnxml.EvnXml_Data::xml))[1]::varchar as recommendations,
			(xpath('data/treatment/text()',evnxml.EvnXml_Data::xml))[1]::varchar as treatment
		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(distinct
				concat_ws(', ', num, setDT, UslugaComplex_Name),
				';'||chr(10)) as diagIssl
		from(
			select distinct
				coalesce(eup.EvnDirection_num,ed.EvnDirection_num) as num,
				to_char(coalesce(eup.EvnDirection_setDT,ed.EvnDirection_setDT), 'dd.mm.yyyy') as setDT,
				uc.UslugaComplex_Name
			from v_EvnUslugaPar as eup 
				left join v_EvnDirection_all as ed on eup.EvnDirection_id = ed.EvnDirection_id
				left join v_UslugaComplex as uc on uc.UslugaComplex_id = eup.UslugaComplex_id
				left join v_UslugaComplexAttribute as uca on uca.UslugaComplex_id = uc.UslugaComplex_id
			where eup.EvnUslugaPar_rid = eps.EvnPS_id
				and uca.UslugaComplexAttributeType_id = 9
		)q
	)diagIssl on true
	
	left join lateral(
		select 
			dk.DeputyKind_Name,
			dps.Person_SurName,
			dps.Person_FirName,
			dps.Person_Secname,
			dps.Document_Num,
			dps.Document_Ser
		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_SurName,
					Person_FirName, 
					Person_SecName,
					Person_EdNum, 
					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 <= current_date
				order by psa.PersonEvn_insDT desc
				limit 1
			)dps on true
			limit 1
	)depk on true
where eps.EvnPS_id = cast($1 as bigint)
limit 1
