USP_DATALIST_MULTILEVELEVENTNAVIGATIONTREE

This datalist is the page navigation tree used by the event page for multi-level events.

Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



                CREATE procedure dbo.USP_DATALIST_MULTILEVELEVENTNAVIGATIONTREE
                (
                    @EVENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    declare @MAINEVENTID uniqueidentifier;
                    select @MAINEVENTID = coalesce(MAINEVENTID,ID) from dbo.EVENT where EVENT.ID = @EVENTID;

                    with [CTE] as
                    (
                        select
                            EVENT.ID,
                            EVENT.NAME,
                            case when (select count(ID) from dbo.EVENT as [SUBEVENT] where [SUBEVENT].MAINEVENTID = EVENT.ID) > 0 then cast(1 as bit) else cast(0 as bit) end as [ISMAINEVENT],
                            EVENTHIERARCHY.HIERARCHYPATH,
                            (select [PARENTEVENTHIERARCHY].ID from dbo.EVENTHIERARCHY [PARENTEVENTHIERARCHY] where EVENTHIERARCHY.HIERARCHYPATH.GetAncestor(1) = [PARENTEVENTHIERARCHY].HIERARCHYPATH) as [PARENTID],
                            case when exists (select ID from dbo.EVENTAUCTION where EVENTAUCTION.ID = EVENT.ID) then cast(1 as bit) else cast(0 as bit) end as [ISAUCTION],
                            case when EVENT.APPEALID is not null then 1 else 0 end as [ISTEAMFUNDRAISINGEVENT]
                        from 
                            dbo.EVENT     
                        left outer join
                            dbo.EVENTHIERARCHY on EVENTHIERARCHY.ID = EVENT.ID
                        where
                            EVENT.MAINEVENTID = @MAINEVENTID or
                            EVENT.ID = @MAINEVENTID
                    )
                    select 
                        [CTE].ID,
                        [CTE].NAME,
                        [CTE].ISMAINEVENT,
                        [CTE].PARENTID,
                        [CTE].ISAUCTION,
                        dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, [CTE].ID) as [USERHASSITEACCESS],
                        [CTE].ISTEAMFUNDRAISINGEVENT
                    from [CTE]
                    order by 
                        [CTE].ISMAINEVENT desc, [CTE].NAME;