USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONSEGMENT_MULTIPLE_3

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

  declare @SEGMENTSTABLE table([ID] uniqueidentifier);
  declare @START int;
  declare @POS int;
  declare @SEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @PACKAGECHANNELCODE tinyint;
  declare @OLDSEGMENTATIONPACKAGEID uniqueidentifier;
  declare @OLDPACKAGEID uniqueidentifier;
  declare @OLDPACKAGECHANNELCODE tinyint;
  declare @OLDSAMPLESIZE int;
  declare @OLDSAMPLESIZETYPECODE tinyint;
  declare @OLDSAMPLESIZEMETHODCODE tinyint;
  declare @OLDSAMPLESIZEEXCLUDEREMAINDER bit;
  declare @CURRENTDATE datetime;
  declare @USEADDRESSPROCESSING bit;
  declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @SEQUENCE integer;

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

    set @CURRENTDATE = GetDate();


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

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

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

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


    if @SAMPLESIZELOADED = 1 and @SAMPLESIZETYPECODE = 0 and @SAMPLESIZE = 100
      set @SAMPLESIZEEXCLUDEREMAINDER = 1;


    declare SEGMENTCURSOR cursor local fast_forward for
      select [SEG].[ID], [MKTSEGMENTATIONSEGMENT].[SEQUENCE]
      from @SEGMENTSTABLE as [SEG]
      inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [SEG].[ID]
      order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE] desc;

    open SEGMENTCURSOR;
    fetch next from SEGMENTCURSOR into @SEGMENTID, @SEQUENCE;

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

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

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

    if @SAMPLESIZELOADED = 1
        select
          @OLDSAMPLESIZE = [SAMPLESIZE],
          @OLDSAMPLESIZETYPECODE = [SAMPLESIZETYPECODE],
          @OLDSAMPLESIZEMETHODCODE = [SAMPLESIZEMETHODCODE],
          @OLDSAMPLESIZEEXCLUDEREMAINDER = [SAMPLESIZEEXCLUDEREMAINDER]
        from dbo.[MKTSEGMENTATIONSEGMENT]
        where [ID] = @SEGMENTID;

      /* Save the segment */
      update dbo.[MKTSEGMENTATIONSEGMENT] set
        [ASKLADDERID] = (case when @ASKLADDERLOADED = 1 then (select T.c.value('(ASKLADDERID)[1]','uniqueidentifier') AS 'ASKLADDERID' from @ASKLADDER.nodes('/ASKLADDER/ITEM') T(c)) else [ASKLADDERID] end),
        [TESTSEGMENTCODE] = (case when @TESTSEGMENTCODELOADED = 1 then @TESTSEGMENTCODE else [TESTSEGMENTCODE] 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),
        [SAMPLESIZEEXCLUDEREMAINDER] = (case when @SAMPLESIZELOADED = 1 then @SAMPLESIZEEXCLUDEREMAINDER else [SAMPLESIZEEXCLUDEREMAINDER] end),
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @SEGMENTID;

      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;

              select @PACKAGECHANNELCODE = [CHANNELCODE] from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID;

              if @OLDPACKAGECHANNELCODE <> @PACKAGECHANNELCODE and @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
                /* clear the cache for this segment and all segments after it, 
                   since the package's channel can affect record counts when address processing is used */
                exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTID, 0, 1;
            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

      if @SAMPLESIZELOADED = 1
        begin
          /* Clear the segment cache only if the sample size settings changed */
          if @OLDSAMPLESIZEEXCLUDEREMAINDER <> @SAMPLESIZEEXCLUDEREMAINDER or (@SAMPLESIZEEXCLUDEREMAINDER = 0 and (@OLDSAMPLESIZE <> @SAMPLESIZE or @OLDSAMPLESIZETYPECODE <> @SAMPLESIZETYPECODE or @OLDSAMPLESIZEMETHODCODE <> @SAMPLESIZEMETHODCODE))
            /* Clear the cache for this segment and all segments after it */
            exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTID, 0, 1;
        end

      fetch next from SEGMENTCURSOR into @SEGMENTID, @SEQUENCE;
    end;

    close SEGMENTCURSOR;
    deallocate SEGMENTCURSOR;
  end try

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

  return 0;