
                
            with mv as (
                select
                    dbo.tzgetdate() as dt
            )
            
            
                
                SELECT 
                PC.PersonCard_id as "PersonCard_id",
                PS.Person_id as "Person_id",
                PS.Server_id as "Server_id",
                PS.PersonEvn_id as "PersonEvn_id",
                
                case when PC.Lpu_id = '5583224'
                    then PC.PersonCard_Code
                    else null
                end as "PersonCard_Code",
                PAC.PersonAmbulatCard_Num as "PersonAmbulatCard_Num",
                PAC.PersonAmbulatCard_id as "PersonAmbulatCard_id",
                coalesce('<a href=''#'' onClick=''getWnd("swPolisInfoWindow").show({Person_id:' || CAST(ps.Person_id as varchar) || '});''>'
                    || 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 "Polis_Num",
                (select coalesce(Person_Inn,'') from v_PersonState where Person_id = ps.Person_id limit 1) as "Person_Inn",
                case when dbo.getPersonPhones(ps.Person_id, '<br />') != ''
                    then coalesce('<a href=''#'' onClick=''getWnd("swPhoneInfoWindow").show({Person_id:'|| CAST(ps.Person_id as varchar) || ', currentElem: this});''>' || dbo.getPersonPhones(ps.Person_id, '<br />') || '</a>','')
                    else '<a href=''#'' onClick=''getWnd("swPhoneInfoWindow").show({Person_id:' || CAST(ps.Person_id as varchar) || ', currentElem: this});''>' || 'Отсутствует' || '</a>'
                end as "Person_Phone",
                ps.Person_Surname as "Person_Surname",
                ps.Person_Firname as "Person_Firname",
                ps.Person_Secname as "Person_Secname",
                to_char(ps.Person_Birthday, 'dd.mm.yyyy') as "Person_Birthday",
                dbo.Age2(ps.Person_Birthday, (select dt from mv)) as "Person_Age",
                ps.Sex_id as "Sex_id",
                to_char(PS.Person_DeadDT, 'dd.mm.yyyy') as "Person_deadDT",
                coalesce(AttachLpu.Lpu_Nick, 'Не прикреплен') as "AttachLpu_Name",
                coalesce(AttachLpu.Lpu_id, 0) as "AttachLpu_id",
                to_char(cast(PC.PersonCard_begDate as timestamp), 'dd.mm.yyyy') as "PersonCard_begDate",
                to_char(cast(PC.PersonCard_endDate as timestamp), 'dd.mm.yyyy') as "PersonCard_endDate",
                PC.LpuAttachType_Name as "LpuAttachType_Name",
                PC.LpuRegionType_Name as "LpuRegionType_Name",
                LR.LpuRegion_Name as "LpuRegion_Name",
                coalesce(LR_Fap.LpuRegion_Name,'') as "LpuRegion_FapName",
                coalesce(paddr.Address_Nick, paddr.Address_Address) as "Person_PAddress",
                coalesce(uaddr.Address_Nick, uaddr.Address_Address) as "Person_UAddress",
        
                CASE WHEN PS.Person_DeadDT is not null
                    THEN 'true'
                    ELSE 'false'
                END as "Person_IsDead",
                CASE WHEN coalesce(PC.PersonCard_IsAttachCondit, 1) = 2
                    then 'true'
                    else 'false'
                end as "PersonCard_IsAttachCondit",
                CASE WHEN persdata.agree = 2
                    THEN 'V'
                WHEN persdata.agree = 1
                    THEN 'X'
                    else ''
                END as "PersonLpuInfo_IsAgree",
                NA.NewslatterAccept_id as "NewslatterAccept_id",
                coalesce(to_char(NA.NewslatterAccept_begDate, 'dd.mm.yyyy'), 'Отсутствует') as "NewslatterAccept",
                CASE WHEN PC.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 pls.Polis_endDate is not null and pls.Polis_endDate <= cast((select dt from mv) as date)
                    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",
                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"
                
                FROM
                
                v_PersonState_All PS
                left join v_Polis pls on pls.Polis_id = ps.Polis_id
                left join Address uaddr on uaddr.Address_id = ps.UAddress_id
                left join Address paddr on paddr.Address_id = ps.PAddress_id
                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
                left join lateral(
                    select
                        case
                            when LpuAttachType_id = 4 and Lpu_id = '5583224' then PersonCard_id -- если есть активное прикрепление к этой МО, то до этой ветки
                            when LpuAttachType_id = 1 then PersonCard_id
                            when LpuAttachType_id in (2,3) and Lpu_id = '5583224' then PersonCard_id
                            else null
                        end as PersonCard_id
                    from v_PersonCard_all
                    where Person_id = PS.Person_id
                        and PersonCard_endDate is null
                        and LpuAttachType_id is not null
                    order by
                        case when LpuAttachType_id = 4 and Lpu_id = '5583224' then 0 else LpuAttachType_id end,
                        PersonCard_begDate
                    limit 1
                ) as PersonCard on true
                left join lateral (select * from v_PersonCard_all PC where PC.PersonCard_id = PersonCard.PersonCard_id limit 1) PC on true
                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 lateral (select * from v_Lpu lpu where lpu.Lpu_id = PC.Lpu_id limit 1) AttachLpu on true
                left join PersonRefuse PRef on (PRef.Person_id = ps.Person_id and PRef.PersonRefuse_Year = date_part('year', (select dt from mv)) + 1)
                left join v_NewslatterAccept NA on NA.Person_id = PS.Person_id and NA.Lpu_id = '5583224' and NA.NewslatterAccept_endDate is null
                left join lateral(
                    select
                    	max(case when Lpu_id = '5583224' then 1 else 0 end) as OwnLpu
                    from 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
                        PersonLpuInfo_IsAgree as agree
                    from v_PersonLpuInfo pli
                    where pli.Person_id = PS.Person_id
                        and pli.Lpu_id = '5583224'
                    order by pli.PersonLpuInfo_setDT desc
                    limit 1
                ) persdata on true
                left join lateral(
                    select
                        PersonAmbulatCard_id,
                        PersonAmbulatCard_Num
                    from v_PersonAmbulatCard
                    where Person_id = PS.Person_id and Lpu_id = '5583224'
                    order by PersonAmbulatCard_begDate desc
                    limit 1
                ) PAC on true
                
                 WHERE 
                (pls.Polis_Num = '5258510894000070' and ps.Person_deadDT is null and coalesce(PS.Person_IsUnknown,1) != 2 and (1=1) )
                
                
                
                        ORDER BY
                            
                
                lower(PS.Person_SurNameR),
                lower(PS.Person_FirNameR),
                lower(PS.Person_SecNameR)
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
