USP_DATALIST_ADHOCQUERY

CREATE procedure dbo.USP_DATALIST_ADHOCQUERY(
    @CURRENTAPPUSERID uniqueidentifier, 
    @NAME nvarchar(100) = null, 
    @ONLYSHOWMYQUERIES bit = null,
    @RECORDTYPEID uniqueidentifier = null, 
    @QUERYCATEGORYCODEID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @INCLUDEFOLDERS bit = 0,
    @ONLYSHOWMOBILIZED bit = 0
    )
as
    set nocount on;

declare @QUERYCATEGORYCODEIDNOTNULL as uniqueidentifier;

declare @RECORDTYPEIDNOTNULL as uniqueidentifier;

if @RECORDTYPEID = '00000000-0000-0000-0000-000000000001'
begin
    set @RECORDTYPEIDNOTNULL = null;
end
else
begin
    set @RECORDTYPEIDNOTNULL = @RECORDTYPEID;
end;

if @QUERYCATEGORYCODEID = '00000000-0000-0000-0000-000000000001'
begin
    set @QUERYCATEGORYCODEIDNOTNULL = null;
end
else
begin
    set @QUERYCATEGORYCODEIDNOTNULL = @QUERYCATEGORYCODEID;
end;

declare @ISSYSADMIN bit;
declare @HASQUERYVIEWSYSTEMPRIVILEGE bit = 0;

select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID;

/* Filter out any ad-hoc queries involving query views to which the current user does not have rights */
if @ISSYSADMIN = 0 
begin
    select @HASQUERYVIEWSYSTEMPRIVILEGE = dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f');


    if (@HASQUERYVIEWSYSTEMPRIVILEGE = 0)
    begin
        declare @OKVIEWS table (OBJECTNAME nvarchar(128));

        insert into @OKVIEWS (OBJECTNAME)
        select QUERYVIEWCATALOG.OBJECTNAME from dbo.QUERYVIEWCATALOG
        where exists (
            select [OKVIEWS].QUERYVIEWCATALOGID 
            from dbo.UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER(@CURRENTAPPUSERID) as [OKVIEWS] where [OKVIEWS].QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID);
    end
end;

declare @PERMISSIONEDFOLDERS table
(
    FOLDERID uniqueidentifier
)

insert into @PERMISSIONEDFOLDERS (FOLDERID)
select ID
from dbo.UFN_ADHOCQUERYFOLDERS_GETUSERSFOLDERS(@CURRENTAPPUSERID)

declare @QUERIESTOINCLUDE table
(
    QUERYID uniqueidentifier,
    FOLDERID uniqueidentifier
)

declare @HASCRITERIA bit
set @HASCRITERIA = 0
if rtrim(ltrim(coalesce(@NAME, ''))) <> '' or coalesce(@SITEFILTERMODE, 0) <> 0 or @RECORDTYPEID is not null or @QUERYCATEGORYCODEID is not null or coalesce(@ONLYSHOWMYQUERIES, 0) <> 0 or @ONLYSHOWMOBILIZED <> 0
    set @HASCRITERIA = 1

insert into @QUERIESTOINCLUDE (QUERYID, FOLDERID)
select
    ADHOCQUERY.ID,
    ADHOCQUERY.FOLDERID
from dbo.ADHOCQUERY
inner join dbo.QUERYVIEWCATALOG on ADHOCQUERY.QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
where 
    /* Filter out restricted query views */
    (
        (
            @ISSYSADMIN = 1 
            or 
            (
                @HASQUERYVIEWSYSTEMPRIVILEGE = 1 
                and 
                (dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, ADHOCQUERY.ID) = 1)
            )
        )
        /* This will return a list of query views in use by the ad-hoc query but not in the OK views table variable; 
        These ad-hoc queries should not be returned to the client. */
        or 
        (
            not exists
            (
                select [QUERYVIEWSINUSE].OBJECTNAME 
                from dbo.ADHOCQUERY as [ADHOCQUERYRIGHTSTEST]
                outer apply dbo.UFN_ADHOCQUERY_QUERYVIEWSINUSE(ADHOCQUERYRIGHTSTEST.QUERYDEFINITIONXML) as [QUERYVIEWSINUSE]
                where 
                    [ADHOCQUERYRIGHTSTEST].ID = ADHOCQUERY.ID and
                    not exists 
                    (
                        select [OKVIEWS].OBJECTNAME 
                        from @OKVIEWS as [OKVIEWS] where [OKVIEWS].OBJECTNAME = [QUERYVIEWSINUSE].OBJECTNAME
                    )
            )
        )
        and
        (dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, ADHOCQUERY.ID) = 1)
    )    
    -- Ensure the folder the query belongs to is visible

    and 
    (
        ADHOCQUERY.FOLDERID is null
        or ADHOCQUERY.FOLDERID in (select FOLDERID from @PERMISSIONEDFOLDERS)
    )
    /* End restricted query views filter */
    and (@ONLYSHOWMOBILIZED = 0 or ADHOCQUERY.MOBILIZE = 1)
    and ((@NAME is null or @NAME = '') or ADHOCQUERY.NAME like '%' + @NAME + '%')
    and ((@ONLYSHOWMYQUERIES is null or @ONLYSHOWMYQUERIES = 0) or (@ONLYSHOWMYQUERIES = 1 and ADHOCQUERY.OWNERID = @CURRENTAPPUSERID))
    and (@RECORDTYPEID is null or ((@RECORDTYPEIDNOTNULL is null and QUERYVIEWCATALOG.RECORDTYPEID is null) or (QUERYVIEWCATALOG.RECORDTYPEID = @RECORDTYPEIDNOTNULL))) 
    and (@QUERYCATEGORYCODEID is null or ((@QUERYCATEGORYCODEIDNOTNULL is null and ADHOCQUERY.QUERYCATEGORYCODEID is null) or (ADHOCQUERY.QUERYCATEGORYCODEID = @QUERYCATEGORYCODEIDNOTNULL)))
    and QUERYVIEWCATALOG.ROOTOBJECT = 1
    and
    (
        ADHOCQUERY.SITEID is null 
        or
        (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[ADHOCQUERY].[SITEID] or (SITEID is null and [ADHOCQUERY].[SITEID] is null)))
    )
    and 
    (
        @SITEFILTERMODE = 0
        or 
        ADHOCQUERY.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))        
    )

