
            -- variables
            WITH cte AS (
                SELECT localtimestamp AS now
            )
            -- end variables

            SELECT
                eup.EvnUslugaPar_id AS "EvnUslugaPar_id",

                eup.Person_id AS "Person_id",
                eup.PersonEvn_id AS "PersonEvn_id",
                eup.Server_id AS "Server_id",

                -- ФИО

                LTRIM(RTRIM(CONCAT_WS(' ',
                    COALESCE(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",

                -- возраст
                
            case
                
                when datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) <= 31
                    then '' || datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) ||
                    case
                        when
                            datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) >= 2
                            and datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) <= 4
                            or
                            datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) >= 22
                            and datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) + 1 <= 24
                        then ' дня'
                        when datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) in (1, 21, 31)
                        then ' день'
                        else ' дней'
                    end
                when datediff('day', cast(ps.Person_Birthday as date), (SELECT now FROM cte)) + 1 > 31 and dbo.Age_newborn(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) < 12
                    then '' || dbo.Age_newborn(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar || ' мес.'

                when dbo.Age_newborn(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) >= 12 and dbo.Age_newborn(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) < 36
                    then '' || dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar ||
                        case
                            when dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) = 2 then ' года '
                            else ' год '
                        end ||
                        case
                            when (dbo.Age_newborn(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) - (dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) * 12)) < 1 then ''
                            else (dbo.Age_newborn(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) - (dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) * 12))::varchar || ' мес.'
                        end

                when dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte)) >= 3
                    then '' || dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar ||
                        case
                    when
                        right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,2)::integer >= 11
                        and right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,2)::integer <= 14
                        or right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,1)::integer >= 5
                        and right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,1)::integer <= 9
                        or right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,1)::integer = 0
                    then ' лет'
                    when
                        right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,1)::integer >= 2
                        and right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,1)::integer <= 4
                    then ' года'
                    when right(dbo.Age2(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar,1)::integer = 1
                    then ' год'
                end
                    else '' || dbo.Age_newborn(cast(ps.Person_Birthday as date), (SELECT now FROM cte))::varchar || ' мес.'
            end
            as "Person_Age"
        ,

                -- Дата выполнения услуги
                to_char(eup.EvnUslugaPar_setDate, 'DD.MM.YYYY') AS "EvnUslugaPar_setDate",

                -- Отделение, ЛПУ
                COALESCE(l.Lpu_Name, 'Не определено') AS "Lpu_Name",
                COALESCE(ls.LpuSection_Name, 'Не определено') AS "LpuSection_Name",

                -- врач
                COALESCE(mp.Person_Fio, 'Не определено') AS "MedPersonal_Fio",

                -- служба
                case when LMS.MedService_Name is null then FMS.MedService_Name else LMS.MedService_Name end as "MedService_Name",

                -- услуга
                COALESCE(uc.UslugaComplex_Code, '') AS "UslugaComplex_Code",
                COALESCE(uc.UslugaComplex_Name, 'Не определено') AS "UslugaComplex_Name",

                -- отклонение
                CASE
                    WHEN els.EvnLabSample_isOutNorm = 2 THEN 1
                    ELSE 0
                END AS "OutNorm",
                ut.results as "results"
            FROM
                v_EvnUslugaPar eup
                inner JOIN LATERAL (
                    SELECT PersonCard_id
                    FROM
                        v_PersonCard pCard
                        INNER JOIN v_MedStaffRegion msRegion ON msRegion.LpuRegion_id = pCard.LpuRegion_id
                        INNER JOIN v_MedStaffFact msFact ON msFact.MedStaffFact_id = msRegion.MedStaffFact_id AND msFact.Lpu_id = msRegion.Lpu_id
                    WHERE
                        (pCard.PersonCard_endDate IS NULL OR CAST(pCard.PersonCard_endDate AS timestamp) > (SELECT now FROM cte))
                        AND msFact.MedPersonal_id = '560101000032984'
                        AND pCard.LpuAttachType_id = 1

                        AND pCard.Person_id = eup.Person_id
                    LIMIT 1
                ) PC ON true

                INNER JOIN v_PersonState ps ON ps.Person_id = eup.Person_id

                -- Сулжба (Лаб.)
                LEFT JOIN v_EvnLabSample ELSa ON ELSa.EvnLabSample_id = eup.EvnLabSample_id
                LEFT JOIN v_EvnLabRequest ELR ON ELR.EvnLabRequest_id = ELSa.EvnLabRequest_id
                LEFT JOIN v_MedService LMS ON LMS.MedService_id = ELR.MedService_id

                -- Служба (Функ.)
                LEFT JOIN v_EvnFuncRequest EFR ON EFR.EvnFuncRequest_pid = eup.EvnDirection_id
                LEFT JOIN v_MedService FMS ON FMS.MedService_id = EFR.MedService_id

                -- Отделение, ЛПУ
                LEFT JOIN v_LpuSection ls ON ls.LpuSection_id = eup.LpuSection_uid
                LEFT JOIN v_Lpu l ON l.Lpu_id = ls.Lpu_id

                -- врач
                LEFT JOIN LATERAL (
                    SELECT Person_Fio FROM v_MedPersonal WHERE MedPersonal_id = eup.MedPersonal_id LIMIT 1
                ) mp ON true

                -- услуга
                LEFT JOIN v_UslugaComplex uc ON uc.UslugaComplex_id = eup.UslugaComplex_id

                -- отклонение
                left join lateral (
                    SELECT els.EvnLabSample_isOutNorm
                    FROM v_EvnLabRequestUslugaComplex elruc
                        inner join v_EvnLabSample els on elruc.EvnLabSample_id = els.EvnLabSample_id
                    WHERE elruc.EvnUslugaPar_id = eup.EvnUslugaPar_id
                    LIMIT 1
                ) els on true
                left join lateral (
                    select
                        string_agg(
                            ut.uslugatest_result || '@' ||
                            coalesce(uslugatest_resultqualitativenorms, '') || '@' ||
                            coalesce(uslugatest_resultlowercrit, '') || '@' ||
                            coalesce(uslugatest_resultuppercrit, ''), '§'
                        ) as results
                    from v_UslugaTest ut
                    where ut.UslugaTest_pid = eup.EvnUslugaPar_id
                    limit 1
                ) ut on true

                LEFT JOIN LATERAL (
                    SELECT
                        d.Person_id
                    FROM
                        v_PersonDisp d
                        INNER JOIN v_PersonDispHist dh ON dh.personDisp_id = d.personDisp_id
                    WHERE
                        (dh.PersonDispHist_endDate IS NULL OR CAST(dh.PersonDispHist_endDate AS timestamp) > (SELECT now FROM cte)) -- ДН еще не окончено

                        AND dh.MedPersonal_id= '560101000032984'

                        AND d.Person_id = eup.Person_id
                ) PD on true

            WHERE
                (1 = 1)
                
            and eup.EvnUslugaPar_setDT >= '2022-07-01 00:00:00'
            and eup.EvnUslugaPar_setDT <= '2023-07-25 00:00:00'
        
            ORDER BY
                -- eup.EvnUslugaPar_id
                EvnUslugaPar_setDT DESC
        
