USP_EVENT_INSERT

Inserts values into the event table.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@SCHEDULEID	uniqueidentifier	IN
@PROGRAMID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@DESCRIPTION	nvarchar(500)	IN
@CAPACITY	int	IN
@EVENTSTARTDATE	date	IN
@EVENTENDDATE	date	IN
@STARTTIME	datetime	IN
@ENDTIME	datetime	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SUPERCEDESPROGRAMPUBLICDESCRIPTION	bit	IN
@PUBLICDESCRIPTIONTEXT	nvarchar(max)	IN
@PUBLICDESCRIPTIONHTML	nvarchar(max)	IN
@SOURCEEVENTID	uniqueidentifier	IN
Definition

Copy


CREATE procedure dbo.USP_EVENT_INSERT
(
    @ID uniqueidentifier = null output,
    @SCHEDULEID uniqueidentifier=null,
    @PROGRAMID uniqueidentifier=null,
    @NAME nvarchar(100)=null,
    @DESCRIPTION nvarchar(500)=null,
    @CAPACITY integer=null,
    @EVENTSTARTDATE date=null,
    @EVENTENDDATE date=null,
    @STARTTIME datetime=null,
    @ENDTIME datetime=null,
    @CHANGEAGENTID uniqueidentifier=null,
    @SUPERCEDESPROGRAMPUBLICDESCRIPTION bit = 0,
    @PUBLICDESCRIPTIONTEXT nvarchar(max) = '',
    @PUBLICDESCRIPTIONHTML nvarchar(max) = '',
    @SOURCEEVENTID uniqueidentifier = null
)
as begin
    set nocount on;

    if @ID is null begin
        set @ID = newid();
    end

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    declare @CURRENTSYSTEMDEFAULTDATE datetime;
    set @CURRENTSYSTEMDEFAULTDATE = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

    declare @HOLDLISTID uniqueidentifier = null;
    declare @PRICELISTID uniqueidentifier = null;

    declare @HASCUSTOMREGISTRATIONINFORMATION bit = 0;
    select @HASCUSTOMREGISTRATIONINFORMATION = HASCUSTOMREGISTRATIONINFORMATION
    from dbo.EVENT
    where ID = @SOURCEEVENTID;

    begin try 
        --handle inserting all the data 

        declare @EVENTHOLDLISTID uniqueidentifier;
        declare @EVENTSALESTYPEID uniqueidentifier;

        --handle the event first          

        insert into dbo.EVENT (
            ID,
            SCHEDULEID,
            PROGRAMID,
            NAME,
            DESCRIPTION,
            CAPACITY,
            STARTDATE,
            STARTTIME,
            ENDDATE,
            ENDTIME,
            PUBLICDESCRIPTIONTEXT,
            PUBLICDESCRIPTIONHTML,
            SUPERCEDESPROGRAMPUBLICDESCRIPTION,
            HASCUSTOMREGISTRATIONINFORMATION,
            ADDEDBYID, 
            CHANGEDBYID, 
            DATEADDED, 
            DATECHANGED
        ) values (
            @ID,
            @SCHEDULEID,
            @PROGRAMID,
            @NAME,
            @DESCRIPTION,
            @CAPACITY,
            @EVENTSTARTDATE,
            dbo.UFN_HOURMINUTE_GETFROMDATE(@STARTTIME),
            @EVENTENDDATE,
            dbo.UFN_HOURMINUTE_GETFROMDATE(@ENDTIME),
            @PUBLICDESCRIPTIONTEXT,
            @PUBLICDESCRIPTIONHTML,
            @SUPERCEDESPROGRAMPUBLICDESCRIPTION,
            @HASCUSTOMREGISTRATIONINFORMATION,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        );

        --handle the sales type

        if exists(select * from dbo.PROGRAMSALESMETHOD where PROGRAMID = @PROGRAMID) begin
            set @EVENTSALESTYPEID = newid();

            insert into EVENTSALESMETHOD (
                ID,
                EVENTID,
                SALESMETHODID,
                ONSALEDATE,
                ONSALETIME,
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED
            )
            select
                newid(),
                @ID,
                SALESMETHODID,
                case ONSALETYPECODE
                    when 0 then @CURRENTSYSTEMDEFAULTDATE
                    when 1 then ONSALEDATE
                    when 2 then dateadd(day, -ONSALETIMEBEFORE, @EVENTSTARTDATE)
                    when 3 then dateadd(week, -ONSALETIMEBEFORE, @EVENTSTARTDATE)
                    when 4 then dateadd(month, -ONSALETIMEBEFORE, @EVENTSTARTDATE)
                end,
                case ONSALETYPECODE
                    when 0 then dbo.UFN_HOURMINUTE_GETFROMDATE(@CURRENTSYSTEMDEFAULTDATE)
                    else ONSALETIME
                end,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from dbo.PROGRAMSALESMETHOD
            where PROGRAMID = @PROGRAMID;
        end

        --handle hold list here 

        select @HOLDLISTID=HOLDLISTID from dbo.PROGRAM where ID=@PROGRAMID;

        if @HOLDLISTID is not null begin
            set @EVENTHOLDLISTID = newid();

            insert into dbo.EVENTHOLDLIST (
                ID, 
                NAME, 
                EVENTID,
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED
            )
            select
                @EVENTHOLDLISTID, 
                NAME,
                @ID,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE 
            from
                dbo.HOLDLIST 
            where
                ID=@HOLDLISTID;
        end

        if @HASCUSTOMREGISTRATIONINFORMATION = 1 begin
            exec dbo.USP_EVENT_COPYREGISTRATIONINFORMATION @SOURCEEVENTID, @ID, @CHANGEAGENTID, @CURRENTDATE;
        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch
end