
                with tempTable as (
                    SELECT
                        els.EvnLabSample_id,
                        substring(els.EvnLabSample_Num from 9 for 4) as EvnLabSample_ShortNum,
                        CASE WHEN COALESCE(elr.EvnLabRequest_IsCito, 1) = 2 THEN '!' else '' END AS EvnDirection_IsCito,
                        els.LabSampleStatus_id,
                        els.EvnLabSample_Num,
                        els.EvnLabSample_BarCode,
                        els.EvnLabRequest_id,
                        els.MedService_id,
                        rm.RefMaterial_id,
                        rm.RefMaterial_Name,
                        ed.EvnDirection_id,
                        ed.EvnDirection_Num,
                        case
                            when 1 = ed.PrehospDirect_id then COALESCE(ls.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(ls.LpuSection_Name, Lpu_Nick)
                        end as PrehospDirect_Name,
                        COALESCE(LSS.LabSampleStatus_SysNick, 'new') as ProbaStatus,
                        ps.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,
                        to_char(PS.Person_Birthday, 'dd.mm.yyyy') as Person_Birthday,
                        case when 0=1 then PEH.PersonEncrypHIV_Encryp end as PersonEncrypHIV_Encryp,
                        to_char (els.EvnLabSample_setDT, 'HH24:MI dd.mm.yyyy') as EvnLabSample_setDT,
                        to_char (els.EvnLabSample_DelivDT, 'HH24:MI dd.mm.yyyy') as EvnLabSample_DelivDT,
                        els.EvnLabSample_StudyDT as EvnLabSample_StudyDT,
                        elr.EvnLabRequest_BarCode as EvnLabRequest_BarCode,
                        elr.UslugaComplex_id as UslugaComplexTarget_id,
                        ls.LpuSection_Code,
                        ls.LpuSection_Name,
                        ms.MedService_Nick,
                        msdid.MedService_Nick as MedServiceDid_Nick,
                        elr.EvnLabRequest_UslugaName,
                        Lpu.Lpu_Nick,
                        ct.ContainerType_Name,
                        case when els.EvnLabSample_IsDeliveryPeriodMissed = 2
                            then 2
                            when (els.EvnLabSample_DelivDT is null
                                and link.MedServiceLink_ExecutionRate is not null
                                and localtimestamp >= DATEADD('minute', link.MedServiceLink_ExecutionRate::int, els.EvnLabSample_setDT))
                            then 2
                            else 1
                        end as BM_DeliveryTimeExpired
                    FROM
                        dbo.v_EvnLabSample els
                        inner join v_EvnLabRequest elr on els.EvnLabRequest_id = elr.EvnLabRequest_id
                        left join v_MedService ms on ms.MedService_id = els.MedService_id
                        left join v_MedService msdid on msdid.MedService_id = elr.MedService_sid
                        left join lateral (
                            select msl.MedServiceLink_ExecutionRate
                            from v_MedServiceLink msl
                            where
                                msl.MedServiceLinkType_id = 1
                                and ((msl.MedService_lid = els.MedService_id and msl.MedService_id = elr.MedService_sid)
                                or (msl.MedService_id = els.MedService_id and msl.MedService_lid = elr.MedService_sid))
                            limit 1
                        ) link on true
                        left join v_EvnDirection_all ed on ed.EvnDirection_id = elr.EvnDirection_id
                        left join v_LpuSection ls on ls.LpuSection_id = ed.LpuSection_id
                        
                left join v_Lpu Lpu on Lpu.Lpu_id = ed.Lpu_sid
                left join v_Org Org on Org.Org_id = ed.Org_sid
                left join v_PersonState ps on elr.Person_id = ps.Person_id
            
                        left join v_PersonEncrypHIV peh on peh.Person_id = ps.Person_id
                        left join v_LabSampleStatus lss on lss.LabSampleStatus_id = els.LabSampleStatus_id
                        left join v_RefSample rs on rs.RefSample_id = els.RefSample_id
                        left join v_RefMaterial rm on rm.RefMaterial_id = rs.RefMaterial_id
                        left join lateral (
                            select UslugaComplex_id
                            from v_EvnLabRequestUslugaComplex
                            where EvnLabSample_id = els.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 = els.Medservice_id
                            limit 1
                        ) container on true
                        left join v_ContainerType ct on ct.ContainerType_id = container.ContainerType_id
                    WHERE
                        els.Lpu_id = '9990010252'
                        and els.EvnLabSample_setDT is not null
                        and elr.SampleStatusType_id = 4 -- Новая проба взята, но не отправлена на анализатор
                        and (els.MedService_id = '500801000005380'  or els.MedService_id IN (select MSL.MedService_lid from v_MedServiceLink MSL where msl.MedService_id = '500801000005380'))
                         and els.evnlabsample_delivdt is null
                        
                and ('2023-07-09'::timestamp <= case
                    when 1=0 then null
                    else COALESCE(
                        cast(els.EvnLabSample_StudyDT as date),
                        cast(els.EvnLabSample_setDate as date),
                        cast(elr.EvnLabRequest_didDate as date)
                    ) end)
                and ('2023-07-22'::timestamp >= case
                    when 1=0 then null
                    else COALESCE(
                        cast(els.EvnLabSample_StudyDT as date),
                        cast(els.EvnLabSample_setDate as date),
                        cast(elr.EvnLabRequest_didDate as date)
                    ) end)
            
                )

                SELECT
                    table1.EvnLabSample_id as "EvnLabSample_id",
                    table1.EvnLabSample_ShortNum as "EvnLabSample_ShortNum",
                    table1.EvnDirection_IsCito as "EvnDirection_IsCito",
                    table1.LabSampleStatus_id as "LabSampleStatus_id",
                    table1.EvnLabSample_Num as "EvnLabSample_Num",
                    table1.EvnLabSample_BarCode as "EvnLabSample_BarCode",
                    table1.EvnLabRequest_id as "EvnLabRequest_id",
                    table1.MedService_id as "MedService_id",
                    table1.RefMaterial_id as "RefMaterial_id",
                    table1.RefMaterial_Name as "RefMaterial_Name",
                    table1.EvnDirection_id as "EvnDirection_id",
                    table1.EvnDirection_Num as "EvnDirection_Num",
                    table1.PrehospDirect_Name as "PrehospDirect_Name",
                    table1.ProbaStatus as "ProbaStatus",
                    table1.Person_id as "Person_id",
                    table1.Person_FIO as "Person_FIO",
                    table1.Person_ShortFio as "Person_ShortFio",
                    table1.Person_Birthday as "Person_Birthday",
                    table1.PersonEncrypHIV_Encryp as "PersonEncrypHIV_Encryp",
                    table1.EvnLabSample_setDT as "EvnLabSample_setDT",
                    table1.EvnLabSample_DelivDT as "EvnLabSample_DelivDT",
                    table1.EvnLabSample_StudyDT as "EvnLabSample_StudyDT",
                    table1.EvnLabRequest_BarCode as "EvnLabRequest_BarCode",
                    table1.UslugaComplexTarget_id as "UslugaComplexTarget_id",
                    table1.LpuSection_Code as "LpuSection_Code",
                    table1.MedService_Nick as "MedService_Nick",
                    table1.MedServiceDid_Nick as "MedServiceDid_Nick",
                    table1.Lpu_Nick as "Lpu_Nick",
                    table1.LpuSection_Name as "LpuSection_Name",
                    table1.EvnLabRequest_UslugaName as "EvnLabRequest_UslugaName",
                    table1.ContainerType_Name as "ContainerType_Name",
                    table1.BM_DeliveryTimeExpired as "BM_DeliveryTimeExpired",
                    ut.cnt as "EvnLabSample_Tests"
                FROM
                    tempTable as table1
                    left join lateral(
                        select
                            count(*) as cnt
                        from
                            v_UslugaTest ut
                        where
                            ut.EvnLabSample_id = table1.EvnLabSample_id
                            and ut.EvnDirection_id is null
                    ) ut on true
            
