
                
            with mv as (
                select
                dbo.tzGetDate() as dt
            )
            
                
                SELECT 
        
                EPLS.EvnPLStom_id as "EvnPLStom_id",
                EPLS.Person_id as "Person_id",
                EPLS.PersonEvn_id as "PersonEvn_id",
                EPLS.Server_id as "Server_id",
                coalesce(EPLS.EvnPLStom_IsTransit, 1) as "EvnPLStom_IsTransit",
                RTRIM(EPLS.EvnPLStom_NumCard) as "EvnPLStom_NumCard",
                CNT.EvnPLStom_VizitCount as "EvnPLStom_VizitCount",
                IsFinish.YesNo_Name as "EvnPLStom_IsFinish",
                RTRIM(EVPLSD.Diag_Code) || '. ' || RTRIM(EVPLSD.Diag_Name) as "Diag_Name",
                RTRIM(MP.Person_Fio) as "MedPersonal_Fio",
                to_char(EPLS.EvnPLStom_setDate, 'DD.MM.YYYY') as "EvnPLStom_setDate",
                to_char(EPLS.EvnPLStom_disDate, 'DD.MM.YYYY') as "EvnPLStom_disDate",
                
                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",
            
                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"
,to_char(PS.Person_deadDT, 'DD.MM.YYYY') as "Person_deadDT"
,ua.Address_Address as "Person_AdrReg"
,pa.Address_Address as "Person_AdrProj"
                
            
                FROM
                
         dbo.v_PersonState PS  inner join v_EvnPLStom EPLS on EPLS.Person_id = PS.Person_id and EPLS.Lpu_id = '13101877' and EPLS.EvnClass_id = 6
                    left join v_EvnVizitPLStom EVPLS on EVPLS.EvnVizitPLStom_pid = EPLS.EvnPLStom_id and EVPLS.EvnVizitPLStom_Index = EVPLS.EvnVizitPLStom_Count - 1 and EVPLS.Lpu_id = '13101877'
                    left join YesNo IsFinish on IsFinish.YesNo_id = EPLS.EvnPLStom_IsFinish
                    left join lateral (select * from v_Diag EVPLSD where EVPLSD.Diag_id = coalesce(EPLS.Diag_id, EVPLS.Diag_id) limit 1) EVPLSD on true
                    left join v_MedStaffFact MSF on MSF.MedStaffFact_id = EVPLS.MedStaffFact_id
                        left join lateral (select * from v_Address ua where ua.Address_id = PS.UAddress_id limit 1) ua on true
                        left join lateral (select * from v_Address pa where pa.Address_id = PS.PAddress_id limit 1) pa on true
                    left join lateral(
                        select count(EvnVizitPLStom_id) as EvnPLStom_VizitCount
                        from v_EvnVizitPLStom
                        where EvnVizitPLStom_pid = EPLS.EvnPLStom_id
                    ) as CNT on true
                 left join v_Polis pls on pls.Polis_id = ps.Polis_id
                 left join v_EvnCostPrint ecp on ecp.Evn_id = EPLS.EvnPLStom_id 
            left join lateral(
                select *
                from v_MedPersonal
                where MedPersonal_id = coalesce(EPLS.MedPersonal_id, EVPLS.MedPersonal_id, MSF.MedPersonal_id)
                  and Lpu_id = '13101877'
                order by case when Lpu_id = '13101877' then 1 else 2 end
                limit 1
            ) as MP on true
        
                         WHERE 
                        (1 = 1)
                and exists (
                    select uc.UslugaComplex_id
                    from
                        v_EvnUsluga EU
                        inner join UslugaComplex uc on uc.UslugaComplex_id = EU.UslugaComplex_id
                    where EU.EvnUsluga_rid = EPLS.EvnPLStom_id
                      and EU.Lpu_id = '13101877'
                      and EU.EvnClass_SysNick in ('EvnUslugaCommon', 'EvnUslugaStom')
             and uc.UslugaComplex_Code >= 'B01.064.003'  and uc.UslugaComplex_Code <= 'B01.067.001' )
                and exists (
                    select 1
                    from v_EvnVizitPLStom EVPLS2
                    where (1 = 1) and EVPLS2.Lpu_id = '13101877'
             and EVPLS2.EvnVizitPLStom_pid = EPLS.EvnPLStom_id and EVPLS2.EvnVizitPLStom_IsPrimaryVizit = '1' and EVPLS2.EvnVizitPLStom_setDate >= '2022-03-01' and EVPLS2.EvnVizitPLStom_setDate <= '2022-03-30') and coalesce(EVPLSD.Diag_Code, '') not in('B18.0','B18.1','B17.1','B18.2','Z11.3') and coalesce(EVPLSD.Diag_Code, '') not between 'B20.0' and 'B24' and coalesce(EVPLSD.Diag_Code, '') not between 'A50.0' and 'A64' and coalesce(EVPLSD.Diag_Code, '') not between 'B16.0' and 'B16.9' and coalesce(EVPLSD.Diag_Code, '') not between 'F00.0' and 'F99' and coalesce(EVPLSD.Diag_Code, '') not between 'A15.0' and 'A19.9'
                        
                
                        ORDER BY
                            
                        EPLS.EvnPLStom_id
                        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
