
                
            with curDT as (select localtimestamp as curDate),
                
                PS as (
                    select
                        PS.*
                    from v_PersonState_All PS 
                        
                        WHERE (1=1) and LOWER(ps.Person_SurNameR) LIKE LOWER('Б')||'%'  and coalesce(PS.Person_IsUnknown, 1) != 2)
            
                
                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 = '13101263' then PC.PersonCard_Code else null end as "PersonCard_Code",
            PAC.PersonAmbulatCard_id as "PersonAmbulatCard_id",
            PAC.PersonAmbulatCard_Num as "PersonAmbulatCard_Num",
            COALESCE('<a href=''#'' onClick=''getWnd("swPolisInfoWindow").show({Person_id:' || ps.Person_id::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 "Person_PolisInfo",
            (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:' || ps.Person_id::varchar || '});''>'|| dbo.getPersonPhones(ps.Person_id, '<br />') ||'</a>','')
                else '<a href=''#'' onClick=''getWnd("swPhoneInfoWindow").show({Person_id:' || ps.Person_id::varchar || '});''>'|| 'Отсутствует' ||'</a>'
            end as "Person_Phone",
            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::timestamp, 'DD.MM.YYYY') as "Person_Birthday",
            to_char(PS.Person_DeadDT, 'DD.MM.YYYY') as "Person_deadDT",
            case when PS.Person_id is not null then dbo.Age2(PS.Person_BirthDay, (select curDate from curDT)) end as "Person_Age",
            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 <= (select curDate from curDT)
                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 exists(
                select *
                from v_PersonQuarantine PQ
                where PQ.Person_id = PS.Person_id
                and PQ.PersonQuarantine_endDT is null
            ) then 'true' else 'false' end as "PersonQuarantine_IsOn",
            case
                when disp.OwnLpu = 1 then 'true'
                when disp.OwnLpu is not null then 'gray'
                else 'false'
            end as "Person_Is7Noz"
        
                
                FROM
                
                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
                        case
                            when LpuAttachType_id = 4 and Lpu_id = '13101263' then PersonCard_id
                            when LpuAttachType_id = 1 then PersonCard_id
                            when LpuAttachType_id in (2, 3) and Lpu_id = '13101263' 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 = '13101263' 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 curDate from curDT)+interval '1 day' ))
                left join v_NewslatterAccept NA on NA.Person_id = PS.Person_id and NA.Lpu_id = '13101263' and NA.NewslatterAccept_endDate is null
                left join lateral (
                    select max(case when Lpu_id = '13101263' then 1 else 0 end) as OwnLpu
                    from PersonDisp
                    where Person_id = ps.Person_id
                      and (PersonDisp_endDate is null or PersonDisp_endDate > (select curDate from curDT))
                      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 = '13101263'
                    order by pli.PersonLpuInfo_setDT desc
                    limit 1
                ) as persdata ON true
                left join lateral (
                    select
                        PersonAmbulatCard_id,
                        PersonAmbulatCard_Num
                    from v_PersonAmbulatCard
                    where Person_id = PS.Person_id
                      and Lpu_id = '13101263'
                      and coalesce(PersonAmbulatCard_endDate, (select curDate from curDT)) >= (select curDate from curDT)
                    order by PersonAmbulatCard_begDate desc
                    limit 1
                ) as PAC ON true
                
                 WHERE 
                (1=1) and 
                exists(
                    (select 1
       				from KLStreet ks
        			where ks.KLStreet_id = uaddr.KLStreet_id
        			and LOWER(ks.KLStreet_Name) LIKE LOWER('иНЖЕНЕРНАЯ')||'%' and uaddr.Address_House = '4'
        			limit 1)

        			union all

        			(select 1
       				from KLStreet ks
        			where ks.KLStreet_id = paddr.KLStreet_id
        			and LOWER(ks.KLStreet_Name) LIKE LOWER('иНЖЕНЕРНАЯ')||'%' and paddr.Address_House = '4'
        			limit 1)
                )
            
                
                
                        ORDER BY
                            
                
                PS.Person_SurName,
                PS.Person_FirName,
                PS.Person_SecName
                
                OFFSET 0 ROWS
                FETCH NEXT 50 ROWS ONLY
                
