
                
                
                with  PC as (
                        SELECT DISTINCT PC.personcard_id, PC.person_id
                        FROM v_PersonCard PC
                            
                        WHERE
                            (PC.personcard_enddate is null or PC.personcard_enddate > '2023-07-20 17:37:09'::timestamp)
                             and PC.Lpu_id = '2703' and PC.LpuAttachType_id = '1'
                        ), 
            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,
                        EvnPLDispDop13.EvnPLDispDop13_IsSigned,
                        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
                    
                inner join v_EvnPLDispDop13 EvnPLDispDop13 on
                    PS.Person_id = EvnPLDispDop13.Person_id and
                    EvnPLDispDop13.Lpu_id in (2703,2703) and
                    (EvnPLDispDop13.DispClass_id = 1 or EvnPLDispDop13.DispClass_id is null) and
                    EvnPLDispDop13.EvnPLDispDop13_consDT>= '2023-01-01'  and EvnPLDispDop13.EvnPLDispDop13_disDt >= cast('2023-01-01' as timestamp)  and EvnPLDispDop13.EvnPLDispDop13_disDt <= cast('2023-06-30' as timestamp)  and COALESCE(EvnPLDispDop13.EvnPLDispDop13_IsEndStage, 1) = '2' 
            
                where
                    (1=1) and (PS.Person_deadDT >= '2023-01-01'::timestamp OR PS.Person_deadDT IS NULL)
                and (exists (
                    select msf1.EvnVizitDispDop_id
                    from v_EvnVizitDispDop msf1
                    
                        left join v_LpuSection ls1 on ls1.LpuSection_id = msf1.LpuSection_id
                    
                    where msf1.EvnVizitDispDop_pid = EvnPLDispDop13.EvnPLDispDop13_id
                     and ls1.LpuBuilding_id = '110101000000372'
                    
                    
                    limit 1
                ) or exists (
                    select msf2.EvnUslugaDispDop_id
                    from v_EvnUslugaDispDop msf2
                    
                        left join v_LpuSection ls3 on ls3.LpuSection_id = msf2.LpuSection_uid
                    
                    where msf2.EvnUslugaDispDop_pid = EvnPLDispDop13.EvnPLDispDop13_id
                     and ls3.LpuBuilding_id = '110101000000372'
                    
                    
                    limit 1
                ))
             and (
            (
                
                    (
                        -- Проверка по возрасту.
                        (
                            -- От 18 до 39 (младше 40 лет).
                            PS.Person_BirthDay BETWEEN '2023-12-31'::TIMESTAMP-interval '1 year' * 39 and '2023-12-31'::TIMESTAMP-interval '1 year' * 18
                            -- нет карты диспансеризации за два предыдущих года.
                            AND not exists (
                                SELECT EvnPLDispDop13_id
                                FROM v_EvnPLDispDop13
                                WHERE
                                    EvnPLDispDop13_disDT BETWEEN '2021-01-01'::timestamp AND '2022-01-01'::timestamp
                                    AND Person_id = PS.Person_id AND DispClass_id in (1, 2)
                                LIMIT 1
                            )
                            -- нет карты профосмотра в указанному году.
                            AND not exists (
                                SELECT EvnPLDispProf_id
                                FROM v_EvnPLDispProf
                                WHERE
                                EvnPLDispProf_consDT >= '2023-01-01'::timestamp
                                    AND EvnPLDispProf_consDT < '2024-01-01'::timestamp
                                    AND Person_id = PS.Person_id
                                LIMIT 1
                            )
                        )
                        OR (
                            -- ДВН проводится ежегодно.
                            -- Старше 40 лет включительно.
                            PS.Person_BirthDay <= '2023-12-31'::TIMESTAMP-interval '1 year' * 39
                        )
                    )
                
            )
        )
            )
        
                
                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",
            case when EPLDD13.EvnPLDispDop13_IsEndStage  = 2 then 'ДА' else 'НЕТ' end as "EvnPLDispDop13_IsEndStage",
            case when EPLDD13.EvnPLDispDop13_isMobile = 2 then 'ДА' else 'НЕТ' end as "EvnPLDispDop13_IsMobile",
            case when DopDispSecond.EvnPLDispDop13_IsEndStage  = 2 then 'ДА' else 'НЕТ' end 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 = '2703' then 0
                when EPLDD13.Lpu_id in (2703,2703) 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",
            EPLDD13.EvnPLDispDop13_IsSigned as "EvnPLDispDop13_IsSigned",
            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 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 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 epld13a.EvnPLDispDop13_id, epld13a.Lpu_id
                from v_EvnPLDispDop13 epld13a
                where Person_id = EPLDD13.Person_id
                    and epld13a.EvnPLDispDop13_setDt >= '2023-01-01'
                    and epld13a.Lpu_id not in (2703,2703)
                    and (epld13a.DispClass_id = 1 or epld13a.DispClass_id is null)
                limit 1
            ) EPLDD13AL on true
        
                 WHERE 
                (1=1)
                
                
                        ORDER BY
                            
                
            EPLDD13.Person_SurName,
            EPLDD13.Person_FirName,
            EPLDD13.Person_SecName
        
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
            
            
