
                
                
            with curDT as (
                select dbo.tzGetDate() as curDate
            ), curD as (
                select cast(dbo.tzGetDate() AS date) as curDate
            ),
        
                lr as (select
                    *
                    from
                        v_EvnLabRequest lr
                    where
                        (1=1)
                        and (lr.MedService_sid = '890101000000456')
                ),
                tempTable as (
                        
                        Select
                            lr.*
                        from
                            lr
                        where
                            lr.EvnStatus_id = 1
                            and cast(lr.EvnLabRequest_prmTime as date) = '2022-05-14'
                        
                        union
                        
                        Select
                            lr.*
                        from
                            lr
                        where
                            lr.EvnStatus_id = 1
                            and cast(lr.EvnLabRequest_statusDate as date) = '2022-05-14' and lr.EvnLabRequest_prmTime is null
                        
                        union
                        
                            Select
                                lr.*
                            from
                                lr
                                left join v_EvnDirection_all ed on lr.EvnDirection_id = ed.EvnDirection_id
                            where
                                lr.EvnStatus_id = 1
                                and cast(lr.EvnLabRequest_prmTime as date) < '2022-05-14' and ed.EvnDirection_setDate = '2022-05-14'
                            
                        union
                        
                    Select
                        lr.*
                    from
                        lr
                    where
                        lr.EvnStatus_id <> 1
                        and cast(lr.EvnLabRequest_statusDate as date) = '2022-05-14'
                    
                ),
            	elr as (
                    
                select distinct
                    elr.EvnDirection_id,
                    COALESCE(elr.EvnStatus_id, 1) as EvnStatus_id,
                    elr.EvnLabRequest_id,
                    elr.Person_id,
                    case when 0=1 and PEH.PersonEncrypHIV_id is not null then PEH.PersonEncrypHIV_Encryp
                        else COALESCE(ps.Person_SurName, '') || COALESCE(' '|| ps.Person_FirName,'') || COALESCE(' '|| ps.Person_SecName,'') end as Person_FIO,
                    case when 0=1 and PEH.PersonEncrypHIV_id is not null then PEH.PersonEncrypHIV_Encryp
                        else COALESCE(ps.Person_SurName, '') || COALESCE(' '|| SUBSTRING(ps.Person_FirName from 1 for 1) || '.','') || COALESCE(' '|| SUBSTRING(ps.Person_SecName from 1 for 1) || '.','') end as Person_ShortFio,
                    case when 0=1 then PEH.PersonEncrypHIV_Encryp end as PersonEncrypHIV_Encryp,
                    elr.UslugaComplex_id,
                    ps.Person_SurName as Person_Surname,
                    ps.Person_SecName as Person_Secname,
                    ps.Person_FirName as Person_Firname,
                    to_char(PS.Person_Birthday, 'dd.mm.yyyy') as Person_Birthday,
                    elr.EvnLabRequest_UslugaName,
                    case when ('372509273927' = elr.pmUser_insID or elr.EvnLabRequest_setDT is null) then 1 else 0 end as canEdit,
                    elr.UslugaExecutionType_id,
                    elr.EvnLabRequest_LabSampleNums as EvnLabSample_ids,
                    elr.EvnLabRequest_BarCodes as EvnLabRequest_FullBarCode,
                    to_char (elr.EvnLabRequest_prmTime, 'HH24:MI dd.mm.yyyy') as TimetableMedService_begTime,
                    elr.EvnLabRequest_IsOutNorm as EvnLabSample_IsOutNorm,
                    COALESCE(elr.EvnLabRequest_UslugaCount, 0) as EvnLabRequest_Tests,
                    COALESCE(SST.SampleStatusType_SysNick, 'notall') as ProbaStatus,
                    SST.SampleStatusType_Code,
                    elr.MedService_id,
                    elr.MedService_sid,
                    elr.EvnLabRequest_RegNum,
                    elr.EvnLabRequest_prmTime,
                    elr.EvnLabRequest_statusDate,
                    elr.EvnLabRequest_updDT,
                    elr.EvnLabRequest_IsProtocolPrinted,
                    case when (elr.EvnLabRequest_PlanDT is not null)
                        then
                            cast(DATEDIFF('minute', dbo.tzgetdate(), elr.EvnLabRequest_PlanDT) as varchar) || '|' ||
                                case when ABS(DATEDIFF('day', dbo.tzgetdate(), elr.EvnLabRequest_PlanDT)) > 1
                                    then cast(DATEDIFF('hour', dbo.tzgetdate(), elr.EvnLabRequest_PlanDT) / 24 as varchar) || 'д '
                                        || cast(DATEDIFF('hour', dbo.tzgetdate(), elr.EvnLabRequest_PlanDT) % 24 as varchar) || 'ч'
                                    else cast(DATEDIFF('minute', dbo.tzgetdate(), elr.EvnLabRequest_PlanDT) / 60 as varchar) || 'ч '
                                        || cast(DATEDIFF('minute', dbo.tzgetdate(), elr.EvnLabRequest_PlanDT) % 60 as varchar) || 'м'
                                end
                    end as EvnLabRequest_PlanTimeLeft,
                    case when (elr.EvnLabRequest_prmTime is not null and dbo.tzgetdate() >= elr.EvnLabRequest_PlanDT)
                        then 2
                        else 1
                    end as EvnLabRequest_IsExecutionPeriodMissed
                from
                    
                tempTable as elr
            
                    left join v_SampleStatusType sst on sst.SampleStatusType_id = elr.SampleStatusType_id
                    left join v_PersonState ps on elr.Person_id = ps.Person_id
                    left join v_PersonEncrypHIV PEH on PEH.Person_id = ps.Person_id
              where (1=1)    and elr.MedService_sid = '890101000000456'
                )
                
                
                SELECT distinct
                    elr.EvnDirection_id as "EvnDirection_id",
                    elr.EvnStatus_id as "EvnStatus_id",
                    elr.EvnLabRequest_id as "EvnLabRequest_id",
                    ed.EvnDirection_pid as "EvnDirection_pid",
                    elr.Person_id as "Person_id",
                    elr.Person_FIO as "Person_FIO",
                    elr.Person_ShortFio as "Person_ShortFio",
                    elr.Person_Surname as "Person_Surname",
                    elr.Person_Secname as "Person_Secname",
                    elr.Person_Firname as "Person_Firname",
                    elr.Person_Birthday as "Person_Birthday",
                    case when exists(
                        select *
                        from v_PersonQuarantine PQ
                        where PQ.Person_id = elr.Person_id
                            and PQ.PersonQuarantine_endDT is null
                        limit 1
                    ) then 2 else 1 end as "PersonQuarantine_IsOn",
                    elr.EvnLabRequest_PlanTimeLeft as "EvnLabRequest_PlanTimeLeft",
                    elr.EvnLabRequest_IsExecutionPeriodMissed as "EvnLabRequest_IsExecutionPeriodMissed",
                    elr.EvnLabRequest_IsProtocolPrinted as "EvnLabRequest_IsProtocolPrinted",
                    elr.PersonEncrypHIV_Encryp as "PersonEncrypHIV_Encryp",
                    ed.Lpu_sid as "Lpu_sid",
                    ed.Org_sid as "Org_sid",
                    ed.LpuSection_id as "LpuSection_id",
                    elr.UslugaComplex_id as "UslugaComplex_id",
                    elr.EvnLabRequest_UslugaName as "EvnLabRequest_UslugaName",
                    elr.canEdit as "canEdit",
                    elr.UslugaExecutionType_id as "UslugaExecutionType_id",
                    case when ed.EvnDirection_IsCito = 2 then '!' else '' end as "EvnDirection_IsCito",
                    to_char (ed.EvnDirection_setDate, 'dd.mm.yyyy') as "EvnDirection_setDate",
                    ed.PrehospDirect_id as "PrehospDirect_id",
                    case
                        when 1 = ed.PrehospDirect_id then COALESCE(LpuSection.LpuSection_Name, Lpu.Lpu_Nick) -- 1 Отделение ЛПУ (Если не выбрали то ЛПУ)
                        when 2 = ed.PrehospDirect_id then Lpu.Lpu_Nick -- 2 Другое ЛПУ --Lpu_sid - Направившее ЛПУ
                        when ed.PrehospDirect_id in ( 3, 4, 5, 6 ) then Org.Org_nick -- 3 Другая организация -- 4 Военкомат -- 5 Скорая помощь -- 6 Администрация -- Org_sid - Направившая организация
                        when 7 = ed.PrehospDirect_id then 'Пункт помощи на дому' --7Пункт помощи на дому
                        else COALESCE(LpuSection.LpuSection_Name, Lpu_Nick)
                    end as "PrehospDirect_Name",
                    ed.EvnDirection_Num as "EvnDirection_Num",
                    elr.EvnLabSample_ids as "EvnLabSample_ids",
                    elr.EvnLabRequest_FullBarCode as "EvnLabRequest_FullBarCode",
                    elr.TimetableMedService_begTime as "TimetableMedService_begTime",
                    LpuSection.LpuSection_Code as "LpuSection_Code",
                    elr.EvnLabSample_IsOutNorm as "EvnLabSample_IsOutNorm",
                    elr.EvnLabRequest_Tests as "EvnLabRequest_Tests",
                    elr.ProbaStatus as "ProbaStatus",
                    elr.SampleStatusType_Code as "SampleStatusType_Code",
                    -- Не показывать меню, если в заявке больше одной пробы или услуги
                    case when (COALESCE(elr.EvnStatus_id, 1) <= 2 and eup.cnt = 1 and els.cnt = 1) then 1 else 0 end as "needTestMenu",
                    elr.MedService_id as "MedService_id",
                    elr.MedService_sid as "MedService_sid",
                    to_char(elr.EvnLabRequest_prmTime,'dd.mm.yyyy') as "TimetableMedService_Date",
                    elr.EvnLabRequest_RegNum as "EvnLabRequest_RegNum",
                    lpu.Lpu_Nick as "Lpu_Nick",
                    Containers.EvnLabSample_Containers as "EvnLabSample_Containers"
                    
                ,LpuSection.LpuSection_Name as "LpuSection_Name"
                ,MPPS.Person_SurName as "EDMedPersonalSurname"
            , ELSMSNicks.MedService_Nicks as "MedService_Nick", ELSMSInfo.MedService_IsExternal as "MedService_IsExternal", ELSMSInfo.MedService_IsFileIntegration as "MedService_IsFileIntegration", ELSNums.EvnLabSample_Nums as "EvnLabRequest_SampleNum"
                    
                FROM
                
                    elr
                    inner join lateral(
                        Select
                            ed.EvnDirection_id, ed.EvnQueue_id, ed.LpuSection_id, ed.Lpu_sid, ed.Org_sid,
                            ed.EvnDirection_IsCito, ed.EvnDirection_pid, ed.EvnDirection_setDate,
                            ed.PrehospDirect_id, ed.EvnDirection_Num,
                            ed.MedPersonal_id, ed.MedStaffFact_id
                            
                        from
                            v_EvnDirection_all ed
                            
                            --left join v_EvnQueue eq on eq.EvnDirection_id = ed.EvnDirection_id
                        where
                            ed.dirtype_id in (10, 25, 30) and ed.EvnDirection_id = elr.EvnDirection_id
                            and ed.DirFailType_id is null
                            --and eq.QueueFailCause_id is null
                            and (ELR.EvnStatus_id IN (3,4) OR COALESCE(ED.EvnStatus_id, 1) not in (12,13)) -- отменённые направление отображать только с выполненными/одобренными заявками
                        limit 1
                    ) ed on true
                    left join lateral(
                        select
                            count(eup.EvnUslugaPar_id) as cnt
                        from
                            v_EvnUslugaPar eup
                        where
                            eup.EvnDirection_id = ed.EvnDirection_id
                    ) eup on true -- придётся запрашивать количество услуг, чтобы поянть надо ли выводить меню с тестами
                    LEFT JOIN LATERAL (
                        SELECT
                            COUNT(EvnLabSample.EvnLabSample_id) AS cnt
                        FROM
                            v_EvnLabSample EvnLabSample
                        WHERE
                            EvnLabSample.EvnLabRequest_id = elr.EvnLabRequest_id
                    ) els ON true
                    left join lateral (select * from v_LpuSection LpuSection where LpuSection.LpuSection_id = ed.LpuSection_id limit 1) LpuSection on true
                    left join lateral (select * from v_Lpu Lpu where Lpu.Lpu_id = ed.Lpu_sid limit 1 ) Lpu on true
                    left join v_Org Org on Org.Org_id = ed.Org_sid
                    left join v_MedService ms on ms.MedService_id = elr.MedService_id
                    left join lateral(
                        select
                            Person_id
                        from
                            dbo.MedPersonalCache
                        where
                            MedPersonal_id = ed.MedPersonal_id
                            and (select curDate from curD) between WorkData_begDate and coalesce(WorkData_endDate, (select curDate from curDT))
                        limit 1
                    ) MP on true
                    left join lateral (select * from dbo.v_PersonState as MPPS where MP.Person_id=MPPS.Person_id limit 1) MPPS on true
                    left join lateral(
                        select
                            string_agg((
                                cast(ls.EvnLabSample_id as varchar) || '||'
                                || coalesce(cast(ct.ContainerType_Name as varchar), '') || '||'
                                || coalesce(cast(ct.ContainerTypeColor_id as varchar), '') || '||'
                                || coalesce(cast(ct.ContainerKind_id as varchar), '')), ', ') as EvnLabSample_Containers
                        from v_EvnLabSample ls
                        left join lateral (
                            select UslugaComplex_id
                            from v_EvnLabRequestUslugaComplex
                            where EvnLabSample_id = ls.EvnLabSample_id
                            limit 1
                        ) ELRUC on true
                        left join lateral (
                            select
                                rs.ContainerType_id
                            from
                                v_UslugaComplexMedService ucms
                                inner join v_RefSample rs on rs.RefSample_id = ucms.RefSample_id
                            where
                                ucms.UslugaComplex_id = ELRUC.UslugaComplex_id
                                and ucms.MedService_id = ls.Medservice_id
                            limit 1
                        ) container on true
                        left join v_ContainerType ct on ct.ContainerType_id = container.ContainerType_id
                        where ls.EvnLabRequest_id = elr.EvnLabRequest_id
                    ) Containers on true
                    
                left join lateral(
                	select
                    	string_agg(COALESCE(ms.MedService_Nick,''), '
') as MedService_Nicks
                    from v_EvnLabSample ls
                    inner join v_MedService ms on ms.MedService_id = ls.MedService_id
                    where ls.EvnLabRequest_id = elr.EvnLabRequest_id
                ) ELSMSNicks on true
                left join lateral(
                	select
                        coalesce(ms.MedService_IsExternal, 0) as MedService_IsExternal,
                        coalesce(ms.MedService_IsFileIntegration, 0) as MedService_IsFileIntegration
                    from v_EvnLabSample ls
                    inner join v_MedService ms on ms.MedService_id = ls.MedService_id
                    where ls.EvnLabRequest_id = elr.EvnLabRequest_id
                ) ELSMSInfo on true

            
                left join lateral(
                	select
                    	string_agg(substring(ls.EvnLabSample_Num from 9 for 4), ', ') as EvnLabSample_Nums
                    from v_EvnLabSample ls
                    where ls.EvnLabRequest_id = elr.EvnLabRequest_id and ls.EvnLabSample_Num is not null
                ) ELSNums on true
            
                     WHERE 
                    (1=1) 
                    and '13101856' = ed.Lpu_sid
                    
                
                        ORDER BY
                            
                        "EvnDirection_id" asc
                    
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
