
                
                
            with curDT as (select localtimestamp as curDate)
            
                SELECT 
                case when EvnPS.PrehospStatus_id = 4 then to_char(EvnPS.EvnPS_setDate, 'DD.MM.YYYY') else null end as "EvnPS_setDate",
                coalesce(LT.LeaveType_Name,PWRC.PrehospWaifRefuseCause_Name) as "LeaveType_Name",
                RTRIM(MedPersLSP.LpuSectionProfile_Name) as "MedPersonalProfile_Name",
                ED.EvnStatus_id as "EvnStatus_id",
                ED.EvnDirection_id as "EvnDirection_id",
                ED.PayType_id as "PayType_id",
                Lpu.Lpu_id as "Lpu_id",
                case when ED.EvnDirection_IsConfirmed = 2 then 'true' else 'false' end as "IsConfirmed",
                Lpu.Org_id as "Org_id",
                Lpu.Org_Nick as "Org_Nick",
                Lpu.Org_Name as "Org_Name",
                RTRIM(Lpu.Lpu_Nick) as "Lpu_Name",
                ED.EvnDirection_Num as "EvnDirection_Num",
                ED.DirType_id as "DirType_id",
                DT.DirType_Code as "DirType_Code",
                DFT.DirFailType_id as "DirFailType_id",
                DFT.DirFailType_Name as "DirFailType_Name",
                RTRIM(DT.DirType_Name) as "DirType_Name",
                ED.LpuSectionProfile_id as "LpuSectionProfile_id",
                RTRIM(LSP.LpuSectionProfile_Name) as "LpuSectionProfile_Name",
                RTRIM(LS.LpuSection_Name) as "LpuSection_Name",
                
            PS.Person_Birthday as "Person_Birthday",
            case
                when (PS.Person_SurName is null and PS.Person_FirName is null and PS.Person_SecName is null)
                then 'Неизвестный'
                else
                    coalesce(upper(left(PS.Person_SurName, 1)) || lower(overlay(PS.Person_SurName placing '' from 1 for 1)),'') ||' '||
                    coalesce(upper(left(PS.Person_FirName, 1)) || lower(overlay(PS.Person_FirName placing '' from 1 for 1)),'') ||' '||
                    coalesce(upper(left(PS.Person_SecName, 1)) || lower(overlay(PS.Person_SecName placing '' from 1 for 1)),'')
            end as "Person_Fio",
            coalesce(upper(left(PS.Person_SurName, 1)) || lower(overlay(PS.Person_SurName placing '' from 1 for 1)),'') as "Person_SurName",
            coalesce(upper(left(PS.Person_FirName, 1)) || lower(overlay(PS.Person_FirName placing '' from 1 for 1)),'') as "Person_FirName",
            coalesce(upper(left(PS.Person_SecName, 1)) || lower(overlay(PS.Person_SecName placing '' from 1 for 1)),'') as "Person_SecName",
            
            case
                
                when PS.diff <= 31
                    then '' || PS.diff ||
                    case
                        when
                            PS.diff >= 2
                            and PS.diff <= 4
                            or
                            PS.diff >= 22
                            and PS.diff + 1 <= 24
                        then ' дня'
                        when PS.diff in (1, 21, 31)
                        then ' день'
                        else ' дней'
                    end
                when PS.diff + 1 > 31 and PS.age_newborn < 12
                    then '' || PS.age_newborn::varchar || ' мес.'

                when PS.age_newborn >= 12 and PS.age_newborn < 36
                    then '' || PS.age2::varchar ||
                        case
                            when PS.age2 = 2 then ' года '
                            else ' год '
                        end ||
                        case
                            when (PS.age_newborn - (PS.age2 * 12)) < 1 then ''
                            else (PS.age_newborn - (PS.age2 * 12))::varchar || ' мес.'
                        end

                when PS.age2 >= 3
                    then '' || PS.age2::varchar ||
                        case
                    when
                        right(PS.age2::varchar,2)::integer >= 11
                        and right(PS.age2::varchar,2)::integer <= 14
                        or right(PS.age2::varchar,1)::integer >= 5
                        and right(PS.age2::varchar,1)::integer <= 9
                        or right(PS.age2::varchar,1)::integer = 0
                    then ' лет'
                    when
                        right(PS.age2::varchar,1)::integer >= 2
                        and right(PS.age2::varchar,1)::integer <= 4
                    then ' года'
                    when right(PS.age2::varchar,1)::integer = 1
                    then ' год'
                end
                    else '' || PS.age_newborn::varchar || ' мес.'
            end
            as "Person_Age"
        ,
            PS.Sex_id as "Sex_id",
        
                ED.Diag_id as "Diag_id",
                coalesce(Diag.Diag_Code,'') as "Diag_Code",
                coalesce(Diag.Diag_Name,'') as "Diag_sName",
                RTRIM(Diag.Diag_FullName) as "Diag_Name",
                ED.EvnDirection_Descr as "EvnDirection_Descr",
                to_char(ED.EvnDirection_desDT, 'DD.MM.YYYY') as "EvnDirection_desDT",
                ED.MedPersonal_id as "MedPersonal_id",
                ED.LpuSection_id as "LpuSection_id",
                MP.Person_Fin as "MedPersonal_Fio",
                coalesce(upper(left(MSF.Person_SurName, 1)) || lower(overlay(MSF.Person_SurName placing '' from 1 for 1)),'') ||' '||
                coalesce(upper(left(MSF.Person_FirName, 1)) || lower(overlay(MSF.Person_FirName placing '' from 1 for 1)),'') ||' '||
                coalesce(upper(left(MSF.Person_SecName, 1)) || lower(overlay(MSF.Person_SecName placing '' from 1 for 1)),'')
                    as "MedStaffFact_Fio",
                coalesce(upper(left(MP.Person_SurName, 1)) || lower(overlay(MP.Person_SurName placing '' from 1 for 1)),'') as "MedPersonal_SurName",
                coalesce(upper(left(MP.Person_FirName, 1)) || lower(overlay(MP.Person_FirName placing '' from 1 for 1)),'') as "MedPersonal_FirName",
                coalesce(upper(left(MP.Person_SecName, 1)) || lower(overlay(MP.Person_SecName placing '' from 1 for 1)),'') as "MedPersonal_SecName",
                ED.Person_id as "Person_id",
                ED.PersonEvn_id as "PersonEvn_id",
                ED.Server_id as "Server_id",
                to_char(ED.EvnDirection_setDT, 'DD.MM.YYYY') as "EvnDirection_setDate",
                left(cast(ED.EvnDirection_setDT as time)||'',5) as "EvnDirection_setTime",
                coalesce(to_char(TTS.TimetableStac_setDate, 'DD.MM.YYYY'),'Очередь') as "TimetableStac_setDate",
                coalesce(to_char(TTS.TimetableStac_setDate, 'DD.MM.YYYY HH24:MI'),'Очередь') as "TimetableStac_setDT",
                case
                    when
                        TTS.TimetableStac_setDate is not null
                    then
                        null
                    else
                        datediff('day', EQ.EvnQueue_setDT, (select curDate from curDT))
                end as "EvnQueue_Days",
                EQ.EvnQueue_id as "EvnQueue_id",
                TTS.TimetableStac_id as "TimetableStac_id",
                LSBP.LpuSectionBedProfile_id as "LpuSectionBedProfile_id",
                LSBP.LpuSectionBedProfile_Name as "LpuSectionBedProfile_Name",
                ED.PrehospDirect_id as "PrehospDirect_id",
                ED.Lpu_sid as "Lpu_did",
                ED.LpuSection_id as "LpuSection_did",
                ED.MedStaffFact_id as "MedStaffFact_did",
                edh.EvnDirectionHTM_id as "EvnDirectionHTM_id",
                thtm.TalonHTM_id as "TalonHTM_id",
                thtm.TalonHTM_IsSigned as "TalonHTM_IsSigned",
                to_char(ED.EvnDirection_setDT, 'dd.mm.yyyy hh24:mi') as "EvnDirection_setDT",
                case
                    when epvkSM.EvnPrescrVK_id is not null then 'true'
                    when epvkHTM.EvnPrescrVK_id is not null and epvkHTM.Lpu_id = '1101004' then 'true'
                    else 'false'
                end as "IsWaitingVK",
                CASE
                	when epvkHTM.Lpu_id = '1101004' or epvkSM.Lpu_id = '1101004' then evk.EvnVK_id
                	else NULL
                END as "EvnVK_id",
                CASE
                	when epvkHTM.EvnPrescrVK_id is not null then evk.EvnVK_id
                	else NULL
                END as "VkProtocol_id",
                CASE
                	when epvkHTM.EvnPrescrVK_id is not null then evk.CareStageType_id
                	else NULL
                END as "CareStageType_id",
                el.Evn_lid as "EvnDirectionVK_id",
                evkd.Lpu_did as "LpuVK_did",
                epvkSM.EvnPrescrVK_id as "SMEvnPrescrVK_id",
                epvkHTM.EvnPrescrVK_id as "HTMEvnPrescrVK_id",
                COALESCE(epvkHTMCount.CountVK, 0) as "EvnDirectionHTMCount",
                case
                    when epvkHTM.Lpu_id = '1101004' then epvkHTM.EvnPrescrVK_id
                    else NULL
                end as "HTMEvnPrescrVK_id",
                epvkHTM.EvnPrescrVK_id as "EvnPrescrVkTalon_id",
                edh.LpuUnitType_id as "LpuUnitType_did"
                
            ,case
                when coalesce(ED.EvnDirection_IsArchive, 1) = 2 or ED.EvnDirection_isInArchive = 2
                then 1
                else 0
            end as "archiveRecord"
        
                ,ParentEvn.Evn_id as "EvnDirection_pid"
                ,ParentEvnClass.EvnClass_SysNick as "ParentEvnClass_SysNick"
                ,RootEvn.Evn_id as "EvnDirection_rid"
                ,RootEvnClass.EvnClass_SysNick as "RootEvnClass_SysNick"
            
                
                
                FROM
                
                v_EvnDirection_all ED
                inner join lateral (
                    select * from v_lpu where Lpu_id = ED.Lpu_sid
                    union
                    select * from v_lpu where Lpu_id = ED.Lpu_id and ED.Lpu_sid is null
                ) as lpu on true
                inner join v_DirType DT on DT.DirType_id = ED.DirType_id
                inner join lateral (
                    select
                        PS.Person_id,
                        PS.Sex_id,
                        dbo.Age_newborn(PS.Person_Birthday, (select curDate from curDT)) as age_newborn,
                        dbo.Age2(PS.Person_Birthday, (select curDate from curDT)) as age2,
                        datediff('day', PS.Person_Birthday, (select curDate from curDT)) as diff,
                        PS.Person_SurName,
                        PS.Person_SecName,
                        PS.Person_FirName,
                        to_char(PS.Person_Birthday, 'DD.MM.YYYY') as Person_Birthday
                    from v_PersonState PS
                    where ED.Person_id = PS.Person_id 
                    limit 1
                ) as PS on true
                LEFT JOIN LATERAL (
                    select
                        EQ.EvnQueue_id,
                        EQ.EvnQueue_setDT
                    from
                        v_EvnQueue EQ
                    where EQ.EvnDirection_id = ED.EvnDirection_id
                        and EQ.EvnQueue_recDT is null
                        and EQ.EvnQueue_failDT is null
                        and EQ.EvnQueue_IsArchived is null
                        limit 1
                ) EQ on true
                LEFT JOIN LATERAL (
                    select
                        Person_Fin,
                        Person_SurName,
                        Person_FirName,
                        Person_SecName
                    from v_MedPersonal
                    where MedPersonal_id = ED.MedPersonal_id
                        and Lpu_id = Lpu.Lpu_id
                    limit 1
                ) MP on true
                left join v_LpuSectionProfile LSP  on LSP.LpuSectionProfile_id = ED.LpuSectionProfile_id
                left join v_Diag Diag  on Diag.Diag_id = ED.Diag_id
                left join v_LpuSection LS  on ED.LpuSection_did = LS.LpuSection_id
                left join v_MedService MS on MS.MedService_id = ED.MedService_id and MS.Lpu_id = '1101004'
                left join v_MedServiceType MST on MST.MedServiceType_id = MS.MedServiceType_id
                left join v_TimetableStac_lite TTS on TTS.Evn_id = ED.EvnDirection_id
                left join lateral (
                    select
                        LeaveType_id,
                        PrehospWaifRefuseCause_id,
                        PrehospStatus_id,
                        EvnPS_setDate
                    from v_EvnPS
                    where EvnDirection_id = ED.EvnDirection_id
                    order by EvnPS_id desc
                    limit 1
                ) as EvnPS on true
                --left join v_EvnPS EvnPS on EvnPS.EvnDirection_id = ED.EvnDirection_id
                left join v_MedStaffFact MSF on MSF.MedStaffFact_id = ED.MedStaffFact_id
                left join v_LpuSectionProfile MedPersLSP on MedPersLSP.LpuSectionProfile_id = MSF.LpuSectionProfile_id
                left join v_LeaveType LT on EvnPS.LeaveType_id = LT.LeaveType_id
                left join v_PrehospWaifRefuseCause PWRC on PWRC.PrehospWaifRefuseCause_id = EvnPS.PrehospWaifRefuseCause_id
                left join v_DirFailType DFT on DFT.DirFailType_id = ED.DirFailType_id
                left join v_LpuSectionBedProfile LSBP on LSBP.LpuSectionBedProfile_id = ED.LpuSectionBedProfile_id
                LEFT JOIN LATERAL (
                    select EvnPrescrVK_id, Lpu_id
                    from v_EvnPrescrVK epvkHTM
                    where epvkHTM.EvnDirectionHTM_id = ED.EvnDirection_id
                    limit 1
                ) epvkHTM on true
                left join v_EvnDirectionHTM edh on edh.EvnDirectionHTM_id = ed.EvnDirection_id
                LEFT JOIN LATERAL (
                    select EvnPrescrVK_id, Lpu_id
                    from v_EvnPrescrVK epvkSM
                    where epvkSM.EvnDirection_sid = ED.EvnDirection_id
                        and epvkSM.Lpu_id = '1101004'
                    limit 1
                ) epvkSM on true
                left join (
                        select
                            COUNT(epvkHTM.EvnPrescrVK_id) as CountVK,
                            epvkHTM.EvnDirectionHTM_id as EvnDirectionHTM_id,
                            epvkHTM.Lpu_id as Lpu_id
                        from
		                    v_EvnPrescrVK epvkHTM
	                    group by epvkHTM.EvnDirectionHTM_id, epvkHTM.Lpu_id
                ) epvkHTMCount on
	                    ED.EvnDirection_id = epvkHTMCount.EvnDirectionHTM_id and epvkHTMCount.Lpu_id = '1101004'
                left join dbo.v_EvnVK evk
                    on evk.EvnPrescrVK_id = coalesce((case when epvkHTM.Lpu_id = '1101004' then epvkHTM.EvnPrescrVK_id else NULL end), epvkSM.EvnPrescrVK_id)
                left join dbo.v_EvnLink el on el.Evn_id = coalesce((case when epvkHTM.Lpu_id = '1101004' then epvkHTM.EvnPrescrVK_id else NULL end), epvkSM.EvnPrescrVK_id)
                left join dbo.v_EvnDirection_all evkd on evkd.EvnDirection_id = el.Evn_lid
                left join v_TalonHTM thtm on thtm.EvnDirectionHTM_id = ed.EvnDirection_id
                
                left join Evn ParentEvn on ParentEvn.Evn_id = ED.EvnDirection_pid and ParentEvn.Evn_deleted = 1
                left join EvnClass ParentEvnClass on ParentEvnClass.EvnClass_id = ParentEvn.EvnClass_id
                left join Evn RootEvn on RootEvn.Evn_id = ED.EvnDirection_rid and RootEvn.Evn_deleted = 1
                left join EvnClass RootEvnClass on RootEvnClass.EvnClass_id = RootEvn.EvnClass_id
            
                
                 WHERE 
                ED.EvnDirection_setDT < (cast('2023-07-31' as date) + '1 day'::interval)
                and ED.EvnDirection_setDT >= cast('2021-07-01' as date)
                
                and coalesce(ED.EvnStatus_id, 0) not in (12,13) and ED.EvnDirection_failDT is null and ED.DirType_id = '1' and coalesce(ED.EvnDirection_IsConfirmed, 1) = '1' and coalesce(Diag.Diag_Code, '') not between 'F00.0' and 'F99' and coalesce(Diag.Diag_Code, '') not between 'B20.0' and 'B24' and coalesce(Diag.Diag_Code, '') not between 'A50.0' and 'A64' and ED.Lpu_id not in(357) and (ED.Lpu_did = '1101004' or ED.Org_oid = '1101004')
                
                
                and (
                    DT.DirType_Code in (1,4,5)
                    or (
                        DT.DirType_Code = 6
                        and MST.MedServiceType_SysNick = 'osmotrgosp'
                        and exists (
                            select MedServiceMedPersonal_id
                            from v_MedServiceMedPersonal
                            where MedPersonal_id = '120101000011879'
                                and (MedServiceMedPersonal_begDT is null or MedServiceMedPersonal_begDT <= (select curDate from curDT))
                                and (MedServiceMedPersonal_endDT is null or MedServiceMedPersonal_endDT >= (select curDate from curDT))
                            limit 1
                        )
                    )
                    
                )
            
                
                
                        ORDER BY
                            
                ED.EvnDirection_setDT DESC
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
