USP_DATALIST_INFORMATIONLIBRARY_QUERYFOLDERS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @GROUPBY | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_INFORMATIONLIBRARY_QUERYFOLDERS
(
@GROUPBY tinyint = 0,
@CURRENTAPPUSERID uniqueidentifier
)
as
set nocount on;
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;
select @HASQUERYVIEWSYSTEMPRIVILEGE = dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f');
---------------
/* folders user has permission to see */
if OBJECT_ID('tempdb..#PERMISSIONEDFOLDERS') is not null
drop table #PERMISSIONEDFOLDERS;
create table #PERMISSIONEDFOLDERS
(
[FOLDERID] uniqueidentifier primary key,
[PARENTFOLDERID] uniqueidentifier,
[FOLDERNAME] nvarchar(max) collate database_default,
[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)
union all
select '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000003', 'root', 1
union all
select '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000000', '<none>', 0;
----------------
/* queries user has permission to see */
if OBJECT_ID('tempdb..#QUERIESTOINCLUDE') is not null
drop table #QUERIESTOINCLUDE;
create table #QUERIESTOINCLUDE
(
[QUERYID] uniqueidentifier primary key,
[FOLDERID] uniqueidentifier,
[QUERYCATEGORYCODEID] uniqueidentifier,
[OWNERID] uniqueidentifier,
[RECORDTYPEID] uniqueidentifier,
[ISFAVORITE] bit
);
/* Filter out any ad-hoc queries involving query views to which the current user does not have access */
if(COALESCE(@ISSYSADMIN,0) = 0 and COALESCE(@HASQUERYVIEWSYSTEMPRIVILEGE,0) = 0 )
begin
insert into #QUERIESTOINCLUDE ([QUERYID], [FOLDERID], [QUERYCATEGORYCODEID], [OWNERID], [RECORDTYPEID], [ISFAVORITE])
select
[QUERY].[ID],
isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID],
isnull([QUERY].[QUERYCATEGORYCODEID], '00000000-0000-0000-0000-000000000001') [QUERYCATEGORYCODEID],
[QUERY].[OWNERID],
[QUERYVIEWCATALOG].[RECORDTYPEID],
case
when [FAVORITE].[ID] is not null then convert(bit, 1)
else convert(bit, 0)
end as [ISFAVORITE]
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 ([QUERY].[OWNERID] = @CURRENTAPPUSERID) and (
[QUERYVIEWCATALOG].[ROOTOBJECT] = 1
and
(
[QUERY].[SITEID] is null
or
(
exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where [SITEID] = [QUERY].[SITEID] or ([SITEID] is null and [QUERY].[SITEID] is null)))
)
)
Union
select
[QUERY].[ID],
isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID],
isnull([QUERY].[QUERYCATEGORYCODEID], '00000000-0000-0000-0000-000000000001') [QUERYCATEGORYCODEID],
[QUERY].[OWNERID],
[QUERYVIEWCATALOG].[RECORDTYPEID],
case
when [FAVORITE].[ID] is not null then convert(bit, 1)
else convert(bit, 0)
end as [ISFAVORITE]
from dbo.[ADHOCQUERY] [QUERY]
inner join dbo.[UFN_ADHOCQUERY_GETUSERQUERIES](@CURRENTAPPUSERID) [OTHERQUERIES] on [OTHERQUERIES].[ID] = [QUERY].[ID] and (@ISSYSADMIN = 0 and @HASQUERYVIEWSYSTEMPRIVILEGE = 0 )
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
([QUERYVIEWCATALOG].[ROOTOBJECT] = 1
and
(
[QUERY].[SITEID] is null
or
(exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where [SITEID] = [QUERY].[SITEID] or ([SITEID] is null and [QUERY].[SITEID] is null)))
))
option(recompile)
end
else if(COALESCE(@ISSYSADMIN,0) = 0 and COALESCE(@HASQUERYVIEWSYSTEMPRIVILEGE,0) = 1 )
begin
with xmlnamespaces ('Blackbaud.AppFx.WebService.API.1' as [ns]),
[USERQUERIES] as
(
select
[SECURITYVIEW].[ADHOCQUERYID] as [ID]
from
dbo.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_ADHOCQUERYINSTANCE] [SECURITYVIEW]
where
([SECURITYVIEW].[APPUSERID] = @CURRENTAPPUSERID)
group by [ADHOCQUERYID]
having min(cast([SECURITYVIEW].[GRANTORDENY] as tinyint)) = 1
union all
select
[ID]
from dbo.[ADHOCQUERY]
where [OWNERID] = @CURRENTAPPUSERID or [SECURITYLEVEL] = 0
)
insert into #QUERIESTOINCLUDE ([QUERYID], [FOLDERID], [QUERYCATEGORYCODEID], [OWNERID], [RECORDTYPEID], [ISFAVORITE])
select
[QUERY].[ID],
isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID],
isnull([QUERY].[QUERYCATEGORYCODEID], '00000000-0000-0000-0000-000000000001') [QUERYCATEGORYCODEID],
[QUERY].[OWNERID],
[QUERYVIEWCATALOG].[RECORDTYPEID],
case
when [FAVORITE].[ID] is not null then convert(bit, 1)
else convert(bit, 0)
end as [ISFAVORITE]
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
(
exists(
select
[USERQUERIES].[ID]
from [USERQUERIES]
where [USERQUERIES].[ID] = [QUERY].[ID]
)
)
and
([QUERYVIEWCATALOG].[ROOTOBJECT] = 1
and
(
[QUERY].[SITEID] is null
or
(
exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where [SITEID] = [QUERY].[SITEID] or ([SITEID] is null and [QUERY].[SITEID] is null))
)
)
)
option(recompile)
end
else if(COALESCE(@ISSYSADMIN,0) = 1)
begin
insert into #QUERIESTOINCLUDE ([QUERYID], [FOLDERID], [QUERYCATEGORYCODEID], [OWNERID], [RECORDTYPEID], [ISFAVORITE])
select
[QUERY].[ID],
isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID],
isnull([QUERY].[QUERYCATEGORYCODEID], '00000000-0000-0000-0000-000000000001') [QUERYCATEGORYCODEID],
[QUERY].[OWNERID],
[QUERYVIEWCATALOG].[RECORDTYPEID],
case
when [FAVORITE].[ID] is not null then convert(bit, 1)
else convert(bit, 0)
end as [ISFAVORITE]
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
(
[QUERYVIEWCATALOG].[ROOTOBJECT] = 1
)
end ;
/* 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], [QUERYCATEGORYCODEID], [OWNERID], [RECORDTYPEID], [ISFAVORITE])
select
[QUERY].[ID],
isnull([QUERY].[FOLDERID], '00000000-0000-0000-0000-000000000001') [FOLDERID],
isnull([QUERY].[QUERYCATEGORYCODEID], '00000000-0000-0000-0000-000000000001') [QUERYCATEGORYCODEID],
[QUERY].[OWNERID],
[SMARTQUERYCATALOG].[RECORDTYPEID],
case
when [FAVORITE].[ID] is not null then convert(bit, 1)
else convert(bit, 0)
end as [ISFAVORITE]
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)
or
([QUERY].[OWNERID] = @CURRENTAPPUSERID)
/*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
);
--Rollup by folders
if @GROUPBY = 0
begin
insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
select
f.[FOLDERID],
f.[PARENTFOLDERID],
f.[FOLDERNAME],
f.[DISPLAYORDER],
count(distinct q.[QUERYID])
from #PERMISSIONEDFOLDERS f
left join #QUERIESTOINCLUDE q on q.[FOLDERID] = f.[FOLDERID]
group by
f.[FOLDERID],
f.[PARENTFOLDERID],
f.[FOLDERNAME],
f.[DISPLAYORDER];
end
--Rollup by categories
else if @GROUPBY = 1
begin
declare @CATEGORIES table
(
[ID] uniqueidentifier,
[PARENTID] uniqueidentifier,
[DESCRIPTION] nvarchar(max),
[DISPLAYORDER] int,
[ACTIVE] bit
);
insert into @CATEGORIES ([ID], [PARENTID], [DESCRIPTION], [DISPLAYORDER], [ACTIVE])
select
[ID],
'00000000-0000-0000-0000-000000000000' [PARENTID],
[DESCRIPTION],
row_number() over(order by [DESCRIPTION]) as [DISPLAYORDER],
[ACTIVE]
from dbo.[QUERYCATEGORYCODE]
union all
select '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000003', 'root', 1, 1
union all
select '00000000-0000-0000-0000-000000000001', '00000000-0000-0000-0000-000000000000', '<none>', 0, 1;
insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
select
c.[ID],
c.[PARENTID],
c.[DESCRIPTION],
c.[DISPLAYORDER],
count(distinct q.[QUERYID])
from @CATEGORIES c
left join #QUERIESTOINCLUDE q on q.[QUERYCATEGORYCODEID] = c.[ID]
group by
c.[ID],
c.[PARENTID],
c.[DESCRIPTION],
c.[DISPLAYORDER],
c.[ACTIVE]
having c.[ACTIVE] = 1 or count(distinct q.[QUERYID]) > 0;
end
--Rollup by owner
else if @GROUPBY = 2
begin
declare @OWNERS table
(
[ID] uniqueidentifier,
[PARENTID] uniqueidentifier,
[NODENAME] nvarchar(max),
[DISPLAYORDER] int
);
insert into @OWNERS ([ID], [PARENTID], [NODENAME], [DISPLAYORDER])
select
a.[ID],
'00000000-0000-0000-0000-000000000000' [PARENTID],
case
when a.[DISPLAYNAME] = '' then a.[USERNAME]
else a.[DISPLAYNAME]
end [NODENAME],
row_number() over(order by
case
when a.[DISPLAYNAME] = '' then a.[USERNAME]
else a.[DISPLAYNAME]
end
) [DISPLAYORDER]
from (
select distinct query.[OWNERID]
from #QUERIESTOINCLUDE query
) q
inner join dbo.[APPUSER] a on q.[OWNERID] = a.[ID]
insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
select
a.[ID],
a.[PARENTID],
a.[NODENAME],
a.[DISPLAYORDER],
count(distinct q.[QUERYID]) [COUNT]
from #QUERIESTOINCLUDE q
inner join @OWNERS a on q.[OWNERID] = a.[ID]
group by
a.[ID],
a.[PARENTID],
a.[NODENAME],
a.[DISPLAYORDER]
union all
select '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000003', 'root', 1, 0;
end
--Rollup by recordtype
else if @GROUPBY = 3
begin
declare @RECORDTYPES table
(
[ID] uniqueidentifier,
[PARENTID] uniqueidentifier,
[NODENAME] nvarchar(max),
[DISPLAYORDER] int
);
insert into @RECORDTYPES ([ID], [PARENTID], [NODENAME], [DISPLAYORDER])
select distinct
r.[ID],
'00000000-0000-0000-0000-000000000000' [PARENTID],
r.[NAME] [NODENAME],
row_number() over(order by r.[NAME]) [DISPLAYORDER]
from (
select distinct query.[RECORDTYPEID]
from #QUERIESTOINCLUDE query
) q
inner join dbo.[RECORDTYPE] r on q.[RECORDTYPEID] = r.[ID]
insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
select
r.[ID],
r.[PARENTID],
r.[NODENAME],
r.[DISPLAYORDER],
count(distinct q.[QUERYID]) [COUNT]
from #QUERIESTOINCLUDE q
inner join @RECORDTYPES r on q.[RECORDTYPEID] = r.[ID]
group by
r.[ID],
r.[PARENTID],
r.[NODENAME],
r.[DISPLAYORDER]
union all
select '00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000003', 'root', 1, 0;
end
/* Favorites */
insert into @NODES([NODEID], [PARENTNODEID], [NODENAME], [DISPLAYORDER], [COUNT])
select
'00000000-0000-0000-0000-000000000002',
'00000000-0000-0000-0000-000000000003',
'Favorite',
0,
count(distinct [QUERYID])
from #QUERIESTOINCLUDE
where [ISFAVORITE] = 1;
---------
/* 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]
)
select
f.[NODEID],
f.[PARENTNODEID],
f.[NODENAME],
f.[DISPLAYORDER],
sum(r.[COUNT]) [COUNT]
from [ROLLUP] r
inner join @NODES f on r.[PARENTNODEID] = f.[NODEID]
group by
f.[NODEID],
f.[PARENTNODEID],
f.[NODENAME],
f.[DISPLAYORDER];
if OBJECT_ID('tempdb..#QUERIESTOINCLUDE') is not null
drop table #QUERIESTOINCLUDE;
if OBJECT_ID('tempdb..#PERMISSIONEDFOLDERS') is not null
drop table #PERMISSIONEDFOLDERS;