
-- addit with

			with vars as (select
			GETDATE()::timestamp as cur_dt,
			(CURRENT_DATE + INTERVAL '13 day')::timestamp as upper_dt,
			GETDATE()::date as cur_date),
		
		tmp as (
		select
		uc11.UslugaComplex_id as UslugaComplex_2011id
,uc.UslugaComplex_id as UslugaComplex_msid
,coalesce(uc.UslugaComplex_id, uc11.UslugaComplex_id) as UslugaComplex_id
,coalesce(uc.UslugaComplex_Code, uc11.UslugaComplex_Code) as UslugaComplex_Code
,coalesce(ucms.UslugaComplex_Name, uc.UslugaComplex_Name, uc11.UslugaComplex_Name) as UslugaComplex_Name
,uca.UslugaComplexAttributeType_SysNick as UslugaComplex_AttributeList
,uc.UslugaCategory_id as UslugaCategory_id
,0 as MedService_cnt
,mss.RecordQueue_id
,mss.MedService_id as MedService_id
,ucms.UslugaComplexMedService_id as UslugaComplexMedService_id
,mss.MedService_Name as MedService_Name
,mss.MedService_Nick as MedService_Nick
,mst.MedServiceType_id as MedServiceType_id
,mst.MedServiceType_SysNick as MedServiceType_SysNick
,mss.MedService_IsExternal as MedService_IsExternal
,mss.Lpu_id as Lpu_id
,l.Lpu_Nick as Lpu_Nick
,lu.LpuBuilding_id as LpuBuilding_id
,lu.LpuBuilding_Name as LpuBuilding_Name
,lu.LpuUnit_id as LpuUnit_id
,lu.LpuUnit_Name as LpuUnit_Name
,lua.Address_Address as LpuUnit_Address
,lu.LpuUnitType_id as LpuUnitType_id
,lu.LpuUnitType_SysNick as LpuUnitType_SysNick
,ls.LpuSection_id as LpuSection_id
,ls.LpuSection_Name as LpuSection_Name
,ls.LpuSectionProfile_id as LpuSectionProfile_id
,ls.LpuSectionProfile_Name as LpuSectionProfile_Name
,1 as isComposite
					
,0 as withResource
,eq.ElectronicQueueInfo_id as ElectronicQueueInfo_id
,eq.ElectronicService_id as ElectronicService_id
,composition.cnt as composition_cnt
,mss.MedService_id as lab_MedService_id
,pzm.MedService_id as pzm_MedService_id
,ucpzm.UslugaComplexMedService_id as pzm_UslugaComplexMedService_id
,pzm.Lpu_id as pzm_Lpu_id
,pzm.RecordQueue_id as pzm_RecordQueue_id
,pzm.MedServiceType_id as pzm_MedServiceType_id
,'pzm' as pzm_MedServiceType_SysNick
,pzm.MedService_Name as pzm_MedService_Name
,pzm.MedService_Nick as pzm_MedService_Nick
,ATT.AnalyzerTest_Name as AnalyzerTest_Name
,uc.UslugaComplex_Name as UCUslugaComplex_Name,
		
				case when mss.Lpu_id = '13101071' then 1 else 2 end as s1,
				case when pzm.Lpu_id = '13101071' then 1 else 2 end as s2,
				case when mss.LpuBuilding_id = '3388' then 1 else 2 end as s3,
				case when pzm.LpuBuilding_id = '3388' then 1 else 2 end as s4,
				case when mss.LpuUnit_id = '99560015132' then 1 else 2 end as s5,
				case when pzm.LpuUnit_id = '99560015132' then 1 else 2 end as s6,
				case when mss.LpuSection_id = '99560021824' then 1 else 2 end as s7,
				case when pzm.LpuSection_id = '99560021824' then 1 else 2 end as s8,
				case when tms.TimetableMedService_id is not null
						and TimetableMedService_begTime between (select cur_dt from vars) and (select upper_dt from vars)
					then 1 else 2 end as s9,
				uc.UslugaComplex_Code as s10
			
		from
		v_UslugaComplex uc
left join v_UslugaComplexMedService ucms on ucms.UslugaComplex_id = uc.UslugaComplex_id AND ucms.UslugaComplexMedService_pid IS NULL AND cast(ucms.UslugaComplexMedService_begDT as date) <= (select cur_date from vars) AND coalesce(cast(ucms.UslugaComplexMedService_endDT as date), (select cur_date from vars)) >= (select cur_date from vars)
left join v_MedService mss on mss.MedService_id = ucms.MedService_id AND (mss.MedServiceType_id in (6,71))
			AND cast(mss.MedService_begDT as date) <= (select cur_date from vars)
			AND (
				mss.MedService_endDT is null
				OR cast(mss.MedService_endDT as date) >= (select cur_date from vars)
			) AND mss.LpuSection_id is not null
left join v_MedServiceLink msl on msl.MedService_lid = mss.MedService_id and msl.MedServiceLinkType_id = 1
left join v_MedServiceLinkUslugaComplexLink mslucl
			on mslucl.MedServiceLink_id = MSL.MedServiceLink_id
			and mslucl.UslugaComplex_id = UC.UslugaComplex_id
			and mslucl.MedServiceLinkUslugaComplexLink_begDate <= (select cur_date from vars)
			and (mslucl.MedServiceLinkUslugaComplexLink_endDate is null or mslucl.MedServiceLinkUslugaComplexLink_endDate > (select cur_date from vars))
		
left join v_MedService pzm on pzm.MedServiceType_id = 7
			and msl.MedService_id = pzm.MedService_id
			and (pzm.MedService_endDT is null OR cast(pzm.MedService_endDT as date) > (select cur_date from vars))
			and (pzm.Lpu_id = '13101071' or (pzm.Lpu_id != '13101071' and coalesce(pzm.MedService_IsThisLPU, 1) != 2))
left join v_UslugaComplexMedService ucpzm on ucpzm.MedService_id = pzm.MedService_id and ucpzm.UslugaComplex_id = uc.UslugaComplex_id


left join v_MedServiceType mst on mss.MedServiceType_id = mst.MedServiceType_id
left join v_Lpu l on mss.Lpu_id = l.Lpu_id
left join v_LpuUnit lu on lu.LpuUnit_id = mss.LpuUnit_id
left join v_LpuSection ls on mss.LpuSection_id = ls.LpuSection_id
left join v_Address lua on lua.Address_id = coalesce(lu.Address_id,mss.Address_id)
left join lateral ( Select * from v_UslugaComplex uc11 where uc.UslugaComplex_2011id = uc11.UslugaComplex_id limit 1 ) uc11 on true
left join lateral (
			select
				eqi.ElectronicQueueInfo_id,
				mseq.ElectronicService_id
			from
				v_ElectronicQueueInfo eqi
				inner join v_MedServiceElectronicQueue mseq on mseq.UslugaComplexMedService_id = ucms.UslugaComplexMedService_id
				inner join v_ElectronicService es on es.ElectronicService_id = mseq.ElectronicService_id
			where
				 eqi.MedService_id = mss.MedService_id
				 and eqi.ElectronicQueueInfo_IsOff = 1
			limit 1
		) eq on true
left join lateral (
			select COUNT(ucoa.UslugaComplex_id) as cnt
			from v_UslugaComplexMedService ucmsoa
			inner join v_UslugaComplex ucoa on ucmsoa.UslugaComplex_id = ucoa.UslugaComplex_id
			where ucmsoa.UslugaComplexMedService_pid = ucms.UslugaComplexMedService_id
		) composition on true
left join lateral (
			select string_agg(t2.UslugaComplexAttributeType_SysNick, ',') as UslugaComplexAttributeType_SysNick
			from v_UslugaComplexAttribute t1
			inner join v_UslugaComplexAttributeType t2 on t2.UslugaComplexAttributeType_id = t1.UslugaComplexAttributeType_id
			where t1.UslugaComplex_id = uc.UslugaComplex_id	
		) uca on true

				inner join lateral (
					select
						Analyzer.Analyzer_id,
						AnalyzerTest_Name
					from lis.v_AnalyzerTest AnalyzerTest
							inner join lis.v_Analyzer Analyzer on Analyzer.Analyzer_id = AnalyzerTest.Analyzer_id
							inner join lateral (
								select
									atc.AnalyzerTest_id
								from lis.v_AnalyzerTest atc
									inner join v_UslugaComplexMedService ucmsch on ucmsch.UslugaComplexMedService_id = atc.UslugaComplexMedService_id
									inner join v_UslugaComplex ucch on ucmsch.UslugaComplex_id = ucch.UslugaComplex_id
										and (ucch.UslugaComplex_endDT > dbo.tzGetDate() or ucch.UslugaComplex_endDT is null)
								where atc.AnalyzerTest_pid = AnalyzerTest.AnalyzerTest_id
									and coalesce(atc.AnalyzerTest_IsNotActive, 1) = 1
									and (atc.AnalyzerTest_endDT > dbo.tzGetDate() or atc.AnalyzerTest_endDT is null)
								limit 1
							) children on true --проверяем, что есть хотя бы один действующий тест
					where AnalyzerTest.UslugaComplexMedService_id = ucms.UslugaComplexMedService_id
						and coalesce(AnalyzerTest.AnalyzerTest_IsNotActive, 1) = 1
						and coalesce(Analyzer.Analyzer_IsNotActive, 1) = 1
						and (Analyzer.Analyzer_endDT > dbo.tzgetdate() or Analyzer.Analyzer_endDT is null)
						and (AnalyzerTest.AnalyzerTest_endDT > dbo.tzgetdate() or AnalyzerTest.AnalyzerTest_endDT is null)
					limit 1
				) ATT on true				
				
				left join lateral (select
					TimetableMedService_id,TimetableMedService_begTime
				from
					v_TimetableMedService_lite
				where
					MedService_id = mss.MedService_id
					and Person_id is null
				limit 1) tms on true
			
		where
		uc.UslugaComplex_id is not null
 AND uc.UslugaComplex_begDT <= (select cur_date from vars)
 AND coalesce(uc.UslugaComplex_endDT, (select cur_date from vars)) >= (select cur_date from vars)
 AND not exists (
	select
		t1.UslugaComplexAttribute_id
	from v_UslugaComplexAttribute t1
		inner join v_UslugaComplexAttributeType t2 on t2.UslugaComplexAttributeType_id = t1.UslugaComplexAttributeType_id
	where t1.UslugaComplex_id = coalesce(uc11.UslugaComplex_id, uc.UslugaComplex_id)
		and t2.UslugaComplexAttributeType_SysNick in ('noprescr')
)
 AND not exists( select * from v_medservice mslo where mslo.MedService_id = coalesce(pzm.MedService_id, mss.MedService_id) and mslo.MedService_IsThisLPU = 2 and mslo.lpu_id != '13101071')
 AND exists (
			select
				t1.UslugaComplexAttribute_id
			from v_UslugaComplexAttribute t1
				inner join v_UslugaComplexAttributeType t2 on t2.UslugaComplexAttributeType_id = t1.UslugaComplexAttributeType_id
			where t1.UslugaComplex_id = coalesce(uc11.UslugaComplex_id, uc.UslugaComplex_id)
				and t2.UslugaComplexAttributeType_SysNick in ('lab')
			limit 1
		)
 AND (coalesce(pzm.MedServiceType_id,0) <> 7  or pzm.MedServiceType_id = 7 and MedServiceLinkUslugaComplexLink_id is not null)
 AND not ( MSL.MedServiceLink_id is not null and pzm.MedService_id is null )
		and (coalesce(mss.MedService_IsThisLPU, 1) != 2)
		),
			DD as (
				Select
					uc.UslugaComplex_2011id
					,uccount.MedService_cnt
					,ucdouble.*
					
					,cast(ucdouble.UslugaComplex_id as varchar) || coalesce('_' || cast(ucdouble.MedService_id as varchar), '') || coalesce('_' || cast(ucdouble.pzm_MedService_id as varchar), '') as Unique_id
				from
					tmp as uc
					left join lateral (
						Select count(*) as MedService_cnt
						from tmp as uccount
						where uc.UslugaComplex_msid = uccount.UslugaComplex_msid
					) uccount on true
					inner join lateral (
						Select
							ucdouble.UslugaComplex_msid
							,ucdouble.UslugaComplex_id
							,ucdouble.UslugaComplex_Code
							,ucdouble.UslugaComplex_Name
							,ucdouble.UslugaComplex_AttributeList
							,ucdouble.UslugaCategory_id
							,ucdouble.MedService_id
							,ucdouble.UslugaComplexMedService_id
							,ucdouble.MedService_Name
							,ucdouble.MedService_Nick
							,ucdouble.MedServiceType_id
							,ucdouble.MedServiceType_SysNick
							,ucdouble.RecordQueue_id
							,ucdouble.MedService_IsExternal
							,ucdouble.Lpu_id
							,ucdouble.Lpu_Nick
							,ucdouble.LpuBuilding_id
							,ucdouble.LpuBuilding_Name
							,ucdouble.LpuUnit_id
							,ucdouble.LpuUnit_Name
							,ucdouble.LpuUnit_Address
							,ucdouble.LpuUnitType_id
							,ucdouble.LpuUnitType_SysNick
							,ucdouble.LpuSection_id
							,ucdouble.LpuSection_Name
							,ucdouble.LpuSectionProfile_id
							,ucdouble.LpuSectionProfile_Name
							,ucdouble.isComposite
							
					,ucdouble.lab_MedService_id
					,ucdouble.pzm_MedService_id
					,ucdouble.pzm_UslugaComplexMedService_id
					,ucdouble.pzm_Lpu_id
					,ucdouble.pzm_MedServiceType_id
					,ucdouble.pzm_MedServiceType_SysNick
					,ucdouble.pzm_MedService_Name
					,ucdouble.pzm_MedService_Nick
					,ucdouble.pzm_RecordQueue_id
					,ucdouble.AnalyzerTest_Name
					,ucdouble.UCUslugaComplex_Name
				
							,ucdouble.ElectronicQueueInfo_id
							,ucdouble.ElectronicService_id
							,ucdouble.withResource
							,ucdouble.composition_cnt
						from tmp as ucdouble
						where
							uc.UslugaComplex_msid = ucdouble.UslugaComplex_msid
							
						order by
								s1, s2, s3, s4, s5, s6, s7, s8, s9, s10
						limit 1
					 ) ucdouble on true
				group by
					uc.UslugaComplex_2011id
					
					,ucdouble.UslugaComplex_msid
					,ucdouble.UslugaComplex_id
					,ucdouble.UslugaComplex_Code
					,ucdouble.UslugaComplex_Name
					,ucdouble.UslugaComplex_AttributeList
					,ucdouble.UslugaCategory_id
					,uccount.MedService_cnt
					,ucdouble.MedService_id
					,ucdouble.UslugaComplexMedService_id
					,ucdouble.MedService_Name
					,ucdouble.MedService_Nick
					,ucdouble.MedServiceType_id
					,ucdouble.MedServiceType_SysNick
					,ucdouble.RecordQueue_id
					,ucdouble.Lpu_id
					,ucdouble.Lpu_Nick
					,ucdouble.LpuBuilding_id
					,ucdouble.LpuBuilding_Name
					,ucdouble.LpuUnit_id
					,ucdouble.LpuUnit_Name
					,ucdouble.LpuUnit_Address
					,ucdouble.LpuUnitType_id
					,ucdouble.LpuUnitType_SysNick
					,ucdouble.LpuSection_id
					,ucdouble.LpuSection_Name
					,ucdouble.LpuSectionProfile_id
					,ucdouble.LpuSectionProfile_Name
					,ucdouble.isComposite
					
					,ucdouble.lab_MedService_id
					,ucdouble.pzm_MedService_id
					,ucdouble.pzm_UslugaComplexMedService_id
					,ucdouble.pzm_Lpu_id
					,ucdouble.pzm_MedServiceType_id
					,ucdouble.pzm_MedServiceType_SysNick
					,ucdouble.pzm_MedService_Name
					,ucdouble.pzm_MedService_Nick
					,ucdouble.pzm_RecordQueue_id
					,ucdouble.AnalyzerTest_Name
					,ucdouble.UCUslugaComplex_Name
				
					,ucdouble.ElectronicQueueInfo_id
					,ucdouble.ElectronicService_id
					,ucdouble.withResource
					,ucdouble.composition_cnt
					,ucdouble.MedService_IsExternal
			)
