
	  
	SELECT 
		'prod-conc-50311-rmis'/*из конфига*/ AS Rmis_Id,
		concat('m.', t.TimeTableMedServiceHist_id) AS Book_Id_Rmis,
		t.TimetableMedService_begTime AS Visit_Time,
		t.Status_id,
		'1' as paymentSourceId,
		LpuUnit.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(lpuunit.LpuUnit_Name, ', ', Org.Org_Nick)
			ELSE CONCAT(COALESCE(FRMOUnit.FRMOUnit_Name, FRMOSection.FRMOUnit_Name, PassportToken_ShortName), ', ', Org.Org_Nick) 
		END AS MO_Name,
		serviceInfo.specialityId specialtyId,
		serviceInfo.serviceId ServiceSpec_Code,
		serviceInfo.serviceInnerId ServiceSpec_Id,
		serviceInfo.serviceInnerName ServiceSpec_Name,
		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,
		CASE WHEN tc.TreatmentClass_Code LIKE '2%' THEN 'PROPHYLACTIC' ELSE 'ILLNESS' end AS appointmentReason_id,
		CASE WHEN evpl.VizitClass_id =2 THEN 'REPEATED' ELSE 'PRIMARY' end AS appointmentType_id,
		CASE 
			WHEN t.UserId = 999900 THEN 'EPGU'
			WHEN RecMethodType.RecMethodType_FedCode = 'OTHER' THEN 'OTHER_WEB' 
			ELSE COALESCE(RecMethodType.RecMethodType_FedCode,'REG') 
		END AS Code_id
	FROM evntable t
	 left join lateral (
			 
			 
			 
			 
		SELECT  
			CASE 
				WHEN g.FRMRNomenSpeciality_Code is not null then g.FRMRNomenSpeciality_Code
				WHEN MedServiceType.MedServiceType_Code = 3 AND t.Resource_id is not null then '35' 
				WHEN MedServiceType.MedServiceType_Code in (1,2,60,62) then '13' 
			END specialityId,
			COALESCE (u0.NomenMedUsluga_UslugaCode, u1.NomenMedUsluga_UslugaCode) serviceId,
			uc.UslugaComplex_id serviceInnerId,
			uc.UslugaComplex_Name serviceInnerName
		FROM dbo.v_UslugaComplex uc  
		LEFT JOIN nsi.v_NomenMedUsluga u0   ON u0.NomenMedUsluga_UslugaCode = 
			 
				case when SUBSTRING(uc.UslugaComplex_Code from 2 for 1) = '.' then  substring(uc.UslugaComplex_Code from 1 for 1) || SUBSTRING(uc.UslugaComplex_Code from 3) else uc.UslugaComplex_Code end
				 
		LEFT JOIN dbo.v_UslugaComplex uc1   ON uc1.UslugaComplex_id = COALESCE (uc.UslugaComplex_2011id , uc.UslugaComplex_2004id)
		LEFT JOIN nsi.v_NomenMedUsluga u1   ON u1.NomenMedUsluga_UslugaCode = 
			 
				case when SUBSTRING(uc1.UslugaComplex_Code from 2 for 1) = '.' then  substring(uc1.UslugaComplex_Code from 1 for 1) || SUBSTRING(uc1.UslugaComplex_Code from 3) else uc1.UslugaComplex_Code end
				 
		LEFT JOIN dbo.MedService MedService   on MedService.MedService_id = t.MedService_id
		LEFT JOIN dbo.MedServiceType MedServiceType   on MedService.MedServiceType_id = MedServiceType.MedServiceType_id 
		LEFT JOIN nsi.UslugaComplexMedSpec ums   ON ums.UslugaComplex_id = CASE WHEN u0.NomenMedUsluga_UslugaCode is not null then uc.UslugaComplex_id else uc1.UslugaComplex_id END
		LEFT JOIN nsi.MedSpecLink msl   on msl.MedSpec_id = ums.MedSpec_id 
		LEFT JOIN dbo.MedSpecOms e   on msl.MedSpecOms_id = e.MedSpecOms_fedid  
  		LEFT JOIN persis.FRMRNomenSpecialityLink f   on f.MedSpecClass_fedid = e.MedSpecClass_id 
		LEFT JOIN persis.FRMRNomenSpeciality g   on g.FRMRNomenSpeciality_id=f.FRMRNomenSpeciality_id	
		WHERE uc.UslugaComplex_id = t.UslugaComplex_did
		  AND COALESCE (u0.NomenMedUsluga_UslugaCode, u1.NomenMedUsluga_UslugaCode) is not null
		 LIMIT 1 
	 
	) serviceInfo  ON true 
	LEFT JOIN dbo.RecMethodType   ON RecMethodType.RecMethodType_id = t.RecMethodType_id
	LEFT JOIN dbo.MedService ms   ON ms.MedService_id = t.MedService_id
	LEFT JOIN dbo.v_EvnVizitPL evpl   ON evpl.EvnVizitPL_id = t.Evn_pid
	LEFT JOIN dbo.TreatmentClass tc   ON tc.TreatmentClass_id = evpl.TreatmentClass_id
	LEFT JOIN dbo.v_LpuUnit LpuUnit   ON LpuUnit.LpuUnit_id = ms.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 fed.v_PassportToken Token   ON Token.Lpu_id = LpuUnit.Lpu_id 
	LEFT JOIN dbo.v_Org Org   ON Org.Org_id = LpuUnit.Lpu_id
	 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=t.Person_id
	 LIMIT 1 
 
	) patient  ON true 
	WHERE COALESCE(LpuUnit_FRMOUnitID, FRMOUnit.FRMOUnit_OID, FRMOSection.FRMOUnit_OID) IS NOT NULL
       AND t.UserId != 999900  
	order by t.TimetableMedService_begTime, t.TimeTableMedServiceHist_id, t.TimeTableMedServiceHist_insDT
