USP_DATAFORMTEMPLATE_ADD_MKTTESTCOMMUNICATIONEFFORT

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTTESTCOMMUNICATIONEFFORT]
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEGMENTATIONID uniqueidentifier,
  @EFFORTCOMMUNICATIONTEMPLATEID uniqueidentifier = null,
  @EFFORTNAME nvarchar(100) = '',
  @EFFORTNAMESCHEMETEXT xml = null,
  @EFFORTDESCRIPTION nvarchar(255) = '',
  @APPEALINFORMATION xml = null,
  @EFFORTCHANNELCODE tinyint = 255,
  @EFFORTSITEID uniqueidentifier = null,
  @EFFORTDUEDATE datetime = null,
  @EFFORTLAUNCHDATE datetime = null,
  @EFFORTALLOWRESERVINGFINDERNUMBERS bit = 0,
  @EFFORTALLOWSPECIFYBUDGET bit = 0,
  @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS bit = 0,
  @EFFORTBASECURRENCYID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SAMPLESIZE int = 5,
  @SAMPLESIZETYPECODE tinyint = 0,
  @COPYFORMAT nvarchar(100) = null
)
as
  set nocount on;

  declare @PARENTID uniqueidentifier = @SEGMENTATIONID;

  if @ID is null
    set @ID = newid();

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

  declare @CURRENTDATE datetime = getdate();

  begin try

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

    exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;

    --load default values for those that were locked and editable on the effort form

    declare @ISBBEC bit = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

    select
      @EFFORTDESCRIPTION = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTIONLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTION] else @EFFORTDESCRIPTION end),
      @EFFORTCHANNELCODE = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODELOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODE] else @EFFORTCHANNELCODE end),
      @EFFORTALLOWRESERVINGFINDERNUMBERS = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERSLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERS] else @EFFORTALLOWRESERVINGFINDERNUMBERS end),
      @EFFORTALLOWSPECIFYBUDGET = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGETLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGET] else @EFFORTALLOWSPECIFYBUDGET end),
      @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTS] else @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS end),
      @APPEALINFORMATION = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED] = 1 then (
          select 
            [MKTAPPEALRECORDSOURCE].[ID] as [RECORDSOURCEID],
            [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
            [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as [SEARCHLISTCATALOGID],
            [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [SEARCHLISTDESCRIPTIONFIELD],
            [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as [APPEALSYSTEMID],
            (case when @ISBBEC = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
              (select [NAME] from dbo.[APPEAL] where [ID] = cast([MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as uniqueidentifier))
              else
              [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALID]
              end) as [APPEALID],
            (case when @ISBBEC = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
              (select [DESCRIPTION] from dbo.[APPEAL] where [ID] = cast([MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as uniqueidentifier))
              else
              [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALDESCRIPTION]
              end) as [APPEALDESCRIPTION]
          from dbo.[MKTAPPEALRECORDSOURCE]
          inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
          left join [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] on [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID] 
            and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[COMMUNICATIONTEMPLATEID] = @EFFORTCOMMUNICATIONTEMPLATEID
          where (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1)
          for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64)
          else @APPEALINFORMATION end)
    from
      dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT]
    where
      [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = @EFFORTCOMMUNICATIONTEMPLATEID;

    --make sure the name is unique if it is a manually entered name

    if @EFFORTNAMESCHEMETEXT is null
      exec dbo.[USP_MKTCOMMUNICATIONEFFORT_GETUNIQUENAME] 
        @COMMUNICATIONNAMESCHEMEID = null,
        @EFFORTNAMESCHEMETEXT = null,
        @EFFORTNAME = @EFFORTNAME output,
        @COUNTERVALUE = '1',
        @COUNTER = 1;

    --@SEGMENTATIONID goes in as the SOURCE SEGMENTATION ID, and comes out as the COPY's ID

    exec dbo.[USP_MKTSEGMENTATION_COPY_HELPER] @SEGMENTATIONID output, @CURRENTAPPUSERID, @CHANGEAGENTID, 0, null, @COPYFORMAT, @EFFORTNAMESCHEMETEXT;
    set @ID = @SEGMENTATIONID;

    --update the new effort with values the user is able to change

    update
      dbo.[MKTSEGMENTATION]
    set
      [DESCRIPTION] = @EFFORTDESCRIPTION,
      [CHANNELCODE] = @EFFORTCHANNELCODE,
      [SITEID] = @EFFORTSITEID,
      [MAILDATE] = @EFFORTLAUNCHDATE,
      [DUEDATE] = @EFFORTDUEDATE,
      [ALLOWRESERVINGFINDERNUMBERS] = @EFFORTALLOWRESERVINGFINDERNUMBERS,
      [ALLOWSPECIFYBUDGET] = @EFFORTALLOWSPECIFYBUDGET,
      [ALLOWEXCLUDEPREVIOUSEFFORTS] = @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS,
      [NAME] = (case when @EFFORTNAMESCHEMETEXT is null then @EFFORTNAME else [NAME] end),
      [PARENTSEGMENTATIONID] = @PARENTID,
      [SAMPLESIZE] = @SAMPLESIZE,
      [SAMPLESIZETYPECODE] = @SAMPLESIZETYPECODE,
      [DATECHANGED] = [DATECHANGED],
      [CHANGEDBYID] = [CHANGEDBYID]
    where
      [ID] = @ID;

    --save appeal information

    exec dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD_FROMXML]
      @ID,
      @APPEALINFORMATION,
      @CHANGEAGENTID;

    --Copy in any test mailings as exclusions on the parent mailing...

    declare @PREVIOUSSEGMENTATIONID uniqueidentifier;
    declare SEGMENTATIONFILTERCURSOR cursor local fast_forward for
      select [ID]
      from [MKTSEGMENTATION]
      where [PARENTSEGMENTATIONID] = @PARENTID
      and [ACTIVE] = 1;

    open SEGMENTATIONFILTERCURSOR;
    fetch next from SEGMENTATIONFILTERCURSOR into @PREVIOUSSEGMENTATIONID;

    while (@@FETCH_STATUS = 0)
    begin
      if not exists(select [ID] from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION] where [SEGMENTATIONID] = @ID and [PREVIOUSSEGMENTATIONID] = @PREVIOUSSEGMENTATIONID)
        exec dbo.[USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONFILTERSEGMENTATION] null, @CHANGEAGENTID, @ID, @PREVIOUSSEGMENTATIONID;

      fetch next from SEGMENTATIONFILTERCURSOR into @PREVIOUSSEGMENTATIONID;
    end;

    close SEGMENTATIONFILTERCURSOR;
    deallocate SEGMENTATIONFILTERCURSOR;

    --Set all the segments to the correct sample size (only for non-vendormanaged segments)...

    update dbo.[MKTSEGMENTATIONSEGMENT] set
      [SAMPLESIZE] = @SAMPLESIZE,
      [SAMPLESIZETYPECODE] = @SAMPLESIZETYPECODE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [SS].[SEGMENTID]
    where [SS].[SEGMENTATIONID] = @ID
    and ([MKTSEGMENT].[SEGMENTTYPECODE] <> 2 or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) = 0));

    -- BTR CR287449-110707 11/20/2007

    -- respect sample size when creating test mailings for mailings with vendor managed list segments

    declare @SEGMENTATIONSEGMENTID uniqueidentifier;
    declare @SEGMENTID uniqueidentifier;
    declare @RENTALQUANTITY integer;
    declare @EXCHANGEQUANTITY integer;
    declare @BASERENTALCOST money;
    declare @BASERENTALCOSTBASISCODE tinyint;
    declare @BASEEXCHANGECOST money;
    declare @BASEEXCHANGECOSTBASISCODE tinyint;
    declare @RENTALCOSTADJUSTMENT money;
    declare @RENTALCOSTADJUSTMENTBASISCODE tinyint;
    declare @EXCHANGECOSTADJUSTMENT money;
    declare @EXCHANGECOSTADJUSTMENTBASISCODE tinyint;

    declare SEGMENTCURSOR cursor local fast_forward for
      select [MKTSEGMENTATIONSEGMENT].[ID], [MKTSEGMENT].[ID]
      from dbo.[MKTSEGMENTATIONSEGMENT]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
      where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID
      and [MKTSEGMENT].[SEGMENTTYPECODE] = 2
      and dbo.[UFN_MKTSEGMENT_ISVENDORMANAGEDLIST]([MKTSEGMENT].[ID]) = 1;

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

    while (@@FETCH_STATUS = 0)
    begin
      if exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENTLIST] where [ID] = @SEGMENTATIONSEGMENTID)
        begin
          select
            @RENTALQUANTITY = [RENTALQUANTITY],
            @EXCHANGEQUANTITY = [EXCHANGEQUANTITY]
          from dbo.[MKTSEGMENTATIONSEGMENTLIST]
          where [ID] = @SEGMENTATIONSEGMENTID;

          update dbo.[MKTSEGMENTATIONSEGMENTLIST] set
            [OVERRIDEQUANTITIESANDORSEGMENTCOSTS] = 1,
            [RENTALQUANTITY] = (case when @SAMPLESIZETYPECODE = 0 then -- percent

                                  floor(cast(@RENTALQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
                                else -- records

                                  (case when @SAMPLESIZE < @RENTALQUANTITY then @SAMPLESIZE else @RENTALQUANTITY end)
                                end),
            [EXCHANGEQUANTITY] = (case when @SAMPLESIZETYPECODE = 0 then -- percent

                                    floor(cast(@EXCHANGEQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
                                  else -- records

                                    (case when @SAMPLESIZE < @EXCHANGEQUANTITY then @SAMPLESIZE else @EXCHANGEQUANTITY end)
                                  end),
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
          where [ID] = @SEGMENTATIONSEGMENTID;
        end;
      else
        begin
          select
            @RENTALQUANTITY = [MKTSEGMENTLIST].[RENTALQUANTITY], 
            @EXCHANGEQUANTITY = [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
            @BASERENTALCOST = [MKTLIST].[BASERENTALCOST],
            @BASERENTALCOSTBASISCODE = [MKTLIST].[BASERENTALCOSTBASISCODE],
            @BASEEXCHANGECOST = [MKTLIST].[BASEEXCHANGECOST],
            @BASEEXCHANGECOSTBASISCODE = [MKTLIST].[BASEEXCHANGECOSTBASISCODE],
            @RENTALCOSTADJUSTMENT = [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
            @RENTALCOSTADJUSTMENTBASISCODE = [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
            @EXCHANGECOSTADJUSTMENT = [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
            @EXCHANGECOSTADJUSTMENTBASISCODE = [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE]
          from dbo.[MKTSEGMENT]
          inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
          inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
          where [MKTSEGMENT].[ID] = @SEGMENTID;

          insert into dbo.[MKTSEGMENTATIONSEGMENTLIST] (
            [ID],
            [OVERRIDEQUANTITIESANDORSEGMENTCOSTS],
            [OVERRIDELISTCOSTS],
            [BASERENTALCOST],
            [BASERENTALCOSTBASISCODE],
            [RENTALQUANTITY],
            [RENTALCOSTADJUSTMENT],
            [RENTALCOSTADJUSTMENTBASISCODE],
            [BASEEXCHANGECOST],
            [BASEEXCHANGECOSTBASISCODE],
            [EXCHANGEQUANTITY],
            [EXCHANGECOSTADJUSTMENT],
            [EXCHANGECOSTADJUSTMENTBASISCODE],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          ) values (
            @SEGMENTATIONSEGMENTID,
            1,
            0,
            @BASERENTALCOST,
            @BASERENTALCOSTBASISCODE,
            (case when @SAMPLESIZETYPECODE = 0 then 
              floor(cast(@RENTALQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
            else
              (case when @SAMPLESIZE < @RENTALQUANTITY then @SAMPLESIZE else @RENTALQUANTITY end)
            end),
            @RENTALCOSTADJUSTMENT,
            @RENTALCOSTADJUSTMENTBASISCODE,
            @BASEEXCHANGECOST,
            @BASEEXCHANGECOSTBASISCODE,
            (case when @SAMPLESIZETYPECODE = 0 then 
              floor(cast(@EXCHANGEQUANTITY as decimal(20,5)) * (cast(@SAMPLESIZE as decimal(20,5)) / 100))
            else
              (case when @SAMPLESIZE < @EXCHANGEQUANTITY then @SAMPLESIZE else @EXCHANGEQUANTITY end)
            end),
            @EXCHANGECOSTADJUSTMENT,
            @EXCHANGECOSTADJUSTMENTBASISCODE,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );
        end;

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

  end try

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

  return 0;