SELECT
		'conc-50148-rmis'/*из конфига*/ AS Rmis_Id,
		TimeTableHistory.TimeTableGrafHist_id AS Book_Id_Rmis,
		TimeTableHistory.TimeTableGraf_begTime AS Visit_Time,
		'PATIENT_NOT_ARRIVED' AS Status_id,
		'1' as paymentSourceId,
		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 CONCAT(PassportToken_ShortName, ', ', lpuunit.LpuUnit_Name)
			ELSE CONCAT(PassportToken_ShortName, ', ', COALESCE(FRMOUnit.FRMOUnit_Name, FRMOSection.FRMOUnit_Name)) 
		END AS MO_Name,	
		smpp.MedPersonalPost_Code postId,
    	Post.Post_Code AS postCode,
		Post.Post_Name AS postName,
		COALESCE(appoint.appointmentType_id, 'PRIMARY') as appointmentType_id,
		COALESCE(appoint.appointmentReason_id, 'ILLNESS') as appointmentReason_id,
    	MedStaffFact.Person_SurName AS lastName,
    	MedStaffFact.Person_FirName AS firstName,
    	MedStaffFact.Person_SecName AS middleName,
    	COALESCE(MedStaffFact.Person_Snils, '00000000000') snils,
    	spec.FRMRNomenSpeciality_Code specialtyId,
    	null ServiceSpec_Code, 
		patient.lastName patient_lastName,
		patient.firstName patient_firstName,
		patient.middleName patient_middleName,
		patient.birthDate patient_birthDate,
		patient.omsNumber patient_omsNumber,
		patient.snils patient_snils, 
		patient.sex patient_sex,
		patient.Age patient_Age,
		patient.isVillager patient_isVillager,
		 'EPGU' 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
	 
	INNER JOIN (SELECT cast(dbo.tzGetDate() - interval '5 day' as date) curDate) cdt ON 1 = 1
	 
	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 dbo.v_MedStaffFact MedStaffFact   on TimeTableHistory.MedStaffFact_id = MedStaffFact.MedStaffFact_id
	INNER JOIN dbo.v_lpuunit lpuunit   ON lpuunit.Lpuunit_id = MedStaffFact.Lpuunit_id 
	LEFT JOIN nsi.FRMOUnit   ON FRMOUnit.FRMOUnit_id = LpuUnit.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=lpuunit.LpuUnit_id
		 LIMIT 1 
	) AS FRMOSection  ON true 
	 left join lateral  (
		SELECT   epl.EvnPL_id,TreatmentClass_code,
		CASE WHEN TreatmentClass_code LIKE '2%' THEN 'PROPHYLACTIC' ELSE 'ILLNESS' end AS appointmentReason_id,
		CASE WHEN evpl.VizitClass_id =2 THEN 'REPEATED' 
			WHEN COALESCE(eplOpen.EvnVizitPL_id,1)=1 THEN 'REPEATED'
			ELSE 'PRIMARY' end AS appointmentType_id
		FROM dbo.v_EvnPL epl  
		INNER JOIN dbo.v_EvnVizitPL evpl   ON evpl.EvnVizitPL_rid = epl.EvnPL_id
		INNER JOIN dbo.TreatmentClass tc   ON tc.TreatmentClass_id = evpl.TreatmentClass_id
					 left join lateral  (
							SELECT   v_EvnVizitPL.EvnVizitPL_id 
							FROM dbo.v_EvnVizitPL   WHERE Person_id=epl.Person_id AND EvnVizitPL_setDate IS NULL
				            ORDER BY EvnVizitPL_updDT
				             LIMIT 1 
							) AS eplOpen  ON true 
		WHERE epl.Person_id = MedStaffFact.Person_id 
			AND epl.LpuSection_id=MedStaffFact.LpuSection_id
			AND CAST(evpl.EvnVizitPL_setDT AS date)=CAST(TimeTableHistory.TimeTableGraf_begTime AS date)
			and CAST(epl.EvnPL_setDT AS date)=CAST(TimeTableHistory.TimeTableGraf_begTime AS date)
		ORDER BY epl.EvnPL_setDT
		 LIMIT 1 
	) AS appoint  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 persis.Post spp   on spp.id = MedStaffFact.Post_id
	INNER JOIN persis.FRMPPost sfrp   on sfrp.id =  spp.frmpEntry_id
	INNER JOIN nsi.MedPersonalPost smpp   on smpp.MedPersonalPost_id =  sfrp.Frmr_id
	INNER JOIN dbo.v_lpu lpu   ON lpu.Lpu_id = MedStaffFact.Lpu_id
	 left join lateral  (
			SELECT   g.FRMRNomenSpeciality_Code
			FROM persis.FRMRNomenSpecialityLink f  
			INNER JOIN persis.FRMRNomenSpeciality g   on g.FRMRNomenSpeciality_id=f.FRMRNomenSpeciality_id 
			WHERE MedSpecOms.MedSpecClass_id=f.MedSpecClass_fedid
			 LIMIT 1 
	) spec  ON true 
	 left join lateral  (
		 
		 
	SELECT   
		ps.Person_SurName lastName,
		ps.Person_FirName firstName,
		ps.Person_SecName middleName,
		ps.Person_BirthDay birthDate,
		ps.Polis_Num omsNumber,
		ps.Person_Snils snils, 
		case when ps.Sex_id is null then null when ps.Sex_id = 1 then 'M' else 'F' end sex,
		dbo.Age(Person_BirthDay,GETDATE()) AS Age,
		CASE WHEN addr.KLAreaType_id=1 THEN 0 ELSE 1 END AS isVillager
	FROM dbo.v_PersonState ps  
	INNER JOIN dbo.v_Address addr   ON addr.Address_id=COALESCE(ps.UAddress_id, ps.PAddress_id)
	WHERE ps.Person_id=TimeTableHistory.Person_id
	 LIMIT 1 
 
	) patient  ON true 
	LEFT JOIN fed.v_PassportToken Token   ON Token.Lpu_id = LpuUnit.Lpu_id
	left JOIN dbo.v_Org Org   ON Org.Org_id = Lpu.Org_id
	WHERE (1 = 1)
	  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 cast(TimeTableHistory.TimeTableGraf_begTime as date) =  cdt.curDate 
	 AND (length(Token.PassportToken_tid) - length(REPLACE(Token.PassportToken_tid, '.', '')))/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
