USP_APPEALMAILING_ADD

CREATE procedure dbo.[USP_APPEALMAILING_ADD]
(
  @ID uniqueidentifier = null output,
  @APPEALID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255) = '',
  @MAILDATE datetime = null,
  @FIXEDCOST money = 0,
  @MKTPACKAGEID uniqueidentifier = null,
  @IDSETREGISTERID uniqueidentifier = null,
  @HOUSEHOLDINGTYPECODE tinyint = 0,
  @EXCLUSIONDATETYPECODE tinyint = 0,
  @EXCLUSIONASOFDATE datetime = null,
  @EXCLUDEDECEASED bit = 1,
  @EXCLUDEINACTIVE bit = 1,
  @EXCLUSIONS xml = null,
  @LABELFILENAME nvarchar(255) = '',
  @LABELFILE varbinary(max) = null,
  @CREATEOUTPUTIDSET bit = 0,
  @OUTPUTIDSETNAME nvarchar(100) = '',
  @OVERWRITEOUTPUTIDSET bit = 0,
  @USEADDRESSPROCESSING bit = 0,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
  @NAMEFORMATPARAMETERID uniqueidentifier = null,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = 0,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null,
  @SEGMENTATIONGENERATORID uniqueidentifier = null,
  @UNIVERSEIDSETREGISTERID uniqueidentifier = null,
  @SEGMENTATIONGENERATORPACKAGEID uniqueidentifier = null,
  @USEKPISASDEFAULT bit = null,
  @ACTIVATIONKPIS xml = null,
  @MAILINGBUDGET money = 0,
  @RECENCYEXCLUSIONSMARTFIELDID uniqueidentifier = null,
  @RECENCYEXCLUSIONSELECTIONID uniqueidentifier = null,
  @RECENCYEXCLUSIONINUSE bit = 0,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = 0,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = 0,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = 0  
)
as
  set nocount on;

  declare @APPEALNAME nvarchar(100);
  declare @APPEALDESCRIPTION nvarchar(255);
  declare @SITEID uniqueidentifier;
  --declare @MKTSEGMENTATIONSEGMENTID uniqueidentifier; --TommyVe 6/2/2009 @MKTSEGMENTATIONSEGMENTID appears to not be used, it is only selected

  declare @MKTEXPORTDEFINITIONID uniqueidentifier;
  declare @CURRENTDATE datetime;

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

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

    set @CURRENTDATE = getdate();

    select
      @APPEALNAME = [NAME],
      @APPEALDESCRIPTION = [DESCRIPTION],
      @SITEID = [SITEID]
    from dbo.[APPEAL]
    where [ID] = @APPEALID;

    select
      @MKTEXPORTDEFINITIONID = [LETTERCODE].[MKTEXPORTDEFINITIONID]
    from dbo.[MKTPACKAGE]
    inner join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
    where [MKTPACKAGE].[ID] = @MKTPACKAGEID;

    --Insert the base mailing...

    insert into dbo.[MKTSEGMENTATION] (
      [ID],
      [NAME],
      [DESCRIPTION],
      [MAILDATE],
      [SITEID],
      [HOUSEHOLDINGTYPECODE],
      [HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
      [HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
      [HOUSEHOLDINGONERECORDPERHOUSEHOLD],      
      [USEADDRESSPROCESSING],
      [ADDRESSPROCESSINGOPTIONID],
      [NAMEFORMATPARAMETERID],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      [MANUALCALCULATIONMODE],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
      @NAME,
      @DESCRIPTION,
      @MAILDATE,
      @SITEID,
      @HOUSEHOLDINGTYPECODE,
      @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
      @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
      @HOUSEHOLDINGONERECORDPERHOUSEHOLD,      
      @USEADDRESSPROCESSING,
      @ADDRESSPROCESSINGOPTIONID,
      @NAMEFORMATPARAMETERID,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
      @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      (case when @SEGMENTATIONGENERATORID is null then 0 else 1 end),
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    --Insert the budget information...

    insert into dbo.[MKTSEGMENTATIONBUDGET] (
      [ID],
    [BUDGETAMOUNT],
      [FIXEDCOST],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      @ID,
    @MAILINGBUDGET,
      @FIXEDCOST,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    --Add the mailing to the ActivationProcess table...

    exec dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE] @ID, @CHANGEAGENTID;

    --Add the mailing activation criteria...

    declare @BBECRECORDSOURCEID uniqueidentifier;
    set @BBECRECORDSOURCEID = dbo.[UFN_MKTRECORDSOURCE_GETFIRSTBBECRECORDSOURCEID]();
    exec dbo.[USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD] @ID, @CHANGEAGENTID, @BBECRECORDSOURCEID, null, null, @APPEALID, @APPEALNAME, @APPEALDESCRIPTION;

    if @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
      exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @ID;

    if @SEGMENTATIONGENERATORID is not null
      begin
        --TODO: TommyVe 6/9/2009 Make sure we're using all of the right objects when doing a mailing with segments

        --Segmentation.Add.xml uses these but we do not

        -- USP_MKTSEGMENTATIONFILTERSELECTION_GETEXCLUDESELECTIONS_ADDFROMXML, USP_MKTSEGMENTATIONFILTERSEGMENTATION_GETEXCLUDESEGMENTATIONS_ADDFROMXML

        -- USP_BUSINESSPROCESSCOMMPREF_ADD

        --Both Segmentation.Add.xml and this procedure use

        -- MKTSEGMENTATIONBUDGET

        -- USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE

        -- USP_MKTSEGMENTATIONFILTERSELECTION_GETINCLUDESELECTIONS_ADDFROMXML (we insert directly into MKTSEGMENTATIONFILTERSELECTION)

        --We use these but Segmentation.Add.xml does not

        -- USP_MKTSEGMENTATIONACTIVATE_SAVEFIELD

        -- MKTSEGMENTATIONEXPORTPROCESS (moved below)


        if @UNIVERSEIDSETREGISTERID is not null
          insert into dbo.MKTSEGMENTATIONFILTERSELECTION
          (SEGMENTATIONID, FILTERTYPECODE, SELECTIONID, [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED])
          values
          (@ID, 1, @UNIVERSEIDSETREGISTERID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        insert into dbo.[MKTMAILINGPREACTIVATIONPROCESS] (
          [ID],
          [SEGMENTATIONID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          newid(),
          @ID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );
        exec dbo.USP_MKTSEGMENTATIONSEGMENTCALCULATEPROCESS_SAVE @ID, @CHANGEAGENTID;
          exec dbo.USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE @ID, @CHANGEAGENTID;

          declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
          select @MKTSEGMENTATIONACTIVATEPROCESSID = [ID]
          from dbo.MKTSEGMENTATIONACTIVATEPROCESS
          where MKTSEGMENTATIONACTIVATEPROCESS.[SEGMENTATIONID] = @ID;


        exec dbo.[USP_BUSINESSPROCESSCOMMPREF_ADD]
          @CHANGEAGENTID = @CHANGEAGENTID,
          @BUSINESSPROCESSCATALOGID = '22C3D75C-A956-4BFC-A5FD-4B866BAEF509',
          @BUSINESSPROCESSPARAMETERSETID = @MKTSEGMENTATIONACTIVATEPROCESSID,
          @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
          @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
          @EXCLUDEDECEASED = @EXCLUDEDECEASED,
          @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
          @EXCLUSIONS = @EXCLUSIONS,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID;


        declare @MKTSEGMENTID uniqueidentifier;
        declare @SEQUENCE int;
        declare SEGMENT_CURSOR cursor local fast_forward
        for
        select
          [MKTSEGMENTID],
          row_number() over (order by [SEGMENTATIONGENERATORSEGMENT].[SEQUENCE]) --SEQUENCE (use row_number to force unique sequence)

        from
          dbo.[SEGMENTATIONGENERATORSEGMENT]
        where
          [SEGMENTATIONGENERATORSEGMENT].[SEGMENTATIONGENERATORID] = @SEGMENTATIONGENERATORID;

        open SEGMENT_CURSOR;
        fetch next from SEGMENT_CURSOR into
          @MKTSEGMENTID, @SEQUENCE;

        while @@fetch_status = 0
        begin
          exec dbo.USP_DATAFORMTEMPLATE_ADD_MKTSEGMENTATIONSEGMENT
            default,
            @CHANGEAGENTID,
            @ID,
            default,
            @MKTSEGMENTID, -- get from cursor

            default,
            default,
            @SEGMENTATIONGENERATORPACKAGEID,
            default,
            default,
            0,
            100,
            0,
            2,
            @SEQUENCE, -- get from cursor

            default,
            default;
          fetch next from SEGMENT_CURSOR into @MKTSEGMENTID, @SEQUENCE;
        end
        close SEGMENT_CURSOR;
        deallocate SEGMENT_CURSOR;

        -- No longer needed since we have a first class business process now (handled down below).

        --declare @QUEUEPROCESSID uniqueidentifier = newid();

        --exec dbo.USP_SEGMENTEDAPPEALMAILINGQUEUE_CREATEORUPDATE @ID, @SEGMENTATIONGENERATORID, @QUEUEPROCESSID output, @CHANGEAGENTID;


        insert into dbo.MKTSEGMENTATIONSEGMENTSPLIT
        (
          [SEGMENTATIONID],
          [SEGMENTID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        )
        select
          MKTSEGMENTATIONSEGMENT.[SEGMENTATIONID],
          MKTSEGMENTATIONSEGMENT.[SEGMENTID],
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from
          dbo.MKTSEGMENTATIONSEGMENT
        where
          MKTSEGMENTATIONSEGMENT.[SEGMENTATIONID] = @ID;

        insert into dbo.[SEGMENTEDAPPEALMAILING]
        (
          [ID],
          [SEGMENTATIONGENERATORID],
          [APPEALID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED],
          [RECENCYEXCLUSIONSMARTFIELDID],
          [RECENCYEXCLUSIONSELECTIONID],
          [RECENCYEXCLUSIONINUSE]
        )
        values
        (
          @ID,
          @SEGMENTATIONGENERATORID,
          @APPEALID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE,
          @RECENCYEXCLUSIONSMARTFIELDID,
          @RECENCYEXCLUSIONSELECTIONID,
          @RECENCYEXCLUSIONINUSE
        );

        declare @REFRESHID uniqueidentifier = newid();
        insert into dbo.SEGMENTEDAPPEALMAILINGREFRESHPROCESS (
          ID,
          SEGMENTEDAPPEALMAILINGID,
          NAME,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        ) values (
          @REFRESHID,
          @ID,
          'Refresh Process for ''' + @NAME + '''',
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        )

        update dbo.[SEGMENTEDAPPEALMAILING] set
          SEGMENTEDAPPEALMAILINGREFRESHPROCESSID = @REFRESHID
        where
          ID = @ID;
          /* Save the export information */
        exec dbo.[USP_MKTSEGMENTATIONPREACTIVATIONEXPORT_SAVEFIELD]
          @ID,
          '',   -- @EXPORTDESCRIPTION,

          null, -- @MAILEXPORTDEFINITIONID,

          null, -- @EMAILEXPORTDEFINITIONID

          null; -- @PHONEEXPORTDEFINITIONID


        /* Save information about the exclusion: */
        if @RECENCYEXCLUSIONSMARTFIELDID is not null and @RECENCYEXCLUSIONSELECTIONID is not null
        begin
          insert into dbo.[MKTSEGMENTATIONFILTERSELECTION]
          (
            [SEGMENTATIONID],
            [FILTERTYPECODE],
            [SELECTIONID],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED]
          )
          values
          (
            @ID,
            2, -- Exclusion

            @RECENCYEXCLUSIONSELECTIONID,
      @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );
          /* Clear the exclusion cache info table first so the cache will get rebuilt the next time they want record counts */
          exec dbo.[USP_MKTSEGMENTATION_CLEARCACHE] @ID;

          /* Update all segment views */
          exec dbo.[USP_MKTSEGMENTATION_UPDATESEGMENTVIEWS] @ID, default, default;
        end
      end
    else
      begin
          --Add the mailing export process...

        insert into dbo.[MKTSEGMENTATIONEXPORTPROCESS] (
          [ID],
          [SEGMENTATIONID],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        ) values (
          newid(),
          @ID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        );

      if @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
        exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @ID;

      --Create the underlying segment information and grab the new mailing segment ID...

      exec dbo.[USP_APPEALMAILING_CREATEORUPDATESEGMENT]
        @APPEALMAILINGID = @ID,
        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
        @NAME = @NAME,
        @IDSETREGISTERID = @IDSETREGISTERID,
        @MKTPACKAGEID = @MKTPACKAGEID,
        @HOUSEHOLDINGTYPECODE = @HOUSEHOLDINGTYPECODE,
        @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD,
        @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS,
        @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @HOUSEHOLDINGONERECORDPERHOUSEHOLD,      
        @EXCLUSIONDATETYPECODE = @EXCLUSIONDATETYPECODE,
        @EXCLUSIONASOFDATE = @EXCLUSIONASOFDATE,
        @EXCLUDEDECEASED = @EXCLUDEDECEASED,
        @EXCLUDEINACTIVE = @EXCLUDEINACTIVE,
        @EXCLUSIONS = @EXCLUSIONS,
        @USEADDRESSPROCESSING = @USEADDRESSPROCESSING,
        @ADDRESSPROCESSINGOPTIONID = @ADDRESSPROCESSINGOPTIONID,
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
        @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @APPEALID = @APPEALID;

        --TommyVe 6/2/2009 @MKTSEGMENTATIONSEGMENTID appears to not be used, it is only selected

        --select

        --  @MKTSEGMENTATIONSEGMENTID = [ID]

        --from dbo.[MKTSEGMENTATIONSEGMENT]

        --where [SEGMENTATIONID] = @ID

        --and [SEQUENCE] = 1;


        --The mailing was created in the SP above, because of ordering issues, so update the rest of the fields here...

        update dbo.[APPEALMAILING] set
          [LABELFILENAME] = @LABELFILENAME,
          [LABELFILE] = @LABELFILE,
          [CREATEOUTPUTIDSET] = @CREATEOUTPUTIDSET,
          [OUTPUTIDSETNAME] = @OUTPUTIDSETNAME,
          [OVERWRITEOUTPUTIDSET] = @OVERWRITEOUTPUTIDSET,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @ID;
      end;

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

    /* Save KPIs */
    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML] 
      @ID, 
      @ACTIVATIONKPIS, 
      @USEKPISASDEFAULT, 
      @CHANGEAGENTID;
  end try

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

  return 0;