USP_MKTSEGMENTATIONEXCLUSION_ADDTOEXCLUSIONREPORT

CREATE procedure dbo.[USP_MKTSEGMENTATIONEXCLUSION_ADDTOEXCLUSIONREPORT]
(
  @SEGMENTATIONEXCLUSIONID uniqueidentifier,
  @TARGETEXCLUSIONTABLE nvarchar(128),
  @TEMPREMOVEDMEMBERSTABLE nvarchar(128)
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @SOURCEEXCLUSIONTABLE nvarchar(128);
  declare @EXCLUSIONTABLE nvarchar(128);
  declare @SQL nvarchar(max);
  declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

  begin try
    select
      @SEGMENTATIONID = [SEGMENTATIONID]
    from dbo.[MKTSEGMENTATIONEXCLUSION]
    where [ID] = @SEGMENTATIONEXCLUSIONID;

    -- determine the name of the table in which members that have been removed are stored

    set @EXCLUSIONTABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@SEGMENTATIONID);
    set @SOURCEEXCLUSIONTABLE = null;

    -- we're going to make a copy of the most recent exclusion report table...

    select
      @SOURCEEXCLUSIONTABLE = [TABLENAME]
    from dbo.[BUSINESSPROCESSOUTPUT]
    where [TABLEKEY] = 'EFFORTEXCLUSIONS'
    and [BUSINESSPROCESSSTATUSID] = (
      select top 1 [MKTUPDATEMAILINGCOUNTSPROCESSSTATUS].[ID]
      from dbo.[MKTUPDATEMAILINGCOUNTSPROCESSSTATUS]
      inner join dbo.[MKTUPDATEMAILINGCOUNTSPROCESS] on [MKTUPDATEMAILINGCOUNTSPROCESS].[ID] = [MKTUPDATEMAILINGCOUNTSPROCESSSTATUS].[PARAMETERSETID]
      inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTUPDATEMAILINGCOUNTSPROCESSSTATUS].[ID]
      inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[BUSINESSPROCESSSTATUSID] = [BUSINESSPROCESSSTATUS].[ID]
      where [MKTUPDATEMAILINGCOUNTSPROCESS].[SEGMENTATIONID] = @SEGMENTATIONID
      and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0
      order by [BUSINESSPROCESSSTATUS].[STARTEDON] desc);

    if @SOURCEEXCLUSIONTABLE is null
      select
        @SOURCEEXCLUSIONTABLE = [TABLENAME]
      from dbo.[BUSINESSPROCESSOUTPUT]
      where [TABLEKEY] = 'EFFORTEXCLUSIONS'
      and [BUSINESSPROCESSSTATUSID] = (
        select top 1 [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
        from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS]
        inner join dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS] on [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[PARAMETERSETID]
        inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS].[ID]
        where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = @SEGMENTATIONID
        and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0
        order by [BUSINESSPROCESSSTATUS].[STARTEDON] desc);

    -- ...and have it be the output of this remove members process

    set @SQL = 'insert into dbo.[' + @TARGETEXCLUSIONTABLE + '] ([DONORID], [DONORNAME], [EXCLUDEDFROMSEGMENT], [INCLUDEDINSEGMENT], [EXCLUSIONREASON], [EXCLUSIONTYPE], [EXCLUSIONTYPECODE])' + char(13) +
               '  select [DONORID], [DONORNAME], [EXCLUDEDFROMSEGMENT], [INCLUDEDINSEGMENT], [EXCLUSIONREASON], [EXCLUSIONTYPE], [EXCLUSIONTYPECODE] from dbo.[' + @SOURCEEXCLUSIONTABLE + ']';
    exec (@SQL);

    -- we're going to add the removed members from this run of the remove members process to the new

    -- exclusion report table

    set @SQL = 'insert into [' + @TEMPREMOVEDMEMBERSTABLE + '] ([DONORID], [SEGMENTID], [SEGMENTNAME], [QUERYVIEWCATALOGID])' + char(13) +
               '  select [EX].[DONORID], [MKTSEGMENT].[ID], case when [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1 then ''Exclude: '' else '''' end + [MKTSEGMENT].[NAME], [EX].[DONORQUERYVIEWCATALOGID]' + char(13) +
               '  from dbo.[' + @EXCLUSIONTABLE + '] as [EX]' + char(13) +
               '  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [EX].[SEGMENTID]' + char(13) +
               '  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
               '  where [EX].[SEGMENTATIONEXCLUSIONID] = @SEGMENTATIONEXCLUSIONID';
    exec sp_executesql @SQL, N'@SEGMENTATIONEXCLUSIONID uniqueidentifier', @SEGMENTATIONEXCLUSIONID = @SEGMENTATIONEXCLUSIONID;

    -- (this will populate the [NAME] field in the temp table and create indexes on it)

    exec dbo.[USP_MKTSEGMENTATION_POPULATEEXCLUDEDIDSTEMPTABLE] @SEGMENTATIONID, @TEMPREMOVEDMEMBERSTABLE, 1;

    -- add the members removed to the marketing effort exclusion data with a type code of 7

    set @SQL = 'insert into dbo.[' + @TARGETEXCLUSIONTABLE + '] ([DONORID], [DONORNAME], [EXCLUDEDFROMSEGMENT], [INCLUDEDINSEGMENT], [EXCLUSIONREASON], [EXCLUSIONTYPE], [EXCLUSIONTYPECODE])' + char(13) +
               '  select [DONORID], [NAME], [SEGMENTNAME], '''', ''Removed'', ''Remove Members Process'', 7' + char(13) +
               '  from [' + @TEMPREMOVEDMEMBERSTABLE + ']';
    exec (@SQL);

    set @SQL = 'create nonclustered index [IX_' + @TARGETEXCLUSIONTABLE + '_DONORID] on dbo.[' + @TARGETEXCLUSIONTABLE + '] ([DONORID]) with (PAD_INDEX  = off, SORT_IN_TEMPDB = on, DROP_EXISTING = off, IGNORE_DUP_KEY = off, ONLINE = off, FILLFACTOR = 100' + (case when @USECOMPRESSION = 1 then ', DATA_COMPRESSION = PAGE' else '' end) + ') on [IDXGROUP]'
    exec (@SQL);
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;