-- end addit with
			
select
	 count(  * ) AS cnt 
from
	-- from
	DD
			left join lateral
			(
				select
					TimetableMedService_id,
					MedService_id,
					TimetableMedService_begTime,
					UslugaComplexMedService_id,
					TimetableResource_id,
					TimetableResource_begTime,
					Resource_id,
					Resource_Name,
					is_pzm
				from (
					 
					(SELECT --lab: ms
						ttms.TimetableMedService_id,
						ttms.MedService_id,
						ttms.TimetableMedService_begTime,
						ttms.UslugaComplexMedService_id,
						null as TimetableResource_id,
						null as TimetableResource_begTime,
						null as Resource_id,
						null as Resource_Name,
						0 as is_pzm
					FROM
						v_TimetableMedService_lite ttms
					WHERE
						ttms.Person_id is null
 AND ttms.TimetableMedService_begTime >= (select cur_dt from vars)
 AND ttms.TimetableMedService_begTime < (select upper_dt from vars)
						and ttms.MedService_id = dd.MedService_id::bigint
						and ttms.UslugaComplexMedService_id is null
						
						and DD.pzm_MedService_id is null --lab: лаба должна быть без ПЗ
					ORDER BY 
			case
				/* При записи в свое МО: Все типы бирок кроме «резервных» */
				when DD.Lpu_id = '13101071' AND ttms.TimeTableType_id not in (2) then 0
				/* При записи в чужое МО: Обычная, По направлению */
				when DD.Lpu_id <> '13101071' AND ttms.TimeTableType_id in (1,5) then 0
				else 1
			end
		
							 
			, ttms.TimetableMedService_begTime
		
					limit 1)
					union
					
						(SELECT --lab: pz
						ttms.TimetableMedService_id,
						ttms.MedService_id,
						ttms.TimetableMedService_begTime,
						ttms.UslugaComplexMedService_id,
						null as TimetableResource_id,
						null as TimetableResource_begTime,
						null as Resource_id,
						null as Resource_Name,
						1 as is_pzm
						FROM
						v_TimetableMedService_lite ttms
						WHERE
						ttms.Person_id is null
 AND ttms.TimetableMedService_begTime >= (select cur_dt from vars)
 AND ttms.TimetableMedService_begTime < (select upper_dt from vars)
						and ttms.MedService_id = dd.pzm_MedService_id
						and ttms.UslugaComplexMedService_id is null
						
						ORDER BY 
			case
				/* При записи в свое МО: Все типы бирок кроме «резервных» */
				when DD.Lpu_id = '13101071' AND ttms.TimeTableType_id not in (2) then 0
				/* При записи в чужое МО: Обычная, По направлению */
				when DD.Lpu_id <> '13101071' AND ttms.TimeTableType_id in (1,5) then 0
				else 1
			end
		
							
			, ttms.TimetableMedService_begTime
		
						limit 1)
						union
						
						(SELECT --lab: usl_pz
							ttms.TimetableMedService_id,
							ttms.MedService_id,
							ttms.TimetableMedService_begTime,
							ttms.UslugaComplexMedService_id,
							null as TimetableResource_id,
							null as TimetableResource_begTime,
							null as Resource_id,
							null as Resource_Name,
							1 as is_pzm
						FROM
							v_TimetableMedService_lite ttms
						WHERE
							ttms.Person_id is null
 AND ttms.TimetableMedService_begTime >= (select cur_dt from vars)
 AND ttms.TimetableMedService_begTime < (select upper_dt from vars)
							and dd.pzm_UslugaComplexMedService_id is not null
							and ttms.UslugaComplexMedService_id::bigint = dd.pzm_UslugaComplexMedService_id::bigint
							
						ORDER BY 
			case
				/* При записи в свое МО: Все типы бирок кроме «резервных» */
				when DD.Lpu_id = '13101071' AND ttms.TimeTableType_id not in (2) then 0
				/* При записи в чужое МО: Обычная, По направлению */
				when DD.Lpu_id <> '13101071' AND ttms.TimeTableType_id in (1,5) then 0
				else 1
			end
		
								 
			, ttms.TimetableMedService_begTime
		
						limit 1)
						union
						
					(SELECT --lab: usl_ms
						ttms.TimetableMedService_id,
						ttms.MedService_id,
						ttms.TimetableMedService_begTime,
						ttms.UslugaComplexMedService_id,
						null as TimetableResource_id,
						null as TimetableResource_begTime,
						null as Resource_id,
						null as Resource_Name,
						0 as is_pzm
					FROM
						v_TimetableMedService_lite ttms
					WHERE
						ttms.Person_id is null
 AND ttms.TimetableMedService_begTime >= (select cur_dt from vars)
 AND ttms.TimetableMedService_begTime < (select upper_dt from vars)
						and ttms.UslugaComplexMedService_id = dd.UslugaComplexMedService_id
						and dd.UslugaComplexMedService_id is not null
						
						and DD.pzm_MedService_id is null --lab: лаба должна быть без ПЗ
					ORDER BY 
			case
				/* При записи в свое МО: Все типы бирок кроме «резервных» */
				when DD.Lpu_id = '13101071' AND ttms.TimeTableType_id not in (2) then 0
				/* При записи в чужое МО: Обычная, По направлению */
				when DD.Lpu_id <> '13101071' AND ttms.TimeTableType_id in (1,5) then 0
				else 1
			end
		
							 
			, ttms.TimetableMedService_begTime
		
					limit 1)
				 
					  
				) ttms2
	
				order by
					ttms2.is_pzm desc,	-- в первую очередь выбирать из пункта забора биоматериала
					case when ttms2.UslugaComplexMedService_id is not null then 0 else 1 end
				limit 1
			) ttmsx on true
			
	-- end from
WHERE
	-- where
	(1=1)
	-- end where


