USP_EVENTCONFLICT_LOADEDIT

The load procedure used by the edit dataform template "Event Conflict Edit"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT
@STARTDATE	datetime	INOUT	Date
@ENDDATE	datetime	INOUT	End date
@STARTTIME	UDT_HOURMINUTE	INOUT	Start time
@ENDTIME	UDT_HOURMINUTE	INOUT	End time
@CONFLICTINGEVENTS	xml	INOUT	Conflicting Events
@LOCATIONS	xml	INOUT	Location
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.

Definition

Copy



                CREATE procedure dbo.USP_EVENTCONFLICT_LOADEDIT 
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit=0 output,
                    @NAME nvarchar(100)=null output,
                    @STARTDATE datetime=null output,
                    @ENDDATE datetime=null output,
                    @STARTTIME dbo.UDT_HOURMINUTE=null output,
                    @ENDTIME dbo.UDT_HOURMINUTE=null output,
                    @CONFLICTINGEVENTS xml=null output,
                    @LOCATIONS xml = null output,
                    @TSLONG bigint = 0 output
                )
                as 
                set nocount on;

                    declare @EVENTCONFLICTDATA as table
                    (
                        LOCATIONID uniqueidentifier
                    )
                    insert into @EVENTCONFLICTDATA
                    select PROGRAMEVENTLOCATION.EVENTLOCATIONID
                    from dbo.EVENTCONFLICT
                    inner join PROGRAMEVENTLOCATION on EVENTCONFLICT.ID = PROGRAMEVENTLOCATION.EVENTCONFLICTID
                    where EVENTCONFLICT.ID = @ID

                    declare @EDATE datetime;
                    declare @SDATE datetime;

                    select @EDATE = ENDDATETIME, @SDATE = STARTDATETIME
                    from dbo.EVENTCONFLICT
                    where ID = @ID

                    set @CONFLICTINGEVENTS = 
                    (
                        select EVENT.ID, 
                            EVENT.PROGRAMID, 
                            EVENT.NAME, 
                            EVENT.STARTDATE, 
                            EVENT.ENDDATE, 
                            EVENT.STARTTIME, 
                            EVENT.ENDTIME,
                            dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATIONS
                        from dbo.EVENT 
                        left join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                        where (event.ID in (select programeventlocation.EVENTID from dbo.PROGRAMEVENTLOCATION inner join @EVENTCONFLICTDATA ECD on ECD.LOCATIONID = PROGRAMEVENTLOCATION.eventlocationid) or EVENT.EVENTLOCATIONID in (select ECD.locationid from @EVENTCONFLICTDATA ecd)) and 
                            (PROGRAM.ISACTIVE = 1  or EVENT.PROGRAMID is null) and
                            (@SDATE >= EVENT.STARTDATETIME and @SDATE < EVENT.ENDDATETIME) or
                            (@EDATE > EVENT.STARTDATETIME and @EDATE <= EVENT.ENDDATETIME) or
                            (@SDATE <= EVENT.STARTDATETIME and @EDATE >= EVENT.ENDDATETIME)
                        for xml raw ('ITEM'), type, elements, root('CONFLICTINGEVENTS'), BINARY BASE64
                    )

                    select @DATALOADED = 1,
                        @NAME = EVENTCONFLICT.NAME,
                        @STARTDATE = EVENTCONFLICT.STARTDATE,
                        @ENDDATE = EVENTCONFLICT.ENDDATE,
                        @STARTTIME = EVENTCONFLICT.STARTTIME,
                        @ENDTIME = EVENTCONFLICT.ENDTIME,
                        @TSLONG = EVENTCONFLICT.TSLONG
                    from dbo.EVENTCONFLICT
                    where EVENTCONFLICT.ID=@ID;

                    set @LOCATIONS = dbo.UFN_PROGRAMEVENTLOCATION_GETEVENTCONFLICTLOCATIONS_TOITEMLISTXML(@ID)

                return 0;