UFN_QUERY_IMPORT_LISTBUILDER
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN | |
| @BATCHTEMPLATEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.[UFN_QUERY_IMPORT_LISTBUILDER]
(
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@BATCHTEMPLATEID uniqueidentifier = null
)
returns @IMPORTS table
(
[ID] uniqueidentifier,
[NAME] nvarchar(255),
[DESCRIPTION] nvarchar(1000),
[BATCHTEMPLATE] nvarchar(60),
[BATCHTEMPLATEID] uniqueidentifier,
[DATECREATED] datetime,
[SITES] nvarchar(255),
[OWNERID] uniqueidentifier,
[USEWILDCARDS] bit,
[LASTRUNDURATION] time,
[LASTRUNBY_USERNAME] nvarchar(255),
[LASTRUNRESULTCOUNT] int,
[LASTRUNSTATUS] nvarchar(25),
[LASTRUNSTARTEDON] datetime
)
with execute as caller
as
begin
if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'aa34552c-a7c4-4487-b10d-65798a87d8b8') = 1
insert into @IMPORTS ([ID], [NAME], [DESCRIPTION], [BATCHTEMPLATE], [BATCHTEMPLATEID], [DATECREATED], [SITES], [OWNERID], [USEWILDCARDS], [LASTRUNDURATION], [LASTRUNBY_USERNAME], [LASTRUNRESULTCOUNT], [LASTRUNSTATUS], [LASTRUNSTARTEDON])
select
[IMPORTPROCESS].[ID],
[IMPORTPROCESS].[NAME],
[IMPORTPROCESS].[DESCRIPTION],
[BATCHTEMPLATE].[NAME] as [BATCHTEMPLATE],
[IMPORTPROCESS].[BATCHTEMPLATEID] as [BATCHTEMPLATEID],
[IMPORTPROCESS].[DATEADDED] as [DATECREATED],
coalesce([SITE].[NAME], 'All Sites') [SITES],
[BPI].[OWNERID] as [OWNERID],
[IMPORTPROCESS].[USEWILDCARDS],
cast([BPS].[ENDEDON] - [BPS].[STARTEDON] as time) as [LASTRUNDURATION],
case when [APPUSER].[DISPLAYNAME] = '' then [APPUSER].[USERNAME] else [APPUSER].[DISPLAYNAME] end as [LASTRUNBY_USERNAME],
[BPS].[NUMBERPROCESSED] as [LASTRUNRESULTCOUNT],
isnull([BPS].[STATUS], 'Not started') as [LASTRUNSTATUS],
[BPS].[STARTEDON] as [LASTRUNSTARTEDON]
from dbo.[IMPORTPROCESS]
inner join dbo.[BATCHTEMPLATE] on [IMPORTPROCESS].[BATCHTEMPLATEID] = [BATCHTEMPLATE].[ID]
left join dbo.[CHANGEAGENT] on [IMPORTPROCESS].[ADDEDBYID] = [CHANGEAGENT].[ID]
left join dbo.[BUSINESSPROCESSINSTANCE] BPI on [IMPORTPROCESS].[ID] = BPI.[BUSINESSPROCESSPARAMETERSETID]
left join dbo.[SITE] on [SITE].[ID] = BPI.[SITEID]
outer apply (
select
top 1
[STARTEDON],
[STATUS],
[STARTEDBYUSERID],
[ENDEDON],
[NUMBERPROCESSED],
[BUSINESSPROCESSPARAMETERSETID]
from dbo.[BUSINESSPROCESSSTATUS]
where [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = BPI.[BUSINESSPROCESSPARAMETERSETID]
order by [STARTEDON] desc
) as [BPS]
left join dbo.[APPUSER] on [APPUSER].[ID] = [BPS].[STARTEDBYUSERID]
where
(@BATCHTEMPLATEID = '00000000-0000-0000-0000-000000000000' or @BATCHTEMPLATEID is null or [IMPORTPROCESS].[BATCHTEMPLATEID] = @BATCHTEMPLATEID)
and (--Apply site filter
@SITEFILTERMODE = 0
or BPI.[SITEID] in (
select [SITEID]
from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
)
else
begin
--The outer apply and the condition in where clause when executed together causes performance issues. Hence, using temporary table @ALLIMPORTS to separate filtering of data from the select statement.
declare @ALLIMPORTS table
(
[ID] uniqueidentifier,
[NAME] nvarchar(255),
[DESCRIPTION] nvarchar(1000),
[BATCHTEMPLATE] nvarchar(60),
[BATCHTEMPLATEID] uniqueidentifier,
[DATECREATED] datetime,
[SITES] nvarchar(255),
[OWNERID] uniqueidentifier,
[USEWILDCARDS] bit,
[LASTRUNDURATION] time,
[LASTRUNBY_USERNAME] nvarchar(255),
[LASTRUNRESULTCOUNT] int,
[LASTRUNSTATUS] nvarchar(25),
[LASTRUNSTARTEDON] datetime
)
insert into @ALLIMPORTS ([ID], [NAME], [DESCRIPTION], [BATCHTEMPLATE], [BATCHTEMPLATEID], [DATECREATED], [SITES], [OWNERID], [USEWILDCARDS], [LASTRUNDURATION], [LASTRUNBY_USERNAME], [LASTRUNRESULTCOUNT], [LASTRUNSTATUS], [LASTRUNSTARTEDON])
select
[IMPORTPROCESS].[ID],
[IMPORTPROCESS].[NAME],
[IMPORTPROCESS].[DESCRIPTION],
[BATCHTEMPLATE].[NAME] as [BATCHTEMPLATE],
[IMPORTPROCESS].[BATCHTEMPLATEID] as [BATCHTEMPLATEID],
[IMPORTPROCESS].[DATEADDED] as [DATECREATED],
coalesce([SITE].[NAME], 'All Sites') [SITES],
[BPI].[OWNERID] as [OWNERID],
[IMPORTPROCESS].[USEWILDCARDS],
cast([BPS].[ENDEDON] - [BPS].[STARTEDON] as time) as [LASTRUNDURATION],
case when [APPUSER].[DISPLAYNAME] = '' then [APPUSER].[USERNAME] else [APPUSER].[DISPLAYNAME] end as [LASTRUNBY_USERNAME],
[BPS].[NUMBERPROCESSED] as [LASTRUNRESULTCOUNT],
isnull([BPS].[STATUS], 'Not started') as [LASTRUNSTATUS],
[BPS].[STARTEDON] as [LASTRUNSTARTEDON]
from dbo.[IMPORTPROCESS]
inner join dbo.[BATCHTEMPLATE] on [IMPORTPROCESS].[BATCHTEMPLATEID] = [BATCHTEMPLATE].[ID]
left join dbo.[CHANGEAGENT] on [IMPORTPROCESS].[ADDEDBYID] = [CHANGEAGENT].[ID]
left join dbo.[BUSINESSPROCESSINSTANCE] BPI on [IMPORTPROCESS].[ID] = BPI.[BUSINESSPROCESSPARAMETERSETID]
left join dbo.[SITE] on [SITE].[ID] = BPI.[SITEID]
outer apply (
select
top 1
[STARTEDON],
[STATUS],
[STARTEDBYUSERID],
[ENDEDON],
[NUMBERPROCESSED],
[BUSINESSPROCESSPARAMETERSETID]
from dbo.[BUSINESSPROCESSSTATUS]
where [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = BPI.[BUSINESSPROCESSPARAMETERSETID]
order by [STARTEDON] desc
) as [BPS]
left join dbo.[APPUSER] on [APPUSER].[ID] = [BPS].[STARTEDBYUSERID]
where
(@BATCHTEMPLATEID = '00000000-0000-0000-0000-000000000000' or @BATCHTEMPLATEID is null or [IMPORTPROCESS].[BATCHTEMPLATEID] = @BATCHTEMPLATEID)
and exists (--Check site security
select top 1 1
from dbo.[UFN_SITEID_MAPFROM_IMPORTPROCESSID]([IMPORTPROCESS].[ID]) as [IMPORTPROCESSSITE]
where exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) where [SITEID] = [IMPORTPROCESSSITE].[SITEID] or ([SITEID] is null and [IMPORTPROCESSSITE].[SITEID] is null))
)
and (--Apply site filter
@SITEFILTERMODE = 0
or BPI.[SITEID] in (
select [SITEID]
from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
);
insert into @IMPORTS ([ID], [NAME], [DESCRIPTION], [BATCHTEMPLATE], [BATCHTEMPLATEID], [DATECREATED], [SITES], [OWNERID], [USEWILDCARDS], [LASTRUNDURATION], [LASTRUNBY_USERNAME], [LASTRUNRESULTCOUNT], [LASTRUNSTATUS], [LASTRUNSTARTEDON])
select
[ID],
[NAME],
[DESCRIPTION],
[BATCHTEMPLATE],
[BATCHTEMPLATEID],
[DATECREATED],
[SITES],
[OWNERID],
[USEWILDCARDS],
[LASTRUNDURATION],
[LASTRUNBY_USERNAME],
[LASTRUNRESULTCOUNT],
[LASTRUNSTATUS],
[LASTRUNSTARTEDON]
from @ALLIMPORTS
where
(dbo.[UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [ID]) = 1)
end
return;
end