UFN_QUERY_QUERIESLIST
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @NODEID | uniqueidentifier | IN | |
| @GROUPBY | tinyint | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN | |
| @FEATURETIPS | xml | IN |
Definition
Copy
CREATE function dbo.[UFN_QUERY_QUERIESLIST]
(
@NODEID uniqueidentifier = null,
@GROUPBY tinyint = 0,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@CURRENTAPPUSERID uniqueidentifier,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@FEATURETIPS xml = null
)
returns table
as
return
with xmlnamespaces ('Blackbaud.AppFx.WebService.API.1' as [ns]),
[HIERARCHYCTE] as
(
select
[PARENTFOLDERID],
[ID] as [FOLDERID],
0 as [LEVEL],
cast([NAME] as nvarchar(max)) as [FOLDERPATH]
from ( select
[ID],
[PARENTFOLDERID],
[NAME]
from dbo.[UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS](@CURRENTAPPUSERID)) as [USERFOLDERS]
where [ID] = @NODEID or (@NODEID = '00000000-0000-0000-0000-000000000000' or @NODEID is null) or @GROUPBY > 0 or @NODEID = '00000000-0000-0000-0000-000000000002'
union all
select
[FOLDERS].[PARENTFOLDERID],
[FOLDERS].[ID] as [FOLDERID],
cte.[LEVEL] + 1 as [LEVEL],
cast(cte.[FOLDERPATH] + ' | ' + [FOLDERS].[NAME] as nvarchar(max)) as [FOLDERPATH]
from ( select
[ID],
[PARENTFOLDERID],
[NAME]
from dbo.[UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS](@CURRENTAPPUSERID)) as [FOLDERS]
inner join [HIERARCHYCTE] as cte on [FOLDERS].[PARENTFOLDERID] = cte.[FOLDERID]
),
[PERMISSIONEDFOLDERS] as
( select
[FOLDERID],
[FOLDERPATH]
from [HIERARCHYCTE] cte
where [LEVEL] = (select max([LEVEL]) from [HIERARCHYCTE] cte2 where cte.[FOLDERID] = cte2.[FOLDERID])),
/* Filter out any ad-hoc queries involving query views to which the current user does not have access */
[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
),
[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]
)
)
select
[QUERY].[ID],
[QUERY].[NAME],
[QUERY].[DESCRIPTION],
[RECORDTYPE].[ID] [RECORDTYPEID],
[RECORDTYPE].[NAME] [RECORDTYPE_NAME],
isnull([QUERYCATEGORYCODE].[ID], '00000000-0000-0000-0000-000000000000') [QUERYCATEGORYCODEID],
[QUERYCATEGORYCODE].[DESCRIPTION] [QUERYCATEGORYCODE_DESCRIPTION],
[QUERYVIEWCATALOG].[ID] [SOURCEVIEWID],
[QUERYVIEWCATALOG].[DISPLAYNAME] [SOURCEVIEW],
case
when [OWNER].[DISPLAYNAME] = '' then [OWNER].[USERNAME]
else [OWNER].[DISPLAYNAME]
end [OWNER],
[QUERY].[OWNERID],
[QUERY].[OTHERSCANMODIFY],
[QUERY].[DATEADDED],
[ADDEDBY].[USERNAME] [ADDEDBY_USERNAME],
[QUERY].[DATECHANGED],
[CHANGEDBY].[USERNAME] [CHANGEDBY_USERNAME],
[QUERY].[ISBROWSABLE],
coalesce([SITE].[NAME], 'All sites') [SITE],
[QUERY].[MOBILIZE],
case
when [SECURITYUSER].[ISSYSADMIN] = 1 then cast(1 as bit)
when dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE](@CURRENTAPPUSERID, [QUERY].[ID]) = 1 then cast(1 as bit)
else cast(0 as bit)
end as [USERCANEDIT],
0 as [QUERY_TYPE],
'Ad-hoc' as [QUERY_TYPE_NAME],
case
when [FAVORITE].[ID] is not null then convert(bit, 1)
else convert(bit, 0)
end as [ISFAVORITE],
case
when [FAVORITE].[ID] is not null then 'catalog:Blackbaud.AppFx.Platform.Catalog.dll,Blackbaud.AppFx.Platform.Catalog.favorites_blue_star_16.png'
else 'catalog:Blackbaud.AppFx.Platform.Catalog.dll,Blackbaud.AppFx.Platform.Catalog.favorites_gray_star_16.png'
end as [FAVORITEPIC],
[FOLDERS].[FOLDERPATH],
case
when [IDSETREGISTERADHOCQUERY].[ID] is null then 0
else 1
end as [HASSELECTION],
@GROUPBY as [GROUPEDBY],
[RUNINFO].[LASTRUNDATE],
[RUNINFO].[LASTRUNDURATION],
case
when [LASTRUNBY].[DISPLAYNAME] = '' then [LASTRUNBY].[USERNAME]
else [LASTRUNBY].[DISPLAYNAME]
end [LASTRUNBY_USERNAME],
[RUNINFO].[LASTRUNRESULTCOUNT],
[RUNINFO].[RUNCOUNT],
'Get OData link' as [ODATAFEED],
[ODATA].[LASTRUNDATE] as [ODATALASTRUNDATE],
[ODATA].[LASTRUNDURATION] as [ODATALASTRUNDURATION],
case
when [ODATALASTRUNBY].[DISPLAYNAME] = '' then [ODATALASTRUNBY].[USERNAME]
else [ODATALASTRUNBY].[DISPLAYNAME]
end [ODATALASTRUNBY_USERNAME],
[ODATA].[LASTRUNRESULTCOUNT] as [ODATALASTRUNRESULTCOUNT],
[ODATA].[RUNCOUNT] as [ODATARUNCOUNT],
case when [IDSETREGISTER].[STATIC] = 1 then '00000000-0000-0000-0000-000000000002'
when [IDSETREGISTER].[STATIC] = 0 then '00000000-0000-0000-0000-000000000001'
else '00000000-0000-0000-0000-000000000000' end as [SELECTIONTYPEID],
case when [IDSETREGISTER].[STATIC] = 1 then 'Static'
when [IDSETREGISTER].[STATIC] = 0 then 'Dynamic'
else '' end as [SELECTIONTYPE],
case
when [DELETEERROR].[ID] is null then 0
else 1
end as [DELETEERROR]
from dbo.[ADHOCQUERY] [QUERY]
inner join dbo.[QUERYVIEWCATALOG] on [QUERY].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
left join dbo.[ADHOCQUERYRUNINFO] [RUNINFO] on [QUERY].[ID] = [RUNINFO].[ID]
left join dbo.[APPUSERADHOCQUERYFAVORITE] [FAVORITE] on [FAVORITE].[ADHOCQUERYID] = [QUERY].[ID] and [FAVORITE].[APPUSERID] = @CURRENTAPPUSERID
left join dbo.[IDSETREGISTERADHOCQUERY] on [QUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
left join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID]
left join dbo.[RECORDTYPE] on [QUERYVIEWCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID]
left join [PERMISSIONEDFOLDERS] as [FOLDERS] on [FOLDERS].[FOLDERID] = [QUERY].[FOLDERID]
left join dbo.[APPUSER] as [OWNER] on [QUERY].[OWNERID] = [OWNER].[ID]
left join dbo.[QUERYCATEGORYCODE] on [QUERY].[QUERYCATEGORYCODEID] = [QUERYCATEGORYCODE].[ID]
left join dbo.[CHANGEAGENT] as [ADDEDBY] on [QUERY].[ADDEDBYID] = [ADDEDBY].[ID]
left join dbo.[CHANGEAGENT] as [CHANGEDBY] on [QUERY].[CHANGEDBYID] = [CHANGEDBY].[ID]
left join dbo.[APPUSER] as [LASTRUNBY] on [RUNINFO].[LASTRUNBY] = [LASTRUNBY].[ID]
left join dbo.[SITE] on [QUERY].[SITEID] = [SITE].[ID]
left join dbo.[ODATAQUERYRUNINFO] as [ODATA] on [ODATA].[QUERYID] = [QUERY].[ID]
left join dbo.[APPUSER] as [ODATALASTRUNBY] on [ODATA].[LASTRUNBY] = [ODATALASTRUNBY].[ID]
left join dbo.[QUERYWITHDELETEERROR] as [DELETEERROR] on [DELETEERROR].[QUERYID] = [QUERY].[ID]
left join(
select
[ISSYSADMIN],
dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f') as [HASQUERYPRIVILEGE]
from dbo.[APPUSER]
where [ID] = @CURRENTAPPUSERID
) as [SECURITYUSER] on 1=1
where
--Check security
(
(
--User is a system administrator
([SECURITYUSER].[ISSYSADMIN] = 1)
or
--User has query system privilege, check system roles for query rights
(
[SECURITYUSER].[HASQUERYPRIVILEGE] = 1
and exists
(
select
[USERQUERIES].[ID]
from [USERQUERIES]
where [USERQUERIES].[ID] = [QUERY].[ID]
)
)
or
([QUERY].[OWNERID] = @CURRENTAPPUSERID)
or
--User is not admin or query system privilege, check system role and query view rights
([SECURITYUSER].[ISSYSADMIN] = 0 and [SECURITYUSER].[HASQUERYPRIVILEGE] = 0
and exists
(
select
[OTHERQUERIES].[ID]
from dbo.[UFN_ADHOCQUERY_GETUSERQUERIES](@CURRENTAPPUSERID) [OTHERQUERIES]
where [OTHERQUERIES].[ID] = [QUERY].[ID]
)
)
)
)
and
(
(
[QUERY].[FOLDERID] in
(
select [FOLDERID]
from [PERMISSIONEDFOLDERS]
)
or [QUERY].[FOLDERID] is null
)
) --Check folder security
and
(
([QUERY].[FOLDERID] in
(
select [FOLDERID]
from [PERMISSIONEDFOLDERS]
)
and @GROUPBY = 0 and @NODEID not in ('00000000-0000-0000-0000-000000000002','00000000-0000-0000-0000-000000000001','00000000-0000-0000-0000-000000000000')
) --selected folder
or ([QUERY].[QUERYCATEGORYCODEID] = @NODEID and @GROUPBY = 1) --Selected category
or ([QUERY].[OWNERID] = @NODEID and @GROUPBY = 2) --Selected owner
or ([QUERYVIEWCATALOG].[RECORDTYPEID] = @NODEID and @GROUPBY = 3) --Selected recordtype
or (@NODEID = '00000000-0000-0000-0000-000000000000' or @NODEID is null) --All queries
or ([FAVORITE].[APPUSERID] = @CURRENTAPPUSERID and @NODEID = '00000000-0000-0000-0000-000000000002') --Favorites
or ([QUERY].[FOLDERID] is null and @NODEID = '00000000-0000-0000-0000-000000000001' and @GROUPBY = 0) --none folder node
or ([QUERY].[QUERYCATEGORYCODEID] is null and @NODEID = '00000000-0000-0000-0000-000000000001' and @GROUPBY = 1) --none category node
)
/* End restricted query views filter */
and [QUERYVIEWCATALOG].[ROOTOBJECT] = 1
and
(
[QUERY].[SITEID] is null
or
([SECURITYUSER].[ISSYSADMIN] = 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)))
)
and
(
@SITEFILTERMODE = 0
or
[QUERY].[SITEID] in
(
select [SITEID]
from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
)
union all
select
[SMARTQUERYINSTANCE].[ID],
[SMARTQUERYINSTANCE].[NAME],
[SMARTQUERYINSTANCE].[DESCRIPTION],
[RECORDTYPE].[ID] [RECORDTYPEID],
[RECORDTYPE].[NAME] [RECORDTYPE_NAME],
isnull([QUERYCATEGORYCODE].[ID], '00000000-0000-0000-0000-000000000000') [QUERYCATEGORYCODEID],
[QUERYCATEGORYCODE].[DESCRIPTION] [QUERYCATEGORYCODE_DESCRIPTION],
[SMARTQUERYCATALOG].[ID] [SOURCEVIEWID],
[SMARTQUERYCATALOG].[NAME] as [SOURCEVIEW],
case
when [OWNER].[DISPLAYNAME] = '' then [OWNER].[USERNAME]
else [OWNER].[DISPLAYNAME]
end [OWNER],
[SMARTQUERYINSTANCE].[OWNERID],
[SMARTQUERYINSTANCE].[OTHERSCANMODIFY],
[SMARTQUERYINSTANCE].[DATEADDED],
[ADDEDBY].[USERNAME] [ADDEDBY_USERNAME],
[SMARTQUERYINSTANCE].[DATECHANGED],
[CHANGEDBY].[USERNAME] [CHANGEDBY_USERNAME],
case
when [SMARTQUERYCATALOG].[PRIMARYKEYFIELD] is not null then 1
else 0
end as [ISBROWSABLE],
coalesce([SITE].[NAME], 'All sites') [SITE],
[SMARTQUERYINSTANCE].[MOBILIZE],
cast(1 as bit) as [USERCANEDIT],
1 as [QUERY_TYPE],
'Smart' as [QUERY_TYPE_NAME],
case
when [FAVORITE].[ID] is not null then convert(bit, 1)
else convert(bit, 0)
end as [ISFAVORITE],
case
when [FAVORITE].[ID] is not null then 'catalog:Blackbaud.AppFx.Platform.Catalog.dll,Blackbaud.AppFx.Platform.Catalog.favorites_blue_star_16.png'
else 'catalog:Blackbaud.AppFx.Platform.Catalog.dll,Blackbaud.AppFx.Platform.Catalog.favorites_gray_star_16.png'
end as [FAVORITEPIC],
[FOLDERS].[FOLDERPATH],
case
when [IDSETREGISTERSMARTQUERYINSTANCE].[ID] is null then 0
else 1
end as [HASSELECTION],
@GROUPBY as [GROUPEDBY],
[RUNINFO].[LASTRUNDATE],
[RUNINFO].[LASTRUNDURATION],
case
when [LASTRUNBY].[DISPLAYNAME] = '' then [LASTRUNBY].[USERNAME]
else [LASTRUNBY].[DISPLAYNAME]
end [LASTRUNBY_USERNAME],
[RUNINFO].[LASTRUNRESULTCOUNT],
[RUNINFO].[RUNCOUNT],
'Get OData link' as [ODATAFEED],
[ODATA].[LASTRUNDATE] as [ODATALASTRUNDATE],
[ODATA].[LASTRUNDURATION] as [ODATALASTRUNDURATION],
case
when [ODATALASTRUNBY].[DISPLAYNAME] = '' then [ODATALASTRUNBY].[USERNAME]
else [ODATALASTRUNBY].[DISPLAYNAME]
end [ODATALASTRUNBY_USERNAME],
[ODATA].[LASTRUNRESULTCOUNT] as [ODATALASTRUNRESULTCOUNT],
[ODATA].[RUNCOUNT] as [ODATARUNCOUNT],
case when [IDSETREGISTER].[STATIC] = 1 then '00000000-0000-0000-0000-000000000002'
when [IDSETREGISTER].[STATIC] = 0 then '00000000-0000-0000-0000-000000000001'
else '00000000-0000-0000-0000-000000000000' end as [SELECTIONTYPEID],
case when [IDSETREGISTER].[STATIC] = 1 then 'Static'
when [IDSETREGISTER].[STATIC] = 0 then 'Dynamic'
else '' end as [SELECTIONTYPE],
case
when [DELETEERROR].[ID] is null then 0
else 1
end as [DELETEERROR]
from dbo.[SMARTQUERYINSTANCE]
inner join dbo.[SMARTQUERYCATALOG] on [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID]
left join dbo.[SMARTQUERYINSTANCERUNINFO] [RUNINFO] on [SMARTQUERYINSTANCE].[ID] = [RUNINFO].[ID]
left join dbo.[APPUSERSMARTQUERYINSTANCEFAVORITE] [FAVORITE] on [FAVORITE].[SMARTQUERYINSTANCEID] = [SMARTQUERYINSTANCE].[ID] and [FAVORITE].[APPUSERID] = @CURRENTAPPUSERID
left join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
left join dbo.[IDSETREGISTER] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
left join dbo.[RECORDTYPE] on [SMARTQUERYCATALOG].[RECORDTYPEID] = [RECORDTYPE].[ID]
left join [PERMISSIONEDFOLDERS] as [FOLDERS] on [FOLDERS].[FOLDERID] = [SMARTQUERYINSTANCE].[FOLDERID]
left join dbo.[APPUSER] as [OWNER] on [SMARTQUERYINSTANCE].[OWNERID] = [OWNER].[ID]
left join dbo.[QUERYCATEGORYCODE] on [SMARTQUERYINSTANCE].[QUERYCATEGORYCODEID] = [QUERYCATEGORYCODE].[ID]
left join dbo.[CHANGEAGENT] as [ADDEDBY] on [SMARTQUERYINSTANCE].[ADDEDBYID] = [ADDEDBY].[ID]
left join dbo.[CHANGEAGENT] as [CHANGEDBY] on [SMARTQUERYINSTANCE].[CHANGEDBYID] = [CHANGEDBY].[ID]
left join dbo.[APPUSER] as [LASTRUNBY] on [RUNINFO].[LASTRUNBY] = [LASTRUNBY].[ID]
left join dbo.[SITE] on [SMARTQUERYINSTANCE].[SITEID] = [SITE].[ID]
left join dbo.[ODATAQUERYRUNINFO] as [ODATA] on [ODATA].[QUERYID] = [SMARTQUERYINSTANCE].[ID]
left join dbo.[APPUSER] as [ODATALASTRUNBY] on [ODATA].[LASTRUNBY] = [ODATALASTRUNBY].[ID]
left join dbo.[QUERYWITHDELETEERROR] as [DELETEERROR] on [DELETEERROR].[QUERYID] = [SMARTQUERYINSTANCE].[ID]
left join(
select
[ISSYSADMIN]
from dbo.[APPUSER]
where [ID] = @CURRENTAPPUSERID
) as [SECURITYUSER] on 1=1
where
/* Filter out restricted smart queries */
(
([SECURITYUSER].[ISSYSADMIN] = 1)
or
([SMARTQUERYINSTANCE].[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]
where [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID]
)
and
(
dbo.[UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [SMARTQUERYINSTANCE].[ID]) = 1
))
)
and
(
(
[SMARTQUERYINSTANCE].[FOLDERID] in
(
select [FOLDERID]
from [PERMISSIONEDFOLDERS]
)
and @NODEID <> '00000000-0000-0000-0000-000000000002'
and @GROUPBY = 0
) --Selected folder
or ([SMARTQUERYINSTANCE].[QUERYCATEGORYCODEID] = @NODEID and @GROUPBY = 1) --Selected category
or ([SMARTQUERYINSTANCE].[OWNERID] = @NODEID and @GROUPBY = 2) --Selected owner
or ([SMARTQUERYCATALOG].[RECORDTYPEID] = @NODEID and @GROUPBY = 3) --Selected recordtype
or (@NODEID = '00000000-0000-0000-0000-000000000000' or @NODEID is null) --All queries
or ([FAVORITE].[APPUSERID] = @CURRENTAPPUSERID and @NODEID = '00000000-0000-0000-0000-000000000002') --Favorites
or ([SMARTQUERYINSTANCE].[FOLDERID] is null and @NODEID = '00000000-0000-0000-0000-000000000001' and @GROUPBY = 0) --none folder node
or ([SMARTQUERYINSTANCE].[QUERYCATEGORYCODEID] is null and @NODEID = '00000000-0000-0000-0000-000000000001' and @GROUPBY = 1) --none category node
)
and
(
[SMARTQUERYINSTANCE].[SITEID] is null
or
(
[SECURITYUSER].[ISSYSADMIN] = 1
or exists
(
select 1
from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
where [SITEID]=[SMARTQUERYINSTANCE].[SITEID] or ([SITEID] is null and [SMARTQUERYINSTANCE].[SITEID] is null)
)
)
)
and
(
@SITEFILTERMODE = 0
or
[SMARTQUERYINSTANCE].[SITEID] in
(
select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
)