select
	'conc-50148-rmis' AS Rmis_Id,
	TimeTableHistory.TimeTableGrafHist_id as Book_Id_Rmis,
	TimeTableHistory.TimeTableGraf_begTime as Visit_Time,
	'RECORDED' 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 || ', ' || lpuunit.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
	limit 1) as ttgC on true
inner join v_MedStaffFact MedStaffFact on TimeTableHistory.MedStaffFact_id = MedStaffFact.MedStaffFact_id
inner join dbo.v_lpuunit lpuunit on lpuunit.Lpuunit_id = MedStaffFact.Lpuunit_id
	and lpuunit.LpuUnitType_SysNick = 'polka'
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
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 = LpuUnit.Lpu_id
	and (length(Token.PassportToken_tid::varchar(75))::bigint - length(replace(Token.PassportToken_tid::varchar(75), '.', ''))) / length('.') = 10
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, Token.PassportToken_tid) is not null
	and (PassportToken_ShortName is not null
	or coalesce(FRMOUnit.FRMOUnit_Name, FRMOSection.FRMOUnit_Name) is not null)
	and TimeTableHistory.TimeTableGrafAction_id in (2)
	AND TimeTableHistory.TimeTableGrafHist_insDT between $1 and $2
	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
