--730022509239090
with params as(
	select
		/*printEvnPrescrMse_pg.rptdesign*/
		cast($1 as bigint) as p_EvnPrescrMse_id,
		cast($2 as bigint) as p_Region_id
)

select
	LPU.Lpu_Name as "Lpu_Name",
	LPU.UAddress_Address as "LpuAddress",
	lpu.Lpu_OGRN as "Lpu_OGRN",
	to_char(EvnVK_setDate, 'dd.mm.yyyy') as "EvnVK_DirectionDate",
	concat(
		to_char(EvnVK_DirectionDate, '« dd »'),
		dbo.MonthToWord(EvnVK_DirectionDate, 2),
		to_char(EvnVK_DirectionDate, ' yyyy')) as "EvnVK_DirectionDateSTR", --дата протокола
	EvnVK_NumProtocol as "EvnVK_NumProtocol", --номер протокола
	rpt.check_pg(coalesce(EvnPrescrMse_IsCanAppear, 1) = 1) as "IsCanAppear", ---2.не может явится в бюро
	rpt.check_pg(EvnPrescrMse_IsPalliative = 2) as "IsPalliative",  --3. нуждается в полиативной помощи
	to_char(EPM.EvnPrescrMse_issueDT, 'dd.mm.yyyy') as "EvnPrescrMse_issueDT",
	concat(
		to_char(EPM.EvnPrescrMse_issueDT, '« dd »'),
		dbo.MonthToWord(EPM.EvnPrescrMse_issueDT, 2),
		to_char(EPM.EvnPrescrMse_issueDT, ' yyyy')) as "EvnPrescrMse_issueDTSTR", 
	cast(EPM.MseDirectionAimType_id as varchar (2)) as "MseDirectionAimType_id", --цель направления
	coalesce(EvnPrescrMse_AimMseOver, ' ') as "EvnPrescrMse_AimMseOver", --другая цель
	P.Person_id as "Person_id",
	concat_ws(' ',
		P.Person_Surname,
		P.Person_FirName,
		P.Person_SecName) as "Person_Fio",
	to_char(P.Person_BirthDay, 'dd.mm.yyyy') as "Person_BirthDay",
	concat(
		to_char(P.Person_BirthDay, '« dd »'),
		dbo.MonthToWord(P.Person_BirthDay, 2),
		to_char(P.Person_BirthDay, ' yyyy')) as "Person_BirthDaySTR", --7. дата рождения
	case
		when dbo.AgeYMD(P.Person_BirthDay,EPM.EvnPrescrMse_setDate, 1) > 0
			then dbo.AgeYMD(P.Person_BirthDay,EPM.EvnPrescrMse_setDate, 1)
		when dbo.AgeYMD(P.Person_BirthDay,EPM.EvnPrescrMse_setDate, 1) = 0
			then dbo.AgeYMD(P.Person_BirthDay,EPM.EvnPrescrMse_setDate, 2)
		end as "years", --возраст PROMEDWEB-54350
	P.Sex_id as "Sex_id", 
	NationalityStatus.KLCountry_id as "G643",
	rpt.check_pg(Documenttype.DocumentType_id = 20) as "NOTGR", --9. Без гражданства
	MilitaryKind_id as "MilitaryKind_id", --10.Отношение к воинской обязанности
	PersonAddr.Address_Address as "Address_Address",
	coalesce(Country.KLCountry_Nick, Country.KLCountry_Name) as "KLCountry_Name",
	PersonAddr.Address_Zip as "Address_Zip",
	KLRgn.KLRgn_FullName as "KLRgn_FullName",
	KLSubRgn.KLSubRgn_FullName as "KLSubRgn_FullName",
	coalesce (KLCity_name,KLTown_name) as "KLCity",
	Strit.KLStreet_FullName as "KLStreet_FullName",
	concat(
		PersonAddr.Address_House,
		'('||nullif(trim(PersonAddr.Address_Corpus), '')||')') as "Address_House",
	PersonAddr.Address_Flat as "Flat",
	KLAreaType.KLAreaType_id as "KLAreaType_id",
	PersonAddrU.Address_Address as "UPersonAddress",
	case when n_ss.SocStatus_id = 22 then 'V' end as "bomzh", --PROMEDWEB-66655
	OrgType.OrgType_id as "OrgType_id", --13. Гражданин находится:
	p_Region_id as "Region_id",
	rpt.check_pg(OrgType.OrgType_id = 11) as "OrgLpu",
	rpt.check_pg(OrgType.OrgType_id = 25) as "OrgSoc",
	rpt.check_pg(OrgType.OrgType_id = 23) as "OrgEdu", 
	rpt.check_pg(OrgType.OrgType_id = 19 
		or p_Region_id = 59 and OrgType.OrgType_id = 1) as "OrgOther",
	AddressOrg.Address_Nick as "Address_Nick",
	Org.Org_OGRN as "Org_OGRN",
	EvnPrescrMse_IsPersonInhabitation as "EvnPrescrMse_IsPersonInhabitation",
	coalesce(P.Person_Phone, PersonInfo.PersonInfo_InternetPhone) as "Phone",
	na.NewslatterAccept_Email as "PersonInfo_Email",
	concat_ws('-',
		substring(P.Person_Snils,1,3),
		substring(P.Person_Snils,4,3),
		substring(P.Person_Snils,7,3),
		substring(P.Person_Snils,10,2)) as "Person_Snils",
	Documenttype.DocumentType_Name as "DocumentType_Name",
	Document.Document_Num as "Document_Num",
	Document.Document_Ser as "Document_Ser",
	Orgdep.OrgDep_Nick as "OrgDep_Nick",
	to_char(Document.Document_begDate, 'dd.mm.yyyy') as "Document_begDate",
	concat(
		to_char(Document.Document_begDate, '« dd »'),
		dbo.MonthToWord(Document.Document_begDate, 2),
		to_char(Document.Document_begDate, ' yyyy')) as "Document_begDateSTR",
	---данные представителя 17. Контактная информация, телефон
	P2.Person_Fio as "Person_Fio2",
	coalesce(P2.PersonPhone_Phone, PersonInfoPred.PersonInfo_InternetPhone) as "PhonePred",
	p_na.NewslatterAccept_Email as "EmailPred",
	concat_ws('-',
		substring(P2.Person_Snils,1,3),
		substring(P2.Person_Snils,4,3),
		substring(P2.Person_Snils,7,3),
		substring(P2.Person_Snils,10,2)) as "SnilsPred",
	DocumentTypePred.DocumentType_Name as "DocumentType_NamePred",
	DocumentPred.Document_Num as "Document_NumPred",
	DocumentPred.Document_Ser as "Document_SerPred", 
	OrgdepPred.OrgDep_Nick as "OrgdepPred",
	to_char(DocumentPred.Document_begDate, 'dd.mm.yyyy') as "Document_begDatePred",
	concat(
		to_char(DocumentPred.Document_begDate, '« dd »'),
		dbo.MonthToWord(DocumentPred.Document_begDate, 2),
		to_char(DocumentPred.Document_begDate, ' yyyy')) as "Document_begDateSTRPred",
	OrgURL.Org_Name as "OrgURL_Name", ---17.5 Юридическое лицо по опеке
	OrgURL.Org_OGRN as "OrgURL_OGRN",  
	addressURL.Address_Address as "OrgURL_addressURL",
	EvnPrescrMse_IsFirstTime as "EvnPrescrMse_IsFirstTime",
	InvalidGroupType_id as "InvalidGroupType_id", 
	case
		when EvnPrescrMse_IsFirstTime = 2 --Повторно
			then coalesce(
				to_char(EvnPrescrMse_InvalidEndDate, 'dd.mm.yyyy'),
				'Бессрочно')
		else ''
		end as "EvnPrescrMse_InvalidEndDate", --дата до которой установлена инвалидность
	EvnPrescrMse_InvalidPeriod as "EvnPrescrMse_InvalidPeriod",
		--период, в течении которого гражданин находился на инвалидности 
	cast(InvalidCouseType_id as varchar(3)) as "InvalidCouseType_id", --19.4 причина инвалидности
	coalesce(EvnPrescrMse_InvalidCouseAnother, ' ') as "EvnPrescrMse_InvalidCouseAnother",
	coalesce(EvnPrescrMse_InvalidCouseAnotherLaw, ' ') as "EvnPrescrMse_InvalidCouseAnotherLaw", 
	-- ИНЫЕ причина инвалидности, другое законодательство
	EvnPrescrMse_InvalidPercent as "EvnPrescrMse_InvalidPercent",  --степень инвалидности
	ProfDisabilityPeriod_name as "ProfDisabilityPeriod_name", --19.6
	EvnPrescrMse_ProfDisabilityEndDate as "EvnPrescrMse_ProfDisabilityEndDate",
	--19.7. дата, до которой установлена степень утраты
	EvnPrescrMse_ProfDisabilityAgainPercent as "EvnPrescrMse_ProfDisabilityAgainPercent",
	--19.8.  степени утраты профессиональной трудоспособности
	---20. Образование
	concat_ws(' ',ORG2.Org_Name, Org2Addr.Address_Address) as "ORGUniver",
	LearnGroupType_id as "LearnGroupType_id", --группа\класс
	EvnPrescrMse_Dop as "EvnPrescrMse_Dop",
	coalesce(EvnPrescrMse_ProfTraining,'') as "EvnPrescrMse_ProfTraining",
	---21. трудовая деятельность
	coalesce(EvnPrescrMse_MainProf,'') as "SpecPerson",
	coalesce(EvnPrescrMse_Skill,'') as "EvnPrescrMse_Skill", --квалификация
	EvnPrescrMse_ExpPost as "EvnPrescrMse_ExpPost", --стаж по должности
	EvnPrescrMse_CondWork as "EvnPrescrMse_CondWork", --условия
	coalesce(ORG_Job.Org_Name, '') as "ORG_Job", --место работы
	coalesce(Address_Job.Address_Address,'') as "Address_Job", --адрес места работы
	EvnPrescrMse_OrgMedDate as "EvnPrescrMse_OrgMedDate", ---22. наблюдается в МО с 
	rpt.StripHTML2_pg(coalesce(EPM.EvnPrescrMse_DiseaseHist,'')) as "EvnPrescrMse_DiseaseHist",
	rpt.StripHTML2_pg(coalesce(EPM.EvnPrescrMse_LifeHist,'')) as "EvnPrescrMse_LifeHist",
	---26. Результаты эффективности  проведенных реабилитационных меропритий
	MeasuresRehabEffect.MeasuresRehabEffect_IsRecovery as "MeasuresRehabEffect_IsRecovery", --восстановление нарушенных функций
	IPRAResult_rid as "IPRAResult_rid",
	MeasuresRehabEffect_IsCompensation as "MeasuresRehabEffect_IsCompensation", ---достижение компенсации..
	IPRAResult_cid as "IPRAResult_cid",
	IPRARegistry.IPRARegistry_Number as "IPRARegistry_Number",
	IPRARegistry.IPRARegistry_issueDate as "IPRARegistry_issueDate",
	IPRARegistry.IPRARegistry_Protocol as "IPRARegistry_Protocol",
	IPRARegistry.IPRARegistry_ProtocolDate as "IPRARegistry_ProtocolDate",
	---27. Антропометрические данные
	case
		when pw.Okei_id = 36
			then cast(coalesce(pw.PersonWeight_Weight,0) as float) / 1000
		else coalesce(pw.PersonWeight_Weight,0)
		end as "PersonWeight_Weight",
	case 
		when PWR.Okei_id = 36
			then cast(coalesce(PWR.PersonWeight_Weight,0) as float) / 1000
		else PWR.PersonWeight_Weight
		end as "PersonWeight_WeightR", --при рождении
	cast(coalesce(PH.PersonHeight_Height,0) as float) as "PersonHeight_Height",
	case
		when pw.Okei_id = 36
			then round(
				cast((PW.PersonWeight_Weight/1000)
				/(PH.PersonHeight_Height*PH.PersonHeight_Height/10000) as numeric),
				3) 
		else round(
			cast((PW.PersonWeight_Weight)
			/(PH.PersonHeight_Height*PH.PersonHeight_Height/10000) as numeric), 
			3) 
		end as "idxWeight",
	PhT.PhysiqueType_Name as "EvnPrescrMse_Physique",
	EvnPrescrMse_DailyPhysicDepartures as "EvnPrescrMse_DailyPhysicDepartures",
	concat_ws(' /', EvnPrescrMse_Waist, EvnPrescrMse_Hips) as "WaistHips",
	EvnPrescrMse_WeightBirth as "EvnPrescrMse_WeightBirth", --масса тела при рождении
	EvnPrescrMse_PhysicalDevelopment as "EvnPrescrMse_PhysicalDevelopment", ---физическое  развитие
	rpt.StripHTML2_pg(coalesce(EvnPrescrMse_State,' ')) as "EvnPrescrMse_State",
	-- 28. состояние здоровья при направлении на МСЭ
	---30.Диагноз при направлении на медико-социальную экспертизу
	coalesce(EPM.EvnPrescrMse_MainDisease, D1.Diag_Name) as "diag1_FullName",
	D1.Diag_FullName as "Diag1_Code",
	Diag3.Diag3_Name as "diag3_FullName",
	Diag2.Diag2_Name as "diag2_FullName",
	Diag2_code.Diag2_Code as "Diag2_Code",
	Diag4.Diag4_Name as "diag4_FullName",
	ClinicalForecastType_id as "ClinicalForecastType_id",
	ClinicalPotentialType_id as "ClinicalPotentialType_id",
	ClinicalForecastType_did as "ClinicalForecastType_did",
	---34.-37 рекомендации
	rpt.StripHTML2_pg(coalesce(EvnPrescrMse_Recomm,'')) as "EvnPrescrMse_Recomm",
	rpt.StripHTML2_pg(coalesce(EvnPrescrMse_MeasureSurgery,'')) as "EvnPrescrMse_MeasureSurgery",
	rpt.StripHTML2_pg(coalesce(EvnPrescrMse_MeasureProstheticsOrthotics,'')) as "EvnPrescrMse_MeasureProstheticsOrthotics",
	rpt.StripHTML2_pg(coalesce(EvnPrescrMse_HealthResortTreatment,'')) as "EvnPrescrMse_HealthResortTreatment",
	EPM.EvnVK_id as "EvnVK_id",
	mp.Person_Fio as "PredVKFIO",
	rtrim(coalesce(Usluga.usluganame, '')) as "usluganame", --  29. часть 1
	rpt.StripHTML2_pg(coalesce(EvnPrescrMse_DopRes,'')) as "EvnPrescrMse_DopRes",-- 29. часть 2
	---17.2 Документ представителя
	coalesce(DA.DocumentAuthority_Name, EvnPrescrMse_Document) as "EvnPrescrMse_Document",
	EvnPrescrMse_DocumentNum as "EvnPrescrMse_DocumentNum",
	EvnPrescrMse_DocumentSer as "EvnPrescrMse_DocumentSer",
	to_char(EvnPrescrMse_DocumentDate, 'dd.mm.yyyy') as "EvnPrescrMse_DocumentDate",
	EvnPrescrMse_DocumentIssue as "EvnPrescrMse_DocumentIssue",
	--21.4
	case
		when rtrim(coalesce(EvnPrescrMse_Spec, '')) != '' and rtrim(coalesce(post_name, '')) != ''
			then coalesce(rtrim(EvnPrescrMse_Spec), '')||', '||rtrim(coalesce(post_name, ''))
		else rtrim(coalesce(EvnPrescrMse_Spec, post_name,'Не работает'))
		end as "v214",
	MeasuresRehabEffect_comment as "MeasuresRehabEffect_comment", --комментарий п.26 
	rpt.check_pg(EvnStick_List != '') as "val25_1",
	EvnStick_List as "EvnStick_List"
