USP_DATALIST_MKTSEGMENTATIONFINDERNUMBER

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTATIONFINDERNUMBER]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @DATATABLE nvarchar(128);
  declare @WIDTH int;
  declare @CHECKDIGIT bit;
  declare @ID uniqueidentifier;
  declare @VENDORID uniqueidentifier;
  declare @MIN bigint;
  declare @MAX bigint;
  declare @QUANTITY bigint;
  declare @SQL nvarchar(max);
  declare @ASSIGNED bigint;
  declare @LASTHOUSEFILERANGEID uniqueidentifier;

  declare @CALCULATIONS table (
    [ID] uniqueidentifier not null,
    [VENDORID] uniqueidentifier,
    [MIN] bigint not null,
    [MAX] bigint not null,
    [QUANTITY] bigint not null,
    [ASSIGNED] bigint,
    [REMAINING] bigint,
    [CHECKDIGIT] bit not null
  );

  select
    @MAILINGTYPECODE = [MAILINGTYPECODE],
    @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME]([ID])
  from dbo.[MKTSEGMENTATION]
  where [ID] = @SEGMENTATIONID;

  if @MAILINGTYPECODE = 2 -- memberships

    set @SQL = 'select @ASSIGNED = count(distinct [FINDERNUMBER]) from dbo.[' + @DATATABLE + '] where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';
  else        
    set @SQL = 'select @ASSIGNED = count([FINDERNUMBER]) from dbo.[' + @DATATABLE + '] where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';


  insert into @CALCULATIONS ([ID], [VENDORID], [MIN], [MAX], [QUANTITY], [ASSIGNED], [REMAINING], [CHECKDIGIT])
    select 
      [ID],
      [VENDORID],
      [MIN],
      [MAX],
      [MAX] - [MIN] + 1 as [QUANTITY],
      0 as [ASSIGNED],
      [MAX] - [MIN] + 1 as [REMAINING],
      [CHECKDIGIT]
    from dbo.[MKTSEGMENTATIONFINDERNUMBER]
    where [SEGMENTATIONID] = @SEGMENTATIONID;

  if not exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
    set @DATATABLE = null;

  select 
    @LASTHOUSEFILERANGEID = [ID]
  from dbo.[MKTSEGMENTATIONFINDERNUMBER]
  where [SEGMENTATIONID] = @SEGMENTATIONID
  and [VENDORID] is null
  and [MIN] = (select max([MIN]) from dbo.[MKTSEGMENTATIONFINDERNUMBER] where [SEGMENTATIONID] = @SEGMENTATIONID and [VENDORID] is null);


  declare CALCULATE cursor local fast_forward for
    select [ID], [VENDORID], [MIN], [MAX], [QUANTITY], [CHECKDIGIT]
    from @CALCULATIONS;

  open CALCULATE;
  fetch next from CALCULATE into @ID, @VENDORID, @MIN, @MAX, @QUANTITY, @CHECKDIGIT;

  while (@@FETCH_STATUS = 0)
    begin
      if @CHECKDIGIT = 1
        begin
          set @MIN = @MIN * 10;
          set @MAX = (@MAX * 10) + 9;
        end

      if @VENDORID is null
        begin
          set @ASSIGNED = 0;

          if not @DATATABLE is null
            exec sp_executesql @SQL, N'@ASSIGNED bigint output, @MIN bigint, @MAX bigint', @ASSIGNED = @ASSIGNED output, @MIN = @MIN, @MAX = @MAX;

          if @ID = @LASTHOUSEFILERANGEID
            set @ASSIGNED = isnull(@ASSIGNED, 0) + dbo.[UFN_MKTSEGMENTATIONSEED_GETSEEDCOUNT](@SEGMENTATIONID);

          update @CALCULATIONS set
            [ASSIGNED] = @ASSIGNED,
            [REMAINING] = case when @QUANTITY - @ASSIGNED < 0 then 0 else @QUANTITY - @ASSIGNED end
          where [ID] = @ID;
        end
      else
        update @CALCULATIONS set
          [ASSIGNED] = @QUANTITY,
          [REMAINING] = 0
        where [ID] = @ID;

      fetch next from CALCULATE into @ID, @VENDORID, @MIN, @MAX, @QUANTITY, @CHECKDIGIT;
    end

  close CALCULATE;
  deallocate CALCULATE;


  select
    @WIDTH = [FIXEDWIDTH],
    @CHECKDIGIT = [CHECKDIGIT]
  from dbo.[MKTFINDERNUMBER];

  select  
    [FN].[ID],
    isnull([C].[KEYNAME], '<House file>') as [VENDOR],
    [CAL].[QUANTITY],
    case 
      when [FN].[INUSE] = 1 then dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_BUILDRANGE]([FN].[MIN], [FN].[MAX], [FN].[FIXEDWIDTH], [FN].[CHECKDIGIT]) 
      else dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_BUILDRANGE]([FN].[MIN], [FN].[MAX], @WIDTH, @CHECKDIGIT) 
    end [RANGE],
    case 
      when [FN].[INUSE] = 1 then case [FN].[CHECKDIGIT] when 1 then 'Mod 10' else 'None' end
      else case @CHECKDIGIT when 1 then 'Mod 10' else 'None' end 
    end [CHECKDIGIT],
    case when [FN].[INUSE] = 1 then [FN].[FIXEDWIDTH] else @WIDTH end as [WIDTH],
    [CAL].[ASSIGNED],
    [CAL].[REMAINING]
  from @CALCULATIONS as [CAL]
  inner join dbo.[MKTSEGMENTATIONFINDERNUMBER] as [FN] on [FN].[ID] = [CAL].[ID]
  left outer join dbo.[CONSTITUENT] as [C] on [FN].[VENDORID] = [C].[ID];

  return 0;