USP_MKTSEGMENTATIONSEGMENT_MOVEDOWN

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_MOVEDOWN]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SEGMENTATIONID uniqueidentifier;
  declare @ISHISTORICAL bit;
  declare @MARKETINGPLANITEMID uniqueidentifier;
  declare @BRIEFID uniqueidentifier;
  declare @NEXTBRIEFSEQ int;
  declare @MAXBRIEFSEQ int;
  declare @BELOWSEGMENTID uniqueidentifier;
  declare @SEQ int;
  declare @MAXSEQ 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();

    select
      @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
      @ISHISTORICAL = [MKTSEGMENTATION].[ISHISTORICAL],
      @SEQ = [MKTSEGMENTATIONSEGMENT].[SEQUENCE],
      @BRIEFID = [MKTSEGMENTATIONSEGMENT].[MARKETINGPLANBRIEFID],
      @NEXTBRIEFSEQ = [MKTSEGMENTATIONSEGMENT].[NEXTBRIEFSEQUENCE],
      @MARKETINGPLANITEMID = [MKTSEGMENTATION].[MARKETINGPLANITEMID]
    from dbo.[MKTSEGMENTATIONSEGMENT]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
    where [MKTSEGMENTATIONSEGMENT].[ID] = @ID;

    if @ISHISTORICAL = 1
      begin
        raiserror('BBERR_MKTSEGMENTATION_ISHISTORICAL', 13, 1);
        raiserror('Segments in historical marketing efforts may not be moved.', 1, 11);
        return 1;
      end

    --Check if the mailing is currently being activated...

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    if @BRIEFID is not null
      begin
        --Segment is under a brief (only move if not the last segment in the brief)

        if @SEQ < (select max([SEQUENCE]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [MARKETINGPLANBRIEFID] = @BRIEFID)
          begin
            select @BELOWSEGMENTID = [ID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            where [SEGMENTATIONID] = @SEGMENTATIONID
            and [MARKETINGPLANBRIEFID] = @BRIEFID
            and [SEQUENCE] = (@SEQ + 1);

            --swap the 2 segments

            exec dbo.[USP_MKTSEGMENTATION_SWAPSEGMENTS] @ID, @BELOWSEGMENTID, @CHANGEAGENTID;
          end
      end
    else
      begin
        --Segment is at the root level (only move if not the last segment in the mailing)

        select @MAXSEQ = max([SEQUENCE]) 
        from dbo.[MKTSEGMENTATIONSEGMENT] 
        where [SEGMENTATIONID] = @SEGMENTATIONID;

        select @MAXBRIEFSEQ = isnull(max([SEQUENCE]), 0)
        from dbo.[MKTMARKETINGPLANBRIEF]
        where [MARKETINGPLANITEMID] = @MARKETINGPLANITEMID;

        if (@SEQ < @MAXSEQ) or ((@SEQ = @MAXSEQ) and (@NEXTBRIEFSEQ between 1 and @MAXBRIEFSEQ))
          begin
            select @BELOWSEGMENTID = [ID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            where [SEGMENTATIONID] = @SEGMENTATIONID
            and [MARKETINGPLANBRIEFID] is null
            and [NEXTBRIEFSEQUENCE] = @NEXTBRIEFSEQ
            and [SEQUENCE] = (@SEQ + 1);

            if @BELOWSEGMENTID is not null
              --Segment is being moved below another segment, swap the 2 segments

              exec dbo.[USP_MKTSEGMENTATION_SWAPSEGMENTS] @ID, @BELOWSEGMENTID, @CHANGEAGENTID;
            else
              begin
                --Segment is being moved down below a brief...


                --Get the brief ID we are moving below

                select @BRIEFID = [ID]
                from dbo.[MKTMARKETINGPLANBRIEF]
                where [MARKETINGPLANITEMID] = @MARKETINGPLANITEMID
                and [SEQUENCE] = @NEXTBRIEFSEQ;

                --Get the minimum sequence to refresh the views

                set @MINSEQ = @SEQ;
                set @MAXSEQ = @MINSEQ + 1;

                --Get the new sequence for the segment

                select @SEQ = isnull(max([SEQUENCE]), @SEQ)
                from dbo.[MKTSEGMENTATIONSEGMENT] 
                where [SEGMENTATIONID] = @SEGMENTATIONID 
                and [MARKETINGPLANBRIEFID] = @BRIEFID;

                --Update the segment sequence

                update dbo.[MKTSEGMENTATIONSEGMENT] set
                  [NEXTBRIEFSEQUENCE] = (@NEXTBRIEFSEQ + 1),
                  [SEQUENCE] = @SEQ,
                  [CHANGEDBYID] = @CHANGEAGENTID,
                  [DATECHANGED] = @CURRENTDATE
                where [ID] = @ID;

                --Resequence all segments in the brief

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

                --Clear the cache for this segment and all segments after it

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

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

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

  return 0;