UFN_EVENTCONFLICT_GETRESOURCECONFLICTDETAILS

CREATE function dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTDETAILS
(
    @START datetime,
    @END datetime,
    @RESOURCES xml,
    @IGNORERECORDID uniqueidentifier = null,
    @IGNORESUBRECORDID uniqueidentifier = null,
    @IGNORESUPERRECORDID uniqueidentifier = null,
    @INCLUDEEVENTCONFLICTS bit = 1,
    @INCLUDEITINERARYITEMCONFLICTS bit = 1,
    @INCLUDEITINERARYCONFLICTS bit = 1,
    @OFFSETRESOURCES xml = null
)
returns @CONFLICTS table (
    EVENTID uniqueidentifier,
    ITINERARYITEMID uniqueidentifier,
    ITINERARYID uniqueidentifier,
    RESOURCEID uniqueidentifier,
    QUANTITYNEEDED int
)
as begin
    -- Take the resource xml passed in and generate a table of resources

    declare @RESOURCESTABLE table
    (
        RESOURCEID uniqueidentifier,
        QUANTITYNEEDED int
    )

    insert into @RESOURCESTABLE
    (RESOURCEID, QUANTITYNEEDED)
    select 
        T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
        T.c.value('(QUANTITYNEEDED)[1]','int')
    from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)

    -- If there are no resources to check, return an empty table

    if @@ROWCOUNT = 0
        return;

    if @IGNORERECORDID is not null
    begin
        declare @VISITORCOUNT int
        set @VISITORCOUNT = isnull((
                                select sum(ITINERARYATTENDEE.QUANTITY)
                                from dbo.ITINERARYATTENDEE
                                where ITINERARYID = @IGNORERECORDID
                            ),0);
        update @RESOURCESTABLE set
            QUANTITYNEEDED = dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(@VISITORCOUNT * RESOURCE.PERTICKETQUANTITY, RESOURCE.PERTICKETDIVISOR)
        from dbo.RESOURCE
        where
            RESOURCE.ID = [@RESOURCESTABLE].RESOURCEID and
            -- Special checks for Itinerary Edit, Reservation Add, Group Checkin

            -- already set this up because of attendee quantity change

            [@RESOURCESTABLE].QUANTITYNEEDED = 0 and 
            RESOURCE.ISPERTICKETITEM = 1
    end

    insert into @RESOURCESTABLE
    (RESOURCEID, QUANTITYNEEDED)
    select T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
        T.c.value('(QUANTITYNEEDED)[1]','int')
    from @RESOURCES.nodes('/EVENTRESOURCES/ITEM') T(c)

    -- Build a table to store resource counts

    declare @RESOURCESUSED table
    (
        EVENTID uniqueidentifier,
        ITINERARYITEMID uniqueidentifier,
        ITINERARYID uniqueidentifier,
        RESOURCEID uniqueidentifier,
        QUANTITYNEEDED int,
        STARTDATETIME datetime,
        ENDDATETIME datetime
    )

    -- fill the table based on those events occurring at the same time        

    insert into @RESOURCESUSED
    (
        EVENTID, 
        RESOURCEID, 
        QUANTITYNEEDED,
        STARTDATETIME,
        ENDDATETIME
    )
    select 
        EVENT.ID,
        EVENTRESOURCE.RESOURCEID,
        EVENTRESOURCE.QUANTITYNEEDED,
        EVENT.STARTDATETIME as STARTDATETIME,
        EVENT.ENDDATETIME as ENDDATETIME
    from dbo.EVENT
    inner join dbo.EVENTRESOURCE
        on EVENT.ID = EVENTRESOURCE.EVENTID
    inner join dbo.RESOURCE
        on (EVENTRESOURCE.RESOURCEID = RESOURCE.ID) and (RESOURCE.TYPECODE = 0) --A non-consumable resource

    left outer join dbo.PROGRAM
        on EVENT.PROGRAMID = PROGRAM.ID
    inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on 
        EVENTRESOURCECONFLICT.RESOURCEID = EVENTRESOURCE.RESOURCEID
    where 
        (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) and
        (EVENT.ID <> @IGNORERECORDID or @IGNORERECORDID is null) and
        (
            (
                @START >= EVENT.STARTDATETIME and 
                @START < EVENT.ENDDATETIME
            ) or
            (
                @END > EVENT.STARTDATETIME and 
                @END <= EVENT.ENDDATETIME
            ) or
            (
                @START < EVENT.STARTDATETIME and 
                @END > EVENT.ENDDATETIME
            )
        )

    -- fill the table based on those itinerary items occurring at the same time

    insert into @RESOURCESUSED
    (
        ITINERARYITEMID, 
        RESOURCEID, 
        QUANTITYNEEDED,
        STARTDATETIME,
        ENDDATETIME
    )
    select 
        ITINERARYITEM.ID,
        ITINERARYITEMRESOURCE.RESOURCEID,
        case RESOURCE.ISPERTICKETITEM
            when 0 then
                ITINERARYITEMRESOURCE.QUANTITYNEEDED
            else
                dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(COALESCE((select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE where ITINERARYATTENDEE.ITINERARYID = ITINERARY.ID),0) * ITINERARYITEMRESOURCE.PERTICKETQUANTITY, ITINERARYITEMRESOURCE.PERTICKETDIVISOR)
        end as QUANTITYNEEDED,
        ITINERARYITEM.STARTDATETIME,
        ITINERARYITEM.ENDDATETIME
    from dbo.ITINERARYITEM
    inner join dbo.ITINERARY on 
        ITINERARYITEM.ITINERARYID = ITINERARY.ID
    inner join dbo.SALESORDER on
        ITINERARY.RESERVATIONID = SALESORDER.ID
    inner join dbo.ITINERARYITEMRESOURCE on 
        ITINERARYITEM.ID = ITINERARYITEMRESOURCE.ITINERARYITEMID
    inner join dbo.RESOURCE on 
        ITINERARYITEMRESOURCE.RESOURCEID = RESOURCE.ID and 
        RESOURCE.TYPECODE = 0 --A non-consumable resource

    inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on 
        EVENTRESOURCECONFLICT.RESOURCEID = ITINERARYITEMRESOURCE.RESOURCEID
    where 
        SALESORDER.STATUSCODE <> 5 and -- Make sure the reservation is not cancelled, cancelled resources do not count against total

        ITINERARYITEM.INVALIDREASONCODE = 0 and -- Make sure that the itinerary item does not have an invalid reason

        ITINERARYITEM.ITEMTYPECODE <> 3 and -- Make sure the itinerary item is showing as scheduled

        (
            @IGNORESUPERRECORDID is null
            or ITINERARY.RESERVATIONID <> @IGNORESUPERRECORDID
        )
        and (
            @IGNORESUBRECORDID is null
            or ITINERARYITEM.ID <> @IGNORESUBRECORDID
        )
        and (
            @IGNORERECORDID is null
            or ITINERARYITEM.ITINERARYID <> @IGNORERECORDID
        )
        and (
            (
                @START >= ITINERARYITEM.STARTDATETIME and 
                @START < ITINERARYITEM.ENDDATETIME
            ) or
            (
                @END > ITINERARYITEM.STARTDATETIME and 
                @END <= ITINERARYITEM.ENDDATETIME
            ) or
            (
                @START < ITINERARYITEM.STARTDATETIME and 
                @END > ITINERARYITEM.ENDDATETIME
            )
        )

    -- fill the table based on those itineraries occurring at the same time

    insert into @RESOURCESUSED
    (
        ITINERARYID, 
        RESOURCEID, 
        QUANTITYNEEDED,
        STARTDATETIME,
        ENDDATETIME
    )
    select 
        ITINERARY.ID,
        ITINERARYRESOURCE.RESOURCEID,
        case RESOURCE.ISPERTICKETITEM
            when 0 then
                ITINERARYRESOURCE.QUANTITYNEEDED
            else
                dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2(COALESCE((select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE where ITINERARYATTENDEE.ITINERARYID = ITINERARYRESOURCE.ITINERARYID),0) * ITINERARYRESOURCE.PERTICKETQUANTITY, ITINERARYRESOURCE.PERTICKETDIVISOR)
        end as QUANTITYNEEDED,
        ITINERARY.STARTDATETIME as STARTDATETIME,
        ITINERARY.ENDDATETIME as ENDDATETIME
    from dbo.ITINERARY
    inner join dbo.SALESORDER on
        ITINERARY.RESERVATIONID = SALESORDER.ID
    inner join dbo.ITINERARYRESOURCE on 
        ITINERARY.ID = ITINERARYRESOURCE.ITINERARYID
    inner join dbo.RESOURCE on 
        ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID and 
        RESOURCE.TYPECODE = 0 --A non-consumable resource

    inner join @RESOURCESTABLE as EVENTRESOURCECONFLICT on 
        EVENTRESOURCECONFLICT.RESOURCEID = ITINERARYRESOURCE.RESOURCEID
    where
        SALESORDER.STATUSCODE <> 5 and -- Make sure the reservation is not cancelled, cancelled resources do not count against total

        (
            @IGNORESUBRECORDID is not null or 
            (ITINERARY.ID <> @IGNORERECORDID or @IGNORERECORDID is null)
        ) and
        (ITINERARY.RESERVATIONID <> @IGNORESUPERRECORDID or @IGNORESUPERRECORDID is null) and
        (
            (@START >= ITINERARY.STARTDATETIME and @START < ITINERARY.ENDDATETIME) or
            (@END > ITINERARY.STARTDATETIME and @END <= ITINERARY.ENDDATETIME) or
            (@START < ITINERARY.STARTDATETIME and @END > ITINERARY.ENDDATETIME)
        )


    -- Checking against reservation this is being moved or copied.

    -- Picking up conflicts that would be caused by the whole reservation.

    if @OFFSETRESOURCES is not null
    begin
        delete from @RESOURCESTABLE;

        with RESOURCES_CTE as
        (
            select 
                T.item.value('(RESOURCEID)[1]','uniqueidentifier') as RESOURCEID,
                T.item.value('(QUANTITYNEEDED)[1]','integer') as QUANTITYNEEDED
            from @OFFSETRESOURCES.nodes('/OFFSETRESOURCES/ITEM') T(item)
            where 
            (
                (
                    (
                        T.item.value('(STARTDATETIME)[1]','datetime') <= @START and 
                        @START < T.item.value('(ENDDATETIME)[1]','datetime')
                    ) or
                    (
                        T.item.value('(STARTDATETIME)[1]','datetime') < @END and 
                        @END <= T.item.value('(ENDDATETIME)[1]','datetime')
                    ) or
                    (
                        @START < T.item.value('(STARTDATETIME)[1]','datetime') and 
                        @END > T.item.value('(ENDDATETIME)[1]','datetime')
                    )
                )
            )
        )
        insert into @RESOURCESTABLE
        select
            RE.RESOURCEID,
            sum(RE.QUANTITYNEEDED)
        from RESOURCES_CTE RE
        group by RE.RESOURCEID
    end

    declare @CURRENTUSEDRESOURCES table
    (
        EVENTID uniqueidentifier,
        ITINERARYITEMID uniqueidentifier,
        ITINERARYID uniqueidentifier,
        RESOURCEID uniqueidentifier,
        QUANTITYNEEDED int
    )                

    declare @THISITINERARYID uniqueidentifier
    declare @THISITINERARYITEMID uniqueidentifier
    declare @THISEVENTID uniqueidentifier
    declare @THISRESOURCEID uniqueidentifier
    declare @THISQUANTITYNEEDED int
    declare @THISTIME datetime
    declare @THISISSTARTTIME bit            

    declare CONFLICTS_CURSOR cursor local fast_forward for
    select
        ITINERARYID,
        ITINERARYITEMID,
        EVENTID,
        RESOURCEID,
        QUANTITYNEEDED,
        TIME,
        ISSTARTTIME
    from
    (
        select
            ITINERARYID,
            ITINERARYITEMID,
            EVENTID,
            RESOURCEID,
            QUANTITYNEEDED,
            STARTDATETIME as TIME,
            1 as ISSTARTTIME
        from @RESOURCESUSED

        union all

        select
            ITINERARYID,
            ITINERARYITEMID,
            EVENTID,
            RESOURCEID,
            QUANTITYNEEDED,
            ENDDATETIME as TIME,
            0 as ISSTARTTIME
        from @RESOURCESUSED
    ) as T
    order by T.TIME, T.ISSTARTTIME

    open CONFLICTS_CURSOR;

    fetch next from CONFLICTS_CURSOR
    into 
        @THISITINERARYID, 
        @THISITINERARYITEMID, 
        @THISEVENTID, 
        @THISRESOURCEID,
        @THISQUANTITYNEEDED,
        @THISTIME,
        @THISISSTARTTIME;

    while @@FETCH_STATUS = 0
    begin
        if @THISISSTARTTIME = 1
        -- PUSH

        begin
            insert into @CURRENTUSEDRESOURCES
            (
                EVENTID,
                ITINERARYITEMID,
                ITINERARYID,
                RESOURCEID,
                QUANTITYNEEDED
            )
            values
            (
                @THISEVENTID,
                @THISITINERARYITEMID,
                @THISITINERARYID,
                @THISRESOURCEID,
                @THISQUANTITYNEEDED
            )

            -- If in conflict, dump @CURRENTUSEDRESOURCES into @CONFLICTS

            -- Not inserting duplicates


            insert into @CONFLICTS
            (
                EVENTID, 
                ITINERARYITEMID,
                ITINERARYID,
                RESOURCEID, 
                QUANTITYNEEDED
            )
            select 
                RESOURCESUSED.EVENTID, 
                RESOURCESUSED.ITINERARYITEMID,
                RESOURCESUSED.ITINERARYID,
                RESOURCESUSED.RESOURCEID, 
                RESOURCESUSED.QUANTITYNEEDED
            from @CURRENTUSEDRESOURCES as RESOURCESUSED
            where 
                not exists
                (
                    select 1 from @CONFLICTS
                    where
                    (
                        [@CONFLICTS].EVENTID = RESOURCESUSED.EVENTID or
                        [@CONFLICTS].ITINERARYITEMID = RESOURCESUSED.ITINERARYITEMID or
                        [@CONFLICTS].ITINERARYID = RESOURCESUSED.ITINERARYID
                    ) and
                    [@CONFLICTS].RESOURCEID = RESOURCESUSED.RESOURCEID
                ) and
                RESOURCESUSED.RESOURCEID in
                (
                    select OVERALLOCATEDRESOURCES.RESOURCEID
                    from @CURRENTUSEDRESOURCES as OVERALLOCATEDRESOURCES
                    group by OVERALLOCATEDRESOURCES.RESOURCEID
                    having 
                    (
                        sum(OVERALLOCATEDRESOURCES.QUANTITYNEEDED) + 
                        (
                            select top 1 RESOURCES.QUANTITYNEEDED 
                            from @RESOURCESTABLE as RESOURCES 
                            where RESOURCES.RESOURCEID = OVERALLOCATEDRESOURCES.RESOURCEID
                        ) > 
                        (
                            select RESOURCE.QUANTITY 
                            from RESOURCE 
                            where RESOURCE.ID = OVERALLOCATEDRESOURCES.RESOURCEID
                        )
                    )
                )

        end
        else
        -- POP

        -- Do not need to check for conflicts as we are actually decrementing the count

        begin
            delete from @CURRENTUSEDRESOURCES
            where
            (
                @THISEVENTID = [@CURRENTUSEDRESOURCES].EVENTID or
                @THISITINERARYITEMID = [@CURRENTUSEDRESOURCES].ITINERARYITEMID or
                @THISITINERARYID = [@CURRENTUSEDRESOURCES].ITINERARYID
            ) and
            @THISRESOURCEID = [@CURRENTUSEDRESOURCES].RESOURCEID
        end

        fetch next from CONFLICTS_CURSOR
        into 
            @THISITINERARYID, 
            @THISITINERARYITEMID, 
            @THISEVENTID, 
            @THISRESOURCEID,
            @THISQUANTITYNEEDED,
            @THISTIME,
            @THISISSTARTTIME;
    end

    close CONFLICTS_CURSOR;
    deallocate CONFLICTS_CURSOR;

    delete from @CONFLICTS
    where
        @INCLUDEEVENTCONFLICTS = 0 and EVENTID is not null or
        @INCLUDEITINERARYCONFLICTS = 0 and ITINERARYID is not null or
        @INCLUDEITINERARYITEMCONFLICTS = 0 and ITINERARYITEMID is not null

    return;
end