USP_DATAFORMTEMPLATE_EDITLOAD_ITINERARYITEMEVENT

The load procedure used by the edit dataform template "Itinerary Item Event Edit Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@ITINERARYID	uniqueidentifier	INOUT	Itinerary ID
@PROGRAMID	uniqueidentifier	INOUT	Program
@EVENTID	uniqueidentifier	INOUT	Event
@ITINERARYITEMSTARTTIME	UDT_HOURMINUTE	INOUT	Start time
@ITINERARYITEMENDTIME	UDT_HOURMINUTE	INOUT	End time
@ITINERARYITEMSTARTDATE	datetime	INOUT	Start date
@ITINERARYITEMENDDATE	datetime	INOUT	End date
@EVENTS	xml	INOUT	Events
@BLOCKEVENT	bit	INOUT	Mark event unavailable to other groups
@NOTES	nvarchar(500)	INOUT	Notes
@RESOURCES	xml	INOUT	Supplies/Equipment resources
@IGNORECONFLICTS	bit	INOUT	Ignore conflicts when saving
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@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.
@STAFFRESOURCES	xml	INOUT	Staffing resources
@PRICINGCODE	tinyint	INOUT
@RESERVATIONRATESCALEINCLUDEALLPROGRAMS	bit	INOUT
@RESERVATIONRATESCALEPROGRAMS	xml	INOUT
@ITEMTYPECODE	tinyint	INOUT
@STARTTIME	UDT_HOURMINUTE	INOUT	Start time
@ENDTIME	UDT_HOURMINUTE	INOUT	End time
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@ITINERARYCAPACITY	int	INOUT	Itinerary capacity

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_ITINERARYITEMEVENT
                ( 
                    @ID uniqueidentifier,
                    @ITINERARYID uniqueidentifier = null output,
                    @PROGRAMID uniqueidentifier = null output,
                    @EVENTID uniqueidentifier = null output,
                    @ITINERARYITEMSTARTTIME dbo.UDT_HOURMINUTE = null output,
                    @ITINERARYITEMENDTIME dbo.UDT_HOURMINUTE = null output,
                    @ITINERARYITEMSTARTDATE datetime = null output,
                    @ITINERARYITEMENDDATE datetime = null output,
                    @EVENTS xml = null output,
                    @BLOCKEVENT bit = null output,
                    @NOTES nvarchar(500) = null output,
                    @RESOURCES xml = null output,
                    @IGNORECONFLICTS bit = null output,
                    @DATALOADED bit = 0 output,
                    @TSLONG bigint = 0 output,
                    @STAFFRESOURCES xml = null output,
                    @PRICINGCODE tinyint = null output,
                    @RESERVATIONRATESCALEINCLUDEALLPROGRAMS bit = null output,
                    @RESERVATIONRATESCALEPROGRAMS xml = null output,
                    @ITEMTYPECODE tinyint = null output,
                    @STARTTIME dbo.UDT_HOURMINUTE = null output,
                    @ENDTIME dbo.UDT_HOURMINUTE = null output,
                    @STARTDATE datetime = null output,
                    @ENDDATE datetime = null output,
                    @ITINERARYCAPACITY int = null output
                )
                as
                    set nocount on;

                    declare @ARRIVALDATETIMEWITHOFFSET datetimeoffset;
                    declare @MAXDATETIMEWITHOFFSET datetimeoffset;
                    declare @RESERVATIONID uniqueidentifier;

                    declare @CURRENTDATETIMEWITHOFFSET datetimeoffset

                    set @CURRENTDATETIMEWITHOFFSET = sysdatetimeoffset()

                    select @DATALOADED = 1,
                        @TSLONG = TSLONG,
                        @ITINERARYITEMSTARTDATE = STARTDATE,
                        @ITINERARYITEMENDDATE = ENDDATE,
                        @EVENTID = EVENTID,
                        @NOTES = NOTES,
                        @RESOURCES=dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCES_TOITEMLISTXML(@ID),
                        @STAFFRESOURCES = dbo.UFN_ITINERARYITEM_GETSTAFFRESOURCES_TOITEMLISTXML(@ID),
                        @ITINERARYITEMSTARTTIME = STARTTIME,
                        @ITINERARYITEMENDTIME = ENDTIME,
                        @BLOCKEVENT = BLOCKEVENT,
                        @ITINERARYID = ITINERARYID,
                        @IGNORECONFLICTS = 0,
                        @ITEMTYPECODE = ITEMTYPECODE,
                        @STARTDATE = STARTDATE,
                        @ENDDATE = ENDDATE,
                        @STARTTIME = STARTTIME,
                        @ENDTIME = ENDTIME
                    from dbo.ITINERARYITEM
                    where ITINERARYITEM.ID = @ID

                    select
                        @ARRIVALDATETIMEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(dbo.UFN_DATE_ADDHOURMINUTE(ARRIVALDATE, ARRIVALTIME), 0),
                        @PRICINGCODE = RESERVATION.PRICINGCODE,
                        @RESERVATIONRATESCALEINCLUDEALLPROGRAMS = RESERVATIONRATESCALE.INCLUDEALLPROGRAMS,
                        @RESERVATIONRATESCALEPROGRAMS = dbo.UFN_RESERVATIONRATESCALE_GETPROGRAMS_TOITEMLISTXML(RESERVATION.ID),
                        @RESERVATIONID = RESERVATION.ID
                    from dbo.ITINERARY
                    inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
                    left outer join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
                    where ITINERARY.ID = @ITINERARYID

                    set @MAXDATETIMEWITHOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(dateadd(d, 4, dbo.UFN_DATE_GETLATESTTIME(@ARRIVALDATETIMEWITHOFFSET)), 0);

                    select @PROGRAMID = PROGRAMID
                    from dbo.EVENT
                    where EVENT.ID = @EVENTID;

                    declare @BLOCKEDEVENTS table
                    (
                        BLOCK bit,
                        EVENTID uniqueidentifier
                    )


                    insert into @BLOCKEDEVENTS
                    (
                        BLOCK,
                        EVENTID
                    )
                    select 
                        1 as BLOCK,
                        EVENTID
                    from 
                        dbo.ITINERARYITEM 
                        inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                        where ITINERARYITEM.BLOCKEVENT = 1 
                            and ITINERARY.RESERVATIONID = @RESERVATIONID
                            and ITINERARYITEM.ITEMTYPECODE = 0
                        group by EVENTID;



                    with EVENTS_CTE as
                    (
                        select 
                            EVENT.ID as EVENTID,
                            EVENT.PROGRAMID,
                            EVENT.NAME,
                            EVENT.STARTTIME,
                            EVENT.ENDTIME,
                            dbo.UFN_GETEVENT_TIMESPAN(EVENT.ID) as TIMESPAN,
                            dbo.UFN_EVENT_GETAVAILABILITY(EVENT.ID) as AVAILABILITY,
                            dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATION,
                            dbo.UFN_EVENTRESOURCE_GETRESOURCESTRING(EVENT.ID) as RESOURCES,
                            dbo.UFN_EVENTSTAFFRESOURCE_GETSTAFFRESOURCESTRING(EVENT.ID) as STAFFRESOURCES,
                            EVENT.STARTDATE,
                            EVENT.ENDDATE,
                            coalesce(BL.BLOCK, 0) as EVENTBLOCK
                        from dbo.EVENT
                            inner join dbo.EVENTSALESMETHOD on EVENT.ID = EVENTSALESMETHOD.EVENTID
                            inner join dbo.SALESMETHOD on EVENTSALESMETHOD.SALESMETHODID = SALESMETHOD.ID
                            left join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
                            left join @BLOCKEDEVENTS BL on EVENT.ID = BL.EVENTID
                        where @ARRIVALDATETIMEWITHOFFSET <= EVENT.STARTDATETIMEWITHOFFSET
                            and @MAXDATETIMEWITHOFFSET >= EVENT.STARTDATETIMEWITHOFFSET
                            and EVENT.ISACTIVE = 1
                            and SALESMETHOD.TYPECODE = 3
                            and dbo.UFN_EVENT_ISONSALE_DATETIMEOFFSET(EVENT.ID, @CURRENTDATETIMEWITHOFFSET, 3) = 1
                            and PROGRAM.ISACTIVE = 1
                            and PROGRAM.ISDAILYADMISSION = 0
                            and PROGRAM.ISPREREGISTERED = 0

                        union

                        select 
                            EVENT.ID as EVENTID,
                            EVENT.PROGRAMID,
                            EVENT.NAME,
                            EVENT.STARTTIME,
                            EVENT.ENDTIME,
                            dbo.UFN_GETEVENT_TIMESPAN(EVENT.ID) as TIMESPAN,
                            dbo.UFN_EVENT_GETAVAILABILITY(EVENT.ID) as AVAILABILITY,
                            dbo.UFN_EVENT_GETLOCATIONNAME(EVENT.ID) as LOCATION,
                            dbo.UFN_EVENTRESOURCE_GETRESOURCESTRING(EVENT.ID) as RESOURCES,
                            dbo.UFN_EVENTSTAFFRESOURCE_GETSTAFFRESOURCESTRING(EVENT.ID) as STAFFRESOURCES,
                            EVENT.STARTDATE,
                            EVENT.ENDDATE,
                            coalesce(BL.BLOCK, 0) as EVENTBLOCK                        
                        from dbo.EVENT
                        left join @BLOCKEDEVENTS BL on EVENT.ID = BL.EVENTID
                        where EVENT.ID = @EVENTID
                    )


                    select @EVENTS =
                    (
                        select * from EVENTS_CTE
                        order by STARTDATE, STARTTIME asc
                        for xml raw ('ITEM'), type, elements, root('EVENTS'), BINARY BASE64
                    );

                    select @ITINERARYCAPACITY = sum(ITINERARYATTENDEE.QUANTITY)
                    from dbo.ITINERARYATTENDEE
                    where ITINERARYATTENDEE.ITINERARYID = @ITINERARYID

                    return 0;