USP_MKTSEGMENTATION_GETSELECTIONS

CREATE procedure dbo.[USP_MKTSEGMENTATION_GETSELECTIONS]
(
  @SEGMENTATIONID uniqueidentifier = null
)
as
  set nocount on;

  create table #NOREFRESHSEGMENTIDS ([SEGMENTATIONSEGMENTID] uniqueidentifier) 
  declare @SEGMENTEDHOUSEFILETABLE nvarchar(256);
  declare @SEGMENTEDHOUSEFILEEXISTS bit;
  select @SEGMENTEDHOUSEFILEEXISTS = case when dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS](@SEGMENTATIONID) = 1 then 1 else 0 end;

  /* Create table of segmentationsegment IDs to exclude from the refresh process (segments that were used in segmented house file import) */
  if @SEGMENTEDHOUSEFILEEXISTS = 1
    begin
      declare @SQL nvarchar(max);
      set @SEGMENTEDHOUSEFILETABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);
      set @SQL = 'insert into #NOREFRESHSEGMENTIDS select distinct [SEGMENTATIONSEGMENTID] from dbo.[' + @SEGMENTEDHOUSEFILETABLE + '];'
      exec(@SQL);
    end

  select
    [MKTSEGMENT].[ID] as [SEGMENTID],
    [MKTSEGMENT].[NAME] as [SEGMENTNAME],
    (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],
    1 as [FORCEORDER]
  from dbo.[MKTSEGMENTATIONSEGMENT]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
  inner join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENT].[ID]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
  left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
  left join dbo.[MKTSEGMENTREFRESHPROCESS] on [MKTSEGMENTREFRESHPROCESS].[SEGMENTID] = [MKTSEGMENT].[ID]
  left join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left join dbo.[IDSETREGISTERDYNAMICIDSET] on [IDSETREGISTERDYNAMICIDSET].[STATICIDSETREGISTERID] = [IDSETREGISTER].[ID]
  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
  and [IDSETREGISTER].[STATIC] = 1
  and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or [MKTSEGMENTLIST].[PARENTSEGMENTID] is not null) -- exclude list segments, but not child list segments

  /* 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])
  /* not segmented house file segmented */
  and not exists(select 1
               from #NOREFRESHSEGMENTIDS
               where @SEGMENTEDHOUSEFILEEXISTS = 1 and [#NOREFRESHSEGMENTIDS].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID])

  union all

  select
    convert(uniqueidentifier, '00000000-0000-0000-0000-000000000000') as [SEGMENTID],
    coalesce([ADHOCQUERY].[NAME], [SMARTQUERYINSTANCE].[NAME], [IDSETREGISTER].[NAME]) + ' (' + [MKTSEGMENTATIONFILTERSELECTION].[FILTERTYPE] + ')' as [SEGMENTNAME],
    (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],
    convert(uniqueidentifier, '00000000-0000-0000-0000-000000000000') as [PARAMETERSETID],
    0 as [FORCEORDER]
  from dbo.[MKTSEGMENTATIONFILTERSELECTION]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTATIONFILTERSELECTION].[SELECTIONID]
  left join dbo.[MKTSEGMENTATIONSEGMENTREFRESHPROCESS] on [MKTSEGMENTATIONSEGMENTREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONFILTERSELECTION].[SEGMENTATIONID]
  left join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left join dbo.[IDSETREGISTERDYNAMICIDSET] on [IDSETREGISTERDYNAMICIDSET].[STATICIDSETREGISTERID] = [IDSETREGISTER].[ID]
  where [MKTSEGMENTATIONFILTERSELECTION].[SEGMENTATIONID] = @SEGMENTATIONID
  and [IDSETREGISTER].[STATIC] = 1

  order by [FORCEORDER], [SEGMENTID];

  return 0;