USP_QUERYVIEW_GETIDSETS

CREATE procedure dbo.USP_QUERYVIEW_GETIDSETS
(
  @OBJECTNAME nvarchar(128),
  @APPUSERID uniqueidentifier = null
)
with execute as caller
as
  set nocount on;

  declare @RECORDTYPEID uniqueidentifier;
  declare @MARTKEY nvarchar(255);
  select @RECORDTYPEID = RECORDTYPEID, @MARTKEY = MARTKEY from QUERYVIEWCATALOG where OBJECTNAME = @OBJECTNAME;

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

  if @MARTKEY is null 
  begin
    select     
      IDSETREGISTER.ID, 
      IDSETREGISTER.NAME, 
      IDSETREGISTER.DESCRIPTION, 
      IDSETREGISTER.DBOBJECTNAME,     
      IDSETREGISTER.OBJECTTYPE, 
      IDSETREGISTERADHOCQUERY.ADHOCQUERYID, 
      ADHOCQUERY.QUERYVIEWCATALOGID,      
      case 
        when IMPORTSELECTIONPROCESSCATEGORYCODE.DESCRIPTION is not null then
          IMPORTSELECTIONPROCESSCATEGORYCODE.DESCRIPTION
        when ADHOCQUERYCATEGORYCODE.DESCRIPTION is null then 
          SMARTQUERYINSTANCECATEGORYCODE.DESCRIPTION
        else ADHOCQUERYCATEGORYCODE.DESCRIPTION
      end as CATEGORY
    from dbo.IDSETREGISTER
    left join dbo.SQLFUNCTIONCATALOG on SQLFUNCTIONCATALOG.FUNCTIONNAME = IDSETREGISTER.DBOBJECTNAME
    left join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTERADHOCQUERY.IDSETREGISTERID = IDSETREGISTER.ID
    left join dbo.IMPORTSELECTIONPROCESS on IMPORTSELECTIONPROCESS.IDSETREGISTERID = IDSETREGISTER.ID
    left join dbo.ADHOCQUERY on IDSETREGISTERADHOCQUERY.ADHOCQUERYID = ADHOCQUERY.ID
    left join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
    left join dbo.SMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID
    left join dbo.QUERYCATEGORYCODE as IMPORTSELECTIONPROCESSCATEGORYCODE on IMPORTSELECTIONPROCESS.QUERYCATEGORYCODEID = IMPORTSELECTIONPROCESSCATEGORYCODE.ID
    left join dbo.QUERYCATEGORYCODE as ADHOCQUERYCATEGORYCODE on ADHOCQUERY.QUERYCATEGORYCODEID = ADHOCQUERYCATEGORYCODE.ID
    left join dbo.QUERYCATEGORYCODE as SMARTQUERYINSTANCECATEGORYCODE on SMARTQUERYINSTANCE.QUERYCATEGORYCODEID = SMARTQUERYINSTANCECATEGORYCODE.ID
    where (SQLFUNCTIONCATALOG.ID is null
        or dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED (
            SQLFUNCTIONCATALOG.SQLFUNCTIONSPECXML.query (
                'declare namespace common="bb_appfx_commontypes";
                /*/common:InstalledProductList'
            )
          ) = 1)
    and IDSETREGISTER.USEINQUERYDESIGNER = 1 
    and IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID
    and (@APPUSERID is null or @ISSYSADMIN = 1 or
          ((IDSETREGISTER.SITEID is null or IDSETREGISTER.SITEID in (select SITEID from dbo.UFN_SITESFORUSER(@APPUSERID)))
          and (dbo.UFN_SECURITY_APPUSER_GRANTED_SELECTION_IN_SYSTEMROLE(@APPUSERID, IDSETREGISTER.ID) = 1)));    
  end
  else
  begin

    declare @INCLUDESELECTIONS bit = (select INCLUDESELECTIONS from OLAPDATASOURCE where MARTKEY = @MARTKEY)

    select     
      IDSETREGISTER.ID, 
      IDSETREGISTER.NAME, 
      IDSETREGISTER.DESCRIPTION, 
      IDSETREGISTER.DBOBJECTNAME,     
      IDSETREGISTER.OBJECTTYPE, 
      IDSETREGISTERADHOCQUERY.ADHOCQUERYID, 
      ADHOCQUERY.QUERYVIEWCATALOGID,
      case 
        when IMPORTSELECTIONPROCESSCATEGORYCODE.DESCRIPTION is not null then
          IMPORTSELECTIONPROCESSCATEGORYCODE.DESCRIPTION
        when ADHOCQUERYCATEGORYCODE.DESCRIPTION is null then 
          SMARTQUERYINSTANCECATEGORYCODE.DESCRIPTION
        else ADHOCQUERYCATEGORYCODE.DESCRIPTION
      end as CATEGORY
    from dbo.IDSETREGISTER    
    left join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTERADHOCQUERY.IDSETREGISTERID = IDSETREGISTER.ID
    left join dbo.ADHOCQUERY on IDSETREGISTERADHOCQUERY.ADHOCQUERYID = ADHOCQUERY.ID
    left join dbo.QUERYCATEGORYCODE as ADHOCQUERYCATEGORYCODE on ADHOCQUERY.QUERYCATEGORYCODEID = ADHOCQUERYCATEGORYCODE.ID
    left join dbo.QUERYVIEWCATALOG on QUERYVIEWCATALOG.ID = ADHOCQUERY.QUERYVIEWCATALOGID
    left join dbo.SQLFUNCTIONCATALOG on SQLFUNCTIONCATALOG.FUNCTIONNAME = IDSETREGISTER.DBOBJECTNAME
    left join dbo.IMPORTSELECTIONPROCESS on IMPORTSELECTIONPROCESS.IDSETREGISTERID = IDSETREGISTER.ID
    left join dbo.QUERYCATEGORYCODE as IMPORTSELECTIONPROCESSCATEGORYCODE on IMPORTSELECTIONPROCESS.QUERYCATEGORYCODEID = IMPORTSELECTIONPROCESSCATEGORYCODE.ID
    left join dbo.IDSETREGISTERMERGE on IDSETREGISTERMERGE.IDSETREGISTERID = IDSETREGISTER.ID
    left join dbo.IDSETREGISTER as MERGESETREGISTER on IDSETREGISTERMERGE.SELECTION1ID = MERGESETREGISTER.ID
    left join dbo.IDSETREGISTERADHOCQUERY as MERGESETREGISTERQUERY on MERGESETREGISTERQUERY.IDSETREGISTERID = MERGESETREGISTER.ID
    left join dbo.ADHOCQUERY as MERGEQUERY on MERGESETREGISTERQUERY.ADHOCQUERYID = MERGEQUERY.ID
    left join dbo.QUERYVIEWCATALOG as MERGEQUERYVIEWCATALOG on MERGEQUERYVIEWCATALOG.ID = MERGEQUERY.QUERYVIEWCATALOGID
    left join dbo.IDSETREGISTERSMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID = IDSETREGISTER.ID
    left join dbo.SMARTQUERYINSTANCE on IDSETREGISTERSMARTQUERYINSTANCE.SMARTQUERYINSTANCEID = SMARTQUERYINSTANCE.ID
    left join dbo.QUERYCATEGORYCODE as SMARTQUERYINSTANCECATEGORYCODE on SMARTQUERYINSTANCE.QUERYCATEGORYCODEID = SMARTQUERYINSTANCECATEGORYCODE.ID
    left join dbo.SMARTQUERYCATALOG on SMARTQUERYCATALOG.ID = SMARTQUERYINSTANCE.SMARTQUERYCATALOGID    

    where 
      (SQLFUNCTIONCATALOG.ID is null
          or dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED (
              SQLFUNCTIONCATALOG.SQLFUNCTIONSPECXML.query (
                  'declare namespace common="bb_appfx_commontypes";
                  /*/common:InstalledProductList'
              )
            ) = 1)
      and IDSETREGISTER.USEINQUERYDESIGNER = 1 
      and (IDSETREGISTER.STATIC = 1 or IDSETREGISTERMERGE.IDSETREGISTERID is not null)
      and IDSETREGISTER.RECORDTYPEID = @RECORDTYPEID
      and (@APPUSERID is null or @ISSYSADMIN = 1 or
          ((IDSETREGISTER.SITEID is null or IDSETREGISTER.SITEID in (select SITEID from dbo.UFN_SITESFORUSER(@APPUSERID)))
          and (dbo.UFN_SECURITY_APPUSER_GRANTED_SELECTION_IN_SYSTEMROLE(@APPUSERID, IDSETREGISTER.ID) = 1)))
      and (IDSETREGISTERADHOCQUERY.ID is not null or IMPORTSELECTIONPROCESS.ID is not null or IDSETREGISTERMERGE.IDSETREGISTERID is not null or IDSETREGISTERSMARTQUERYINSTANCE.IDSETREGISTERID is not null)
      and (@INCLUDESELECTIONS = 1 or (QUERYVIEWCATALOG.MARTKEY = @MARTKEY or MERGEQUERYVIEWCATALOG.MARTKEY = @MARTKEY or SMARTQUERYCATALOG.MARTKEY = @MARTKEY));

end