UFN_ADHOCQUERY_GETHIERARCHY

CREATE function dbo.UFN_ADHOCQUERY_GETHIERARCHY 
(
  @CURRENTAPPUSERID uniqueidentifier = null
)
returns @HIERARCHY table 
(
  ID uniqueidentifier, 
  PARENTFOLDERID uniqueidentifier, 
  NAME nvarchar(255),
  DISPLAYORDER int, 
  ITEMTYPE int, 
  APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS bit,
  SECURITYLEVEL nvarchar(14),
  QUERYDEFAULTALLOWALLUSERSTORUN bit,
  QUERYDEFAULTALLOWALLUSERSTOEDIT bit,
  ISFAVORITE bit,
  SMARTQUERYDEFAULTALLOWALLUSERSTORUN bit,
  SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT bit,
  DESCRIPTION nvarchar(1024), 
  TYPE nvarchar(50), 
  DATEADDED datetime
)
as
begin 

  declare @HASQUERYVIEWSYSTEMPRIVILEGE bit = 0;
  declare @ISSYSADMIN bit = (select top 1 ISSYSADMIN from APPUSER where ID = @CURRENTAPPUSERID);

  if @ISSYSADMIN = 0
    select @HASQUERYVIEWSYSTEMPRIVILEGE = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f')

  if (@ISSYSADMIN = 0 and @HASQUERYVIEWSYSTEMPRIVILEGE = 0)
  begin
    declare @ADHOCCATALOGITEMS table (OBJECTNAME nvarchar(128));

    insert into @ADHOCCATALOGITEMS (OBJECTNAME)
    select QUERYVIEWCATALOG.OBJECTNAME 
    from dbo.QUERYVIEWCATALOG
    inner join dbo.UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER(@CURRENTAPPUSERID) as [ADHOCCATALOGITEMS] 
      on [ADHOCCATALOGITEMS].QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID;

    declare @SMARTCATALOGITEMS table (SMARTQUERYCATALOGID uniqueidentifier);

    insert into @SMARTCATALOGITEMS (SMARTQUERYCATALOGID)
    select SMARTQUERYCATALOG.ID 
    from dbo.SMARTQUERYCATALOG
    inner join dbo.UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER(@CURRENTAPPUSERID) as [SMARTCATALOGITEMS] 
      on [SMARTCATALOGITEMS].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID;
  end;

  declare @PERMISSIONEDFOLDERS table (FOLDERID uniqueidentifier);
  insert into @PERMISSIONEDFOLDERS (FOLDERID)
  select ID
  from dbo.UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS(@CURRENTAPPUSERID);

  insert into @HIERARCHY (ID, PARENTFOLDERID, NAME, DISPLAYORDER, ITEMTYPE, APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS, SECURITYLEVEL, QUERYDEFAULTALLOWALLUSERSTORUN, QUERYDEFAULTALLOWALLUSERSTOEDIT, ISFAVORITE, SMARTQUERYDEFAULTALLOWALLUSERSTORUN, SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT,  DESCRIPTION,  TYPE, DATEADDED)
  select
    ID,
    PARENTFOLDERID,
    NAME,
    case 
      -- Since DISPLAYORDER can't hold nulls, 0 or less is acting as null.  If the DISPLAYORDER

      -- isn't set, then the items will be sorted by their name.

      when DISPLAYORDER > 0 then DISPLAYORDER
      else (row_number() over (order by NAME)) + coalesce((    select max(DISPLAYORDER)
      from
      (
        select DISPLAYORDER from dbo.ADHOCQUERYFOLDER
        union all
        select DISPLAYORDER from dbo.ADHOCQUERY
        union all
        select DISPLAYORDER from dbo.SMARTQUERYINSTANCE
      ) as HIERARCHYITEMS)
    , 0)
    end as DISPLAYORDER,
    ITEMTYPE,
    APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS,
    SECURITYLEVEL,
    QUERYDEFAULTALLOWALLUSERSTORUN,
    QUERYDEFAULTALLOWALLUSERSTOEDIT,
    ISFAVORITE,
    SMARTQUERYDEFAULTALLOWALLUSERSTORUN,
    SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT,
    DESCRIPTION,
    TYPE,
    DATEADDED
  from 
  (
    select
      ADHOCQUERYFOLDER.ID,
      ADHOCQUERYFOLDER.PARENTFOLDERID,
      ADHOCQUERYFOLDER.NAME,
      null DESCRIPTION,
      ADHOCQUERYFOLDER.DISPLAYORDER,
      1 as ITEMTYPE, -- Indicates folder,

      null TYPE,
      null DATEADDED,
      convert(bit, 0) as ISFAVORITE,
      APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS,
      SECURITYLEVELCODE as SECURITYLEVEL,
      QUERYDEFAULTALLOWALLUSERSTORUN,
      QUERYDEFAULTALLOWALLUSERSTOEDIT,
      SMARTQUERYDEFAULTALLOWALLUSERSTORUN,
      SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT
    from dbo.ADHOCQUERYFOLDER
    inner join @PERMISSIONEDFOLDERS PERMISSIONED_FOLDERS
      on ADHOCQUERYFOLDER.ID = PERMISSIONED_FOLDERS.FOLDERID

    union all

    select 
      QUERY_ITEM.ID,
      QUERY_ITEM.FOLDERID,
      QUERY_ITEM.NAME,
      QUERY_ITEM.DESCRIPTION,
      QUERY_ITEM.DISPLAYORDER,
      QUERY_ITEM.ITEMTYPE, 
      RECORDTYPE.NAME TYPE,
      QUERY_ITEM.DATEADDED,
      QUERY_ITEM.ISFAVORITE,
      0 as APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS,
      null as SECURITYLEVEL,
      null as QUERYDEFAULTALLOWALLUSERSTORUN,
      null as QUERYDEFAULTALLOWALLUSERSTOEDIT,
      null as SMARTQUERYDEFAULTALLOWALLUSERSTORUN,
      null as SMARTQUERYDEFAULTALLOWALLUSERSTOEDIT
    from  
    (select
      QUERY.ID,
      QUERY.FOLDERID,
      QUERY.NAME,
      QUERY.DESCRIPTION,
      QUERY.DISPLAYORDER,
      0 as ITEMTYPE, -- Indicates ad-hoc query

      QUERYVIEWCATALOG.RECORDTYPEID,
      QUERY.DATEADDED,
      case when exists(select ID from dbo.APPUSERADHOCQUERYFAVORITE where ADHOCQUERYID = QUERY.ID and APPUSERID = @CURRENTAPPUSERID)
          then convert(bit, 1) else convert(bit, 0)
      end as ISFAVORITE
    from dbo.ADHOCQUERY QUERY
    inner join dbo.QUERYVIEWCATALOG on QUERY.QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
    where
      (@ISSYSADMIN = 1 or @HASQUERYVIEWSYSTEMPRIVILEGE = 1)
      or 
      (not exists(
        select [QUERYVIEWSINUSE].OBJECTNAME 
        from dbo.UFN_ADHOCQUERY_QUERYVIEWSINUSE(QUERY.QUERYDEFINITIONXML) as [QUERYVIEWSINUSE]
        where not exists (
          select 1 
          from @ADHOCCATALOGITEMS as [ADHOCCATALOGITEMS] 
          where [ADHOCCATALOGITEMS].OBJECTNAME = [QUERYVIEWSINUSE].OBJECTNAME)
      )
      and
      dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, QUERY.ID) = 1) and
      -- Ensure the folder the query belongs to is visible

      (QUERY.FOLDERID is null or QUERY.FOLDERID in (select FOLDERID from @PERMISSIONEDFOLDERS))

    union all

    select
      SMARTQUERY.ID,
      SMARTQUERY.FOLDERID,
      SMARTQUERY.NAME,
      SMARTQUERY.DESCRIPTION,
      SMARTQUERY.DISPLAYORDER,
      2 as ITEMTYPE, -- Indicates template query

      SMARTQUERYCATALOG.RECORDTYPEID,
      SMARTQUERY.DATEADDED,
      case when exists(select ID from dbo.APPUSERSMARTQUERYINSTANCEFAVORITE where SMARTQUERYINSTANCEID = SMARTQUERY.ID and APPUSERID = @CURRENTAPPUSERID)
          then convert(bit, 1) else convert(bit, 0)
      end as ISFAVORITE
    from dbo.SMARTQUERYINSTANCE SMARTQUERY
    inner join dbo.SMARTQUERYCATALOG on SMARTQUERY.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
    where
      (@ISSYSADMIN = 1 or @HASQUERYVIEWSYSTEMPRIVILEGE = 1)
      or 
      (exists (
        select 1
        from @SMARTCATALOGITEMS as [SMARTCATALOGITEMS] 
        where [SMARTCATALOGITEMS].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID)
      and
      dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, SMARTQUERY.ID) = 1) and
      -- Ensure the folder the query belongs to is visible

      (SMARTQUERY.FOLDERID is null or SMARTQUERY.FOLDERID in (select FOLDERID from @PERMISSIONEDFOLDERS)) ) as QUERY_ITEM 
    left join dbo.RECORDTYPE on QUERY_ITEM.RECORDTYPEID = RECORDTYPE.ID
  ) as HIERARCHY

  return
end