
                
                with 
                   lr as (select lr.EvnLabRequest_id,
                                   lr.EvnDirection_id,
                                   lr.Person_id,
                                   lr.EvnStatus_id,
                                   lr.pmUser_insID,
                                   lr.EvnLabRequest_UslugaName,
                                   lr.EvnLabRequest_setDT,
                                   lr.UslugaComplex_id,
                                   lr.UslugaExecutionType_id,
                                   lr.EvnLabRequest_LabSampleNums,
                                   lr.EvnLabRequest_BarCodes,
                                   lr.EvnLabRequest_prmTime,
                                   lr.EvnLabRequest_IsOutNorm,
                                   lr.EvnLabRequest_UslugaCount,
                                   lr.MedService_id,
                                   lr.MedService_sid,
                                   lr.EvnLabRequest_RegNum,
                                   lr.EvnLabRequest_statusDate,
                                   lr.EvnLabRequest_updDT,
                                   lr.EvnLabRequest_IsProtocolPrinted,
                                   lr.PayType_id,
                                   lr.EvnLabRequest_PlanDT,
                                   lr.SampleStatusType_id,
                                   ps.Person_SurName,
                                   ps.Person_FirName,
                                   ps.Person_SecName,
                                   ps.Person_Birthday
                              from v_EvnLabRequest lr
                                inner join v_EvnDirection_all ed on lr.EvnDirection_id = ed.EvnDirection_id
                                    
                                left join v_PersonState ps on lr.Person_id = ps.Person_id
                
                    left join lateral (
                        select distinct
                            ucg.RoleRuleType_id
                        from v_EvnLabRequestUslugaComplex elruc
                            left join v_EvnUslugaPar eup on elruc.EvnUslugaPar_id = eup.EvnUslugaPar_id
                            left join v_UslugaComplexGroupLink ucgl on eup.UslugaComplex_id = ucgl.UslugaComplex_id
                            left join v_UslugaComplexGroup ucg on ucgl.UslugaComplexGroup_id = ucg.UslugaComplexGroup_id
                        where elruc.EvnLabRequest_id = lr.EvnLabRequest_id
                    ) rrt on true
                
                    left join v_EvnLabSample els on els.EvnLabRequest_id = lr.EvnLabRequest_id
                        and els.MedService_id = '70101000000102'
                
                    where (1=1)
                      and ed.dirtype_id in (10, 25, 30)
                      and ed.DirFailType_id is null
                      and (lr.EvnStatus_id IN (3,4) OR COALESCE(ED.EvnStatus_id, 1) not in (12,13))

                and lr.MedService_id in ('70101000000102') and lr.EvnStatus_id = 1
                          and (lr.EvnLabRequest_PrmTime is null
                            and lr.EvnLabRequest_StatusDate >= '2023-07-01 00:00:00'
                            and lr.EvnLabRequest_StatusDate <= '2023-07-31 23:59:59'
                          )
                    AND (lr.MedService_id = '70101000000102' OR els.EvnLabSample_id is not null)
                 and (1=1) and exists(
                select
                    EvnUsluga_id as "EvnUslugaPar_id"
                from v_EvnUsluga EvnUsluga
                    inner join v_Evn Evn on Evn.Evn_id = EvnUsluga.EvnUsluga_id and Evn.EvnClass_id = 47
                where
                    EvnUsluga.EvnDirection_id = lr.EvnDirection_id
                    and EvnUsluga.UslugaComplex_id =  '206894'
                limit 1
            )
                    and (rrt.RoleRuleType_id is null or rrt.RoleRuleType_id in (57, 65, 58, 64, 63, 62, 59, 60, 61))
                
                    union
                select lr.EvnLabRequest_id,
                                   lr.EvnDirection_id,
                                   lr.Person_id,
                                   lr.EvnStatus_id,
                                   lr.pmUser_insID,
                                   lr.EvnLabRequest_UslugaName,
                                   lr.EvnLabRequest_setDT,
                                   lr.UslugaComplex_id,
                                   lr.UslugaExecutionType_id,
                                   lr.EvnLabRequest_LabSampleNums,
                                   lr.EvnLabRequest_BarCodes,
                                   lr.EvnLabRequest_prmTime,
                                   lr.EvnLabRequest_IsOutNorm,
                                   lr.EvnLabRequest_UslugaCount,
                                   lr.MedService_id,
                                   lr.MedService_sid,
                                   lr.EvnLabRequest_RegNum,
                                   lr.EvnLabRequest_statusDate,
                                   lr.EvnLabRequest_updDT,
                                   lr.EvnLabRequest_IsProtocolPrinted,
                                   lr.PayType_id,
                                   lr.EvnLabRequest_PlanDT,
                                   lr.SampleStatusType_id,
                                   ps.Person_SurName,
                                   ps.Person_FirName,
                                   ps.Person_SecName,
                                   ps.Person_Birthday
                              from v_EvnLabRequest lr
                                inner join v_EvnDirection_all ed on lr.EvnDirection_id = ed.EvnDirection_id
                                    
                                left join v_PersonState ps on lr.Person_id = ps.Person_id
                
                    left join lateral (
                        select distinct
                            ucg.RoleRuleType_id
                        from v_EvnLabRequestUslugaComplex elruc
                            left join v_EvnUslugaPar eup on elruc.EvnUslugaPar_id = eup.EvnUslugaPar_id
                            left join v_UslugaComplexGroupLink ucgl on eup.UslugaComplex_id = ucgl.UslugaComplex_id
                            left join v_UslugaComplexGroup ucg on ucgl.UslugaComplexGroup_id = ucg.UslugaComplexGroup_id
                        where elruc.EvnLabRequest_id = lr.EvnLabRequest_id
                    ) rrt on true
                
                    left join v_EvnLabSample els on els.EvnLabRequest_id = lr.EvnLabRequest_id
                        and els.MedService_id = '70101000000102'
                
                    where (1=1)
                      and ed.dirtype_id in (10, 25, 30)
                      and ed.DirFailType_id is null
                      and (lr.EvnStatus_id IN (3,4) OR COALESCE(ED.EvnStatus_id, 1) not in (12,13))

                and lr.MedService_id in ('70101000000102') and lr.EvnStatus_id = 1
                          and lr.EvnLabRequest_PrmTime >= '2023-07-01 00:00:00'
                          and lr.EvnLabRequest_PrmTime <= '2023-07-31 23:59:59'
                    AND (lr.MedService_id = '70101000000102' OR els.EvnLabSample_id is not null)
                 and (1=1) and exists(
                select
                    EvnUsluga_id as "EvnUslugaPar_id"
                from v_EvnUsluga EvnUsluga
                    inner join v_Evn Evn on Evn.Evn_id = EvnUsluga.EvnUsluga_id and Evn.EvnClass_id = 47
                where
                    EvnUsluga.EvnDirection_id = lr.EvnDirection_id
                    and EvnUsluga.UslugaComplex_id =  '206894'
                limit 1
            )
                    and (rrt.RoleRuleType_id is null or rrt.RoleRuleType_id in (57, 65, 58, 64, 63, 62, 59, 60, 61))
                
                    union
                select lr.EvnLabRequest_id,
                                   lr.EvnDirection_id,
                                   lr.Person_id,
                                   lr.EvnStatus_id,
                                   lr.pmUser_insID,
                                   lr.EvnLabRequest_UslugaName,
                                   lr.EvnLabRequest_setDT,
                                   lr.UslugaComplex_id,
                                   lr.UslugaExecutionType_id,
                                   lr.EvnLabRequest_LabSampleNums,
                                   lr.EvnLabRequest_BarCodes,
                                   lr.EvnLabRequest_prmTime,
                                   lr.EvnLabRequest_IsOutNorm,
                                   lr.EvnLabRequest_UslugaCount,
                                   lr.MedService_id,
                                   lr.MedService_sid,
                                   lr.EvnLabRequest_RegNum,
                                   lr.EvnLabRequest_statusDate,
                                   lr.EvnLabRequest_updDT,
                                   lr.EvnLabRequest_IsProtocolPrinted,
                                   lr.PayType_id,
                                   lr.EvnLabRequest_PlanDT,
                                   lr.SampleStatusType_id,
                                   ps.Person_SurName,
                                   ps.Person_FirName,
                                   ps.Person_SecName,
                                   ps.Person_Birthday
                              from v_EvnLabRequest lr
                                inner join v_EvnDirection_all ed on lr.EvnDirection_id = ed.EvnDirection_id
                                    
                                left join v_PersonState ps on lr.Person_id = ps.Person_id
                
                    left join lateral (
                        select distinct
                            ucg.RoleRuleType_id
                        from v_EvnLabRequestUslugaComplex elruc
                            left join v_EvnUslugaPar eup on elruc.EvnUslugaPar_id = eup.EvnUslugaPar_id
                            left join v_UslugaComplexGroupLink ucgl on eup.UslugaComplex_id = ucgl.UslugaComplex_id
                            left join v_UslugaComplexGroup ucg on ucgl.UslugaComplexGroup_id = ucg.UslugaComplexGroup_id
                        where elruc.EvnLabRequest_id = lr.EvnLabRequest_id
                    ) rrt on true
                
                    left join v_EvnLabSample els on els.EvnLabRequest_id = lr.EvnLabRequest_id
                        and els.MedService_id = '70101000000102'
                
                    where (1=1)
                      and ed.dirtype_id in (10, 25, 30)
                      and ed.DirFailType_id is null
                      and (lr.EvnStatus_id IN (3,4) OR COALESCE(ED.EvnStatus_id, 1) not in (12,13))

                and lr.MedService_id in ('70101000000102') and (lr.EvnStatus_id > 1
                            and lr.EvnLabRequest_StatusDate >= '2023-07-01 00:00:00'
                            and lr.EvnLabRequest_StatusDate <= '2023-07-31 23:59:59'
                          )
                    AND (lr.MedService_id = '70101000000102' OR els.EvnLabSample_id is not null)
                 and (1=1) and exists(
                select
                    EvnUsluga_id as "EvnUslugaPar_id"
                from v_EvnUsluga EvnUsluga
                    inner join v_Evn Evn on Evn.Evn_id = EvnUsluga.EvnUsluga_id and Evn.EvnClass_id = 47
                where
                    EvnUsluga.EvnDirection_id = lr.EvnDirection_id
                    and EvnUsluga.UslugaComplex_id =  '206894'
                limit 1
            )
                    and (rrt.RoleRuleType_id is null or rrt.RoleRuleType_id in (57, 65, 58, 64, 63, 62, 59, 60, 61))
                ),
                	elr as (
                    
                select distinct
                    elr.EvnDirection_id,
                    COALESCE(elr.EvnStatus_id, 1) as EvnStatus_id,
                    elr.EvnLabRequest_id,
                    elr.Person_id,
                    elr.UslugaComplex_id,
                    
                    case when 0=1 and PEH.PersonEncrypHIV_id is not null then PEH.PersonEncrypHIV_Encryp
                        else COALESCE(elr.Person_SurName, '') || COALESCE(' '|| elr.Person_FirName,'') || COALESCE(' '|| elr.Person_SecName,'') end as Person_FIO,
                    case when 0=1 and PEH.PersonEncrypHIV_id is not null then PEH.PersonEncrypHIV_Encryp
                        else COALESCE(elr.Person_SurName, '') || COALESCE(' '|| SUBSTRING(elr.Person_FirName from 1 for 1) || '.','') || COALESCE(' '|| SUBSTRING(elr.Person_SecName from 1 for 1) || '.','') end as Person_ShortFio,
                    case when 0=1 then PEH.PersonEncrypHIV_Encryp end as PersonEncrypHIV_Encryp,
                    elr.Person_SurName as Person_Surname,
                    elr.Person_SecName as Person_Secname,
                    elr.Person_FirName as Person_Firname,
                    to_char(elr.Person_Birthday, 'dd.mm.yyyy') as Person_Birthday,
                
                    elr.EvnLabRequest_UslugaName,
                    case when ('381236315494' = 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,
                    elr.PayType_id,
                    case when (elr.EvnLabRequest_prmTime is not null)
                        then
                            cast(DATEDIFF('minute', ct.curDT, elr.EvnLabRequest_PlanDT) as varchar) || '|' ||
                                case when ABS(DATEDIFF('day', ct.curDT, elr.EvnLabRequest_PlanDT)) > 1
                                    then cast(DATEDIFF('hour', ct.curDT, elr.EvnLabRequest_PlanDT) / 24 as varchar) || 'д '
                                        || cast(DATEDIFF('hour', ct.curDT, elr.EvnLabRequest_PlanDT) % 24 as varchar) || 'ч'
                                    else cast(DATEDIFF('minute', ct.curDT, elr.EvnLabRequest_PlanDT) / 60 as varchar) || 'ч '
                                        || cast(DATEDIFF('minute', ct.curDT, elr.EvnLabRequest_PlanDT) % 60 as varchar) || 'м'
                                end
                    end as EvnLabRequest_PlanTimeLeft,
                    case when (elr.EvnLabRequest_prmTime is not null and ct.curDT >= elr.EvnLabRequest_PlanDT)
                        then 2
                        else 1
                    end as EvnLabRequest_IsExecutionPeriodMissed
                from
                    lr as elr
                    join lateral (select localtimestamp as curDT) ct on true
                    left join v_SampleStatusType sst on sst.SampleStatusType_id = elr.SampleStatusType_id
                    left join v_PersonEncrypHIV PEH on PEH.Person_id = elr.Person_id
            
                )
                
                
                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 pq.PersonQuarantine_id is not null 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 then 1 else 0 end as "needTestMenu",
                    elr.MedService_id as "MedService_id",
                    elr.MedService_sid as "MedService_sid",
                    msdid.MedService_Nick as "MedServiceDid_Nick",
                    mst.MedServiceType_SysNick as "MedServiceType_SysNick",
                    to_char(elr.EvnLabRequest_prmTime,'dd.mm.yyyy') as "TimetableMedService_Date",
                    elr.EvnLabRequest_RegNum as "EvnLabRequest_RegNum",
                    MP.Person_SurName as "EDMedPersonalSurname",
                    lpu.Lpu_Nick as "Lpu_Nick",
                    LSINFO.EvnLabSample_Containers as "EvnLabSample_Containers"
                    
                ,LpuSection.LpuSection_Name as "LpuSection_Name"
                ,MP.Person_SurName as "EDMedPersonalSurname"
                ,MP.Person_FirName as "EDMedPersonalFirname"
                ,MP.Person_SecName as "EDMedPersonalSecname"

            , ms.MedService_Nick as "MedService_Nick"
                    
                FROM
                
                    elr
                    inner join v_EvnDirection_all ed on elr.EvnDirection_id = ed.EvnDirection_id
                    left join lateral (
                        select PersonQuarantine_id
                        from v_PersonQuarantine PQ
                        where PQ.Person_id = elr.Person_id
                            and PQ.PersonQuarantine_endDT is null
                        limit 1
                    ) PQ 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 LpuSection.LpuSection_Code,
                                              LpuSection.LpuSection_Name
                                         from v_LpuSection LpuSection
                                        where LpuSection.LpuSection_id = ed.LpuSection_id
                                        limit 1) LpuSection on true
                    left join lateral (select Lpu.Lpu_Nick
                                         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 v_MedServiceType mst on mst.MedServiceType_id = ms.MedServiceType_id
                    left join v_MedService msdid on msdid.MedService_id = elr.MedService_sid
                    left join lateral(select ps.Person_Surname, ps.Person_FirName, ps.Person_SecName
                                        from dbo.v_MedPersonalCache mpc
                                             join dbo.v_PersonState ps on ps.Person_id=mpc.Person_id
                                       where mpc.MedPersonal_id = ed.MedPersonal_id
                                         and current_date between WorkData_begDate and coalesce(WorkData_endDate, current_date)
                                       limit 1) MP 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
                                    eup.uslugacomplex_id
                                from v_evnlabrequestuslugacomplex elruc
                                    inner join v_evnuslugapar eup on elruc.evnuslugapar_id = eup.evnuslugapar_id
                                where ls.evnlabsample_id = elruc.evnlabsample_id
                                limit 1
                            ) ELRUC on true
                            left join lateral (
                                select
                                    uslugacomplexmedservice_id
                                from v_uslugacomplexmedservice
                                where uslugacomplexmedservice_pid is null
                                    and uslugacomplex_id = elruc.uslugacomplex_id
                                    and medservice_id = ls.MedService_id
                                limit 1
                            ) ucmsp on true
                            left join lateral (
                                select
                                    ct.ContainerType_Name,
                                    ct.ContainerTypeColor_id,
                                    ct.ContainerKind_id
                                from v_UslugaComplexMedService ucms
                                    inner join v_RefSample rs on rs.RefSample_id = ucms.RefSample_id
                                    left join v_ContainerType ct on ct.ContainerType_id = rs.ContainerType_id
                                where ucms.uslugacomplexmedservice_pid = ucmsp.uslugacomplexmedservice_id
                                limit 1
                            ) ct on true
                        where ls.EvnLabRequest_id = elr.EvnLabRequest_id
                    ) LSINFO on true
                    
                    
                    
                     WHERE 
                  1=1
                  
                  and '864870' = ed.Lpu_sid
                    
                
                        ORDER BY
                            
                        "EvnDirection_id" asc
                    
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
