SELECT 'CANCELED_BY_PATIENT' AS Status,
	 	

	'conc-50148-rmis' AS Rmis_id,
 	v_HomeVisit.HomeVisit_id AS HC_ID_Rmis,
 	HomeVisit_setDT AS VisitTime,
 	'30' AS Duration,
	CAST(lu.LpuUnit_id AS VARCHAR(20)) AS MO_Id,
	COALESCE(LpuUnit_FRMOUnitID, FRMOUnit.FRMOUnit_OID,FRMOSection.FRMOUnit_OID) AS MO_OID,
	CASE 
		WHEN LpuUnit_FRMOid IS NOT NULL THEN CONCAT(PassportToken_ShortName, ', ', lu.LpuUnit_Name)
		ELSE CONCAT(PassportToken_ShortName, ', ', COALESCE(FRMOUnit.FRMOUnit_Name,FRMOSection.FRMOUnit_Name))
	END AS MO_Name,	
	CONCAT(COALESCE(msf.Person_Snils, '00000000000'), '.', msf.MedStaffFact_id) AS Resource_id,	
	Person_Fio as Resource_Name,
	 'EPGU' AS Source_Code 
FROM dbo.v_HomeVisitStatusHist  
INNER JOIN (SELECT dbo.tzGetDate() curDate) cdt ON 1=1
INNER JOIN dbo.v_HomeVisit   ON v_HomeVisitStatusHist.HomeVisit_id = v_HomeVisit.HomeVisit_id
INNER JOIN dbo.v_LpuRegion lr   ON lr.LpuRegion_id = v_HomeVisit.LpuRegion_cid
INNER JOIN dbo.v_LpuSection ls   ON ls.LpuSection_id = lr.LpuSection_id
LEFT JOIN v_lpuunit lu   ON lu.LpuUnit_id = ls.LpuUnit_id 
INNER JOIN dbo.v_Lpu Lpu   ON Lpu.Lpu_id =lu.Lpu_id and COALESCE(Lpu.Lpu_IsTest,5)!=2
INNER JOIN fed.PassportToken Token   ON Token.Lpu_id = lu.Lpu_id
INNER JOIN dbo.v_Org Org   ON	Org.Org_id =Lpu.Org_id
INNER JOIN dbo.v_Address as v_address   ON v_address.Address_id = lu.Address_id
INNER JOIN dbo.v_KLArea	as KLAreaRegion   ON KLAreaRegion.KLArea_id = Lpu.Region_id
INNER JOIN dbo.v_KLStreet as KLStreet   ON  KLStreet.KLStreet_id = v_address.KLStreet_id
left JOIN dbo.HomeVisitSource   ON HomeVisitSource.HomeVisitSource_id = v_HomeVisit.HomeVisitSource_id
LEFT JOIN dbo.v_KLCity as KLCity   ON KLCity.KLCity_id =	v_address.KLCity_id
LEFT JOIN dbo.v_KLTown as KLTown   ON kltown.KLTown_id=v_address.KLTown_id
LEFT JOIN nsi.FRMOUnit   ON FRMOUnit.FRMOUnit_id = lu.FRMOUnit_id

 left join lateral  (
	SELECT   FRMOSection.FRMOUnit_OID, FRMOUnit.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=lu.LpuUnit_id
	 LIMIT 1 
) AS FRMOSection  ON true 

 left join lateral  (
	SELECT   hvsh.MedStaffFact_id 
	FROM dbo.v_HomeVisitStatusHist hvsh   
	WHERE hvsh.HomeVisit_id = v_HomeVisit.HomeVisit_id
	  AND hvsh.MedStaffFact_id IS NOT NULL 
	  ORDER BY hvsh.HomeVisitStatusHist_id desc
	 LIMIT 1 
) AS hvsh  ON true 

LEFT JOIN dbo.v_MedStaffFact AS msf   ON msf.MedStaffFact_id = COALESCE(v_HomeVisitStatusHist.MedStaffFact_id,hvsh.MedStaffFact_id)
	AND COALESCE(msf.WorkData_endDate, cdt.CurDate +  interval '1 day' ) > cdt.CurDate
INNER JOIN nsi.v_PostLink PostLink   on msf.Post_id =	PostLink.Post_lid
INNER JOIN nsi.v_Post Post   on PostLink.Post_id = Post.Post_id


WHERE 
	 COALESCE(v_HomeVisit.HomeVisitSource_id,99)=4 
AND HomeVisitStatusHist_updDT between $1 and $2
 
	AND v_HomeVisitStatusHist.HomeVisitStatus_id=2
