
                
                
                SELECT distinct
                count(1) over(partition by null order by null)                          as "_count",
                lij.LISIntegrationJournal_id                                            as "LISIntegrationJournal_id",
                lij.LisIntegrationJournal_ExportedDt                                    as "ExportedDt",
                lij.LISIntegrationSystem_id                                             as "LISIntegrationSystem_id",
                lijs.LISIntegrationJournalStatus_Name                                   as "LISIntegrationJournalStatus_Name",
                lij.LISIntegrationJournalStatus_id                                      as "LISIntegrationJournalStatus_id",
                lij.LISIntegrationJournal_Error                                         as "LISIntegrationJournal_Error",
                lij.EvnLabRequest_id                                                    as "EvnLabRequest_id",
                elr.MedService_id                                                       as "MedService_id",
                vEDa.EvnDirection_Num                                                   as "EvnDirection_Num",
                lijs.LISIntegrationJournalStatus_Code                                   as "LISIntegrationJournalStatus_Code",
                to_char(lij.LISIntegrationJournal_StatusDT, 'DD.MM.YYYY')               as "LISIntegrationJournal_StatusDT",
                case when lijs.LISIntegrationJournalStatus_Code <> 'New'
                     then to_char(lij.LISIntegrationJournal_NextStatusDT, 'DD.MM.YYYY')
                     else ''
                end                                                                     as "LISIntegrationJournal_NextStatusDT",
                to_char(ps.Person_BirthDay, 'DD.MM.YYYY')                               as "Person_BirthDay",
                coalesce(coalesce(ps.Person_SurName, '')
                    || coalesce(' ' || substring(ps.Person_FirName, 1, 1), '')
                    || coalesce(' ' || substring(ps.Person_SecName, 1, 1), ''),'')      as "Person_Fio",
                eup.EvnUslugaPar_setDT                                                  as "EvnUslugaPar_setDT",
                eup.EvnUslugaPar_insDT                                                  as "EvnUslugaPar_insDT",
                elr.EvnLabRequest_UslugaName                                            as "UslugaComplex_Name",
                resultvalue.result                                                      as "UslugaTest_ResultValues",
                research.code                                                           as "UslugaComplex_Codes",
                research.name                                                           as "UslugaComplex_Names",
                resultvalue.testsystem                                                  as "TestSystemNames"
                
                
                FROM
                
                dbo.v_EvnLabRequest elr
                join dbo.v_EvnDirection_all vEDa on vEDa.EvnDirection_id = elr.EvnDirection_id
                join dbo.v_LISIntegrationJournal lij on lij.EvnLabRequest_id = elr.EvnLabRequest_id
                join lateral (
                    select to_char(_eup.EvnUslugaPar_setDT, 'DD.MM.YYYY') as EvnUslugaPar_setDT,
                           to_char(_eup.EvnUslugaPar_insDT, 'DD.MM.YYYY') as EvnUslugaPar_insDT,
                            _eup.uslugacomplex_id,
                           _eup.Person_id
                      from dbo.v_EvnUslugaPar _eup
                     where elr.EvnDirection_id = _eup.EvnDirection_id
                     limit 1
                ) eup on true
                join dbo.v_LISIntegrationJournalStatus lijs
                    on lij.LISIntegrationJournalStatus_id = lijs.LISIntegrationJournalStatus_id
                left join dbo.v_LISIntegrationJournalStatus parentlijs
                    on parentlijs.LISIntegrationJournalStatus_id = lij.LISIntegrationJournalStatus_pid
                left join lateral (
                    select
                        string_agg(cast(coalesce(uc.UslugaComplex_Code, '') as varchar), '; ') as code,
                        string_agg(cast(coalesce(uc.UslugaComplex_Name, '') as varchar), '; ') as name
                    from v_EvnUslugaPar vEUP
                        left join dbo.v_UslugaComplex uc on vEUP.UslugaComplex_id = uc.UslugaComplex_id
                    where vEUP.EvnDirection_id = elr.EvnDirection_id
                        and exists (
                            select uca.UslugaComplex_id
                            from dbo.v_UslugaComplexAttribute uca
                                inner join dbo.v_UslugaComplexAttributeType ucat on
                                    uca.UslugaComplexAttributeType_id = ucat.UslugaComplexAttributeType_id
                            where uca.UslugaComplex_id = uc.UslugaComplex_id
                                and ucat.UslugaComplexAttributeType_SysNick
                                    in ('pcrcovid', 'abcovidigg', 'abcovidigm', 'abcovidsum')
                        )
                ) as research on true
                left join lateral (
                    select string_agg(cast(coalesce(ut.UslugaTest_ResultValue, '0') as varchar), '; ') as result,
                           string_agg(coalesce(
                           tsc.TestSystemCovid_Name,
                           atsl.AnalyzerTestSystemsLink_Name,
                           'Не задано'), ';') as testsystem
                      from v_EvnLabSample els
                           inner join v_UslugaTest ut on ut.evnlabsample_id = els.evnlabsample_id
                           left join dbo.v_UslugaComplex uc on ut.UslugaComplex_id = uc.UslugaComplex_id
                           left join lis.v_AnalyzerTestSystemsLink atsl
                                     on atsl.AnalyzerTestSystemsLink_id = ut.AnalyzerTestSystemsLink_id
                           left join lis.v_TestSystemCovid tsc on tsc.TestSystemCovid_id = atsl.TestSystemCovid_id
                     where els.EvnLabRequest_id = elr.EvnLabRequest_id
                       and exists (select uca.UslugaComplex_id
                                     from dbo.v_UslugaComplexAttribute uca
                                          inner join dbo.v_UslugaComplexAttributeType ucat on
                                                uca.UslugaComplexAttributeType_id = ucat.UslugaComplexAttributeType_id
                                    where uca.UslugaComplex_id = uc.UslugaComplex_id
                                      and ucat.UslugaComplexAttributeType_SysNick
                                            in ('pcrcovid', 'abcovidigg', 'abcovidigm', 'abcovidsum'))
                ) as resultvalue on true
                join lateral (
                    select Person_BirthDay, Person_SurName, Person_FirName, Person_SecName
                    from dbo.v_PersonState ps
                    where eup.Person_id = ps.Person_id limit 1
                ) ps on true
                
                 WHERE 
                (1=1) and lijs.LISIntegrationJournalStatus_id in (1, 2, 3, 4, 5)
                and research.code is not null
             and elr.MedService_id in (
                    SELECT
                        ms.MedService_id
                    FROM dbo.v_MedServiceLink msl
                        INNER JOIN dbo.v_MedService ms
                            on ms.MedService_id = msl.MedService_lid
                    WHERE msl.MedServiceLinkType_id = 2
                        and msl.MedService_id = '500801000001870'
                ) and cast(eup.EvnUslugaPar_setDT as date) = cast('2023-07-20' as date)
                
                
                        ORDER BY
                            
            lij.LISIntegrationJournal_id
            
                OFFSET 0 ROWS
                FETCH NEXT 50 ROWS ONLY
                
