USP_APPEALMAILING_COPY

CREATE procedure dbo.[USP_APPEALMAILING_COPY]
(
  @ID uniqueidentifier output,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @NEWAPPEALMAILINGID uniqueidentifier;
  declare @APPEALID uniqueidentifier;
  declare @NAME nvarchar(100);
  declare @DESCRIPTION nvarchar(255);
  declare @MAILDATE datetime;
  declare @FIXEDCOST money;
  declare @MKTPACKAGEID uniqueidentifier;
  declare @IDSETREGISTERID uniqueidentifier;
  declare @HOUSEHOLDINGTYPECODE tinyint;
  declare @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit;
  declare @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit;
  declare @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit;
  declare @EXCLUSIONDATETYPECODE tinyint;
  declare @EXCLUSIONASOFDATE datetime;
  declare @EXCLUDEDECEASED bit;
  declare @EXCLUDEINACTIVE bit;
  declare @EXCLUSIONS xml;
  declare @USEADDRESSPROCESSING bit;
  declare @ADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @NAMEFORMATPARAMETERID uniqueidentifier;
  declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
  declare @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
  declare @LABELFILENAME nvarchar(255);
  declare @LABELFILE varbinary(max);
  declare @CREATEOUTPUTIDSET bit;
  declare @OUTPUTIDSETNAME nvarchar(100);
  declare @OVERWRITEOUTPUTIDSET bit;
  declare @ISHYBRID bit;
  declare @MAILINGBUDGET money;
  declare @SELECTEDSELECTIONS xml;
  declare @EXCLUDEDSELECTIONS xml;
  declare @CHANNELCODE tinyint;
  declare @CHANNELPREFERENCECODE tinyint;
  declare @MAILPACKAGEID uniqueidentifier;
  declare @EMAILPACKAGEID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @MKTASKLADDERID uniqueidentifier;

  declare @LETTERS xml;
  declare @MAILEXPORTDEFINITIONID uniqueidentifier;
  declare @EMAILEXPORTDEFINITIONID uniqueidentifier;

  begin try
    set @NEWAPPEALMAILINGID = newid();

    --Copy KPIs from existing mailing

    declare @ACTIVATIONKPIS xml;
    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] @ID, 1;

    set @ACTIVATIONKPIS = 
      (select 
         [KPICATALOGID],
         [SELECTED],
         [NAME],
         [GOALTYPECODE],
         [DEFAULT]
       from @KPITABLE 
       where [SELECTED] = 1
       for xml raw('ITEM'), type, elements, root('ACTIVATIONKPIS'), binary base64);

    --Grab the source appeal mailing's information...

    select
      @APPEALID = [APPEALMAILING].[APPEALID],
      @NAME = dbo.[UFN_MKTSEGMENTATION_GETUNIQUENAME](@NEWAPPEALMAILINGID, [MKTSEGMENTATION].[NAME], null),
      @DESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
      @MAILDATE = [MKTSEGMENTATION].[MAILDATE],
      @FIXEDCOST = [MKTSEGMENTATIONBUDGET].[FIXEDCOST],
      @MKTPACKAGEID = [MKTSEGMENTATIONSEGMENT].[PACKAGEID],
      @IDSETREGISTERID = [MKTSEGMENTSELECTION].[SELECTIONID],
      @HOUSEHOLDINGTYPECODE = 
        case when [APPEALMAILINGSETUP].[ID] is null
          then [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE]
          else [APPEALMAILINGSETUP].[HOUSEHOLDINGTYPECODE]
        end,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
      @EXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
      @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
      @EXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
      @EXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
      @EXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
      @USEADDRESSPROCESSING = MKTSEGMENTATION.USEADDRESSPROCESSING,
      @ADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
      @NAMEFORMATPARAMETERID = [MKTSEGMENTATION].[NAMEFORMATPARAMETERID],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @LABELFILENAME = [APPEALMAILING].[LABELFILENAME],
      @LABELFILE = [APPEALMAILING].[LABELFILE],
      @CREATEOUTPUTIDSET = [MKTSEGMENTATION].[CREATEOUTPUTIDSET],
      @OUTPUTIDSETNAME = (case when [MKTSEGMENTATION].[CREATEOUTPUTIDSET] = 1 and [MKTSEGMENTATION].[OVERWRITEOUTPUTIDSET] = 0 and dbo.UFN_BUSINESSPROCESS_IDSETEXISTS(MKTSEGMENTATION.OUTPUTIDSETNAME) = 1 then dbo.[UFN_MKTSELECTION_GETUNIQUENAME]([MKTSEGMENTATION].[OUTPUTIDSETNAME]) else [MKTSEGMENTATION].[OUTPUTIDSETNAME] end),
      @OVERWRITEOUTPUTIDSET = [MKTSEGMENTATION].[OVERWRITEOUTPUTIDSET],
      @ISHYBRID = (case when [APPEALMAILINGSETUP].[ID] is not null then 1 else 0 end),
      @MAILINGBUDGET = MKTSEGMENTATIONBUDGET.BUDGETAMOUNT,
      @SELECTEDSELECTIONS = isnull(dbo.UFN_APPEALMAILINGSETUP_GETSELECTIONS_TOITEMLISTXML(APPEALMAILINGSETUP.ID), dbo.[UFN_MKTSEGMENT_GETSELECTIONS_TOITEMLISTXML]([MKTSEGMENTATIONSEGMENT].[SEGMENTID], @CURRENTAPPUSERID)),
      @EXCLUDEDSELECTIONS = dbo.[UFN_MKTSEGMENTATIONFILTERSELECTION_GETEXCLUDESELECTIONS_TOITEMLISTXML]([MKTSEGMENTATION].[ID]),
      @CHANNELCODE = [APPEALMAILINGSETUP].[CHANNELCODE],
      @CHANNELPREFERENCECODE = [APPEALMAILINGSETUP].[CHANNELPREFERENCECODE],
      @MAILPACKAGEID = [APPEALMAILINGSETUP].[MAILPACKAGEID],
      @EMAILPACKAGEID = [APPEALMAILINGSETUP].[EMAILPACKAGEID],
      @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID],
      @MKTASKLADDERID = [APPEALMAILINGSETUP].[MKTASKLADDERID],
      @MAILEXPORTDEFINITIONID = [APPEALMAILINGSETUP].[MAILEXPORTDEFINITIONID],
      @EMAILEXPORTDEFINITIONID = [APPEALMAILINGSETUP].[EMAILEXPORTDEFINITIONID]
    from dbo.[APPEALMAILING]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [APPEALMAILING].[ID]
    inner join dbo.[MKTSEGMENTATIONBUDGET] on [MKTSEGMENTATIONBUDGET].[ID] = [MKTSEGMENTATION].[ID]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    inner join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    left join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [APPEALMAILING].[MKTSEGMENTATIONSEGMENTID]
    left join dbo.[MKTSEGMENTSELECTION] on [MKTSEGMENTSELECTION].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]
    left join dbo.[APPEALMAILINGSETUP] on [APPEALMAILINGSETUP].[ID] = [APPEALMAILING].[ID]
    where [APPEALMAILING].[ID] = @ID;

    --Delete the exclusion IDs from the xml so it will create new exclusions when we save below...

    if @EXCLUSIONS is not null
      set @EXCLUSIONS.modify('delete /EXCLUSIONS/ITEM/ID');

    if @ISHYBRID = 0
      begin
        --Create a copy of the source appeal mailing....

        exec dbo.[USP_DATAFORMTEMPLATE_ADD_APPEALMAILING]
          @ID = @NEWAPPEALMAILINGID,
          @APPEALID = @APPEALID,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @NAME = @NAME,
          @DESCRIPTION = @DESCRIPTION,
          @MAILDATE = @MAILDATE,
          @FIXEDCOST = @FIXEDCOST,
          @MAILINGBUDGET = @MAILINGBUDGET,
          @MKTPACKAGEID = @MKTPACKAGEID,
          @IDSETREGISTERID = @IDSETREGISTERID,
          @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
          @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
          @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
          @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,
          @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
          @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
          @EXCLUDEDECEASED = @EXCLUDEDECEASED,
          @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
          @EXCLUSIONS = @EXCLUSIONS,
          @LABELFILENAME = @LABELFILENAME,
          @LABELFILE = @LABELFILE,
          @CREATEOUTPUTIDSET = @CREATEOUTPUTIDSET,
          @OUTPUTIDSETNAME = @OUTPUTIDSETNAME,
          @OVERWRITEOUTPUTIDSET = @OVERWRITEOUTPUTIDSET,
          @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
          @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
          @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
          @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
          @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
          @USEKPISASDEFAULT = 0,
          @ACTIVATIONKPIS = @ACTIVATIONKPIS,
          @BASECURRENCYID = @BASECURRENCYID;
      end
    else
      begin

        set @LETTERS = (
          SELECT 
            newID() as ID,
            COMMUNICATIONLETTER.RUNNOW,
            COMMUNICATIONLETTER.RUNSCHEDULED,
            COMMUNICATIONLETTER.SEQUENCE,
            COMMUNICATIONLETTER.NAME,
            COMMUNICATIONLETTER.CHANNELCODE,
            COMMUNICATIONLETTER.CHANNELPREFERENCECODE,
            COMMUNICATIONLETTER.OUTPUTTYPECODE,
            COMMUNICATIONLETTER.INCLUDEINACTIVE,
            COMMUNICATIONLETTER.MKTASKLADDERID,
            APPEALMAILINGSETUP.MAILEXPORTDEFINITIONID,
            COMMUNICATIONLETTER.MAILCONTENTHTML,
            APPEALMAILINGSETUP.EMAILEXPORTDEFINITIONID,
            COMMUNICATIONLETTER.EMAILCONTENTHTML,
            COMMUNICATIONLETTER.EMAILSUBJECT,
            COMMUNICATIONLETTER.EMAILFROMADDRESS,
            COMMUNICATIONLETTER.EMAILFROMDISPLAYNAME,
            COMMUNICATIONLETTER.EMAILREPLYTOADDRESS,
            COMMUNICATIONLETTER.PAPERSIZECODE,
            COMMUNICATIONLETTER.MARGINTOP,
            COMMUNICATIONLETTER.MARGINBOTTOM,
            COMMUNICATIONLETTER.MARGINLEFT,
            COMMUNICATIONLETTER.MARGINRIGHT,
            null as MAILPACKAGEID,
            null as MAILSEGMENTID,
            null as EMAILPACKAGEID,
            null as EMAILSEGMENTID,
            cast((
              SELECT 
                null as ID, 
                [NAME], 
                [SELECTIONID]
              FROM dbo.[UFN_COMMUNICATIONLETTER_GETINCLUDEDSELECTIONS](COMMUNICATIONLETTER.ID)
              for xml raw('ITEM'),type,elements,root('SELECTIONS'),BINARY BASE64
            ) as nvarchar(max)) as SELECTIONSXML,
            cast((
              SELECT 
                newID() as ID, 
                [NAME], 
                [SELECTIONID]
              FROM dbo.[UFN_COMMUNICATIONLETTER_GETEXCLUDEDSELECTIONS](COMMUNICATIONLETTER.ID)
              for xml raw('ITEM'),type,elements,root('EXCLUDEDSELECTIONS'),BINARY BASE64
            ) as nvarchar(max)) as EXCLUDEDSELECTIONSXML,
            cast((
              SELECT 
                newID() as ID, 
                [NAME], 
                [SOLICITCODEID], 
                [TYPE]
              FROM dbo.[UFN_COMMUNICATIONLETTER_GETEXCLUDEDSOLICITCODES](COMMUNICATIONLETTER.ID)
              for xml raw('ITEM'),type,elements,root('EXCLUDEDSOLICITCODES'),BINARY BASE64
            ) as nvarchar(max)) as EXCLUDEDSOLICITCODESXML,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONRECENTCOMMUNICATION,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMRECENTCOMMUNICATIONPERIODS,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.RECENTCOMMUNICATIONPERIODTYPECODE,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMTOTALCOMMUNICATIONSINPASTYEAR,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONRECENTGIVING,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.NUMRECENTGIVINGPERIODS,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.RECENTGIVINGPERIODTYPECODE,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
            COMMUNICATIONLETTERACTIVITYEXCLUSIONS.TOTALREVENUEAMOUNTINPASTYEAR,           
            cast((
              SELECT 
                newID() as ID, 
                [COMMUNICATIONTYPE], 
                [COMMUNICATIONTYPECODE]
              FROM dbo.[UFN_COMMUNICATIONLETTERACTIVITYEXCLUSION_GETCOMMUNICATIONTYPES](COMMUNICATIONLETTERACTIVITYEXCLUSIONS.ID)
              for xml raw('ITEM'),type,elements,root('COMMUNICATIONTYPES'),BINARY BASE64
            ) as nvarchar(max)) as COMMUNICATIONTYPESXML
          from dbo.COMMUNICATIONLETTER
          left outer join dbo.APPEALMAILINGSETUP
            on COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILINGSETUP.ID
          left outer join dbo.COMMUNICATIONLETTERACTIVITYEXCLUSIONS
            on COMMUNICATIONLETTER.ID = COMMUNICATIONLETTERACTIVITYEXCLUSIONS.COMMUNICATIONLETTERID    
          where SEGMENTATIONID = @ID
          order by SEQUENCE
          for xml raw('ITEM'),type,elements,root('LETTERS'),BINARY BASE64
        )

        -- Create a copy of the hybrid appeal mailing...

        exec dbo.[USP_DATAFORMTEMPLATE_ADD_APPEALMAILINGSETUP_2]
          @ID = @NEWAPPEALMAILINGID,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @NAME = @NAME,
          @DESCRIPTION = @DESCRIPTION,
          @MAILDATE = @MAILDATE,
          @APPEALID = @APPEALID,
          @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
          @NAMEFORMATPARAMETERID = @NAMEFORMATPARAMETERID,
          @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
          @MAILEXPORTDEFINITIONID = @MAILEXPORTDEFINITIONID,
          @EMAILEXPORTDEFINITIONID = @EMAILEXPORTDEFINITIONID,
          @LETTERS = @LETTERS,
          @CREATEOUTPUTIDSET = @CREATEOUTPUTIDSET,
          @OUTPUTIDSETNAME = @OUTPUTIDSETNAME,
          @OVERWRITEOUTPUTIDSET = @OVERWRITEOUTPUTIDSET;
      end

    --Return the new appeal mailing ID...

    set @ID = @NEWAPPEALMAILINGID;
  end try

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

  return 0;