
                
                
                SELECT 
                        els.EvnLabSample_id as "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",
                        coalesce(els.LabSampleStatus_id, 0) as "LabSampleStatus_id",
                        els.EvnLabSample_Num as "EvnLabSample_Num",
                        els.EvnLabSample_BarCode as "EvnLabSample_BarCode",
                        els.EvnLabRequest_id as "EvnLabRequest_id",
                        els.MedService_id as "MedService_id",
                        rm.RefMaterial_id as "RefMaterial_id",
                        rm.RefMaterial_Name as "RefMaterial_Name",
                        ct.ContainerType_id as "ContainerType_id",
                        ct.ContainerType_Name as "ContainerType_Name",
                        bms.BiomaterialStorage_id as "BiomaterialStorage_id",
                        ed.EvnDirection_id as "EvnDirection_id",
                        ed.EvnDirection_Num as "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",
                        LSS.LabSampleStatus_Code as "LabSampleStatus_Code",
                        ps.Person_id as "Person_id",
                        case when 0=1 and PEH.PersonEncrypHIV_id is not null then PEH.PersonEncrypHIV_Encryp
                            else COALESCE(ps.Person_SurName, '')
                        end as "Person_Surname",
                        case when 0=1 and PEH.PersonEncrypHIV_id is not null then PEH.PersonEncrypHIV_Encryp
                            else COALESCE(ps.Person_FirName,'')
                        end as "Person_Firname",
                        case when 0=1 and PEH.PersonEncrypHIV_id is not null then PEH.PersonEncrypHIV_Encryp
                            else COALESCE(ps.Person_SecName,'')
                        end as "Person_Secname",
                        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",
                        els.EvnLabSample_StudyDT as "EvnLabSample_StudyDT",
                        elr.EvnLabRequest_BarCode as "EvnLabRequest_BarCode",
                        elr.UslugaComplex_id as "UslugaComplexTarget_id",
                        a.Analyzer_id as "Analyzer_id",
                        a.Analyzer_Name as "Analyzer_Name",
                        a.Analyzer_2wayComm as "Analyzer_2wayComm",
                        ls.LpuSection_Code as "LpuSection_Code",
                        ls.LpuSection_Name as "LpuSection_Name",
                        ms.MedService_Nick as "MedService_Nick",
                        MP.Person_SurName as "EDMedPersonalSurname",
                        elr.EvnLabRequest_RegNum as "EvnLabRequest_RegNum",
                        elr.EvnLabRequest_UslugaName as "EvnLabRequest_UslugaName",
                        case when (elr.EvnLabRequest_prmTime 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",
                        Lpu.Lpu_Nick as "Lpu_Nick",
                        COALESCE(els.EvnLabSample_IsOutNorm, 1) as "EvnLabSample_IsOutNorm",
                        ut.cnt as "EvnLabSample_Tests"
                        
                        
                    ,coalesce(extdir.ExtMedPersonalEvnDirectionLink_ExtID, '') as "ExtDirection_id"
                    ,extmp.ExtMedPersonal_Section as "ExtMedPersonal_Section"
                    ,extmp.ExtMedPersonal_SurName as "ExtMedPersonal_SurName"
                
                        
                FROM
                
                        dbo.v_EvnLabSample els
                        inner join v_EvnLabRequest elr on els.EvnLabRequest_id = elr.EvnLabRequest_id
                        inner join v_MedService ms on ms.MedService_id = elr.MedService_id
                        inner join v_EvnDirection_all ed on ed.EvnDirection_id = elr.EvnDirection_id
                        inner join lis.v_Analyzer a on a.Analyzer_id = els.Analyzer_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 lateral(
                    select
                        PersonState.Person_SurName
                    from dbo.MedPersonalCache MP
                        inner JOIN v_PersonState PersonState ON mp.Person_id = PersonState.Person_id
                        inner JOIN Lpu l ON l.Lpu_id = mp.Lpu_id
                        inner JOIN Org ON Org.Org_id = l.Org_id
                    where 1=1
                        and MP.MedPersonal_id = ed.MedPersonal_id
                        and COALESCE(l.region_id, getregion()) = getregion()
                    limit 1
                ) mp on true --#PROMEDWEB-31626 оптимизация
                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 v_ContainerType ct on rs.ContainerType_id = ct.ContainerType_id
                        left join lis.v_BiomaterialStorage bms on els.EvnLabSample_id = bms.EvnLabSample_id
                        left join lateral(
                            select
                                count(*) as cnt
                            from
                                v_UslugaTest ut
                            where
                                ut.EvnLabSample_id = els.EvnLabSample_id
                                and ut.EvnDirection_id is null
                        ) ut on true
                        
                    left join integra.v_ExtMedPersonalEvnDirectionLink extdir on ed.EvnDirection_id = extdir.EvnDirection_id
                    left join integra.v_ExtMedPersonal extmp on extdir.ExtMedPersonal_id = extmp.ExtMedPersonal_id
                
                        
                        
                         WHERE 
                        els.Lpu_id = '424'
                        and els.EvnLabSample_setDT is not null
                        and (els.MedService_id = '15853' )
                         and cast(extdir.ExtMedPersonalEvnDirectionLink_ExtID as varchar) LIKE '%' || '31330110859648' || '%'
                        
                    and (
                        (els.EvnLabSample_StudyDT is null
                            and cast(els.EvnLabSample_setDT as date) between '2022-04-14'::date and '2022-05-07'::date
                        ) or cast(els.EvnLabSample_StudyDT as date) between '2022-04-14'::date and '2022-05-07'::date
                    )
                
                        
                
                        ORDER BY
                            
                        "EvnLabSample_id" asc
                    
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
