
                
                
            with table1 as (
                SELECT
                    NULL as TimetableResource_id,
                    ED.EvnDirection_id,
                    EPC.EvnClass_SysNick as parentEvnClass_SysNick,
                    null as EvnQueue_id,
                    EFR.EvnFuncRequest_id,
                    dbo.getPersonPhones(ed.Person_id, '<br />') as Person_Phone,
                    to_char(ED.EvnDirection_setDT, 'dd.mm.yyyy') as group_name,
                    case when 2 = coalesce(ED.EvnDirection_IsCito, 1) then 'true' else 'false' end as EvnDirection_IsCito,
                    to_char(ED.EvnDirection_setDT, 'dd.mm.yyyy') as EvnDirection_setDT,
                    ED.EvnDirection_Num,
                    efr.EvnFuncRequest_UslugaCache,
                    efr.EvnStatus_id,
                    efr.Person_id,
                    efr.pmUser_insID,
                    case
                        when PUC.PMUser_surName is null then ''
                        else PUC.PMUser_surName||' '||coalesce(left(PUC.PMUser_firName,1),'')||' '||coalesce(left(PUC.PMUser_secName,1),'')
                    end as Operator,
                    d.Diag_Code
                FROM
                    v_EvnFuncRequest EFR
                    inner JOIN v_EvnDirection_all ED ON ed.EvnDirection_id = EFR.EvnFuncRequest_pid and coalesce(ed.EvnStatus_id, 16) not in (12, 13)
                    left join lateral (select EvnClass_SysNick from v_Evn EPC where EPC.Evn_id = ED.EvnDirection_pid limit 1) EPC on true
                    LEFT JOIN v_pmUserCache PUC on PUC.PMUser_id = efr.pmUser_insID
                    left join v_Diag d on ed.Diag_id = d.Diag_id
                WHERE
                    efr.Lpu_id = '13101875'
                    and efr.MedService_id = '890101000000495'
                    and ED.TimetableResource_id is null -- без бирки
                    and ED.EvnQueue_id is null -- без очереди
                    and (CAST(efr.evnfuncrequest_setdt as date) between '2022-03-31' and '2022-03-31')
            ), table2 as (
                SELECT
                    ED.EvnDirection_id,
                    EPC.EvnClass_SysNick as parentEvnClass_SysNick,
                    ED.EvnQueue_id as EvnQueue_id,
                    EFR.EvnFuncRequest_id,
                    dbo.getPersonPhones(ed.Person_id, '<br />') as Person_Phone,
                    case when 2 = coalesce(ED.EvnDirection_IsCito, 1) then 'true' else 'false' end as EvnDirection_IsCito,
                    to_char(ED.EvnDirection_setDT, 'dd.mm.yyyy') as EvnDirection_setDT,
                    ED.EvnDirection_Num,
                    efr.EvnFuncRequest_UslugaCache,
                    ed.Lpu_sid,
                    ed.LpuSection_id,
                    ed.Person_id,
                    efr.EvnFuncRequest_pid as EvnFuncRequest_pid,
                    ed.pmUser_insID,
                    es.EvnStatus_SysNick,
                    efr.EvnFuncRequest_statusDate as EvnFuncRequest_statusDate,
                    case
                        when PUC.PMUser_surName is null then ''
                        else PUC.PMUser_surName||' '||coalesce(left(PUC.PMUser_firName,1),'')||' '||coalesce(left(PUC.PMUser_secName,1),'')
                    end as Operator,
                    d.Diag_Code
                FROM
                    v_EvnFuncRequest efr
                    inner join v_EvnDirection_all ed on ed.EvnDirection_id = EFR.EvnFuncRequest_pid and coalesce(ed.EvnStatus_id, 16) not in (12, 13)
                    left join v_EvnStatus ES on ES.EvnStatus_id = efr.EvnStatus_id
                    left join lateral (select EvnClass_SysNick from v_Evn EPC where EPC.Evn_id = ED.EvnDirection_pid limit 1) EPC on true
                    LEFT JOIN v_pmUserCache PUC on PUC.PMUser_id = ed.pmUser_insID
                    left join v_Diag d on ed.Diag_id = d.Diag_id
                WHERE
                    efr.MedService_id = '890101000000495'
                    and (
                efr.EvnStatus_id = (select EvnStatus_id from v_EvnStatus where EvnStatus_SysNick = 'FuncNew' limit 1)
                OR
                (ES.EvnStatus_SysNick IN ('FuncDonerec', 'FuncDone') and CAST(efr.evnfuncrequest_statusdate as date) between '2022-03-31' and '2022-03-31')
            )
            ),
            curDT as (select dbo.tzGetDate() as curDate)
            
                SELECT 
                TimetableResource_id as "TimetableResource_id",
                EvnDirection_id as "EvnDirection_id",
                parentEvnClass_SysNick as "parentEvnClass_SysNick",
                EvnQueue_id as "EvnQueue_id",
                EvnFuncRequest_id as "EvnFuncRequest_id",
                Person_id as "Person_id",
                PersonQuarantine_IsOn as "PersonQuarantine_IsOn",
                Person_FIO as "Person_FIO",
                Person_BirthDay as "Person_BirthDay",
                group_name as "group_name",
                TimetableResource_begDate as "TimetableResource_begDate",
                Resource_id as "Resource_id",
                Resource_Name as "Resource_Name",
                TimetableResource_begTime as "TimetableResource_begTime",
                TimetableResource_Type as "TimetableResource_Type",
                EvnDirection_IsCito as "EvnDirection_IsCito",
                EvnDirection_setDT as "EvnDirection_setDT",
                EvnDirection_Num as "EvnDirection_Num",
                EvnUslugaPar_id as "EvnUslugaPar_id",
                
                RCC_MedService_id as "RCC_MedService_id",
                RemoteConsultCenterResearch_id as "RemoteConsultCenterResearch_id",
                RemoteConsultCenterResearch_status as "RemoteConsultCenterResearch_status",
                FuncRequestState as "FuncRequestState",
                Operator as "Operator",
                EvnFuncRequest_UslugaCache as "EvnFuncRequest_UslugaCache",
                Lpu_Name as "Lpu_Name",
                LpuSection_Name as "LpuSection_Name",
                EvnCostPrint_PrintStatus as "EvnCostPrint_PrintStatus",
                PersonAmbulatCard_Num as "PersonAmbulatCard_Num",
                Diag_Code as "Diag_Code"
                
                FROM
                
                (
                select
                    TTR.TimetableResource_id,
                    table2.EvnDirection_id,
                    table2.parentEvnClass_SysNick,
                    table2.EvnQueue_id,
                    table2.EvnFuncRequest_id,
                    PS.Person_id,
                    case when exists(
                        select *
                        from v_PersonQuarantine PQ
                        where PQ.Person_id = PS.Person_id
                        and PQ.PersonQuarantine_endDT is null
                    ) then 'true' else 'false' end as PersonQuarantine_IsOn,
                    dbo.getPersonPhones(table2.Person_id, '<br />') as Person_Phone,
                rtrim(PS.Person_SurName) as Person_Surname,
                rtrim(PS.Person_FirName) as Person_Firname,
                rtrim(PS.Person_SecName) as Person_Secname,
                (PS.Person_SurName || ' ' || coalesce(PS.Person_FirName, '') || ' ' || coalesce(PS.Person_SecName,'')) as Person_FIO,
                to_char(PS.Person_BirthDay, 'dd.mm.yyyy') as Person_BirthDay,
                    null as group_name,
                    case
                        when table2.EvnStatus_SysNick = 'FuncDonerec' then to_char(cast(table2.EvnFuncRequest_statusDate as timestamp), 'dd.mm.yyyy')
                        else null
                    end as TimetableResource_begDate,
                    r.Resource_id,
                    r.Resource_Name,
                    'б/з' as TimetableResource_begTime,
                    null as TimetableResource_Type,
                    table2.EvnDirection_IsCito,
                    table2.EvnDirection_setDT,
                    table2.EvnDirection_Num,
                    EvnUslugaPar.EvnUslugaPar_id
                    ,
                    wls.WorkListStatus_Name,
                    MSL.MedService_id as RCC_MedService_id,
                    RCCR.RemoteConsultCenterResearch_id,
                    RCCR.RemoteConsultCenterResearch_status,
                    case when EUPAR.StatusResearch_Code in (4,5) and EUPAR.EvnUslugaParAssociatedResearches_limitDT < (select curDate from curDT)
                        then 1 else 0
                    end as "isExpired",
                    EUPAR.StatusResearch_Code as "StatusResearch_Code",
                    EUPAR.StatusResearch_Name as "StatusResearch_Name",
                    coalesce(EUPAR.RiskGroup_Code, 1) as "RiskGroup_Code",
                    EUPAR.RiskGroup_Name as "RiskGroup_Name",
                    EUPAR.Study_uid as "Study_uid",
                    EUPAR.LpuEquipmentPacs_id as "LpuEquipmentPacs_id",
                    to_char(EUPAR.EvnUslugaParAssociatedResearches_limitDT, 'DD.MM.YYYY HH24:MI') as "EvnUslugaParAssociatedResearches_limitDT",
                    case
                        when EUPAR.StatusResearch_Code = 7 or EUPAR.StatusResearch_Code = 6
                            then 'Выполнено'
                        when EUPAR.StatusResearch_Code = 4 or EUPAR.StatusResearch_Code = 5
                            then 'Очередь'
                    end as "ResearchStatusGroup",
                    case when table2.EvnStatus_SysNick = 'FuncDonerec' then 'true' else 'false' end as FuncRequestState,
                    Operator as Operator,
                    table2.EvnFuncRequest_UslugaCache,
                    LpuFrom.Lpu_Nick as Lpu_Name,
                    LpuSectionFrom.LpuSection_Name as LpuSection_Name,
                    --'' as EvnCostPrint_PrintStatus,
                    case when NoPrintCount.NPC > 0 then 'true' else 'false' end as EvnCostPrint_PrintStatus,
                    case when pac.PersonAmbulatCard_Num is null then '-' else pac.PersonAmbulatCard_Num end as PersonAmbulatCard_Num,
                    table2.Diag_Code
                FROM
                    table2 table2
                    inner join v_PersonState PS on PS.Person_id = table2.Person_id
                    left join v_TimetableResource_lite TTR on TTR.EvnDirection_id = table2.EvnDirection_id
                    left join v_Resource R on R.Resource_id = TTR.Resource_id
                    left join v_Lpu LpuFrom on LpuFrom.Lpu_id = table2.Lpu_sid
                    left join v_LpuSection LpuSectionFrom on LpuSectionFrom.LpuSection_id = table2.LpuSection_id
                    
                    left join lateral(
                        select ECP.EvnCostPrint_id as NPC
                        from v_EvnUslugaPar EUP
                        left join v_EvnCostPrint ECP on ECP.Evn_id = EUP.EvnUslugaPar_id
                        where EUP.EvnDirection_id = table2.EvnFuncRequest_pid
                        and coalesce(ECP.EvnCostPrint_IsNoPrint,0) <> 2
                        limit 1
                    ) NoPrintCount on true
                    left join lateral(
                        select EvnUslugaPar_id
                         
                         from v_EvnUslugaPar where EvnDirection_id = table2.EvnDirection_id limit 1
                    ) EvnUslugaPar on true
                    left join lateral(
                        select
                            SR.StatusResearch_Code,
                            SR.StatusResearch_Name,
                            RG.RiskGroup_Code,
                            RG.RiskGroup_Name,
                            EUPAR.Study_uid,
                            EUPAR.LpuEquipmentPacs_id,
                            EUPAR.EvnUslugaParAssociatedResearches_limitDT
                        from v_EvnUslugaParAssociatedResearches EUPAR
                        left join v_StatusResearch SR on SR.StatusResearch_id = EUPAR.StatusResearch_id
                        left join v_RiskGroup RG  on RG.RiskGroup_id = EUPAR.RiskGroup_id
                        where EUPAR.EvnUslugaPar_id = EvnUslugaPar.EvnUslugaPar_id
                        limit 1
                    ) EUPAR on true
                    LEFT JOIN v_RemoteConsultCenterResearch RCCR on RCCR.EvnUslugaPar_id = EvnUslugaPar.EvnUslugaPar_id
                    left join v_WorkListQueue wlq on EvnUslugaPar.EvnUslugaPar_id = wlq.EvnUslugaPar_id
                    left join v_WorkListStatus wls on wlq.WorkListStatus_id = wls.WorkListStatus_id
                    LEFT JOIN v_MedServiceLink MSL on MSL.MedService_lid = '890101000000495'
                    AND MSL.MedServiceLinkType_id = (select MSLT.MedServiceLinkType_id from v_MedServiceLinkType MSLT where MSLT.MedServiceLinkType_Code = '3' limit 1)
                    inner join lateral(SELECT 1 AS Eq from v_EvnQueue eq where eq.EvnDirection_id = table2.EvnDirection_id and eq.EvnQueue_failDT is null and eq.EvnQueue_recDT is NULL limit 1) AS EQ on true
                    left join lateral (
                        SELECT
                            PAC.PersonAmbulatCard_id,
                            PAC.PersonAmbulatCard_Num
                        FROM v_PersonAmbulatCard PAC
                        WHERE PAC.Person_id = table2.Person_id
                        ORDER BY PAC.PersonAmbulatCard_id DESC
                        limit 1
                    ) pac on true
                WHERE
                    r.Resource_Name is null
                    and TTR.TimetableResource_id is null
                    

                union all

                -- без направления (без EvnDirection, связь с услугой была по EvnFuncRequest_pid, но скриптом создали направления без объекта очереди и без объекта бирки...)
                select
                    NULL as TimetableResource_id,
                    table1.EvnDirection_id,
                    table1.parentEvnClass_SysNick,
                    null as EvnQueue_id,
                    table1.EvnFuncRequest_id,
                    PS.Person_id,
                    case when exists(
                        select *
                        from v_PersonQuarantine PQ
                        where PQ.Person_id = PS.Person_id
                        and PQ.PersonQuarantine_endDT is null
                    ) then 'true' else 'false' end as PersonQuarantine_IsOn,
                    dbo.getPersonPhones(table1.Person_id, '<br />') as Person_Phone,
                rtrim(PS.Person_SurName) as Person_Surname,
                rtrim(PS.Person_FirName) as Person_Firname,
                rtrim(PS.Person_SecName) as Person_Secname,
                (PS.Person_SurName || ' ' || coalesce(PS.Person_FirName, '') || ' ' || coalesce(PS.Person_SecName,'')) as Person_FIO,
                to_char(PS.Person_BirthDay, 'dd.mm.yyyy') as Person_BirthDay,
                    table1.group_name,
                    case when EvnUslugaPar.EvnUslugaPar_setDT is not null
                        then to_char(cast(EvnUslugaPar.EvnUslugaPar_setDT as timestamp), 'dd.mm.yyyy')
                        else null
                    end as TimetableResource_begDate,
                    null as Resource_id,
                    '' as Resource_Name,
                    'б/н' as TimetableResource_begTime,
                    null as TimetableResource_Type,
                    table1.EvnDirection_IsCito,
                    table1.EvnDirection_setDT,
                    table1.EvnDirection_Num,
                    EvnUslugaPar.EvnUslugaPar_id,
                    
                    wls.WorkListStatus_Name,
                    MSL.MedService_id as RCC_MedService_id,
                    RCCR.RemoteConsultCenterResearch_id,
                    RCCR.RemoteConsultCenterResearch_status,
                    case when EUPAR.StatusResearch_Code in (4,5) and EUPAR.EvnUslugaParAssociatedResearches_limitDT < (select curDate from curDT)
                        then 1 else 0
                    end as "isExpired",
                    EUPAR.StatusResearch_Code as "StatusResearch_Code",
                    EUPAR.StatusResearch_Name as "StatusResearch_Name",
                    coalesce(EUPAR.RiskGroup_Code, 1) as "RiskGroup_Code",
                    EUPAR.RiskGroup_Name as "RiskGroup_Name",
                    EUPAR.Study_uid as "Study_uid",
                    EUPAR.LpuEquipmentPacs_id as "LpuEquipmentPacs_id",
                    to_char(EUPAR.EvnUslugaParAssociatedResearches_limitDT, 'DD.MM.YYYY HH24:MI') as "EvnUslugaParAssociatedResearches_limitDT",
                    case
                        when EUPAR.StatusResearch_Code = 7 or EUPAR.StatusResearch_Code = 6
                            then 'Выполнено'
                        when EUPAR.StatusResearch_Code = 4 or EUPAR.StatusResearch_Code = 5
                            then 'Очередь'
                    end as "ResearchStatusGroup",
                    case when EvnUslugaPar.EvnUslugaPar_setDT is not null then 'true' else 'false' end as FuncRequestState,
                    Operator as Operator,
                    table1.EvnFuncRequest_UslugaCache,
                    '' as Lpu_Name,
                    '' as LpuSection_Name,
                    '' as EvnCostPrint_PrintStatus,
                    case when pac.PersonAmbulatCard_Num is null then '-' else pac.PersonAmbulatCard_Num end as PersonAmbulatCard_Num,
                    table1.Diag_Code
                FROM
                    table1 table1
                    inner join v_PersonState PS on PS.Person_id = table1.Person_id
                    inner join lateral(
                        Select EvnUslugaPar_id, EvnUslugaPar_setDT
                         
                         from v_EvnUslugaPar EvnUslugaPar where EvnUslugaPar.EvnDirection_id = table1.EvnDirection_id limit 1
                    ) EvnUslugaPar on true
                    left join lateral(
                        select
                            SR.StatusResearch_Code,
                            SR.StatusResearch_Name,
                            RG.RiskGroup_Code,
                            RG.RiskGroup_Name,
                            EUPAR.Study_uid,
                            EUPAR.LpuEquipmentPacs_id,
                            EUPAR.EvnUslugaParAssociatedResearches_limitDT
                        from v_EvnUslugaParAssociatedResearches EUPAR
                        left join v_StatusResearch SR on SR.StatusResearch_id = EUPAR.StatusResearch_id
                        left join v_RiskGroup RG  on RG.RiskGroup_id = EUPAR.RiskGroup_id
                        where EUPAR.EvnUslugaPar_id = EvnUslugaPar.EvnUslugaPar_id
                        limit 1
                    ) EUPAR on true
                    
                    LEFT JOIN v_RemoteConsultCenterResearch RCCR on RCCR.EvnUslugaPar_id = EvnUslugaPar.EvnUslugaPar_id
                    left join v_WorkListQueue wlq on EvnUslugaPar.EvnUslugaPar_id = wlq.EvnUslugaPar_id
                    left join v_WorkListStatus wls on wlq.WorkListStatus_id = wls.WorkListStatus_id
                    LEFT JOIN v_MedServiceLink MSL on MSL.MedService_lid = '890101000000495'
                    AND MSL.MedServiceLinkType_id = (select MSLT.MedServiceLinkType_id from v_MedServiceLinkType MSLT where MSLT.MedServiceLinkType_Code = '3' limit 1)
                    left join lateral (
                        SELECT
                            PAC.PersonAmbulatCard_id,
                            PAC.PersonAmbulatCard_Num
                        FROM v_PersonAmbulatCard PAC
                        WHERE PAC.Person_id = table1.Person_id
                        ORDER BY PAC.PersonAmbulatCard_id DESC
                        limit 1
                    ) pac on true
                WHERE
                    1 = 1
                    and EvnUslugaPar.EvnUslugaPar_id is not null
                    
                ) paging
                 WHERE 
                (1=1)
                
                
                        ORDER BY
                            
                paging.EvnDirection_setDT,
                paging.EvnDirection_id
                
                OFFSET 0 ROWS
                FETCH NEXT 20 ROWS ONLY
                
