USP_DATALIST_SMARTQUERYINSTANCE

CREATE procedure dbo.USP_DATALIST_SMARTQUERYINSTANCE(
    @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,
    @ONLYSHOWMOBILIZED bit = 0)
with execute as caller
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;

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

    /*Filter out smart query instances involving smart queries to which the user does not have access*/
    if @ISSYSADMIN = 0
    begin

        declare @OKSMARTQUERIES table (SMARTQUERYCATALOGID uniqueidentifier);

        insert into @OKSMARTQUERIES (SMARTQUERYCATALOGID)
        select SMARTQUERYCATALOG.ID from dbo.SMARTQUERYCATALOG
        where exists (
            select [OKSMARTQUERIES].SMARTQUERYCATALOGID
            from dbo.UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER(@CURRENTAPPUSERID) as [OKSMARTQUERIES] where [OKSMARTQUERIES].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID);
    end;

    declare @PERMISSIONEDFOLDERS table
    (
        FOLDERID uniqueidentifier
    );

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

    with xmlnamespaces ('bb_appfx_smartquery' as [ns])
    select SMARTQUERYINSTANCE.ID,
        SMARTQUERYINSTANCE.NAME,
        SMARTQUERYINSTANCE.DESCRIPTION,
        SMARTQUERYCATALOG.RECORDTYPEID,
        (select T.c.value('@PrimaryKeyField', 'nvarchar(100)') from SMARTQUERYCATALOG.SMARTQUERYSPEC.nodes('ns:SmartQuerySpec') as T(c)) as [PRIMARYKEYFIELD],
        RECORDTYPE.NAME as [RECORDTYPE_NAME],
        SMARTQUERYINSTANCE.QUERYCATEGORYCODEID,
        QUERYCATEGORYCODE.DESCRIPTION as [QUERYCATEGORYCODE_DESCRIPTION],
        SMARTQUERYCATALOG.[NAME] AS [SMARTQUERYTEMPLATE_NAME],
        APPUSER.USERNAME as [OWNER],
        SMARTQUERYINSTANCE.OWNERID,
        SMARTQUERYINSTANCE.OTHERSCANMODIFY,
        SMARTQUERYINSTANCE.DATEADDED,
        CHANGEAGENT.USERNAME as [ADDEDBY_USERNAME],
        case (select coalesce(T.c.value('@PrimaryKeyField', 'nvarchar(100)'), '') from SMARTQUERYCATALOG.SMARTQUERYSPEC.nodes('ns:SmartQuerySpec') as T(c))
            when '' then convert(bit, 0)
            else convert(bit, 1) end as [ISBROWSABLE],
        coalesce(SITE.NAME, 'All sites') [SITE],
        SMARTQUERYINSTANCE.MOBILIZE
    from dbo.SMARTQUERYINSTANCE
    inner join dbo.SMARTQUERYCATALOG on SMARTQUERYINSTANCE.SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
    left join dbo.RECORDTYPE on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
    left join dbo.APPUSER on SMARTQUERYINSTANCE.OWNERID = APPUSER.ID
    left join dbo.QUERYCATEGORYCODE on SMARTQUERYINSTANCE.QUERYCATEGORYCODEID = QUERYCATEGORYCODE.ID
    left join dbo.CHANGEAGENT on SMARTQUERYINSTANCE.ADDEDBYID = CHANGEAGENT.ID
    left join dbo.SITE on SMARTQUERYINSTANCE.SITEID = SITE.ID
    where 
        /* Filter out restricted smart queries */
        (
            @ISSYSADMIN = 1
            /*This will return a list of smart queries in use by the smart query but not in the OK smart queries table variable;
              These smart queries should not be returned to the client.*/
            or exists
            (
                select [OKSMARTQUERIES].SMARTQUERYCATALOGID 
                from @OKSMARTQUERIES as [OKSMARTQUERIES] 
                where [OKSMARTQUERIES].SMARTQUERYCATALOGID = SMARTQUERYCATALOG.ID
            )
            and
            (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, SMARTQUERYINSTANCE.ID) = 1)
        )
        -- Ensure the folder the query belongs to is visible

        and
        (
            SMARTQUERYINSTANCE.FOLDERID is null
            or SMARTQUERYINSTANCE.FOLDERID in (select FOLDERID from @PERMISSIONEDFOLDERS)
        )
        and (@ONLYSHOWMOBILIZED = 0 or SMARTQUERYINSTANCE.MOBILIZE = 1)
        and ((@NAME is null or @NAME = '') or SMARTQUERYINSTANCE.NAME like '%' + @NAME + '%')
        and ((@ONLYSHOWMYQUERIES is null or @ONLYSHOWMYQUERIES = 0) or (@ONLYSHOWMYQUERIES = 1 and SMARTQUERYINSTANCE.OWNERID = @CURRENTAPPUSERID))
        and (@RECORDTYPEID is null or ((@RECORDTYPEIDNOTNULL is null and SMARTQUERYCATALOG.RECORDTYPEID is null) or (SMARTQUERYCATALOG.RECORDTYPEID = @RECORDTYPEIDNOTNULL)))
        and (@QUERYCATEGORYCODEID is null or ((@QUERYCATEGORYCODEIDNOTNULL is null and SMARTQUERYINSTANCE.QUERYCATEGORYCODEID is null) or (SMARTQUERYINSTANCE.QUERYCATEGORYCODEID = @QUERYCATEGORYCODEIDNOTNULL)))
        and
        (
            SMARTQUERYINSTANCE.SITEID is null 
            or
            (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 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))
        )