USP_DATALIST_SELECTIONS

CREATE procedure dbo.[USP_DATALIST_SELECTIONS]
(
    @NAME nvarchar(100) = null, 
    @RECORDTYPEID uniqueidentifier = null,
    @ADHOC bit = 1,
    @SMART bit = 1,
    @OTHER bit = 1,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @INCLUDEINACTIVE bit = 0
)
as
    set nocount on;

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

    select
        IDSR.ID AS SELECTIONID,
        IDSR.[NAME] as [SELECTIONNAME],
        coalesce(AQ.[NAME], SQ.[NAME], '') as [SOURCEQUERYNAME],
        (case 
            when (AQ.ID is not null) then 'Ad-hoc'
            when (SQ.ID is not null) then 'Smart'     
        else 
            'Other'
        end) as [SOURCEQUERYTYPE],
        coalesce(RECORDTYPE.NAME, '') as RECORDTYPE,
        coalesce(QUERYCATEGORYCODE.DESCRIPTION, '') as CATEGORY,
        (case IDSR.STATIC when 1 then 'Static' else 'Dynamic' end) as [SELECTIONTYPE],
        IDSR.USEINQUERYDESIGNER as USEINQUERY,
        IDSR.STATIC as ISSTATIC,
        isnull(AQ.ID, SQ.ID) as [SOURCEQUERYID],
        AQ.ID as ADHOCQUERYID,
        SQ.ID as SMARTQUERYID,
        IDSR.DESCRIPTION,
        IDSR.OWNERID,
        isnull(SITE.NAME, 'All sites') as [SITE],        
        IDSR.ACTIVE,
        IDSR.DATECHANGED as CURRENTASOFDATE,
        IDSR.NUMROWS 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 ((@RECORDTYPEID is null) or (IDSR.RECORDTYPEID = @RECORDTYPEID))
        and ((@ADHOC = 1) or (AQ.ID is null))
        and ((@SMART = 1) or (SQ.ID is null))
        and ((@OTHER = 1) or (AQ.ID is not null or SQ.ID is not null))
        and ((@NAME is null or @NAME = '') or (IDSR.NAME like '%' + @NAME + '%'))
        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.ACTIVE = 1 or @INCLUDEINACTIVE = 1)
        and IDSR.ISSYSTEM = 0;

        return 0;