USP_MKTMARKETINGPLANBRIEF_SWAPBRIEFS

CREATE procedure dbo.[USP_MKTMARKETINGPLANBRIEF_SWAPBRIEFS]
(
  @TOPBRIEFID uniqueidentifier,
  @BOTTOMBRIEFID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @TOPCOUNT int;
  declare @BOTTOMCOUNT int;
  declare @MINSEQ int;
  declare @MINSEGMENTID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = getdate();

    /* Swap the sequences */
    update dbo.[MKTMARKETINGPLANBRIEF] set
      [SEQUENCE] = [SEQUENCE] + 1,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @TOPBRIEFID;

    update dbo.[MKTMARKETINGPLANBRIEF] set
      [SEQUENCE] = [SEQUENCE] - 1,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @BOTTOMBRIEFID;


    /* Check if the marketing plan is used in a mailing, and update any segments appropriately */
    select
      @SEGMENTATIONID = [ID]
    from dbo.[MKTSEGMENTATION]
    where [MARKETINGPLANITEMID] = (select [MARKETINGPLANITEMID] from dbo.[MKTMARKETINGPLANBRIEF] where [ID] = @TOPBRIEFID);

    if @SEGMENTATIONID is not null
    begin
      --Check if the mailing is currently being activated...

      exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

      --Get the segment count for each brief

      select
        @TOPCOUNT = count([ID])
      from dbo.[MKTSEGMENTATIONSEGMENT]
      where [SEGMENTATIONID] = @SEGMENTATIONID
      and [MARKETINGPLANBRIEFID] = @TOPBRIEFID;

      select
        @BOTTOMCOUNT = count([ID])
      from dbo.[MKTSEGMENTATIONSEGMENT]
      where [SEGMENTATIONID] = @SEGMENTATIONID
      and [MARKETINGPLANBRIEFID] = @BOTTOMBRIEFID;

      if (@TOPCOUNT > 0) and (@BOTTOMCOUNT > 0)
      begin
        --Resequence all segments in both briefs

        update dbo.[MKTSEGMENTATIONSEGMENT] set
          [SEQUENCE] = [SEQUENCE] + @BOTTOMCOUNT,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [SEGMENTATIONID] = @SEGMENTATIONID
        and [MARKETINGPLANBRIEFID] = @TOPBRIEFID;

        update dbo.[MKTSEGMENTATIONSEGMENT] set
          [SEQUENCE] = [SEQUENCE] - @TOPCOUNT,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [SEGMENTATIONID] = @SEGMENTATIONID
        and [MARKETINGPLANBRIEFID] = @BOTTOMBRIEFID;

        --Get the minimum segment ID and sequence to update

        select
          @MINSEQ = min([SEQUENCE])
        from dbo.[MKTSEGMENTATIONSEGMENT]
        where [SEGMENTATIONID] = @SEGMENTATIONID
        and [MARKETINGPLANBRIEFID] = @BOTTOMBRIEFID;

        select 
          @MINSEGMENTID = [ID]
        from dbo.[MKTSEGMENTATIONSEGMENT]
        where [SEGMENTATIONID] = @SEGMENTATIONID
        and [MARKETINGPLANBRIEFID] = @BOTTOMBRIEFID
        and [SEQUENCE] = @MINSEQ;

        --Clear the exclusion cache info table first, for every segment after the minimum segment, so the cache will get rebuilt the next time they want record counts...

        exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @MINSEGMENTID, 0, 1;
      end
    end
  end try

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

  return 0;