USP_DATAFORMTEMPLATE_VIEW_MICROSITE_EVENT

The load procedure used by the view dataform template "Microsite Programming Event 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.
@ROWTYPE	tinyint	INOUT	Row Type
@PROGRAMNAME	nvarchar(100)	INOUT	Program
@PROGRAMISACTIVE	bit	INOUT	Program active
@PROGRAMONLINE	bit	INOUT	Approved for website
@EVENTNAME	nvarchar(100)	INOUT	Event
@EVENTISACTIVE	bit	INOUT	Event active
@EVENTONLINE	bit	INOUT	Approved for website
@EVENTONSALEDATETIME	datetime	INOUT	Web sale date
@AVAILABILITY	nvarchar(max)	INOUT	Remaining capacity
@STARTDATE	date	INOUT
@STARTTIME	UDT_HOURMINUTE	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MICROSITE_EVENT
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ROWTYPE tinyint = null output,
    @PROGRAMNAME nvarchar(100) = null output,
    @PROGRAMISACTIVE bit = null output, 
    @PROGRAMONLINE bit = null output,
    @EVENTNAME nvarchar(100) = null output,
    @EVENTISACTIVE bit = null output, 
    @EVENTONLINE bit = null output, 
    @EVENTONSALEDATETIME datetime = null output,
    @AVAILABILITY nvarchar(max) = null output,
    @STARTDATE date = null output,
    @STARTTIME dbo.UDT_HOURMINUTE = 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
    declare @CURRENTDATETIME datetime = getdate();

    if exists(select ID from PROGRAM where ID = @ID)

        select 
            @DATALOADED = 1,
            @ROWTYPE = PROGRAM.ISDAILYADMISSION,
            @PROGRAMNAME = PROGRAM.NAME,
            @PROGRAMISACTIVE = PROGRAM.ISACTIVE,
            @PROGRAMONLINE = case when PROGRAMSALESMETHOD.ID is null then 0 else 1 end,
            @EVENTNAME = null,
            @EVENTISACTIVE = null,
            @EVENTONLINE = null,
            @EVENTONSALEDATETIME = null,
            @AVAILABILITY = '',
            @STARTDATE = null,
            @STARTTIME = null
        from dbo.PROGRAM
        left outer join dbo.PROGRAMSALESMETHOD
            on PROGRAM.ID = PROGRAMSALESMETHOD.PROGRAMID 
            and PROGRAMSALESMETHOD.SALESMETHODID = @ONLINESALESMETHOD 
        where PROGRAM.ID = @ID

    else

        select
            @DATALOADED = 1,
            @ROWTYPE = 2, -- 2 = Programmed Event
            @PROGRAMNAME = PROGRAM.NAME, 
            @PROGRAMISACTIVE = PROGRAM.ISACTIVE,
            @PROGRAMONLINE = null, -- not really applicable for events, not bothering joining to PROGRAMSALESMETHOD 
            @EVENTNAME = EVENT.NAME,
              @EVENTISACTIVE = EVENT.ISACTIVE,
          @EVENTONLINE = case when EVENTSALESMETHOD.ONSALEDATETIME is null then 0 else 1 end,--(@CURRENTDATETIME between EVENTSALESMETHOD.ONSALEDATETIME and EVENT.ENDDATETIME) --dbo.UFN_DATE_ADDHOURMINUTE(EVENTSALESMETHOD.ONSALEDATE, EVENTSALESMETHOD.ONSALETIME) (EVENTSALESMETHOD.ONSALEDATE is not null) and (EVENTSALESMETHOD.ONSALETIME is not null)
              @EVENTONSALEDATETIME = EVENTSALESMETHOD.ONSALEDATETIME,
          @AVAILABILITY = cast(dbo.UFN_EVENT_GETAVAILABILITY(@ID) as nvarchar(20)) + '/' + cast(EVENT.CAPACITY as nvarchar(20)),
          @STARTDATE = EVENT.STARTDATE,
          @STARTTIME = EVENT.STARTTIME            
        from dbo.EVENT
        left outer join dbo.EVENTSALESMETHOD
            on (EVENT.ID = EVENTSALESMETHOD.EVENTID)
            and (EVENTSALESMETHOD.SALESMETHODID = @ONLINESALESMETHOD )
        inner join dbo.PROGRAM 
            on EVENT.PROGRAMID = PROGRAM.ID   
                where EVENT.ID = @ID

    return 0;