
				
			with mv as (
				select
				dbo.tzGetDate() as dt
			),
			
				
			    reg as (
                	select dbo.getregion() as region_id
            	)
				
				SELECT 
		
			PR.PersonRegister_id as "PersonRegister_id",
			PR.Lpu_iid as "Lpu_iid",
			PR.MedPersonal_iid as "MedPersonal_iid",
			PR.EvnNotifyBase_id as "EvnNotifyBase_id",
			EONN.EvnOnkoNotifyNeglected_id as "EvnOnkoNotifyNeglected_id",
			MOV.MorbusOnkoVizitPLDop_id as "MorbusOnkoVizitPLDop_id",
			MOL.MorbusOnkoLeave_id as "MorbusOnkoLeave_id",
			MO.MorbusOnko_id as "MorbusOnko_id",
			AnnArborClass.id as "AnnArborClass_id",
			AnnArborClass.Stage as "AnnArborClass_Stage",
			AnnArborClass.Name as "AnnArborClass_Name",
			MO.MorbusOnko_IsCancerReg as "MorbusOnko_IsCancerReg",MO.MorbusOnko_IsConfirmImp as "MorbusOnko_IsConfirmImp",
			M.Morbus_id as "Morbus_id",
			PS.Person_id as "Person_id",
			PS.Server_id as "Server_id",
			PS.PersonEvn_id as "PersonEvn_id",
			RTRIM(PS.Person_SurName) as "Person_Surname",
			RTRIM(PS.Person_FirName) as "Person_Firname",
			RTRIM(PS.Person_SecName) as "Person_Secname",
			to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday",
			Lpu.Lpu_Nick as "Lpu_Nick",
			LpuDisp.Lpu_Nick as "Lpu_DispNick",
			Diag.Diag_id as "Diag_id",
			Diag.diag_FullName as "Diag_Name",
			PROUT.PersonRegisterOutCause_id as "PersonRegisterOutCause_id",
			PROUT.PersonRegisterOutCause_Name as "PersonRegisterOutCause_Name",
			CASE WHEN OnkoDiag.OnkoDiag_Name is null THEN '' ELSE OnkoDiag.OnkoDiag_Code || '. ' || OnkoDiag.OnkoDiag_Name END as "OnkoDiag_Name",
			CASE 
				WHEN otd.OnkoTopographyDiag_Name is null THEN '' 
				ELSE otd.OnkoTopographyDiag_Code || '. ' || otd.OnkoTopographyDiag_Name 
			END as "OnkoTopographyDiag_Name",
			CASE WHEN (MO.MorbusOnko_IsMainTumor is null OR MO.MorbusOnko_IsMainTumor < 2) THEN 'Нет' ELSE 'Да' END as "MorbusOnko_IsMainTumor",
			TumorStage.TumorStage_Name as "TumorStage_Name",
			to_char(MO.MorbusOnko_setDiagDT, 'DD.MM.YYYY') as "MorbusOnko_setDiagDT",
			to_char(PR.PersonRegister_setDate, 'DD.MM.YYYY') as "PersonRegister_setDate",
			to_char(PR.PersonRegister_disDate, 'DD.MM.YYYY') as "PersonRegister_disDate",
			RTRIM(MP.Person_Fio) as "MedPersonal_Fio"
		
			
				FROM  
				
		 v_PersonState PS 
			inner join v_PersonRegister PR on PR.Person_id = PS.Person_id
			left join v_PersonRegisterOutCause PROUT on PROUT.PersonRegisterOutCause_id = PR.PersonRegisterOutCause_id
			left join v_EvnOnkoNotify EON on EON.EvnOnkoNotify_id = PR.EvnNotifyBase_id
			left join v_EvnOnkoNotifyNeglected EONN on EONN.EvnOnkoNotify_id = PR.EvnNotifyBase_id
			left join v_Morbus M on M.Morbus_id = coalesce(EON.Morbus_id, PR.Morbus_id)
			left join v_MorbusOnko MO on MO.Morbus_id = M.Morbus_id
			left join lateral (
				select *
				from v_MorbusOnkoBase MOB
				where MOB.MorbusBase_id = M.MorbusBase_id 
				order by MorbusOnkoBase_id desc
				limit 1
			) MOB on true
			left join v_Diag Diag on Diag.Diag_id = PR.Diag_id
			left join nsi.OnkoTopographyDiagLink otdl on otdl.Diag_id = M.Diag_id
			left join nsi.OnkoTopographyDiag otd on otd.OnkoTopographyDiag_id = otdl.OnkoTopographyDiag_id
			
			left join v_OnkoDiag OnkoDiag on OnkoDiag.OnkoDiag_id = MO.OnkoDiag_mid
			left join v_TumorStage TumorStage on TumorStage.TumorStage_id = MO.TumorStage_id
			left join lateral (
				select PC.Lpu_id
				from v_PersonCard PC
				where PC.Person_id = PS.Person_id and PC.LpuAttachType_id = 1
				order by PC.PersonCard_begDate desc
				limit 1
			) PC on true
			left join v_Lpu Lpu on Lpu.Lpu_id = PC.Lpu_id
			left join lateral(
				select MP.Person_Fio
				from v_MedPersonal MP
				where MP.MedPersonal_id = PR.MedPersonal_iid
				limit 1
			) as MP on true
			left join lateral(
				select MOL.MorbusOnkoLeave_id
				from v_EvnSection ES
				inner join v_MorbusOnkoLeave MOL on ES.EvnSection_id = MOL.EvnSection_id
				where 
					ES.Person_id = PS.Person_id
					and ES.Diag_id = PR.Diag_id
					and MOL.OnkoLesionSide_id = MO.OnkoLesionSide_id
					and MOL.Diag_id = PR.Diag_id
				order by EvnSection_setDT desc
				limit 1
			) as MOL on true
			left join lateral(
				select MOV.MorbusOnkoVizitPLDop_id
				from v_EvnVizitPL EVP
				inner join v_MorbusOnkoVizitPLDop MOV on EVP.EvnVizitPL_id = MOV.EvnVizit_id
				where 
					EVP.Person_id = PS.Person_id
					and EVP.Diag_id = PR.Diag_id
					and MOV.OnkoLesionSide_id = MO.OnkoLesionSide_id
					and MOV.Diag_id = PR.Diag_id
				order by EvnVizitPL_setDT desc
				limit 1
			) MOV on true
			left join lateral(
				select pd.Lpu_id
				from v_PersonRegisterDispLink prdl
				inner join v_PersonDisp pd on pd.PersonDisp_id = prdl.PersonDisp_id
				where 
					prdl.PersonRegister_id = PR.PersonRegister_id
				order by pd.PersonDisp_begDate desc
				limit 1
			) pd on true
			left join lateral (
				select
					MOC.AnnArborClass_id as id,
					left(AAC.AnnArborClass_Name, strpos(':', AAC.AnnArborClass_Name) - 8) as Stage,
					AAC.AnnArborClass_Name as Name
				from v_MorbusOnkoChem MOC
				inner join v_AnnArborClass AAC on MOC.AnnArborClass_id = AAC.AnnArborClass_id
				where MOC.MorbusOnko_id = MO.MorbusOnko_id
				order by MOC.MorbusOnkoChem_insDT desc
				limit 1
			) AnnArborClass on true
			left join v_Lpu LpuDisp on LpuDisp.Lpu_id = pd.Lpu_id
		
						 WHERE 
						(1 = 1) and PR.PersonRegisterType_id = 3 
				and exists(
					select 1
					from v_Person_all PStmp
					where PStmp.Person_id = PS.Person_id
			 and lower(PStmp.Person_FirName) like lower('любовь')||'%' and lower(PStmp.Person_SecName) like lower('александровна')||'%' and lower(PStmp.Person_SurName) like lower('тюпанова')||'%')  and ( exists (
						select
							PC.personcard_id
						from
							
                                v_PersonCard_all PC
                                left join v_LpuRegion LR on LR.LpuRegion_id = PC.LpuRegion_id
                                left join v_LpuRegion LR_Fap on LR_Fap.LpuRegion_id = PC.LpuRegion_fapid
                                left join v_MedStaffRegion MedStaffRegion on MedStaffRegion.LpuRegion_id = PC.LpuRegion_id
                                left join v_MedStaffFact msf on msf.MedStaffFact_id = MedStaffRegion.MedStaffFact_id and MedStaffRegion.Lpu_id = msf.Lpu_id
                                left join persis.Post p on p.id = msf.Post_id
                            
						where
							PC.Person_id = PS.Person_id
									and (
										PC.PersonCard_endDate is null
										or PC.PersonCard_endDate::timestamp > (select dt from mv)
									)
							 and PC.Lpu_id = '13100875' and PC.Lpu_id = '13100875'
						limit 1
					)) 
						
				
						ORDER BY 
							
						PR.PersonRegister_setDate DESC,
						PR.PersonRegister_id
						
				OFFSET 0 ROWS		
				FETCH NEXT 100 ROWS ONLY 
				
