USP_DATAFORMTEMPLATE_VIEW_ITINERARYITEMEVENTSCHEDULELOAD

The load procedure used by the view dataform template "Itinerary Item Event Schedule Load Data Form"

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	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@CAPACITY	int	INOUT	Capacity
@ITINERARYID	uniqueidentifier	INOUT	Itinerary ID
@PROGRAMID	uniqueidentifier	INOUT	Program
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@STARTTIME	UDT_HOURMINUTE	INOUT	Start time
@ENDTIME	UDT_HOURMINUTE	INOUT	End time
@BLOCKEVENT	bit	INOUT	Mark event unavailable to other groups
@NOTES	nvarchar(500)	INOUT	Notes
@RESOURCES	xml	INOUT	Resources
@STAFFRESOURCES	xml	INOUT	Resources
@LOCATIONS	xml	INOUT	LOCATIONS
@ONSALEINFO	xml	INOUT	On-sale information
@USEPROGRAMPRICES	bit	INOUT	Use program prices
@PRICES	xml	INOUT	Prices

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ITINERARYITEMEVENTSCHEDULELOAD
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @NAME nvarchar(100) = null output,
                @DESCRIPTION nvarchar(255) = null output,
                @CAPACITY int = null output,
                @ITINERARYID uniqueidentifier = null output,
                @PROGRAMID uniqueidentifier = null output,
                @STARTDATE datetime = null output,
                @ENDDATE datetime = null output,
                @STARTTIME dbo.UDT_HOURMINUTE = null output,
                @ENDTIME dbo.UDT_HOURMINUTE = null output,
                @BLOCKEVENT bit = null output,
                @NOTES nvarchar(500) = null output,
                @RESOURCES xml = null output,
                @STAFFRESOURCES xml = null output,
                @LOCATIONS xml = null output,
                @ONSALEINFO xml = null output,
                @USEPROGRAMPRICES bit = null output,
                @PRICES xml = null output
            )
            as
                set nocount on;

                set @DATALOADED = 0;

                declare @CURRENTDATE datetime 
                    set @CURRENTDATE = getdate()

                select
                    @DATALOADED = 1,
                    @PROGRAMID = PROGRAMID,
                    @ITINERARYID = ITINERARYID,
                    @STARTDATE = STARTDATE,
                    @ENDDATE = ENDDATE,
                    @STARTTIME = STARTTIME,
                    @ENDTIME = ENDTIME,
                    @BLOCKEVENT = BLOCKEVENT,
                    @NOTES = NOTES
                from dbo.ITINERARYITEM
                where ID = @ID

                if @DATALOADED = 1
                begin
                    select
                        @NAME = NAME,
                        @DESCRIPTION = DESCRIPTION,
                        @CAPACITY = CAPACITY,
                        @LOCATIONS = dbo.UFN_PROGRAM_GETSEQUENCEDLOCATIONS_TOITEMLISTXML(@PROGRAMID),
                        @RESOURCES = dbo.UFN_PROGRAMRESOURCE_GETRESOURCES_TOITEMLISTXML(@PROGRAMID),
                        @STAFFRESOURCES = dbo.UFN_PROGRAMSTAFFRESOURCE_GETRESOURCESWITHJOBS_TOITEMLISTXML(@PROGRAMID),
                        @PRICES = dbo.UFN_PROGRAM_GETPRICES_TOITEMLISTXML(@PROGRAMID)
                    from dbo.PROGRAM
                    where ID = @PROGRAMID

                    -- Need to update the EVENTLOCATIONID node to be LOCATIONID so it match the spec's collection field

                    set @LOCATIONS = (
                        select
                            T.c.value('(CAPACITY)[1]','int') as 'CAPACITY',
                            T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier') as 'LOCATIONID',
                            T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
                            T.c.value('(SEQUENCE)[1]','int') as 'SEQUENCE'
                        from
                            @LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)
                        for xml raw('ITEM'),type,elements,root('LOCATIONS'),binary base64
                    );

                    set @ONSALEINFO =
                    (
                        select 
                            SALESMETHODID,
                            case ONSALETYPECODE
                                when 0 then @CURRENTDATE
                                when 1 then ONSALEDATE
                                when 2 then dateadd(day, -1 * ONSALETIMEBEFORE, @STARTDATE)
                                when 3 then dateadd(week, -1 * ONSALETIMEBEFORE, @STARTDATE)
                                when 4 then dateadd(month, -1 * ONSALETIMEBEFORE, @STARTDATE)
                            end as ONSALEDATE,
                            case ONSALETYPECODE
                                when 0 then dbo.UFN_HOURMINUTE_GETFROMDATE(@CURRENTDATE)
                                else ONSALETIME
                            end as ONSALETIME
                        from dbo.PROGRAMSALESMETHOD
                        where PROGRAMID = @PROGRAMID
                        for xml raw('ITEM'),type,elements,root('ONSALEINFO'),binary base64
                    );    

                    if @PRICES is null
                        set @USEPROGRAMPRICES = 0
                    else
                        set @USEPROGRAMPRICES = 1

                end

                return 0;