

            with myvars as (
            	select dbo.tzgetdate() as curdate
            )
            
                    ,elruc as (
                        select
                            elruc.UslugaComplex_id,
                            elruc.EvnLabSample_id,
                            elruc.EvnUslugaPar_id
                        from
                            v_EvnLabRequestUslugaComplex elruc
                            inner join v_EvnLabRequest elr on elr.EvnLabRequest_id = elruc.EvnLabRequest_id
                        where
                            elr.EvnDirection_id = '890101029878063'
                    )
                

            Select
                uc.UslugaComplex_Code as "UslugaComplex_Code",
                uc.UslugaComplex_id as "UslugaComplex_id",
                els.EvnLabSample_id as "EvnLabSample_id",
                ut.UslugaTest_id as "UslugaTest_id",
                ut.UslugaTest_Result as "UslugaTest_Result",
                ut.RefValues_id as "RefValues_id",
                ut.Unit_id as "Unit_id",
                rv.RefValues_Name || COALESCE(' (' || analyzer.Analyzer_Name || ')','') as "RefValues_Name",
                COALESCE(ut.UslugaTest_ResultLower,'') || ' - ' || COALESCE(ut.UslugaTest_ResultUpper,'') as "UslugaTest_ResultNorm",
                COALESCE(ut.UslugaTest_ResultLowerCrit,'') || ' - ' || COALESCE(ut.UslugaTest_ResultUpperCrit,'') as "UslugaTest_ResultCrit",
                ut.UslugaTest_ResultLower as "UslugaTest_ResultLower",
                ut.UslugaTest_ResultUpper as "UslugaTest_ResultUpper",
                ut.UslugaTest_ResultQualitativeNorms as "UslugaTest_ResultQualitativeNorms",
                trim(ut.UslugaTest_ResultLowerCrit) as "UslugaTest_ResultLowerCrit",
                trim(ut.UslugaTest_ResultUpperCrit) as "UslugaTest_ResultUpperCrit",
                ut.UslugaTest_ResultValue as "UslugaTest_ResultValue",
                ut.UslugaTest_ResultUnit as "UslugaTest_ResultUnit",
                ut.UslugaTest_Comment as "UslugaTest_Comment",
                ut.AnalyzerTestComment_id as "AnalyzerTestComment_id",
                ut.UslugaTest_Interpret as "UslugaTest_Interpret",
                els.EvnLabRequest_id as "EvnLabRequest_id",
                to_char(elr.EvnLabRequest_PlanDT, 'HH24:MI DD.MM.YYYY') as "EvnLabRequest_PlanDT",
                case when 
                    uc.UslugaComplex_id IN (
                        select
                            elruc.UslugaComplex_id
                        from
                            elruc
                        where
                            COALESCE(elruc.EvnLabSample_id, els.EvnLabSample_id) = els.EvnLabSample_id
                            and COALESCE(elruc.EvnUslugaPar_id, eup.EvnUslugaPar_id) = eup.EvnUslugaPar_id
                    )
                 then 2 else 1 end as "inPrescr",
                case
                    when ut.UslugaTest_ResultApproved = 2 then 'Одобрен'
                    when ut.UslugaTest_ResultValue is not null and rtrim(ut.UslugaTest_ResultValue) <> '' then 'Выполнен'
                    when (ut.UslugaTest_id is not null or 
                    uc.UslugaComplex_id IN (
                        select
                            elruc.UslugaComplex_id
                        from
                            elruc
                        where
                            COALESCE(elruc.EvnLabSample_id, els.EvnLabSample_id) = els.EvnLabSample_id
                            and COALESCE(elruc.EvnUslugaPar_id, eup.EvnUslugaPar_id) = eup.EvnUslugaPar_id
                    )
                ) then 'Назначен'
                    else 'Не назначен'
                end as "UslugaTest_Status",
                case
                    when ut.UslugaTest_ResultApproved = 2 then 0
                    when ut.UslugaTest_ResultValue is not null and rtrim(ut.UslugaTest_ResultValue) <> '' then 0
                    when (ut.UslugaTest_id is not null or 
                    uc.UslugaComplex_id IN (
                        select
                            elruc.UslugaComplex_id
                        from
                            elruc
                        where
                            COALESCE(elruc.EvnLabSample_id, els.EvnLabSample_id) = els.EvnLabSample_id
                            and COALESCE(elruc.EvnUslugaPar_id, eup.EvnUslugaPar_id) = eup.EvnUslugaPar_id
                    )
                ) then 0
                    else 1
                end as "SortStatus",
                COALESCE(analyzertest.AnalyzerTest_SortCode, analyzertest2.AnalyzerTest_SortCode, 999999999) as "AnalyzerTest_SortCode",
                to_char (ut.UslugaTest_setDT, 'HH24:MI DD.MM.YYYY') as "UslugaTest_setDT",
                ut.UslugaTest_ResultApproved as "UslugaTest_ResultApproved",
                to_char (ut.UslugaTest_CheckDT, 'HH24:MI DD.MM.YYYY') as "UslugaTest_CheckDT",
                coalesce(analyzertest.AnalyzerTest_SysNick, analyzertest.UslugaComplex_Name, uc.UslugaComplex_Name) as "UslugaComplex_Name",
                uc.UslugaComplex_ACode as "UslugaComplex_ACode",
                els.EvnLabSample_Comment as "EvnLabSample_Comment",
                dct.DefectCauseType_Name as "DefectCauseType_Name",
                COALESCE(analyzertest.UslugaComplex_ParentName, ucp.UslugaComplex_Name) as "ResearchName",
                eup.EvnUslugaPar_id as "UslugaTest_pid",
                eup.UslugaComplex_id as "UslugaComplexTarget_id",
                eup.EvnUslugaPar_Comment as "EvnUslugaPar_pComment",
                a.Analyzer_IsAutoOk as "Analyzer_IsAutoOk",
                a.Analyzer_IsAutoGood as "Analyzer_IsAutoGood",
                a.Analyzer_IsUseAutoReg as "Analyzer_IsUseAutoReg",
                a.Analyzer_Code as "Analyzer_Code",
                els.Analyzer_id as "Analyzer_id",
                analyzertest.Analyzer_isFEK as "Analyzer_isFEK",
                analyzertest.AnalyzerTestType_id as "AnalyzerTestType_id",
                analyzertest.AnalyzerTest_id as "AnalyzerTest_id",
                analyzertest.LabTest_id as "LabTest_id",
                case when FEK.FEKcounter > 0 then 2 else 1 end as "UslugaComplex_isFEK",
                lss.LabSampleStatus_SysNick as "LabSampleStatus_SysNick",
                (
                select
                    count(vels.*)
                from
                    v_EvnLabSample vels
                inner join v_UslugaTest vut on
                    vels.EvnLabSample_id = vut.EvnLabSample_id
                where
                    vut.UslugaTest_CheckDT is not null
                    and vut.UslugaComplex_id = ut.UslugaComplex_id
                    and vels.Person_id = els.person_id
                ) as "UslugaTest_Count",
    			eup.EvnUslugaPar_IsSigned as "EvnUslugaPar_IsSigned",
                analyzertest.UslugaComplexMedService_IsSeparateSample as "IsSeparateSample"
            From
                v_EvnLabSample els
                inner join v_UslugaTest ut on ut.UslugaTest_id in (0)
                inner join v_EvnUslugaPar eup on ut.UslugaTest_pid = eup.EvnUslugaPar_id and eup.EvnDirection_id is not null
                left join v_UslugaComplex ucp on ucp.UslugaComplex_id = eup.UslugaComplex_id
                left join v_UslugaComplex uc on ut.UslugaComplex_id = uc.UslugaComplex_id
                left join v_RefValues rv on rv.RefValues_id = ut.RefValues_id
                left join lis.v_DefectCauseType dct on dct.DefectCauseType_id = els.DefectCauseType_id
                left join lis.v_Analyzer a on a.Analyzer_id = els.Analyzer_id
                left join v_LabSampleStatus lss on els.LabSampleStatus_id = lss.LabSampleStatus_id
                left join v_EvnLabRequest elr on els.EvnLabRequest_id = elr.EvnLabRequest_id
                left join lateral(
                    select
                        at_child.AnalyzerTest_id,
                        at_child.AnalyzerTest_SysNick,
                        at_child.AnalyzerTest_SortCode,
                        at_child.LabTest_id,
                        uctest.UslugaComplex_Name,
                        ucms_parent.UslugaComplex_Name as UslugaComplex_ParentName,
                        a.Analyzer_isFEK,
                        ucms_at.UslugaComplexMedService_IsSeparateSample,
                        at_child.AnalyzerTestType_id
                    from
                        lis.v_AnalyzerTest at_child
                        left join lis.v_AnalyzerTest at on at.AnalyzerTest_id = at_child.AnalyzerTest_pid
                        inner join v_UslugaComplexMedService ucms_at on at_child.UslugaComplexMedService_id = ucms_at.UslugaComplexMedService_id
                        left join v_UslugaComplexMedService ucms_parent on ucms_parent.UslugaComplexMedService_id = COALESCE(ucms_at.UslugaComplexMedService_pid, ucms_at.UslugaComplexMedService_id) -- родительская услуга, если есть
                        inner join lis.v_Analyzer a on a.Analyzer_id = COALESCE(at.Analyzer_id, at_child.Analyzer_id)
                        left join v_UslugaComplex uctest on uctest.UslugaComplex_id = at_child.UslugaComplex_id
                    where
                        ucms_at.UslugaComplex_id = ut.UslugaComplex_id
                        and ucms_parent.UslugaComplex_id = eup.UslugaComplex_id
                        and ucms_at.MedService_id = els.MedService_id
                        and (at.analyzertest_isnotactive is null or at.analyzertest_isnotactive = 1)
                        and (a.analyzer_isnotactive is null or a.analyzer_isnotactive = 1)
                        and at_child.analyzertest_istest = 2
                        and (at.AnalyzerTest_endDT >= (select curdate from myvars) or at.AnalyzerTest_endDT is null)
                        and (at_child.AnalyzerTest_endDT >= (select curdate from myvars) or at_child.AnalyzerTest_endDT is null)
                        and (uctest.UslugaComplex_endDT >= (select curdate from myvars) or uctest.UslugaComplex_endDT is null)
                    order by coalesce(at_child.AnalyzerTest_SortCode, 999999999)
                    limit 1
                ) analyzertest on true
                left join lateral(
                    select
                        at_child.AnalyzerTest_id,
                        at_child.AnalyzerTest_SysNick,
                        at_child.AnalyzerTest_SortCode,
                        uctest.UslugaComplex_Name,
                        ucms_parent.UslugaComplex_Name as UslugaComplex_ParentName
                    from
                        lis.v_AnalyzerTest at_child
                        inner join lis.v_Analyzer a  on a.Analyzer_id = at_child.Analyzer_id
                        inner join v_UslugaComplexMedService ucms_at  on at_child.UslugaComplexMedService_id = ucms_at.UslugaComplexMedService_id
                        left join v_UslugaComplexMedService ucms_parent  on ucms_parent.UslugaComplexMedService_id = coalesce(ucms_at.UslugaComplexMedService_pid, ucms_at.UslugaComplexMedService_id)
                        left join v_UslugaComplex uctest  on at_child.UslugaComplex_id = uctest.UslugaComplex_id
                    where
                        at_child.Analyzer_id = els.Analyzer_id
                        and (at_child.AnalyzerTest_endDT > (select curdate from myvars) or at_child.AnalyzerTest_endDT is null)
                        and at_child.UslugaComplex_id = ut.UslugaComplex_id
                    order by coalesce(at_child.AnalyzerTest_SortCode, 999999999)
                    limit 1
                ) analyzertest2 on true
                left join lateral(
                    select count(uca.UslugaComplex_id) as FEKcounter
                    from v_UslugaComplexAttribute uca
                    inner join v_UslugaComplexAttributeType ucat on ucat.UslugaComplexAttributeType_id = uca.UslugaComplexAttributeType_id
                    where uca.UslugaComplex_id = uc.UslugaComplex_id and ucat.UslugaComplexAttributeType_SysNick = 'FEKcounter'
                ) FEK on true
                left join lateral(
                    select
                        Analyzer_Name
                    from
                        lis.v_Analyzer a
                        inner join lis.v_AnalyzerTest at on at.Analyzer_id = a.Analyzer_id
                        inner join lis.v_AnalyzerTestRefValues atrv on atrv.AnalyzerTest_id = at.AnalyzerTest_id
                    where
                        atrv.RefValues_id = ut.RefValues_id
                    limit 1
                ) analyzer on true
            where
                els.EvnLabSample_id = '890101029878065'
                

            
            union all

            Select distinct
                uc.UslugaComplex_Code as "UslugaComplex_Code",
                uc.UslugaComplex_id as "UslugaComplex_id",
                els.EvnLabSample_id as "EvnLabSample_id",
                ut.UslugaTest_id as "UslugaTest_id",
                ut.UslugaTest_Result as "UslugaTest_Result",
                ut.RefValues_id as "RefValues_id",
                ut.Unit_id as "Unit_id",
                rv.RefValues_Name || COALESCE(' (' || analyzer.Analyzer_Name || ')','') as "RefValues_Name",
                COALESCE(ut.UslugaTest_ResultLower,'') || ' - ' || COALESCE(ut.UslugaTest_ResultUpper,'') as "UslugaTest_ResultNorm",
                COALESCE(ut.UslugaTest_ResultLowerCrit,'') || ' - ' || COALESCE(ut.UslugaTest_ResultUpperCrit,'') as "UslugaTest_ResultCrit",
                ut.UslugaTest_ResultLower as "UslugaTest_ResultLower",
                ut.UslugaTest_ResultUpper as "UslugaTest_ResultUpper",
                ut.UslugaTest_ResultQualitativeNorms as "UslugaTest_ResultQualitativeNorms",
                ut.UslugaTest_ResultLowerCrit as "UslugaTest_ResultLowerCrit",
                ut.UslugaTest_ResultUpperCrit as "UslugaTest_ResultUpperCrit",
                ut.UslugaTest_ResultValue as "UslugaTest_ResultValue",
                ut.UslugaTest_ResultUnit as "UslugaTest_ResultUnit",
                ut.UslugaTest_Comment as "UslugaTest_Comment",
                ut.AnalyzerTestComment_id as "AnalyzerTestComment_id",
                ut.UslugaTest_Interpret as "UslugaTest_Interpret",
                els.EvnLabRequest_id as "EvnLabRequest_id",
                to_char(elr.EvnLabRequest_PlanDT, 'HH24:MI DD.MM.YYYY') as "EvnLabRequest_PlanDT",
                case when 
                    uc.UslugaComplex_id IN (
                        select
                            elruc.UslugaComplex_id
                        from
                            elruc
                        where
                            COALESCE(elruc.EvnLabSample_id, els.EvnLabSample_id) = els.EvnLabSample_id
                            and COALESCE(elruc.EvnUslugaPar_id, eup.EvnUslugaPar_id) = eup.EvnUslugaPar_id
                    )
                 then 2 else 1 end as "inPrescr",
                case
                    when ut.UslugaTest_ResultApproved = 2 then 'Одобрен'
                    when ut.UslugaTest_ResultValue is not null and rtrim(ut.UslugaTest_ResultValue) <> '' then 'Выполнен'
                    when (ut.UslugaTest_id is not null or 
                    uc.UslugaComplex_id IN (
                        select
                            elruc.UslugaComplex_id
                        from
                            elruc
                        where
                            COALESCE(elruc.EvnLabSample_id, els.EvnLabSample_id) = els.EvnLabSample_id
                            and COALESCE(elruc.EvnUslugaPar_id, eup.EvnUslugaPar_id) = eup.EvnUslugaPar_id
                    )
                ) then 'Назначен'
                    else 'Не назначен'
                end as "UslugaTest_Status",
                case
                    when ut.UslugaTest_ResultApproved = 2 then 0
                    when ut.UslugaTest_ResultValue is not null and rtrim(ut.UslugaTest_ResultValue) <> '' then 0
                    when (ut.UslugaTest_id is not null or 
                    uc.UslugaComplex_id IN (
                        select
                            elruc.UslugaComplex_id
                        from
                            elruc
                        where
                            COALESCE(elruc.EvnLabSample_id, els.EvnLabSample_id) = els.EvnLabSample_id
                            and COALESCE(elruc.EvnUslugaPar_id, eup.EvnUslugaPar_id) = eup.EvnUslugaPar_id
                    )
                ) then 0
                    else 1
                end as "SortStatus",
                COALESCE(analyzertest.AnalyzerTest_SortCode, 999999999) as "AnalyzerTest_SortCode",
                to_char (ut.UslugaTest_setDT, 'HH24:MI DD.MM.YYYY') as "UslugaTest_setDT",
                ut.UslugaTest_ResultApproved as "UslugaTest_ResultApproved",
                to_char (ut.UslugaTest_CheckDT, 'HH24:MI DD.MM.YYYY') as "UslugaTest_CheckDT",
                coalesce(analyzertest.AnalyzerTest_SysNick, analyzertest.UslugaComplex_Name, uc.UslugaComplex_Name) as "UslugaComplex_Name",
                uc.UslugaComplex_ACode as "UslugaComplex_ACode",
                '' as "EvnLabSample_Comment",
                '' as "DefectCauseType_Name",
                COALESCE(ucms_usluga_parent.UslugaComplex_Name, ucp.UslugaComplex_Name) as "ResearchName",
                eup.EvnUslugaPar_id as "UslugaTest_pid",
                eup.UslugaComplex_id as "UslugaComplexTarget_id",
                eup.EvnUslugaPar_Comment as "EvnUslugaPar_pComment",
                analyzertest.Analyzer_IsAutoOk as "Analyzer_IsAutoOk",
                analyzertest.Analyzer_IsAutoGood as "Analyzer_IsAutoGood",
                analyzertest.Analyzer_IsUseAutoReg as "Analyzer_IsUseAutoReg",
                analyzertest.Analyzer_Code as "Analyzer_Code",
                els.Analyzer_id as "Analyzer_id",
                analyzertest.Analyzer_isFEK as "Analyzer_isFEK",
                analyzertest.AnalyzerTestType_id as "AnalyzerTestType_id",
                analyzertest.AnalyzerTest_id as "AnalyzerTest_id",
                analyzertest.LabTest_id as "LabTest_id",
                case when FEK.FEKcounter > 0 then 2 else 1 end as "UslugaComplex_isFEK",
                lss.LabSampleStatus_SysNick as "LabSampleStatus_SysNick",
                (
                select
                    count(vels.*)
                from
                    v_EvnLabSample vels
                inner join v_UslugaTest vut on
                    vels.EvnLabSample_id = vut.EvnLabSample_id
                where
                    vut.UslugaTest_CheckDT is not null
                    and vut.UslugaComplex_id = ut.UslugaComplex_id
                    and vels.Person_id = els.person_id
                ) as "UslugaTest_Count",
    			eup.EvnUslugaPar_IsSigned as "EvnUslugaPar_IsSigned",
                ucms_usluga.UslugaComplexMedService_IsSeparateSample as "IsSeparateSample"
            From
                v_EvnLabSample els
                inner join v_EvnUslugaPar eup on eup.EvnUslugaPar_id in (890101029878066)
                    and eup.EvnDirection_id is not null
                left join v_UslugaComplex ucp on ucp.UslugaComplex_id = eup.UslugaComplex_id
                inner join lateral(
                    select
                        uup.UslugaComplexMedService_id,
                        uup.UslugaComplex_id,
                        uup.UslugaComplex_Name
                    from UslugaComplexMedService uup
                    	inner join lis.v_AnalyzerTest at on at.UslugaComplexMedService_id = uup.UslugaComplexMedService_id
                    where
                        eup.UslugaComplex_id = uup.UslugaComplex_id
                        and uup.MedService_id = els.MedService_id
                        and uup.UslugaComplexMedService_pid is null
                        and COALESCE(at.AnalyzerTest_IsNotActive, 1) = 1
                        and (at.AnalyzerTest_endDT >= (select curdate from myvars) or at.AnalyzerTest_endDT is null)
                    limit 1
                ) ucms_usluga_parent on true
                inner join v_UslugaComplexMedService ucms_usluga on ucms_usluga.UslugaComplexMedService_pid = ucms_usluga_parent.UslugaComplexMedService_id
                left join v_UslugaComplex uc on COALESCE(ucms_usluga.UslugaComplex_id, ucp.UslugaComplex_id, ucms_usluga_parent.UslugaComplex_id) = uc.UslugaComplex_id
                left join v_UslugaTest ut on ut.UslugaTest_pid = eup.EvnUslugaPar_id
                    and ut.UslugaComplex_id = uc.UslugaComplex_id
                    and ut.EvnLabSample_id = els.EvnLabSample_id -- Результат по пробе
                left join v_RefValues rv on rv.RefValues_id = ut.RefValues_id
                left join v_LabSampleStatus lss on els.LabSampleStatus_id = lss.LabSampleStatus_id
                left join v_EvnLabRequest elr on els.EvnLabRequest_id = elr.EvnLabRequest_id
                left join lateral(
                    select
                        at_child.AnalyzerTest_id,
                        at_child.AnalyzerTest_SysNick,
                        at_child.AnalyzerTest_SortCode,
                        at_child.LabTest_id,
                        uctest.UslugaComplex_Name,
                        a.Analyzer_IsAutoOk,
                        a.Analyzer_IsAutoGood,
                        a.Analyzer_Code,
                        a.Analyzer_IsUseAutoReg,
                        a.Analyzer_isFEK,
                        at_child.AnalyzerTestType_id
                    from
                        lis.v_AnalyzerTest at_child
                        left join lis.v_AnalyzerTest at on at.AnalyzerTest_id = at_child.AnalyzerTest_pid
                        inner join lis.v_Analyzer a on a.Analyzer_id = COALESCE(at.Analyzer_id, at_child.Analyzer_id)
                        left join v_UslugaComplex uctest on uctest.UslugaComplex_id = at_child.UslugaComplex_id
                    where
                        at_child.UslugaComplexMedService_id = COALESCE(ucms_usluga.UslugaComplexMedService_id, ucms_usluga_parent.UslugaComplexMedService_id)
                        and COALESCE(at.UslugaComplexMedService_id, 0) = COALESCE(ucms_usluga.UslugaComplexMedService_pid, 0)
                        and COALESCE(at_child.AnalyzerTest_IsNotActive, 1) = 1
                        and COALESCE(at.AnalyzerTest_IsNotActive, 1) = 1
                        and COALESCE(a.Analyzer_IsNotActive, 1) = 1
                        and at_child.analyzertest_istest = 2
                        and (at_child.AnalyzerTest_endDT >= (select curdate from myvars) or at_child.AnalyzerTest_endDT is null)
                        and (uctest.UslugaComplex_endDT >= (select curdate from myvars) or uctest.UslugaComplex_endDT is null)
                    order by coalesce(at_child.AnalyzerTest_SortCode, 999999999)
                    limit 1
                ) analyzertest on true
                left join lateral(
                    select
                        Analyzer_Name
                    from
                        lis.v_Analyzer a
                        inner join lis.v_AnalyzerTest at on at.Analyzer_id = a.Analyzer_id
                        inner join lis.v_AnalyzerTestRefValues atrv on atrv.AnalyzerTest_id = at.AnalyzerTest_id
                    where
                        atrv.RefValues_id = ut.RefValues_id
                    limit 1
                ) analyzer on true
                left join lateral(
                    select count(uca.UslugaComplex_id) as FEKcounter
                    from v_UslugaComplexAttribute uca
                    inner join v_UslugaComplexAttributeType ucat on ucat.UslugaComplexAttributeType_id = uca.UslugaComplexAttributeType_id
                    where uca.UslugaComplex_id = uc.UslugaComplex_id and ucat.UslugaComplexAttributeType_SysNick = 'FEKcounter'
                ) FEK on true
            where
                els.EvnLabSample_id = '890101029878065' and ut.UslugaTest_id is null
                and (
                    analyzertest.AnalyzerTest_id is not null
                    or exists (
                        select
                            EvnLabRequestUslugaComplex_id
                        from
                            v_EvnLabRequestUslugaComplex elruc_child
                        where
                            elruc_child.EvnUslugaPar_id = eup.EvnUslugaPar_id
                            and elruc_child.EvnLabSample_id = els.EvnLabSample_id
                            and elruc_child.UslugaComplex_id = uc.UslugaComplex_id
                        limit 1
                    )
                ) -- назначен или действует на дату
                
            

            order by
                "SortStatus", "AnalyzerTest_SortCode", "UslugaComplex_Name"
        
