USP_DATAFORMTEMPLATE_ADD_EVENTHIERARCHY

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_EVENTHIERARCHY
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @EVENTMANAGEMENTPROPERTYGROUPTEMPLATEID uniqueidentifier = null,
    @EVENTHIERARCHY xml = null
)
as

set nocount on;

if @ID is null
    set @ID = newid()

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

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

begin try

    if not exists (
        select 
            SITEID 
        from 
            dbo.EVENTMANAGEMENTPROPERTYGROUPTEMPLATE
        left outer join
            dbo.EVENTMANAGEMENTPROPERTYGROUPTEMPLATESITE on EVENTMANAGEMENTPROPERTYGROUPTEMPLATESITE.EVENTMANAGEMENTPROPERTYGROUPTEMPLATEID = EVENTMANAGEMENTPROPERTYGROUPTEMPLATE.ID
        where 
            EVENTMANAGEMENTPROPERTYGROUPTEMPLATE.ID = @EVENTMANAGEMENTPROPERTYGROUPTEMPLATEID
        and
            dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, SITEID) = 1
    )
    begin
        raiserror('ERR_EVENTMANAGEMENTPROPERTYGROUPTEMPLATEID_NOSITEACCESS',13,1);
        return 1;
    end

    declare @HierarchyTable table
    (
        ID uniqueidentifier,
        PARENTEVENTID uniqueidentifier,
        NAME nvarchar(100),
        DESCRIPTION nvarchar(255),
        STARTDATE datetime,
        STARTTIME dbo.UDT_HOURMINUTE,
        ENDDATE datetime,
        ENDTIME dbo.UDT_HOURMINUTE,
        CAPACITY int,
        EVENTLOCATIONID uniqueidentifier,
        EVENTLOCATIONROOMID uniqueidentifier,
        EVENTLOCATIONCONTACTID uniqueidentifier,
        SITES xml,
        EVENTCATEGORYCODEID uniqueidentifier,
        APPEALID uniqueidentifier,
        DISPLAYORDER int,
        ISAUCTION bit
    );

    insert into 
        @HierarchyTable
    select
        T.c.value('(ID)[1]','uniqueidentifier'),
        T.c.value('(PARENTEVENTID)[1]','uniqueidentifier'),
        T.c.value('(NAME)[1]','nvarchar(100)'),
        T.c.value('(DESCRIPTION)[1]','nvarchar(255)'),
        T.c.value('(STARTDATE)[1]','datetime'),
        T.c.value('(STARTTIME)[1]','dbo.UDT_HOURMINUTE'),
        T.c.value('(ENDDATE)[1]','datetime'),
        T.c.value('(ENDTIME)[1]','dbo.UDT_HOURMINUTE'),
        T.c.value('(CAPACITY)[1]','int'),
        T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier'),
        T.c.value('(EVENTLOCATIONROOMID)[1]','uniqueidentifier'),
        T.c.value('(EVENTLOCATIONCONTACTID)[1]','uniqueidentifier'),
        case when T.c.exist('./SITES/ITEM') = 1 then T.c.query('(SITES)[1]') else null end as SITES,
        T.c.value('(EVENTCATEGORYCODEID)[1]','uniqueidentifier'),
        T.c.value('(APPEALID)[1]','uniqueidentifier'),
        T.c.value('(DISPLAYORDER)[1]','int'),
        T.c.value('(ISAUCTION)[1]','bit')
    from
        @EVENTHIERARCHY.nodes('/EVENTHIERARCHY/ITEM') T(c);

    -- validate that there's at least one event in the structure

    if ((select count(ID) from @HierarchyTable) = 0)
        raiserror('ERR_EVENTHIERARCHY_NOEVENTS',13,1)

    -- validate that there's only one root in the structure

    if ((select count(ID) from @HierarchyTable where PARENTEVENTID is null) > 1)
        raiserror('ERR_EVENTHIERARCHY_MULTIPLEROOT',13,1);    

    -- validate that auction events do not have children

    if exists (
        select
            [CHILDEVENT].ID
        from
            @HierarchyTable [AUCTIONEVENT]
        inner join
            @HierarchyTable [CHILDEVENT] on [CHILDEVENT].PARENTEVENTID = [AUCTIONEVENT].ID
        where
            [AUCTIONEVENT].ISAUCTION = 1
    )
        raiserror('ERR_EVENTHIERARCHY_AUCTIONEVENT_CANNOTHAVECHILDREN',13,1);


    select
        @ID = ID
    from
        @HierarchyTable
    where
        PARENTEVENTID is null;

    declare @MAINEVENTID uniqueidentifier;
    declare @EVENTID uniqueidentifier;
    declare @PARENTEVENTID uniqueidentifier;
    declare @NAME nvarchar(100);
    declare @DESCRIPTION nvarchar(255);
    declare @STARTDATE datetime;
    declare @STARTTIME dbo.UDT_HOURMINUTE;
    declare @ENDDATE datetime;
    declare @ENDTIME dbo.UDT_HOURMINUTE;
    declare @CAPACITY int;
    declare @EVENTLOCATIONID uniqueidentifier;
    declare @EVENTLOCATIONROOMID uniqueidentifier;
    declare @EVENTLOCATIONCONTACTID uniqueidentifier;
    declare @SITES xml;
    declare @EVENTCATEGORYCODEID uniqueidentifier;
    declare @APPEALID uniqueidentifier;
    declare @DISPLAYORDER int;
    declare @ISAUCTION bit;

    declare EVENTCURSOR cursor local fast_forward for
        select
            ID,
            PARENTEVENTID,
            NAME,
            DESCRIPTION,
            STARTDATE,
            STARTTIME,
            ENDDATE,
            ENDTIME,
            CAPACITY,
            EVENTLOCATIONID,
            EVENTLOCATIONROOMID,
            EVENTLOCATIONCONTACTID,
            SITES,
            EVENTCATEGORYCODEID,
            APPEALID,
            DISPLAYORDER,
            ISAUCTION
        from
            @HierarchyTable
        -- ensure we create the main event first so the MAINEVENTID foreign key doesn't fail

        order by
            (case when PARENTEVENTID is null then 0 else 1 end) asc; 

    open EVENTCURSOR;
    fetch next from EVENTCURSOR into @EVENTID,
        @PARENTEVENTID,
        @NAME,
        @DESCRIPTION,
        @STARTDATE,
        @STARTTIME,
        @ENDDATE,
        @ENDTIME,
        @CAPACITY,
        @EVENTLOCATIONID,
        @EVENTLOCATIONROOMID,
        @EVENTLOCATIONCONTACTID,
        @SITES,
        @EVENTCATEGORYCODEID,
        @APPEALID,
        @DISPLAYORDER,
        @ISAUCTION;

    while @@FETCH_STATUS = 0
    begin

        if @EVENTID = @ID
            set @MAINEVENTID = null;
        else
            set @MAINEVENTID = @ID;

        exec dbo.USP_EVENT_ADD
                @ID = @EVENTID output,
                @CURRENTAPPUSERID = @CURRENTAPPUSERID,
                @CHANGEAGENTID = @CHANGEAGENTID,
                @NAME = @NAME,
                @DESCRIPTION = @DESCRIPTION,
                @STARTDATE = @STARTDATE,
                @STARTTIME = @STARTTIME,
                @ENDDATE = @ENDDATE,
                @ENDTIME = @ENDTIME,
                @CAPACITY = @CAPACITY,
                @EVENTLOCATIONID = @EVENTLOCATIONID,
                @EVENTLOCATIONCONTACTID = @EVENTLOCATIONCONTACTID,
                @MAINEVENTID = @MAINEVENTID,
                @COPYFROMEVENTID = null,
                @COPYTASKS = 0,
                @COPYEXPENSES = 0,
                @COPYPRICES = 0,
                @COPYINVITATIONS = 0,
                @COPYINVITEES = 0,
                @COPYATTRIBUTES = 0,
                @ATTRIBUTEDEFINED = 0,
                @SITES = @SITES,
                @COPYPREFERENCES = 0,
                @EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID,
                @COPYTEAMSTRUCTURE = 0,
                @COPYTEAMFUNDRAISERS = 0,
                @APPEALID = @APPEALID,
                @ADDQUICKCOMPARE = 0,
                @COPYLODGINGOPTIONS = 0,
                @EVENTLOCATIONROOMID = @EVENTLOCATIONROOMID,
                @COPYJOBOCCURRENCES = 0,
                @ISAUCTION = @ISAUCTION;

        fetch next from EVENTCURSOR into @EVENTID,
            @PARENTEVENTID,
            @NAME,
            @DESCRIPTION,
            @STARTDATE,
            @STARTTIME,
            @ENDDATE,
            @ENDTIME,
            @CAPACITY,
            @EVENTLOCATIONID,
            @EVENTLOCATIONROOMID,
            @EVENTLOCATIONCONTACTID,
            @SITES,
            @EVENTCATEGORYCODEID,
            @APPEALID,
            @DISPLAYORDER,
            @ISAUCTION;
    end        
    deallocate EVENTCURSOR;

    -- Use a recursive CTE to build up paths for the events (i.e. '/2/1/3/')

    with EVENT_ROWNUMBER_CTE as (
        select
            ID,
            PARENTEVENTID,
            row_number() over (partition by PARENTEVENTID order by DISPLAYORDER) as ROWNUM,
            DISPLAYORDER
        from
            @HierarchyTable
    ),
    EVENT_PATHS_CTE as (
        select
            ID,
            PARENTEVENTID,
            cast(dbo.UFN_EVENT_GETHIERARCHYPATHINSERTPOSITION(null) as varchar(900)) as HIERARCHYPATH,
            DISPLAYORDER
        from
            @HierarchyTable
        where
            PARENTEVENTID is null

        union all

        select
            EVENT_ROWNUMBER_CTE.ID,
            EVENT_ROWNUMBER_CTE.PARENTEVENTID,
            cast(HIERARCHYPATH + cast(EVENT_ROWNUMBER_CTE.ROWNUM as varchar(20)) + '/' as varchar(900)),
            EVENT_ROWNUMBER_CTE.DISPLAYORDER
        from
            EVENT_PATHS_CTE
        inner join
            EVENT_ROWNUMBER_CTE on EVENT_ROWNUMBER_CTE.PARENTEVENTID = EVENT_PATHS_CTE.ID
    )
    insert into dbo.EVENTHIERARCHY (
        ID,
        HIERARCHYPATH,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        EVENT_PATHS_CTE.ID,
        EVENT_PATHS_CTE.HIERARCHYPATH,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from
        EVENT_PATHS_CTE;

    declare @EVENTMANAGEMENTPROPERTYGROUPINSTANCEID uniqueidentifier;    
    exec dbo.USP_EVENTMANAGEMENTPROPERTYGROUPTEMPLATE_CREATEINSTANCE @EVENTMANAGEMENTPROPERTYGROUPTEMPLATEID, @EVENTMANAGEMENTPROPERTYGROUPINSTANCEID output, @CHANGEAGENTID;

    with EVENTMANAGEMENTPROPERTYGROUP_CTE as (
        select
            ID,
            LEVEL,
            HASREGISTRANTSANDOPTIONS,
            HASSPEAKERS,
            HASEXPENSES,
            HASINVITATIONS,
            HASTASKS,
            HASLODGINGOPTIONS,
            HASJOBOCCURRENCES,
            HASAPPEALS,
            HASDOCUMENTATION
        from
            dbo.EVENTMANAGEMENTPROPERTYLEVELINSTANCE
        where
            EVENTMANAGEMENTPROPERTYLEVELINSTANCE.EVENTMANAGEMENTPROPERTYGROUPINSTANCEID = @EVENTMANAGEMENTPROPERTYGROUPINSTANCEID
    )
    insert into dbo.EVENTMANAGEMENTPROPERTYGROUP
    (
        ID,
        EVENTMANAGEMENTPROPERTYLEVELINSTANCEID,
        EVENTID,
        HASREGISTRANTSANDOPTIONS,
        HASSPEAKERS,
        HASEXPENSES,
        HASINVITATIONS,
        HASTASKS,
        HASLODGINGOPTIONS,
        HASJOBOCCURRENCES,
        HASAPPEALS,
        HASDOCUMENTATION,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        newid(),
        EVENTMANAGEMENTPROPERTYGROUP_CTE.ID,
        EVENT.ID,
        isnull(HASREGISTRANTSANDOPTIONS,1),
        isnull(HASSPEAKERS,1),
        isnull(HASEXPENSES,1),
        isnull(HASINVITATIONS,1),
        isnull(HASTASKS,1),
        isnull(HASLODGINGOPTIONS,1),
        isnull(HASJOBOCCURRENCES,1),
        isnull(HASAPPEALS,1),
        isnull(HASDOCUMENTATION,1),
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from
        dbo.EVENT
    inner join
        dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
    left outer join 
        EVENTMANAGEMENTPROPERTYGROUP_CTE on EVENTMANAGEMENTPROPERTYGROUP_CTE.LEVEL = EVENTHIERARCHY.HIERARCHYPATH.GetLevel()
    where
        ((EVENT.MAINEVENTID = @ID) or (EVENT.ID = @ID));

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


return 0