UFN_QUERY_KPISLIST
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @NODEID | nvarchar(255) | IN | |
| @GROUPBY | tinyint | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.[UFN_QUERY_KPISLIST]
(
@NODEID nvarchar(255) = null,
@GROUPBY tinyint = 1,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@CURRENTAPPUSERID uniqueidentifier
)
returns @KPI_DETAIL table
(
[KPIINSTANCEID] uniqueidentifier,
[NAME] nvarchar(255),
[DESCRIPTION] nvarchar(1000),
[KPICATALOGNAME] nvarchar(255),
[GOALTYPECODE] tinyint,
[KPIVALUEMONEY] decimal(20,4),
[KPIVALUENUMBER] decimal(20,4),
[KPIVALUEPERCENT] decimal(20,4),
[GOALVALUEMONEY] decimal(20,4),
[GOALVALUENUMBER] decimal(20,4),
[GOALVALUEPERCENT] decimal(20,4),
[YELLOWTARGETMONEY] decimal(20,4),
[YELLOWTARGETNUMBER] decimal(20,4),
[YELLOWTARGETPERCENT] decimal(20,4),
[GREENTARGETMONEY] decimal(20,4),
[GREENTARGETNUMBER] decimal(20,4),
[GREENTARGETPERCENT] decimal(20,4),
[GOALSTATUSIMAGE] nvarchar(255),
[VARIANCE] decimal(20,4),
[CLOSESTASOFDATE] datetime,
[DATEADDED] datetime,
[DATECHANGED] datetime,
[ADDEDBY_USERNAME] nvarchar(128),
[CHANGEDBY_USERNAME] nvarchar(128),
[CONTEXTRECORDTYPEID] uniqueidentifier,
[CONTEXTRECORDTYPE] nvarchar(50),
[HASVALUE] bit,
[USERHASRIGHTSTOKPI] bit,
[SITES] nvarchar(250),
[CURRENCYID] uniqueidentifier,
[FOLDERPATH] nvarchar(max),
[ISFAVORITE] bit,
[FAVORITEPIC] nvarchar(255),
[UIFOLDER] nvarchar(255),
[GROUPEDBY] tinyint
)
with execute as caller
as
begin
declare @ISSYSADMIN bit;
select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;
declare @SITEPRODUCTINSTALLED bit;
set @SITEPRODUCTINSTALLED = dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('133f9bca-00f1-4007-9792-586b931340c6');
--Check if NODEID is 00000000-0000-0000-0000-000000000002, display Favorites
--or if empty guid or null, display all queries available to user
--or if guid is '00000000-0000-0000-0000-000000000001', display the none node
declare @ISFAVORITE bit = 0;
declare @SHOWALL bit = 0;
declare @ISNONENODE bit = 0;
--This will handle situation where the list loads before a node is selected
declare @NODEGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
set @NODEGUID =
case
when @NODEID like replace('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]') then cast(@NODEID as uniqueidentifier)
else null
end;
if @NODEID = '00000000-0000-0000-0000-000000000002' --Favorites
set @ISFAVORITE = 1;
if @NODEID = '00000000-0000-0000-0000-000000000000' or @NODEID is null or @NODEID = ''
set @SHOWALL = 1;
if @NODEID = '00000000-0000-0000-0000-000000000001'
set @ISNONENODE = 1;
------
--Grab all the folders and subfolders based on the group type and permissions
declare @PERMISSIONEDFOLDERS table
(
[FOLDERID] uniqueidentifier,
[FOLDERPATH] nvarchar(max)
);
with [HIERARCHYCTE] as
(
select
[PARENTFOLDERID],
[ID] as [FOLDERID],
0 as [LEVEL],
cast([NAME] as nvarchar(max)) as [FOLDERPATH]
from [KPIINSTANCEFOLDER]
where [ID] = @NODEGUID or @SHOWALL = 1 or @ISFAVORITE = 1 or @GROUPBY = 1
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 [KPIINSTANCEFOLDER] as [FOLDERS]
inner join [HIERARCHYCTE] as cte on [FOLDERS].[PARENTFOLDERID] = cte.[FOLDERID]
)
insert into @PERMISSIONEDFOLDERS ([FOLDERID], [FOLDERPATH])
select
[FOLDERID],
[FOLDERPATH]
from [HIERARCHYCTE] cte
where [LEVEL] = (select max([LEVEL]) from [HIERARCHYCTE] cte2 where cte.[FOLDERID] = cte2.[FOLDERID]);
------
with
xmlnamespaces ('bb_appfx_dataforms' as [DFI]),
[USERKPIS] as
(
select distinct [KPIINSTANCEID]
from
(select
distinct [SYSTEMROLEKPIINSTANCE].[KPIINSTANCEID]
from dbo.[SYSTEMROLEKPIINSTANCE]
inner join dbo.[SYSTEMROLEAPPUSER] on [SYSTEMROLEKPIINSTANCE].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
where [SYSTEMROLEAPPUSER].[APPUSERID] = @CURRENTAPPUSERID
union all
select
[KPIINSTANCE].[ID]
from dbo.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_KPI] [SECURITYVIEW]
inner join dbo.[KPIINSTANCE] on [SECURITYVIEW].[KPICATALOGID] = [KPIINSTANCE].[KPICATALOGID]
where [SECURITYVIEW].[APPUSERID] = @CURRENTAPPUSERID
group by [KPIINSTANCE].[ID]
having min(cast([SECURITYVIEW].[GRANTORDENY] as tinyint)) = 1) as [KPI]
)
insert into @KPI_DETAIL
(
[KPIINSTANCEID],
[NAME],
[DESCRIPTION],
[KPICATALOGNAME],
[GOALTYPECODE],
[KPIVALUEMONEY],
[KPIVALUENUMBER],
[KPIVALUEPERCENT],
[GOALVALUEMONEY],
[GOALVALUENUMBER],
[GOALVALUEPERCENT],
[YELLOWTARGETMONEY],
[YELLOWTARGETNUMBER],
[YELLOWTARGETPERCENT],
[GREENTARGETMONEY],
[GREENTARGETNUMBER],
[GREENTARGETPERCENT],
[GOALSTATUSIMAGE],
[VARIANCE],
[CLOSESTASOFDATE],
[DATEADDED],
[DATECHANGED],
[ADDEDBY_USERNAME],
[CHANGEDBY_USERNAME],
[CONTEXTRECORDTYPEID],
[CONTEXTRECORDTYPE],
[HASVALUE],
[USERHASRIGHTSTOKPI],
[SITES],
[CURRENCYID],
[ISFAVORITE],
[FAVORITEPIC],
[FOLDERPATH],
[UIFOLDER],
[GROUPEDBY]
)
select
[KPIINSTANCE].[ID] [KPIINSTANCEID],
[KPIINSTANCE].[NAME],
[KPIINSTANCE].[DESCRIPTION],
[KPICATALOG].[NAME] [KPICATALOGNAME],
[KPICATALOG].[GOALTYPECODE],
convert(money, case [KPICATALOG].[GOALTYPECODE]
when 0 then [HISTORY].[VALUE]
else null
end) [KPIVALUEMONEY],
convert(bigint, case [KPICATALOG].[GOALTYPECODE]
when 1 then [HISTORY].[VALUE]
else null
end) [KPIVALUENUMBER],
case [KPICATALOG].[GOALTYPECODE]
when 2 then [HISTORY].[VALUE]
else null
end [KPIVALUEPERCENT],
convert(money, case [KPICATALOG].[GOALTYPECODE]
when 0 then [KPIINSTANCE].[GOALVALUE]
else null
end) [GOALVALUEMONEY],
convert(bigint, case [KPICATALOG].[GOALTYPECODE]
when 1 then [KPIINSTANCE].[GOALVALUE]
else null
end) [GOALVALUENUMBER],
case [KPICATALOG].[GOALTYPECODE]
when 2 then [KPIINSTANCE].[GOALVALUE]
else null
end [GOALVALUEPERCENT],
convert(money, case [KPICATALOG].[GOALTYPECODE]
when 0 then case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then [GOALUPWARDLOWVALUE]
else [GOALDOWNWARDHIGHVALUE]
end
else null
end) [YELLOWTARGETMONEY],
convert(bigint, case [KPICATALOG].[GOALTYPECODE]
when 1 then case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then [GOALUPWARDLOWVALUE]
else [GOALDOWNWARDHIGHVALUE]
end
else null
end) [YELLOWTARGETNUMBER],
case [KPICATALOG].[GOALTYPECODE]
when 2 then case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then [GOALUPWARDLOWVALUE]
else [GOALDOWNWARDHIGHVALUE]
end
else null
end [YELLOWTARGETPERCENT],
convert(money, case [KPICATALOG].[GOALTYPECODE]
when 0 then case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then [GOALUPWARDMIDVALUE]
else [GOALDOWNWARDMIDVALUE]
end
end) [GREENTARGETMONEY],
convert(bigint, case [KPICATALOG].[GOALTYPECODE]
when 1 then case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then [GOALUPWARDMIDVALUE]
else [GOALDOWNWARDMIDVALUE]
end
end) [GREENTARGETNUMBER],
case [KPICATALOG].[GOALTYPECODE]
when 2 then
case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then [GOALUPWARDMIDVALUE]
else [GOALDOWNWARDMIDVALUE]
end
end [GREENTARGETPERCENT],
case
when [HISTORY].[VALUE] is null then 'res:roadblank'
else
case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then
case
when [HISTORY].[VALUE] < [GOALUPWARDMIDVALUE] then 'res:road0'
when [HISTORY].[VALUE] < [GOALUPWARDLOWVALUE] then 'res:road1'
else 'res:road2'
end
when 1 then
case
when [HISTORY].[VALUE] > [GOALDOWNWARDMIDVALUE] then 'res:road0'
when [HISTORY].[VALUE] > [GOALDOWNWARDHIGHVALUE] then 'res:road1'
else 'res:road2'
end
end
end [GOALSTATUSIMAGE],
case
when [KPIINSTANCE].[GOALVALUE] <> 0 then
case [KPIINSTANCE].[GOALAIMTYPECODE]
when 0 then ([HISTORY].[VALUE] - [KPIINSTANCE].[GOALVALUE]) / abs([KPIINSTANCE].[GOALVALUE])
when 1 then -1 * (([HISTORY].[VALUE] - [KPIINSTANCE].[GOALVALUE]) / abs([KPIINSTANCE].[GOALVALUE]))
end
else 0
end [VARIANCE],
[HISTORY].[ASOFDATE] as [CLOSESTASOFDATE],
[KPIINSTANCE].[DATEADDED],
[KPIINSTANCE].[DATECHANGED],
[ADDEDBY].[USERNAME] [ADDEDBY_USERNAME],
[CHANGEDBY].[USERNAME] [CHANGEDBY_USERNAME],
isnull([RECORDTYPE].[ID], '00000000-0000-0000-0000-000000000000') [CONTEXTRECORDTYPEID],
[RECORDTYPE].[NAME] [CONTEXTRECORDTYPE],
cast((case when [HISTORY].[VALUE] is null then 0 else 1 end) as bit) as [HASVALUE],
1 as [USERHASRIGHTSTOKPI],
case
when [KPIINSTANCE].[SITEFILTERENABLEDFORINSTANCE] = 1
then
(
select
dbo.[UDA_BUILDLIST]([SITE].[NAME]) as [SITES]
from dbo.[KPIINSTANCESITE]
inner join dbo.[SITE] on [KPIINSTANCESITE].[SITEID] = [SITE].[ID]
where [KPIINSTANCESITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
)
else ''
end as [SITES],
[KPIINSTANCE].PARAMETERSXML.value('data(/DFI:DataFormItem/DFI:Values/DFI:fv[@ID="CURRENCYID"]/DFI:Value)[1]','varchar(36)') as [CURRENCYID],
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],
[KPICATALOG].[UIFOLDER],
@GROUPBY as [GROUPEDBY]
from dbo.[KPIINSTANCE]
left join dbo.[KPICATALOG] on [KPIINSTANCE].[KPICATALOGID] = [KPICATALOG].[ID]
left join dbo.[RECORDTYPE] on [KPICATALOG].[CONTEXTRECORDTYPEID] = [RECORDTYPE].[ID]
left join dbo.[CHANGEAGENT] [ADDEDBY] on [KPIINSTANCE].ADDEDBYID = [ADDEDBY].[ID]
left join dbo.[CHANGEAGENT] [CHANGEDBY] on [KPIINSTANCE].CHANGEDBYID = [CHANGEDBY].[ID]
left join dbo.[APPUSERKPIINSTANCEFAVORITE] [FAVORITE] on [FAVORITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID] and [FAVORITE].[APPUSERID] = @CURRENTAPPUSERID
left join @PERMISSIONEDFOLDERS as [FOLDERS] on [FOLDERS].[FOLDERID] = [KPIINSTANCE].[FOLDERID]
outer apply (
select top (1) [ID], [KPIINSTANCEID], [VALUE], [ASOFDATE]
from dbo.[KPIINSTANCEHISTORY]
where [KPIINSTANCEID] = [KPIINSTANCE].[ID]
order by [ASOFDATE] desc
) as [HISTORY]
where
(
(
[KPIINSTANCE].[FOLDERID] in
(
select [FOLDERID]
from @PERMISSIONEDFOLDERS
)
and @ISFAVORITE = 0 and @GROUPBY = 0
) --Selected folder
or (
(
[KPICATALOG].[UIFOLDER] + '\' + [KPICATALOG].[NAME] = @NODEID
or
[KPICATALOG].[UIFOLDER] like @NODEID + '\%'
or
[KPICATALOG].[UIFOLDER] = @NODEID
)
and @GROUPBY = 1
) --Selected type
or ([KPICATALOG].[CONTEXTRECORDTYPEID] = @NODEGUID and @GROUPBY = 2) --Selected recordtype
or (@SHOWALL = 1) --All queries
or ([KPIINSTANCE].[FOLDERID] is null and @ISNONENODE = 1 and @GROUPBY = 0) --none folder node
or ([KPICATALOG].[CONTEXTRECORDTYPEID] is null and @ISNONENODE = 1 and @GROUPBY = 2) --none record type node
or ([FAVORITE].[APPUSERID] = @CURRENTAPPUSERID and @ISFAVORITE = 1) --Favorites
)
and
(@ISSYSADMIN = 1
or exists
(select [KPIINSTANCEID]
from [USERKPIS]
where [USERKPIS].[KPIINSTANCEID] = [KPIINSTANCE].[ID])
) --Security check
and
(@ISSYSADMIN = 1 or
isnull(@SITEFILTERMODE, 0) = 0
or exists
(
select top 1 1
from dbo.[KPIINSTANCESITE]
inner join dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as [VALIDSITE] on [KPIINSTANCESITE].[SITEID] = [VALIDSITE].[SITEID]
where [KPIINSTANCESITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
)
)
and
(
[KPICATALOG].[HASSITEFILTER] = 0
or [KPIINSTANCE].[SITEFILTERENABLEDFORINSTANCE] = 0
or @SITEPRODUCTINSTALLED = 0
or @ISSYSADMIN = 1
or exists
(
select top 1 1
from dbo.[SYSTEMROLEAPPUSER]
where [SYSTEMROLEAPPUSER].[APPUSERID] = @CURRENTAPPUSERID
--make sure the kpi instance is permissioned for this role
and exists
(
select top 1 1
from dbo.[SYSTEMROLEKPIINSTANCE]
where [SYSTEMROLEKPIINSTANCE].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
and [SYSTEMROLEKPIINSTANCE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
)
and
(
[SYSTEMROLEAPPUSER].[SECURITYMODECODE] = 0 -- all sites
or
(
[SYSTEMROLEAPPUSER].[SECURITYMODECODE] = 1 -- no site
and not exists (select top 1 1 from dbo.[KPIINSTANCESITE] where [KPIINSTANCEID] = [KPIINSTANCE].[ID])
)
or
(
[SYSTEMROLEAPPUSER].[SECURITYMODECODE] in (2, 3) -- records with one of these sites assigned or within a branch
and exists
(
select top 1 1
from dbo.[SITEPERMISSION]
inner join dbo.[KPIINSTANCESITE] on [SITEPERMISSION].[SITEID] = [KPIINSTANCESITE].[SITEID]
where [SITEPERMISSION].[SYSTEMROLEID] = [SYSTEMROLEAPPUSER].[SYSTEMROLEID]
and [SITEPERMISSION].[APPUSERID] = [SYSTEMROLEAPPUSER].[APPUSERID]
and [KPIINSTANCESITE].[KPIINSTANCEID] = [KPIINSTANCE].[ID]
)
)
)
)
);
return;
end;