USP_DATAFORMTEMPLATE_VIEW_EVENTONLINE

The load procedure used by the view dataform template "Event Online View"

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
@PUBLICDESCRIPTIONHTML	nvarchar(max)	INOUT	Description
@ONSALEDATETIME	datetime	INOUT	Web sale date
@BEFOREONSALE	bit	INOUT	Event is not yet on sale
@AVAILABILITY	int	INOUT	Availability
@CAPACITY	int	INOUT	Capacity
@LOCATIONNAME	nvarchar(500)	INOUT	Location
@DATE	datetime	INOUT	Date
@STARTTIME	UDT_HOURMINUTE	INOUT	Time
@ENDTIME	UDT_HOURMINUTE	INOUT	End time
@PROGRAMID	uniqueidentifier	INOUT	Program ID
@PROGRAMNAME	nvarchar(100)	INOUT	Program name
@PROGRAMPUBLICDESCRIPTIONHTML	nvarchar(max)	INOUT	Program description
@PROGRAMISACTIVE	bit	INOUT	Program active
@PROGRAMISPREREGISTERED	bit	INOUT	Program requires registration
@ACKNOWLEDGEMENTEMAILTEMPLATEID	int	INOUT
@STARTDATETIMEWITHOFFSET	datetimeoffset	INOUT
@ENDDATETIMEWITHOFFSET	datetimeoffset	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EVENTONLINE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(100) = null output,
    @PUBLICDESCRIPTIONHTML nvarchar(max) = null output,
    @ONSALEDATETIME datetime = null output,
    @BEFOREONSALE bit = null output,
    @AVAILABILITY int = null output,
    @CAPACITY int = null output,
    @LOCATIONNAME nvarchar(500) = null output,
    @DATE datetime = null output,
    @STARTTIME dbo.UDT_HOURMINUTE = null output,
    @ENDTIME dbo.UDT_HOURMINUTE = null output,
    @PROGRAMID uniqueidentifier = null output,
    @PROGRAMNAME nvarchar(100) = null output,
    @PROGRAMPUBLICDESCRIPTIONHTML nvarchar(max) = null output,
    @PROGRAMISACTIVE bit = null output,
    @PROGRAMISPREREGISTERED bit = null output,
    @ACKNOWLEDGEMENTEMAILTEMPLATEID int = null output,
    @STARTDATETIMEWITHOFFSET datetimeoffset(3) = null output,
    @ENDDATETIMEWITHOFFSET datetimeoffset(3) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    declare @ONLINESALESMETHOD uniqueidentifier = dbo.UFN_SALESMETHOD_GETIDFROMTYPECODE(2);--2 = online

    select 
        @DATALOADED = 1,
        @NAME = EVENT.NAME,
        @PUBLICDESCRIPTIONHTML = case when EVENT.SUPERCEDESPROGRAMPUBLICDESCRIPTION = 0 then PROGRAM.PUBLICDESCRIPTIONHTML else EVENT.PUBLICDESCRIPTIONHTML end,
        @ONSALEDATETIME = EVENTSALESMETHOD.ONSALEDATETIMEWITHOFFSET,
        @BEFOREONSALE = case when (sysdatetimeoffset() < EVENTSALESMETHOD.ONSALEDATETIMEWITHOFFSET) then 1 else 0 end,
        @AVAILABILITY = dbo.UFN_EVENT_GETAVAILABILITY(@ID),
        @CAPACITY = EVENT.CAPACITY,
        @LOCATIONNAME = dbo.UFN_EVENT_GETLOCATIONNAME(@ID),
        @DATE = EVENT.STARTDATE,
        @STARTTIME = EVENT.STARTTIME,
        @ENDTIME = EVENT.ENDTIME,
        @PROGRAMID = PROGRAM.ID,
        @PROGRAMNAME = PROGRAM.NAME, 
        @PROGRAMPUBLICDESCRIPTIONHTML = PROGRAM.PUBLICDESCRIPTIONHTML,
        @PROGRAMISACTIVE = PROGRAM.ISACTIVE,
        @PROGRAMISPREREGISTERED = PROGRAM.ISPREREGISTERED,
        @ACKNOWLEDGEMENTEMAILTEMPLATEID = isnull([EVENT_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID], [PROGRAM_MICROSITEEMAILTEMPLATE].[EMAILTEMPLATEID]),
        @STARTDATETIMEWITHOFFSET = EVENT.STARTDATETIMEWITHOFFSET,
        @ENDDATETIMEWITHOFFSET = 
            case
                when [PROGRAMSALESMETHOD].[ID] is null then [EVENT].[ENDDATETIMEWITHOFFSET]
                when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 0 then [EVENT].[STARTDATETIMEWITHOFFSET]
                when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 1 then dateadd(mi, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 2 then dateadd(hh, -1 * [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
                when [PROGRAMSALESMETHOD].[ONSALEENDTYPECODE] = 3 then dateadd(mi, [PROGRAMSALESMETHOD].[ONSALEENDINTERVAL], [EVENT].[STARTDATETIMEWITHOFFSET])
            end
    from dbo.EVENT
    left join dbo.[EVENT_MICROSITEEMAILTEMPLATE] on
        ([EVENT].[ID] = [EVENT_MICROSITEEMAILTEMPLATE].[EVENTID]) and ([EVENT_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
    left outer join dbo.EVENTSALESMETHOD
        on (EVENT.ID = EVENTSALESMETHOD.EVENTID)
        and (EVENTSALESMETHOD.SALESMETHODID = @ONLINESALESMETHOD)
    inner join dbo.PROGRAM 
        on (EVENT.PROGRAMID = PROGRAM.ID)
    left join dbo.[PROGRAMSALESMETHOD]
            on PROGRAM.ID = [PROGRAMSALESMETHOD].[PROGRAMID]
            and    [PROGRAMSALESMETHOD].[SALESMETHODID] = @ONLINESALESMETHOD
    left join dbo.[PROGRAM_MICROSITEEMAILTEMPLATE] on
        ([PROGRAM].[ID] = [PROGRAM_MICROSITEEMAILTEMPLATE].[PROGRAMID]) and ([PROGRAM_MICROSITEEMAILTEMPLATE].[ACTIVE] = 1)
    where EVENT.ID = @ID

    return 0;