
        -- TOP SL, FIXDATE 10.03.2023, JIRA PROMEDWEB-131105
            with eu as (
                select
                    EvnUsluga_id,
                    EvnClass_SysNick,
                    UslugaComplex_id,
                    EvnUsluga_pid,
                    EvnUsluga_setDate,
                    EvnUsluga_Result,
                    EvnUsluga_IsVizitCode,
                    Person_id,
                    Diag_id
                from v_EvnUsluga
                where
                    Person_id = '180101000101686'
                    and EvnUsluga_setDate is not null
                    and UslugaComplex_id is not null
            ),
            t as (
            with curDT as (select localtimestamp as curDate)
            select
                eu.EvnUsluga_id as EvnUsluga_id,
                eu.EvnClass_SysNick as EvnClass_SysNick,
                evn.EvnClass_SysNick as ParentClass_SysNick,
                eu.UslugaComplex_id,
                eu.Person_id as Person_id,
                to_char(eu.EvnUsluga_setDate, 'dd.mm.yyyy') as EvnUsluga_setDate,
                case
                    when
                        2 = 1 and
                        ucmdl.UslugaComplexMSEDiagLink_PeriodF is not null and
                        dateadd('month', cast(ucmdl.UslugaComplexMSEDiagLink_PeriodF as int), eu.EvnUsluga_setDate) <
                            '2023-07-06'
                        then 'false'
                    when
                        2 = 2 and
                        ucmdl.UslugaComplexMSEDiagLink_PeriodS is not null and
                        dateadd('month', cast(ucmdl.UslugaComplexMSEDiagLink_PeriodS as int), eu.EvnUsluga_setDate) <
                            '2023-07-06'
                        then 'false'
                    else 'true'
                end as EvnUsluga_isActual,
                case
                    when uc.UslugaComplex_Code ilike '[0-9]%'
                        then coalesce(uc2011.UslugaComplex_Code, uc2004.UslugaComplex_Code, '') ||
                            ' ' || uc.UslugaComplex_Name
                    else uc.UslugaComplex_Code || ' ' || uc.UslugaComplex_Name
                end as UslugaComplex_Name,
                case
                    when ut.list is not null then 0
                    when ex1.EvnXml_id is not null then 1
                    when ex1.EvnXml_id is null and ex2.EvnXml_id is not null then 1
                    when ex1.EvnXml_id is null and ex2.EvnXml_id is null then 0
                    else 0
                end as isResultFromEvnXml_Data,
                case
                    when ut.list is not null then ut.list
                    when ex1.EvnXml_id is not null then ex1.EvnXml_Data::varchar
                    when ex1.EvnXml_id is null and ex2.EvnXml_id is not null then ex2.EvnXml_Data::varchar
                    when ex1.EvnXml_id is null and ex2.EvnXml_id is null then sr.StudyResult_Name
                    else eu.EvnUsluga_Result
                end as result,
                case
                    when eu.EvnUsluga_setDate is not null then 'true'
                    else 'false'
                end as EvnUsluga_isExec,
                ucmdl.UslugaComplexMSEDiagLink_id as UslugaComplexMSEDiagLink_id,
                ucmdl.UslugaComplexMSEDiagLink_Code as UslugaComplexMSEDiagLink_Code,
                case when uc.UslugaComplex_id = ucmdl.UslugaComplex_id then 1 else 0 end as isMain,
                0 as UslugaFromLis
            from
                eu
                inner join v_UslugaComplex uc on eu.UslugaComplex_id = uc.UslugaComplex_id
                left join v_UslugaComplex uc2011 on uc2011.UslugaComplex_id = uc.UslugaComplex_2011id
                left join v_UslugaComplex uc2004 on uc2004.UslugaComplex_id = uc.UslugaComplex_2004id
                left join v_Evn evn on evn.Evn_id = eu.EvnUsluga_pid
                left join lateral (
                    select
                        EvnXml_id,
                        EvnXml_Data
                    from dbo.v_EvnXml ex
                    where ex.Evn_id = eu.EvnUsluga_pid
                        AND eu.EvnUsluga_IsVizitCode = 2
                    order by EvnXml_insDT desc
                    limit 1
                ) ex1 on true
                left join lateral (
                    select
                        EvnXml_id,
                        EvnXml_Data
                    from dbo.v_EvnXml ex
                    where ex.Evn_id = eu.EvnUsluga_pid
                        AND eu.EvnUsluga_IsVizitCode <> 2
                    order by EvnXml_insDT desc
                    limit 1
                ) ex2 on true
                left join dbo.v_EvnUslugaPar eup on eup.EvnUslugaPar_id = eu.EvnUsluga_id
                left join dbo.v_StudyResult sr on sr.StudyResult_id = eup.StudyResult_id
                left join lateral (
                    select count(*) as uslugaTestsCount
                    from dbo.v_UslugaTest ut
                    where ut.EvnLabSample_id = eup.EvnLabSample_id
                ) uslugaTests on true
                left join lateral (
                    SELECT (
                       SELECT STRING_AGG(
                            (case
                                when uslugaTests.uslugaTestsCount = 1 and ut.UslugaComplex_id = uc.UslugaComplex_id
                                then ut.UslugaTest_ResultValue || ' ' || coalesce(ut.UslugaTest_ResultUnit, '')
                                else uc2.UslugaComplex_Name || ': ' || ut.UslugaTest_ResultValue || ' ' ||
                                    coalesce(ut.UslugaTest_ResultUnit, '')
                            end ),';')
                       FROM
                           dbo.v_UslugaTest ut
                           left join v_UslugaComplex uc2 on uc2.UslugaComplex_id = ut.UslugaComplex_id
                           where ut.EvnLabSample_id = eup.EvnLabSample_id
                    ) list
                ) ut on true
                left join lateral (
                    select
                        ucmdlw.UslugaComplexMSEDiagLink_id as UslugaComplexMSEDiagLink_id,
                        ucmdlw.UslugaComplexMSEDiagLink_PeriodF as UslugaComplexMSEDiagLink_PeriodF,
                        ucmdlw.UslugaComplexMSEDiagLink_PeriodS as UslugaComplexMSEDiagLink_PeriodS,
                        ucmdlw.UslugaComplex_id as UslugaComplex_id,
                        ucmdlw.UslugaComplexMSEDiagLink_Code as UslugaComplexMSEDiagLink_Code
                    from (
                        select
                            ucmdl.UslugaComplexMSEDiagLink_id,
                            ucmdl.UslugaComplexMSEDiagLink_PeriodF,
                            ucmdl.UslugaComplexMSEDiagLink_PeriodS,
                            ucmdl.UslugaComplex_id,
                            ucmdl.UslugaComplex_id as UslugaComplex_lid,
                            ucmdl.UslugaComplexMSEDiagLink_Code
                        from v_UslugaComplexMSEDiagLink ucmdl
                        where
                            ucmdl.UslugaComplexMSEDiagLink_Code is not null
                            and ucmdl.Diag_id in ('3451','5393','3451','3451','3451','3451','3451')
                            and ucmdl.CureStandartAgeGroupType_id = 1
                        union all
                        select
                            ucmdl.UslugaComplexMSEDiagLink_id,
                            ucmdl.UslugaComplexMSEDiagLink_PeriodF,
                            ucmdl.UslugaComplexMSEDiagLink_PeriodS,
                            ucmdl.UslugaComplex_id,
                            ucrl.UslugaComplex_id as UslugaComplex_lid,
                            ucmdl.UslugaComplexMSEDiagLink_Code
                        from v_UslugaComplexMSEDiagLink ucmdl
                            inner join v_UslugaComplexReplacementLink ucrl on
                                ucrl.UslugaComplexMSEDiagLink_id = ucmdl.UslugaComplexMSEDiagLink_id
                        where
                            ucmdl.UslugaComplexMSEDiagLink_Code is not null
                            and ucmdl.Diag_id in ('3451','5393','3451','3451','3451','3451','3451')
                            and ucmdl.CureStandartAgeGroupType_id = 1
                    ) ucmdlw
                    where
                        ucmdlw.UslugaComplex_lid = eu.UslugaComplex_id
                    limit 1
                ) ucmdl on true
                where
                ucmdl.UslugaComplexMSEDiagLink_id is not null 
            )

            select
                EvnUsluga_id as "EvnUsluga_id",
                UslugaComplex_id as "UslugaComplex_id",
                EvnClass_SysNick as "EvnClass_SysNick",
                ParentClass_SysNick as "ParentClass_SysNick",
                Person_id as "Person_id",
                EvnUsluga_setDate as "EvnUsluga_setDate",
                UslugaComplexMSEDiagLink_id as "UslugaComplexMSEDiagLink_id",
                UslugaComplex_Name as "UslugaComplex_Name",
                EvnUsluga_isExec as "EvnUsluga_isExec",
                isResultFromEvnXml_Data as "isResultFromEvnXml_Data",
                result as "result",
                isMain as "isMain",
                EvnUsluga_isActual as "EvnUsluga_isActual",
                UslugaFromLis as "UslugaFromLis",
                case when UslugaComplexMSEDiagLink_id is not null then 'true' else 'false' end as "isRecommended",
                UslugaComplexMSEDiagLink_Code as "UslugaComplexMSEDiagLink_Code"
            from t
        where
                isMain = 1 or not exists (
                    select
                        tmp.EvnUsluga_id as EvnUsluga_id
                    from t tmp
                    where
                        t.UslugaComplexMSEDiagLink_id = tmp.UslugaComplexMSEDiagLink_id
                        and tmp.isMain = 1
                    limit 1
                ) 
            order by UslugaComplex_id, EvnUsluga_setDate
        
