
                
                
            with curDT as (select localtimestamp as curDate)
            
                SELECT 
                ED.Person_id as "Person_id"
                ,et.ElectronicService_id as "ElectronicService_id"
                ,COALESCE(TT.TimetableType_id, ttf.TimeTableType_id) as "TimeTableType_id"
                ,COALESCE(cast(et.ElectronicTalon_Num as varchar), cast(ED.EvnDirection_TalonCode as varchar)) as "EvnDirection_TalonCode"
                ,eqi.ElectronicQueueInfo_Letter as "ElectronicQueueInfo_Letter"
                ,eqi.ElectronicQueueInfo_Code as "ElectronicQueueInfo_Code"
                ,ED.LpuUnit_did as "LpuUnit_did"
                --,COALESCE(MPD.Person_Fio, MPTT.Person_Fio) || ' / ' || COALESCE(MPD.Dolgnost_Name, MPTT.Dolgnost_Name) as "MedPersonalDid"
                ,CASE
                    WHEN ED.DirType_id in (1, 5) THEN NULL
                    ELSE COALESCE(MSFTT.Person_Fio, MPD.Person_Fio)
                        ||
                        CASE
                            WHEN ED.EvnStatus_id IN (12,13) THEN ''
                            ELSE ' / ' || COALESCE(MSFTTPM.PostMed_Name, MPD.Dolgnost_Name, '')
                        end
                END AS "MedPersonalDid"
                ,CASE
                    WHEN COALESCE(MSFTT.MedStaffFact_IsQueueOnFree, 1) = 1
                    THEN 0 ELSE 1
                END as "MedStaffFact_IsQueueOnFree"
                ,ES.EvnStatus_SysNick as "EvnStatus_SysNick"
                ,TT.recDate as "EvnDirection_RecDate"
                ,to_char(ED.EvnDirection_setDate, 'DD.MM.YYYY') as "EvnDirection_setDate"

                ,EUP.UslugaComplex_id as "UslugaComplex_id"
                ,EUP.UslugaComplex_Name as "UslugaComplex_Name"
                ,TT.EvnQueue_id as "EvnQueue_id"
                ,TT.TimetableMedService_id as "TimetableMedService_id"
                ,TT.TimetableResource_id as "TimetableResource_id"
                ,TT.TimetableGraf_id as "TimetableGraf_id"
                ,TT.TimetableStac_id as "TimetableStac_id"
                ,ED.EvnDirection_id as "EvnDirection_id"
                ,COALESCE(ED.EvnDirection_IsAuto, 1) as "EvnDirection_IsAuto"

                ,ED.EvnDirection_Num as "EvnDirection_Num"
                ,Lpu.Lpu_id as "Lpu_sid"
                ,LSP.LpuSectionProfile_Name as "LpuSectionProfile_Name"
                ,TT.recSort as "recSort"
                ,DLpu.Lpu_id as "Lpu_did"
                ,DT.DirType_id as "DirType_id"
                ,DT.DirType_Code as "DirType_Code"
                ,DT.DirType_Name as "DirType_Name"
                ,Diag.Diag_Code || ' ' || Diag.Diag_Name as "Diag_Name"
                ,1 as "accessType"
            ,to_char(PS.Person_BirthDay, 'DD.MM.YYYY') as "Person_BirthDay"

                ,ps.Person_SurName || COALESCE(' ' || ps.Person_FirName,'') || COALESCE(' ' || ps.Person_SecName,'') as "Person_Fio"

                ,coalesce(PI.PersonInfo_InternetPhone || ' (портал самозаписи); ','') || coalesce(PS.Person_Phone || ' (БД)','') as "Person_Phone"
                ,COALESCE(Uaddress.Address_Nick, UAddress.Address_Address) as "Address_Address"
                ,to_char(ED.EvnDirection_insDT, 'DD.MM.YYYY') as "EvnDirection_insDT"

                ,ES.EvnStatus_Name as "EvnStatus_Name"
                ,case when COALESCE(ttf.RecClass_id,1) = 2 then '' when COALESCE(slot.Slot_id,0) > 0 then '' else (Lpu.Lpu_Nick || COALESCE(' / ' || LS.LpuSection_Name, '') || COALESCE(' / ' || MP.Person_Fio, '')) end as "EvnDirection_From"

                ,COALESCE(DLpu.Lpu_Nick, Org.Org_Name) || COALESCE(' / ' || DLpuSection.LpuSection_Name, '') || COALESCE(' / ' || MS.MedService_Name, '') || COALESCE(' / ' || TT.MedPersonal_Fio, '')  as "EvnDirection_To"

                ,case when COALESCE(ttf.RecClass_id,1) = 2 then 'Запись через интернет' else rtrim(COALESCE(pmi.pmUser_Name,'')) end as "pmUser_Fio"

                ,ED.ARMType_id as "ARMType_id"
                ,RMT.RecMethodType_Name as "RecMethodType_Name"
                ,TT.pmUser_updID as "pmUser_updID"
                ,ED.MedPersonal_id as "MedPersonal_id"
                ,Ed.MedPersonal_did as "MedPersonal_did"
                ,case
                    when
                        TT.EvnQueue_id is not null
                    then
                        datediff('day', CAST(TT.EvnQueue_setDT as date), (select curDate from curDT))
                    else
                        null
                end as "EvnQueue_Days"

                
                ,case when ed.MedStaffFact_id is null
               		then
               			case when inQueueCounterProfile.counter > 0
               				then inQueueCounterProfile.counter
               				else null
                        end
                    else
                        case when (inQueueCounterMSF.counter + inQueueCounterProfile.counter) > 0
                            then (inQueueCounterMSF.counter + inQueueCounterProfile.counter)
                            else null
                        end
               	end as "inQueueCounter"
            
            
                FROM
                
                v_EvnDirection_all ED

                inner join v_PersonState PS  on ED.Person_id = PS.Person_id
                

                inner join v_Lpu_all DLpu  on DLpu.Lpu_id = ED.Lpu_did  and DLpu.Lpu_id = '13030003'
                left join v_Org Org on Org.Org_id = ED.Org_oid

                
                INNER JOIN LATERAL (

                        (select
                            Diag.Diag_Code,
                            Diag.Diag_Name
                        from v_Diag Diag

                        /* фильтруем направления по диагнозам СЗЗ, если указан диагноз в направлении */
                        where ED.Diag_id is not null AND ED.Diag_id = Diag.Diag_id and coalesce(Diag.Diag_Code, '') not in('Z21','R75','Y58.0','Z20.1','R76.1','Z03.0') and coalesce(Diag.Diag_Code, '') not between 'B20.0' and 'B24' and coalesce(Diag.Diag_Code, '') not between 'F20.0' and 'F29' and coalesce(Diag.Diag_Code, '') not between 'A50.0' and 'A64' and coalesce(Diag.Diag_Code, '') not between 'F10.0' and 'F19.9' and coalesce(Diag.Diag_Code, '') not between 'A15.0' and 'A19.9' limit 1)
                        UNION ALL
                        (select
                            '' as Diag_Code,
                            '' as Diag_Name
                        where ED.Diag_id is null)
                ) Diag on true
                INNER JOIN LATERAL (
                    select * from (
                        (select
                            CAST(null as bigint) as EvnQueue_id,
                            ED.LpuSectionProfile_id as LpuSectionProfile_did,
                            Timetable.TimetableType_id as TimetableType_id,
                            CAST(null as bigint) as TimetableGraf_id,
                            null as TimetableMedService_id,
                            null as TimetableResource_id,
                            Timetable.TimetableStac_id::text as TimetableStac_id,
                            null as EvnQueue_setDT,
                            Timetable.pmUser_insID,
                            Timetable.pmUser_updID,
                            CAST(null as bigint) as MedStaffFact_id,
                            to_char(Timetable.TimetableStac_setDate, 'DD.MM.YYYY') as recDate,
                            null as recSort,
                            null as MedPersonal_Fio
                        from
                            v_TimetableStac_lite Timetable
                        where
                            ED.DirType_id in (1,2,4,5,6)
                            and Timetable.EvnDirection_id = ED.EvnDirection_id  and cast(TimeTableStac_setDate as date) >= cast('2023-06-01' as date) and cast(TimeTableStac_setDate as date) <= cast('2023-07-28' as date)
                        limit 1)
                        UNION ALL
                        (select
                            null as EvnQueue_id,
                            ED.LpuSectionProfile_id as LpuSectionProfile_did,
                            Timetable.TimetableType_id as TimetableType_id,
                            CAST(null as bigint) as TimetableGraf_id,
                            Timetable.TimetableMedService_id::text as TimetableMedService_id,
                            null as TimetableResource_id,
                            null as TimetableStac_id,
                            null as EvnQueue_setDT,
                            Timetable.pmUser_insID,
                            Timetable.pmUser_updID,
                            CAST(null as bigint) as MedStaffFact_id,
                            to_char(Timetable.TimetableMedService_begTime, 'DD.MM.YYYY HH24:MI') as recDate,
                            Timetable.TimetableMedService_begTime as recSort,
                            null as MedPersonal_Fio
                        from
                            v_TimetableMedService_lite Timetable
                            left join v_UslugaComplexMedService ucms on ucms.UslugaComplexMedService_id = Timetable.UslugaComplexMedService_id
                        where
                            ED.DirType_id in (2,3,6,10,11,15,17,25,32,35,39)
                            and Timetable.EvnDirection_id = ED.EvnDirection_id 
                        limit 1)
                        UNION ALL
                        (select
                            null as EvnQueue_id,
                            ED.LpuSectionProfile_id as LpuSectionProfile_did,
                            Timetable.TimetableType_id as TimetableType_id,
                            CAST(null as bigint) as TimetableGraf_id,
                            null as TimetableMedService_id,
                            Timetable.TimetableResource_id::text as TimetableResource_id,
                            null as TimetableStac_id,
                            null as EvnQueue_setDT,
                            Timetable.pmUser_insID,
                            Timetable.pmUser_updID,
                            CAST(null as bigint) as MedStaffFact_id,
                            to_char(Timetable.TimetableResource_begTime, 'DD.MM.YYYY HH24:MI') as recDate,
                            Timetable.TimetableResource_begTime as recSort,
                            null as MedPersonal_Fio
                        from
                            v_TimetableResource_lite Timetable
                            left join v_Resource r on r.Resource_id = Timetable.Resource_id
                        where
                            ED.DirType_id in (10)
                            and Timetable.EvnDirection_id = ED.EvnDirection_id 
                        limit 1)
                        UNION ALL
                        (select
                            null as EvnQueue_id,
                            ED.LpuSectionProfile_id as LpuSectionProfile_did,
                            Timetable.TimetableType_id as TimetableType_id,
                            Timetable.TimetableGraf_id as TimetableGraf_id,
                            null as TimetableMedService_id,
                            null as TimetableResource_id,
                            null as TimetableStac_id,
                            null as EvnQueue_setDT,
                            Timetable.pmUser_insID,
                            Timetable.pmUser_updID,
                            Timetable.MedStaffFact_id,
                            to_char(coalesce(Timetable.TimetableGraf_begTime, Timetable.TimetableGraf_factTime), 'DD.MM.YYYY HH24:MI') as recDate,
                            coalesce(Timetable.TimetableGraf_begTime, Timetable.TimetableGraf_factTime) as recSort,
                            MSFT.Person_Fio as MedPersonal_Fio
                        from
                            v_TimetableGraf_lite Timetable
                            left join v_MedStaffFact MSFT on Timetable.MedStaffFact_id = MSFT.MedStaffFact_id
                        where
                            ED.DirType_id in (2,3,4,6,16,17)
                            and (
                                (Timetable.TimeTableType_id <> 14 AND Timetable.EvnDirection_id = ED.EvnDirection_id)
                                OR
                                (Timetable.EvnDirection_id IS NULL AND Timetable.TimetableGraf_id = ED.TimetableGraf_id)
                            )
                             and Timetable.TimeTableGraf_factTime is null
                        limit 1)
                        UNION ALL
                        (select
                            EQ.EvnQueue_id,
                            EQ.LpuSectionProfile_did,
                            null as TimetableType_id,
                            CAST(null as bigint) as TimetableGraf_id,
                            null as TimetableMedService_id,
                            null as TimetableResource_id,
                            null as TimetableStac_id,
                            CAST(EQ.EvnQueue_setDT as varchar),
                            EQ.pmUser_insID,
                            EQ.pmUser_updID,
                            EQ.MedStaffFact_did as MedStaffFact_id,
                            null as recDate,
                            EQ.EvnQueue_setDT as recSort,
                            null as MedPersonal_Fio
                        from
                            v_EvnQueue EQ
                        where EQ.EvnDirection_id = ED.EvnDirection_id
                            and EQ.EvnQueue_IsArchived is null  and cast(ED.EvnDirection_setDate as date) >= cast('2023-06-01' as date) and cast(ED.EvnDirection_setDate as date) <= cast('2023-07-28' as date)
                and (EQ.EvnQueue_recDT is null or EQ.pmUser_recID = 1)
                and EQ.EvnQueue_failDT is null
                and eq.EvnQueueStatus_id is null
            
                        limit 1)
                        UNION ALL
                        (select
                            null as EvnQueue_id,
                            ED.LpuSectionProfile_id as LpuSectionProfile_did,
                            null as TimetableType_id,
                            CAST(null as bigint) as TimetableGraf_id,
                            null as TimetableMedService_id,
                            null as TimetableResource_id,
                            null as TimetableStac_id,
                            null as EvnQueue_setDT,
                            ED.pmUser_insID,
                            ED.pmUser_updID,
                            CAST(null as bigint) as MedStaffFact_id,
                            null as recDate,
                            null as recSort,
                            null as MedPersonal_Fio
                        where ED.DirType_id = 5 and coalesce(ED.EvnDirection_IsAuto,1) = 1 
                        limit 1)
                        
                    ) tt
                    limit 1
                ) TT on true
                left join v_MedStaffFact MSF  on ed.MedStaffFact_id = MSF.MedStaffFact_id

                left join v_MedPersonal MPD on MPD.MedPersonal_id = ED.MedPersonal_did and MPD.Lpu_id = ED.Lpu_did
                left join RecMethodType RMT on RMT.RecMethodType_id = ED.RecMethodType_id
                left join v_PersonInfo PI on PI.Person_id = ED.Person_id
                left join v_Lpu_all Lpu  on Lpu.Lpu_id = ED.Lpu_sid  and coalesce(Lpu.Lpu_id, 0) not in(180101000000016,180101000000031,180101000000033)

                left join v_MedPersonal MP  on ED.MedPersonal_id = MP.MedPersonal_id and MP.Lpu_id = Lpu.Lpu_id
                left join v_MedStaffFact MSFTT on MSFTT.MedStaffFact_id = TT.MedStaffFact_id
                LEFT JOIN v_PostMed MSFTTPM  on MSFTTPM.PostMed_id = MSFTT.Post_id
                left join v_MedPersonal MPTT on MPTT.MedPersonal_id = MSFTT.MedPersonal_id and MPTT.Lpu_id = Lpu.Lpu_id
                left join v_LpuSection LS  on ED.LpuSection_id = LS.LpuSection_id
                left join v_Address_all UAddress  on UAddress.Address_id = PS.UAddress_id
                left join v_MedService MS  on MS.MedService_id = ED.MedService_id
                left join v_LpuSection DLpuSection  on DLpuSection.LpuSection_id = ED.LpuSection_did
                left join v_DirType DT  on DT.DirType_id = ED.DirType_id
                left join v_EvnStatus ES  on ES.EvnStatus_id = ED.EvnStatus_id
                LEFT JOIN LATERAL (
                    Select * from v_ElectronicTalon et  where et.EvnDirection_id = ed.EvnDirection_id limit 1
                ) et on true
                left join v_ElectronicQueueInfo eqi on et.ElectronicQueueInfo_id = eqi.ElectronicQueueInfo_id
                LEFT JOIN LATERAL (
                    Select
                        EUP.UslugaComplex_id,
                        uc.UslugaComplex_Name
                    from
                        v_EvnUslugaPar EUP
                        left join v_UslugaComplex uc  on uc.UslugaComplex_id = EUP.UslugaComplex_id
                    where
                        ED.DirType_id in (10,11,15)
                        and EUP.EvnDirection_id = ED.EvnDirection_id
                        and ED.Person_id = EUP.Person_id
                        limit 1
                ) EUP on true
                left join v_pmUser pmi  on pmi.PMUser_id=ED.pmUser_insID
                
                left join lateral (
                    select
                        q.EvnQueue_id,
                        q.EvnQueue_insDT
                    from v_EvnQueue q
                    where q.EvnQueue_id = TT.EvnQueue_id
                    limit 1
                ) q on true
                left join lateral (
                    select count(q_inner.EvnQueue_id) as counter
                        from v_EvnQueue q_inner
                        inner join v_EvnDirection_all ed_inner on ed_inner.EvnDirection_id = q_inner.EvnDirection_id
                        where q_inner.EvnQueueStatus_id in (1,2)
                            and q_inner.EvnQueue_failDT is null
                            and q_inner.EvnQueue_insDT <= q.EvnQueue_insDT
                            and q_inner.RecMethodType_id = 1
                            and q_inner.LpuSectionProfile_did = TT.LpuSectionProfile_did
                            and ed_inner.MedStaffFact_id = ED.MedStaffFact_id
                            and q_inner.Lpu_id = ED.Lpu_sid
                            and ED.RecMethodType_id = 1
                ) inQueueCounterMSF on true
                left join lateral (
                    select count(q_inner.EvnQueue_id) as counter
                        from v_EvnQueue q_inner
                        inner join v_EvnDirection_all ed_inner on ed_inner.EvnDirection_id = q_inner.EvnDirection_id
                        where q_inner.EvnQueueStatus_id in (1,2)
                            and q_inner.EvnQueue_failDT is null
                            and q_inner.EvnQueue_insDT <= q.EvnQueue_insDT
                            and q_inner.RecMethodType_id = 1
                            and q_inner.LpuSectionProfile_did = TT.LpuSectionProfile_did
                            and ed_inner.MedStaffFact_id is null
                            and q_inner.Lpu_id = ED.Lpu_sid
                            and ED.RecMethodType_id = 1
                ) inQueueCounterProfile on true
            
                left join v_LpuSectionProfile LSP  on TT.LpuSectionProfile_did = LSP.LpuSectionProfile_id
                left join v_TimeTableGraf_lite ttf  on ttf.TimeTableGraf_id = TT.TimetableGraf_id
                left join fer.v_Slot slot  on slot.TimeTableGraf_id = TT.TimetableGraf_id
             WHERE 
            (1=1)
            and ED.DirType_id not in (24, 37)
             and COALESCE(ED.DirType_id,1) not in (7, 18, 19, 20) and ED.EvnStatus_id='10' and ED.Lpu_did = '13030003' and cast(ED.EvnDirection_setDate as date) >= cast('2023-06-01' as date) and cast(ED.EvnDirection_setDate as date) <= cast('2023-07-28' as date)
            
                
                        ORDER BY
                            
            TT.recSort desc
            
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
