select 
	   visit.EvnDirection_id, visit.Evn_id, --constype.ConsultationType_Code, 
	   ved.MedPersonal_id, vpe.Person_id,ved.EvnDirection_setDate as signature_date, ved.Post_id as post_id,
        ved.Lpu_sid as represented_lpu_id, ved.Lpu_sid as custodian_lpu_id, vpt.PassportToken_tid as directionMoID, l.Lpu_Name as directionLpuName
		, mpr.Person_Snils, mp.MedPost_Code, mp.MedPost_Name, adrmp.Address_Address as mpAddress_Address, adrmp.KLRgn_id as mpKLRgn_id, adrmp.KLADR_Code as mpAddress_Kladr
		, p.Person_SurName, p.Person_FirName, p.Person_SecName
		, ved.Lpu_sid, l.Lpu_phone as directionLpu_phone, adrmo.Address_Address as moAddress_Address, adrmo.KLRgn_id as moKLRgn_id, adrmo.KLADR_Code as moAddress_Kladr, adrmo.Address_House, street.KLStreet_AOGUID
		, constype.ConsultationType_Code, constype.ConsultationType_Name
		, visit.Evn_setDT, visit.Evn_disDT, visit.MedPersonal_id as vzMedPersonal_id, vzpr.Person_id as vzPerson_id, vzpr.Person_Snils as vzPerson_Snils
		, vzmp.MedPost_Code as vzMedPost_Code, vzmp.MedPost_Name as vzMedPost_Name, vzadr.Address_Address as vzAddress_Address, vzadr.KLRgn_id as vzKLRgn_id, vzadr.KLADR_Code as vzAddress_Kladr
		, vzpr.Person_SurName as vzPerson_SurName, vzpr.Person_FirName as vzPerson_FirName, vzpr.Person_SecName as vzPerson_SecName, vzpr.Person_Phone as vzPerson_Phone
		, vzl.Lpu_id as vzLpu_id, vzl.Lpu_phone as vzLpu_phone, vzl.Lpu_Name as vzLpu_Name, vzpt.PassportToken_tid as vzMoID, adrvzmo.Address_Address as vzmoAddress_Address, adrvzmo.KLRgn_id as vzmoKLRgn_id, adrvzmo.KLADR_Code as vzmoAddress_Kladr
		, coalesce(visit.VizitClass_Code, 1) as VizitClass_Code, case when coalesce(visit.VizitClass_Code, 1) = 1 then 'первичный' else 'повторный' end as VizitClass_Name
		, uslap.UslugaComplex_Code, uslap.UslugaComplex_Name
		, vitpar.PersonVitalParam_SistolPress, vitpar.PersonVitalParam_DiastolPress, vitpar.PersonVitalParam_HeartFrequency, vitpar.PersonVitalParam_BreathFrequency 
		, visit.DiagSetPhase_Code, visit.DiagSetPhase_Name
		 
		, cast(x.EvnXml_Data as varchar) as EvnXml_Data
		, coalesce(nullif(trim(both '{}' from xpath('./data/resolution/text()', x.evnxml_data)::varchar),''), 'Заключение отсутсвует') as resolution
		, coalesce(nullif(trim(both '{}' from xpath('./data/recommendations/text()', x.evnxml_data)::varchar),''), 'Нет рекомендаций') as recommendations
		, trim(both '{}' from xpath('./data/complaint/text()', x.evnxml_data)::varchar) as complaint
		, trim(both '{}' from xpath('./data/anamnesis/text()', x.evnxml_data)::varchar) as anamnesis
		, trim(both '{}' from xpath('./data/receivetreatment/text()', x.evnxml_data)::varchar) as receiveTreatment
		, trim(both '{}' from xpath('./data/objectivestatus/text()', x.evnxml_data)::varchar) as objectiveStatus
			 
		, d.Diag_SCode, d.Diag_Name, dirnext.DirType_Code as dirnextDirType_Code, dirnext.DirType_Name as dirnextDirType_Name
		, visit.UslugaComplex_Code as consultUslugaComplex_Code, visit.UslugaComplex_Name as consultUslugaComplex_Name, aim_code, aim_name
		, 1 as diagDegreeCode, 'Предварительный диагноз' as diagDegreeName
		, dircon.EvnDirection_id as dircon_id, dircon.EvnDirection_setDT as dircon_desDT, dircontype.DirType_Code as dircon_typecode, dircontype.DirType_Name as dircon_typename
		, dirconusl.UslugaComplex_Code as dircon_uslcode, dirconusl.UslugaComplex_Name as dircon_uslname

        from 
			(select vz.EvnDirection_id, vz.Evn_id, vzs.EvnVizitPL_id as vzsid, CAST(NULL AS bigint) as ConsultationType_id, vz.MedStaffFact_id, e.Evn_setDT, e.Evn_disDT, vz.MedPersonal_id
			, vzcl.VizitClass_Code, dsp.DiagSetPhase_Code, dsp.DiagSetPhase_Name, vzs.Diag_id
			, uc.UslugaComplex_Code , uc.UslugaComplex_Name
			, 10 as aim_code, 'по заболеваниям' as aim_name
			from v_EvnVizitPL vzs  
           
     
			inner join EvnVizit vz  
           
      on vzs.EvnVizitPL_id = vz.Evn_id
			inner join Evn e  
           
      on e.Evn_id = vz.Evn_id
			left join v_VizitClass vzcl  
           
      on vzs.VizitClass_id = vzcl.VizitClass_id
			left join v_DiagSetPhase dsp  
           
      on vz.DiagSetPhase_id = dsp.DiagSetPhase_id
			 left join lateral  
			   (select     
				uc1.nomenmedusluga_uslugacode as UslugaComplex_Code, uc1.nomenmedusluga_name as UslugaComplex_Name
			   from
			   dbo.v_evnusluga eu  
           
     
				left join dbo.v_UslugaComplex uc  
           
      on uc.UslugaComplex_id = eu.UslugaComplex_id
				left join nsi.ConsultationType ct  
           
      on ct.ConsultationType_id = uc.ConsultationType_id
				 
         left join lateral 
      (select   uc1.nomenmedusluga_uslugacode, uc1.nomenmedusluga_name
			   from nsi.p_NomenMedUsluga_getUsluga(uc.UslugaComplex_id) uc1 where uc1.nomenmedusluga_uslugacode is not null  LIMIT 1 ) uc1  on true 
			   where
			   eu.EvnUsluga_pid = vzs.EvnVizitPL_id
			   order by case when uc.ConsultationType_id is not null then 0 else 1 end
			    limit 1 
			   ) uc   on true 

			where vzs.EvnVizitPL_id = $1 

			union 

			select vz.EvnDirection_id, vz.Evn_id, vzs.EvnVizitDisp_id as vzsid, CAST(NULL AS bigint) as ConsultationType_id, vz.MedStaffFact_id, e.Evn_setDT, e.Evn_disDT, vz.MedPersonal_id
			, case when eprev.evn_id is NULL then 1 else 2 end as VizitClass_Code
			, dsp.DiagSetPhase_Code, dsp.DiagSetPhase_Name, vzs.Diag_id
			, null as UslugaComplex_Code, null as UslugaComplex_Name
			, 22 as aim_code, 'диспансеризация' as aim_name
			from v_EvnVizitDisp vzs  
           
     
			inner join EvnVizit vz  
           
      on vzs.EvnVizitDisp_id = vz.Evn_id
			inner join Evn e  
           
      on e.Evn_id = vz.Evn_id
			 left join lateral  
			 (select    ep.Evn_id from Evn ep  
           
      where ep.Evn_rid = e.Evn_rid and ep.Evn_setDT < e.Evn_setDT
			  limit 1 ) eprev  on true 
			left join v_DiagSetPhase dsp  
           
      on vz.DiagSetPhase_id = dsp.DiagSetPhase_id

			where vzs.EvnVizitDisp_id = $2 

			union 

			select us.EvnDirection_id, us.Evn_id, vzs.EvnUslugaTelemed_id as vzsid, vzs.ConsultationType_id, us.MedStaffFact_id, e.Evn_setDT, e.Evn_disDT, us.MedPersonal_id
			, case when eprev.evn_id is NULL then 1 else 2 end as VizitClass_Code, dsp.DiagSetPhase_Code, dsp.DiagSetPhase_Name, us.Diag_id
			, uc1.nomenmedusluga_uslugacode as UslugaComplex_Code
			, uc1.nomenmedusluga_name as UslugaComplex_Name
			, 10 as aim_code, 'по заболеваниям' as aim_name
			from v_EvnUslugaTelemed vzs  
           
     
			inner join EvnUsluga us  
           
      on  vzs.EvnUslugaTelemed_id = us.Evn_id
			inner join Evn e  
           
      on e.Evn_id = us.Evn_id
			left join dbo.v_UslugaComplex uc  
           
      on uc.UslugaComplex_id = us.UslugaComplex_id
			left join nsi.ConsultationType ct  
           
      on ct.ConsultationType_id = uc.ConsultationType_id
			 
         left join lateral 
      (select   uc1.nomenmedusluga_uslugacode, uc1.nomenmedusluga_name
			   from nsi.p_NomenMedUsluga_getUsluga(uc.UslugaComplex_id) uc1 where uc1.nomenmedusluga_uslugacode is not null  LIMIT 1 ) uc1  on true 
			 left join lateral  
 			(select    ep.Evn_id from Evn ep  
           
      where ep.Evn_rid = e.Evn_rid and ep.Evn_setDT < e.Evn_setDT
 			 limit 1 ) eprev  on true 
			left join v_DiagSetPhase dsp  
           
      on vzs.DiagSetPhase_id = dsp.DiagSetPhase_id
			where vzs.EvnUslugaTelemed_id = $3 
			) visit 

			 left join lateral  
 				((select    'A02.12.002' as UslugaComplex_Code, 'Измерение артериального давления на периферических артериях' as UslugaComplex_Name
			from PersonVitalParam pvp where pvp.Evn_id = visit.Evn_id and coalesce(pvp.PersonVitalParam_SistolPress, 0) <> 0 and coalesce(pvp.PersonVitalParam_DiastolPress, 0) <> 0  limit 1 )
			union
			(select    'A02.10.002' as UslugaComplex_Code, 'Измерение частоты сердцебиения' as UslugaComplex_Name
			from PersonVitalParam pvp where pvp.Evn_id = visit.Evn_id and coalesce(pvp.PersonVitalParam_HeartFrequency, 0) <> 0  limit 1 ) 
			union
			(select    'A02.09.001' as UslugaComplex_Code, 'Измерение частоты дыхания' as UslugaComplex_Name 
			from PersonVitalParam pvp where pvp.Evn_id = visit.Evn_id and coalesce(pvp.PersonVitalParam_BreathFrequency , 0) <> 0  limit 1 )
			
			) uslap  on true 

			 left join lateral  (select    pvp.PersonVitalParam_SistolPress, pvp.PersonVitalParam_DiastolPress, pvp.PersonVitalParam_HeartFrequency, pvp.PersonVitalParam_BreathFrequency
			from PersonVitalParam pvp where pvp.Evn_id = visit.Evn_id  
			 limit 1 ) vitpar  on true 

			left join v_EvnDirection ved  
           
      on ved.EvnDirection_id = visit.EvnDirection_id
            left join v_PersonEvn vpe  
           
      ON vpe.PersonEvn_id = ved.PersonEvn_id

			 left join lateral 
			(select   mpr.Person_id, mpr.Person_Snils  from v_MedPersonal mpr  
           
     
			where mpr.MedPersonal_id = ved.MedPersonal_id and mpr.WorkData_begDate <= visit.Evn_setDT
			order by mpr.WorkData_begDate desc 
			 limit 1 
			) mpr  on true 
            
			left join dbo.v_Lpu l   
           
      on ved.Lpu_sid = l.Lpu_id
			left join dbo.v_Org O   
           
      on L.Org_id = O.Org_id
            LEFT JOIN fed.v_PassportToken vpt  
           
      ON vpt.Lpu_id = ved.Lpu_sid
			left join persis.v_Post post   
           
      on post.id = ved.Post_id
			
			left join persis.FRMPPost fp  
           
      on fp.id=post.frmpEntry_id
			left join nsi.MedPersonalPost medpp  
           
     on medpp.MedPersonalPost_id= fp.Frmr_id
			left join nsi.MedPost mp   
           
      on mp.MedPost_id = medpp.MedPost_id
			left join dbo.v_PersonState p  
           
      on mpr.Person_id = p.Person_id
			left join dbo.v_Address_KLAdr adrmp   
           
      on adrmp.Address_id = coalesce(p.UAddress_id, p.PAddress_id)
			left join dbo.v_Address_KLAdr adrmo   
           
      on adrmo.Address_id = coalesce(o.UAddress_id, o.PAddress_id)
			left join dbo.v_KLStreet street  
           
      on adrmo.KLStreet_id = street.KLStreet_id

			left join v_MedStaffFact msf  
           
      on msf.MedStaffFact_id = visit.MedStaffFact_id
			left join persis.v_Post vzpost  
           
      on msf.Post_id = vzpost.id
			
			left join persis.FRMPPost vzfp  
           
      on vzfp.id=vzpost.frmpEntry_id
			left join nsi.MedPersonalPost vzmedpp  
           
     on vzmedpp.MedPersonalPost_id= vzfp.Frmr_id
			left join nsi.MedPost vzmp   
           
      on vzmp.MedPost_id = vzmedpp.MedPost_id
			left join nsi.MedPersonalPost vzmpp  
           
      on vzmpp.MedPost_id = vzpost.MedPost_id
			left join nsi.MedPersonalPostConsultationTypeLink link  
           
      on link.MedPersonalPost_id = vzmpp.MedPersonalPost_id

			 
         left join lateral 
      (select   uc.ConsultationType_id
			from v_evnusluga eu  
           
     
			left join dbo.UslugaComplex uc  
           
      on uc.UslugaComplex_id = eu.UslugaComplex_id
			where eu.EvnUsluga_pid = visit.vzsid
			 limit 1 
			) constypeusl  on true 
			left join  nsi.ConsultationType constype  
           
      on constype.ConsultationType_id = coalesce(constypeusl.ConsultationType_id, visit.ConsultationType_id, link.ConsultationType_id)

			 
         left join lateral 
     (select   vzmpr.Person_id from v_MedPersonal vzmpr  
           
     
			where vzmpr.MedPersonal_id = visit.MedPersonal_id and vzmpr.WorkData_begDate <= visit.Evn_setDT
			order by vzmpr.WorkData_begDate desc 
			 limit 1 
			) vzmpr  on true 

            left join v_PersonState vzpr  
           
      on vzmpr.Person_id = vzpr.Person_id
			left join dbo.v_Address_KLAdr vzadr  
           
      on vzadr.Address_id = coalesce(vzpr.UAddress_id, vzpr.PAddress_id)
			left join dbo.v_Lpu vzl  
           
      on msf.Lpu_id = vzl.Lpu_id
			left join dbo.v_Org vzO  
           
      on vzl.Org_id = vzO.Org_id
            LEFT JOIN fed.v_PassportToken vzpt  
           
      ON vzpt.Lpu_id = vzl.Lpu_id
			left join dbo.v_Address_KLAdr adrvzmo  
           
      on adrvzmo.Address_id = coalesce(vzo.UAddress_id, vzo.PAddress_id)

			 
         left join lateral 
      (select   EvnXml_Data from EvnXml  
           
     
			where visit.Evn_id = EvnXml.Evn_id 
			order by case when EvnXml.XmlType_id = 3 then 0 else 1 end
			 limit 1 ) x  on true 

			left join v_Diag d  
           
      on visit.Diag_id = d.Diag_id
			
			 
         left join lateral 
      (select    dirnexttype.DirType_Code, dirnexttype.DirType_Name
			from v_EvnDirection dirnext  
           
     
			left join v_DirTypeLink dirnexttl  
           
      on dirnext.DirType_id = dirnexttl.DirType_fid
			left join fed.v_DirType dirnexttype  
           
      on dirnext.DirType_id = dirnexttl.DirType_id
			where dirnext.EvnDirection_rid = visit.vzsid order by dirnexttype.DirType_Code DESC, dirnext.EvnDirection_setDate asc
			 limit 1 ) dirnext  on true 

 			left join v_EvnDirection dircon  
           
      on dircon.EvnDirection_pid = visit.Evn_id
			
			 
         left join lateral 
      (select    dirtype.DirType_Code, dirtype.DirType_Name
			from v_DirTypeLink dirtl  
           
      left join fed.v_DirType dirtype  
           
      on dirtl.DirType_id = dirtype.DirType_id
			where dircon.DirType_id = dirtl.DirType_fid order by dirtype.DirType_Code DESC
			 limit 1 ) dircontype  on true 
			
			 
         left join lateral 
      (select  
			uc1.nomenmedusluga_uslugacode as UslugaComplex_Code, uc1.nomenmedusluga_name as UslugaComplex_Name
			   from nsi.p_NomenMedUsluga_getUsluga(dircon.UslugaComplex_did) uc1 where uc1.nomenmedusluga_uslugacode is not null 
			    LIMIT 1 ) dirconusl  on true 
			   
			

			 
         left join lateral 
      (select    bgt.BloodGroupType_Code + 100 as BloodGroupType_Code, bgt.BloodGroupType_Name, rft.RhFactorType_Code + 210 as RhFactorType_Code, rft.RhFactorType_Name
			from v_PersonBloodGroup pbg  
           
     
			inner join v_BloodGroupType bgt  
           
      on pbg.BloodGroupType_id = bgt.BloodGroupType_id
			inner join v_RhFactorType rft  
           
      on pbg.RhFactorType_id = rft.RhFactorType_id
			where pbg.Person_id = vpe.Person_id
			order by pbg.PersonBloodGroup_setDT desc
			 limit 1 ) blood  on true 

			WHERE (1=1)
