USP_DATAFORMTEMPLATE_ADD_EVENTHIERARCHYADDEXISTINGEVENT

The save procedure used by the add dataform template "Event Hierarchy Add Existing Event Add Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@PARENTEVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@EVENTID	uniqueidentifier	IN	Event
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_EVENTHIERARCHYADDEXISTINGEVENT
(
    @ID uniqueidentifier = null output,
    @PARENTEVENTID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @EVENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
as

set nocount on;

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()


begin try
    if exists(select 1 from dbo.EVENTAUCTION where EVENTAUCTION.ID = @PARENTEVENTID)
        raiserror('ERR_EVENT_AUCTIONSCANNOTHAVESUBEVENTS', 13, 1);

    if exists(select 1 from dbo.EVENT where EVENT.ID = @PARENTEVENTID and APPEALID is not null)
        raiserror('ERR_EVENT_TEAMFUNRAISINGEVENTSCANNOTHAVESUBEVENTS', 13, 1);

    if not exists(select 1 from dbo.EVENTHIERARCHY where EVENTHIERARCHY.ID = @PARENTEVENTID)
        raiserror('ERR_EVENT_PARENTEVENTMUSTBEINHIERARCHY', 13, 1);

    if not exists(
            select 1 
            from 
                dbo.EVENT 
                left join dbo.EVENTSITE on EVENTSITE.EVENTID = EVENT.ID
            where 
                EVENT.ID = @EVENTID 
                and dbo.UFN_SITEALLOWEDFORUSERONFEATURE(@CURRENTAPPUSERID, SITEID, '8e0dc59c-f36e-4144-aa44-4043119a6380', 1) = 1
            )
        raiserror('ERR_EVENT_SITEACCESSREQUIRED', 13, 1);

    if exists(select 1 from dbo.EVENTHIERARCHY where EVENTHIERARCHY.ID = @EVENTID)
        raiserror('ERR_EVENT_SELECTEDEVENTMUSTNOTBEINAHIERARCHY', 13, 1);

    set @ID = @EVENTID;

    declare @MAINEVENTID uniqueidentifier;

    select
        @MAINEVENTID = isnull(EVENT.MAINEVENTID, @PARENTEVENTID)
    from 
        dbo.EVENT
    where 
        EVENT.ID = @PARENTEVENTID

    update 
        dbo.EVENT
    set 
        MAINEVENTID = @MAINEVENTID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where 
        EVENT.ID = @ID

    declare @HIERARCHYINSERTPOSITION hierarchyid = null;
    set @HIERARCHYINSERTPOSITION = dbo.UFN_EVENT_GETHIERARCHYPATHINSERTPOSITION(@PARENTEVENTID);    

    insert into dbo.EVENTHIERARCHY
        (ID, HIERARCHYPATH, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    values
        (@ID, @HIERARCHYINSERTPOSITION, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

    declare @PARENTEVENTMANAGEMENTINSTANCEID uniqueidentifier;
    select
        @PARENTEVENTMANAGEMENTINSTANCEID = EVENTMANAGEMENTLEVELINSTANCE.EVENTMANAGEMENTINSTANCEID
    from
        dbo.EVENT
    inner join
        dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
    inner join
        dbo.EVENTMANAGEMENTOPTIONS on EVENTMANAGEMENTOPTIONS.EVENTID = EVENT.ID
    inner join
        dbo.EVENTMANAGEMENTLEVELINSTANCE on EVENTMANAGEMENTLEVELINSTANCE.ID = EVENTMANAGEMENTOPTIONS.EVENTMANAGEMENTLEVELINSTANCEID
    where
        EVENTHIERARCHY.HIERARCHYPATH = @HIERARCHYINSERTPOSITION.GetAncestor(1);

    declare @EVENTMANAGEMENTOPTIONSCONFLICT bit = 0;
    select @EVENTMANAGEMENTOPTIONSCONFLICT = 1
    from dbo.EVENTMANAGEMENTOPTIONS
        inner join dbo.EVENTMANAGEMENTLEVELINSTANCE on EVENTMANAGEMENTLEVELINSTANCE.ID = EVENTMANAGEMENTOPTIONS.EVENTMANAGEMENTLEVELINSTANCEID
    where
        EVENTMANAGEMENTOPTIONS.EVENTID = @PARENTEVENTID
        and dbo.UFN_EVENT_CONFLICTSWITHMANAGEMENTLEVELINSTANCE(
            @ID,
            EVENTMANAGEMENTLEVELINSTANCE.EVENTMANAGEMENTINSTANCEID,
            1,1,1,1,1,1,1,1,1
        ) = 1

    insert into dbo.EVENTMANAGEMENTOPTIONS
    (
        ID,
        EVENTMANAGEMENTLEVELINSTANCEID,
        EVENTID,
        HASREGISTRANTSANDOPTIONS,
        HASSPEAKERS,
        HASEXPENSES,
        HASINVITATIONS,
        HASTASKSANDCOORDINATORS,
        HASLODGINGOPTIONS,
        HASJOBOCCURRENCES,
        HASAPPEALS,
        HASDOCUMENTATION,
        HASCAMPAIGNS,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        newid(),
        EVENTMANAGEMENTLEVELINSTANCE.ID,
        @ID,
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASREGISTRANTSANDOPTIONS end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASSPEAKERS end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASEXPENSES end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASINVITATIONS end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASTASKSANDCOORDINATORS end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASLODGINGOPTIONS end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASJOBOCCURRENCES end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASAPPEALS end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASDOCUMENTATION end,
            1),
        isnull(
            case when @EVENTMANAGEMENTOPTIONSCONFLICT = 0 then EVENTMANAGEMENTLEVELINSTANCE.HASCAMPAIGNS end,
            1),                                        
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from
        dbo.EVENTHIERARCHY
    left outer join 
        dbo.EVENTMANAGEMENTLEVELINSTANCE on EVENTMANAGEMENTLEVELINSTANCE.EVENTMANAGEMENTINSTANCEID = @PARENTEVENTMANAGEMENTINSTANCEID and
            EVENTMANAGEMENTLEVELINSTANCE.LEVEL = EVENTHIERARCHY.HIERARCHYPATH.GetLevel()
    where
        EVENTHIERARCHY.ID = @ID;
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0