declare @QUERYANCESTORS table
(
    QUERYID uniqueidentifier,
    ANCESTORFOLDERID uniqueidentifier
)

insert into @QUERYANCESTORS (QUERYID, ANCESTORFOLDERID)
select
    QUERIESTOINCLUDE.QUERYID,
    ANCESTORS.ID
from @QUERIESTOINCLUDE QUERIESTOINCLUDE
cross apply dbo.UFN_ADHOCQUERY_GETANCESTORS(QUERIESTOINCLUDE.FOLDERID) as ANCESTORS

select ADHOCQUERY.ID,
    ADHOCQUERY.NAME,
    ADHOCQUERY.DESCRIPTION,
    QUERYVIEWCATALOG.RECORDTYPEID,
    RECORDTYPE.NAME [RECORDTYPE_NAME],
    ADHOCQUERY.QUERYCATEGORYCODEID,
    QUERYCATEGORYCODE.DESCRIPTION [QUERYCATEGORYCODE_DESCRIPTION],
    QUERYVIEWCATALOG.DISPLAYNAME [QUERYVIEWCATALOG_NAME],
    case when APPUSER.DISPLAYNAME = ''
        then APPUSER.USERNAME
        else APPUSER.DISPLAYNAME
    end [OWNER],
    ADHOCQUERY.OWNERID,
    ADHOCQUERY.OTHERSCANMODIFY,
    ADHOCQUERY.DATEADDED,
    CHANGEAGENT.USERNAME [ADDEDBY_USERNAME],
    ADHOCQUERY.ISBROWSABLE,
    coalesce(SITE.NAME, 'All sites') [SITE],
    cast(1 as bit) as ISQUERY,
    ADHOCQUERY.FOLDERID as PARENTFOLDERID,
    'RES:newitem',
    ADHOCQUERY.DISPLAYORDER,
    case 
        when DISPLAYORDER = 0 then 1 
        else 0 
    end as DISPLAYORDERNOTSET,
    ADHOCQUERY.MOBILIZE,
    case 
        when @ISSYSADMIN = 1 then cast(1 as bit)
        when dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE(@CURRENTAPPUSERID, ADHOCQUERY.ID) = 1 then cast(1 as bit)
        else cast(0 as bit)
    end as USERCANEDIT
from @QUERIESTOINCLUDE as QUERIESTOINCLUDE
inner join dbo.ADHOCQUERY on ADHOCQUERY.ID = QUERIESTOINCLUDE.QUERYID
inner join dbo.QUERYVIEWCATALOG on ADHOCQUERY.QUERYVIEWCATALOGID = QUERYVIEWCATALOG.ID
left join dbo.RECORDTYPE on QUERYVIEWCATALOG.RECORDTYPEID = RECORDTYPE.ID
left join dbo.APPUSER on ADHOCQUERY.OWNERID = dbo.APPUSER.ID
left join dbo.QUERYCATEGORYCODE on ADHOCQUERY.QUERYCATEGORYCODEID = QUERYCATEGORYCODE.ID
left join dbo.CHANGEAGENT on ADHOCQUERY.ADDEDBYID = CHANGEAGENT.ID
left join dbo.SITE on ADHOCQUERY.SITEID = SITE.ID


union all

select ADHOCQUERYFOLDER.ID,
    ADHOCQUERYFOLDER.NAME + ' (' + cast((select count(*) from @QUERYANCESTORS as QUERYANCESTORS where QUERYANCESTORS.ANCESTORFOLDERID = ADHOCQUERYFOLDER.ID) as nvarchar(10)) + ')',
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    cast(1 as bit) as OTHERSCANMODIFY,
    null,
    null,
    null,
    null,
    cast(0 as bit) as ISQUERY,
    ADHOCQUERYFOLDER.PARENTFOLDERID,
    'RES:folder_closed',
    ADHOCQUERYFOLDER.DISPLAYORDER,
    case 
        when DISPLAYORDER = 0 then 1 
        else 0 
    end as DISPLAYORDERNOTSET,
    null,
    cast(0 as bit) as USERCANEDIT
from @PERMISSIONEDFOLDERS PERMISSIONEDFOLDERS
inner join dbo.ADHOCQUERYFOLDER on PERMISSIONEDFOLDERS.FOLDERID = ADHOCQUERYFOLDER.ID
where
    @INCLUDEFOLDERS = 1 and
    (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYFOLDER_IN_SYSTEMROLE(@CURRENTAPPUSERID, ADHOCQUERYFOLDER.ID) = 1) and
    (
        @HASCRITERIA = 0 or
        -- Ensure that there are visible queries under this folder

        exists
        (
            select 1 from 
            @QUERYANCESTORS as QUERYANCESTORS 
            where 
                QUERYANCESTORS.ANCESTORFOLDERID = ADHOCQUERYFOLDER.ID
        )
    )
order by 
    -- This order statement is to ensure that rows with a blank DISPLAYORDER appear

    -- after rows that have a set DISPLAYORDER

    DISPLAYORDERNOTSET,
    DISPLAYORDER, 
    NAME;