USP_EVENTCONFLICT_SAVERESOLVECONFLICT

                    CREATE procedure dbo.USP_EVENTCONFLICT_SAVERESOLVECONFLICT 
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @NAME nvarchar(100),
                        @STARTDATE datetime,
                        @ENDDATE datetime,
                        @STARTTIME dbo.UDT_HOURMINUTE,
                        @ENDTIME dbo.UDT_HOURMINUTE,
                        @CONFLICTINGEVENTS xml,
                        @LOCATIONS xml
                    )
                    as 
                        set nocount on;

                        if @CHANGEAGENTID is NULL 
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        begin try
                            -- Location(s) must exist.

                            if @LOCATIONS is null
                                raiserror('ERR_LOCATION_REQUIRED', 13, 1)

                            -- Check for duplicate locations.

                            if exists
                            (
                                select count(LOCATIONID)
                                from
                                (
                                    select T.locations.value('(EVENTLOCATIONID)[1]', 'uniqueidentifier') LOCATIONID
                                    from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(LOCATIONS)
                                ) Result
                                group by LOCATIONID
                                having count(*) > 1
                            )
                                raiserror('ERR_DUPLICATE_LOCATIONS', 13, 1)

                            declare @CONFLICTID uniqueidentifier=null;
                            declare @STIME datetime;
                            declare @ETIME datetime;

                            set @STIME=dbo.UFN_DATE_ADDHOURMINUTE(@STARTDATE,@STARTTIME);
                            set @ETIME=dbo.UFN_DATE_ADDHOURMINUTE(@ENDDATE,@ENDTIME);

                            -- Check to make sure new event is not scheduled in the past.

                            if @STIME < getdate()
                                raiserror('ERR_INVALID_DATE', 13, 1)


                            select TOP(1) @CONFLICTID = EVENT.ID
                            from dbo.EVENT
                            left join dbo.PROGRAMEVENTLOCATION on EVENT.ID = PROGRAMEVENTLOCATION.EVENTID 
                            inner join @LOCATIONS.nodes('/LOCATIONS/ITEM') T(LOCATIONS) on (T.locations.value('(EVENTLOCATIONID)[1]', 'uniqueidentifier') = PROGRAMEVENTLOCATION.EVENTLOCATIONID or
                                T.locations.value('(EVENTLOCATIONID)[1]', 'uniqueidentifier') = EVENT.EVENTLOCATIONID)
                            left join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                            where
                            (PROGRAM.ISACTIVE = 1  or EVENT.PROGRAMID is null) and
                            (@STIME >= EVENT.STARTDATETIME and @STIME < EVENT.ENDDATETIME) or
                            (@ETIME > EVENT.STARTDATETIME and @ETIME <= EVENT.ENDDATETIME) or
                            (@STIME <= EVENT.STARTDATETIME and @ETIME >= EVENT.ENDDATETIME)


                            if @CONFLICTID is null 
                            begin
                                declare @SCHEDULEID uniqueidentifier;
                                declare @PROGRAMID uniqueidentifier;
                                declare @DESCRIPTION nvarchar(500);
                                declare @CAPACITY integer;

                                select @SCHEDULEID = SCHEDULEID,
                                    @PROGRAMID = PROGRAMID,
                                    @DESCRIPTION = DESCRIPTION,
                                    @CAPACITY = CAPACITY
                                from dbo.EVENTCONFLICT
                                where ID = @ID

                                exec dbo.USP_PROGRAMEVENTLOCATION_GETEVENTCONFLICTLOCATIONS_UPDATEFROMXML @ID, @LOCATIONS, @CHANGEAGENTID;

                                --1.insert into the event table                

                                declare @EVENTID uniqueidentifier;
                                exec dbo.USP_EVENT_INSERT @EVENTID output, @SCHEDULEID,@PROGRAMID,@NAME,@DESCRIPTION,@CAPACITY,@STARTDATE,@ENDDATE,@STIME,@ETIME,@CHANGEAGENTID;

                                --2.update the event location table

                                update dbo.PROGRAMEVENTLOCATION 
                                set EVENTID=@EVENTID, EVENTCONFLICTID = null
                                where EVENTCONFLICTID=@ID;

                                --3.delete it from the event conflict table.

                                exec USP_EVENTCONFLICT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
                            end
                            else
                            begin
                                declare @EVENTNAME nvarchar(100)
                                declare @EVENTSTARTDATE datetime
                                declare @EVENTENDDATE datetime
                                declare @EVENTSTARTTIME udt_HOURMINUTE
                                declare @EVENTENDTIME udt_HOURMINUTE
                                declare @LOCATION nvarchar(100)

                                select @EVENTNAME = NAME,
                                    @EVENTSTARTDATE = STARTDATE,
                                    @EVENTENDDATE = ENDDATE,
                                    @LOCATION = dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID),
                                    @EVENTSTARTTIME = STARTTIME,
                                    @EVENTENDTIME = ENDTIME
                                from dbo.EVENT
                                where ID = @CONFLICTID

                                declare @ERRORMESSAGE nvarchar(max)

                                set @ERRORMESSAGE = 'Cannot schedule this event due to a conflict.' + CHAR(10) + char(10) +
                                    @NAME + char(10) + convert(nvarchar(10), @STARTDATE, 101) + char(10) + 
                                    dbo.UFN_HOURMINUTE_DISPLAYTIME(@STARTTIME) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(@ENDTIME) + char(10) +
                                    @LOCATION + char(10) + char(10) + 'Conflicts with:' + char(10) + char(10) +
                                    @EVENTNAME + char(10) + convert(nvarchar(10), @EVENTSTARTDATE, 101) + char(10) + 
                                    dbo.UFN_HOURMINUTE_DISPLAYTIME(@EVENTSTARTTIME) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(@EVENTENDTIME) + char(10) +
                                    @LOCATION;

                                raiserror(@ERRORMESSAGE,13,1)
                            end
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                        return 0;