
                
                
            WITH ps AS (
            with curDT as (select dbo.tzGetDate() as curDate)
            SELECT * FROM v_PersonState ps
            WHERE 1=1 and COALESCE(ps.Person_IsUnknown, 1) <> 2 
            ),
            curDT as (select dbo.tzGetDate() as curDate),
            curD as (select cast(dbo.tzGetDate() as date) as curDate),
            curDD as (select dbo.tzGetdate()::date as curDate),
            curDTD as (select date_part('day',dbo.tzGetDate()) as curDate),
            curDTM as (select date_part('month',dbo.tzGetDate()) as curDate),
            curDTY as (select date_part('year',dbo.tzGetDate()) as curDate)
            
                SELECT 
                
            ps.Person_id as "Person_id",
            ps.Server_id as "Server_id",
            ps.PersonEvn_id as "PersonEvn_id",
            ps.Person_IsInErz as "Person_IsInErz",
            ps.Person_Phone as "Person_Phone",
            ps.Person_Inn as "Person_Inn",
            ps.Person_IsUnknownSign as "Person_IsUnknownSign",
            CASE WHEN COALESCE(OC.OftenCallers_id,0) = 0 THEN 1 ELSE 2 END as "Person_isOftenCaller",
            CASE WHEN PersonRefuse.PersonRefuse_IsRefuse = 2 THEN 'true' ELSE 'false' END as "Person_IsRefuse",
            CASE WHEN (ps.Person_deadDT is not null) or (ps.Person_IsDead = 2) THEN 'true' ELSE 'false' END as "Person_IsDead",
            CASE WHEN fedl.Person_id is not null then 'true' else 'false' end as "Person_IsFedLgot",
            CASE WHEN regl.OwnLpu = 1 THEN 'true' ELSE CASE WHEN regl.OwnLpu is not null THEN 'gray' ELSE 'false' END END as "Person_IsRegLgot",
            CASE WHEN fedl.Person_id is not null OR regl.OwnLpu = 1 then 'true' else 'false' end as "Person_Lgots",
            CASE WHEN disp.OwnLpu = 1 THEN 'true' ELSE CASE WHEN disp.OwnLpu is not null THEN 'gray' ELSE 'false' END END as "Person_Is7Noz",
            uaddr.Address_Address as "UAddress_AddressText",
            paddr.Address_Address as "PAddress_AddressText",
            
            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 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 exists (
                select PersonCard_id
                from v_PersonCard
                where Person_id = ps.Person_id
                  and LpuAttachType_id = 5
                  and PersonCard_endDate >= (select curDate from curDT)
                  and CardCloseCause_id is null
            ) THEN 'true' ELSE 'false' END as "PersonCard_IsDms"
            
            ,COALESCE(ps.Person_SurName, '') as "PersonSurName_SurName"
            ,COALESCE(ps.Person_FirName, '') as "PersonFirName_FirName"
            ,COALESCE(ps.Person_SecName, '') as "PersonSecName_SecName"
            ,pls.Polis_Ser as "Polis_Ser"
            ,pls.PolisFormType_id as "PolisFormType_id"
            ,pls.OrgSMO_id as "OrgSMO_id"
            ,pls.OMSSprTerr_id as "OMSSprTerr_id"
            ,to_char(pls.Polis_endDate, 'DD.MM.YYYY') as "Polis_endDate"
            ,ps.Person_Snils as "Person_Snils"
            ,to_char(DOC.Document_begDate, 'DD.MM.YYYY') as "Document_begDate"
            ,OD.OrgDep_Name as "OrgDep_Text"
            ,case when pls.PolisType_id = 4 then ps.Person_EdNum else pls.Polis_Num end as "Polis_Num"
            ,ps.Document_Ser as "Document_Ser"
            ,ps.Document_Num as "Document_Num"
            ,DocType.DocumentType_Name as "DocumentType_Name"
            ,ps.Person_edNum as "Polis_EdNum"
            ,dbo.Age(ps.Person_BirthDay, (select curDate from curDT)) as "Person_Age"
            ,to_char(ps.Person_BirthDay::timestamp, 'DD.MM.YYYY') as "PersonBirthDay_BirthDay"
            ,to_char(ps.Person_deadDT::timestamp, 'DD.MM.YYYY') as "Person_deadDT"
            ,ps.Sex_id as "Sex_id"
            ,org.org_nick  as "Lpu_Nick"
            ,lpu.Lpu_id as "CmpLpu_id"
            ,pcard1.PersonCardState_Code as "PersonCard_Code"
        
            ,'edit' as "accessType"
        
            
                FROM
                
                
            ps
            
            left join Person per on per.Person_id = ps.Person_id
            left join v_Document DOC on DOC.Document_id = PS.Document_id
            left join v_DocumentType DocType on DOC.DocumentType_id = DocType.DocumentType_id
            left join v_OrgDep OD on DOC.OrgDep_id = OD.OrgDep_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 PersonCardState_Code
                from PersonCardState
                where Person_id = ps.Person_id
                  and Lpu_id = '13100901'
                  and LpuAttachType_id = 1
                   limit 1
            ) as pcard1 ON true
            left join Polis pls  on pls.Polis_id = ps.Polis_id
            left join v_OrgSMO vos on vos.OrgSmo_id = pls.OrgSmo_id
            --Информаия про СМО refs #PROMEDWEB-5677
            left join lateral (
                select
                    pc.Person_id as PersonCard_Person_id,
                    pc.Lpu_id
                from v_PersonCard pc
                where pc.Person_id = ps.Person_id
                  and LpuAttachType_id = 1
                  and (1=1)
                order by PersonCard_begDate desc
                   limit 1
            ) as pcard ON true
            LEFT JOIN lpu ON pcard.Lpu_id=lpu.Lpu_id
            LEFT JOIN org ON org.org_id = lpu.org_id
            left join lateral (
                select PersonRefuse_IsRefuse
                from v_PersonRefuse pr
                where pr.Person_id = ps.Person_id
                  and pr.PersonRefuse_IsRefuse = 2
                  and pr.PersonRefuse_Year = (select curDate from curDTY)
                   limit 1
            ) as PersonRefuse ON true
            left join lateral (
                select Person_id
                from
                	v_personprivilege pp
                    left join v_PrivilegeType pt on pt.PrivilegeType_id = pp.PrivilegeType_id
                where pp.person_id = ps.person_id
                  and pt.ReceptFinance_id = 1
                  and pp.personprivilege_begdate <= (select curDate from curDT)
                  and COALESCE(pp.personprivilege_enddate, (select curDate from curDT)) >= (select curDate from curDD)
                   limit 1
            ) as fedl ON true
            left join lateral (
                select max(case when Lpu_id = '13100901' then 1 else 0 end) as OwnLpu
                from
                	v_personprivilege pp
                    left join v_PrivilegeType pt on pt.PrivilegeType_id = pp.PrivilegeType_id
                where pp.person_id = ps.person_id
                  and pt.ReceptFinance_id = 2
                  and pp.personprivilege_begdate <= (select curDate from curDT)
                  and COALESCE(pp.personprivilege_enddate, (select curDate from curDT)) >= (select curDate from curDD)
            ) as regl ON true
            left join lateral (
                select max(case when Lpu_id = '13100901' then 1 else 0 end) as OwnLpu
                from v_PersonDisp
                where Person_id = ps.Person_id
                  and COALESCE(PersonDisp_endDate, (select curDate from curDT)+interval '1 day') > (select curDate from curDT)
                  and Sickness_id in (1,3,4,5,6,7,8)
            ) as disp ON true
            left join lateral (
                select OftenCallers_id
                from v_OftenCallers
                where Person_id = ps.Person_id
                   limit 1
            ) OC ON true
            left join v_Address uaddr on ps.UAddress_id = uaddr.Address_id
            left join v_Address paddr on ps.PAddress_id = paddr.Address_id
        
             WHERE 
            (1=1 and regl.OwnLpu is not null)
            
                
                        ORDER BY
                            
                
            
            ps.Person_SurNameR ASC,
            ps.Person_FirNameR ASC,
            ps.Person_SecNameR ASC
        
            
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
