USP_MKTSEGMENTATIONACTIVATE_FINALIZEACTIVATE

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_FINALIZEACTIVATE]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @ACTIVATEDATE datetime = null
)
as
  set nocount on;

  declare @COMMUNICATIONTYPECODE tinyint;
  declare @ACTIVE bit;
  declare @SQL nvarchar(max);
  declare @CURRENTDATE datetime = getdate();
  if @ACTIVATEDATE is null
  begin
    set @ACTIVATEDATE = @CURRENTDATE;
  end

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    select
      @COMMUNICATIONTYPECODE = [COMMUNICATIONTYPECODE],
      @ACTIVE = [ACTIVE]
    from dbo.[MKTSEGMENTATION]
    where [ID] = @SEGMENTATIONID;

    /* Only save this info if this is the first activation */
    if @ACTIVE = 0
      begin
        /* Update the mailing to show that it is activated now */
        update dbo.[MKTSEGMENTATION] set
          [ACTIVE] = 1,
          [ACTIVATEDATE] = @ACTIVATEDATE,
          [MAILDATE] = (case when [MAILINGTYPECODE] <> 4 and [MAILDATE] is null then @ACTIVATEDATE else [MAILDATE] end),
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @SEGMENTATIONID;


        /* Skip for specific mailing types because they don't track responses. */
        if @COMMUNICATIONTYPECODE <> 2  --Event invitation

          /* Insert a refresh parameter set for the mailing refresh business process */
          insert into dbo.[MKTSEGMENTATIONREFRESHPROCESS] (
            [ID],
            [SEGMENTATIONID],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          ) values (
            NewID(),
            @SEGMENTATIONID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );


        /* Add the test mailing to the exclusions of the parent mailing */
        declare @PARENTMAILINGID uniqueidentifier;

        select @PARENTMAILINGID = [PARENTSEGMENTATIONID]
        from dbo.[MKTSEGMENTATION]
        where [ID] = @SEGMENTATIONID;

        if @PARENTMAILINGID is not null
          begin
            /* Add the Test to the Exclusions of the Parent Mailing */                              
            exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFILTERSEGMENTATION] null, @CHANGEAGENTID, @PARENTMAILINGID, @SEGMENTATIONID;

            /* Save the test as an exclusion to any inactive test mailings with the same parent id */
            declare @TESTSEGMENTATIONID uniqueidentifier;
            declare EXCEPTIONCURSOR cursor local fast_forward for
            select [ID] from [MKTSEGMENTATION] where [PARENTSEGMENTATIONID]=@PARENTMAILINGID and [ACTIVE]=0 and [ID]<>@SEGMENTATIONID;

            open EXCEPTIONCURSOR;
            fetch next from EXCEPTIONCURSOR into @TESTSEGMENTATIONID;

            while (@@FETCH_STATUS = 0)
              begin
                if not exists (select [ID] from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION] where [SEGMENTATIONID] = @TESTSEGMENTATIONID and [PREVIOUSSEGMENTATIONID] = @SEGMENTATIONID)
                  exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFILTERSEGMENTATION] null, @CHANGEAGENTID, @TESTSEGMENTATIONID, @SEGMENTATIONID;

                fetch next from EXCEPTIONCURSOR into @TESTSEGMENTATIONID;
              end;

            close EXCEPTIONCURSOR;
            deallocate EXCEPTIONCURSOR;
          end


        /* Clear the segment cache... */
        /* The SKIPADDRESSCACHETABLE parameter must always be set to "True" so that we don't truncate the address cache table, */
        /* because we need this table to stay around after activation.  Also, the code below will handle the address table.    */
        exec dbo.[USP_MKTSEGMENTATION_CLEARCACHE] @SEGMENTATIONID, 0, 1;


        /* Drop the address cache if it exists and if this mailing is not using it */
        declare @ADDRESSCACHETABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @ADDRESSCACHETABLENAME)
          begin
            if exists(select * from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID and [USEADDRESSPROCESSING] = 1 and [ADDRESSPROCESSINGOPTIONID] is not null) or
               exists(select * from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [OVERRIDEADDRESSPROCESSING] = 1 and [USEADDRESSPROCESSING] = 1 and [ADDRESSPROCESSINGOPTIONID] is not null)
              begin
                /* Remove unnecessary records from the address cache table.  These are records that we cached addresses for  */
                /* but the constituent record did not make it into the final mailing data table because of other exclusions. */
                /* This is just to free up some extra space.  Address processing cache is only for the BBEC house file.      */
                set @SQL = 'delete from dbo.[' + @ADDRESSCACHETABLENAME + ']' + char(13) +
                           'where not exists(' + char(13) +
                           '  select *' + char(13) +
                           '  from dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DATA]' + char(13) +
                           '  where [DATA].[SEGMENTID] = [' + @ADDRESSCACHETABLENAME + '].[SEGMENTID]' + char(13) +
                           '  and [DATA].[DONORID] = [' + @ADDRESSCACHETABLENAME + '].[CONSTITUENTID]' + char(13) +
                           '  and [DATA].[DONORQUERYVIEWCATALOGID] = ''DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0''' + char(13) +
                           ')';
                exec (@SQL);
              end
            else
              begin
                /* Drop the address cache if it exists and if this mailing is not using it */
                set @SQL = 'drop table dbo.[' + @ADDRESSCACHETABLENAME + ']';
                exec (@SQL);
              end
          end

        /* Drop the email address cache if it exists and if this mailing is not using it */
        declare @EMAILADDRESSCACHETABLENAME nvarchar(128) = dbo.[UFN_MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES_MAKETABLENAME](@SEGMENTATIONID);
        if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @EMAILADDRESSCACHETABLENAME)
          begin
            if exists(select * from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID and [USEADDRESSPROCESSING] = 1 and [ADDRESSPROCESSINGOPTIONID] is not null) or
               exists(select * from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [OVERRIDEADDRESSPROCESSING] = 1 and [USEADDRESSPROCESSING] = 1 and [ADDRESSPROCESSINGOPTIONID] is not null)
              begin
                /* Remove unnecessary records from the address cache table.  These are records that we cached email addresses for  */
                /* but the constituent record did not make it into the final mailing data table because of other exclusions.       */
                /* This is just to free up some extra space.  Email address processing cache is only for the BBEC house file.      */
                set @SQL = 'delete from dbo.[' + @EMAILADDRESSCACHETABLENAME + ']' + char(13) +
                           'where not exists(' + char(13) +
                           '  select *' + char(13) +
                           '  from dbo.[' + dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID) + '] as [DATA]' + char(13) +
                           '  where [DATA].[SEGMENTID] = [' + @EMAILADDRESSCACHETABLENAME + '].[SEGMENTID]' + char(13) +
                           '  and [DATA].[DONORID] = [' + @EMAILADDRESSCACHETABLENAME + '].[CONSTITUENTID]' + char(13) +
                           '  and [DATA].[DONORQUERYVIEWCATALOGID] = ''DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0''' + char(13) +
                           ')';
                exec (@SQL);
              end
            else
              begin
                /* Drop the email address cache if it exists and if this mailing is not using it */
                set @SQL = 'drop table dbo.[' + @EMAILADDRESSCACHETABLENAME + ']';
                exec (@SQL);
              end
          end
      end
    else
      begin
        /* Update the date the mailing was refreshed since activate is basically a refresh */
        update dbo.[MKTSEGMENTATIONREFRESHPROCESS] set
          [DATEREFRESHED] = @ACTIVATEDATE,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [SEGMENTATIONID] = @SEGMENTATIONID;
      end
  end try

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

  return 0;