
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
                EPS.EvnPS_id as "EvnPS_id",
                
                EPS.Person_id as "Person_id",
                EPS.PersonEvn_id as "PersonEvn_id",
                EPS.Server_id as "Server_id",
                coalesce(EPS.EvnPS_IsTransit, 1) as "EvnPS_IsTransit",
                RTRIM(EPS.EvnPS_NumCard) as "EvnPS_NumCard",
                to_char(EPS.EvnPS_setDate, 'DD.MM.YYYY') as "EvnPS_setDate",
                to_char(EPS.EvnPS_disDate, 'DD.MM.YYYY') as "EvnPS_disDate",
                coalesce(LStmp.LpuSection_Name, '') as "LpuSection_Name",
                coalesce(LSP.LpuSectionProfile_Name, '') as "LpuSectionProfile_Name",
                coalesce(Dtmp.Diag_FullName, DP.Diag_FullName) as "Diag_Name",
                
                case when LpuUnitType.LpuUnitType_SysNick = 'stac'
then date_part('day', EPS.EvnPS_disDate - EPS.EvnPS_setDate) + abs(sign(date_part('day', EPS.EvnPS_disDate - EPS.EvnPS_setDate)) - 1)
else (date_part('day', EPS.EvnPS_disDate - EPS.EvnPS_setDate) + 1)
                end - coalesce(EPSLastES.EvnSection_Absence, 0) as "EvnPS_KoikoDni",
            
                
                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",
            
                dbfpayt.PayType_Name as "PayType_Name", --Вид оплаты
                CASE
WHEN LT.LeaveType_Name is not null THEN LT.LeaveType_Name
WHEN EPS.PrehospWaifRefuseCause_id > 0 THEN pwrc.PrehospWaifRefuseCause_Name
ELSE '' END as "LeaveType_Name",
                LT.LeaveType_Code as "LeaveType_Code",
                CASE WHEN DeathSvid.DeathSvid_id is null
then 'false'
else 'true'
                end as "DeadSvid",
                EPS.PrehospWaifRefuseCause_id as "PrehospWaifRefuseCause_id",
                 coalesce(ksgkpg.Mes_Code, '') || ' ' ||  coalesce(ksgkpg.Mes_Name, '') as "EvnSection_KSG",
                coalesce(ksgkpg.Mes_Code, '') || ' ' ||  coalesce(ksgkpg.Mes_Name, '') as "EvnSection_KSGKPG",
                 kpg.Mes_Code as "EvnSection_KPG",
                to_char(ecp.EvnCostPrint_setDT, 'DD.MM.YYYY') as "EvnCostPrint_setDT",
                case when ecp.EvnCostPrint_IsNoPrint = 2 then 'Отказ от справки' when ecp.EvnCostPrint_IsNoPrint = 1 then 'Справка выдана' else '' end as "EvnCostPrint_IsNoPrintText"
                
            
,RTRIM(PS.Person_SurName) as "Person_Surname"
,RTRIM(PS.Person_FirName) as "Person_Firname"
,RTRIM(PS.Person_SecName) as "Person_Secname"
,to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday"
,COALESCE(to_char(DeathSvid.DeathSvid_DeathDate, 'DD.MM.YYYY'),to_char(EvnDie.EvnDie_setDate, 'DD.MM.YYYY'),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
                
         dbo.v_PersonState PS 


    inner join ( select distinct person_id
           from v_Person_all PStmp
           where (1 = 1) and lower(PStmp.Person_SurName) like lower('неизв') || '%'
    ) PStmp on PStmp.Person_id = PS.Person_id
    

 inner join v_EvnPS EPS on EPS.Person_id = PS.Person_id
                            and EPS.Lpu_id = '500801000000013'
            left join v_EvnCostPrint ecp on ecp.Evn_id = EPS.EvnPS_id
            left join v_EvnSection EPSLastES on EPSLastES.EvnSection_id = EPS.EvnSection_lastid
                and EPSLastES.EvnSection_pid = EPS.EvnPS_id
            left join LpuSection LStmp on LStmp.LpuSection_id = EPSLastES.LpuSection_id
            left join LpuSectionProfile LSP on LSP.LpuSectionProfile_id = coalesce(EPSLastES.LpuSectionProfile_id, LStmp.LpuSectionProfile_id)
            left join v_Diag Dtmp on Dtmp.Diag_id = EPSLastES.Diag_id
            left join v_Diag eps_diag on EPS.Diag_id = eps_diag.Diag_id
            left join v_Diag eps_diag_depart on EPS.Diag_did = eps_diag_depart.Diag_id
            left join LeaveType LT on LT.LeaveType_id = coalesce(EPSLastES.LeaveType_id, EPSLastES.LeaveType_prmid)
            left join v_Diag DP on DP.Diag_id = EPS.Diag_pid
            left join v_PrehospWaifRefuseCause pwrc on pwrc.PrehospWaifRefuseCause_id = EPS.PrehospWaifRefuseCause_id
            left join LpuUnit on LpuUnit.LpuUnit_id = LStmp.LpuUnit_id
            left join LpuUnitType on LpuUnitType.LpuUnitType_id = LpuUnit.LpuUnitType_id
            left join PayType dbfpayt on dbfpayt.PayType_id = EPS.PayType_id
            left join v_Polis pls on pls.Polis_id = ps.Polis_id
            left join lateral(
                select EvnDie_setDate
                from v_EvnDie
                where Person_id = PS.Person_id
                order by EvnDie_setDt
                limit 1
            ) as EvnDie on true
            left join lateral(
                select
                    DeathSvid_id,
                    DeathSvid_DeathDate
                from dbo.v_DeathSvid
                where Person_id = PS.Person_id
                    and (DeathSvid_IsBad is null or DeathSvid_IsBad = 1)
                    and DeathSvid_IsActual = 2
                limit 1
            ) as DeathSvid on true
            left join v_MesTariff spmt on EPSLastES.MesTariff_id = spmt.MesTariff_id
            left join v_MesOld as sksg on sksg.Mes_id = EPSLastES.Mes_sid
            
            left join v_MesOld as ksgkpg on spmt.Mes_id = ksgkpg.Mes_id
            left join v_MesOld as kpg on kpg.Mes_id = EPSLastES.Mes_kid
            
        
            left join lateral(
                select
                    PersonEvn_id,
                    Server_id
                from v_PersonState
                where Person_id = PS.Person_id
                limit 1
            ) as CPS on true
        
            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 PS.PersonEvn_id = coalesce(EPSLastES.PersonEvn_id, CPS.PersonEvn_id)
                        and PS.Server_id = coalesce(EPSLastES.Server_id, CPS.Server_id)
                    
                        
                
                        ORDER BY
                            
                            EPS.EvnPS_id
                            
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
