
                
            with mv as (
                select
                localtimestamp as dt,to_char(2023, 'yyyy') || '-12-31' as PPD_YearEndDate
            ),
            
                
                
            D1 AS (
                SELECT Diag_id FROM diag D1 WHERE (
                    D1.Diag_Code like 'I10.%'
                    or D1.Diag_Code like 'I11.%'
                    or D1.Diag_Code like 'I12.%'
                    or D1.Diag_Code like 'I13.%'
                    or D1.Diag_Code like 'I15.%'
                    or D1.Diag_Code in ('I20.1', 'I20.8', 'I20.9','I25.0', 'I25.1',  'I25.2', 'I25.5', 'I25.6', 'I25.8', 'I25.9')
                )
            ),
            D2 AS (
                SELECT Diag_id FROM diag D2 WHERE(
                    D2.Diag_Code like 'I48.%'
                    or D2.Diag_Code like 'E11.%'
                    or D2.Diag_Code in ('J44.0', 'J44.8', 'J44.9', 'I69.0', 'I69.1', 'I69.2', 'I69.3', 'I69.4', 'I67.8')
                )
            )
                
                SELECT 
                -- TOP SL, FIXDATE 18.05.2023, JIRA PROMEDWEB-148004
        
            PS.Person_id as "Person_id",
            RTRIM(PS.Person_SurName) || ' ' || coalesce(PS.Person_FirName, '') || ' ' || coalesce(PS.Person_SecName, '') as "Person_FIO",
            to_char(PS.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday",
            sex.Sex_Name as "Person_Sex",
            to_char(IsPersonDopDispPassed.EvnPLDisp_setDate, 'DD.MM.YYYY') as "EvnPLDisp_setDate",
            to_char(IsPersonDopDispPassed.EvnPLDisp_disDate, 'DD.MM.YYYY') as "EvnPLDisp_disDate",
            2 as "IsChecked"
        
            
                FROM
                
        dbo.v_PersonState_all PS
            join Sex on Sex.Sex_id = PS.Sex_id
            left join lateral(
                select
                    epld.EvnPLDisp_id,
                    epld.EvnPLDisp_setDate,
                    epld.EvnPLDisp_disDate
                from v_EvnPLDisp epld
                where epld.Person_id = PS.Person_id
                  and (epld.EvnPLDisp_setDate between '2023-01-01' and '2023-12-31 23:59:59')
                  and epld.DispClass_id = '31'
                limit 1
            ) as IsPersonDopDispPassed on true
        
            left join lateral(
                select ppl.PlanPersonList_id
                from
                    v_PlanPersonList ppl
                    inner join v_PersonDopDispPlan pddp on pddp.PersonDopDispPlan_id = ppl.PersonDopDispPlan_id
                    left join v_PlanPersonListStatus pddps on pddps.PlanPersonListStatus_id = PPL.PlanPersonListStatus_id
                where ppl.Person_id = PS.Person_id
                  and pddp.PersonDopDispPlan_Year = 2023
                  and pddp.DispClass_id = '31'
                  and coalesce(PDDPS.PlanPersonListStatusType_id, 1) <> 4
                limit 1
            ) as IsPersonDopDispPlanned on true
        
                         WHERE 
                        (1 = 1) and PS.Person_DeadDT is null and IsPersonDopDispPlanned.PlanPersonList_id is null  and IsPersonDopDispPassed.EvnPLDisp_id is null  and exists(
                select
                    EPL.Person_id
                from
                    v_EvnPL EPL
                    join v_Person P on EPL.Person_id = P.Person_id and P.Person_deadDT is null
                    join v_Diag D on EPL.Diag_id = D.Diag_id and D.Diag_Code in ('U07.1', 'U07.2')
                where
                    EPL.Person_id = PS.Person_id
                    and EPL.EvnPL_IsFinish = 2
                    and EPL.Evnpl_disDT <= localtimestamp + interval '-60 days'
            ) and exists(
                    select
                        PD.Person_id
                    from
                        v_PersonDiag PD
                    where
                        PD.Person_id = PS.Person_id
                        and PD.Diag_id IN (SELECT * FROM D1)
                )
                and exists(
                    select
                        PD.Person_id
                    from
                        v_PersonDiag PD
                    where
                        PD.Person_id = PS.Person_id
                        and PD.Diag_id IN (SELECT * FROM D2)
                ) and ( exists (
                        select
                            PC.personcard_id
                        from
                            
                                v_PersonCard_all PC
                                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_MedStaffRegion MedStaffRegion on MedStaffRegion.LpuRegion_id = PC.LpuRegion_id
                                left join v_MedStaffFact msf on msf.MedStaffFact_id = MedStaffRegion.MedStaffFact_id and MedStaffRegion.Lpu_id = msf.Lpu_id
                                left join persis.Post p on p.id = msf.Post_id
                            
                        where
                            PC.Person_id = PS.Person_id
                                    and (
                                        PC.PersonCard_endDate is null
                                        or PC.PersonCard_endDate > (select dt from mv)
                                    )
                             and PC.Lpu_id = '70101000000002'
                        limit 1
                    )) 
                        -- end where
                
                    group by
                        PS.Person_id,
                        PS.Person_SurName,
                        PS.Person_FirName,
                        PS.Person_SecName,
                        Sex.Sex_Name,
                        PS.Person_Birthday,
                        IsPersonDopDispPassed.EvnPLDisp_setDate,
                        IsPersonDopDispPassed.EvnPLDisp_disDate
                    
                
                        ORDER BY
                            
                        
                        PS.Person_SurName,
                        PS.Person_FirName,
                        PS.Person_SecName
                        
                OFFSET 0 ROWS
                FETCH NEXT 5000000 ROWS ONLY
                
