
            WITH table1 as (
                SELECT
                    efr.EvnFuncRequest_id as Evn_id,
                    efr.EvnClass_id,
                    EvnFuncRequest_id,
                    ttr.TimetableType_id,
                    efr.EvnFuncRequest_pid as EvnFuncRequest_pid,
                    EvnFuncRequest_UslugaCache,
                    ed2.EvnDirection_id,
                    to_char(ED.EvnDirection_desDT, 'dd.mm.yyyy') as EvnDirection_desDT,
                    ed2.EvnDirection_pid,
                    ed.EvnQueue_id,
                    efr.Person_id,
                    case
                        when EvnPrescrFuncDiag_IsCito = 2 then 2
                        when ed2.EvnDirection_IsCito = 2 then 2 else 1
                    end as	EvnPrescrFuncDiag_IsCito,
                    ed.EvnDirection_setDT,
                    ed.EvnDirection_Num,
                    ed.Lpu_sid,
                    ed.LpuSection_id,
                    efr.EvnStatus_id,
                    ed.pmUser_insID,
                    efr.evnfuncrequest_statusdate as EvnFuncRequest_statusDate,
                    ttr.Resource_id,
                    ttr.TimetableResource_id,
                    TimetableResource_begTime,
                    Resource_Name,
                    case
                        when TimetableResource_begTime is not null then to_char(TimetableResource_begTime, 'dd.mm.yyyy')
                        else to_char(TimetableResource_insDT, 'dd.mm.yyyy')
                    end as TimetableResource_Date,
                    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,
                    ed.Org_sid
                from
                    v_TimeTableResource_lite ttr
                        left join v_Resource r on ttr.Resource_id = r.Resource_id
                        left join v_MedService ms on r.MedService_id = ms.MedService_id
                        left join v_EvnDirection_all ed on ed.EvnDirection_id = ttr.EvnDirection_id and coalesce(ed.EvnStatus_id, 16) not in (12,13)
                        left join v_EvnDirection_all ed2 on ed2.EvnDirection_id = ttr.EvnDirection_id
                        left join v_EvnPrescrDirection epd on ed.EvnDirection_id = epd.evnDirection_id
                        left join v_EvnPrescrFuncDiag epfd on epfd.EvnPrescrFuncDiag_id = epd.EvnPrescr_id
                        left join v_EvnFuncRequest efr on efr.EvnFuncRequest_pid = ttr.EvnDirection_id
                        left JOIN v_pmUserCache PUC on PUC.PMUser_id = ed.pmUser_insID
                        left join v_Diag d on ed2.Diag_id = d.Diag_id
                WHERE
                    ms.MedService_id = '890101000001071'
                    and efr.person_id is not null
                    and (
                (TTR.TimetableResource_Day between 44681 and 44711)
            )
            ),
            table2 as (
            SELECT * FROM v_PersonState PS WHERE PS.Person_id IN (SELECT Person_id FROM table1)
            ),
            curDT as (select dbo.tzGetDate() as curDate)

            select
                efr.Evn_id as "Evn_id",
                efr.EvnClass_id as "EvnClass_id",
                efr.TimetableResource_id as "TimetableResource_id",
                efr.TimetableType_id as "TimetableType_id",
                EFR.EvnDirection_id as "EvnDirection_id",
                EFR.EvnDirection_desDT as "EvnDirection_desDT",
                wls.WorkListStatus_Name as "WorkListStatus_Name",
                EPC.EvnClass_SysNick as "parentEvnClass_SysNick",
                EFR.EvnQueue_id as "EvnQueue_id",
                EFR.EvnFuncRequest_id as "EvnFuncRequest_id",
                PS.Person_id as "Person_id",
                dbo.getPersonPhones(efr.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",
                to_char(efr.TimetableResource_begTime, 'dd.mm.yyyy') as "group_name",
                case
                    when efr.TimetableResource_begTime is not null then to_char(cast(efr.TimetableResource_begTime as timestamp), 'dd.mm.yyyy')
                    when EFR.EvnStatus_id = (select EvnStatus_id from v_EvnStatus where EvnStatus_SysNick = 'FuncDonerec' limit 1) then to_char(cast(efr.EvnFuncRequest_statusDate as timestamp), 'dd.mm.yyyy')
                    else null
                end as "TimetableResource_begDate",
                efr.TimetableResource_Date as "TimetableResource_Date",
                efr.Resource_id as "Resource_id",
                efr.Resource_Name as "Resource_Name",
                coalesce(to_char(efr.TimetableResource_begTime, 'HH24:MI:SS'),'б/з') as "TimetableResource_begTime",
                to_char(efr.TimetableResource_begTime, 'dd.mm.yyyy') as "TimetableResource_Type",
                case when 2 = coalesce(EFR.EvnPrescrFuncDiag_IsCito, 1) then 'true' else 'false' end as "EvnDirection_IsCito",
                to_char(EFR.EvnDirection_setDT, 'dd.mm.yyyy') as "EvnDirection_setDT",
                EFR.EvnDirection_Num as "EvnDirection_Num",
                EvnUslugaPar.EvnUslugaPar_id as "EvnUslugaPar_id",
                
                to_char(EvnUslugaPar.EvnUslugaPar_setDate, 'dd.mm.yyyy') as "EvnUslugaPar_setDate",
                MSL.MedService_id as "RCC_MedService_id",
                RCCR.RemoteConsultCenterResearch_id as "RemoteConsultCenterResearch_id",
                RCCR.RemoteConsultCenterResearch_status as "RemoteConsultCenterResearch_status",
                case when EFR.EvnStatus_id = (select EvnStatus_id from v_EvnStatus where EvnStatus_SysNick = 'FuncDonerec' limit 1) then 'true' else 'false' end as "FuncRequestState",
                efr.Operator as "Operator",
                efr.EvnFuncRequest_UslugaCache as "EvnFuncRequest_UslugaCache",
                coalesce(OrgLpuFrom.Lpu_Nick, 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",
                COALESCE(et.ElectronicTalon_Num, eth.ElectronicTalon_Num) as "ElectronicTalon_Num",
                COALESCE(ets.ElectronicTalonStatus_Name, eth.ElectronicTalonStatus_Name) as "ElectronicTalonStatus_Name",
                et.ElectronicService_id as "ElectronicService_id",
                COALESCE(et.ElectronicTalonStatus_id, eth.ElectronicTalonStatus_id) as "ElectronicTalonStatus_id",
                case
                   when eth.ElectronicTalonStatus_id = 4 then 1
                   else 0
                end as "ElectronicTalon_IsProcessed",
                et.ElectronicTalon_id as "ElectronicTalon_id",
                to_char(et.ElectronicTalon_updDT, 'YYYY-MM-DD"T"HH24:MI:SS') as "ElectronicTalon_updDT",
                et.EvnDirection_uid as "EvnDirection_uid",
                et.ElectronicQueueInfo_id as "ElectronicQueueInfo_id",
                case when exists(
                    select *
                    from v_PersonQuarantine PQ
                    where PQ.Person_id = PS.Person_id
                    and PQ.PersonQuarantine_endDT is null
                ) then 2 else 1 end as "PersonQuarantine_IsOn",
                null as "Person_IsBDZ",
                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",
                etr.ElectronicService_id as "toElectronicService_id",
                etr.ElectronicService_uid as "fromElectronicService_id",
                et.MedServiceType_SysNick as "fromMedServiceType_SysNick",
                et_uca.UslugaComplexAttributeType_SysNick as "UslugaComplexAttributeType_SysNick",
                et_uca.UslugaComplexAttribute_DBTableID as "UslugaComplexAttribute_DBTableID",
                case when pac.PersonAmbulatCard_Num is null then '-' else pac.PersonAmbulatCard_Num end as "PersonAmbulatCard_Num",
                efr.Diag_Code as "Diag_Code"
            FROM table1 efr
                left join v_Lpu LpuFrom on LpuFrom.Lpu_id = efr.Lpu_sid
                   left join v_Lpu OrgLpuFrom on OrgLpuFrom.Lpu_id =
                       (SELECT lpu_id as "Lpu_id" FROM v_Lpu WHERE Org_id = efr.Org_sid limit 1)
                left join v_LpuSection LpuSectionFrom on LpuSectionFrom.LpuSection_id = efr.LpuSection_id
                inner join table2 PS on PS.Person_id = efr.Person_id
                left join v_Person pers on pers.Person_id = efr.Person_id
                --left join v_EvnStatus ES on ES.EvnStatus_id = EFR.EvnStatus_id
                left join lateral(
                    SELECT EvnUslugaPar_id, EvnUslugaPar_setDate
                     
                     from v_EvnUslugaPar where EvnDirection_id = efr.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 = '890101000001071' AND MSL.MedServiceLinkType_id = (select MSLT.MedServiceLinkType_id from v_MedServiceLinkType MSLT where MSLT.MedServiceLinkType_Code = '3' limit 1)
                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 = efr.EvnFuncRequest_pid
                    and coalesce(ECP.EvnCostPrint_IsNoPrint,0) <> 2
                    limit 1
                ) NoPrintCount on true
                left join v_ElectronicTalonRedirect etr on etr.EvnDirection_uid = efr.EvnDirection_id
                --left join v_ElectronicTalon et on (coalesce(et.EvnDirection_uid, et.EvnDirection_id) = efr.EvnDirection_id)
                left join lateral (
                    Select
                        et.*
                        ,mst.MedServiceType_SysNick
                    from v_ElectronicTalon et
                    left join v_ElectronicQueueInfo eqi on eqi.ElectronicQueueInfo_id = et.ElectronicQueueInfo_id
                    left join v_MedService ms on ms.MedService_id = eqi.MedService_id
                    left join v_MedServiceType mst on mst.MedServiceType_id = ms.MedServiceType_id
                    where (et.EvnDirection_uid = efr.EvnDirection_id or et.EvnDirection_id = efr.EvnDirection_id)
                    limit 1
                ) et on true
                -- выбираем информацию для обслуженных талонов
                left join lateral (
                    select
                        et2.ElectronicTalon_Num,
                        e.ElectronicTalonStatus_id,
                        ets.ElectronicTalonStatus_Name
                    from v_ElectronicTalonHist e
                        left join v_ElectronicTalon et2 on et2.ElectronicTalon_id = e.ElectronicTalon_id
                        left join v_ElectronicTalonRedirect etr on et2.ElectronicTalon_id = etr.ElectronicTalon_id
                        left join v_ElectronicTalonStatus ets on e.ElectronicTalonStatus_id = ets.ElectronicTalonStatus_id
                    where (1 = 1)
                        and e.ElectronicTalonStatus_id = 4
                        and etr.EvnDirection_uid = efr.EvnDirection_id
                        and et.ElectronicTalon_id is null
                ) eth on true
                left join lateral (
                    select
                        uca.UslugaComplexAttribute_DBTableID
                        ,ucat.UslugaComplexAttributeType_SysNick
                    from v_TimeTableMedService_lite ttms
                    left join v_UslugaComplexMedService ucms on ucms.UslugaComplexMedService_id = ttms.UslugaComplexMedService_id
                    left join v_UslugaComplex uc on uc.UslugaComplex_id = ucms.UslugaComplex_id
                    left join v_UslugaComplexAttribute uca on uca.UslugaComplex_id = uc.UslugaComplex_id
                    left join v_UslugaComplexAttributeType ucat on ucat.UslugaComplexAttributeType_id = uca.UslugaComplexAttributeType_id
                    where ttms.EvnDirection_id = et.EvnDirection_id
                        and ucat.UslugaComplexAttributeType_SysNick in ('DispClass', 'CertType')
                    limit 1
                ) et_uca on true
                left join v_ElectronicTalonStatus ets on ets.ElectronicTalonStatus_id = et.ElectronicTalonStatus_id
                left join lateral (select EPC.EvnClass_SysNick from v_evn EPC where EPC.Evn_id = efr.EvnDirection_pid limit 1) EPC on true
                left join lateral (
                    SELECT
                        PAC.PersonAmbulatCard_id,
                        PAC.PersonAmbulatCard_Num
                    FROM v_PersonAmbulatCard PAC
                    WHERE PAC.Person_id = ps.Person_id
                    ORDER BY PAC.PersonAmbulatCard_id DESC
                    limit 1
                ) pac on true
                
            WHERE
                (1 = 1)
                
                
        
