USP_MKTSEGMENTATIONEXPORTPROCESS_GETEXPORTDEFINITIONS

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXPORTPROCESS_GETEXPORTDEFINITIONS]
(
  @SEGMENTATIONID uniqueidentifier,
  @PREVIEWEXPORTDEFINITIONID uniqueidentifier = null,
  @PREVIEWDEFAULTMAILEXPORTDEFINITION bit = 0,
  @PREVIEWDEFAULTEMAILEXPORTDEFINITION bit = 0,
  @PREVIEWDEFAULTPHONEEXPORTDEFINITION bit = 0
)
as
  set nocount on;

  if @PREVIEWEXPORTDEFINITIONID is null
    begin
      set @PREVIEWDEFAULTMAILEXPORTDEFINITION = 0;
      set @PREVIEWDEFAULTEMAILEXPORTDEFINITION = 0;
      set @PREVIEWDEFAULTPHONEEXPORTDEFINITION = 0;
    end;

  with [EXPORTDEFINITIONS] ([EXPORTDEFINITIONID]) as (
    select distinct
      case [MKTPACKAGE].[CHANNELCODE]
        when 0 then case when @PREVIEWDEFAULTMAILEXPORTDEFINITION = 1 then @PREVIEWEXPORTDEFINITIONID
                    else coalesce([MAILEXPORTDEFINITION].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[MAILEXPORTDEFINITIONID]) end
        when 1 then case when @PREVIEWDEFAULTEMAILEXPORTDEFINITION = 1 then @PREVIEWEXPORTDEFINITIONID
                    else coalesce([EMAILEXPORTDEFINITION].[ID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[EMAILEXPORTDEFINITIONID]) end
        when 2 then case when @PREVIEWDEFAULTPHONEEXPORTDEFINITION = 1 then @PREVIEWEXPORTDEFINITIONID
                    else coalesce([MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[PHONEEXPORTDEFINITIONID]) end
      end as [EXPORTDEFINITIONID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID]
    left outer join dbo.[LETTERCODE] as [MAILEXPORTDEFINITION] on [MAILEXPORTDEFINITION].[ID] = [MKTPACKAGE].[LETTERCODEID]
    left outer join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
    left outer join dbo.[EXPORTDEFINITION] as [EMAILEXPORTDEFINITION] on [EMAILEXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1) -- exclude vendor-managed segments


    union

    select distinct
      case [MKTPACKAGE].[CHANNELCODE]
        when 0 then case when @PREVIEWDEFAULTMAILEXPORTDEFINITION = 1 then @PREVIEWEXPORTDEFINITIONID
                    else coalesce([MAILEXPORTDEFINITION].[EXPORTDEFINITIONID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[MAILEXPORTDEFINITIONID]) end
        when 1 then case when @PREVIEWDEFAULTEMAILEXPORTDEFINITION = 1 then @PREVIEWEXPORTDEFINITIONID
                    else coalesce([EMAILEXPORTDEFINITION].[ID], [MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[EMAILEXPORTDEFINITIONID]) end
        when 2 then case when @PREVIEWDEFAULTPHONEEXPORTDEFINITION = 1 then @PREVIEWEXPORTDEFINITIONID
                    else coalesce([MKTPACKAGE].[EXPORTDEFINITIONID], [MKTSEGMENTATIONEXPORTPROCESS].[PHONEEXPORTDEFINITIONID]) end
      end as [EXPORTDEFINITIONID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
    inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left outer join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
    left outer join dbo.[LETTERCODE] as [MAILEXPORTDEFINITION] on [MAILEXPORTDEFINITION].[ID] = [MKTPACKAGE].[LETTERCODEID]
    left outer join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
    left outer join dbo.[EXPORTDEFINITION] as [EMAILEXPORTDEFINITION] on [EMAILEXPORTDEFINITION].[ID] = [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID]
    where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID
    and ([MKTSEGMENTLIST].[TYPECODE] is null or [MKTSEGMENTLIST].[TYPECODE] <> 1)) -- exclude vendor-managed segments

  select
    [EXPORTDEFINITIONS].[EXPORTDEFINITIONID],
    [EXPORTDEFINITION].[NAME] as [EXPORTDEFINITIONNAME]
  from [EXPORTDEFINITIONS]
  inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [EXPORTDEFINITIONS].[EXPORTDEFINITIONID]
  where (@PREVIEWEXPORTDEFINITIONID is null or [EXPORTDEFINITIONS].[EXPORTDEFINITIONID] = @PREVIEWEXPORTDEFINITIONID);

  return 0;