USP_DATALIST_MKTSELECTION_NORMALVIEW

CREATE procedure dbo.[USP_DATALIST_MKTSELECTION_NORMALVIEW]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @QUERYVIEWCATALOGID uniqueidentifier = null,
  @RECORDTYPEID uniqueidentifier = null,
  @ADHOC bit = 1,
  @SMART bit = 1,
  @OTHER bit = 1,
  @NAME nvarchar(100) = null, 
  @ONLYSHOWMYQUERIES bit = null,
  @QUERYCATEGORYCODEID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @ONLYSHOWMOBILIZED bit = 0,
  @SHOWINACTIVE bit = 1
)
as
  set nocount on;

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

  if @ADHOC is null
    set @ADHOC = 0;
  if @SMART is null
    set @SMART = 0;
  if @OTHER is null
    set @OTHER = 0;
  if @ONLYSHOWMYQUERIES is null
    set @ONLYSHOWMYQUERIES = 0;
  if @ONLYSHOWMOBILIZED is null
    set @ONLYSHOWMOBILIZED = 0;

  declare @QUERYCATEGORYCODEIDNOTNULL as uniqueidentifier;
  if @QUERYCATEGORYCODEID = '00000000-0000-0000-0000-000000000001'
    set @QUERYCATEGORYCODEIDNOTNULL = null;
  else
    set @QUERYCATEGORYCODEIDNOTNULL = @QUERYCATEGORYCODEID;

  set @NAME = '%' + isnull(@NAME, '') + '%';

  declare @QUERYVIEWSINUSE table ([ID] uniqueidentifier, [OBJECTNAME] nvarchar(128));
  if @ISSYSADMIN = 0
  begin
    declare @OKVIEWS table (OBJECTNAME nvarchar(128));
  -- filter out any ad-hoc queries involving query views to which the current user does not have rights

    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]);

    insert into @QUERYVIEWSINUSE(ID, OBJECTNAME)
    select distinct [ADHOCQUERY].[ID], [QUERYVIEWSINUSE].[OBJECTNAME]
    from
      dbo.[ADHOCQUERY]
      outer apply dbo.[UFN_ADHOCQUERY_QUERYVIEWSINUSE]([ADHOCQUERY].[QUERYDEFINITIONXML]) as [QUERYVIEWSINUSE]
    where 
      (@ADHOC = 1 or [ADHOCQUERY].[ID] is null)
      and (@ONLYSHOWMOBILIZED = 0 or [ADHOCQUERY].[MOBILIZE] = 1)
      and ((@ONLYSHOWMYQUERIES is null or @ONLYSHOWMYQUERIES = 0) or (@ONLYSHOWMYQUERIES = 1 and [ADHOCQUERY].[OWNERID] = @CURRENTAPPUSERID))
      and (@QUERYCATEGORYCODEID is null or ((@QUERYCATEGORYCODEIDNOTNULL is null and [ADHOCQUERY].[QUERYCATEGORYCODEID] is null) or ([ADHOCQUERY].[QUERYCATEGORYCODEID] = @QUERYCATEGORYCODEIDNOTNULL)))
      and (@SITEFILTERMODE = 0 or [ADHOCQUERY].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)));
  end

  declare @SELECTIONTYPE bit; -- this needs to be a parameter at some point

  set @SELECTIONTYPE = 1;     -- 1 = static, 0 = dynamic


  with [AVAILABLERECORDTYPES] ([ID]) as
  (
    select [ID] from dbo.[UFN_MKTRECORDSOURCE_GETRECORDTYPES](null, @RECORDTYPEID, null)
    union all
    select [ID] from dbo.[UFN_MKTGIFTRECORDSOURCE_GETRECORDTYPES](null, @RECORDTYPEID)
    union all
    select [ID] from dbo.[UFN_MKTMEMBERSHIPRECORDSOURCE_GETRECORDTYPES](null, @RECORDTYPEID)
    union all
    select [ID] from dbo.[UFN_MKTSPONSORSHIPRECORDSOURCE_GETRECORDTYPES](null, @RECORDTYPEID)
  )
  select 
    isnull((case when [AQ].[ID] is not null then (case when [QVC].[ROOTOBJECT] = 1 then [AQ].[ID] else null end) else null end), [SQ].[ID]) as [ID],
    coalesce([SQ].[NAME], [AQ].[NAME], [IDSR].[NAME]) as [NAME],
    coalesce([AQ].[NAME], [SQ].[NAME], '') as [SOURCEQUERY],
    (case when ([AQ].[ID] is not null) then 1 when ([SQ].[ID] is not null) then 2 else 0 end) as [QUERYTYPECODE],
    (case when ([AQ].[ID] is not null) then 'Ad-hoc' when ([SQ].[ID] is not null) then 'Smart' else 'Other' end) as [QUERYTYPE],
    isnull((select [NAME] from dbo.[RECORDTYPE] where [ID] = [IDSR].RECORDTYPEID), '') as [RECORDTYPEID],
    isnull([QUERYCATEGORYCODE].[DESCRIPTION], '') as [CATEGORY],
    (case [IDSR].[STATIC] when 1 then 'Static' else 'Dynamic' end) as [SELECTIONTYPE],
    [AQ].[ID] as [ADHOCQUERYID],
    [SQ].[ID] as [SMARTQUERYID],
    [IDSR].[DESCRIPTION] as [DESCRIPTION],
    [APPUSER].[USERNAME] as [OWNER],
    [APPUSER].[ID] as [OWNERID],
    isnull([AQ].[OTHERSCANMODIFY], [SQ].[OTHERSCANMODIFY]) as [OTHERSCANMODIFY],
    coalesce([AQ].[DATEADDED], [SQ].[DATEADDED], [IMPORTSELECTIONPROCESS].[DATEADDED], [IDSR].[DATEADDED]) as [DATEADDED],
    [CHANGEAGENT].[USERNAME] as [ADDEDBYUSER],
    cast((case when [SQ].[ID] is null then 0 else 1 end) as bit) as [ISSMARTQUERY],
    [IDSR].[ID] as [IDSETREGISTERID],
    (case 
       when @ISSYSADMIN = 1 then cast(1 as bit)
       when [AQ].[ID] is not null and dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE](@CURRENTAPPUSERID, [AQ].[ID]) = 1 then cast(1 as bit)
       when [SQ].[ID] is not null and ([SQ].[OTHERSCANMODIFY] = 1 or [APPUSER].[ID] = @CURRENTAPPUSERID) then cast(1 as bit)
       else cast(0 as bit)
     end) as [USERCANEDIT],
     [IDSR].DATECHANGED as [CURRENTASOFDATE],
     [IDSR].NUMROWS as [RECORDCOUNT]
  from dbo.IDSETREGISTER as [IDSR]
  -- 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]
  -- imported selection joins

  left outer join dbo.[IMPORTSELECTIONPROCESS] on [IMPORTSELECTIONPROCESS].[IDSETREGISTERID] = [IDSR].[ID]
  -- category join

  left outer join dbo.[QUERYCATEGORYCODE] on [QUERYCATEGORYCODE].[ID] = coalesce([AQ].[QUERYCATEGORYCODEID], [SQ].[QUERYCATEGORYCODEID], [IMPORTSELECTIONPROCESS].[QUERYCATEGORYCODEID])
  -- user info

  left outer join dbo.[APPUSER] on [APPUSER].[ID] = coalesce([AQ].[OWNERID], [SQ].[OWNERID], [IMPORTSELECTIONPROCESS].[OWNERID], [IDSR].[OWNERID])
  left outer join dbo.[CHANGEAGENT] on [CHANGEAGENT].[ID] = coalesce([AQ].[ADDEDBYID], [SQ].[ADDEDBYID], [IMPORTSELECTIONPROCESS].[ADDEDBYID], [IDSR].[ADDEDBYID])
  where
    -- filter out restricted query views

    (@ISSYSADMIN = 1 
     or
     ([AQ].[ID] is null)
     or 
     not exists (
      select [QUERYVIEWSINUSE].[OBJECTNAME] 
      from dbo.[ADHOCQUERY] as [ADHOCQUERYRIGHTSTEST]
      inner join @QUERYVIEWSINUSE [QUERYVIEWSINUSE] on [QUERYVIEWSINUSE].[ID] = [ADHOCQUERYRIGHTSTEST].[ID] and [QUERYVIEWSINUSE].[ID] = [AQ].[ID]
      where not exists (
        select [OKVIEWS].[OBJECTNAME] 
        from @OKVIEWS as [OKVIEWS] 
        where [OKVIEWS].[OBJECTNAME] = [QUERYVIEWSINUSE].[OBJECTNAME])))
    -- end restricted query views filter

  and [IDSR].[ISSYSTEM] = 0
  and [IDSR].[RECORDTYPEID] in (select [ID] from [AVAILABLERECORDTYPES])
  and [IDSR].[ID] not in (select [IDSETREGISTERID] from dbo.[MKTSEGMENT] where [IDSETREGISTERID] is not null)
  and (@QUERYVIEWCATALOGID is null or (@ADHOC = 1 and [QVC].[ID] = @QUERYVIEWCATALOGID) or ((@SMART = 1 or @OTHER = 1) and [IDSR].[RECORDTYPEID] = (select [RECORDTYPEID] from dbo.[QUERYVIEWCATALOG] where [ID] = @QUERYVIEWCATALOGID)))
  and ([QVC].[ID] is null or [QVC].[ROOTOBJECT] = 1)
  and (@SELECTIONTYPE is null or [IDSR].[STATIC] = @SELECTIONTYPE)
  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 ([IDSR].[NAME] like @NAME)
  and (@ONLYSHOWMOBILIZED = 0 or [AQ].[MOBILIZE] = 1)
  and ((@ONLYSHOWMYQUERIES is null or @ONLYSHOWMYQUERIES = 0) or (@ONLYSHOWMYQUERIES = 1 and [AQ].[OWNERID] = @CURRENTAPPUSERID))
  and (@QUERYCATEGORYCODEID is null or ((@QUERYCATEGORYCODEIDNOTNULL is null and [AQ].[QUERYCATEGORYCODEID] is null) or ([AQ].[QUERYCATEGORYCODEID] = @QUERYCATEGORYCODEIDNOTNULL)))
  and (@SITEFILTERMODE = 0 or [AQ].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  and (@SHOWINACTIVE = 1 or [IDSR].[ACTIVE] = 1)
  order by [NAME];

  return 0;