UFN_QUERY_GETFOLDERS

create function dbo.[UFN_QUERY_GETFOLDERS]
(
  @CURRENTAPPUSERID uniqueidentifier = null
)
returns @FOLDERS table 
(
  [FOLDERID] uniqueidentifier, 
  [PARENTFOLDERID] uniqueidentifier, 
  [FOLDERNAME] nvarchar(255),
  [DISPLAYORDER] int, 
  [COUNT] int, 
  [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS] bit,
  [SECURITYLEVELCODE] tinyint,
  [QUERYDEFAULTALLOWALLUSERSTORUN] bit,
  [QUERYDEFAULTALLOWALLUSERSTOEDIT] bit,
  [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] bit,
  [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] bit
)
as
begin 


    declare @ISSYSADMIN bit;
    declare @HASQUERYVIEWSYSTEMPRIVILEGE bit = 0;

    /* Query List Query is being used to drive site security */
    declare @SECURITYFEATUREID uniqueidentifier = '2043b264-918e-4c28-8b6c-250b62b0106b';
    declare @SECURITYFEATURETYPE tinyint = 10;

    select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;


    ---------------


  /* folders user has permission to see */
    declare @PERMISSIONEDFOLDERS table
    (
        [FOLDERID] uniqueidentifier,
        [PARENTFOLDERID] uniqueidentifier,
        [FOLDERNAME] nvarchar(max),
        [DISPLAYORDER] int
    );

    insert into @PERMISSIONEDFOLDERS ([FOLDERID], [PARENTFOLDERID], [FOLDERNAME], [DISPLAYORDER])
        select 
          [ID],
          isnull([PARENTFOLDERID], '00000000-0000-0000-0000-000000000000'),
          [NAME],
          [DISPLAYORDER]
        from dbo.[UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS](@CURRENTAPPUSERID)

    ----------------    


    /* queries user has permission to see */
      declare @QUERIESTOINCLUDE table
      (
          [QUERYID] uniqueidentifier,
          [FOLDERID] uniqueidentifier
          );        

        /* Filter out any ad-hoc queries involving query views to which the current user does not have access */
        with [ADHOCCATALOGITEMS] as
        (
            select [QUERYVIEWCATALOG].[OBJECTNAME]
            from dbo.[QUERYVIEWCATALOG]
            where    exists
            (
              select [ADHOCCATALOGITEMS].[QUERYVIEWCATALOGID]
              from dbo.[UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER](@CURRENTAPPUSERID) as [ADHOCCATALOGITEMS] 
              where [ADHOCCATALOGITEMS].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
            )
        )
      insert into @QUERIESTOINCLUDE ([QUERYID], [FOLDERID])
        select
          [QUERY].[ID],
          isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID]
        from dbo.[ADHOCQUERY] [QUERY]    
        inner join @PERMISSIONEDFOLDERS [FOLDER] on isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000000') = [FOLDER].[FOLDERID]
        inner join dbo.[QUERYVIEWCATALOG] on [QUERY].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
        left join dbo.[APPUSERADHOCQUERYFAVORITE] [FAVORITE] on [FAVORITE].[ADHOCQUERYID] = [QUERY].[ID] and [FAVORITE].[APPUSERID] = @CURRENTAPPUSERID
        where
        /* Filter out restricted query views */
        (
          (@ISSYSADMIN = 1 
          or
          (@HASQUERYVIEWSYSTEMPRIVILEGE = 1 and (dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [QUERY].[ID]) = 1)))
          /* This will return a list of query views in use by the ad-hoc query but not in the ADHOCCATALOGITEMS variable;
          These ad-hoc queries should not be returned to the client. */
          or
          (
            not exists(
              select [QUERYVIEWSINUSE].[OBJECTNAME]
              from dbo.[UFN_ADHOCQUERY_QUERYVIEWSINUSE]([QUERY].[QUERYDEFINITIONXML]) as [QUERYVIEWSINUSE]
              where not exists (
                select
                [ADHOCCATALOGITEMS].[OBJECTNAME]
                from [ADHOCCATALOGITEMS]
                where [ADHOCCATALOGITEMS].[OBJECTNAME] = [QUERYVIEWSINUSE].[OBJECTNAME]
              )
            )
          )
          and
          (
          dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [QUERY].[ID]) = 1
          )
        )
        and
        (    
          [QUERYVIEWCATALOG].[ROOTOBJECT] = 1
          and
          (
            [QUERY].[SITEID] is null
            or
            (
              dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1 
              or exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where [SITEID] = [QUERY].[SITEID] or ([SITEID] is null and [QUERY].[SITEID] is null))
            )
          )
        );

      /* Filter out any smart queries involving query views to which the current user does not have access */
        with [SMARTCATALOGITEMS] as
        (
          select [SMARTQUERYCATALOG].[ID] [SMARTQUERYCATALOGID]
          from dbo.[SMARTQUERYCATALOG]
          where exists
            (
              select [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID]
              from dbo.[UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER](@CURRENTAPPUSERID) as [SMARTCATALOGITEMS] 
              where [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID]
            )
    )
      insert into @QUERIESTOINCLUDE ([QUERYID], [FOLDERID])
        select
          [QUERY].[ID],
          isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID]
        from dbo.[SMARTQUERYINSTANCE] [QUERY]    
        inner join @PERMISSIONEDFOLDERS [FOLDER] on isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000000') = [FOLDER].[FOLDERID]
        inner join dbo.[SMARTQUERYCATALOG] on [QUERY].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID]
        left join dbo.[APPUSERSMARTQUERYINSTANCEFAVORITE] [FAVORITE] on [FAVORITE].[SMARTQUERYINSTANCEID] = [QUERY].[ID] and [FAVORITE].[APPUSERID] = @CURRENTAPPUSERID
        where
        /* Filter out restricted smart queries */
        (
          @ISSYSADMIN = 1
        /*This will return a list of smart queries in use by the smart query but not in the SMARTCATALOGITEMS variable;
        These smart queries should not be returned to the client.*/
          or
          exists (
            select
            [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID]
            from [SMARTCATALOGITEMS] as [SMARTCATALOGITEMS]
            where [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID]
          )
          and
          (
            dbo.[UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, QUERY.ID) = 1
          )
        )
        and
        (
          [QUERY].[SITEID] is null
          or
          (
            dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1
            or exists
            (
              select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
              where [SITEID]=[QUERY].[SITEID] or ([SITEID] is null and [QUERY].[SITEID] is null))
          )
        );


    ---------

    /* nodes to show the user */    
      declare @NODES table
      (
        [NODEID] uniqueidentifier,
        [PARENTNODEID] uniqueidentifier,
        [NODENAME] nvarchar(max),
        [DISPLAYORDER] int,
        [COUNT] int default 0,
    [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS] bit,
    [SECURITYLEVELCODE] tinyint,
    [QUERYDEFAULTALLOWALLUSERSTORUN] bit,
    [QUERYDEFAULTALLOWALLUSERSTOEDIT] bit,
    [SMARTQUERYDEFAULTALLOWALLUSERSTORUN] bit,
    [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT] bit
      ); 

      --Rollup by folders

      begin   
          insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT], [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS], [SECURITYLEVELCODE], [QUERYDEFAULTALLOWALLUSERSTORUN], [QUERYDEFAULTALLOWALLUSERSTOEDIT], [SMARTQUERYDEFAULTALLOWALLUSERSTORUN], [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT])
            select  
              f.[FOLDERID],
              f.[PARENTFOLDERID],
              f.[FOLDERNAME],
              f.[DISPLAYORDER],
              count(distinct q.[QUERYID]) as [COUNT],
        af.[APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS],
        af.[SECURITYLEVELCODE],
        af.[QUERYDEFAULTALLOWALLUSERSTORUN],
        af.[QUERYDEFAULTALLOWALLUSERSTOEDIT],
        af.[SMARTQUERYDEFAULTALLOWALLUSERSTORUN],
        af.[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT]
            from @PERMISSIONEDFOLDERS f
            left join @QUERIESTOINCLUDE q on q.[FOLDERID] = f.[FOLDERID]
      left join dbo.[ADHOCQUERYFOLDER] af on f.[FOLDERID] = af.[ID]
            group by 
              f.[FOLDERID],
              f.[PARENTFOLDERID],
              f.[FOLDERNAME],
              f.[DISPLAYORDER],
        af.[APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS],
        af.[SECURITYLEVELCODE],
        af.[QUERYDEFAULTALLOWALLUSERSTORUN],
        af.[QUERYDEFAULTALLOWALLUSERSTOEDIT],
        af.[SMARTQUERYDEFAULTALLOWALLUSERSTORUN],
        af.[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT];
      end;

    ---------

    /* rollup node counts */
    with [ROLLUP] as
    (  
      select 
        f.[NODEID] [PARENTNODEID],
        f.[NODEID],
        f.[COUNT]
      from @NODES f

      union all 

      select 
        r.[PARENTNODEID],
        f.[NODEID],
        f.[COUNT]
      from @NODES f
      inner join [ROLLUP] r on f.[PARENTNODEID] = r.[NODEID]
    )  

  insert into @FOLDERS ([FOLDERID], [PARENTFOLDERID], [FOLDERNAME], [DISPLAYORDER], [COUNT], [APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS], [SECURITYLEVELCODE], [QUERYDEFAULTALLOWALLUSERSTORUN], [QUERYDEFAULTALLOWALLUSERSTOEDIT], [SMARTQUERYDEFAULTALLOWALLUSERSTORUN], [SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT])
    select
      f.[NODEID],
      f.[PARENTNODEID],
      f.[NODENAME],
      f.[DISPLAYORDER],
      sum(r.[COUNT]) [COUNT],
    f.[APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS],
    f.[SECURITYLEVELCODE],
    f.[QUERYDEFAULTALLOWALLUSERSTORUN],
    f.[QUERYDEFAULTALLOWALLUSERSTOEDIT],
    f.[SMARTQUERYDEFAULTALLOWALLUSERSTORUN],
    f.[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT]
    from [ROLLUP] r
    inner join @NODES f on r.[PARENTNODEID] = f.[NODEID]
    group by 
      f.[NODEID],
      f.[PARENTNODEID],
      f.[NODENAME],
      f.[DISPLAYORDER],
    f.[APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS],
    f.[SECURITYLEVELCODE],
    f.[QUERYDEFAULTALLOWALLUSERSTORUN],
    f.[QUERYDEFAULTALLOWALLUSERSTOEDIT],
    f.[SMARTQUERYDEFAULTALLOWALLUSERSTORUN],
    f.[SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT];


  return;

end