USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE_6

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONTESTSEGMENT_MULTIPLE_6]
(
  @ID nvarchar(max),
  @CHANGEAGENTID uniqueidentifier = null,
  @ASKLADDER xml,
  @ASKLADDERLOADED bit,
  @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,
  @TESTSEGMENTCODE nvarchar(10),
  @TESTSEGMENTCODELOADED bit,
  @PREFIXCODE tinyint,
  @PREFIXCODELOADED bit,
  @TESTSEGMENTCODEVALUEID uniqueidentifier,
  @PACKAGECODEVALUEID uniqueidentifier,
  @ITEMLIST xml,
  @CHANNELSOURCECODE nvarchar(10),
  @CHANNELSOURCECODEVALUEID uniqueidentifier,
  @FRACTION nvarchar(10),
  @OVERRIDEBUSINESSUNITSLOADED bit,
  @OVERRIDEBUSINESSUNITS bit,
  @BUSINESSUNITS xml,
  @CURRENTAPPUSERID uniqueidentifier
)
as
  set nocount on;

  declare @SEGMENTATIONTESTSEGMENTS table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @PARENTSEGMENTID uniqueidentifier;
  declare @OLDSEGMENTATIONPACKAGEID uniqueidentifier;
  declare @OLDPACKAGEID uniqueidentifier;
  declare @OLDSAMPLESIZE int;
  declare @OLDSAMPLESIZETYPECODE tinyint;
  declare @OLDSAMPLESIZEMETHODCODE tinyint;
  declare @OLDFRACTION nvarchar(10);
  declare @OLDASKLADDERID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @SEGMENTTYPECODE tinyint;
  declare @ASKLADDERID uniqueidentifier;
  declare @SEGMENTEDHOUSEFILEEXISTS bit;

  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 @SEGMENTATIONTESTSEGMENTS
          select substring(@ID, @START, @POS - @START);

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

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

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

    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),
          @SEGMENTTYPECODE = [MKTSEGMENT].[SEGMENTTYPECODE],
          @SEGMENTEDHOUSEFILEEXISTS = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_IMPORTTABLEEXISTS]([MKTSEGMENTATION].[ID])
        from dbo.[MKTSEGMENTATIONTESTSEGMENT]
        inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[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;

        --If the mailing is using a segmented house file, then do not allow the user to change the sample size...

        if @SEGMENTEDHOUSEFILEEXISTS = 1
          set @SAMPLESIZELOADED = 0;

        /* Grab the old packageID info before we save the test segment */
        if @PACKAGELOADED = 1 or @SAMPLESIZELOADED = 1 or @ASKLADDERLOADED = 1
          select 
            @PARENTSEGMENTID = [TESTSEG].[SEGMENTID],
          @OLDSEGMENTATIONPACKAGEID = (select [PAK].[ID] from dbo.[MKTSEGMENTATIONPACKAGE] as [PAK] where [PAK].[SEGMENTATIONID] = [SEG].[SEGMENTATIONID] and [PAK].[PACKAGEID] = [TESTSEG].[PACKAGEID]),
            @OLDPACKAGEID = [TESTSEG].[PACKAGEID],
            @OLDSAMPLESIZE = [TESTSEG].[SAMPLESIZE],
            @OLDSAMPLESIZETYPECODE = [TESTSEG].[SAMPLESIZETYPECODE],
            @OLDSAMPLESIZEMETHODCODE = [TESTSEG].[SAMPLESIZEMETHODCODE],
            @OLDFRACTION  = [TESTSEG].[FRACTION],
            @OLDASKLADDERID = [TESTSEG].[ASKLADDERID]
          from dbo.[MKTSEGMENTATIONTESTSEGMENT] as [TESTSEG]
          inner join dbo.[MKTSEGMENTATIONSEGMENT] as [SEG] on [SEG].[ID] = [TESTSEG].[SEGMENTID]
          where [TESTSEG].[ID] = @TESTSEGMENTID;

        if @ASKLADDERLOADED = 1
          select
            @ASKLADDERID = T.c.value('(ASKLADDERID)[1]','uniqueidentifier')
          from @ASKLADDER.nodes('/ASKLADDER/ITEM') T(c);

        /* Save the test segment */
        update dbo.[MKTSEGMENTATIONTESTSEGMENT] set
          [ASKLADDERID] = (case when @ASKLADDERLOADED = 1 then @ASKLADDERID else [ASKLADDERID] end),
          [TESTSEGMENTCODE] = (case when @TESTSEGMENTCODELOADED = 1 then @TESTSEGMENTCODE else [TESTSEGMENTCODE] end),
          [TESTPARTDEFINITIONVALUESID] = (case when @TESTSEGMENTCODELOADED = 1 then @TESTSEGMENTCODEVALUEID else [TESTPARTDEFINITIONVALUESID] end),
          [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),
          [PREFIXCODE] = (case when @PREFIXCODELOADED = 1 then @PREFIXCODE else [PREFIXCODE] end),
          [FRACTION] = (case when @SAMPLESIZELOADED = 1 then case when @SAMPLESIZETYPECODE = 2 then @FRACTION else '' end else [FRACTION] 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 */
            exec dbo.[USP_MKTPACKAGE_UPDATECODE] @PACKAGEID, @PACKAGECODE, @PACKAGECODEVALUEID, @CHANNELSOURCECODE, @CHANNELSOURCECODEVALUEID, @CHANGEAGENTID, @CURRENTAPPUSERID;
          end

     if (@SAMPLESIZELOADED = 1 and (@OLDSAMPLESIZE <> @SAMPLESIZE or @OLDSAMPLESIZETYPECODE <> @SAMPLESIZETYPECODE or @OLDSAMPLESIZEMETHODCODE <> @SAMPLESIZEMETHODCODE or @OLDFRACTION <> @FRACTION)) or
           (@ASKLADDERLOADED = 1 and ((@OLDASKLADDERID is null and @ASKLADDERID is not null) or (@OLDASKLADDERID is not null and @ASKLADDERID is null) or (@OLDASKLADDERID <> @ASKLADDERID)))
          -- clear the cached information for the parent segment to force mailing data recalculation

          exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @PARENTSEGMENTID, 0, 1;

        /* insert new ids into item list for each test segment */
        if @ITEMLIST is not null
          begin
            declare @ITEMLISTTABLE table
            (
              [CODE] nvarchar(50),
              [ID] uniqueidentifier,
              [PARTDEFINITIONVALUESID] uniqueidentifier,
              [SEGMENTATIONID] uniqueidentifier,
              [SOURCECODEITEMID] uniqueidentifier
            );

            /* insert unique MKTSOURCECODEPART ID for each value in the itemlist */ 
            insert into @ITEMLISTTABLE
            select 
              [CODE], 
              newid(), 
              [PARTDEFINITIONVALUESID], 
              [SEGMENTATIONID], 
              [SOURCECODEITEMID]  
            from dbo.[UFN_MKTSOURCECODEPART_SEGMENTATIONSEGMENT_GETITEMLIST_FROMITEMLISTXML](@ITEMLIST);

            set @ITEMLIST = (select
                               [ID],
                               [SOURCECODEITEMID],
                               [PARTDEFINITIONVALUESID],
                               [CODE],
                               [SEGMENTATIONID]
                             from @ITEMLISTTABLE
                             for xml raw('ITEM'), type, elements, root('ITEMLIST'), binary base64);

            delete from @ITEMLISTTABLE;
          end

        /* Save the source code information */
        exec dbo.[USP_MKTSOURCECODEPART_SEGMENTATIONTESTSEGMENT_GETITEMLIST_UPDATEFROMXML] @TESTSEGMENTID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE;

         /* Save the business units information */
        if @SEGMENTTYPECODE in (1, 2) and @OVERRIDEBUSINESSUNITSLOADED = 1
          begin
            update dbo.[MKTSEGMENTATIONTESTSEGMENT] set 
              [OVERRIDEBUSINESSUNITS] = @OVERRIDEBUSINESSUNITS,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE
            where [ID] = @TESTSEGMENTID

            exec dbo.[USP_MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT_GETBUSINESSUNITS_UPDATEFROMXML] @TESTSEGMENTID, @BUSINESSUNITS, @CHANGEAGENTID;  
          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;