UFN_CONFLICTCHECK_GETSTAFFRESOURCECONFLICTS

        CREATE function dbo.UFN_CONFLICTCHECK_GETSTAFFRESOURCECONFLICTS
        (
            @START datetime,
            @END datetime,
            @STAFFRESOURCES xml,
            @OFFSETSTAFFRESOURCES xml = null,
            @SUPERRECORDID uniqueidentifier = null,
            @RECORDID uniqueidentifier = null,
            @SUBRECORDID uniqueidentifier = null,
            @IGNORESUPERRECORD bit = 1,
            @IGNORERECORD bit = 1,
            @IGNORESUBRECORD bit = 1,
            @IGNORERECORDSUBRECORDS bit = 1
        )
        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 */

            insert into @STAFFRESOURCESTABLE
            (VOLUNTEERTYPEID, QUANTITYNEEDED)
            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 */

            -- 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
                (@IGNORERECORD = 0 or @RECORDID is null or EVENT.ID <> @RECORDID) 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

                (@IGNORESUPERRECORD = 0 or @SUPERRECORDID is null or ITINERARY.RESERVATIONID <> @SUPERRECORDID) and
                (
                    not (ITINERARYITEM.ITINERARYID = @RECORDID and @IGNORERECORDSUBRECORDS = 1) and
                    (@IGNORESUBRECORD = 0 or @SUBRECORDID is null or ITINERARYITEM.ID <> @SUBRECORDID) 
                ) 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

                (@IGNORERECORD = 0 or @RECORDID is null or ITINERARY.ID <> @RECORDID) and
                (@IGNORESUPERRECORD = 0 or @SUPERRECORDID is null or ITINERARY.RESERVATIONID <> @SUPERRECORDID) and
                (
                    (@START >= ITINERARY.STARTDATETIME and @START < ITINERARY.ENDDATETIME) or
                    (@END > ITINERARY.STARTDATETIME and @END <= ITINERARY.ENDDATETIME) or
                    (@START < ITINERARY.STARTDATETIME and @END > ITINERARY.ENDDATETIME) or    
                    -- Checking against an itinerary that has a time extended by the itinerary item

                    (@RECORDID is not null and @RECORDID = ITINERARY.ID)
                )

            -- Set itinerary times to be within itinerary item window when changing time of itinerary

            -- Times outside of @START and @END are not an issue against conflicts

            if @RECORDID is not null and @SUBRECORDID is not null and @IGNORERECORD = 0
                update @STAFFRESOURCESUSED set
                    STARTDATETIME = @START,
                    ENDDATETIME = @END
                where
                    ITINERARYID is not null and
                    ITINERARYID = @RECORDID

            -- 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
                with STAFFRESOURCES_CTE as
                (
                    select 
                        T.item.value('(ITINERARYID)[1]','uniqueidentifier') as ITINERARYID,
                        T.item.value('(ITINERARYITEMID)[1]','uniqueidentifier') as ITINERARYITEMID,
                        T.item.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') as VOLUNTEERTYPEID,
                        T.item.value('(QUANTITYNEEDED)[1]','integer') as QUANTITYNEEDED,
                        T.item.value('(STARTDATETIME)[1]','datetime') as STARTDATETIME,
                        T.item.value('(ENDDATETIME)[1]','datetime') as ENDDATETIME
                    from @OFFSETSTAFFRESOURCES.nodes('/OFFSETSTAFFRESOURCES/ITEM') T(item)
                    where 
                        (
                            (
                                @START >= T.item.value('(STARTDATETIME)[1]','datetime') and 
                                @START < T.item.value('(ENDDATETIME)[1]','datetime')
                            ) or
                            (
                                @END > T.item.value('(STARTDATETIME)[1]','datetime') 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 @STAFFRESOURCESUSED
                (
                    ITINERARYID,
                    ITINERARYITEMID,
                    VOLUNTEERTYPEID, 
                    QUANTITYNEEDED,
                    STARTDATETIME,
                    ENDDATETIME
                )
                select
                    case 
                        when ITINERARYITEMID is null or ITINERARYITEMID = '00000000-0000-0000-0000-000000000000' 
                            then ITINERARYID
                        else null 
                    end as ITINERARYID,
                    ITINERARYITEMID,
                    VOLUNTEERTYPEID,
                    QUANTITYNEEDED,
                    STARTDATETIME,
                    ENDDATETIME
                from STAFFRESOURCES_CTE as ST
                where
                    -- Staff Resources already in itinerary should be in STAFFRESOURCESTABLE 

                    (
                        @IGNORERECORD = 0 or -- Double counting resources if not ignoring record

                        @RECORDID is null or
                        ITINERARYID is null or
                        @SUBRECORDID is not null or
                        (
                            ITINERARYITEMID is not null and
                            ITINERARYITEMID <> '00000000-0000-0000-0000-000000000000'
                        ) or
                        ITINERARYID <> @RECORDID
                    ) and
                    (
                        @IGNORESUBRECORD = 0 or
                        @SUBRECORDID is null or  
                        (
                            ITINERARYITEMID is null or
                            ITINERARYITEMID = '00000000-0000-0000-0000-000000000000'
                        ) or
                        ITINERARYITEMID <> @SUBRECORDID
                    )
            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


                    with STAFFRESOURCETOTAL_CTE as
                    (
                        select
                            VOLUNTEERTYPEID,
                            sum(QUANTITYNEEDED) as QUANTITYNEEDED,
                            count(VOLUNTEERTYPEID) as RESOURCECOUNT
                        from
                        (
                            select
                                VOLUNTEERTYPEID,
                                QUANTITYNEEDED
                            from @CURRENTUSEDSTAFFRESOURCES

                            union all

                            select
                                VOLUNTEERTYPEID,
                                QUANTITYNEEDED
                            from @STAFFRESOURCESTABLE                        
                        ) as T
                        group by T.VOLUNTEERTYPEID
                    )        
                    insert into @CONFLICTS
                    (
                        EVENTID, 
                        ITINERARYITEMID,
                        ITINERARYID,
                        VOLUNTEERTYPEID, 
                        QUANTITYNEEDED
                    )
                    select 
                        STAFFRESOURCESUSED.EVENTID, 
                        STAFFRESOURCESUSED.ITINERARYITEMID,
                        STAFFRESOURCESUSED.ITINERARYID,
                        STAFFRESOURCESUSED.VOLUNTEERTYPEID, 
                        STAFFRESOURCESUSED.QUANTITYNEEDED
                    from @CURRENTUSEDSTAFFRESOURCES as STAFFRESOURCESUSED
                    inner join STAFFRESOURCETOTAL_CTE STAFFRESOURCETOTAL on
                        STAFFRESOURCETOTAL.VOLUNTEERTYPEID = STAFFRESOURCESUSED.VOLUNTEERTYPEID
                    inner join dbo.VOLUNTEERTYPE on
                        STAFFRESOURCESUSED.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                    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
                        STAFFRESOURCETOTAL.QUANTITYNEEDED > VOLUNTEERTYPE.QUANTITY and
                        STAFFRESOURCETOTAL.RESOURCECOUNT > 1

                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;

            return;

        end