USP_EVENTHIERARCHY_LOADPAGENAVIGATIONDATA

CREATE procedure dbo.USP_EVENTHIERARCHY_LOADPAGENAVIGATIONDATA
(
    @EVENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier
)
with execute as caller
as
    set nocount on;

    declare @MAINEVENTID uniqueidentifier;
    declare @ROOTPATH hierarchyid;

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

    --For very old events that only use the EVENT.MAINEVENTID column, there may be no

    --EVENTHIERARCHY data, but this page navigation tree is only displayed if the event

    --HASEVENTMANAGEMENTOPTIONS, so we can ignore those older events. This is consistent

    --with the behavior of the ClickOnce docked section that existed before we added

    --the page navigation tree in web shell.


    --Use a recursive CTE to build a new hierarchy path for each node so that we can

    --have a date-based sort rather than a sort based on the order in which nodes

    --were added.

    with EVENTWITHSORTEDHIERARCHYCTE
    as
    (
        --Anchor the CTE with just the main event

        select
            EVENT.ID as EVENTID,
            EVENT.NAME as EVENTNAME,
            EVENTHIERARCHY.HIERARCHYPATH as HIERARCHYPATH,
            dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENTHIERARCHY.ID) as [USERHASSITEACCESS],
            hierarchyid::Parse('/') as SORTEDPATH
        from
            dbo.EVENTHIERARCHY
            inner join dbo.EVENT on EVENT.ID = EVENTHIERARCHY.ID
        where
            EVENTHIERARCHY.ID = @MAINEVENTID

        union all

        select
            EVENT.ID as EVENTID,
            EVENT.NAME as EVENTNAME,
            EVENTHIERARCHY.HIERARCHYPATH as HIERARCHYPATH,
            dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, EVENTHIERARCHY.ID) as [USERHASSITEACCESS],
            hierarchyid::Parse
            (
                EVENTWITHSORTEDHIERARCHYCTE.SORTEDPATH.ToString()
                + cast
                    (
                        row_number() over
                            (
                                order by
                                    EVENT.STARTDATE,
                                    EVENT.STARTTIME,
                                    EVENT.ENDDATE,
                                    EVENT.ENDTIME,
                                    EVENT.NAME
                            )
                        as nvarchar(10)
                    )
                + '/'
            ) as SORTEDPATH
        from
            EVENTWITHSORTEDHIERARCHYCTE
            --Join to direct children from the previous level

            inner join dbo.EVENTHIERARCHY on EVENTHIERARCHY.HIERARCHYPATH.GetAncestor(1) = EVENTWITHSORTEDHIERARCHYCTE.HIERARCHYPATH
            inner join dbo.EVENT on EVENT.ID = EVENTHIERARCHY.ID
    )
    select
        EVENTWITHSORTEDHIERARCHYCTE.EVENTID,
        EVENTWITHSORTEDHIERARCHYCTE.EVENTNAME,
        EVENTWITHSORTEDHIERARCHYCTE.SORTEDPATH.GetAncestor(1).ToString() as PARENTPATH,
        EVENTWITHSORTEDHIERARCHYCTE.SORTEDPATH.ToString() as SORTEDPATH,
        EVENTWITHSORTEDHIERARCHYCTE.USERHASSITEACCESS
    from
        EVENTWITHSORTEDHIERARCHYCTE
    order by
        EVENTWITHSORTEDHIERARCHYCTE.SORTEDPATH;