USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATION_3

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDIT_MKTSEGMENTATION_3]
(
  @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
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  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 @MKTSEGMENTATIONACTIVATEPROCESSID uniqueidentifier;
  declare @ADDRESSPROCESSINGOPTIONSCHANGED bit;
  declare @EXCLUSIONSCHANGED bit;

  if exists(select top 1 1 from dbo.[MKTSEGMENTATION] where [ID] = @ID and [MARKETINGPLANITEMID] is not null) or 
     exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEGMENT] where [SEGMENTATIONID] = @ID) or
     exists(select top 1 1 from dbo.[MKTSEGMENTATIONSEED] where [SEGMENTATIONID] = @ID) or
     exists(select top 1 1 from dbo.[MKTSEGMENTATION] where [PARENTSEGMENTATIONID] = @ID)
    begin
      select @CURRENTSITEID = [SITEID] from dbo.[MKTSEGMENTATION] where [ID] = @ID;

      if (@CURRENTSITEID <> @SITEID or (@CURRENTSITEID is null and @SITEID is not null) or (@CURRENTSITEID is not null and @SITEID is null))
        begin
          raiserror('Site may not be changed on a marketing effort that is in use or has dependencies.', 13, 1);
          return 1;
        end;
    end;

  begin try
    select
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE],
      @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 join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
    where [MKTSEGMENTATION].[ID] = @ID;

    if @ACTIVE = 0
      --Check if the mailing is currently being activated...

      exec dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @ID;

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

    /* Update the mailing fields */
    update dbo.[MKTSEGMENTATION] set
      [CODE] = @CODE,
      [NAME] = @NAME,
      [DESCRIPTION] = @DESCRIPTION,
      [SITEID] = @SITEID,
      [SOURCECODEID] = @SOURCECODEID,
      [MAILDATE] = @MAILDATE,
      [HOUSEHOLDINGTYPECODE] = @HOUSEHOLDINGTYPECODE,
      [USEADDRESSPROCESSING] = @USEADDRESSPROCESSING,
      [ADDRESSPROCESSINGOPTIONID] = @ADDRESSPROCESSINGOPTIONID,
      [NAMEFORMATPARAMETERID] = @NAMEFORMATPARAMETERID,
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE] = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE,
      [ADDRESSPROCESSINGOPTIONSEASONALASOFDATE] = @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @ID;

    /* Save the sourcecode parts, only for non-activated mailings */
    if @ACTIVE = 0
      exec dbo.[USP_MKTSOURCECODEPART_GETITEMLIST_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 and @EXCLUSIONSCHANGED = 1
      begin
        /* Update the contact rules/communication preferences for the mailing activation business process... */
        select
          @MKTSEGMENTATIONACTIVATEPROCESSID = [ID]
        from
          dbo.[MKTSEGMENTATIONACTIVATEPROCESS]
        where
          [SEGMENTATIONID] = @ID;

        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, then clear the cache tables... */
    if @ACTIVE = 0 and (@HOUSEHOLDINGTYPECODE <> @OLDHOUSEHOLDINGTYPECODE or @ADDRESSPROCESSINGOPTIONSCHANGED = 1 or @EXCLUSIONSCHANGED = 1)
      begin
        if @ADDRESSPROCESSINGOPTIONSCHANGED = 1 and @USEADDRESSPROCESSING = 1 and @ADDRESSPROCESSINGOPTIONID is not null
          exec dbo.[USP_MKTSEGMENTATIONSEGMENTCACHEADDRESSES_CREATETABLE] @ID;

        --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, 1;
      end

    --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];

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

  return 0;