
            with cte as (select dbo.tzGetDate() as curdate)
            select
                EPLD.UslugaComplex_id as "UslugaComplex_id",
                UC.UslugaComplex_Name as "UslugaComplex_Name",
                ucms.UslugaComplexMedService_id as "UslugaComplexMedService_pid",
                CAST(etr.checked as varchar) as checked,
                EP.MedService_id as "MedService_id",
                EP.EvnPrescr_id as "EvnPrescr_id"
            from v_EvnPrescr EP

                inner join EvnPrescrLabDiag EPLD  on EPLD.Evn_id = EP.EvnPrescr_id

                left join v_UslugaComplex UC  on UC.UslugaComplex_id = EPLD.UslugaComplex_id

                left JOIN v_UslugaComplexMedService ucms on ucms.UslugaComplex_id = EPLD.UslugaComplex_id
                    AND ucms.MedService_id = coalesce('13729', NULL, EP.MedService_id)
                    AND ucms.UslugaComplexMedService_pid IS NULL
                	and ucms.UslugaComplexMedService_begDT <= (select curdate from cte)
                    and (ucms.UslugaComplexMedService_endDT is null or ucms.UslugaComplexMedService_endDT > (select curdate from cte))
                left join lateral (
                        select
                            string_agg(coalesce(CAST(UC.UslugaComplex_id as VARCHAR),''), ',') as checked
                        from v_UslugaComplexMedService ucmsTemp
                        inner join v_UslugaComplex UC on ucmsTemp.UslugaComplex_id = UC.UslugaComplex_id
                        inner join lateral (
                            select
                                at_child.AnalyzerTest_SortCode,
                                at_child.AnalyzerTest_id,
                                coalesce(at_child.AnalyzerTest_SysNick, uc.UslugaComplex_Name) as AnalyzerTest_SysNick
                            from
                                lis.v_AnalyzerTest at_child
                                inner join lis.v_AnalyzerTest at on at.AnalyzerTest_id = at_child.AnalyzerTest_pid
                                inner join lis.v_Analyzer a on a.Analyzer_id = at.Analyzer_id
                                left join v_UslugaComplex uc on uc.UslugaComplex_id = at_child.UslugaComplex_id
                            where
                                at_child.UslugaComplexMedService_id = ucmsTemp.UslugaComplexMedService_id
                                and at.UslugaComplexMedService_id = ucmsTemp.UslugaComplexMedService_pid
                                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_endDT >= (select curdate from cte) or at_child.AnalyzerTest_endDT is null)
                                and (uc.UslugaComplex_endDT >= (select curdate from cte) or uc.UslugaComplex_endDT is null)
                                and UC.uslugacomplex_id in (
                                    select
                                        epldu.UslugaComplex_id
                                    from
                                        v_EvnPrescrLabDiag epld_set
                                        -- состав услуги, если услуга комплексная
                                        left join v_EvnPrescrLabDiagUsluga epldu on epldu.EvnPrescrLabDiag_id = epld_set.EvnPrescrLabDiag_id
                                    WHERE
                                        epld_set.EvnPrescrLabDiag_id = EPLD.Evn_id
                                )
                            order by coalesce(at_child.AnalyzerTest_SortCode, 999999999)
                            limit 1
                        ) ATEST on true -- фильтрация услуг по активности тестов связанных с ними
                        where ucmsTemp.UslugaComplexMedService_pid = ucms.UslugaComplexMedService_id
                        group by atest.analyzertest_sortcode
                        order by coalesce(ATEST.AnalyzerTest_SortCode, 999999999)
                    ) etr on true
            where
                EP.EvnPrescr_pid  = '500801013281469'
                and EP.EvnPrescr_id != '500801016503219'
                and EP.PrescriptionType_id = 11
                and EP.PrescriptionStatusType_id != 3
                and not exists (
                    Select epd.EvnDirection_id
                    from v_EvnPrescrDirection epd

                    --inner join v_EvnDirection_all ED  on epd.EvnDirection_id = ED.EvnDirection_id

                    where epd.EvnPrescr_id = EP.EvnPrescr_id
                    --and  COALESCE(ED.EvnStatus_id, 16) not in (12,13)
                    limit 1

                )
                 AND EPLD.UslugaComplex_id IN (201519,201528,202284,202289,202290,202302,206892,206893,223812,223929,223930,224033,224205,224206,224210,224645,3000911,3000926,3000927,3000928,3000931,3000932,3000935,3003734,3003735,3003768,3003769,3003771,3003772,3003778,3003779,3003785,3003786,3003798,3003799,3003800,3003822,9990005642) 
                and etr.checked IS NOT NULL
        
