USP_MKTSEGMENTSREFRESH_GETSEGMENTSELECTIONS

CREATE procedure dbo.[USP_MKTSEGMENTSREFRESH_GETSEGMENTSELECTIONS]
(
  @SEGMENTID uniqueidentifier = null,
  @SEGMENTGROUPID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  select 
    [MKTSEGMENT].[ID],
    [MKTSEGMENT].[NAME],
    (case when [ADHOCQUERY].[ID] is null then case when [SMARTQUERYINSTANCE].[ID] is null then case when [IDSETREGISTERDYNAMICIDSET].[DYNAMICIDSETREGISTERID] is null then 3    else 4 end else 2 end else 1 end) as [QUERYTYPE],
    coalesce([ADHOCQUERY].[ID], [SMARTQUERYINSTANCE].[ID], [IDSETREGISTERDYNAMICIDSET].[DYNAMICIDSETREGISTERID], [IDSETREGISTER].[ID]) as [QUERYID],
    coalesce([ADHOCQUERY].[NAME], [SMARTQUERYINSTANCE].[NAME], [IDSETREGISTER].[NAME]) as [QUERYNAME],
    [MKTSEGMENTREFRESHPROCESS].[ID] as [PARAMETERSETID]
  from dbo.[MKTSEGMENT]
  inner join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
  left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left outer join dbo.[MKTSEGMENTREFRESHPROCESS] on [MKTSEGMENTREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left outer join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left outer join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left outer join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left outer join dbo.[IDSETREGISTERDYNAMICIDSET] on [IDSETREGISTERDYNAMICIDSET].[STATICIDSETREGISTERID] = [IDSETREGISTER].[ID]
  left outer join dbo.[MKTGROUPSEGMENTS] on [MKTGROUPSEGMENTS].[SEGMENTID] = [MKTSEGMENT].[ID]
  where [IDSETREGISTER].[STATIC] = 1
  and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[PARENTSEGMENTID] is not null) -- exclude list segments, but not child list segments

  and (@SEGMENTID is null or [MKTSEGMENT].[ID] = @SEGMENTID)
  and (@SEGMENTGROUPID is null or [MKTGROUPSEGMENTS].[SEGMENTGROUPID] = @SEGMENTGROUPID)
  and (@SEGMENTID is not null or @SEGMENTGROUPID is not null or -- check site security if this call is for refresh ALL segments

        (dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1 or exists (select top 1 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, 'F2E4036B-A903-47A2-A9D3-E0E106080596', 4) where [SITEID] = [MKTSEGMENT].[SITEID] or ([SITEID] is null and [MKTSEGMENT].[SITEID] is null))))
  -- exclude appeal mailing segments

  and not exists(select 1
                 from dbo.[APPEALMAILING]
                 inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
                 where [MKTSEGMENTATIONSEGMENT].[SEGMENTID] = [MKTSEGMENT].[ID])
  order by [MKTSEGMENT].[ID];

  return 0;