UFN_QUERY_SELECTIONS

CREATE function dbo.[UFN_QUERY_SELECTIONS]
(
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)

  returns @SELECTIONS table 
  (
    [ID] uniqueidentifier,
    [NAME] nvarchar(300),
    [SOURCEQUERY] nvarchar(255),
    [QUERYTYPE] nvarchar(6),
    [RECORDTYPEID] uniqueidentifier,
    [RECORDTYPE] nvarchar(50),
    [CATEGORY] nvarchar(100),
    [CATEGORYID] uniqueidentifier,
    [SELECTIONTYPE] nvarchar(10),
    [SHOWINQUERYDESIGNER] bit,
    [ISSTATIC] bit,
    [SOURCEQUERYID] uniqueidentifier,
    [ADHOCQUERYID] uniqueidentifier,
    [SMARTQUERYID] uniqueidentifier,
    [SITE] nvarchar(255),
    [DESCRIPTION] nvarchar(1024),
    [OWNERID] uniqueidentifier,
    [ACTIVE] bit,
    [CURRENTASOFDATE] datetime,
    [RECORDCOUNT] int
  )

  with execute as caller

  as

  begin


      declare @ISSYSADMIN bit;
      select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;

    insert into @SELECTIONS ([ID], [NAME], [SOURCEQUERY], [QUERYTYPE], [RECORDTYPEID], [RECORDTYPE], [CATEGORY], [CATEGORYID], [SELECTIONTYPE], [SHOWINQUERYDESIGNER], [ISSTATIC], [SOURCEQUERYID], [ADHOCQUERYID], [SMARTQUERYID], [SITE], [DESCRIPTION], [OWNERID], [ACTIVE], [CURRENTASOFDATE], [RECORDCOUNT])

        select
            IDSR.[ID],
            IDSR.[NAME],
            coalesce(AQ.[NAME], SQ.[NAME], '') as [SOURCEQUERY],
            (case 
                when (AQ.[ID] is not null) then 'Ad-hoc'
                when (SQ.[ID] is not null) then 'Smart'     
            else 
                'Other'
            end) as [QUERYTYPE],
        IDSR.[RECORDTYPEID],
            coalesce([RECORDTYPE].[NAME], '') as [RECORDTYPE],
            coalesce([QUERYCATEGORYCODE].[DESCRIPTION], '') as [CATEGORY],
        [QUERYCATEGORYCODE].[ID] as [CATEGORYID],
            (case IDSR.[STATIC] when 1 then 'Static' else 'Dynamic' end) as [SELECTIONTYPE],
            IDSR.[USEINQUERYDESIGNER] as [SHOWINQUERYDESIGNER],
            IDSR.[STATIC] as [ISSTATIC],
            isnull(AQ.[ID], SQ.[ID]) as [SOURCEQUERYID],
            AQ.[ID] as [ADHOCQUERYID],
            SQ.[ID] as [SMARTQUERYID],
        isnull([SITE].[NAME], 'All sites') as [SITE],        
            IDSR.[DESCRIPTION],
            IDSR.[OWNERID],
            IDSR.[ACTIVE],
        (case IDSR.[STATIC] when 1 then IDSR.[DATECHANGED] else null end) as [CURRENTASOFDATE],
        (case IDSR.[STATIC] when 1 then IDSR.[NUMROWS] else null end) as [RECORDCOUNT]
        from 
            dbo.[IDSETREGISTER] as IDSR
            -- Filter by installed products

            left join dbo.[SQLFUNCTIONCATALOG] on [SQLFUNCTIONCATALOG].[FUNCTIONNAME] = IDSR.[DBOBJECTNAME]
            --AD HOC QUERY JOINS

            left outer join dbo.[IDSETREGISTERADHOCQUERY] as IDSRA on IDSR.[ID] = IDSRA.[IDSETREGISTERID]
            left outer join dbo.[ADHOCQUERY] as AQ on IDSRA.[ADHOCQUERYID] = AQ.[ID]
            left outer join dbo.[QUERYVIEWCATALOG] as QVC on AQ.[QUERYVIEWCATALOGID] = QVC.[ID]
            --SMART QUERY JOINS

            left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] as IDSRS on IDSR.[ID] = IDSRS.[IDSETREGISTERID]
            left outer join dbo.[SMARTQUERYINSTANCE] as SQ on IDSRS.[SMARTQUERYINSTANCEID] = SQ.[ID]
            left outer join dbo.[SMARTQUERYCATALOG] as SQC on SQ.[SMARTQUERYCATALOGID] = SQC.[ID]   
            --Site join

            left outer join dbo.[SITE] on IDSR.[SITEID] = [SITE].[ID]
            left outer join dbo.[RECORDTYPE] on IDSR.[RECORDTYPEID] = [RECORDTYPE].[ID]
            left outer join dbo.[QUERYCATEGORYCODE] on coalesce(AQ.[QUERYCATEGORYCODEID], SQ.[QUERYCATEGORYCODEID]) = [QUERYCATEGORYCODE].[ID]
        where 
            ([SQLFUNCTIONCATALOG].[ID] is null or 
                dbo.[UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED] (
                [SQLFUNCTIONCATALOG].[SQLFUNCTIONSPECXML].query (
                      'declare namespace common="bb_appfx_commontypes";
                      /*/common:InstalledProductList'
                )
            ) = 1)
            and not exists(select [ID] from dbo.[IMPORTSELECTIONPROCESS] where [IDSETREGISTERID] = [IDSR].[ID])
            -- note that @CURRENTAPPUSERID is optional to maintain backwards compatibility

            and ((@ISSYSADMIN = 1) or (@CURRENTAPPUSERID is null) or (dbo.[UFN_SECURITY_APPUSER_GRANTED_SELECTION_IN_SYSTEMROLE](@CURRENTAPPUSERID, IDSR.[ID]) = 1))
            and
            (
                IDSR.[SITEID] is null 
                or
                (dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where [SITEID]=[IDSR].[SITEID] or ([SITEID] is null and [IDSR].[SITEID] is null)))
            )
            and 
            (
                @SITEFILTERMODE = 0
                or 
                IDSR.[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
            )
            and IDSR.[ISSYSTEM] = 0;

        return;

    end