USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MKTCOMMUNICATIONEFFORT 
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @EFFORTCOMMUNICATIONTEMPLATEID uniqueidentifier,
  @EFFORTNAME nvarchar(100),
  @COMMUNICATIONNAMESCHEMEID uniqueidentifier,
  @EFFORTNAMESCHEMETEXT xml,
  @EFFORTDESCRIPTION nvarchar(255),
  @APPEALINFORMATION xml,
  @EFFORTCHANNELCODE tinyint,
  @EFFORTSITEID uniqueidentifier,
  @EFFORTDUEDATE datetime,
  @EFFORTLAUNCHDATE datetime,
  @EFFORTALLOWRESERVINGFINDERNUMBERS bit,
  @EFFORTALLOWSPECIFYBUDGET bit,
  @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS bit,
  @EFFORTBASECURRENCYID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as

  set nocount on;

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

  declare @COUNTERVALUE nvarchar(10);
  declare @ISBBEC bit;
  declare @OLDEFFORTNAME nvarchar(100);
  declare @TMPEFFORTNAME nvarchar(max) = '';
  declare @CURRENTDATE datetime = getdate();
  declare @ACTIVE bit = 0;
  declare @OLDEFFORTALLOWSPECIFYBUDGET bit;
  declare @OLDEFFORTALLOWRESERVINGFINDERNUMBERS bit;
  declare @OLDEFFORTALLOWEXCLUDEPREVIOUSEFFORTS bit;
  declare @MKTSEGMENTATIONFINDERNUMBERID uniqueidentifier;
  declare @MKTSEGMENTATIONFILTERSEGMENTATIONID uniqueidentifier;
  declare @COUNTER int = 0;


  begin try

    /* throw error if current added package channel conflicts with effort channel */
    if @EFFORTCHANNELCODE <> 255 -- not multi-channel
      begin
        if exists(select * 
                  from dbo.[MKTSEGMENTATIONSEGMENT]
                  left join [MKTSEGMENTATIONTESTSEGMENT] on [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]
                  inner join [MKTPACKAGE] on [MKTPACKAGE].[ID] = [MKTSEGMENTATIONSEGMENT].[PACKAGEID] or [MKTPACKAGE].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[PACKAGEID]
                  where
                    [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @ID
                    and [MKTPACKAGE].[CHANNELCODE] <> @EFFORTCHANNELCODE)
          begin
            raiserror('ERR_COMMUNICATIONEFFORT_CHANNELCODEMISMATCH', 13, 1);
          end
      end

    select
      @OLDEFFORTNAME = [MKTSEGMENTATION].[NAME],
      @OLDEFFORTALLOWSPECIFYBUDGET = [MKTSEGMENTATION].[ALLOWSPECIFYBUDGET],
      @OLDEFFORTALLOWRESERVINGFINDERNUMBERS = [MKTSEGMENTATION].[ALLOWRESERVINGFINDERNUMBERS],
      @OLDEFFORTALLOWEXCLUDEPREVIOUSEFFORTS = [MKTSEGMENTATION].[ALLOWEXCLUDEPREVIOUSEFFORTS],
      @ACTIVE = [MKTSEGMENTATION].[ACTIVE]
    from dbo.[MKTSEGMENTATION]
    where [MKTSEGMENTATION].[ID] = @ID;

    select
      @EFFORTDESCRIPTION = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTIONLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[DESCRIPTION] else @EFFORTDESCRIPTION end),
      @EFFORTCHANNELCODE = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODELOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[CHANNELCODE] else @EFFORTCHANNELCODE end),
      @EFFORTALLOWRESERVINGFINDERNUMBERS = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERSLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWRESERVINGFINDERNUMBERS] else @EFFORTALLOWRESERVINGFINDERNUMBERS end),
      @EFFORTALLOWSPECIFYBUDGET = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGETLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWSPECIFYBUDGET] else @EFFORTALLOWSPECIFYBUDGET end),
      @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTSLOCKED] = 1 then [MKTCOMMUNICATIONTEMPLATEDEFAULT].[ALLOWEXCLUDEPREVIOUSEFFORTS] else @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS end),
      @APPEALINFORMATION = (case when [MKTCOMMUNICATIONTEMPLATEDEFAULT].[APPEALLOCKED] = 1 then (
          select 
            [MKTAPPEALRECORDSOURCE].[ID] as [RECORDSOURCEID],
            [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
            [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as [SEARCHLISTCATALOGID],
    [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [SEARCHLISTDESCRIPTIONFIELD],
            [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as [APPEALSYSTEMID],
            (case when @ISBBEC = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
              (select [APPEAL].[NAME] from dbo.[APPEAL] where [APPEAL].[ID] = cast([MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as uniqueidentifier))
              else
              [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALID]
              end) as [APPEALID],
            (case when @ISBBEC = 1 and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] <> '' and dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC]([MKTAPPEALRECORDSOURCE].[ID]) = 1 then
              (select [APPEAL].[DESCRIPTION] from dbo.[APPEAL] where [APPEAL].[ID] = cast([MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALSYSTEMID] as uniqueidentifier))
              else
              [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[APPEALDESCRIPTION]
              end) as [APPEALDESCRIPTION]
          from dbo.[MKTAPPEALRECORDSOURCE]
          inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
          left join [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] on [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID] 
            and [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[COMMUNICATIONTEMPLATEID] = @EFFORTCOMMUNICATIONTEMPLATEID
          where (dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1)
          for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64)
          else @APPEALINFORMATION end)
    from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULT]
    where [MKTCOMMUNICATIONTEMPLATEDEFAULT].[COMMUNICATIONTEMPLATEID] = @EFFORTCOMMUNICATIONTEMPLATEID;

    --get the name from the name pattern
    if @COMMUNICATIONNAMESCHEMEID is not null
      begin
        --build the effort name to check if it is longer than 100
        set @TMPEFFORTNAME = dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETNAMEFROMNAMESCHEMETEXT](@EFFORTNAMESCHEMETEXT, NULL);
        if len(@TMPEFFORTNAME) > 100
          raiserror('ERR_COMMUNICATIONEFFORT_NAMESCHEMETEXTTOOLONG', 13, 1);
        else
          set @EFFORTNAME = @TMPEFFORTNAME;

        --determine if we have a special counter at the end
        if (len(@OLDEFFORTNAME) > len(@EFFORTNAME)) and (charindex(@EFFORTNAME + ' (', @OLDEFFORTNAME) = 1)
        begin
          declare @NAMESUFFIX nvarchar(100) = substring(@OLDEFFORTNAME, len(@EFFORTNAME) + 1, 100);
          set @EFFORTNAME = @EFFORTNAME + @NAMESUFFIX;
        end
    end

    if @EFFORTNAME <> @OLDEFFORTNAME
      begin

      --build the name based on name pattern or supplied text
      exec dbo.[USP_MKTCOMMUNICATIONEFFORT_GETUNIQUENAME] 
        @COMMUNICATIONNAMESCHEMEID = @COMMUNICATIONNAMESCHEMEID,
        @EFFORTNAMESCHEMETEXT = @EFFORTNAMESCHEMETEXT,
        @EFFORTNAME = @EFFORTNAME output,
        @COUNTERVALUE = @COUNTERVALUE output,
        @COUNTER = @COUNTER output,
        @SEGMENTATIONID =  @ID;

      if @COMMUNICATIONNAMESCHEMEID is not null
        begin
          --update part values
          update dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
          set
            [MKTCOMMUNICATIONNAMESCHEMEPART].[COMMUNICATIONEFFORTVALUE] = case when [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] = 8  then @COUNTERVALUE 
            else [NAMEPARTS].[FREEFORMPART] end
          from dbo.[MKTCOMMUNICATIONNAMESCHEMEPART]
          inner join 
            (select
              T.c.value('(@FREEFORMPART)[1]', 'nvarchar(100)') as [FREEFORMPART],
              T.c.value('(@SEQUENCE)[1]', 'int') as [SEQUENCE],
              @COMMUNICATIONNAMESCHEMEID [ID],
              T.c.value('(@NAMEPARTTYPECODE)[1]', 'tinyint') as [NAMEPARTTYPECODE]
            from @EFFORTNAMESCHEMETEXT.nodes('/EFFORTNAMESCHEMETEXT/ITEM') T(c)
            ) [NAMEPARTS] on [NAMEPARTS].[ID] = [MKTCOMMUNICATIONNAMESCHEMEPART].[MKTCOMMUNICATIONNAMESCHEMEID]
          where 
            [MKTCOMMUNICATIONNAMESCHEMEPART].[SEQUENCE] = [NAMEPARTS].[SEQUENCE] 
            and [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] in (8, 10);
        end
      end

    update
      dbo.[MKTSEGMENTATION]
    set
      [MKTSEGMENTATION].[NAME] = @EFFORTNAME,
      [MKTSEGMENTATION].[DESCRIPTION] = @EFFORTDESCRIPTION,
      [MKTSEGMENTATION].[CHANNELCODE] = @EFFORTCHANNELCODE,
      [MKTSEGMENTATION].[SITEID] = @EFFORTSITEID,
      [MKTSEGMENTATION].[DUEDATE] = @EFFORTDUEDATE,
      [MKTSEGMENTATION].[MAILDATE] = @EFFORTLAUNCHDATE,
      [MKTSEGMENTATION].[ALLOWRESERVINGFINDERNUMBERS] = @EFFORTALLOWRESERVINGFINDERNUMBERS,
      [MKTSEGMENTATION].[ALLOWSPECIFYBUDGET] = @EFFORTALLOWSPECIFYBUDGET,
      [MKTSEGMENTATION].[ALLOWEXCLUDEPREVIOUSEFFORTS] = @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS,
      [MKTSEGMENTATION].[CHANGEDBYID] = @CHANGEAGENTID,
      [MKTSEGMENTATION].[DATECHANGED] = @CURRENTDATE
    where
      [MKTSEGMENTATION].[ID] = @ID;

    if @EFFORTNAME <> @OLDEFFORTNAME
    begin

      --update the name on the plan item the mailings is from
      update dbo.[MKTMARKETINGPLANITEM] set 
        [MKTMARKETINGPLANITEM].[NAME] = @EFFORTNAME,
        [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
        [IDSETREGISTER].[NAME] = @EFFORTNAME + substring([IDSETREGISTER].[NAME], len(@OLDEFFORTNAME) + 1, 300),
        [IDSETREGISTER].[CHANGEDBYID] = @CHANGEAGENTID,
        [IDSETREGISTER].[DATECHANGED] = @CURRENTDATE
      where [IDSETREGISTER].[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 [IDSETREGISTER].[NAME] like (replace(replace(replace(@OLDEFFORTNAME, '\', '\\'), '_', '\_'), '%', '\%') + ' (%)') escape '\';

      -- update the name of any KPIs associated with this marketing effort to reflect its name
      update dbo.[KPIINSTANCE] set
        [KPIINSTANCE].[NAME] = @EFFORTNAME + substring([KPIINSTANCE].[NAME], len(@OLDEFFORTNAME) + 1, 255),
        [KPIINSTANCE].[CHANGEDBYID] = @CHANGEAGENTID,
        [KPIINSTANCE].[DATECHANGED] = @CURRENTDATE
      where [KPIINSTANCE].[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(@OLDEFFORTNAME, '\', '\\'), '_', '\_'), '%', '\%') + ' - %') escape '\'
      );
    end

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

    if @ACTIVE = 0
    begin
      --check if we need to clear out additional options

      --user entered budget values
      if @OLDEFFORTALLOWSPECIFYBUDGET = 1 and @EFFORTALLOWSPECIFYBUDGET = 0
      begin
        exec dbo.[USP_MKTSEGMENTATIONBUDGET_UPDATE] @ID, @CHANGEAGENTID, 0, 0;
      end

      --reserved finder numbers
      if @OLDEFFORTALLOWRESERVINGFINDERNUMBERS = 1 and @EFFORTALLOWRESERVINGFINDERNUMBERS = 0
      begin
        declare DELETERESERVEDFINDERNUMBERCURSOR cursor local fast_forward for
          select [MKTSEGMENTATIONFINDERNUMBER].[ID]
          from dbo.[MKTSEGMENTATIONFINDERNUMBER]
          where [MKTSEGMENTATIONFINDERNUMBER].[SEGMENTATIONID] = @ID;

        open DELETERESERVEDFINDERNUMBERCURSOR;
        fetch next from DELETERESERVEDFINDERNUMBERCURSOR into @MKTSEGMENTATIONFINDERNUMBERID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_MKTSEGMENTATIONFINDERNUMBER_DELETE] @MKTSEGMENTATIONFINDERNUMBERID, @CHANGEAGENTID;
          fetch next from DELETERESERVEDFINDERNUMBERCURSOR into @MKTSEGMENTATIONFINDERNUMBERID;
        end

        close DELETERESERVEDFINDERNUMBERCURSOR;
        deallocate DELETERESERVEDFINDERNUMBERCURSOR;
      end

      --previous effort exclusions
      if @OLDEFFORTALLOWEXCLUDEPREVIOUSEFFORTS = 1 and @EFFORTALLOWEXCLUDEPREVIOUSEFFORTS = 0
      begin
        declare DELETEEFFORTEXCLUSIONSCURSOR cursor local fast_forward for
          select [MKTSEGMENTATIONFILTERSEGMENTATION].[ID]
          from dbo.[MKTSEGMENTATIONFILTERSEGMENTATION]
          where [MKTSEGMENTATIONFILTERSEGMENTATION].[SEGMENTATIONID] = @ID;

        open DELETEEFFORTEXCLUSIONSCURSOR;
        fetch next from DELETEEFFORTEXCLUSIONSCURSOR into @MKTSEGMENTATIONFILTERSEGMENTATIONID;

        while (@@FETCH_STATUS = 0)
        begin
          exec dbo.[USP_MKTSEGMENTATIONFILTERSEGMENTATION_DELETE] @MKTSEGMENTATIONFILTERSEGMENTATIONID, @CHANGEAGENTID;
          fetch next from DELETEEFFORTEXCLUSIONSCURSOR into @MKTSEGMENTATIONFILTERSEGMENTATIONID;
        end

        close DELETEEFFORTEXCLUSIONSCURSOR;
        deallocate DELETEEFFORTEXCLUSIONSCURSOR;
      end
    end

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

return 0;