USP_SHELL_GETNAVIGATION

CREATE procedure dbo.USP_SHELL_GETNAVIGATION

@APPUSERID uniqueidentifier,
@APPUSER_IS_KNOWN_SYSADMIN bit = null

as
set nocount on;

declare @IsSysAdmin bit;

if @APPUSER_IS_KNOWN_SYSADMIN is null
    select @IsSysAdmin = ISSYSADMIN 
    from dbo.APPUSER 
    where ID = @APPUSERID;    
else
    set @IsSysAdmin=@APPUSER_IS_KNOWN_SYSADMIN;

if @IsSysAdmin = 1        
    select
        FA.FUNCTIONALAREASPECXML, TASKCATALOG.TASKSPECXML,
        case when 
            (select count(*) 
             from dbo.SYSTEMROLETASK as SRT 

             where (SRT.TASKID = TASKCATALOG.ID) and 
                   (SRT.DISPLAYONHOMEPAGE = 1)) = 0 
        then convert(bit, 0)
        else convert(bit, 1)
        end as 'DISPLAYONHOMEPAGE', FA.ID AS FUNCTIONALAREAID

        from 
            dbo.FUNCTIONALAREACATALOG FA inner join dbo.TASKCATALOG on FA.ID = TASKCATALOG.FUNCTIONALAREAID            

        where  
            dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(FA.INSTALLEDPRODUCTLISTXML)=1

        order by 
            FA.SEQUENCE, FA.[NAME], TASKCATALOG.SEQUENCE, TASKCATALOG.[NAME];


else

        select 
            FA.FUNCTIONALAREASPECXML, TASKCATALOG.TASKSPECXML,
            case when 
                (select count(*) 
                 from 
                        dbo.SYSTEMROLETASK 
                 inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.SYSTEMROLEID = SYSTEMROLETASK.SYSTEMROLEID
                 where (SYSTEMROLETASK.TASKID = TASKCATALOG.ID) and 
                       (dbo.SYSTEMROLEAPPUSER.APPUSERID = @APPUSERID) and 
                       (dbo.SYSTEMROLETASK.DISPLAYONHOMEPAGE = 1)) = 0
            then convert(bit, 0)
            else convert(bit, 1)
            end as 'DISPLAYONHOMEPAGE' , FA.ID AS FUNCTIONALAREAID
            from 
                dbo.TASKCATALOG inner join dbo.FUNCTIONALAREACATALOG as FA on TASKCATALOG.FUNCTIONALAREAID = FA.ID
            where 

                (                 
                dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(FA.INSTALLEDPRODUCTLISTXML)=1                                                
                ) 
              AND TASKCATALOG.ID in (                            
                      select GRANTEDTASKS.TASKID
                    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_TASK GRANTEDTASKS
                    where GRANTEDTASKS.APPUSERID = @APPUSERID
                )

            order by 
                FA.SEQUENCE, FA.[NAME], TASKCATALOG.SEQUENCE, TASKCATALOG.[NAME];