USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_APPEALMAILINGSETUP
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @MAILDATE datetime = null,
  @MAILINGBUDGET money = 0,
  @APPEALID uniqueidentifier,
  @SELECTEDSELECTIONS xml = null,
  @EXCLUSIONS xml = null,
  @EXCLUDESELECTIONS xml = null,
  @EXCLUSIONDATETYPECODE tinyint = 0,
  @EXCLUSIONASOFDATE datetime = null,
  @CHANNELCODE tinyint = 0,
  @CHANNELPREFERENCECODE tinyint = 0,
  @MAILPACKAGEID uniqueidentifier = null,
  @EMAILPACKAGEID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @HOUSEHOLDINGTYPECODE tinyint = 0,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0 ,
  @CREATEOUTPUTIDSET bit = 0,
  @OUTPUTIDSETNAME nvarchar(100) = '',
  @OVERWRITEOUTPUTIDSET bit = 0,
  @FINALSAVECODE tinyint = 0,
  @OUTPUTPATH nvarchar(256) = '',
  @MKTASKLADDERID uniqueidentifier = null,
  @ESTIMATEDRESPONSERATE decimal(5, 2) = 0,
  @ESTIMATEDAVERAGEGIFTAMOUNT money = 0
)
as
begin
  declare @CURRENTDATE datetime;
  declare @ACTIVE bit;
  declare @OLDNAME nvarchar(100);
  declare @OLDEXCLUSIONS as xml;
  declare @OLDEXCLUSIONDATETYPECODE as tinyint;
  declare @OLDEXCLUSIONASOFDATE as datetime;
  declare @OLDUSEADDRESSPROCESSING bit;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
  declare @OLDADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @OLDNAMEFORMATPARAMETERID uniqueidentifier;
  declare @OLDHOUSEHOLDINGTYPECODE tinyint;
  declare @OLDEXCLUDEDECEASED as bit;
  declare @OLDEXCLUDEINACTIVE as bit;
  declare @OLDMAILPACKAGEID uniqueidentifier;
  declare @OLDEMAILPACKAGEID uniqueidentifier;
  declare @ADDRESSPROCESSINGOPTIONSCHANGED bit;
  declare @EXCLUSIONSCHANGED bit;
  declare @APPEALNAME nvarchar(100);
  declare @APPEALDESCRIPTION nvarchar(255);
  declare @SITEID uniqueidentifier;
  declare @EMAILEXPORTDEFINITIONID uniqueidentifier;
  declare @MAILEXPORTDEFINITIONID uniqueidentifier;
  declare @SEGMENTATIONEXPORTPROCESSID uniqueidentifier;
  declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;

  begin try
    set @CURRENTDATE = getDate();

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

    exec dbo.[USP_COMMUNICATIONS_CREATEORUPDATEMAILING]    
      @ID,
      1,
      1,
      @NAME,
      @DESCRIPTION,
      @MAILINGBUDGET,
      @MAILDATE,
      @SELECTEDSELECTIONS,
      @EXCLUSIONS,
      @CHANNELCODE,
      @CHANNELPREFERENCECODE,
      @EMAILPACKAGEID,
      @MAILPACKAGEID,
      @MKTASKLADDERID,
      @ADDRESSPROCESSINGOPTIONID,
      @NAMEFORMATPARAMETERID,
      @HOUSEHOLDINGTYPECODE,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
      @APPEALID,
      @CREATEOUTPUTIDSET,
      @OUTPUTIDSETNAME,
      @OVERWRITEOUTPUTIDSET,
      @CURRENTAPPUSERID,
      @CHANGEAGENTID,
      null, -- PACKAGESITEID

      @EXCLUDESELECTIONS,
      @EXCLUSIONDATETYPECODE,
      @EXCLUSIONASOFDATE,
      @ESTIMATEDRESPONSERATE,
      @ESTIMATEDAVERAGEGIFTAMOUNT;

    -- Update appeal mailing information...

    update dbo.[APPEALMAILING] set
      [APPEALID] = @APPEALID,
      [MKTSEGMENTATIONSEGMENTID] = null,
      [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
      [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
      [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where
      [ID] = @ID;

    --Because the appeal mailing name uniqueness depends on the appeal, we need to check for name uniqueness after the appeal mailing is created.

    if dbo.[UFN_MKTSEGMENTATION_NAMEEXISTS](@NAME, 0, 1) = 1
      begin
        --Name is not unique, throw error.

        raiserror('BBERR_MKTSEGMENTATION_VALIDNAME', 13, 1);
      end

    -- If not Enterprise, update AppealMailingSetup table

    if dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 0
      begin
        if exists (select [ID] from dbo.[APPEALMAILINGSETUP] where [ID] = @ID)
          begin
            update dbo.[APPEALMAILINGSETUP] set
              [CHANNELCODE] = @CHANNELCODE,
              [CHANNELPREFERENCECODE] = @CHANNELPREFERENCECODE,
              [MAILPACKAGEID] = @MAILPACKAGEID,
              [EMAILPACKAGEID] = @EMAILPACKAGEID,
              [ESTIMATEDRESPONSERATE] = @ESTIMATEDRESPONSERATE,
              [ESTIMATEDAVERAGEGIFTAMOUNT] = @ESTIMATEDAVERAGEGIFTAMOUNT,
              [CHANGEDBYID] = @CHANGEAGENTID,
              [DATECHANGED] = @CURRENTDATE,
              [MKTASKLADDERID] = @MKTASKLADDERID
            where
              [ID] = @ID;

            exec dbo.[USP_APPEALMAILINGSETUP_GETSELECTIONS_UPDATEFROMXML] @ID, @SELECTEDSELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
          end
        else
          begin
            insert into dbo.[APPEALMAILINGSETUP]
            (
              [ID], 
              [CHANNELCODE], 
              [CHANNELPREFERENCECODE], 
              [MAILPACKAGEID], 
              [EMAILPACKAGEID], 
              [ESTIMATEDRESPONSERATE], 
              [ESTIMATEDAVERAGEGIFTAMOUNT], 
              [ADDEDBYID], 
              [CHANGEDBYID], 
              [DATEADDED], 
              [DATECHANGED], 
              [MKTASKLADDERID]
            )
            values
            (
              @ID, 
              @CHANNELCODE, 
              @CHANNELPREFERENCECODE, 
              @MAILPACKAGEID, 
              @EMAILPACKAGEID, 
              @ESTIMATEDRESPONSERATE, 
              @ESTIMATEDAVERAGEGIFTAMOUNT, 
              @CHANGEAGENTID, 
              @CHANGEAGENTID, 
              @CURRENTDATE,
              @CURRENTDATE, 
              @MKTASKLADDERID
            );

            exec dbo.[USP_APPEALMAILINGSETUP_GETSELECTIONS_ADDFROMXML] @ID, @SELECTEDSELECTIONS, @CHANGEAGENTID, @CURRENTDATE;
          end
      end

    -- Always activate KPIs

    -- Load KPI values

    declare @KpiTable as table
    (
      [KPICATALOGID] uniqueidentifier,
      [SELECTED] bit,
      [NAME] nvarchar(255),
      [GOALTYPECODE] tinyint,
      [DEFAULT] bit,
      [LOCKED] bit,
      [TEMPLATETYPECODE] tinyint
    );

    insert into @KpiTable
      exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_GETFIELDS] null, 0

    declare @ACTIVATIONKPIS xml;
    set @ACTIVATIONKPIS = (
      select 
        [KPICATALOGID],
        [SELECTED],
        [NAME],
        [GOALTYPECODE],
        [DEFAULT]
      from
        @KpiTable 
      for xml raw('ITEM'),type,elements,root('ACTIVATIONKPIS'),BINARY BASE64);

    /* Clear KPI fields */
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_CLEARFIELDS]
      @ID,
      1,
      @CHANGEAGENTID

    /* Save KPIs */
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML] 
      @ID, 
      @ACTIVATIONKPIS, 
      1, 
      @CHANGEAGENTID,
      @CURRENTAPPUSERID;

  end try

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

  return 0;
end