
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
         DISTINCT
                EPL.EvnPL_id as "EvnPL_id",
                EPL.Person_id as "Person_id",
                EPL.PersonEvn_id as "PersonEvn_id",
                EPL.Server_id as "Server_id",
                CASE WHEN pls.PolisType_id = 4 THEN PS.Person_edNum ELSE pls.Polis_Num END as "Polis_Num",
                coalesce(EPL.EvnPL_IsTransit, 1) as "EvnPL_IsTransit",
                RTRIM(EPL.EvnPL_NumCard) as "EvnPL_NumCard",
                (
                    select
                        PersonAmbulatCard_Num
                    from v_PersonAmbulatCard PAC
                        left join v_PersonAmbulatCardLocat PACL on PAC.PersonAmbulatCard_id = PACL.PersonAmbulatCard_id
                        left join v_AmbulatCardLpuBuilding ACLB on ACLB.PersonAmbulatCard_id = PAC.PersonAmbulatCard_id
                    where
                        PAC.Person_id = EPL.Person_id
                        and PAC.Lpu_id = '2052468'
                        and EPL.EvnPL_setDT between
                            coalesce(ACLB.AmbulatCardLpuBuilding_begDate, EPL.EvnPL_setDT)
                            and coalesce(ACLB.AmbulatCardLpuBuilding_endDate, EPL.EvnPL_setDT)
                        order by
                            PACL.PersonAmbulatCardLocat_begDate desc,
                            PAC.PersonAmbulatCard_id desc
                    limit 1
                ) as "PersonAmbulatCard_Num",
                EPL.EvnPL_VizitCount as "EvnPL_VizitCount",
                CASE WHEN EPL.EvnPL_IsFinish = 2 THEN 'Да' ELSE 'Нет' END as "EvnPL_IsFinish",
                RTRIM(EVPLD.Diag_Code) || '. ' || RTRIM(EVPLD.Diag_Name) as "Diag_Name",
                RTRIM(EVPLDL.Diag_Code) || '. ' || RTRIM(EVPLDL.Diag_Name) as "DiagL_Name",
                RTRIM(MP.Person_Fio) as "MedPersonal_Fio",
                to_char(EPL.EvnPL_setDT, 'DD.MM.YYYY') as "EvnPL_setDate",
                to_char(EPL.EvnPL_disDT, 'DD.MM.YYYY') as "EvnPL_disDate",
                RTRIM(HK.HealthKind_Name) as "HealthKind_Name",
                
                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",
            
                coalesce(VT.VizitType_Name,'') as "VizitType_Name",
                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"
            ,case when SES1.ServiceEvnStatus_SysNick in ('sendegis','loadegis') then 'true' else 'false' end as "fedservice_iemk"
,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"
,to_char(PS.Person_deadDT, 'DD.MM.YYYY') as "Person_deadDT"
                
            
                FROM
                
        
            v_EvnPL EPL
            
                    inner join v_EvnVizitPL EVizitPL on EVizitPL.EvnVizitPL_pid = EPL.EvnPL_id and EVizitPL.Lpu_id = '2052468'
                    INNER JOIN v_PersonState PS ON PS.Person_id = EPL.Person_id
                
                    
                    inner join lateral (
                        select
                            EVPL.EvnVizitPL_id,
                            EVPL.EvnVizitPL_pid
                        from
                            v_EvnVizitPL EVPL
                            inner join v_Diag D on D.Diag_id = EVPL.Diag_id 
                inner join v_LpuSection LS on LS.LpuSection_id = EVPL.LpuSection_id
                inner join LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
                
                        where
                            EVPL.Lpu_id = '2052468' and D.Diag_Code >= 'B17.1' and D.Diag_Code <= 'B19.9' and EVPL.LpuSection_id = '560101000005356' and EVPL.MedStaffFact_id = '560101000205143' and EVPL.MedPersonal_id = '560101000038474'
                             and EvnVizitPL_pid = EPL.EvnPL_id limit 1 
                    ) as EVPL2 on true
                
                
            inner join v_LpuSection LS on LS.LpuSection_id = EPL.LpuSection_id
            inner join LpuUnit LU on LU.LpuUnit_id = LS.LpuUnit_id
            
                    left join v_Polis pls on pls.Polis_id = ps.Polis_id
                
                    left join lateral(
                        select
                            EVPL.EvnVizitPL_id,
                            EVPL.HealthKind_id,
                            EVPL.VizitType_id,
                            EVPL.MedPersonal_id,
                            EVPL.Lpu_id,
                            EVPL.Diag_id,
                            EVPL.LpuSection_id,
                            EVPL.DeseaseType_id,
                            EVPL.MedPersonal_sid,
                            EVPL.EvnVizitPL_isPaid,
                            EVPL.ServiceType_id,
                            EVPL.TreatmentClass_id,
                            EVPL.EvnVizitPL_setDT,
                            EVPL.UslugaComplex_id,
                            EVPL.EvnVizitPL_IsInReg,
                            EVPL.VizitClass_id
                        from v_EvnVizitPL EVPL
                        where EVPL.EvnVizitPL_pid = EPL.EvnPL_id
                        order by
                            EVPL.EvnVizitPL_setDT desc nulls last
                        limit 1
                    ) as EVPL on true
                    left join v_Diag EVPLD on EVPLD.Diag_id = EPL.Diag_id
                    left join v_Diag EVPLDL on EVPLDL.Diag_id = EPL.Diag_lid
                    left join v_HealthKind as HK on HK.HealthKind_id = EVPL.HealthKind_id
                    left join v_VizitType VT on VT.VizitType_id = EVPL.VizitType_id
                    left join lateral (
                        select MP.Person_Fio, MP.MedPersonal_id
                        from v_MedPersonal MP
                        where MP.MedPersonal_id = EPL.MedPersonal_id and MP.Lpu_id = EPL.Lpu_id
                        limit 1
                    ) as MP on true
                
                    LEFT JOIN LATERAL (
                        SELECT
                            EvnCostPrint_setDT,
                            EvnCostPrint_IsNoPrint
                        FROM v_EvnCostPrint ecp
                        where ecp.Evn_id = EPL.EvnPL_id
                        LIMIT 1
                    ) ecp on true
            left join lateral(
                select ServiceEvnStatus_id
                from v_ServiceEvnHist
                where Evn_id = EPL.EvnPL_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 MP.MedPersonal_id is not NULL  and MP.MedPersonal_id = '560101000038474' and LU.LpuBuilding_id = '560101000001390' and LS.LpuSection_id = '560101000005356' and EPL.EvnClass_id = 3 and EPL.Lpu_id = '2052468' and EVPLD.Diag_Code >= 'B17.1' and EVPLD.Diag_Code <= 'B19.9' and coalesce(EVPLD.Diag_Code, '') not in('Z20.6','R75','Z11.4','Z71.7','Z83.0','Z21') and coalesce(EVPLD.Diag_Code, '') not between 'F10.0' and 'F19.9' and coalesce(EVPLD.Diag_Code, '') not between 'F00.0' and 'F09' and coalesce(EVPLD.Diag_Code, '') not between 'F20.0' and 'F99' and coalesce(EVPLD.Diag_Code, '') not between 'B20.0' and 'B24' and coalesce(EVPLD.Diag_Code, '') not between 'A50.0' and 'A64'
                        
                
                        ORDER BY
                            
                        EPL.EvnPL_id
                        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
