
                
                

            with vars as (select
            localtimestamp as tzdate,
            localtimestamp::timestamp as cur_dt,
            (localtimestamp + INTERVAL '59 day')::timestamp as upper_dt,
            localtimestamp::date as cur_date),
            noprescr as (
                select UslugaComplexAttributeType_id id
                from dbo.v_UslugaComplexAttributeType
                where UslugaComplexAttributeType_SysNick in ('noprescr')),
            lab as (
                select UslugaComplexAttributeType_id id
                from dbo.v_UslugaComplexAttributeType
                where UslugaComplexAttributeType_SysNick in ('func')),
        
        tmp as (
        select
        uc11.UslugaComplex_id as UslugaComplex_2011id
,uc.UslugaComplex_id as UslugaComplex_msid
,coalesce(uc.UslugaComplex_id, uc11.UslugaComplex_id) as UslugaComplex_id
,coalesce(uc.UslugaComplex_Code, uc11.UslugaComplex_Code) as UslugaComplex_Code
,coalesce(ucms.UslugaComplex_Name, uc.UslugaComplex_Name, uc11.UslugaComplex_Name) as UslugaComplex_Name
,uc.UslugaCategory_id as UslugaCategory_id
,0 as MedService_cnt
,mss.RecordQueue_id
,mss.MedService_id as MedService_id
,ucms.UslugaComplexMedService_id as UslugaComplexMedService_id
,mss.MedService_Name as MedService_Name
,mss.MedService_Nick as MedService_Nick
,mst.MedServiceType_id as MedServiceType_id
,mst.MedServiceType_SysNick as MedServiceType_SysNick
,mss.MedService_IsExternal as MedService_IsExternal
,mss.Lpu_id as Lpu_id
,l.Lpu_Nick as Lpu_Nick
,lu.LpuBuilding_id as LpuBuilding_id
,lu.LpuBuilding_Name as LpuBuilding_Name
,lu.LpuUnit_id as LpuUnit_id
,lu.LpuUnit_Name as LpuUnit_Name
,lua.Address_Address as LpuUnit_Address
,lu.LpuUnitType_id as LpuUnitType_id
,lu.LpuUnitType_SysNick as LpuUnitType_SysNick
,ls.LpuSection_id as LpuSection_id
,ls.LpuSection_Name as LpuSection_Name
,ls.LpuSectionProfile_id as LpuSectionProfile_id
,ls.LpuSectionProfile_Name as LpuSectionProfile_Name
,0 as isComposite
,1 as withResource
,eq.ElectronicQueueInfo_id as ElectronicQueueInfo_id
,eq.ElectronicService_id as ElectronicService_id
,composition.cnt as composition_cnt
,cast(mss.MedService_id as varchar) as Group_id
,res.Resource_id as Resource_id
,res.Resource_isJoinable as Resource_isJoinable,
        
                case when mss.Lpu_id = '140101000000008' then 1 else 2 end as s1,
                case when mss.LpuBuilding_id = '140101000000138' then 1 else 2 end as s2,
                case when mss.LpuUnit_id = '140101000002421' then 1 else 2 end as s3,
                case when mss.LpuSection_id = '140101000004717' then 1 else 2 end as s4,
                
                        case when exists(
                            select
                                ttr.TimetableResource_id
                            from
                                v_TimetableResource_lite ttr
                                inner join v_UslugaComplexResource ucr on ucr.Resource_id = ttr.Resource_id
                            where
                                ucms.UslugaComplexMedService_id = ucr.UslugaComplexMedService_id
                                and ttr.Person_id is null
                                and ttr.TimetableResource_jid is null
                                AND ttr.TimetableResource_begTime >= (select cur_dt from vars)
                                AND ttr.TimetableResource_begTime < (select upper_dt from vars)
                            limit 1
                        ) then 1 else 2 end as s5,
                        1 as s6,
                    
                1 as s7,
                1 as s8,
                1 as s9,
                uc.UslugaComplex_Code as s10
            
        from
        v_UslugaComplex uc
inner join v_UslugaComplexMedService ucms on ucms.UslugaComplex_id = uc.UslugaComplex_id AND ucms.UslugaComplexMedService_pid IS NULL AND cast(ucms.UslugaComplexMedService_begDT as date) <= (select cur_date from vars) AND coalesce(cast(ucms.UslugaComplexMedService_endDT as date), (select cur_date from vars)) >= (select cur_date from vars)
inner join v_MedService mss on mss.MedService_id = ucms.MedService_id AND mss.MedServiceType_id = 8
            AND cast(mss.MedService_begDT as date) <= (select cur_date from vars)
            AND (
                mss.MedService_endDT is null
                OR cast(mss.MedService_endDT as date) >= (select cur_date from vars)
            ) AND mss.Lpu_id = '140101000000008' AND mss.LpuSection_id is not null
LEFT JOIN LATERAL (
                SELECT
                    UslugaComplex_id,
                    UslugaComplex_Code,
                    UslugaComplex_Name
                FROM v_UslugaComplex uc11
                WHERE uc.UslugaComplex_2011id = uc11.UslugaComplex_id
                LIMIT 1
            ) uc11 ON true
left join lateral (select Q.UslugaComplex_id
                            from v_UslugaComplexAttribute Q
                            join noprescr on noprescr.id = Q.UslugaComplexAttributeType_id
                            where Q.UslugaComplex_id = coalesce(uc11.UslugaComplex_id, uc.UslugaComplex_id)
                            limit 1) Q on true





            LEFT JOIN LATERAL (--есть ли у лабы ПЗ
                SELECT vms2.MedService_id
                FROM v_MedService vms2
                    INNER JOIN v_MedServiceLink vmsl2 ON vmsl2.MedService_id = vms2.MedService_id
                WHERE
                    vmsl2.MedService_lid = mss.MedService_id --родитель МС
                    AND vms2.MedServiceType_id = 7 --это ПЗ
                    AND vmsl2.MedServiceLinkType_id = 1 --это связь лаба-ПЗ
                    AND (vms2.MedService_endDT IS NULL OR CAST(vms2.MedService_endDT AS DATE) > (select cur_date from vars))
                limit 1
            ) msPZtest ON TRUE
        
left join v_UslugaComplexResource ucres on ucres.UslugaComplexMedService_id = ucms.UslugaComplexMedService_id
left join v_Resource res on res.Resource_id = ucres.Resource_id and (res.Resource_endDT is null OR cast(res.Resource_endDT as date) > (select cur_date from vars))
left join v_MedServiceType mst on mss.MedServiceType_id = mst.MedServiceType_id
left join v_Lpu l on mss.Lpu_id = l.Lpu_id
left join v_LpuUnit lu on lu.LpuUnit_id = mss.LpuUnit_id
left join v_LpuSection ls on mss.LpuSection_id = ls.LpuSection_id

left join v_Address lua on lua.Address_id = coalesce(lu.Address_id,mss.Address_id)
left join lateral (
            select
                eqi.ElectronicQueueInfo_id,
                mseq.ElectronicService_id
            from
                v_ElectronicQueueInfo eqi
                inner join v_MedServiceElectronicQueue mseq on mseq.UslugaComplexMedService_id = ucms.UslugaComplexMedService_id
                inner join v_ElectronicService es on es.ElectronicService_id = mseq.ElectronicService_id
            where
                 eqi.MedService_id = mss.MedService_id
                 and eqi.ElectronicQueueInfo_IsOff = 1
            limit 1
        ) eq on true
left join lateral (
            select COUNT(ucoa.UslugaComplex_id) as cnt
            from v_UslugaComplexMedService ucmsoa
            inner join v_UslugaComplex ucoa on ucmsoa.UslugaComplex_id = ucoa.UslugaComplex_id
            where ucmsoa.UslugaComplexMedService_pid = ucms.UslugaComplexMedService_id
        ) composition on true
left join lateral (
            select string_agg(t2.UslugaComplexAttributeType_SysNick, ',') as UslugaComplexAttributeType_SysNick
            from v_UslugaComplexAttribute t1
            inner join v_UslugaComplexAttributeType t2 on t2.UslugaComplexAttributeType_id = t1.UslugaComplexAttributeType_id
            where t1.UslugaComplex_id = uc.UslugaComplex_id
        ) uca on true
        where
        uc.UslugaComplex_id is not null
 AND uc.UslugaComplex_begDT <= (select cur_date from vars)
 AND coalesce(uc.UslugaComplex_endDT, (select cur_date from vars)) >= (select cur_date from vars)
 AND Q.UslugaComplex_id is null
 AND not exists( select * from v_medservice mslo where mslo.MedService_id = mss.MedService_id and mslo.MedService_IsThisLPU = 2 and mslo.lpu_id != '140101000000008')
 AND exists (
            select
                t1.UslugaComplexAttribute_id
            from
                v_UslugaComplexAttribute t1
            JOIN
                lab on lab.id = t1.UslugaComplexAttributeType_id
            where
                t1.UslugaComplex_id = coalesce(uc11.UslugaComplex_id, uc.UslugaComplex_id)
            limit 1
        )
 AND (exists(
                    select
                        t2.Resource_id
                    from
                        v_UslugaComplexResource t1
                        inner join v_Resource t2 on t1.Resource_id = t2.Resource_id
                    where
                        t1.UslugaComplexMedService_id = ucms.UslugaComplexMedService_id
                        and coalesce(cast(t2.Resource_begDT as date), (select cur_date from vars)) <= (select cur_date from vars)
                        and coalesce(cast(t2.Resource_endDT as date), (select cur_date from vars)) >= (select cur_date from vars)
                    limit 1
                )
            )
 AND   mss.MedService_id = '140101000000302'
        --IsThisLPU
        )

                SELECT 
    a.*
    
                FROM
                
    (
                select
                    
                DD.UslugaComplex_2011id as "UslugaComplex_2011id"
                --,DD.MedService_cnt as "MedService_cnt"
                ,DD.UslugaComplex_msid as "UslugaComplex_msid"
                ,DD.UslugaComplex_id as "UslugaComplex_id"
                ,DD.UslugaComplex_Code as "UslugaComplex_Code"
                ,DD.UslugaComplex_Name as "UslugaComplex_Name"
                ,DD.UslugaCategory_id as "UslugaCategory_id"
                ,DD.MedService_id as "MedService_id"
                ,DD.UslugaComplexMedService_id as "UslugaComplexMedService_id"
                ,DD.MedService_Name as "MedService_Name"
                ,DD.MedService_Nick as "MedService_Nick"
                ,DD.MedServiceType_id as "MedServiceType_id"
                ,DD.MedServiceType_SysNick as "MedServiceType_SysNick"
                ,DD.RecordQueue_id as "RecordQueue_id"
                ,DD.MedService_IsExternal as "MedService_IsExternal"
                ,DD.Lpu_id as "Lpu_id"
                ,DD.Lpu_Nick as "Lpu_Nick"
                ,DD.LpuBuilding_id as "LpuBuilding_id"
                ,DD.LpuBuilding_Name as "LpuBuilding_Name"
                ,DD.LpuUnit_id as "LpuUnit_id"
                ,DD.LpuUnit_Name as "LpuUnit_Name"
                ,DD.LpuUnit_Address as "LpuUnit_Address"
                ,DD.LpuUnitType_id as "LpuUnitType_id"
                ,DD.LpuUnitType_SysNick as "LpuUnitType_SysNick"
                ,DD.LpuSection_id as "LpuSection_id"
                ,DD.LpuSection_Name as "LpuSection_Name"
                ,DD.LpuSectionProfile_id as "LpuSectionProfile_id"
                ,DD.LpuSectionProfile_Name as "LpuSectionProfile_Name"
                ,DD.isComposite as "isComposite"
                ,DD.ElectronicQueueInfo_id as "ElectronicQueueInfo_id"
                ,DD.ElectronicService_id as "ElectronicService_id"
                ,DD.withResource as "withResource"
                ,DD.composition_cnt as "composition_cnt"
            ,cast(dd.UslugaComplex_id as varchar) || coalesce('_' || cast(dd.MedService_id as varchar), '') as "Unique_id"
                    ,to_char(ttmsx.TimetableMedService_begTime::timestamp, 'DD.MM.YYYY HH24:MI') as "TimetableMedService_begTime"
                    ,ttmsx.TimetableMedService_id as "TimetableMedService_id"
                    ,ttmsx.MedService_id as "ttms_MedService_id"
                    ,to_char(ttmsx.TimetableResource_begTime::timestamp, 'DD.MM.YYYY HH24:MI') as "TimetableResource_begTime"
                    ,ttmsx.Resource_id as "Resource_id"
                    ,ttmsx.Resource_isJoinable as "Resource_isJoinable"
                    ,ttmsx.Resource_Name as "Resource_Name"
                    ,ttmsx.Resource_id as "ttr_Resource_id"
                    ,ttmsx.TimetableResource_id as "TimetableResource_id"
                    ,ttmsx.TimetableResource_Time as "TimetableResource_Time"
                    ,ttmsx.UslugaComplexResource_id as "UslugaComplexResource_id"
                    ,ttmsx.UslugaComplexResource_Time as "UslugaComplexResource_Time"
                , "minTimeValue"
                from
                    tmp DD
            left join lateral
            (
                select
                    TimetableMedService_id,
                    MedService_id,
                    TimetableMedService_begTime,
                    UslugaComplexMedService_id,
                    TimetableResource_id,
                    TimetableResource_jid,
                    TimetableResource_begTime,
                    TimetableResource_Day,
                    Person_id,
                    Resource_id,
                    Resource_Name,
                    Resource_isJoinable,
                    UslugaComplexResource_id,
                    UslugaComplexResource_Time,
                    TimetableResource_Time
                from (
                    SELECT
                        null as TimetableMedService_id,
                        DD.MedService_id,
                        cast(null as timestamp) as TimetableMedService_begTime,
                        DD.UslugaComplexMedService_id,
                        ttr.TimetableResource_id,
                        ttr.TimetableResource_jid,
                        ttr.TimetableResource_begTime,
                        ttr.TimetableResource_Day,
                        ttr.Person_id,
                        r.Resource_id,
                        r.Resource_Name,
                        r.Resource_isJoinable,
                        ucr.UslugaComplexResource_id,
                        ucr.UslugaComplexResource_Time,
                        ttr.TimetableResource_Time
                    FROM
                        v_TimetableResource_lite ttr
                        inner join v_Resource r on r.Resource_id = ttr.Resource_id
                        inner join v_UslugaComplexResource ucr on ucr.Resource_id = r.Resource_id
                    where
                        ttr.Person_id is null
                        AND ttr.TimetableResource_jid is null
                        AND not exists (select * from TimetableLock where TimetableResource_id = ttr.TimetableResource_id)
                        AND (ttr.Resource_id = DD.Resource_id or (DD.Resource_id is null and ucr.Resource_id is not null))
                        and ucr.UslugaComplexMedService_id = DD.UslugaComplexMedService_id
                        AND ttr.TimetableResource_begTime >= (select cur_dt from vars)
                        AND ttr.TimetableResource_begTime < (select upper_dt from vars)
                        
                        
                    ORDER BY
                        
            case
                /* При записи в свое МО: Все типы бирок кроме «резервных» */
                when DD.Lpu_id = '140101000000008' AND ttr.TimeTableType_id not in (2) then 0
                /* При записи в чужое МО: Обычная, По направлению */
                when DD.Lpu_id <> '140101000000008' AND ttr.TimeTableType_id in (1,5) then 0
                else 1
            end
        
                        
                , ttr.TimetableResource_begTime
            
                    limit 1
                ) ttms2

                order by
                    case when ttms2.UslugaComplexMedService_id is not null then 0 else 1 end
                limit 1
            ) ttmsx on true
             left join lateral( 
            SELECT
                min(ttr.TimetableResource_begTime) as "minTimeValue"
            FROM v_TimetableResource_lite ttr
                inner join v_Resource r on r.Resource_id = ttr.Resource_id
                inner join v_UslugaComplexResource ucr on ucr.Resource_id = r.Resource_id
            where ttr.Person_id is null
                AND ttr.TimetableResource_jid is null
                AND not exists(select * from TimetableLock where TimetableResource_id = ttr.TimetableResource_id)
                and ucr.UslugaComplexMedService_id = DD.UslugaComplexMedService_id
                AND ttr.TimetableResource_begTime >= (select cur_dt from vars)
                AND ttr.TimetableResource_begTime < (select upper_dt from vars)
            group by ucr.UslugaComplexMedService_id
            limit 1
        ) mtt on true
                where
                    (1=1)
            and case
                when "minTimeValue" is not null then
                    ttmsx.TimetableResource_begTime = "minTimeValue"
                else
                    ttmsx.TimetableResource_begTime is null
                end
        
            ) as a
     WHERE 
    1 = 1
    
                
                        ORDER BY
                            
    "UslugaComplex_2011id"
    
                OFFSET 0 ROWS
                FETCH NEXT 500 ROWS ONLY
                
