USP_DATALIST_MKTSELECTION_MAILINGVIEW

CREATE procedure dbo.[USP_DATALIST_MKTSELECTION_MAILINGVIEW]
(
  @CURRENTAPPUSERID uniqueidentifier, 
  @RECORDTYPEID uniqueidentifier = null, 
  @QUERYCATEGORYCODEID uniqueidentifier = null, 
  @SHOWACTIVEMAILINGS bit = 0, 
  @MAILINGID uniqueidentifier = null
)
as
  set nocount on;

  if @RECORDTYPEID = '00000000-0000-0000-0000-000000000001' set @RECORDTYPEID = null;
  if @QUERYCATEGORYCODEID = '00000000-0000-0000-0000-000000000001' set @QUERYCATEGORYCODEID = null;
  if @SHOWACTIVEMAILINGS is null set @SHOWACTIVEMAILINGS = 0;
  if @MAILINGID = '00000000-0000-0000-0000-000000000001' set @MAILINGID = null;

  declare @ISSYSADMIN bit;

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

  -- filter out any ad-hoc queries involving query views to which the current user does not have rights

  if @ISSYSADMIN = 0 
    begin
      declare @OKVIEWS table (OBJECTNAME nvarchar(128));

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

  select distinct
    isnull([ADHOCQUERY].[ID], [SMARTQUERYINSTANCE].[ID]) as [ID],
    coalesce([ADHOCQUERY].[NAME], [SMARTQUERYINSTANCE].[NAME], [IDSETREGISTER].[NAME]) as [NAME],
    coalesce([ADHOCQUERY].[DESCRIPTION], [SMARTQUERYINSTANCE].[DESCRIPTION], [IDSETREGISTER].[DESCRIPTION]) as [DESCRIPTION],
    [MKTSEGMENTATION].[ID] as [MAILINGID],
    [MKTSEGMENTATION].[NAME] as [MAILING_NAME],
    [RECORDTYPE].[ID] as [RECORDTYPEID],
    [RECORDTYPE].[NAME] as [RECORDTYPE_NAME],
    [QUERYCATEGORYCODE].[ID] as [QUERYCATEGORYCODEID],
    [QUERYCATEGORYCODE].[DESCRIPTION] as [QUERYCATEGORYCODE_DESCRIPTION],
    [APPUSER].[USERNAME] as [OWNER],
    [APPUSER].[ID] as [OWNERID],
    coalesce([ADHOCQUERY].[OTHERSCANMODIFY], [SMARTQUERYINSTANCE].[OTHERSCANMODIFY], cast(0 as bit)) as [OTHERSCANMODIFY],
    coalesce([ADHOCQUERY].[DATEADDED], [SMARTQUERYINSTANCE].[DATEADDED], [IMPORTSELECTIONPROCESS].[DATEADDED], [IDSETREGISTER].[DATEADDED]) as [DATEADDED],
    [CHANGEAGENT].[USERNAME] as [ADDEDBY_USERNAME],
    (case when [ADHOCQUERY].[ID] is not null then 1 when [SMARTQUERYINSTANCE].[ID] is not null then 2 else 0 end) as [QUERYTYPECODE],
    (case when [ADHOCQUERY].[ID] is not null then 'Ad-hoc' when [SMARTQUERYINSTANCE].[ID] is not null then 'Smart' else 'Other' end) as [QUERYTYPE],
    cast((case when [SMARTQUERYINSTANCE].[ID] is null then 0 else 1 end) as bit) as [ISSMARTQUERY],
    --This field is duplicated below and named USERCANEDIT because we need it named differently so that the platform actions behave properly

    (case
       when @ISSYSADMIN = 1 then cast(1 as bit)
       when [ADHOCQUERY].[ID] is not null and dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE](@CURRENTAPPUSERID, [ADHOCQUERY].[ID]) = 1 then cast(1 as bit)
       when [SMARTQUERYINSTANCE].[ID] is not null and ([SMARTQUERYINSTANCE].[OTHERSCANMODIFY] = 1 or [APPUSER].[ID] = @CURRENTAPPUSERID) then cast(1 as bit)
       else cast(0 as bit)
     end) as [CANEDIT],
    [IDSETREGISTER].DATECHANGED as [CURRENTASOFDATE],
    [IDSETREGISTER].NUMROWS as [RECORDCOUNT],
    --This field is a duplicate of CANEDIT because we need it named differently so that the platform actions behave properly

    (case
       when @ISSYSADMIN = 1 then cast(1 as bit)
       when [ADHOCQUERY].[ID] is not null and dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE](@CURRENTAPPUSERID, [ADHOCQUERY].[ID]) = 1 then cast(1 as bit)
       when [SMARTQUERYINSTANCE].[ID] is not null and ([SMARTQUERYINSTANCE].[OTHERSCANMODIFY] = 1 or [APPUSER].[ID] = @CURRENTAPPUSERID) then cast(1 as bit)
 else cast(0 as bit)
     end) as [USERCANEDIT]
  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
  left join dbo.[MKTSEGMENTATIONFILTERSELECTION] on [MKTSEGMENTATIONFILTERSELECTION].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID] or [IDSETREGISTER].[ID] = [MKTSEGMENTATIONFILTERSELECTION].[SELECTIONID]
  left join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [ADHOCQUERY].[QUERYVIEWCATALOGID]
  left join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left join dbo.[SMARTQUERYCATALOG] on [SMARTQUERYCATALOG].[ID] = [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID]
  left join dbo.[IMPORTSELECTIONPROCESS] on [IMPORTSELECTIONPROCESS].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = coalesce([QUERYVIEWCATALOG].[RECORDTYPEID], [SMARTQUERYCATALOG].[RECORDTYPEID], [IDSETREGISTER].[RECORDTYPEID])
  left join dbo.[QUERYCATEGORYCODE] on [QUERYCATEGORYCODE].[ID] = coalesce([ADHOCQUERY].[QUERYCATEGORYCODEID], [SMARTQUERYINSTANCE].[QUERYCATEGORYCODEID], [IMPORTSELECTIONPROCESS].[QUERYCATEGORYCODEID])
  left join dbo.[APPUSER] on [APPUSER].[ID] = coalesce([ADHOCQUERY].[OWNERID], [SMARTQUERYINSTANCE].[OWNERID], [IMPORTSELECTIONPROCESS].[OWNERID], [IDSETREGISTER].[OWNERID])
  left join dbo.[CHANGEAGENT] on [CHANGEAGENT].[ID] = coalesce([ADHOCQUERY].[ADDEDBYID], [SMARTQUERYCATALOG].[ADDEDBYID], [IMPORTSELECTIONPROCESS].[ADDEDBYID], [IDSETREGISTER].[ADDEDBYID])
  where 
    -- filter out restricted query views

    (@ISSYSADMIN = 1 
     or 
     not exists (
      select [QUERYVIEWSINUSE].[OBJECTNAME] 
      from dbo.[ADHOCQUERY] as [ADHOCQUERYRIGHTSTEST]
      outer apply dbo.[UFN_ADHOCQUERY_QUERYVIEWSINUSE]([ADHOCQUERY].[QUERYDEFINITIONXML]) as [QUERYVIEWSINUSE]
      where not exists (
        select [OKVIEWS].[OBJECTNAME] 
        from @OKVIEWS as [OKVIEWS] 
        where [OKVIEWS].[OBJECTNAME] = [QUERYVIEWSINUSE].[OBJECTNAME])))
    -- end restricted query views filter

  and (@SHOWACTIVEMAILINGS = 1 or [MKTSEGMENTATION].[ACTIVE] = 0)
  and (@MAILINGID is null or [MKTSEGMENTATION].[ID] = @MAILINGID)
  and (@RECORDTYPEID is null or [RECORDTYPE].[ID] = @RECORDTYPEID)
  and (@QUERYCATEGORYCODEID is null or ([SMARTQUERYINSTANCE].[ID] is not null or [ADHOCQUERY].[QUERYCATEGORYCODEID] = @QUERYCATEGORYCODEID))
  and [MKTSEGMENTATION].[COMMUNICATIONTYPECODE] = 0 -- only return marketing efforts

  order by [NAME];

  return 0;