
                
            with mv as (
                select
                localtimestamp as dt
            )
            
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
            PS.Person_id as "Person_id",
            PS.Server_id as "Server_id",
            PS.PersonEvn_id as "PersonEvn_id",
            CASE WHEN PS.Person_DeadDT is not null  THEN 'true' ELSE 'false' END as "Person_IsDead",
            case when PersonCard.Lpu_id = '808' then PersonCard.PersonCard_Code else null end as "PersonCard_Code",
            coalesce('<a href=''#'' onClick=''getWnd("swPolisInfoWindow").show({Person_id:' || PS.Person_id || '});''>'|| case when PLS.PolisType_id = 4 and coalesce(PS.Person_EdNum, '') != '' then PS.Person_EdNum else coalesce(PS.Polis_Ser, '') || ' ' || coalesce(PS.Polis_Num, '') end ||'</a>','') as "Person_PolisInfo",
            case
                when dbo.getPersonPhones(PS.Person_id, '<br />') != '' then coalesce('<a href=''#'' onClick=''getWnd("swPhoneInfoWindow").show({Person_id:' || PS.Person_id || '});''>'|| dbo.getPersonPhones(PS.Person_id, '<br />') ||'</a>','')
                else '<a href=''#'' onClick=''getWnd("swPhoneInfoWindow").show({Person_id:' || PS.Person_id || '});''>'|| 'Отсутствует' ||'</a>'
            end as "Person_Phone",
            case when PS.Person_id is not null then dbo.Age2(PS.Person_BirthDay, (select dt from mv)) end as "Person_Age",
            coalesce(AttachLpu.Lpu_Nick, 'Не прикреплен') as "AttachLpu_Name",
            to_char(PersonCard.PersonCard_begDate, 'DD.MM.YYYY') as "PersonCard_begDate",
            to_char(PersonCard.PersonCard_endDate, 'DD.MM.YYYY') as "PersonCard_endDate",
            PersonCard.LpuAttachType_Name as "LpuAttachType_Name",
            PersonCard.LpuRegionType_Name as "LpuRegionType_Name",
            LR.LpuRegion_Name as "LpuRegion_Name",
            coalesce(LR_Fap.LpuRegion_Name,'') as "LpuRegion_FapName",
            NA.NewslatterAccept_id as "NewslatterAccept_id",
            coalesce(to_char(NA.NewslatterAccept_begDate, 'DD.MM.YYYY'), 'Отсутствует') as "NewslatterAccept",
            CASE
                WHEN PersonCard.PersonCard_IsAttachCondit = 1 then 'false'
                WHEN PersonCard.PersonCard_IsAttachCondit = 2 then 'true'
                ELSE null
            end as "PersonCard_IsAttachCondit",
            CASE WHEN PersonCard.PersonCardAttach_id IS NULL then 'false' else 'true' end as "PersonCardAttach",
            CASE WHEN PS.Person_IsRefuse = 1 THEN 'true' ELSE 'false' END as "Person_IsRefuse",
            CASE WHEN PRef.PersonRefuse_IsRefuse = 2 THEN 'true' ELSE 'false' END as "Person_NextYearRefuse",
            CASE WHEN PS.Person_IsFedLgot = 1 THEN 'true' ELSE 'false' END as "Person_IsFedLgot",
            CASE WHEN PS.Person_IsRegLgot = 1 THEN 'true' ELSE 'false' END as "Person_IsRegLgot",
            CASE WHEN PQ.PersonQuarantine_id is not null THEN 'true' ELSE 'false' END as "PersonQuarantine_IsOn",
            to_char(PQ.PersonQuarantine_begDT, 'DD.MM.YYYY') as "PersonQuarantine_begDT",
            CASE WHEN disp.OwnLpu = 1 THEN 'true' WHEN disp.OwnLpu is not null THEN 'gray' ELSE 'false' END as "Person_Is7Noz",
            
                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(paddr.Address_Nick, paddr.Address_Address) as "Person_PAddress",
            coalesce(uaddr.Address_Nick, uaddr.Address_Address) as "Person_UAddress"
        
                ,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_PersonState_all PS
                    
                left join lateral(
                    select
                        PersonCard_id,
                        PersonCard_Code,
                        PersonCard_begDate,
                        PersonCard_endDate,
                        LpuAttachType_Name,
                        LpuRegionType_Name,
                        PersonCard_IsAttachCondit,
                        PersonCardAttach_id,
                        LpuRegion_id,
                        Lpu_id,
                        LpuRegion_fapid
                    from v_PersonCard PC
                    where PC.Person_id = PS.Person_id 
                    order by LpuAttachType_id
                    limit 1
                ) as PersonCard on true
                left join v_LpuRegion LR on LR.LpuRegion_id = PersonCard.LpuRegion_id
                left join v_LpuRegion LR_Fap on LR_Fap.LpuRegion_id = PersonCard.LpuRegion_fapid
                left join v_Lpu AttachLpu on AttachLpu.Lpu_id = PersonCard.Lpu_id
                left join v_Address uaddr on uaddr.Address_id = ps.UAddress_id
                left join v_Address paddr on paddr.Address_id = ps.PAddress_id
                left join v_PersonRefuse PRef on PRef.Person_id = ps.Person_id and PRef.PersonRefuse_Year = date_part('year', (select dt from mv)) + 1
                left join v_Polis pls on pls.Polis_id = ps.Polis_id
                left join v_NewslatterAccept NA on NA.Person_id = PS.Person_id and NA.Lpu_id = '808' and NA.NewslatterAccept_endDate is null
                left join lateral(
                    select max(case when Lpu_id = '808' then 1 else 0 end) as OwnLpu
                    from v_PersonDisp
                    where Person_id = ps.Person_id
                      and (PersonDisp_endDate is null or PersonDisp_endDate > (select dt from mv))
                      and Sickness_id in (1, 3, 4, 5, 6, 7, 8)
                ) as disp on true
                left join lateral (
                    select PQ.*
                    from v_PersonQuarantine PQ
                    where PQ.Person_id = ps.Person_id
                    and PQ.PersonQuarantine_endDT is null
                    limit 1
                    ) PQ on true
            
                         WHERE 
                        (1 = 1) and coalesce(PS.Person_IsUnknown,1) != 2 
                    and exists(
                        select Polis_id
                        from
                            Polis
                            left join OmsSprTerr on OmsSprTerr.OmsSprTerr_id = Polis.OmsSprTerr_id
                        where Polis.Polis_id = PS.Polis_id
                 and Polis.Polis_Ser = '0558800898000566') 
                        
                
                        ORDER BY
                            
                        
                        PS.Person_SurName,
                        PS.Person_FirName,
                        PS.Person_SecName
                        
                OFFSET 0 ROWS
                FETCH NEXT 50 ROWS ONLY
                
