SELECT
	'prod-conc-50327-rmis'/*из конфига*/ AS Rmis_Id,
	TimeTableHistory.TimeTableGrafHist_id AS Book_Id_Rmis,
	TimeTableHistory.TimeTableGraf_begTime AS Visit_Time,
	'PATIENT_NOT_ARRIVED' AS Status_id,
	MedStaffFact.LpuUnit_id AS MO_Id,
	coalesce (LpuUnit_FRMOUnitID,FRMOUnit.FRMOUnit_OID,FRMOSection.FRMOUnit_OID, Token.PassportToken_tid) AS MO_OID,
	CASE WHEN LpuUnit_FRMOid IS NOT NULL THEN PassportToken_ShortName  ||', '|| MedStaffFact.LpuUnit_Name
	ELSE PassportToken_ShortName ||', '|| coalesce(FRMOUnit.FRMOUnit_Name,FRMOSection.FRMOUnit_Name) END AS MO_Name,	
    Post.Post_Code AS ServiceSpec_Id,
	Post.Post_Name AS ServiceSpec_Name,
    coalesce(MedStaffFact.Person_Snils,'00000000000') ||'.'|| MedStaffFact.MedStaffFact_id::VARCHAR(20) AS Resource_Id,
    Person_Fio AS Resource_Name,
	 
			coalesce(RecMethodType.RecMethodType_FedCode,'REG') AS Code_id
		 
	FROM TimeTableGrafHist TimeTableHistory 
	INNER JOIN dbo.v_TimeTableGraf ttg ON TimeTableHistory.TimeTableGraf_id = ttg.TimeTableGraf_id
	INNER JOIN dbo.v_EvnDirection_all ed ON ttg.EvnDirection_id = ed.EvnDirection_id and not COALESCE(ed.EvnStatus_id, 0) = 15
	LEFT JOIN dbo.RecMethodType ON RecMethodType.RecMethodType_id = TimeTableHistory.RecMethodType_id
	LEFT JOIN LATERAL (SELECT TimeTableGrafHist_id 
	from TimeTableGrafHist TTHCancel 
	where TTHCancel.TimeTableGraf_id = TimeTableHistory.TimeTableGraf_id 
	AND TTHCancel.TimeTableGrafHist_insDT > TimeTableHistory.TimeTableGrafHist_insDT 
	AND TTHCancel.TimeTableGrafAction_id = 3 limit 1) AS ttgC	on true
	
	inner join lateral (select MedStaffFact_id,MedStaffFact.Post_id,MedStaffFact.lpu_id,Person_Snils,lpuunit.LpuUnit_id,LpuUnit.FRMOUnit_id,
						LpuUnit_FRMOid,Person_Fio,lpuunit.LpuUnit_Name,LpuUnit_FRMOUnitID,lpuunit.lpu_id lpuunit_lpu_id ,MedStaffFact.MedSpecOms_id
					from v_MedStaffFact MedStaffFact 
					INNER JOIN dbo.v_lpuunit lpuunit   ON lpuunit.Lpuunit_id = MedStaffFact.Lpuunit_id AND lpuunit.LpuUnitType_SysNick='polka'
					where TimeTableHistory.MedStaffFact_id = MedStaffFact.MedStaffFact_id
					limit 1) MedStaffFact  on true 
	
		left JOIN nsi.FRMOUnit ON FRMOUnit.FRMOUnit_id = MedStaffFact.FRMOUnit_id
		
		LEFT JOIN LATERAL (SELECT FRMOSection.FRMOUnit_OID, FRMOUnit_Name FROM dbo.v_LpuSection
		INNER JOIN nsi.FRMOSection ON FRMOSection.FRMOSection_id = v_LpuSection.FRMOSection_id
		INNER JOIN nsi.FRMOUnit ON FRMOUnit.FRMOUnit_OID = FRMOSection.FRMOUnit_OID
		WHERE FRMOSection.FRMOUnit_OID IS NOT NULL
		AND v_LpuSection.LpuUnit_id=MedStaffFact.LpuUnit_id limit 1) AS FRMOSection on true
	
	inner join v_MedSpecOms MedSpecOms on MedStaffFact.MedSpecOms_id = MedSpecOms.MedSpecOms_id
	inner join nsi.v_MedSpecLink MedSpecLink on MedSpecOms.MedSpecOms_fedid = MedSpecLink.MedSpecOms_id
	inner join nsi.v_MedSpec MedSpec on MedSpecLink.MedSpec_id = MedSpec.MedSpec_id
	inner join nsi.v_PostLink PostLink on MedStaffFact.Post_id = PostLink.Post_lid
	inner join nsi.v_Post Post on PostLink.Post_id = Post.Post_id
	INNER JOIN dbo.v_lpu lpu ON lpu.Lpu_id = MedStaffFact.Lpu_id

	left JOIN fed.v_PassportToken Token ON Token.Lpu_id = MedStaffFact.Lpu_id
	INNER JOIN dbo.v_Org Org ON Org.Org_id = Lpu.Org_id
	where (1 = 1)
	AND TimeTableHistory.TimeTableGraf_begTime::date > (getdate()- interval '30 day')::date
	AND ttgC.TimeTableGrafHist_id IS NULL
	and coalesce(LpuUnit_FRMOUnitID,FRMOUnit.FRMOUnit_OID,FRMOSection.FRMOUnit_OID) IS NOT NULL
	AND TimeTableHistory.TimeTableGrafAction_id IN (2)
	AND TimeTableHistory.TimeTableGraf_begTime::date = (getdate()- interval '1 day')::date
	and (length(Token.PassportToken_tid::varchar(75))::bigint - length(REPLACE(Token.PassportToken_tid::varchar(75), '.', '')))/length('.')=10
   	 
			AND TimeTableHistory.pmUser_updID != 999900
		 
	and TimeTableHistory.TimeTableType_id in (1,2,4,9,10,11,12,15,16,17)
	order by TimeTableHistory.TimeTableGrafHist_insDT
