UFN_EVENTCONFLICT_GETSTAFFRESOURCECONFLICTDETAILS

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

                declare @STAFFRESOURCESTABLE table
                (
                    VOLUNTEERTYPEID uniqueidentifier,
                    QUANTITYNEEDED int
                )

                insert into @STAFFRESOURCESTABLE(VOLUNTEERTYPEID, QUANTITYNEEDED)
                    select 
                        T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier'),
                        T.c.value('(QUANTITYNEEDED)[1]','int')
                    from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
                    where T.c.value('(FILLEDBYCODE)[1]','tinyint') = 0 /* Volunteer */

                    union all

                    select
                        T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier'),
                        T.c.value('(QUANTITYNEEDED)[1]','int')
                    from @STAFFRESOURCES.nodes('/EVENTSTAFFRESOURCES/ITEM') T(c)
                    where T.c.value('(FILLEDBYCODE)[1]','tinyint') = 0 /* Volunteer */

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

                if @@ROWCOUNT = 0
                    return;

                -- Build a table to store resource counts

                declare @STAFFRESOURCESUSED table
                (
                    EVENTID uniqueidentifier,
                    ITINERARYITEMID uniqueidentifier,
                    ITINERARYID uniqueidentifier,
                    VOLUNTEERTYPEID uniqueidentifier,
                    QUANTITYNEEDED int,
                    STARTDATETIME datetime,
                    ENDDATETIME datetime
                )

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

                insert into @STAFFRESOURCESUSED
                (
                    EVENTID, 
                    VOLUNTEERTYPEID, 
                    QUANTITYNEEDED,
                    STARTDATETIME,
                    ENDDATETIME
                )
                select 
                    EVENT.ID,
                    EVENTSTAFFRESOURCE.VOLUNTEERTYPEID,
                    EVENTSTAFFRESOURCE.QUANTITYNEEDED,
                    EVENT.STARTDATETIME as STARTDATETIME,
                    EVENT.ENDDATETIME as ENDDATETIME
                from dbo.EVENT
                inner join dbo.EVENTSTAFFRESOURCE on 
                    EVENT.ID = EVENTSTAFFRESOURCE.EVENTID and 
                    EVENTSTAFFRESOURCE.FILLEDBYCODE = 0 /* Volunteer */
                left outer join dbo.PROGRAM on 
                    EVENT.PROGRAMID = PROGRAM.ID
                inner join @STAFFRESOURCESTABLE as EVENTSTAFFRESOURCECONFLICT on 
                    EVENTSTAFFRESOURCECONFLICT.VOLUNTEERTYPEID = EVENTSTAFFRESOURCE.VOLUNTEERTYPEID
                where 
                    (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) and
                    (EVENT.ID <> @IGNORERECORDID or (@IGNORERECORDID is null and EVENT.ID is not 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 @STAFFRESOURCESUSED
                (
                    ITINERARYITEMID, 
                    VOLUNTEERTYPEID, 
                    QUANTITYNEEDED,
                    STARTDATETIME,
                    ENDDATETIME
                )
                select 
                    ITINERARYITEM.ID,
                    ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
                    ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED,
                    ITINERARYITEM.STARTDATETIME as STARTDATETIME,
                    ITINERARYITEM.ENDDATETIME as 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.ITINERARYITEMSTAFFRESOURCE on 
                    ITINERARYITEM.ID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
                inner join @STAFFRESOURCESTABLE as EVENTSTAFFRESOURCECONFLICT on 
                    EVENTSTAFFRESOURCECONFLICT.VOLUNTEERTYPEID = ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID
                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

                    (ITINERARY.RESERVATIONID <> @IGNORESUPERRECORDID or @IGNORESUPERRECORDID is null) and
                    (
                        (ITINERARYITEM.ID <> @IGNORESUBRECORDID or @IGNORESUBRECORDID is null) or
                        (ITINERARYITEM.ITINERARYID <> @IGNORERECORDID or @IGNORERECORDID is null) 
                    ) 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 @STAFFRESOURCESUSED
                (
                    ITINERARYID, 
                    VOLUNTEERTYPEID, 
                    QUANTITYNEEDED,
                    STARTDATETIME,
                    ENDDATETIME
                )
                select 
                    ITINERARY.ID,
                    ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
                    ITINERARYSTAFFRESOURCE.QUANTITYNEEDED,
                    ITINERARY.STARTDATETIME as STARTDATETIME,
                    ITINERARY.ENDDATETIME as ENDDATETIME
                from dbo.ITINERARY
                inner join dbo.SALESORDER on
                    ITINERARY.RESERVATIONID = SALESORDER.ID
                inner join ITINERARYSTAFFRESOURCE
                    on ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID
                inner join @STAFFRESOURCESTABLE as EVENTSTAFFRESOURCECONFLICT
                    on (EVENTSTAFFRESOURCECONFLICT.VOLUNTEERTYPEID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID)
                where 
                    SALESORDER.STATUSCODE <> 5 and -- Make sure the reservation is not cancelled, cancelled resources do not count against total

                    (ITINERARY.RESERVATIONID <> @IGNORESUPERRECORDID or @IGNORESUPERRECORDID is null) and
                    (
                        @IGNORESUBRECORDID is not null or 
                        (ITINERARY.ID <> @IGNORERECORDID or @IGNORERECORDID 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 @OFFSETSTAFFRESOURCES is not null
                begin
                    delete from @STAFFRESOURCESTABLE;

                    with STAFFRESOURCES_CTE as
                    (
                        select 
                            T.item.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') as VOLUNTEERTYPEID,
                            T.item.value('(QUANTITYNEEDED)[1]','integer') as QUANTITYNEEDED
                        from @OFFSETSTAFFRESOURCES.nodes('/OFFSETSTAFFRESOURCES/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')
                                )
                            ) and 
                            T.item.value('(FILLEDBYCODE)[1]','tinyint') = 0 /* Volunteer */
                    )
                    insert into @STAFFRESOURCESTABLE
                    select
                        ST.VOLUNTEERTYPEID,
                        sum(ST.QUANTITYNEEDED)
                    from STAFFRESOURCES_CTE as ST
                    group by ST.VOLUNTEERTYPEID                
                end

                declare @CURRENTUSEDSTAFFRESOURCES table
                (
                    EVENTID uniqueidentifier,
                    ITINERARYITEMID uniqueidentifier,
                    ITINERARYID uniqueidentifier,
                    VOLUNTEERTYPEID uniqueidentifier,
                    QUANTITYNEEDED int
                )                

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

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

                    union all

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

                open CONFLICTS_CURSOR;

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

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

                    begin
                        insert into @CURRENTUSEDSTAFFRESOURCES
                        (
                            EVENTID,
                            ITINERARYITEMID,
                            ITINERARYID,
                            VOLUNTEERTYPEID,
                            QUANTITYNEEDED
                        )
                        values
                        (
                            @THISEVENTID,
                            @THISITINERARYITEMID,
                            @THISITINERARYID,
                            @THISVOLUNTEERTYPEID,
                            @THISQUANTITYNEEDED
                        )

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

                        -- Not inserting duplicates


                        insert into @CONFLICTS
                        (
                            EVENTID, 
                            ITINERARYITEMID,
                            ITINERARYID,
                            VOLUNTEERTYPEID, 
                            QUANTITYNEEDED
                        )
                        select 
                            STAFFRESOURCESUSED.EVENTID, 
                            STAFFRESOURCESUSED.ITINERARYITEMID,
                            STAFFRESOURCESUSED.ITINERARYID,
                            STAFFRESOURCESUSED.VOLUNTEERTYPEID, 
                            STAFFRESOURCESUSED.QUANTITYNEEDED
                        from @CURRENTUSEDSTAFFRESOURCES as STAFFRESOURCESUSED
                        where 
                            not exists
                            (
                                select 1 from @CONFLICTS
                                where
                                (
                                    [@CONFLICTS].EVENTID = STAFFRESOURCESUSED.EVENTID or
                                    [@CONFLICTS].ITINERARYITEMID = STAFFRESOURCESUSED.ITINERARYITEMID or
                                    [@CONFLICTS].ITINERARYID = STAFFRESOURCESUSED.ITINERARYID
                                ) and
                                [@CONFLICTS].VOLUNTEERTYPEID = STAFFRESOURCESUSED.VOLUNTEERTYPEID
                            ) and
                            STAFFRESOURCESUSED.VOLUNTEERTYPEID in
                            (
                                select OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID 
                                from @CURRENTUSEDSTAFFRESOURCES as OVERALLOCATEDSTAFFRESOURCES
                                group by OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID
                                having 
                                (
                                    sum(OVERALLOCATEDSTAFFRESOURCES.QUANTITYNEEDED) + 
                                    (
                                        select top 1 STAFFRESOURCES.QUANTITYNEEDED 
                                        from @STAFFRESOURCESTABLE as STAFFRESOURCES 
                                        where STAFFRESOURCES.VOLUNTEERTYPEID = OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID
                                    ) > 
                                    (
                                        select VOLUNTEERTYPE.QUANTITY from dbo.VOLUNTEERTYPE 
                                        where VOLUNTEERTYPE.ID = OVERALLOCATEDSTAFFRESOURCES.VOLUNTEERTYPEID
                                    )
                                )
                            )

                    end
                    else
                    -- POP

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

                    begin
                        delete from @CURRENTUSEDSTAFFRESOURCES
                        where
                        (
                            @THISEVENTID = [@CURRENTUSEDSTAFFRESOURCES].EVENTID or
                            @THISITINERARYITEMID = [@CURRENTUSEDSTAFFRESOURCES].ITINERARYITEMID or
                            @THISITINERARYID = [@CURRENTUSEDSTAFFRESOURCES].ITINERARYID
                        ) and
                        @THISVOLUNTEERTYPEID = [@CURRENTUSEDSTAFFRESOURCES].VOLUNTEERTYPEID
                    end

                    fetch next from CONFLICTS_CURSOR
                    into 
                        @THISITINERARYID, 
                        @THISITINERARYITEMID, 
                        @THISEVENTID, 
                        @THISVOLUNTEERTYPEID,
                        @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