USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE
(
  @ID nvarchar(max),
  @CHANGEAGENTID uniqueidentifier = null,
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @PACKAGELOADED bit,
  @RESPONSERATE decimal(5,2),
  @RESPONSERATELOADED bit,
  @GIFTAMOUNT money,
  @GIFTAMOUNTLOADED bit,
  @SAMPLESIZE int,
  @SAMPLESIZETYPECODE tinyint,
  @SAMPLESIZEMETHODCODE tinyint,
  @SAMPLESIZELOADED bit
)
as
  set nocount on;

  declare @TESTSEGMENTSTABLE table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @OLDSEGMENTATIONPACKAGEID uniqueidentifier;
  declare @OLDPACKAGEID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

    set @CURRENTDATE = GetDate();


    /* Parse out the multiple test segment IDs */
    set @START = 0;
    select @POS = charindex(',', @ID, @START)

    while (@POS <> 0)
    begin
      insert into @TESTSEGMENTSTABLE
        select substring(@ID, @START, @POS - @START);

      set @START = @POS + 1;
      select @POS = charindex(',', @ID, @START);
    end;

    if len(@ID) > 0
      insert into @TESTSEGMENTSTABLE
        select substring(@ID, @START, 37);


    declare TESTSEGMENTCURSOR cursor local fast_forward for
      select [ID] from @TESTSEGMENTSTABLE;

    open TESTSEGMENTCURSOR;
    fetch next from TESTSEGMENTCURSOR into @TESTSEGMENTID;

    while (@@FETCH_STATUS = 0)
    begin
        select 
          @SEGMENTATIONID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID],
          @PACKAGELOADED = (case when [MKTSEGMENTATION].[MAILINGTYPECODE] = 1 then 0 else @PACKAGELOADED end)
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
        inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
        where [MKTSEGMENTATIONTESTSEGMENT].[ID] = @TESTSEGMENTID;

      /* Check if the mailing is currently being activated */
      exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

      /* Grab the old packageID info before we save the test segment */
      if @PACKAGELOADED = 1
        select 
          @OLDSEGMENTATIONPACKAGEID = (select [PAK].[ID] from dbo.[MKTSEGMENTATIONPACKAGE] as [PAK] where [PAK].[SEGMENTATIONID] = [SEG].[SEGMENTATIONID] and [PAK].[PACKAGEID] = [TESTSEG].[PACKAGEID]),
          @OLDPACKAGEID = [TESTSEG].[PACKAGEID] 
        from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [TESTSEG]
        inner join dbo.[MKTSEGMENTATIONSEGMENT] as [SEG] on [SEG].[ID] = [TESTSEG].[SEGMENTID]
        where [TESTSEG].[ID] = @TESTSEGMENTID;

      /* Save the test segment */
      update dbo.[MKTSEGMENTATIONTESTSEGMENT] set 
        [PACKAGEID] = (case when @PACKAGELOADED = 1 then @PACKAGEID else [PACKAGEID] end),
        [RESPONSERATE] = (case when @RESPONSERATELOADED = 1 then @RESPONSERATE else [RESPONSERATE] end),
        [GIFTAMOUNT] = (case when @GIFTAMOUNTLOADED = 1 then @GIFTAMOUNT else [GIFTAMOUNT] end),
        [SAMPLESIZE] = (case when @SAMPLESIZELOADED = 1 then @SAMPLESIZE else [SAMPLESIZE] end),
        [SAMPLESIZETYPECODE] = (case when @SAMPLESIZELOADED = 1 then @SAMPLESIZETYPECODE else [SAMPLESIZETYPECODE] end),
        [SAMPLESIZEMETHODCODE] = (case when @SAMPLESIZELOADED = 1 then @SAMPLESIZEMETHODCODE else [SAMPLESIZEMETHODCODE] end),
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @TESTSEGMENTID;

      if @PACKAGELOADED = 1
        begin
          /* Save and update the package for the segment */
          if @OLDPACKAGEID <> @PACKAGEID
            begin
              /* Remove the old package, only if is not being used by any other segments and test segments */
              if not exists(select [ID] from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @OLDPACKAGEID) and
                 not exists(select [MKTSEGMENTATIONTESTSEGMENT].[ID] from dbo.[MKTSEGMENTATIONTESTSEGMENT] inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID and [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID] = @OLDPACKAGEID)
                exec dbo.[USP_MKTSEGMENTATIONPACKAGE_DELETE] @OLDSEGMENTATIONPACKAGEID, @CHANGEAGENTID;

              /* Add the new package, only if it doesn't exist already */
              if not exists(select [ID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID and [PACKAGEID] = @PACKAGEID)
                exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONPACKAGE] null, @CHANGEAGENTID, @SEGMENTATIONID, @PACKAGEID;
            end

          /* Update the package with the selected code */
          declare @OLDCODE nvarchar(10);
          select @OLDCODE=coalesce([CODE],'') from dbo.[MKTPACKAGE] where [ID]=@PACKAGEID;
          if @OLDCODE<>@PACKAGECODE
            update dbo.[MKTPACKAGE] set [CODE]=@PACKAGECODE,[CHANGEDBYID]=@CHANGEAGENTID,[DATECHANGED]=@CURRENTDATE where [ID]=@PACKAGEID;

        end

      fetch next from TESTSEGMENTCURSOR into @TESTSEGMENTID;
    end;

    close TESTSEGMENTCURSOR;
    deallocate TESTSEGMENTCURSOR;
  end try

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

  return 0;