select 
	concat(lpu.lpu_name || E'\n', lpu_adr.address_address)::varchar as lpu_fullname,
	
	(case
		when d.TimetableGraf_id is not null then msfmp.MedPersonal_FIO
		when d.TimetableStac_id is not null then ls.LpuSection_Name
		when d.TimetablePar_id is not null then ls1.LpuSection_Name
		when d.LpuSection_did is not null then ls2.LpuSection_Name
		when d.TimetableMedService_id is not null then ls3.LpuSection_Name
		else rtrim(LpuSection.LpuSection_Code)||'. '||rtrim(LpuSection.LpuSection_Name)
		end)::varchar as rec_mp,
	
	concat(person.person_surname || ' ', person.person_firname || ' ', person.person_secname)::varchar as "Person_FIO",
	sex.sex_name::varchar as sex,
	to_char(person.person_birthday, 'dd.mm.yyyy')::varchar as person_birthday,
	(case
		when pol.Polis_endDate is null or d.EvnDirection_setDate < pol.Polis_endDate 
			then 
				case 
					when pol.PolisType_id = 4
						then '' 
					else coalesce(pol.Polis_Ser, '') end
				||' '|| 
				case
					when pol.PolisType_id = 4 and person.Person_EdNum is not null
						then person.Person_EdNum
					else coalesce(pol.Polis_Num, '') end
		else ''
		end)::varchar as "Polis",
	coalesce(person.Person_Snils,'')::varchar as snils,
	addr.address_address::varchar as reg_addres,
	addr.klareatype_id::int4 as klareatype_id, --город/село
	diag.diag_code::varchar as "Diag_Code",
	diag.diag_name::varchar as "Diag_Name",
	coalesce(lud.LpuUnit_Phone,ld.Lpu_Phone,'')::varchar as "Contact_Phone",--PROMEDWEB-9550
	to_char(d.evndirection_setdate, 'dd.mm.yyyy')::varchar as direction_date,
	msf.person_fio::varchar as medpersonal_fio,
	coalesce(HTMFinance.HTMFinance_Name ,PayType.PayType_Name,'' )::varchar as "PayType_Name",
	b_study.biopsystudytype_name::varchar as v11,
	eu_onko.eu_onko_string::varchar as v12,
	his_proto.his_proto_string::varchar as v13,
	null::varchar as v14, --нет реализации 
	b_rec.biopsyreceive_name::varchar as v15,
	to_char(d_his.EvnDirectionHistologic_didDate, 'dd.mm.yyyy')::varchar as v16,
	case when d_his.EvnDirectionHistologic_IsPlaceSolFormalin = 2 then 'да' else 'нет' end::varchar as v17

from (
	select dir.* from v_evndirection_all dir 
	where dir.evndirection_id = cast($1 as bigint)
) d
inner join v_lpu lpu on lpu.lpu_id = d.lpu_id --направляющая МО
left join v_address lpu_adr on lpu_adr.address_id = coalesce(lpu.uaddress_id, lpu.paddress_id)

left join TimetableGraf as ttg on d.EvnDirection_id = ttg.EvnDirection_id and ttg.Person_id is not null
left join v_medstafffact_er as msfmp on ttg.MedStaffFact_id = msfmp.MedStaffFact_id
left join TimetableStac as tts on d.EvnDirection_id = tts.EvnDirection_id and tts.Person_id is not null
left join v_LpuSection as ls on tts.LpuSection_id = ls.LpuSection_id
left join v_TimetablePar as ttp on d.TimetablePar_id = ttp.TimetablePar_id and ttp.Person_id is not null
left join v_LpuSection as ls1 on ttp.LpuSection_id = ls1.LpuSection_id
left join v_LpuSection as ls2 on ls2.LpuSection_id = d.LpuSection_did
left join TimetableMedService as ttms on d.EvnDirection_id = ttms.EvnDirection_id and ttms.Person_id is not null
left join v_MedService as MS3 on MS3.MedService_id = ttms.MedService_id
left join v_LpuSection as ls3 on ls3.LpuSection_id = MS3.LpuSection_id
left join EvnPL on EvnPL.Evn_id = d.EvnDirection_pid
left join EvnPS on EvnPS.Evn_id = d.EvnDirection_pid
left join LpuSection on EvnPL.LpuSection_id = LpuSection.LpuSection_id or EvnPS.LpuSection_id = LpuSection.LpuSection_id

left join v_diag diag on diag.diag_id = d.diag_id

left join v_LpuUnit as lud on lud.LpuUnit_id = d.LpuUnit_did
left join v_Lpu as ld on ld.Lpu_id = d.Lpu_did

