
                
            WITH cte AS (
                SELECT dbo.tzGetDate() AS now
            )
            
                
                SELECT 
                        EPS.EvnPS_id as "EvnPS_id",
                        EPS.Person_id as "Person_id",
                        EPS.PersonEvn_id as "PersonEvn_id",
                        EPS.Server_id as "Server_id",
                        EPSLastES.EvnSection_id as "EvnSection_id",
                        COALESCE(EPS.EvnPS_IsTransit, 1) as "EvnPS_IsTransit",
                        RTRIM(EPS.EvnPS_NumCard) as "EvnPS_NumCard",
                        to_char(EPS.EvnPS_setDate, 'dd.mm.yyyy') as "EvnPS_setDate",
                        to_char(EPS.EvnPS_disDate, 'dd.mm.yyyy') as "EvnPS_disDate",
                        COALESCE(LP.Lpu_Name, '') as "Lpu_Name",
                        COALESCE(LP.Lpu_Nick, '') as "Lpu_Nick",
                        COALESCE(LStmp.LpuSection_Name, '') as "LpuSection_Name",
                        COALESCE(p.LpuSectionProfile_Name, '') as "ProfilHospital",
                        COALESCE(Dtmp.Diag_FullName, DP.Diag_FullName) as "Diag_Name",
                        COALESCE(Prehos.PrehospType_Name, '') as "PrehospType_Name",
                        -- поскольку в одном КВС не может быть движений по круглосуточным и дневным стационарам вместе (поскольку это делается через перевод и создание новой карты)
                        -- то подсчет количества койкодней реализуем так (с) Night, 2011-06-22

                    case when LpuUnitType.LpuUnitType_SysNick = 'stac'
                    then extract(DAY FROM (EPS.EvnPS_setDate - EPS.EvnPS_disDate)) + abs(sign(extract(day from (EPS.EvnPS_setDate - EPS.EvnPS_disDate))) - 1) -- круглосуточные
                    else (extract(day from (EPS.EvnPS_setDate - EPS.EvnPS_disDate)) + 1) -- дневные
                    end as "EvnPS_KoikoDni",

                        --CASE WHEN PS.Server_pid = 0 THEN 'true' ELSE 'false' END as Person_IsBDZ,
                        dbfpayt.PayType_Name as "PayType_Name", --Вид оплаты
                        CASE
                            WHEN LT.LeaveType_Name is not null THEN LT.LeaveType_Name
                            WHEN EPS.PrehospWaifRefuseCause_id > 0 THEN pwrc.PrehospWaifRefuseCause_Name
                            ELSE ''
                        END as "LeaveType_Name",
                        LT.LeaveType_Code as "LeaveType_Code",
                        CASE WHEN DeathSvid.DeathSvid_id is null then 'false'
                        else 'true'
                        end as "DeadSvid",
                        EPS.PrehospWaifRefuseCause_id as "PrehospWaifRefuseCause_id",
                         COALESCE(ksgkpg.Mes_Code, '') || COALESCE(ksgkpg.Mes_Name, '') as "EvnSection_KSG",
                        COALESCE(ksgkpg.Mes_Code, '') ||  COALESCE(ksgkpg.Mes_Name, '') as "EvnSection_KSGKPG",
                         kpg.Mes_Code as "EvnSection_KPG",
                        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"
                            ,RTRIM(PS.Person_SurName) as "Person_Surname"
                            ,RTRIM(PS.Person_FirName) as "Person_Firname"
                            ,RTRIM(PS.Person_SecName) as "Person_Secname"
                            ,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', PS.Person_Birthday, dbo.tzGetDate()) <= 31
                    then '' || datediff('day', PS.Person_Birthday, dbo.tzGetDate()) ||
                    case
                        when
                            datediff('day', PS.Person_Birthday, dbo.tzGetDate()) >= 2
                            and datediff('day', PS.Person_Birthday, dbo.tzGetDate()) <= 4
                            or
                            datediff('day', PS.Person_Birthday, dbo.tzGetDate()) >= 22
                            and datediff('day', PS.Person_Birthday, dbo.tzGetDate()) + 1 <= 24
                        then ' дня'
                        when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) in (1, 21, 31)
                        then ' день'
                        else ' дней'
                    end
                when datediff('day', PS.Person_Birthday, dbo.tzGetDate()) + 1 > 31 and dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) < 12
                    then '' || dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate())::varchar || ' мес.'

                when dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) >= 12 and dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) < 36
                    then '' || dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar ||
                        case
                            when dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) = 2 then ' года '
                            else ' год '
                        end ||
                        case
                            when (dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) - (dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) * 12)) < 1 then ''
                            else (dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate()) - (dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) * 12))::varchar || ' мес.'
                        end

                when dbo.Age2(PS.Person_Birthday, dbo.tzGetDate()) >= 3
                    then '' || dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar ||
                        case
                    when
                        right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,2)::integer >= 11
                        and right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,2)::integer <= 14
                        or right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer >= 5
                        and right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer <= 9
                        or right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer = 0
                    then ' лет'
                    when
                        right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer >= 2
                        and right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer <= 4
                    then ' года'
                    when right(dbo.Age2(PS.Person_Birthday, dbo.tzGetDate())::varchar,1)::integer = 1
                    then ' год'
                end
                    else '' || dbo.Age_newborn(PS.Person_Birthday, dbo.tzGetDate())::varchar || ' мес.'
            end
            as "Person_Age"
        
                            ,COALESCE(to_char(DeathSvid.DeathSvid_DeathDate,'dd.mm.yyyy'),to_char(EvnDie.EvnDie_setDate, 'dd.mm.yyyy'),to_char(PS.Person_DeadDT, 'dd.mm.yyyy'),'') as "Person_deadDT"
                        ,case when SES1.ServiceEvnStatus_SysNick in ('sendegis','loadegis') then 'true' else 'false' end as "fedservice_iemk"
                , case when COALESCE(EPS.EvnPS_IsArchive, 1) = 1 then 0 else 1 end as "archiveRecord"


                
                FROM
                
                         v_EvnSection EPSLastES
                         inner join v_EvnPS EPS on EPS.EvnSection_lastid = EPSLastES.EvnSection_id
                            and EPS.EvnPS_id = EPSLastES.EvnSection_pid
                         inner join v_PersonState PS on EPS.Person_id = PS.Person_id --and EPS.Lpu_id = '39'
                         --inner join v_EvnSection as ESEC on ESEC.EvnSection_pid = EPS.EvnPS_id and ESEC.Lpu_id = '39'
                                left join v_EvnCostPrint ecp  on ecp.Evn_id = EPS.EvnPS_id
                                left join v_Lpu LP on EPS.Lpu_id = LP.Lpu_id
                                left join v_PrehospType Prehos on EPS.PrehospType_id = Prehos.PrehospType_id
                                left join LpuSection LStmp on LStmp.LpuSection_id = EPSLastES.LpuSection_id
                                left join v_LpuSectionProfile p on p.LpuSectionProfile_id = EPSLastES.LpuSectionProfile_id
                                left join v_Diag Dtmp on Dtmp.Diag_id = EPSLastES.Diag_id
                                left join LeaveType LT on LT.LeaveType_id = COALESCE(EPSLastES.LeaveType_id, EPSLastES.LeaveType_prmid)
                                left JOIN LATERAL(
                                    select MedPersonal_id
                                    from v_MedPersonal
                                    where MedPersonal_id = EPSLastES.MedPersonal_id
                                        and Lpu_id = '39'
                                    order by
                                        case when Lpu_id = '39' then 1 else 2 end
                                        limit 1
                                ) MP on true
                                LEFT JOIN LATERAL (
                                    select *
                                    from v_PersonCard_all PCA
                                    where PCA.Person_id = EPS.Person_id
                                    limit 1
                                ) PersonCardAll on true
                                LEFT JOIN LATERAL (
                                    select LpuRegion_id
                                    from v_MedStaffRegion MSR
                                    where PersonCardAll.LpuRegion_id = MSR.LpuRegion_id
                                    limit 1
                                ) MedStaffReg on true
                                left join v_Diag DP on DP.Diag_id = EPS.Diag_pid
                                left join LpuSection LS on LS.LpuSection_id = EPS.LpuSection_id
                                left join v_PrehospWaifRefuseCause pwrc on pwrc.PrehospWaifRefuseCause_id = EPS.PrehospWaifRefuseCause_id
                                left join LpuUnit  on LpuUnit.LpuUnit_id = LStmp.LpuUnit_id
                                left join LpuUnitType  on LpuUnitType.LpuUnitType_id = LpuUnit.LpuUnitType_id
                                left join PayType dbfpayt  on dbfpayt.PayType_id = EPS.PayType_id
                                left join v_Polis pls on pls.Polis_id = ps.Polis_id
                                left JOIN LATERAL (
                                    select EvnDie_setDate
                                    from v_EvnDie
                                    where Person_id = PS.Person_id
                                    order by EvnDie_setDate
                                    limit 1
                                ) EvnDie on true
                                left JOIN LATERAL(
                                    SELECT DeathSvid_id,DeathSvid_DeathDate
                                    FROM dbo.v_DeathSvid
                                    WHERE Person_id = PS.Person_id and (DeathSvid_IsBad is null or DeathSvid_IsBad = 1)
                                    limit 1
                                ) DeathSvid on true
                                left join v_MesTariff spmt on EPSLastES.MesTariff_id = spmt.MesTariff_id
                                left join v_MesOld as sksg on sksg.Mes_id = EPSLastES.Mes_sid
                                left join v_MesOld as ksg on ksg.Mes_id = case
                                    when spmt.Mes_id in (EPSLastES.Mes_sid, EPSLastES.Mes_tid) then spmt.Mes_id
                                    else COALESCE(EPSLastES.Mes_sid, EPSLastES.Mes_tid)
                                end
                                left join v_MesOld as ksgkpg on spmt.Mes_id = ksgkpg.Mes_id
                                left join v_MesOld as kpg on kpg.Mes_id = EPSLastES.Mes_kid
                                left join v_EvnReanimatPeriod ERP on ERP.EvnReanimatPeriod_rid = EPS.EvnPS_id
                                left JOIN LATERAL (
                                    select ESS.EvnSection_id
                                    from v_EvnSection ESS
                                    inner join v_Diag ESSDiag on ESSDiag.Diag_id = ESS.Diag_id
                                    where ESS.EvnSection_pid = EPS.EvnPS_id
                                        and ESS.Lpu_id = '39'
                                 and ((coalesce(ESSDiag.Diag_Code, '') in('H00.0')) or (coalesce(ESSDiag.Diag_Code, '') between 'B00.0' and 'B00.9'))
                                 limit 1) adf on true
                    left JOIN LATERAL (
                        select ServiceEvnStatus_id
                        from v_ServiceEvnHist
                        where Evn_id = EPS.EvnPS_id
                            and ServiceEvnList_id = 1
                        order by ServiceEvnHist_id desc
                        limit 1
                    ) SEH1 on true
                    left join v_ServiceEvnStatus SES1 on SES1.ServiceEvnStatus_id = SEH1.ServiceEvnStatus_id
                    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= '10101000006459'

                            AND d.Person_id = EPS.Person_id
                        LIMIT 1
                    ) PD on true
                    LEFT JOIN LATERAL (
                        SELECT PersonCard_id
                        FROM
                            v_PersonCard pCard
                            LEFT JOIN v_MedStaffRegion msRegion ON msRegion.LpuRegion_id = pCard.LpuRegion_id
                            LEFT 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 = '10101000006459'
                            AND pCard.LpuAttachType_id = 1

                            AND pCard.Person_id = EPS.Person_id
                        LIMIT 1
                    ) PC ON true
                 WHERE 
                    (1 = 1)
                    and COALESCE(EPS.EvnPS_IsArchive, 1) = 1
                    
                    and adf.EvnSection_id is null
                    and LT.LeaveType_fedid in (1, 7)
                    and coalesce(Dtmp.Diag_Code, '') not in('H00.0')
                    and coalesce(Dtmp.Diag_Code, '') not between 'B00.0' and 'B00.9'
                    
                    and (
                        PD.Person_id is not null
                        OR
                        PC.PersonCard_id is not null
                    )
                
                
                        ORDER BY
                            
                            EPS.EvnPS_id
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
