
				
				
			WITH People (Person_id, Server_id, Person_Surname, Person_Firname, Person_Secname)
			AS
			(
				SELECT
					distinct pc.Person_id, ps.Server_id, ps.Person_Surname, ps.Person_Firname,  ps.Person_Secname
				FROM
					v_PersonCard_all pc							
					inner join	v_Person_all pall on
						pc.Person_id = pall.Person_id
					inner join Polis  on 
						pall.Polis_id = Polis.Polis_id							
					inner join v_PersonState ps  on ps.Person_id = pc.Person_id and ps.Server_pid = 0
					left join LpuRegionType rt  on rt.LpuRegionType_id = pc.LpuRegionType_id
				WHERE
					pc.Lpu_id = '13101874'							
					and pc.PersonCard_begDate < '2022-01-31 23:59:59'
					and coalesce(pc.PersonCard_endDate, '2030-01-01 00:00:00') > '2022-01-31 23:59:59'					
					and Polis.Polis_begDate < '2022-01-31 23:59:59'
					and coalesce(Polis.Polis_endDate, '2030-01-01 00:00:00') > '2022-01-31 23:59:59'
					
					and pc.LpuRegion_id is not null
					and rt.LpuRegionType_id in (1)
				
			),
			curDT as (select dbo.tzGetDate() as curDate)
			
				SELECT 
			*
			
				FROM  
				
			(			
				
			-- поликлинические случаи			
			SELECT
				EvnPL.EvnPL_id as "Evn_id",
				EvnPL.EvnClass_id as "EvnClass_id",
				People.Person_id as "Person_id",
				EvnPL.Server_id as "Server_id",
				People.Person_SurName as "Person_SurName",
				People.Person_FirName as "Person_FirName",
				People.Person_SecName as "Person_SecName",
				to_char(cast(EvnPL.EvnPL_setDT as timestamp),'dd.mm.yyyy') as "Evn_begDate",
				case when coalesce(EvnPL_IsFinish, 1) = 2 then to_char(cast(EvnPL.EvnPL_disDate as timestamp),'dd.mm.yyyy') end as "Evn_endDate",
				Lpu.Lpu_Nick as "Lpu_Nick",
				Lpu.Lpu_id as "Lpu_id",
				LpuSect.LpuSection_Name as "LpuSection_Name",
				vizits.kd as "KoikoDni",
				Diag.Diag_Name as "Diag_Name",
				vizits.PlanSum as "PlanSum",
				vizits.FactSum as "FactSum"
			FROM	
				v_EvnPL EvnPL
				inner join People on People.Person_id = EvnPL.Person_id
				inner join v_Lpu Lpu on Lpu.Lpu_id = EvnPL.Lpu_id
				inner join v_LpuSection LpuSect on LpuSect.LpuSection_id = EvnPL.LpuSection_id
				inner join v_Diag Diag on Diag.Diag_id = EvnPL.Diag_id
				left join lateral(
					select
						count(ev.EvnVizitPL_id) as kd,
						sum(coalesce(tarif.LpuSectionTariff_Tariff, 0)) as PlanSum,
						sum(coalesce(tarif.LpuSectionTariff_Tariff, 0)) as FactSum
					from
						v_EvnVizitPL ev
						left join lateral(
							select 
								LpuSectionTariff_Tariff
							from
								LpuSectionTariff
							where
								LpuSection_id = ev.LpuSection_id
								and LpuSectionTariff_setDate <= EvnPL.EvnPL_disDate
								and TariffClass_id = 1
							order by
								LpuSectionTariff_setDate desc
							limit 1
						) as tarif on true
					where
						ev.EvnVizitPL_pid = EvnPL.EvnPL_id
						and ev.EvnVizitPL_setDT between '2022-01-01 00:00:00' and '2022-01-31 23:59:59' 			
				) as vizits on true
			WHERE				 
				exists (
					select
						ev1.EvnVizitPL_id
					from
						v_EvnVizitPL ev1
						inner join v_LpuSection ls1 on ls1.LpuSection_id = ev1.LpuSection_id and coalesce(ls1.LpuSectionProfile_Code, '0') not in ('1200', '1210', '1220', '2500', '1004', '1700', '610', '630', '620', '1803', '1801', '1850', '1840', '1811', '1802', '1830', '1812', '1810', '1820', '1860', '1800', '1870')
					where
						ev1.EvnVizitPL_pid = EvnPL.EvnPL_id
						and ev1.EvnVizitPL_setDT between '2022-01-01 00:00:00' and '2022-01-31 23:59:59'			
				)
		
					union
				
			-- случаи лечения в стационаре	 
			-- расчетное прикрепленное население
			SELECT
				EvnPS.EvnPS_id as "Evn_id",
				EvnPS.EvnClass_id as "EvnClass_id",
				People.Person_id as "Person_id",
				EvnPS.Server_id as "Server_id",
				People.Person_SurName as "Person_SurName",
				People.Person_FirName as "Person_FirName",
				People.Person_SecName as "Person_SecName",
				to_char(cast(EvnPS.EvnPS_setDT as timestamp),'dd.mm.yyyy') as "Evn_begDate",
				case when EvnPS.EvnPS_disDate is not null then to_char(cast(EvnPS.EvnPS_disDate as timestamp),'dd.mm.yyyy') end as "Evn_endDate",
				Lpu.Lpu_Nick as "Lpu_Nick",
				Lpu.Lpu_id as "Lpu_id",
				LpuSect.LpuSection_Name as "LpuSection_Name",
				/*case when LpuUnitType.LpuUnitType_SysNick = 'stac' 
					then datediff('day', EvnPS.EvnPS_setDate, coalesce(EvnPS.EvnPS_disDate, (select curDate from curDT))) + abs(sign(datediff('day', EvnPS.EvnPS_setDate, coalesce(EvnPS.EvnPS_disDate, (select curDate from curDT)))) - 1) -- круглосуточные 
					else (datediff('day', EvnPS.EvnPS_setDate, coalesce(EvnPS.EvnPS_disDate, (select curDate from curDT))) + 1) -- дневные 
				end as KoikoDni,*/
				evnsections.KoikoDni as "KoikoDni",
				Diag.Diag_Name as "Diag_Name",
				evnsections.PlanSum as "PlanSum",
				evnsections.FactSum as "FactSum"
			FROM	
				 v_EvnPS EvnPS 
				inner join People on People.Person_id = EvnPS.Person_id
				inner join v_Lpu Lpu  on Lpu.Lpu_id = EvnPS.Lpu_id
				left join v_EvnSection EPSLastES on EPSLastES.EvnSection_pid = EvnPS.EvnPS_id and EPSLastES.EvnSection_Index = EPSLastES.EvnSection_Count-1 
				left join LpuSection LpuSect on LpuSect.LpuSection_id = EPSLastES.LpuSection_id
				left join v_Diag Diag on Diag.Diag_id = EPSLastES.Diag_id
				left join LpuUnit on LpuUnit.LpuUnit_id = LpuSect.LpuUnit_id 
				left join LpuUnitType on LpuUnitType.LpuUnitType_id = LpuUnit.LpuUnitType_id 
				left join lateral(
					select
						max(coalesce(ms.Mes_id, 0)) as Mes_id,
						sum(
							case when
								lut.LpuUnitType_SysNick = 'stac'
							then
								datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end ) + abs(sign(datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end)) - 1)
							else
								(datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end) + 1)
							end
						) as KoikoDni,
						sum(
							coalesce(tarif.LpuSectionTariff_Tariff, 0) * 
							-- если есть МЭС, то по нему берем
							CASE WHEN
								ms.Mes_id is not null
							THEN
								ms.Mes_KoikoDni				
							-- если МЭСа нет, то по фактическому (с) Андрей Борматов
							ELSE
								case when
									lut.LpuUnitType_SysNick = 'stac'
								then
									datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end ) + abs(sign(datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end)) - 1)
								else
									(datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end) + 1)
								end
							END
						) as PlanSum,
						sum(
							coalesce(tarif.LpuSectionTariff_Tariff, 0) *
							case when
								lut.LpuUnitType_SysNick = 'stac'
							then
								datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end ) + abs(sign(datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end)) - 1)
							else
								(datediff('day', case when es.EvnSection_setDT < '2022-01-01 00:00:00' then '2022-01-01 00:00:00' else es.EvnSection_setDT end, case when coalesce(es.EvnSection_disDT, (select curDate from curDT)) > '2022-01-31 23:59:59' then '2022-01-31 23:59:59' else coalesce(es.EvnSection_disDT, (select curDate from curDT)) end) + 1)
							end
						) as FactSum
					from
						v_EvnSection es
						left join v_LpuSection ls1 on ls1.LpuSection_id = es.LpuSection_id
						left join v_LpuSection ls2 on ls2.LpuSection_id = ls1.LpuSection_pid
						left join LpuUnit lu on lu.LpuUnit_id = ls1.LpuUnit_id 
						left join LpuUnitType lut on lut.LpuUnitType_id = lu.LpuUnitType_id
						left join MesOld ms on ms.Mes_id = es.Mes_id
						left join lateral (
							select 
								LpuSectionTariff_Tariff
							from
								LpuSectionTariff tar
							where
								tar.LpuSection_id = CASE WHEN ls1.LpuSection_pid is null then ls1.LpuSection_id else ls1.LpuSection_pid end
								--and LpuSectionTariff_setDate <= es.EvnSection_disDate
								and TariffClass_id = 7
							order by
								LpuSectionTariff_setDate desc
							limit 1
						) as tarif on true
					where
						es.EvnSection_pid = EvnPS.EvnPS_id
						and	es.EvnSection_setDT <= '2022-01-31 23:59:59'
						and coalesce(es.EvnSection_disDT, (select curDate from curDT)) >= '2022-01-01 00:00:00'
						and coalesce(ls1.LpuSectionProfile_Code, '0') not in ('1200', '1210', '1220', '2500', '1004', '1700', '610', '630', '620', '1803', '1801', '1850', '1840', '1811', '1802', '1830', '1812', '1810', '1820', '1860', '1800', '1870')
						and coalesce(ls2.LpuSectionProfile_Code, '0') not in ('1200', '1210', '1220', '2500', '1004', '1700', '610', '630', '620', '1803', '1801', '1850', '1840', '1811', '1802', '1830', '1812', '1810', '1820', '1860', '1800', '1870')
						--stac_filter
				) as evnsections on true
			WHERE
				 exists (
					select
						EvnSection_id
					from
						v_EvnSection es1 
						left join v_LpuSection ls1 on es1.LpuSection_id = ls1.LpuSection_id
						left join v_LpuSection ls2 on ls2.LpuSection_id = ls1.LpuSection_pid
					where
						es1.EvnSection_pid = EvnPS.EvnPS_id
						and	es1.EvnSection_setDT <= '2022-01-31 23:59:59'
						and coalesce(es1.EvnSection_disDT, (select curDate from curDT)) >= '2022-01-01 00:00:00'
						and coalesce(ls1.LpuSectionProfile_Code, '0') not in ('1200', '1210', '1220', '2500', '1004', '1700', '610', '630', '620', '1803', '1801', '1850', '1840', '1811', '1802', '1830', '1812', '1810', '1820', '1860', '1800', '1870')
						and coalesce(ls2.LpuSectionProfile_Code, '0') not in ('1200', '1210', '1220', '2500', '1004', '1700', '610', '630', '620', '1803', '1801', '1850', '1840', '1811', '1802', '1830', '1812', '1810', '1820', '1860', '1800', '1870')
				 )
				and evnsections.KoikoDni is not null
		
			) as subsel
			 WHERE 
			(1=1)
				
			
				
						ORDER BY 
							
				"Person_SurName",
				"Person_FirName",
				"Person_SecName",
				"Evn_endDate"
			
				OFFSET 0 ROWS		
				FETCH NEXT 100 ROWS ONLY 
				