from params
	inner join v_EvnPrescrMse as EPM on EPM.EvnPrescrMse_id = p_EvnPrescrMse_id
	left join v_Lpu as LPU on LPU.Lpu_id = EPM.Lpu_id
	left join v_EvnVK as EvnVK  on EvnVK.EvnVK_id = EPM.EvnVK_id
	left join v_PersonState as P on P.Person_id = EPM.Person_id --#160671
	left join NationalityStatus on NationalityStatus.NationalityStatus_id = P.NationalityStatus_id
	left join v_Document as Document on Document.Document_id = P.Document_id
	left join DocumentType on DocumentType.DocumentType_id = Document.DocumentType_id
	left join v_Orgdep as Orgdep on Orgdep.OrgDep_id = Document.OrgDep_id
	left join Address as PersonAddr on PersonAddr.Address_id = P.PAddress_id 
	left join v_KLCountry as Country on Country.KLCountry_id = PersonAddr.KLCountry_id
	left join v_KLRgn as KLRgn on KLRgn.KLRgn_id = PersonAddr.KLRgn_id
	left join v_KLSubRgn as KLSubRgn on KLSubRgn.KLSubRgn_id = PersonAddr.KLSubRgn_id
	left join v_KLCity as City on City.KLCity_id = PersonAddr.KLCity_id
	left join v_KLTown as KLTown on KLTown.KLTown_id = PersonAddr.KLTown_id
	left join v_KLStreet as Strit on Strit.KLStreet_id = PersonAddr.KLStreet_id
	left join KLAreaType on KLAreaType.KLAreaType_id = PersonAddr.KLAreaType_id
	left join Address PersonAddrU on PersonAddrU.Address_id = P.UAddress_id
	left join dbo.SocStatus as ss on ss.SocStatus_id = P.SocStatus_id
	left join nsi.SocStatusLink as n_ssl on n_ssl.SocStatus_did = ss.SocStatus_id
	left join nsi.SocStatus as n_ss on n_ssl.SocStatus_nid = n_ss.SocStatus_id
	left join Org on Org.org_id = EPM.Org_gid
	left join OrgType on OrgType.OrgType_id = Org.OrgType_id
	left join Address AddressOrg on AddressOrg.Address_id = Org.PAddress_id
	left join v_PersonInfo PersonInfo on PersonInfo.Person_id = P.Person_id
	left join v_PhysiqueType PhT on PhT.PhysiqueType_id = EPM.PhysiqueType_id
	left join lateral(
		select na.NewslatterAccept_Email
		from v_NewslatterAccept as na
		where na.Person_id = P.Person_id
		order by na.NewslatterAccept_begDate desc
		limit 1
	)na  on true --PROMEDWEB-52133
	---по представителю
	left join lateral(
		select
			Person_id,
			concat_ws(' ',
				Person_Surname,
				Person_firname,
				Person_secname) as Person_Fio,
			Document_id,
			Person_Phone as PersonPhone_Phone,
			Person_Snils
		from v_PersonState
		where Person_id = EPM.Person_sid
		limit 1
	)P2 on true
	
	left join lateral(
		select p_na.NewslatterAccept_Email
		from v_NewslatterAccept as p_na
		where p_na.Person_id = P2.Person_id
		order by p_na.NewslatterAccept_begDate desc
		limit 1
	)p_na  on true --PROMEDWEB-52133
	
	left join v_PersonInfo as PersonInfoPred on PersonInfoPred.Person_id = P2.Person_id
	left join v_Document as DocumentPred on DocumentPred.Document_id = P2.Document_id
	left join DocumentType as DocumentTypePred
		on DocumentTypePred.DocumentType_id = DocumentPred.DocumentType_id
	left join v_Orgdep as OrgdepPred on OrgdepPred.OrgDep_id = DocumentPred.OrgDep_id
	left join v_DocumentAuthority as DA on DA.DocumentAuthority_id = EPM.DocumentAuthority_id
	---законный представитель Юр. лицо
	left join v_Org as OrgURL on OrgURL.Org_id =Org_sid
	left join address as addressURL on addressURL.Address_id = OrgURL.UAddress_id
	left join ProfDisabilityPeriod
		on ProfDisabilityPeriod.ProfDisabilityPeriod_id = EPM.ProfDisabilityPeriod_id
	---сведения об образовании
	left join v_Org as ORG2 on ORG2.Org_id = EPM.Org_did
	left join v_Address as Org2Addr on Org2Addr.Address_id = ORG2.PAddress_id
	--сведения о работе
	left join v_Org as ORG_Job on ORG_Job.Org_id = EPM.Org_id
	left join v_Address as Address_Job on Address_Job.Address_id = epm.Address_oid
	left join MeasuresRehabEffect on MeasuresRehabEffect.EvnPrescrMse_id = EPM.EvnPrescrMse_id
	left join IPRARegistry on IPRARegistry.IPRARegistry_id = MeasuresRehabEffect.IPRARegistry_id
	left join v_PersonWeight as PW on PW.PersonWeight_id = EPM.PersonWeight_id
		and PW.Person_id = EPM.Person_id and coalesce(PW.WeightMeasureType_id,1) != 1
	left join v_PersonHeight as PH on PH.PersonHeight_id = EPM.PersonHeight_id
		and PH.Person_id = EPM.Person_id and coalesce(PH.HeightMeasureType_id,1) != 1
	left join v_PersonWeight as PWR on PWR.Person_id = EPM.Person_id and PWR.WeightMeasureType_id = 1
	left join v_Diag as D1 on D1.Diag_id = EPM.Diag_id
	left join v_Diag as D3 on D3.Diag_id = EPM.Diag_aid
	left join v_Diag as D2 on D2.Diag_id = EPM.Diag_sid

	left join lateral(---сопутсвующие
		select string_agg(dl.EvnPrescrMseDiagLink_DescriptDiag, ', ') as Diag2_Name
		from EvnPrescrMseDiagLink as dl
			inner join v_Diag as d on d.Diag_id = dl.Diag_id
		where dl.EvnPrescrMse_id = EPM.EvnPrescrMse_id and dl.Diag_id is not null
	)Diag2 on true

	left join lateral (---сопутсвующие коды
		select string_agg(d.Diag_FullName, ', ') as Diag2_Code
		from EvnPrescrMseDiagLink as dl
			inner join v_Diag as d on d.Diag_id = dl.Diag_id
		where dl.EvnPrescrMse_id = EPM.EvnPrescrMse_id and dl.Diag_id is not null
	)Diag2_Code on true

	left join lateral ( --осложнения основного диагноза
		select string_agg(
			concat_ws(' - ',
				d.Diag_FullName,
				dl.EvnPrescrMseDiagLink_DescriptDiag),
			';'||chr(10)) as Diag3_Name
		from EvnPrescrMseDiagLink as dl
			inner join v_Diag as d on d.Diag_id = dl.Diag_oid
		where dl.EvnPrescrMse_id = EPM.EvnPrescrMse_id and dl.Diag_oid is not null
	)Diag3 on true

	left join lateral (---осложнения сопутсвующих диагнозов
		select string_agg(d.Diag_Name, ', ') as Diag4_Name
		from EvnPrescrMseDiagMkb10Link as dlSop
			inner join EvnPrescrMseDiagLink as dl
				on dlSop.EvnPrescrMseDiagLink_id = dl.EvnPrescrMseDiagLink_id
			inner join v_Diag as d on d.Diag_id = dlSop.Diag_id
		where dl.EvnPrescrMse_id = EPM.EvnPrescrMse_id and dl.Diag_id is not null
	)Diag4 on true
	
	left join v_EvnVKExpert as VKE on EvnVK.EvnVK_id = VKE.EvnVK_id and VKE.ExpertMedStaffType_id = 1
	left join v_MedServiceMedPersonal as msmp
		on msmp.MedServiceMedPersonal_id = vke.MedServiceMedPersonal_id
	left join v_MedPersonal as mp on mp.MedPersonal_id = msmp.MedPersonal_id and mp.lpu_id = EvnVK.lpu_id

	left join lateral(
		select string_agg(
			concat_ws(' ',
				to_char(eu.EvnUsluga_setDate, 'dd.mm.yyyy'),
				uc.UslugaComplex_Code,
				uc.UslugaComplex_Name),
			', ') as usluganame
		from EvnPrescrMseLink as epml 
			inner join v_EvnUsluga as eu on eu.EvnUsluga_id = epml.EvnUsluga_id
			inner join v_UslugaComplex as uc on uc.UslugaComplex_id = eu.UslugaComplex_id
			left join v_Evn as evn on evn.Evn_id = eu.EvnUsluga_pid
		where epml.EvnPrescrMse_id = EPM.EvnPrescrMse_id
	)Usluga on true
	
	left join v_post as post on post.post_id = EPM.post_id
	left join lateral (
		select string_agg("EvnMseStick_StickNum"::varchar, ', ') as EvnStick_List
		from rpt.printEvnPrescrMse_Stick_pg(EPM.EvnPrescrMse_id)
		where "EvnMseStick_IsStick" = 2
	)EvnStick on true