left join v_personstate person on person.person_id = d.person_id
left join v_sex sex on sex.sex_id = person.sex_id
left join Polis as pol on person.Polis_id = pol.Polis_id
left join v_address addr on addr.address_id = person.uaddress_id --место регистрации

left join lateral (
		select
			msf.Person_FIO
		from v_Medstafffact as msf 
		where msf.medpersonal_id = coalesce (
				d.Medpersonal_id, EvnPL.MedPersonal_id, EvnPS.MedPersonal_pid, d.MedPersonal_did)
			or d.MedStaffFact_id = msf.MedStaffFact_id
		limit 1
) msf on true

left join v_PayType PayType on d.PayType_id = PayType.PayType_id
left join EvnDirectionHTM on d.EvnDirection_id = EvnDirectionHTM.Evn_id -- https://redmine.swan-it.ru/issues/189628
left join HTMFinance on EvnDirectionHTM.HTMFinance_id = HTMFinance.HTMFinance_id

left join v_EvnDirectionHistologic d_his on d_his.evndirectionhistologic_id = d.evndirection_id
left join v_BiopsyStudyTypeLink b_study_link on b_study_link.evndirectionhistologic_id = d_his.evndirectionhistologic_id
left join v_BiopsyStudyType b_study on b_study.BiopsyStudyType_id = b_study_link.BiopsyStudyType_id
left join v_BiopsyReceive b_rec on b_rec.biopsyreceive_id = d_his.biopsyreceive_id

left join lateral ( --п.12
	select 
		string_agg(concat(coalesce(lpu_eu.lpu_nick || ' ', ''), 
			   coalesce(uc_onko.uslugacomplex_name || ' ', ''),
			   coalesce(to_char(eu_onko.evnusluga_setdt, 'dd.mm.yyyy hh:mi'), '')
		),E'\n') eu_onko_string
	from (select d.evndirection_id, d.evndirection_pid) d
	--ищется событие по направлению: КВС/движение/ТАП/посещение
	left join v_evn evn_p on evn_p.evn_id = d.evndirection_pid
	left join lateral (select epl.evnpl_id, epl.morbus_id from v_evnpl epl where epl.evndirection_id = d.evndirection_id limit 1) epl on true 
	left join lateral (select eps.evnps_id, eps.morbus_id from v_evnps eps where eps.evndirection_id = d.evndirection_id limit 1) eps on true 
	left join lateral (
		select vizit.evnvizitpl_id, vizit.morbus_id 
		from v_evnvizitpl vizit 
		where 1 = case when vizit.evnvizitpl_pid = epl.evnpl_id then 1
					   when vizit.evndirection_id = d.evndirection_id then 1 end
		limit 2) vizit on true
	left join v_evnsection es on es.evnsection_pid = eps.evnps_id
	--ищется специфика по онкологии
	left join v_morbus m on 1 = case when m.morbus_id = coalesce(evn_p.morbus_id,epl.morbus_id,eps.morbus_id,vizit.morbus_id,es.morbus_id) then 1
								     when m.evn_pid = coalesce(evn_p.evn_id,epl.evnpl_id,eps.evnps_id,vizit.evnvizitpl_id,es.evnsection_id) then 1 end
		and m.morbustype_id=3
	left join v_evnusluga eu_onko on 1 = case when eu_onko.evnusluga_pid = coalesce(evn_p.evn_id,epl.evnpl_id,eps.evnps_id,vizit.evnvizitpl_id,es.evnsection_id) then 1
											  when eu_onko.morbus_id = m.morbus_id then 1 end
		and eu_onko.evnclass_id in (74,75,76,100,197,213) --услуги по лечению онкологии
	left join v_uslugacomplex uc_onko on uc_onko.uslugacomplex_id = eu_onko.uslugacomplex_id
	left join v_lpu lpu_eu on lpu_eu.lpu_id = eu_onko.lpu_id
) eu_onko on true


left join lateral ( --п.13
	select 
		string_agg(distinct concat(coalesce(lpu_proto.lpu_nick || ' ', ''), 
			   coalesce(to_char(his_proto.evnhistologicproto_setdate, 'dd.mm.yyyy') || ' ', ''),
			   coalesce(his_proto.evnhistologicproto_num || ' ', ''), 
			   coalesce(his_proto.evnhistologicproto_histologicconclusion, '') --заключение
		), E'\n') as his_proto_string
	from v_EvnHistologicProto his_proto 
	left join v_lpu lpu_proto on lpu_proto.lpu_id = his_proto.lpu_id
	where his_proto.person_id = d.person_id
		and his_proto.evnhistologicproto_disdt < d.evndirection_setdt --дата завершения до выписки направления
		and his_proto.evndirectionhistologic_id <> d_his.evndirectionhistologic_id
) his_proto on true 
limit 1

