USP_MKTSEGMENTATIONSEGMENT_MOVEUP

CREATE procedure dbo.[USP_MKTSEGMENTATIONSEGMENT_MOVEUP]
(
  @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 @ABOVESEGMENTID uniqueidentifier;
  declare @SEQ int;
  declare @MAXSEQ 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]
    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 first segment in the brief)

        if @SEQ > (select min([SEQUENCE]) from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [MARKETINGPLANBRIEFID] = @BRIEFID)
          begin
            select @ABOVESEGMENTID = [ID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            where [SEGMENTATIONID] = @SEGMENTATIONID
            and [MARKETINGPLANBRIEFID] = @BRIEFID
            and [SEQUENCE] = (@SEQ - 1);

            --swap the 2 segments

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

        if (@SEQ > 1) or ((@SEQ = 1) and (@NEXTBRIEFSEQ <> 1))
          begin
            select @ABOVESEGMENTID = [ID]
            from dbo.[MKTSEGMENTATIONSEGMENT]
            where [SEGMENTATIONID] = @SEGMENTATIONID
            and [MARKETINGPLANBRIEFID] is null
            and [NEXTBRIEFSEQUENCE] = @NEXTBRIEFSEQ
            and [SEQUENCE] = (@SEQ - 1);

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

              exec dbo.[USP_MKTSEGMENTATION_SWAPSEGMENTS] @ABOVESEGMENTID, @ID, @CHANGEAGENTID;
            else
              begin
                --Segment is being moved up above a brief...

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

                --Get the brief ID we are moving above

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

                --Get the new sequence for the segment, and the minimum sequence to refresh the views

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

                set @MAXSEQ = @SEQ + 1;

                --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] = @SEQ;

                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;