
            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('13951', 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  = '500801016279062'
                and EP.EvnPrescr_id != '500801016517863'
                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 (201298,201313,201519,201520,201524,201525,201526,201527,201528,201534,201535,201537,201538,201540,201541,201545,201546,201548,201550,201551,201552,201553,201554,201565,201567,201570,201571,201572,201573,201575,201576,201577,201580,201607,201614,201664,201711,201716,201883,201887,201891,201892,201896,201898,201899,201902,201909,201914,201922,202289,202290,202295,202303,202314,202315,202330,202426,202430,206351,206840,206842,206892,206893,206894,206896,207302,207304,207310,207311,207318,207326,207341,207360,207374,207378,207460,223777,223787,223789,223811,223812,223856,223858,223861,223909,223953,224021,224028,224041,224201,224202,224203,224204,224205,224206,224207,224211,224214,224216,224217,224219,224220,224221,224224,224225,224226,224237,224264,224287,224300,224331,224646,224648,224649,224651,224652,224658,224829,224830,224846,224881,224944,225079,225081,225085,225090,225283,225393,225421,225423,225429,225437,225438,3000087,3000556,3000911,3000926,3000927,3000928,3000930,3000935,3001004,3001028,3001035,3003730,3003734,3003735,3003738,3003742,3003744,3003762,3003766,3003768,3003769,3003771,3003772,3003774,3003775,3003776,3003777,3003778,3003779,3003781,3003782,3003783,3003784,3003785,3003786,3003787,3003788,3003798,3003799,3003800,3003808,3003817,3003822,3003847,3003849,3003855,3003860,3003907,3003922,3003928,3003930,3003934,3003935,3003956,3003957,3003958,3003959,3003960,3003961,3003962,3003963,3003964,3003968,3003969,3003971,3003972,3003975,3003976,3003978,3003979,3003982,3003995,3003996,3003998,3003999,3004025,3004026,3004027,4577574,4581688,4619506,4625369,4625370,9990005642) 
                and etr.checked IS NOT NULL
        
