USP_LOCATIONAVAILABILITY_GETLOCATIONSINUSE

CREATE procedure dbo.USP_LOCATIONAVAILABILITY_GETLOCATIONSINUSE
(
    @LOCATIONSSELECTED xml = null
    , @STARTDATE date = null
    , @ENDDATE date = null
)
as
begin
    -- do not use down to the second. All types we care about are to the minute.

    declare @EARLIESTTIME time(0) = '00:00:00';
    declare @LATESTTIME time(0) = '23:59:00';

    declare @LOCATIONLIST as table 
    (
        ID uniqueidentifier
    );

    insert into @LOCATIONLIST 
    select
        T.c.value('(ID)[1]','uniqueidentifier') as 'ID'
    from @LOCATIONSSELECTED.nodes('/LOCATIONSSELECTED/ITEM') T(c);

    -- Unroll the events into entries by day. 

    -- Might need to be a Temp Table instead considering the record counts that could occur.

    declare @EVENTDATA as table
    (
        ID uniqueidentifier,
        NAME nvarchar(100),
        PROGRAMID uniqueidentifier,
        EVENTLOCATIONID uniqueidentifier,
        CAPACITY int,
        STARTDATE date,
        ENDDATE date,
        STARTTIME time(0),
        ENDTIME time(0)
    );

    insert into @EVENTDATA
    select 
        e.ID,
        e.NAME,
        e.PROGRAMID,
        e.EVENTLOCATIONID,
        e.CAPACITY,
        d.[DAY] STARTDATE,
        d.[DAY] ENDDATE,
        case 
            when d.[DAY] = e.STARTDATE then
                case e.STARTTIME
                    when '' then @EARLIESTTIME
                    else convert(time(0), e.STARTDATETIME)
                end
            else @EARLIESTTIME
        end as STARTTIME,
        case 
            when d.[DAY] = e.ENDDATE then
                case e.ENDTIME
                    when '' then @LATESTTIME
                    else convert(time(0), e.ENDDATETIME)
                end
            else @LATESTTIME
        end as ENDTIME
    from dbo.EVENT e
        inner join 
        (
            select DATEADD(D, NUM, @STARTDATE) as [DAY] 
            from dbo.NUMBERS
        ) as D on D.[DAY] <= ENDDATE and D.[DAY] >= STARTDATE
        left join dbo.PROGRAM p on e.PROGRAMID = p.ID
    where 
        e.ISACTIVE = 1
        -- Currently Programs are being treated as higher than events for active status.

        and (coalesce(p.ISACTIVE, 1) = 1);

    select
        NAME,
        LOCATIONID,
        LOCATIONNAME,
        STARTDATE,
        ENDDATE,
        STARTTIME,
        ENDTIME,
        QUANTITYUSED,
        EVENTCAPACITY,
        LOCATIONCAPACITY,
        ENTRYTYPE
    from (
        select 
            p.NAME as NAME,
            el.ID as LOCATIONID,
            el.NAME as LOCATIONNAME,
            e.STARTDATE as STARTDATE,
            e.ENDDATE as ENDDATE,
            e.STARTTIME as STARTTIME,
            e.ENDTIME as ENDTIME,
            TICKETCOUNTS.INUSE as QUANTITYUSED,
            e.CAPACITY as EVENTCAPACITY,
            el.CAPACITY as LOCATIONCAPACITY,
            0 as ENTRYTYPE -- events

        from  @LOCATIONLIST ll
            inner join dbo.EVENTLOCATION el on el.ID = ll.ID
            inner join dbo.PROGRAMEVENTLOCATION pel on pel.EVENTLOCATIONID = el.ID
            inner join @EVENTDATA e on e.ID = pel.EVENTID
            inner join dbo.PROGRAM p on p.ID = e.PROGRAMID
            inner join dbo.V_PROGRAMEVENT_TICKETCOUNTS as TICKETCOUNTS on TICKETCOUNTS.ID = e.ID
            where 
                (e.STARTDATE     >= @STARTDATE or @STARTDATE is null)
                and (e.ENDDATE   <= @ENDDATE or @ENDDATE is null)
                and (e.STARTTIME >= @EARLIESTTIME or @EARLIESTTIME is null)
                and (e.ENDTIME   <= @LATESTTIME or @LATESTTIME is null)

        union all

        -- Events

        select 
            e.NAME as NAME,
            el.ID as LOCATIONID,
            el.NAME as LOCATIONNAME,
            e.STARTDATE as STARTDATE,
            e.ENDDATE as ENDDATE,
            e.STARTTIME as STARTIME,
            e.ENDTIME as ENDTIME,
            count(r.ID) as QUANTITYUSED,
            max(e.CAPACITY) as EVENTCAPACITY,
            max(el.CAPACITY) as LOCATIONCAPACITY,
            1 as ENTRYTYPE -- Programs

        from @LOCATIONLIST ll
            inner join dbo.EVENTLOCATION el on el.ID = ll.ID
            inner join @EVENTDATA e on e.EVENTLOCATIONID = el.ID
            left join dbo.REGISTRANT r ON r.EVENTID = e.ID and r.WILLNOTATTEND = 0 and r.ISCANCELLED = 0
        where
            (e.STARTDATE     >= @STARTDATE or @STARTDATE is null)
            and (e.ENDDATE   <= @ENDDATE or @ENDDATE is null)
            and (e.STARTTIME >= @EARLIESTTIME or @EARLIESTTIME is null)
            and (e.ENDTIME   <= @LATESTTIME or @LATESTTIME is null)
        group by 
            e.NAME,
            el.ID, 
            el.NAME,
            e.STARTDATE,
            e.ENDDATE,
            e.STARTTIME,
            e.ENDTIME

        union all 

        -- Custom Itineraries from Group Sales - uses same unroll method for events

        select 
            i.NAME as NAME,
            t.EVENTLOCATIONID as LOCATIONID,
            el.NAME as LOCATIONNAME,
            d.[DAY] as STARTDATE,
            d.[DAY] as ENDDATE,
            case 
                when d.[DAY] = t.STARTDATE then
                    case t.STARTTIME
                        when '' then @EARLIESTTIME
                        else convert(time(0), t.STARTDATETIME)
                    end
                else @EARLIESTTIME
            end as STARTTIME,
            case 
                when d.[DAY] = t.ENDDATE then
                    case t.ENDTIME
                        when '' then @LATESTTIME
                        else convert(time(0), t.ENDDATETIME)
                    end
                else @LATESTTIME
            end as ENDTIME,
            case when T.BLOCKEVENT = 1
                then el.CAPACITY
                else a.QUANTITY
            end as QUANTITYUSED,
            el.CAPACITY as EVENTCAPACITY,
            el.CAPACITY as LOCATIONCAPACITY,
            2 as ENTRYTYPE -- Custom Items

        from @LOCATIONLIST ll
            inner join dbo.EVENTLOCATION el on el.ID = ll.ID
            inner join dbo.ITINERARYITEM T on T.EVENTLOCATIONID = el.ID
            inner join dbo.ITINERARY I on T.ITINERARYID = I.ID
            inner join dbo.ITINERARYATTENDEE A on A.ITINERARYID = I.ID
            inner join 
            (
                select DATEADD(D,NUM, @STARTDATE) as [DAY] from NUMBERS
            ) as D on D.[DAY] <= ENDDATE and D.[DAY] >= STARTDATE
        where 
            t.ITEMTYPECODE = 2 
            and t.INVALIDREASONCODE <> 1
    ) L
      where L.STARTDATE < L.ENDDATE or (L.STARTDATE = L.ENDDATE and L.STARTTIME < L.ENDTIME)
    order by 
        LOCATIONNAME, STARTDATE, STARTTIME;

end;