
				
			with mv as (
				select
				dbo.tzGetDate() as dt
			),
			
				
			    reg as (
                	select dbo.getregion() as region_id
            	)
				
				SELECT 
		
				 ESEC.EvnSection_id as "EvnSection_id"
				,ESEC.EvnSection_pid as "EvnSection_pid"
				,EPS.Person_id as "Person_id"
				,EPS.PersonEvn_id as "PersonEvn_id"
				,EPS.Server_id as "Server_id"
				,EPS.PrehospWaifRefuseCause_id as "PrehospWaifRefuseCause_id"
				,RTRIM(coalesce(EPS.EvnPS_NumCard, '')) as "EvnPS_NumCard"
				,coalesce(LS.LpuSection_Name, '') as "LpuSection_Name"
				,coalesce(LSW.LpuSectionWard_Name, '') as "LpuSectionWard_Name"
				,coalesce(Dtmp.Diag_FullName, '') as "Diag_Name"
				,to_char(ESEC.EvnSection_setDate, 'DD.MM.YYYY') as "EvnSection_setDate"
				,to_char(ESEC.EvnSection_disDate, 'DD.MM.YYYY') as "EvnSection_disDate"
				,coalesce(LT.LeaveType_Name, '') as "LeaveType_Name"
				,PT.PayType_Name as "PayType_Name"
				,RTRIM(coalesce(MP.Person_Fio, '')) as "MedPersonal_Fio"
				,case when ESEC.EvnSection_disDate is not null
					then
						case
							when LUT.LpuUnitType_Code = 2 and date_part('day', ESEC.EvnSection_disDate - ESEC.EvnSection_setDate) + 1 > 1
							then date_part('day', ESEC.EvnSection_disDate - ESEC.EvnSection_setDate)
							else date_part('day', ESEC.EvnSection_disDate - ESEC.EvnSection_setDate) + 1
						end - coalesce(ESEC.EvnSection_Absence, 0)
					else null
				  end as "EvnSection_KoikoDni"
				 ,MES.Mes_Code as "Mes_Code"
				 ,
				case when pls.Polis_endDate is not null and pls.Polis_endDate <= (select dt from mv)
then 'orange'
else case when ps.PersonCloseCause_id = 2 and ps.Person_closeDT is not null
		then 'red'
		else case when ps.Server_pid = 0
				then 'true'
				else 'false'
		end
