USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_DRAGDROPINSERT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_DRAGDROPINSERT
(
  @ID uniqueidentifier,                            /* Required. The mailing ID. */
  @CHANGEAGENTID uniqueidentifier = null,          /* Optional. The user ID that is making the change. */
  @BRIEFS xml,                                     /* Optional. Brief IDs to insert at the specified segment sequence. */
  @SEGMENTS xml,                                   /* Optional. Segment IDs (and their associated brief ID if any) to insert at the specified segment sequence.  Brief IDs should only exist for segments where the entire brief is being moved. */
  @SEGMENTSEQUENCE int,                            /* Required. The sequence to insert the segments at. */
  @BRIEFSEQUENCE int,                              /* Required. The sequence to insert the briefs at. */
  @MARKETINGPLANBRIEFID uniqueidentifier,          /* Required. The brief to insert the segments into. */
  @NEXTBRIEFSEQUENCE int                           /* Required. The brief sequence that comes after the segments being inserted.  This number should already have the number of briefs that are selected above the drop point subtracted from it. */
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SEGMENTSTABLE table ([ID] uniqueidentifier, [BRIEFID] uniqueidentifier);
  declare @BRIEFSTABLE table ([ID] uniqueidentifier);
  declare @TOPSEQ int;
  declare @BOTTOMSEQ int;
  declare @SEQ int;
  declare @MINSEQ int;
  declare @MINSEGMENTID uniqueidentifier;
  declare @CURSOR cursor;
  declare @COUNTABOVE int;
  declare @COUNTBELOW int;

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

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @ID;

    if @CHANGEAGENTID is null  
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    set @CURRENTDATE = GetDate();

    if @SEGMENTS is not null
      begin
        /*********************************/
        /* Update the segments sequences */
        /*********************************/

        --Put the segment and brief IDs into a temp table

        insert into @SEGMENTSTABLE
          select T.c.value('(ID)[1]','uniqueidentifier') as 'ID',
            T.c.value('(BRIEFID)[1]','uniqueidentifier') as 'BRIEFID'
          from @SEGMENTS.nodes('/SEGMENTS/ITEM') T(c);

        --Get the top and bottom sequences for the segments being inserted

        select 
          @TOPSEQ = min([MKTSEGMENTATIONSEGMENT].[SEQUENCE]), 
          @BOTTOMSEQ = max([MKTSEGMENTATIONSEGMENT].[SEQUENCE])
        from dbo.[MKTSEGMENTATIONSEGMENT]
        inner join @SEGMENTSTABLE as [T] on [T].[ID] = [MKTSEGMENTATIONSEGMENT].[ID];

        if @SEGMENTSEQUENCE < @TOPSEQ
          set @MINSEQ = @SEGMENTSEQUENCE;
        else
          set @MINSEQ = @TOPSEQ;

        set @SEQ = @MINSEQ;

        if @TOPSEQ <= (@SEGMENTSEQUENCE - 1)
          begin
            --Update the other segments sequences before the drop node first

            set @CURSOR = cursor local fast_forward for
              select [ID], null
              from dbo.[MKTSEGMENTATIONSEGMENT]
              where [SEGMENTATIONID] = @ID
              and [SEQUENCE] between @TOPSEQ and (@SEGMENTSEQUENCE - 1)
              and [ID] not in (select [ID] from @SEGMENTSTABLE)
              order by [SEQUENCE];
            exec dbo.[USP_MKTSEGMENTATIONSEGMENT_RESEQUENCESEGMENTS] @CURSOR, @SEQ output, default, default, @CHANGEAGENTID, @CURRENTDATE;
          end

        --Update the inserted segments sequences

        set @CURSOR = cursor local fast_forward for
          select [ID], [BRIEFID] from @SEGMENTSTABLE;
        exec dbo.[USP_MKTSEGMENTATIONSEGMENT_RESEQUENCESEGMENTS] @CURSOR, @SEQ output, @MARKETINGPLANBRIEFID, @NEXTBRIEFSEQUENCE, @CHANGEAGENTID, @CURRENTDATE;

        if @SEGMENTSEQUENCE <= @BOTTOMSEQ
          begin
 --Now update the other segments sequences after the drop node

            set @CURSOR = cursor local fast_forward for
              select [ID], null
              from dbo.[MKTSEGMENTATIONSEGMENT]
              where [SEGMENTATIONID] = @ID
              and [SEQUENCE] between @SEGMENTSEQUENCE and @BOTTOMSEQ
              and [ID] not in (select [ID] from @SEGMENTSTABLE)
              order by [SEQUENCE];
            exec dbo.[USP_MKTSEGMENTATIONSEGMENT_RESEQUENCESEGMENTS] @CURSOR, @SEQ output, default, default, @CHANGEAGENTID, @CURRENTDATE;
          end

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

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

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


    if @BRIEFS is not null
      begin
        /*******************************/
        /* Update the briefs sequences */
        /*******************************/

        --Put the brief IDs into a temp table

        insert into @BRIEFSTABLE
          select T.c.value('(ID)[1]','uniqueidentifier') as 'ID'
          from @BRIEFS.nodes('/BRIEFS/ITEM') T(c);

        --Get the top and bottom sequences for the briefs being inserted

        select 
          @TOPSEQ = min([MKTMARKETINGPLANBRIEF].[SEQUENCE]), 
          @BOTTOMSEQ = max([MKTMARKETINGPLANBRIEF].[SEQUENCE]),
          @COUNTABOVE = sum(case when [MKTMARKETINGPLANBRIEF].[SEQUENCE] < @BRIEFSEQUENCE then 1 else 0 end),
          @COUNTBELOW = sum(case when [MKTMARKETINGPLANBRIEF].[SEQUENCE] >= @BRIEFSEQUENCE then 1 else 0 end)
        from dbo.[MKTMARKETINGPLANBRIEF]
        inner join @BRIEFSTABLE as [T] on [T].[ID] = [MKTMARKETINGPLANBRIEF].[ID];

        if @BRIEFSEQUENCE < @TOPSEQ
          set @SEQ = @BRIEFSEQUENCE;
        else
          set @SEQ = @TOPSEQ;

        if @TOPSEQ <= (@BRIEFSEQUENCE - 1)
          begin
            --Update the other briefs sequences before the drop node first

            set @CURSOR = cursor local fast_forward for
              select [MKTMARKETINGPLANBRIEF].[ID]
              from dbo.[MKTMARKETINGPLANBRIEF]
              inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANBRIEF].[MARKETINGPLANITEMID]
              where [MKTSEGMENTATION].[ID] = @ID
              and [MKTMARKETINGPLANBRIEF].[SEQUENCE] between @TOPSEQ and (@BRIEFSEQUENCE - 1)
              and [MKTMARKETINGPLANBRIEF].[ID] not in (select [ID] from @BRIEFSTABLE)
              order by [MKTMARKETINGPLANBRIEF].[SEQUENCE];
            exec dbo.[USP_MKTMARKETINGPLANBRIEF_RESEQUENCEBRIEFS] @CURSOR, @SEQ output, @CHANGEAGENTID, @CURRENTDATE;

            declare @ABOVECOUNTS as table(ID uniqueidentifier, [COUNT] int);
            insert into 
                @ABOVECOUNTS
            select 
                MKTSEGMENTATIONSEGMENT.ID,
                COUNT(MKTMARKETINGPLANBRIEF.ID)
            from 
                MKTSEGMENTATIONSEGMENT
            left join
                MKTMARKETINGPLANBRIEF on MKTMARKETINGPLANBRIEF.SEQUENCE < MKTSEGMENTATIONSEGMENT.NEXTBRIEFSEQUENCE
            inner join
                @BRIEFSTABLE as [BRIEFSTABLE] on [BRIEFSTABLE].ID = MKTMARKETINGPLANBRIEF.ID
            where [SEGMENTATIONID] = @ID
                and [NEXTBRIEFSEQUENCE] <> 0
                and [NEXTBRIEFSEQUENCE] between @TOPSEQ and @BRIEFSEQUENCE
                and MKTSEGMENTATIONSEGMENT.[SEQUENCE] < @SEGMENTSEQUENCE
            group by
                MKTSEGMENTATIONSEGMENT.ID;

            update dbo.[MKTSEGMENTATIONSEGMENT] set
              [NEXTBRIEFSEQUENCE] = [NEXTBRIEFSEQUENCE] - [ABOVECOUNTS].[COUNT],
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            from
                dbo.[MKTSEGMENTATIONSEGMENT]
      inner join @ABOVECOUNTS as [ABOVECOUNTS] on [ABOVECOUNTS].ID = dbo.MKTSEGMENTATIONSEGMENT.ID
            where [SEGMENTATIONID] = @ID
            and [NEXTBRIEFSEQUENCE] <> 0
            and [NEXTBRIEFSEQUENCE] between @TOPSEQ and @BRIEFSEQUENCE
            and [SEQUENCE] < @SEGMENTSEQUENCE;
          end

        --Update the inserted briefs sequences

        set @CURSOR = cursor local fast_forward for
          select [ID] from @BRIEFSTABLE;
        exec dbo.[USP_MKTMARKETINGPLANBRIEF_RESEQUENCEBRIEFS] @CURSOR, @SEQ output, @CHANGEAGENTID, @CURRENTDATE;

        if @BRIEFSEQUENCE <= @BOTTOMSEQ
          begin
            --Now update the other briefs sequences after the drop node

            set @CURSOR = cursor local fast_forward for
              select [MKTMARKETINGPLANBRIEF].[ID]
              from dbo.[MKTMARKETINGPLANBRIEF]
              inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[MARKETINGPLANITEMID] = [MKTMARKETINGPLANBRIEF].[MARKETINGPLANITEMID]
              where [MKTSEGMENTATION].[ID] = @ID
              and [MKTMARKETINGPLANBRIEF].[SEQUENCE] between @BRIEFSEQUENCE and @BOTTOMSEQ
              and [MKTMARKETINGPLANBRIEF].[ID] not in (select [ID] from @BRIEFSTABLE)
              order by [MKTMARKETINGPLANBRIEF].[SEQUENCE];
            exec dbo.[USP_MKTMARKETINGPLANBRIEF_RESEQUENCEBRIEFS] @CURSOR, @SEQ output, @CHANGEAGENTID, @CURRENTDATE;

            update dbo.[MKTSEGMENTATIONSEGMENT] set
              [NEXTBRIEFSEQUENCE] = [NEXTBRIEFSEQUENCE] + @COUNTBELOW,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [SEGMENTATIONID] = @ID
            and [NEXTBRIEFSEQUENCE] <> 0
            and [NEXTBRIEFSEQUENCE] between (@BRIEFSEQUENCE - 1) and @BOTTOMSEQ
            and [SEQUENCE] >= @SEGMENTSEQUENCE;

          end
      end




    /*********************************************************/
    /* Double check that the sequence reordering is correct. */
    /*********************************************************/
    declare @BRIEFSEQS table ([ID] uniqueidentifier primary key, [OLDSEQUENCE] int, [NEWSEQUENCE] int);
    declare @SEGMENTSEQS table ([ID] uniqueidentifier primary key, [OLDSEQUENCE] int, [NEWSEQUENCE] int);

    insert into @BRIEFSEQS ([ID], [OLDSEQUENCE], [NEWSEQUENCE])
      select [ID], [SEQUENCE], row_number() over (order by [SEQUENCE])
      from dbo.[MKTMARKETINGPLANBRIEF]
      where [MARKETINGPLANITEMID] = (select [MARKETINGPLANITEMID] from dbo.[MKTSEGMENTATION] where [ID] = @ID)
      order by [SEQUENCE];

    if exists(select * from @BRIEFSEQS where [OLDSEQUENCE] <> [NEWSEQUENCE])
      begin
        raiserror('BBERR_MKTSEGMENTATIONSEGMENT_DRAGDROPINSERT_OUTOFORDER', 13, 1);
        raiserror('Error:  Briefs will be out of order.', 1, 11);
      end

    insert into @SEGMENTSEQS ([ID], [OLDSEQUENCE], [NEWSEQUENCE])
      select SS.[ID], SS.[SEQUENCE], row_number() over (order by isnull(cast(B.[SEQUENCE] as decimal(18,1)), cast(SS.[NEXTBRIEFSEQUENCE] as decimal(18,1)) - 0.5), SS.[SEQUENCE])
      from dbo.[MKTSEGMENTATIONSEGMENT] as SS
      left join dbo.[MKTMARKETINGPLANBRIEF] as B on B.[ID] = SS.[MARKETINGPLANBRIEFID]
      where SS.[SEGMENTATIONID] = @ID
      order by isnull(cast(B.[SEQUENCE] as decimal(18,1)), cast(SS.[NEXTBRIEFSEQUENCE] as decimal(18,1)) - 0.5), SS.[SEQUENCE];

    if exists(select * from dbo.[MKTSEGMENTATIONSEGMENT] as SS inner join @SEGMENTSEQS as S on S.[ID] = SS.[ID] where S.[OLDSEQUENCE] <> S.[NEWSEQUENCE])
      begin
        raiserror('BBERR_MKTSEGMENTATIONSEGMENT_DRAGDROPINSERT_OUTOFORDER', 13, 1);
        raiserror('Error:  Segments will be out of order.', 1, 11);
      end

  end try

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

  return 0;