
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
            PD.PersonDisp_id as "PersonDisp_id",
            PD.Person_id as "Person_id",
            PD.Server_id as "Server_id",
            to_char(PD.PersonDisp_begDate, 'DD.MM.YYYY') as "PersonDisp_begDate",
            to_char(PD.PersonDisp_endDate, 'DD.MM.YYYY') as "PersonDisp_endDate",
            to_char(coalesce(oapdv.PersonDispVizit_NextDate, PD.PersonDisp_NextDate), 'DD.MM.YYYY') as "PersonDisp_NextDate",
            to_char(
                case
when LD.PersonDisp_LastDate is not null and lapdv.PersonDispVizit_NextFactDate is not null
then case when LD.PersonDisp_LastDate > lapdv.PersonDispVizit_NextFactDate then LD.PersonDisp_LastDate else lapdv.PersonDispVizit_NextFactDate end
else coalesce(lapdv.PersonDispVizit_NextFactDate,LD.PersonDisp_LastDate)
                end, 'DD.MM.YYYY') as "PersonDisp_LastDate",
            dg1.Diag_Code as "Diag_Code",
            mp1.Person_Fio as "MedPersonal_FIO",
            mph_last.MedPersonal_FIO_last as "MedPersonalHist_FIO",
            lpus1.LpuSection_Name as "LpuSection_Name",
            scks.Sickness_Name as "Sickness_Name",
            rtrim(lpu1.Lpu_Nick) as "Lpu_Nick",
            CASE WHEN PD.Lpu_id = '150432' THEN 2 ELSE 1 END as "IsOurLpu",
            CASE WHEN noz.isnoz = 1
                THEN 'true'
                ELSE CASE WHEN noz.isnoz is not null
    THEN 'gray'
    ELSE 'false'
END
            END as "Is7Noz",
            coalesce(PCA.LpuRegion_Name,'') as "LpuRegion_Name"
        
                ,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"
            
            
                FROM
                
        
                	    v_PersonDisp PD
                    
            inner join v_PersonState PS on PD.Person_id = PS.Person_id
            left join v_Diag dg1 on PD.Diag_id = dg1.Diag_id
            left join v_Diag dg2 on PD.Diag_pid = dg2.Diag_id
            left join v_Diag dg3 on PD.Diag_nid = dg3.Diag_id
            left join v_MedPersonal mp1 on PD.MedPersonal_id = mp1.MedPersonal_id and PD.Lpu_id = mp1.Lpu_id
            left join LpuSection lpus1 on PD.LpuSection_id = lpus1.LpuSection_id
            left join Sickness scks on PD.Sickness_id = scks.Sickness_id
            left join v_Lpu lpu1 on PD.Lpu_id = lpu1.Lpu_id
            left join lateral(
                select EVPL.EvnVizitPL_setDT as PersonDisp_LastDate
                from
                    v_EvnVizitPL EVPL
                    left join v_VizitType VT on VT.VizitType_id = EVPL.VizitType_id
                where VT.VizitType_SysNick='disp'
                  and PD.PersonDisp_begDate::date <= EVPL.EvnVizitPL_setDT::date
                  and PD.Diag_id = EVPL.Diag_id
                  and EVPL.Person_id = PD.Person_id
                order by EVPL.EvnVizitPL_setDT desc
                limit 1
            ) as LD on true
            left join lateral(
                select pdv.PersonDispVizit_NextFactDate
                from v_PersonDispVizit pdv
                where pdv.PersonDisp_id = PD.PersonDisp_id
                order by pdv.PersonDispVizit_NextFactDate desc NULLS last
                limit 1
            ) as lapdv on true
            left join lateral(
                select pdv.PersonDispVizit_NextDate
                from v_PersonDispVizit pdv
                where pdv.PersonDisp_id = PD.PersonDisp_id
                  and pdv.PersonDispVizit_NextDate::date >= (select dt from mv)::date
                  and pdv.PersonDispVizit_NextFactDate is null
                order by pdv.PersonDispVizit_NextDate asc
                limit 1
            ) as oapdv on true
        
                left join lateral(
                    select
                        PersonCard_id,
                        LpuRegion_Name
                    from v_PersonCard_all
                    where Person_id = PS.Person_id
                      and LpuAttachType_id = 1
                      and Lpu_id = 150432
                    order by PersonCard_begDate desc
                    limit 1
                ) as PCA on true
            
            left join lateral(
                select
                    max(
                        case when PersonDispMedicament_begDate <= (select dt from mv) and (PersonDispMedicament_endDate >= (select dt from mv) or PersonDispMedicament_endDate is null)
                            then 1
                            else
                                case when PersonDispMedicament_begDate > (select dt from mv)
                                    then 0  else null
                                end
                        end
                    ) as isnoz
                from PersonDispMedicament pdm
                where pdm.PersonDisp_id = PD.PersonDisp_id and pdm.PersonDispMedicament_begDate is not null
            ) as noz on true
            left join lateral(
                select
                    MP_L.MedPersonal_id as MedPersonal_id_last,
                    MP_L.Person_Fio as MedPersonal_FIO_last
                from
                    v_PersonDispHist PDH_L
                    left join v_MedPersonal MP_L on MP_L.MedPersonal_id = PDH_L.MedPersonal_id
                where PDH_L.PersonDisp_id = PD.PersonDisp_id
                order by PDH_L.PersonDispHist_begDate desc
                limit 1
            ) as mph_last on true
        
                         WHERE 
                        (1 = 1) and PD.Lpu_id = 150432  and (PD.PersonDisp_endDate is null or PD.PersonDisp_endDate > (select dt from mv))  and coalesce(dg1.Diag_Code, '') not in('Z20.6','R75','Z21','Z83.0','O98.7','Z83.0','Z71.7') and coalesce(dg1.Diag_Code, '') not between 'F10.0' and 'F19.9' and coalesce(dg1.Diag_Code, '') not between 'F00.0' and 'F09' and coalesce(dg1.Diag_Code, '') not between 'F20.0' and 'F99' and coalesce(dg1.Diag_Code, '') not between 'B21.0' and 'B24' and coalesce(dg1.Diag_Code, '') not between 'B20.0' and 'B20.9' and ( exists (
                        select
                            PC.personcard_id
                        from
                            
                                v_PersonCard_all PC
                                left join v_LpuRegion LR on LR.LpuRegion_id = PC.LpuRegion_id
                                left join v_LpuRegion LR_Fap on LR_Fap.LpuRegion_id = PC.LpuRegion_fapid
                                left join v_MedStaffRegion MedStaffRegion on MedStaffRegion.LpuRegion_id = PC.LpuRegion_id
                                left join v_MedStaffFact msf on msf.MedStaffFact_id = MedStaffRegion.MedStaffFact_id and MedStaffRegion.Lpu_id = msf.Lpu_id
                                left join persis.Post p on p.id = msf.Post_id
                            
                        where
                            PC.Person_id = PS.Person_id
                                    and (
                                        PC.PersonCard_endDate is null
                                        or PC.PersonCard_endDate > (select dt from mv)
                                    )
                             and PC.Lpu_id = '150432' and PC.LpuAttachType_id = '1' and LR.LpuRegion_id = 709
                        limit 1
                    )) 
                        
                
                        ORDER BY
                            
                        
                        PS.Person_SurName,
                        PS.Person_FirName,
                        PS.Person_SecName
                        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