end
				end as "Person_IsBDZ",
			
				 CASE WHEN ESEC.EvnSection_Index = ESEC.EvnSection_Count-1 THEN 1 ELSE 0 END as "EvnSection_isLast"
				 ,coalesce(ksgkpg.Mes_Code, '')||' '||coalesce(ksgkpg.Mes_Name, '') as "EvnSection_KSG"
				 ,kpg.Mes_Code as "EvnSection_KPG"
				,case when ESEC.EvnSection_IsAdultEscort = 2 then 'Да' else 'Нет' end as "EvnSection_IsAdultEscort"
				,coalesce(ksgkpg.Mes_Code, '')||' '||coalesce(ksgkpg.Mes_Name, '') as "EvnSection_KSGKPG"
			
				,RTRIM(PS.Person_SurName) as "Person_Surname"
				,RTRIM(PS.Person_FirName) as "Person_Firname"
				,RTRIM(PS.Person_SecName) as "Person_Secname"
				,case 
					when (PS.Person_SurName is null and PS.Person_FirName is null and PS.Person_SecName is null) then 'Неизвестный'
					else 
						coalesce(upper(left(PS.Person_SurName, 1)) || lower(overlay(PS.Person_SurName placing '' from 1 for 1)),'') ||' '|| 
						coalesce(upper(left(PS.Person_FirName, 1)) || lower(overlay(PS.Person_FirName placing '' from 1 for 1)),'') ||' '||
						coalesce(upper(left(PS.Person_SecName, 1)) || lower(overlay(PS.Person_SecName placing '' from 1 for 1)),'') 
				end
				as "Person_Fio"
				,PS.Sex_id as "Sex_id"
				,to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday"
				,
            case 
                
                when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) <= 31
                    then '' || datediff('day', PS.Person_Birthday, dbo.tzGetDate()) ||
                    case
                        when 
                            datediff('day', PS.Person_Birthday, dbo.tzGetDate()) >= 2
                            and datediff('day', PS.Person_Birthday, dbo.tzGetDate()) <= 4
                            or
                            datediff('day', PS.Person_Birthday, dbo.tzGetDate()) >= 22
                            and datediff('day', PS.Person_Birthday, dbo.tzGetDate()) + 1 <= 24
                        then ' дня'
                        when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) in (1, 21, 31)
                        then ' день'
                        else ' дней'
                    end
                when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) + 1 > 31 and dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) < 12
                    then '' || dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate())::varchar || ' мес.'
                    
                when dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) >= 12 and dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) < 36
                    then '' || dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar || 
                        case
                            when dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) = 2 then ' года '
                            else ' год '
                        end ||
                        case
                            when (dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) - (dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) * 12)) < 1 then ''
                            else (dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) - (dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) * 12))::varchar || ' мес.'
                        end
                        
                when dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) >= 3  
                    then '' || dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar || 
                        case
                    when 
                        right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,2)::integer >= 11 
                        and right(dbo.Age2(PS.Person_Birthday,dbo.tzGetDate())::varchar,2)::integer <= 14 
                        or right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer >= 5 
                        and right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer <= 9 
                        or right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer = 0 
                    then ' лет' 
                    when 
                        right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer >= 2  
                        and right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer <= 4 
                    then ' года' 
                    when right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer = 1 
                    then ' год' 
                end
                    else '' || dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate())::varchar || ' мес.' 
            end 
            as "Person_age"
        
				,to_char(PS.Person_DeadDT, 'DD.MM.YYYY') as "Person_deadDT"
				,case when SES1.ServiceEvnStatus_SysNick in ('sendegis','loadegis') then 'true' else 'false' end as "fedservice_iemk"
			
				FROM  
				
		 v_PersonState PS  inner join v_EvnPS EPS on EPS.Person_id = PS.Person_id
							and EPS.Lpu_id = '13101024'
			inner join v_EvnSection as ESEC on ESEC.EvnSection_pid = EPS.EvnPS_id and ESEC.Lpu_id = '13101024'
				and COALESCE(ESEC.EvnSection_IsPriem, 1) = 1
			left join v_EvnCostPrint ecp on ecp.Evn_id = EPS.EvnPS_id
			left join v_Diag Dtmp on Dtmp.Diag_id = ESEC.Diag_id
			left join LpuSection LS on LS.LpuSection_id = ESEC.LpuSection_id
			inner join v_LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
			inner join LpuUnitType LUT on LUT.LpuUnitType_id = LU.LpuUnitType_id
			left join LpuSectionWard LSW on LSW.LpuSectionWard_id = ESEC.LpuSectionWard_id
			left join v_Polis pls on pls.Polis_id = ps.Polis_id
			left join PayType as PT on PT.PayType_id = ESEC.PayType_id
			left join lateral(
				select Person_Fio
				from v_MedPersonal
				where MedPersonal_id = ESEC.MedPersonal_id
				limit 1
			) as MP on true
			left join v_MesOld as MES on MES.Mes_id = ESEC.Mes_id
			left join LeaveType LT on LT.LeaveType_id = ESEC.LeaveType_id
			left join v_MesTariff spmt on ESEC.MesTariff_id = spmt.MesTariff_id
			left join v_MesOld as sksg on sksg.Mes_id = ESEC.Mes_sid
			left join v_MesOld as ksg on ksg.Mes_id = case
				when spmt.Mes_id in (ESEC.Mes_sid, ESEC.Mes_tid) then spmt.Mes_id
				else coalesce(ESEC.Mes_sid, ESEC.Mes_tid)
			end
			left join v_MesOld as ksgkpg on spmt.Mes_id = ksgkpg.Mes_id
			left join v_MesOld as kpg on kpg.Mes_id = ESEC.Mes_kid
		
			left join lateral(
				select ServiceEvnStatus_id
				from v_ServiceEvnHist
				where Evn_id = EPS.EvnPS_id
				  and ServiceEvnList_id = 1
				order by ServiceEvnHist_id desc
				limit 1
			) as SEH1 on true
			left join v_ServiceEvnStatus SES1 on SES1.ServiceEvnStatus_id = SEH1.ServiceEvnStatus_id
		
						 WHERE 
						(1 = 1) and ESEC.EvnSection_disDate >= '2022-01-21' and ESEC.EvnSection_disDate <= '2022-01-22' and ESEC.LpuSection_id = '99560027623'
						
				
						ORDER BY 
							
							EPS.EvnPS_id
							
				OFFSET 0 ROWS		
				FETCH NEXT 100 ROWS ONLY 
				
