USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATION_6

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATION_6]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @CODE nvarchar(10),
  @NAME nvarchar(100),
  @DESCRIPTION nvarchar(255),
  @SITEID uniqueidentifier,
  @SOURCECODEID uniqueidentifier,
  @ITEMLIST xml,
  @MAILDATE datetime,
  @HOUSEHOLDINGTYPECODE tinyint,
  @EXCLUSIONDATETYPECODE tinyint,
  @EXCLUSIONASOFDATE datetime,
  @EXCLUDEDECEASED bit,
  @EXCLUDEINACTIVE bit,
  @EXCLUSIONS xml,
  @USEADDRESSPROCESSING bit,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier,
  @NAMEFORMATPARAMETERID uniqueidentifier,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime,
  @ACTIVATIONKPIS xml,
  @USEKPISASDEFAULT bit,
  @APPEALINFORMATION xml,
  @RUNACTIVATEANDEXPORT bit,
  @EXPORTDESCRIPTION nvarchar(255),
  @MAILEXPORTDEFINITIONID uniqueidentifier,
  @EMAILEXPORTDEFINITIONID uniqueidentifier,
  @PHONEEXPORTDEFINITIONID uniqueidentifier,
  @CODEVALUEID uniqueidentifier,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit,
  @CACHESOURCEANALYSISRULEDATA bit,
  @BUSINESSUNITS xml,
  @OVERRIDEBUSINESSUNITS bit,
  @RUNMARKETINGEXCLUSIONSREPORT bit
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @OLDNAME nvarchar(100);
  declare @OLDSOURCECODEID uniqueidentifier;
  declare @OLDHOUSEHOLDINGTYPECODE tinyint;
  declare @OLDUSEADDRESSPROCESSING bit;
  declare @OLDADDRESSPROCESSINGOPTIONID uniqueidentifier;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint;
  declare @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime;
  declare @OLDEXCLUSIONDATETYPECODE as tinyint;
  declare @OLDEXCLUSIONASOFDATE as datetime;
  declare @OLDEXCLUDEDECEASED as bit;
  declare @OLDEXCLUDEINACTIVE as bit;
  declare @OLDEXCLUSIONS as xml;
  declare @CURRENTSITEID uniqueidentifier;
  declare @ACTIVE bit;
  declare @ISHISTORICAL bit;
  declare @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
  declare @ADDRESSPROCESSINGOPTIONSCHANGED bit;
  declare @EXCLUSIONSCHANGED bit;
  declare @SEGMENTATIONEXPORTPROCESSID uniqueidentifier;

  if @USEADDRESSPROCESSING = 0
    begin
      set @ADDRESSPROCESSINGOPTIONID = null;
      set @NAMEFORMATPARAMETERID = null;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = 0;
      set @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = null;
    end;

  begin try
    select
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @ISHISTORICAL = [MKTSEGMENTATION].[ISHISTORICAL],
      @SEGMENTATIONEXPORTPROCESSID = [MKTSEGMENTATIONEXPORTPROCESS].[ID],
      @OLDNAME = [MKTSEGMENTATION].[NAME],
      @OLDSOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
      @OLDHOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
      @OLDUSEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
      @OLDADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
      @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
      @OLDEXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
      @OLDEXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
      @OLDEXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
      @OLDEXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
      @OLDEXCLUSIONS = dbo.[UFN_BUSINESSPROCESSCOMMPREF_GETEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
      @CURRENTDATE = getdate()
    from dbo.[MKTSEGMENTATION]
    inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left outer join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    left join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    where [MKTSEGMENTATION].[ID] = @ID;

    if @ACTIVE = 0
      exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @ID;

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

    update dbo.[MKTSEGMENTATION] set
      [NAME] = @NAME,
      [DESCRIPTION] = @DESCRIPTION,
      [CODE] = @CODE,
      [PARTDEFINITIONVALUESID] = @CODEVALUEID,
      [SITEID] = @SITEID,
      [SOURCECODEID] = @SOURCECODEID,
      [MAILDATE] = @MAILDATE,
      [HOUSEHOLDINGTYPECODE] = @HOUSEHOLDINGTYPECODE,
      [USEADDRESSPROCESSING] = @USEADDRESSPROCESSING,
      [ADDRESSPROCESSINGOPTIONID] = case when @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONID else null end,
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] = case when @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE else 0 end,
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = case when @USEADDRESSPROCESSING = 1 then @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE else null end,
      [NAMEFORMATPARAMETERID] = case when @USEADDRESSPROCESSING = 1 then @NAMEFORMATPARAMETERID else null end,
      [RUNACTIVATEANDEXPORT] = @RUNACTIVATEANDEXPORT,
      [OVERRIDEBUSINESSUNITS] = @OVERRIDEBUSINESSUNITS,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    exec dbo.USP_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITS_UPDATEFROMXML @ID, @BUSINESSUNITS, @CHANGEAGENTID;  

    if @ACTIVE = 0
      exec dbo.[USP_MKTSOURCECODEPART_GETITEMLIST2_UPDATEFROMXML] @ID, @ITEMLIST, @CHANGEAGENTID, @CURRENTDATE;

    -- update any segment codes in this mailing with their base segment codes

    if isnull(cast(@OLDSOURCECODEID as varchar(36)),'') <> isnull(cast(@SOURCECODEID as varchar(36)),'')
      update dbo.[MKTSEGMENTATIONSEGMENT] set
        [CODE] = (case when @SOURCECODEID is null then '' else [MKTSEGMENT].[CODE] end),
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      from dbo.[MKTSEGMENTATIONSEGMENT] as [SS]
      inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [SS].[SEGMENTID]
      where [SS].[SEGMENTATIONID] = @ID;

    set @ADDRESSPROCESSINGOPTIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPAREADDRESSPROCESSINGOPTIONS](@USEADDRESSPROCESSING, @ADDRESSPROCESSINGOPTIONID, @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE, @OLDUSEADDRESSPROCESSING, @OLDADDRESSPROCESSINGOPTIONID, @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE, @OLDADDRESSPROCESSINGOPTIONSEASONALASOFDATE);
    set @EXCLUSIONSCHANGED = dbo.[UFN_MKTSEGMENTATION_COMPARECOMMPREFS](@EXCLUSIONDATETYPECODE, @EXCLUSIONASOFDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @EXCLUSIONS, @OLDEXCLUSIONDATETYPECODE, @OLDEXCLUSIONASOFDATE, @OLDEXCLUDEDECEASED, @OLDEXCLUDEINACTIVE, @OLDEXCLUSIONS);

    if @ACTIVE = 0
      begin
        select @MKTSEGMENTATIONACTIVATEPROCESSID = [ID]
        from dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
        where [SEGMENTATIONID] = @ID;

        exec dbo.[USP_MKTSEGMENTATIONACTIVATEPROCESS_SAVE] @ID, @CHANGEAGENTID, 1, @RUNSEGMENTATIONSEGMENTREFRESHPROCESS, @CACHESOURCEANALYSISRULEDATA, @RUNMARKETINGEXCLUSIONSREPORT;

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

    -- if they changed the householding options or communication preferences, clear the cache tables so the cache will get rebuilt the next time they want record counts

    if @ACTIVE = 0 and (@HOUSEHOLDINGTYPECODE <> @OLDHOUSEHOLDINGTYPECODE or @ADDRESSPROCESSINGOPTIONSCHANGED = 1 or @EXCLUSIONSCHANGED = 1)
      exec dbo.[USP_MKTSEGMENTATION_CLEARCACHE] @ID, 1;

    --update the name on the plan item the mailings is from

    update dbo.[MKTMARKETINGPLANITEM] set 
      [MKTMARKETINGPLANITEM].[NAME] = @NAME,
      [MKTMARKETINGPLANITEM].[CHANGEDBYID] = @CHANGEAGENTID,
      [MKTMARKETINGPLANITEM].[DATECHANGED] = @CURRENTDATE
    from [MKTSEGMENTATION]
    where [MKTSEGMENTATION].[ID] = @ID
    and [MKTMARKETINGPLANITEM].[ID] = [MKTSEGMENTATION].[MARKETINGPLANITEMID];

    -- update the name of any gift ID set associated with this mailing to reflect its name

    update dbo.[IDSETREGISTER] set
      [NAME] = @NAME + substring([NAME], len(@OLDNAME) + 1, 300),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] in (
      select [NORMALGIFTIDSETREGISTERID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @ID and [NORMALGIFTIDSETREGISTERID] is not null
      union all
      select [UNRESOLVEDGIFTIDSETREGISTERID] from dbo.[MKTSEGMENTATIONACTIVATE] where [SEGMENTATIONID] = @ID and [UNRESOLVEDGIFTIDSETREGISTERID] is not null
    )
    and [NAME] like (replace(replace(replace(@OLDNAME, '\', '\\'), '_', '\_'), '%', '\%') + ' (%)') escape '\';

    exec dbo.[USP_MKTSEGMENTATIONACTIVATEKPI_SAVEFIELD_FROMXML] 
      @ID,
      @ACTIVATIONKPIS,
      @USEKPISASDEFAULT,
      @CHANGEAGENTID,
      @CURRENTAPPUSERID;

    -- update the name of any KPIs associated with this marketing effort to reflect its name

    update dbo.[KPIINSTANCE] set
      [NAME] = @NAME + substring([NAME], len(@OLDNAME) + 1, 255),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] in (
      select [KPIINSTANCE].[ID]
      from dbo.[MKTSEGMENTATIONACTIVATEKPI]
      inner join dbo.[KPIINSTANCE] on [KPIINSTANCE].[KPICATALOGID] = [MKTSEGMENTATIONACTIVATEKPI].[KPICATALOGID]
      where [MKTSEGMENTATIONACTIVATEKPI].[SEGMENTATIONID] = @ID
      and [KPIINSTANCE].[CONTEXTRECORDID] = cast([MKTSEGMENTATIONACTIVATEKPI].[SEGMENTATIONID] as nvarchar(36))
      and [KPIINSTANCE].[NAME] like (replace(replace(replace(@OLDNAME, '\', '\\'), '_', '\_'), '%', '\%') + ' - %') escape '\'
    );

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

    if @ISHISTORICAL = 0
      exec dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATIONEXPORTPROCESS_4]
        @SEGMENTATIONEXPORTPROCESSID,
        @CHANGEAGENTID,
        @ID, 
        @EXPORTDESCRIPTION,
        @MAILEXPORTDEFINITIONID,
        @EMAILEXPORTDEFINITIONID,
        @PHONEEXPORTDEFINITIONID;
  end try

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

  return 0;