USP_MKTCOMMUNICATIONEFFORT_GETUNIQUENAME

CREATE procedure dbo.[USP_MKTCOMMUNICATIONEFFORT_GETUNIQUENAME]
(
  @COMMUNICATIONNAMESCHEMEID uniqueidentifier,
  @EFFORTNAMESCHEMETEXT xml,
  @EFFORTNAME nvarchar(100) output,
  @COUNTERVALUE nvarchar(10) output,
  @COUNTER int output,
  @SEGMENTATIONID uniqueidentifier =  null,
  @ISCOPY bit = 0,
  @NAMEFORMAT nvarchar(100) = '{0}{1}'
)
as
begin
  set nocount on;

  declare @TMPEFFORTNAME nvarchar(max);
  declare @COUNTERFORMAT nvarchar(10);
  declare @OLDEFFORTNAME nvarchar(100);

  declare @NAMESCHEMEPARTSTABLE table (
    [TEXTPART] nvarchar(100), 
    [FREEFORMPART] nvarchar(100), 
    [SEQUENCE] int, 
    [ID] uniqueidentifier, 
    [NAMEPARTTYPECODE] tinyint
  );

  begin try

    if @SEGMENTATIONID is not null
      select @OLDEFFORTNAME = [MKTSEGMENTATION].[NAME]
      from dbo.[MKTSEGMENTATION]
      where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

    --place the namescheme data into a table variable

    if @COMMUNICATIONNAMESCHEMEID is not null
      begin
        insert into @NAMESCHEMEPARTSTABLE
          select
            case when T.c.value('(@SEPARATORTYPECODE)[1]', 'tinyint') = 1 then ' ' else T.c.value('(@TEXTPART)[1]', 'nvarchar(100)') end as [TEXTPART],
            T.c.value('(@FREEFORMPART)[1]', 'nvarchar(100)') as [FREEFORMPART],
            T.c.value('(@SEQUENCE)[1]', 'int') as [SEQUENCE],
            @COMMUNICATIONNAMESCHEMEID,
            T.c.value('(@NAMEPARTTYPECODE)[1]', 'tinyint') as [NAMEPARTTYPECODE]
          from @EFFORTNAMESCHEMETEXT.nodes('/EFFORTNAMESCHEMETEXT/ITEM') T(c);
      end

    if @ISCOPY = 0 or @SEGMENTATIONID is null or (@OLDEFFORTNAME is not null and (@OLDEFFORTNAME <> @EFFORTNAME))
      begin
        --get the correct counter as needed

        if exists(select 1 from @NAMESCHEMEPARTSTABLE where [NAMEPARTTYPECODE] = 8)
          begin
            --namescheme has counter

            --get the next counter value


            if @SEGMENTATIONID is null--counter start value is grabbed from the table

              select
                @COUNTER = 
                  isnull(case when isnull([MKTCOMMUNICATIONNAMESCHEME].[MAXCOUNTERVALUE], 0) >= [MKTCOMMUNICATIONNAMESCHEMEPART].[VALUE] then isnull([MKTCOMMUNICATIONNAMESCHEME].[MAXCOUNTERVALUE], 0) + 1 
                          else [MKTCOMMUNICATIONNAMESCHEMEPART].[VALUE] end, 1),
                @COUNTERFORMAT = [MKTCOMMUNICATIONNAMESCHEMEPART].[OPTIONTYPE]
              from dbo.[MKTCOMMUNICATIONNAMESCHEME]
              left join dbo.[MKTCOMMUNICATIONNAMESCHEMEPART] on [MKTCOMMUNICATIONNAMESCHEME].[ID] = [MKTCOMMUNICATIONNAMESCHEMEPART].[MKTCOMMUNICATIONNAMESCHEMEID] and [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] = 8
              where [MKTCOMMUNICATIONNAMESCHEME].[ID] = @COMMUNICATIONNAMESCHEMEID;

            else --counter start value is assumed to be what is currently in the name

              begin

                select @COUNTER = [TEXTPART]
                from @NAMESCHEMEPARTSTABLE
                where [NAMEPARTTYPECODE] = 8;

                select @COUNTERFORMAT = [MKTCOMMUNICATIONNAMESCHEMEPART].[OPTIONTYPE]
                from dbo.[MKTCOMMUNICATIONNAMESCHEME]
                left join dbo.[MKTCOMMUNICATIONNAMESCHEMEPART] on [MKTCOMMUNICATIONNAMESCHEME].[ID] = [MKTCOMMUNICATIONNAMESCHEMEPART].[MKTCOMMUNICATIONNAMESCHEMEID] and [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] = 8
                where [MKTCOMMUNICATIONNAMESCHEME].[ID] = @COMMUNICATIONNAMESCHEMEID;

              end

            --if the number is already longer than the format we do not need to format it

            if len(@COUNTER) > len(@COUNTERFORMAT)
              set @COUNTERVALUE = cast(@COUNTER as nvarchar(10));
            else
              set @COUNTERVALUE = right(replicate('0',len(@COUNTERFORMAT)-1)+ rtrim(@COUNTER), len(@COUNTERFORMAT));

            --now rebuild the effort's name

            set @TMPEFFORTNAME = dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETNAMEFROMNAMESCHEMETEXT](@EFFORTNAMESCHEMETEXT, @COUNTERVALUE);

            declare @LOOPS int = 0;
            while (select count(1) from dbo.[MKTSEGMENTATION] where [MKTSEGMENTATION].[NAME] = @TMPEFFORTNAME) > 0
              begin
                set @COUNTER = @COUNTER + 1;

                if len(@COUNTER) > len(@COUNTERFORMAT)
                  set @COUNTERVALUE = cast(@COUNTER as nvarchar(10));
                else
                  set @COUNTERVALUE = right(replicate('0',len(@COUNTERFORMAT)-1)+ rtrim(@COUNTER), len(@COUNTERFORMAT));

                --now rebuild the effort's name

                set @TMPEFFORTNAME = dbo.[UFN_MKTCOMMUNICATIONEFFORT_GETNAMEFROMNAMESCHEMETEXT](@EFFORTNAMESCHEMETEXT, @COUNTERVALUE);

                if @COUNTER = 2147483647
                begin
                  set @COUNTER = 0;
                  set @LOOPS = @LOOPS + 1;
                  if @LOOPS = 2
                    raiserror('ERR_COMMUNICATIONEFFORT_UNABLETOFINDUNIQUECOUNTER', 13, 1);
                end
              end

            if len(@TMPEFFORTNAME) > 100
              raiserror('ERR_COMMUNICATIONEFFORT_NAMESCHEMETEXTTOOLONG', 13, 1);
            else
              set @EFFORTNAME = @TMPEFFORTNAME;

          end
        else
          --name pattern does NOT have a counter or they do not have a namescheme so we need to make sure the name is unique

          exec dbo.[USP_MKTCOMMON_GETUNIQUENAME] @EFFORTNAME output, @NAMEFORMAT, 'MKTSEGMENTATION', 'NAME', @COUNTER output;

      end

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

  return 0;
end