
                
            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 PS.Person_IsDead = 2 THEN 'true' ELSE 'false' END as "Person_IsDead",
            PC.PersonCard_Code as "PersonCard_Code",
            dbo.getPersonPhones(PS.Person_id, '<br />') as "Person_Phone",
            coalesce(AttachLpu.Lpu_Nick, 'Не прикреплен') as "AttachLpu_Name",
            to_char(PC.PersonCard_begDate, 'DD.MM.YYYY') as "PersonCard_begDate",
            to_char(PC.PersonCard_endDate, 'DD.MM.YYYY') as "PersonCard_endDate",
            coalesce(PC.LpuAttachType_id::text, '') as "LpuAttachType_id",
            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",
            PACLT.AmbulatCardLocatType_Name as "AmbulatCardLocatType_Name",
            PAC.PersonAmbulatCard_id as "PersonAmbulatCard_id",
            CASE WHEN coalesce(PC.PersonCard_IsAttachCondit, 1) = 2 then 'true' else 'false' end as "PersonCard_IsAttachCondit",
            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 disp.OwnLpu = 1 THEN 'true' ELSE CASE WHEN disp.OwnLpu is not null THEN 'gray' ELSE 'false' END 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  inner join lateral
                        (select * from  v_PersonCard PC where PC.Person_id = PS.Person_id) PC on true
                left join lateral (
                    select
                        PAC.personambulatcard_id
                    from
                        v_PersonAmbulatCard PAC
                    where
                        PAC.Person_id = pc.Person_id
                        and PAC.personambulatcard_num = pc.PersonCard_code
                    limit 1
                ) PAC on true
                left join lateral(
                    select ACLT.AmbulatCardLocatType_Name
                    from
                        v_PersonAmbulatCardLocat PACL
                        left join AmbulatCardLocatType ACLT on ACLT.AmbulatCardLocatType_id = PACL.AmbulatCardLocatType_id
                        left join v_PersonAmbulatCardLink PACLink on PACLink.PersonAmbulatCard_id = PACL.PersonAmbulatCard_id
                    where pc.PersonCard_id = PACLink.PersonCard_id
                    order by PACL.PersonAmbulatCardLocat_begDate desc
                    limit 1
                ) as PACLT 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 v_Lpu AttachLpu on AttachLpu.Lpu_id = PC.Lpu_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 v_Polis pls on pls.Polis_id = ps.Polis_id
                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 lateral(
                    select max(case when Lpu_id = '2878' 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
            
                         WHERE 
                        (1 = 1) and coalesce(PS.Person_IsUnknown,1) != 2  and exists (select AR.Address_id from Address AR  where AR.Address_id = PS.UAddress_id and AR.KLCountry_id = '643' and AR.KLRgn_id = '52' and AR.KLCity_id = '3217' and AR.KLStreet_id = '394320' and AR.Address_House = '10б')  and (PC.PersonCard_endDate is null or PC.PersonCard_endDate::timestamp > (select dt from mv)) and PC.Lpu_id = '2878' and PC.Lpu_id = '2878'
                        
                
                        ORDER BY
                            
                        
                        "Person_Surname",
                        "Person_Firname",
                        "Person_Secname"
                        
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
