USP_MKTSEGMENTATION_PREACTIVATION

CREATE procedure dbo.[USP_MKTSEGMENTATION_PREACTIVATION]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @CALCULATEPROCESSSTATUSID uniqueidentifier;
  declare @REMOVEMEMBERSPROCESSSTATUSID uniqueidentifier;
  declare @TABLENAME nvarchar(255);
  declare @PROCESSDATE datetime;
  declare @REMOVEDDATE datetime;
  declare @SQL nvarchar(max);
  declare @COUNTS table(
    [SEGMENTID] uniqueidentifier, 
    [SEGMENTNAME] nvarchar(100), 
    [DONORVIEWID] uniqueidentifier,
    [RECORDSOURCENAME] nvarchar(255), 
    [SOURCECODE] nvarchar(50), 
    [RECORDCOUNT] int, 
    [MAILINGNAME] nvarchar(100), 
    [PROCESSDATE] datetime,
    [EXCLUDE] bit
  );
  declare @REMOVED table(
    [SEGMENTID] uniqueidentifier, 
    [RECORDCOUNT] int
  );

  -- @BUSINESSPROCESSSTATUSID is either a MKTSEGMENTATIONSEGMENTCALCULATEPROCESSSTATUS.ID or a MKTUPDATEMAILINGCOUNTSPROCESSSTATUS.ID


  select
    @CALCULATEPROCESSSTATUSID = [BUSINESSPROCESSSTATUS].[ID],
    @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID],
    @PROCESSDATE = [BUSINESSPROCESSSTATUS].[ENDEDON]
  from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
  inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID]
  where [BUSINESSPROCESSSTATUS].[ID] = @BUSINESSPROCESSSTATUSID;

  if @CALCULATEPROCESSSTATUSID is null
    begin
      select
        @REMOVEMEMBERSPROCESSSTATUSID = [BUSINESSPROCESSSTATUS].[ID],
        @SEGMENTATIONID = [MKTUPDATEMAILINGCOUNTSPROCESS].[SEGMENTATIONID],
        @PROCESSDATE = [BUSINESSPROCESSSTATUS].[ENDEDON]
      from dbo.[MKTUPDATEMAILINGCOUNTSPROCESS]
      inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [MKTUPDATEMAILINGCOUNTSPROCESS].[ID]
      where [BUSINESSPROCESSSTATUS].[ID] = @BUSINESSPROCESSSTATUSID;

      select top 1
        @CALCULATEPROCESSSTATUSID = [BUSINESSPROCESSSTATUS].[ID]
      from dbo.[MKTSEGMENTATIONSEGMENTCALCULATEPROCESS]
      inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[ID]
      where [MKTSEGMENTATIONSEGMENTCALCULATEPROCESS].[SEGMENTATIONID] = @SEGMENTATIONID
      and [BUSINESSPROCESSSTATUS].[STATUSCODE] = 0
      order by [BUSINESSPROCESSSTATUS].[ENDEDON] desc;
    end

  select
    @TABLENAME = [TABLENAME]
  from dbo.[BUSINESSPROCESSOUTPUT]
  where [BUSINESSPROCESSSTATUSID] = @CALCULATEPROCESSSTATUSID
  and [TABLEKEY] = 'PREACTIVATION';

  set @SQL = 'select' + char(13) +
             '  [PREACT].[SEGMENTID],' + char(13) +
             '  [PREACT].[SEGMENTNAME],' + char(13) +
             '  [PREACT].[DONORVIEWID],' + char(13) +
             '  case [MKTSEGMENTATIONSEGMENT].[EXCLUDE] when 1 then ''Segment exclusions'' else [PREACT].[RECORDSOURCENAME] end [RECORDSOURCENAME],' + char(13) +
             '  [PREACT].[SOURCECODE],' + char(13) +
             '  [PREACT].[RECORDCOUNT],' + char(13) +
             '  [PREACT].[MAILINGNAME],' + char(13) +
             '  @PROCESSDATE as [PROCESSDATE],' + char(13) +
             '  [MKTSEGMENTATIONSEGMENT].[EXCLUDE]' + char(13) +
             'from dbo.[' + @TABLENAME + '] [PREACT]' + char(13) +
             'left outer join [MKTSEGMENTATIONSEGMENT] on [PREACT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
             'where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13);

  insert into @COUNTS
    exec sp_executesql @SQL, N'@PROCESSDATE datetime, @SEGMENTATIONID uniqueidentifier', @PROCESSDATE = @PROCESSDATE, @SEGMENTATIONID = @SEGMENTATIONID;

  if not @REMOVEMEMBERSPROCESSSTATUSID is null
    begin
      set @TABLENAME = dbo.[UFN_MKTSEGMENTATIONACTIVATEEXCLUSION_MAKETABLENAME](@SEGMENTATIONID);

      set @SQL = 'select' + char(13) +
                 '  [DATA].[SEGMENTID],' + char(13) +
                 '  count(*) as [RECORDCOUNT]' + char(13) +
                 'from dbo.[' + @TABLENAME + '] as [DATA]' + char(13) +
                 'inner join dbo.[MKTSEGMENTATIONEXCLUSION] on [MKTSEGMENTATIONEXCLUSION].[ID] = [DATA].[SEGMENTATIONEXCLUSIONID]' + char(13) +
                 'inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [MKTSEGMENTATIONEXCLUSION].[STATUSID]' + char(13) +
                 'where [BUSINESSPROCESSSTATUS].[ENDEDON] <= @PROCESSDATE' + char(13) +
                 'group by [DATA].[SEGMENTID]';

      insert into @REMOVED
        exec sp_executesql @SQL, N'@PROCESSDATE datetime', @PROCESSDATE = @PROCESSDATE;
    end

  select
    [COUNTS].[SEGMENTID] as [SEGMENTID],
    [COUNTS].[SEGMENTNAME] as [SEGMENTNAME],
    [MKTSEGMENTLIST].[LISTID] as [LISTID],
    [COUNTS].[RECORDSOURCENAME] as [RECORDSOURCENAME],
    [COUNTS].[SOURCECODE] as [SOURCECODE],
    ([COUNTS].[RECORDCOUNT] - isnull([REMOVED].[RECORDCOUNT], 0)) as [RECORDCOUNT],
    [COUNTS].[MAILINGNAME] as [MAILINGNAME],
    [COUNTS].[PROCESSDATE] as [PROCESSDATE],
    [COUNTS].[EXCLUDE]
  from @COUNTS as [COUNTS]
  left outer join @REMOVED as [REMOVED] on [COUNTS].[SEGMENTID] = [REMOVED].[SEGMENTID]
  left join [MKTSEGMENTLIST] on [MKTSEGMENTLIST].[QUERYVIEWCATALOGID] = [COUNTS].[DONORVIEWID];

  return 0;