USP_MKTCONSTITUENTFILEIMPORT_UPDATEQUANTITIES

CREATE procedure dbo.[USP_MKTCONSTITUENTFILEIMPORT_UPDATEQUANTITIES]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @MINIMUMSEQUENCE int = 0,
  @MAXIMUMSEQUENCE int = 2147483647
)
as
  set nocount on;

  declare @SEGMENTID uniqueidentifier;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @SQL nvarchar(max);
  declare @CONSTITFILETABLE nvarchar(128);
  declare @SEQUENCE int;
  declare @CACHECLEARED bit;
  declare @SEGMENTCURSOR cursor;
  declare @CURRENTDATE datetime;
  declare @QUANTITY int;

  begin try
    set @CURRENTDATE = getdate();

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

    set @CACHECLEARED = 0;
    set @CONSTITFILETABLE = dbo.[UFN_MKTCONSTITUENTFILEIMPORT_BUILDTABLENAME](@SEGMENTATIONID);

    set @SQL = 
      'set @SEGMENTCURSOR = cursor local fast_forward for' + char(13) +
      '  select distinct' + char(13) +
      '    [MKTSEGMENTATIONSEGMENT].[ID],' + char(13) +
      '    [MKTSEGMENTATIONSEGMENT].[SEQUENCE]' + char(13) +
      '  from dbo.[MKTSEGMENTATIONSEGMENT]' + char(13) +
      '  inner join dbo.[' + @CONSTITFILETABLE + '] as [SEGHOUSEFILE] on [SEGHOUSEFILE].[SEGMENTATIONSEGMENTID] = [MKTSEGMENTATIONSEGMENT].[ID]' + char(13) +
      '  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTID]' + char(13) +
      '  left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]' + char(13) +
      '  where ' + char(13) +
      '    [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID and' + char(13) +
      '    [MKTSEGMENTATIONSEGMENT].[SEQUENCE] between @MINIMUMSEQUENCE and @MAXIMUMSEQUENCE and' + char(13) +
      '    ([MKTSEGMENT].[SEGMENTTYPECODE] in (1, 4, 5) or ([MKTSEGMENT].[SEGMENTTYPECODE] = 2 and [MKTSEGMENTLIST].[TYPECODE] = 0)) ' + char(13) +
      '  order by [MKTSEGMENTATIONSEGMENT].[SEQUENCE];' + char(13) +
      'open @SEGMENTCURSOR;'

    exec sp_executesql @SQL, N'@SEGMENTCURSOR cursor output, @SEGMENTATIONID uniqueidentifier, @MINIMUMSEQUENCE int, @MAXIMUMSEQUENCE int', @SEGMENTCURSOR = @SEGMENTCURSOR output, @SEGMENTATIONID = @SEGMENTATIONID, @MINIMUMSEQUENCE = @MINIMUMSEQUENCE, @MAXIMUMSEQUENCE = @MAXIMUMSEQUENCE;

    fetch next from @SEGMENTCURSOR into @SEGMENTID, @SEQUENCE;  
    while (@@FETCH_STATUS = 0)
    begin
      /* Only run clear cache for the lowest sequence segment. */
      if @CACHECLEARED = 0
        begin
          exec dbo.[USP_MKTSEGMENTATIONSEGMENT_CLEARCACHE] @SEGMENTID, 0, 0;
          set @CACHECLEARED = 1
        end

      /* Reset all the test segment counts to zero for this segment... */
      update dbo.[MKTSEGMENTATIONTESTSEGMENT] set 
        [SAMPLESIZE] = 0,
        [SAMPLESIZETYPECODE] = 1,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [SEGMENTID] = @SEGMENTID;

      fetch next from @SEGMENTCURSOR into @SEGMENTID, @SEQUENCE;
    end;

    close @SEGMENTCURSOR;
    deallocate @SEGMENTCURSOR;

    /* Update the sample size in the MKTSEGMENTATIONTESTSEGMENT table. */
    set @SQL = 
      'set @SEGMENTCURSOR = cursor local fast_forward for' + char(13) +
      '  select distinct' + char(13) +
      '    [SEGHOUSEFILE].[SEGMENTATIONTESTSEGMENTID],' + char(13) +
      '    count(distinct [SEGHOUSEFILE].[RECORDID])' + char(13) +
      '  from dbo.[MKTSEGMENTATIONTESTSEGMENT]' + char(13) +
      '  inner join dbo.[' + @CONSTITFILETABLE + '] as [SEGHOUSEFILE] on [SEGHOUSEFILE].[SEGMENTATIONTESTSEGMENTID] = [MKTSEGMENTATIONTESTSEGMENT].[ID]' + char(13) +
      '  inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [MKTSEGMENTATIONTESTSEGMENT].[SEGMENTID]' + char(13) +
      '  where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
      '  group by [SEGHOUSEFILE].[SEGMENTATIONTESTSEGMENTID]' + char(13) +
      'open @SEGMENTCURSOR;'

    exec sp_executesql @SQL,N'@SEGMENTCURSOR cursor output, @SEGMENTATIONID uniqueidentifier', @SEGMENTCURSOR = @SEGMENTCURSOR output, @SEGMENTATIONID = @SEGMENTATIONID;

    fetch next from @SEGMENTCURSOR into @TESTSEGMENTID, @QUANTITY;
    while(@@fetch_status = 0)
    begin
      /* Update the test segment to the actual quantity from the constituent file... */
      update dbo.[MKTSEGMENTATIONTESTSEGMENT] set
        [SAMPLESIZE] = @QUANTITY,
        [SAMPLESIZETYPECODE] = 1,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @TESTSEGMENTID;

      fetch next from @SEGMENTCURSOR into @TESTSEGMENTID, @QUANTITY;
    end

    close @SEGMENTCURSOR;
    deallocate @SEGMENTCURSOR;
  end try

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

  return 0;