
                
                
            with cte as (
                select
                    dbo.tzGetDate() as date,
                    dateadd('month', -3, dbo.tzGetDate()) as date3m
                limit 1
            ),
            e as (
                SELECT
                    EPL.EvnPL_id as Evn_id,
                    EPL.EvnPL_setDate as Evn_setDate,
                    EPL.EvnPL_disDate as Evn_disDate,
                    EPL.Diag_id,
                    EPL.Person_id,
                    EPL.Lpu_id,
                    'Амбулаторный' as EvnType,
                    EVPL.MedStaffFact_id,
                    EPL.EvnPL_NumCard as Evn_NumCard,
                    RC.ResultClass_Name as EvnResult
                FROM
                    v_EvnPL EPL
                    inner join v_MorbusDiag MD  on MD.MorbusType_id = 2 and MD.Diag_id = EPL.Diag_id
                    left join v_PersonPregnancy PP  on PP.Evn_id = EPL.EvnPL_id
                    left join lateral (select * from v_EvnVizitPL  where EvnVizitPL_pid = EPL.EvnPL_id and EvnVizitPL_Index = EvnVizitPL_Count - 1 limit 1) EVPL on true
                    left join v_ResultClass RC  on RC.ResultClass_id = EPL.ResultClass_id
                WHERE
                    EPL.EvnPL_setDate >= (select date3m from cte)
                    and PP.PersonPregnancy_id is null
                    and EPL.Diag_id not in (1944,1946,11071,11072,11073,11074,11075,11076,11077,11078,11079,11089,11090,11091)

                union all

                SELECT
                    EPS.EvnPS_id as Evn_id,
                    EPS.EvnPS_setDate as Evn_setDate,
                    EPS.EvnPS_disDate as Evn_disDate,
                    EPS.Diag_id,
                    EPS.Person_id,
                    EPS.Lpu_id,
                    'Стационарный' as EvnType,
                    ES.MedStaffFact_id,
                    EPS.EvnPS_NumCard as Evn_NumCard,
                    LT.LeaveType_Name as EvnResult
                FROM
                    v_EvnPS EPS
                    inner join v_MorbusDiag MD  on MD.MorbusType_id = 2 and MD.Diag_id = EPS.Diag_id
                    left join v_PersonPregnancy PP  on PP.Evn_id = EPS.EvnPS_id
                    left join lateral (select * from v_EvnSection  where EvnSection_pid = EPS.EvnPS_id and EvnSection_Index = EvnSection_Count - 1 limit 1) ES on true
                    left join v_LeaveType LT  on LT.LeaveType_id = EPS.LeaveType_id
                WHERE
                    EPS.EvnPS_setDate >= (select date3m from cte)
                    and PP.PersonPregnancy_id is null
                    and EPS.Diag_id not in (1944,1946,11071,11072,11073,11074,11075,11076,11077,11078,11079,11089,11090,11091)
            )
            
                SELECT 
                e.Evn_id as "Evn_id",
                e.EvnType as "EvnType",
                to_char(E.Evn_setDate, 'dd.mm.yyyy') as "Evn_setDate",
                to_char(E.Evn_disDate, 'dd.mm.yyyy') as "Evn_disDate",
                L.Lpu_id as "Lpu_id",
                L.Lpu_Nick as "Lpu_Nick",
                D.Diag_id as "Diag_id",
                D.Diag_FullName as "Diag_FullName",
                PS.Person_id as "Person_id",
                PS.Server_id as "Server_id",
                PS.Person_SurName || ' ' || PS.Person_FirName || coalesce(' ' || PS.Person_SecName,'') as "Person_Fio",
                to_char(PS.Person_BirthDay, 'dd.mm.yyyy') as "Person_BirthDay",
                LpuAttach.Lpu_id as "LpuAttach_id",
                LpuAttach.Lpu_Nick as "LpuAttach_Nick",
                coalesce(PAddress.Address_Nick, PAddress.Address_Address) as "Person_PAddress",
                MSF.Person_Fio as "MedPersonal",
                case when exists(
                    select *
                    from v_PersonQuarantine PQ
                    where PQ.Person_id = PS.Person_id
                    and PQ.PersonQuarantine_endDT is null
                ) then 2 else 1 end as "PersonQuarantine_IsOn",
                e.Evn_NumCard as "Evn_NumCard",
                e.EvnResult as "EvnResult"
            
                FROM
                
                e
                left join v_PersonState PS on PS.Person_id = E.Person_id
                left join v_Lpu L on L.Lpu_id = E.Lpu_id
                left join v_PersonCard PC on PC.Person_id = E.Person_id and PC.LpuAttachType_id = 1
                left join v_Lpu_all LpuAttach on LpuAttach.Lpu_id = PC.Lpu_id
                left join v_Address PAddress on PAddress.Address_id = PS.PAddress_id
                left join v_Diag D  on D.Diag_id = e.Diag_id
                left join v_MedStaffFact MSF  on MSF.MedStaffFact_id = e.MedStaffFact_id
             WHERE 
                (1=1)
                and not exists (
                    select 1 from v_PersonRegister PR
                    where PR.Person_id = E.Person_id
                        and PR.PersonRegisterType_id = 2 -- общерегиональный
                        and coalesce(PR.PersonRegister_disDate, (select date from cte)) >= E.Evn_setDate
                    limit 1
                )
                and exists (
                    select 1 from v_PersonCard PCG
                        inner join v_LpuRegion LR on LR.LpuRegion_id = PCG.LpuRegion_id
                        inner join v_MedStaffRegion msr on msr.LpuRegion_id = lr.LpuRegion_id
                    where
                        PCG.LpuAttachType_id = 2 and
                        PCG.Person_id = E.Person_id and
                        PCG.Lpu_id = '2770' and
                        coalesce(msr.MedStaffRegion_endDate, (select date from cte)) >= (select date from cte) and
                        msr.MedPersonal_id = '520101000013534'
                    limit 1
                )
            
                
                        ORDER BY
                            
                E.Evn_disDate desc,
                E.Evn_setDate desc
            
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
