
                
                
                with  PC as (
                        SELECT PC.personcard_id, PC.person_id
                        FROM v_PersonCard PC
                        LEFT JOIN v_personcardstate vPC on vPC.personcard_id = PC.personcard_id
                            
                        left join lateral( select * from v_LpuRegion LR where LR.LpuRegion_id = PC.LpuRegion_id limit 1)lr on true
                        left join lateral( select * from v_LpuRegion LR_Fap where LR_Fap.LpuRegion_id = PC.LpuRegion_fapid)LR_Fap on true
                    
                        WHERE
                            (vPC.PersonCardstate_endDate is null or cast(vPC.PersonCardstate_endDate as timestamp) > cast(replace('2022-03-31 23:44:52.04571', '?', '') as timestamp))
                             and LR.LpuRegion_id = 140101000000398
                        ), 
            EvnPLDispDop13Top as (
                select
                        EvnPLDispDop13.EvnPLDispDop13_id,
                        EvnPLDispDop13.EvnPLDispDop13_IsEndStage,
                        EvnPLDispDop13.EvnPLDispDop13_isMobile,
                        EvnPLDispDop13.EvnPLDispDop13_IsTransit,
                        EvnPLDispDop13.HealthKind_id,
                        EvnPLDispDop13.EvnPLDispDop13_IsArchive,
                        PS.Person_id,
                        PS.Server_id,
                        EvnPLDispDop13.PersonEvn_id,
                        EvnPLDispDop13.Lpu_id,
                        EvnPLDispDop13.EvnPLDispDop13_consDT,
                        EvnPLDispDop13.EvnPLDispDop13_disDate,
                        EvnPLDispDop13.EvnPLDispDop13_IsRefusal,
                        EvnPLDispDop13.EvnPLDispDop13_IsTwoStage,
                        DopDispSecond.EvnPLDispDop13_id as DopDispSecond_EvnPLDispDop13_id,
                        DopDispSecond.HealthKind_id as DopDispSecond_HealthKind_id,
                        DopDispSecond.EvnPLDispDop13_IsEndStage as DopDispSecond_EvnPLDispDop13_IsEndStage,
                        DopDispSecond.EvnPLDispDop13_consDT as DopDispSecond_EvnPLDispDop13_consDT,
                        DopDispSecond.EvnPLDispDop13_disDate as DopDispSecond_EvnPLDispDop13_disDate,
                        PS.UAddress_id,
                        PS.PAddress_id,
                        
                RTRIM(PS.Person_Surname) as Person_Surname,
                RTRIM(PS.Person_Firname) as Person_Firname,
                RTRIM(PS.Person_Secname) as Person_Secname,
                PS.Person_Birthday
            
                from v_PersonState PS
                    INNER JOIN PC on PC.Person_id = PS.Person_id
                    
                left join v_EvnPLDispDop13 EvnPLDispDop13 on PS.Person_id = EvnPLDispDop13.Person_id and EvnPLDispDop13.Lpu_id in (7,7) and COALESCE(EvnPLDispDop13.DispClass_id,1) = 1 and date_part('year', EvnPLDispDop13.EvnPLDispDop13_consDT) = 2022 
            
                    LEFT JOIN LATERAL (
                        select
                            EPLDD13_SEC.EvnPLDispDop13_id,
                            EPLDD13_SEC.EvnPLDispDop13_isPaid,
                            EPLDD13_SEC.EvnPLDispDop13_IsTransit,
                            EPLDD13_SEC.EvnPLDispDop13_setDate,
                            EPLDD13_SEC.EvnPLDispDop13_disDate,
                            EPLDD13_SEC.EvnPLDispDop13_consDT,
                            EPLDD13_SEC.HealthKind_id,
                            COALESCE(EPLDD13_SEC.EvnPLDispDop13_IsEndStage, 1) as EvnPLDispDop13_IsEndStage,
                            HK_SEC.HealthKind_Name,
                            EPLDD13_SEC.EvnPLDispDop13_insDT,
                            EPLDD13_SEC.EvnPLDispDop13_updDT,
                            EPLDD13_SEC.pmUser_insID,
                            EPLDD13_SEC.pmUser_updID
                        from
                            v_EvnPLDispDop13 EPLDD13_SEC
                            left join v_HealthKind HK_SEC on HK_SEC.HealthKind_id = EPLDD13_SEC.HealthKind_id
                        where
                            EPLDD13_SEC.EvnPLDispDop13_fid = EvnPLDispDop13.EvnPLDispDop13_id
                            
                        limit 1
                    ) DopDispSecond on true -- данные по 2 этапу
                where
                    (1=1) and (PS.Person_deadDT >= cast(cast(2022 as varchar) || '-01-01' as timestamp) OR PS.Person_deadDT IS NULL) and (
            (
                
                    (
                        -- Проверка по возрасту.
                        (
                            -- ДВН проводится раз в 3 года.
                            -- От 18 до 39 (младше 40 лет).
                            (dbo.Age2(PS.Person_BirthDay, '2022-12-31') >= 18)
                            AND (dbo.Age2(PS.Person_BirthDay, '2022-12-31') <= 39)
                            -- нет карты диспансеризации за два предыдущих года.
                            AND not exists (SELECT EvnPLDispDop13_id FROM v_EvnPLDispDop13 WHERE (date_part('year', EvnPLDispDop13_disDT) BETWEEN 2022 - 2 AND 2022 - 1) AND Person_id = PS.Person_id LIMIT 1)
                            -- нет карты профосмотра в указанному году.
                            AND not exists (SELECT EvnPLDispProf_id FROM v_EvnPLDispProf WHERE date_part('year', EvnPLDispProf_consDT) = 2022 AND Person_id = PS.Person_id LIMIT 1)
                        )
                        OR (
                            -- ДВН проводится ежегодно.
                            -- Старше 40 лет включительно.
                            dbo.Age2(PS.Person_BirthDay, '2022-12-31') >= 40
                        )
                    )
                
            )
            and dbo.Age2(PS.Person_BirthDay, '2022-12-31') <= 999
        )
            )
        
                
                SELECT 
                
            cast(COALESCE(EPLDD13.EvnPLDispDop13_id, 0) as varchar) || '_' || cast(COALESCE(EPLDD13.Person_id, 0) as varchar) as "id",
            EPLDD13.EvnPLDispDop13_id as "EvnPLDispDop13_id",
            COALESCE(EPLDD13.EvnPLDispDop13_IsTransit, 1) as "EvnPLDispDop13_IsTransit",
            EPLDD13.Person_id as "Person_id",
            EPLDD13.Server_id as "Server_id",
            EPLDD13.PersonEvn_id as "PersonEvn_id",
            RTRIM(EPLDD13.Person_Surname) as "Person_Surname",
            RTRIM(EPLDD13.Person_Firname) as "Person_Firname",
            RTRIM(EPLDD13.Person_Secname) as "Person_Secname",
            to_char(EPLDD13.Person_Birthday, 'DD.MM.YYYY') as "Person_Birthday",
            IsFinish.YesNo_Name as "EvnPLDispDop13_IsEndStage",
            IsMobile.YesNo_Name as "EvnPLDispDop13_IsMobile",
            IsFinishSecond.YesNo_Name as "EvnPLDispDop13Second_IsEndStage",
            to_char(EPLDD13.EvnPLDispDop13_consDT, 'DD.MM.YYYY') as "EvnPLDispDop13_setDate",
            to_char(EPLDD13.EvnPLDispDop13_disDate, 'DD.MM.YYYY') as "EvnPLDispDop13_disDate",
            HK.HealthKind_Name as "EvnPLDispDop13_HealthKind_Name",
            case when EPLDD13.EvnPLDispDop13_IsEndStage = 2 and EPLDD13.EvnPLDispDop13_IsTwoStage = 2 then to_char(EPLDD13.EvnPLDispDop13_disDate, 'DD.MM.YYYY') else null end as "EvnPLDispDop13Second_napDate",
            DopDispSecond.HealthKind_Name as "EvnPLDispDop13Second_HealthKind_Name",
            case when EPLDD13.EvnPLDispDop13_IsRefusal = 2 then to_char(EPLDD13.EvnPLDispDop13_consDT, 'DD.MM.YYYY') else null end as "EvnPLDispDop13_rejDate",
            case
                when EPLDD13AL.EvnPLDispDop13_id is not null then 4
                when EPLDD13.EvnPLDispDop13_id is null then 0
                when EPLDD13.Lpu_id = '7' then 0
                when EPLDD13.Lpu_id in (7,7) and COALESCE(EPLDD13.EvnPLDispDop13_IsTransit, 1) = 2 then 0
                else 4
            end as "AccessType_Code",
            DopDispSecond.EvnPLDispDop13_id as "EvnPLDispDop13Second_id",
            to_char(DopDispSecond.EvnPLDispDop13_consDT, 'DD.MM.YYYY') as "EvnPLDispDop13Second_setDate",
            to_char(DopDispSecond.EvnPLDispDop13_disDate, 'DD.MM.YYYY') as "EvnPLDispDop13Second_disDate",
            case when DDICDataSecond.DopDispInfoConsent_IsAgree = 1 then to_char(DopDispSecond.EvnPLDispDop13_consDT, 'DD.MM.YYYY') else null end as "EvnPLDispDop13Second_rejDate",
            to_char(ecp.EvnCostPrint_setDT, 'DD.MM.YYYY') as "EvnCostPrint_setDT",
            case when ecp.EvnCostPrint_IsNoPrint = 2 then 'Отказ от справки' when ecp.EvnCostPrint_IsNoPrint = 1 then 'Справка выдана' else '' end as "EvnCostPrint_IsNoPrintText"
        
            ,COALESCE(UAdd.Address_Nick, UAdd.Address_Address) as "ua_name"
            ,COALESCE(PAdd.Address_Nick, PAdd.Address_Address) as "pa_name"
        
                
                FROM
                
                EvnPLDispDop13Top EPLDD13
            left join v_EvnCostPrint ecp on ecp.Evn_id = EPLDD13.EvnPLDispDop13_id
            left join YesNo IsFinish on IsFinish.YesNo_id = COALESCE(EPLDD13.EvnPLDispDop13_IsEndStage, 1)
            left join YesNo IsMobile on IsMobile.YesNo_id = COALESCE(EPLDD13.EvnPLDispDop13_isMobile, 1)
            left join v_HealthKind HK on HK.HealthKind_id = EPLDD13.HealthKind_id
            left join v_Address UAdd on UAdd.Address_id = EPLDD13.UAddress_id
            left join v_Address PAdd on PAdd.Address_id = EPLDD13.PAddress_id
            LEFT JOIN LATERAL (
                select
                    EPLDD13_SEC.EvnPLDispDop13_id,
                    EPLDD13_SEC.EvnPLDispDop13_isPaid,
                    EPLDD13_SEC.EvnPLDispDop13_IsTransit,
                    EPLDD13_SEC.EvnPLDispDop13_setDate,
                    EPLDD13_SEC.EvnPLDispDop13_disDate,
                    EPLDD13_SEC.EvnPLDispDop13_consDT,
                    EPLDD13_SEC.HealthKind_id,
                    COALESCE(EPLDD13_SEC.EvnPLDispDop13_IsEndStage, 1) as EvnPLDispDop13_IsEndStage,
                    HK_SEC.HealthKind_Name,
                    EPLDD13_SEC.EvnPLDispDop13_insDT,
                    EPLDD13_SEC.EvnPLDispDop13_updDT,
                    EPLDD13_SEC.pmUser_insID,
                    EPLDD13_SEC.pmUser_updID
                from
                    v_EvnPLDispDop13 EPLDD13_SEC
                    left join v_HealthKind HK_SEC on HK_SEC.HealthKind_id = EPLDD13_SEC.HealthKind_id
                where
                    EPLDD13_SEC.EvnPLDispDop13_fid = EPLDD13.EvnPLDispDop13_id
                    
                limit 1
            ) DopDispSecond on true -- данные по 2 этапу
            left join YesNo IsFinishSecond on IsFinishSecond.YesNo_id = DopDispSecond.EvnPLDispDop13_IsEndStage
            LEFT JOIN LATERAL (
                select
                    DDIC.DopDispInfoConsent_IsAgree
                from
                    v_DopDispInfoConsent DDIC
                    left join v_SurveyTypeLink STL on STL.SurveyTypeLink_id = DDIC.SurveyTypeLink_id
                    left join v_SurveyType ST on ST.SurveyType_id = STL.SurveyType_id
                where
                    DDIC.EvnPLDisp_id = DopDispSecond.EvnPLDispDop13_id
                    and ST.SurveyType_Code = 48
                limit 1
            ) DDICDataSecond on true
            LEFT JOIN LATERAL (
                select EvnPLDispDop13_id, Lpu_id
                from v_EvnPLDispDop13
                where Person_id = EPLDD13.Person_id
                    and date_part('year', EvnPLDispDop13_setDate) = 2022
                    and Lpu_id not in (7,7)
                    and COALESCE(DispClass_id,1) = 1
                limit 1
            ) EPLDD13AL on true
            left join v_Lpu lpu on lpu.Lpu_id = COALESCE(EPLDD13.Lpu_id, EPLDD13AL.Lpu_id)
        
                 WHERE 
                (1=1)
                
                
                        ORDER BY
                            
                
            EPLDD13.Person_SurName,
            EPLDD13.Person_FirName,
            EPLDD13.Person_SecName
        
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
            
            
