USP_MKTFINDERFILE_UPDATEQUANTITIES

CREATE procedure dbo.[USP_MKTFINDERFILE_UPDATEQUANTITIES]
(
  @FINDERFILEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @SQL nvarchar(max);
  declare @FINDERFILETABLE nvarchar(128);
  declare @SEGMENTCURSOR cursor;
  declare @SEGMENTID uniqueidentifier;
  declare @TESTSEGMENTID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;
  declare @QUANTITY int;
  declare @CURRENTDATE datetime;

  begin try  
    set @CURRENTDATE = getdate();

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

    select
      @SEGMENTATIONID = [SEGMENTATIONID],
      @FINDERFILETABLE = dbo.[UFN_MKTFINDERFILE_BUILDTABLENAME]([SEGMENTATIONID])
    from dbo.[MKTFINDERFILEIMPORTPROCESS]
    where [ID] = @FINDERFILEID;


    --Get QUANTITY for MKTSEGMENTATIONSEGMENTLIST cache table

    set @SQL = 'set @SEGMENTCURSOR = cursor local fast_forward for' + char(13) +
               '  select [SC].[SEGMENTATIONSEGMENTID], count(distinct [FT].[FINDERNUMBER])' + char(13) +
               '  from dbo.[' + @FINDERFILETABLE + '] as [FT]' + char(13) +
               '  inner join dbo.[MKTSOURCECODEMAP] as [SC] on [FT].[SOURCECODE] = [SC].[SOURCECODE]' + char(13) +
               '  where [SC].[SEGMENTATIONID] = @SEGMENTATIONID' + char(13) +
               '  group by [SC].[SEGMENTATIONSEGMENTID];' + char(13) + 
               'open @SEGMENTCURSOR;';
    exec sp_executesql @SQL,N'@SEGMENTCURSOR cursor output, @SEGMENTATIONID uniqueidentifier', @SEGMENTCURSOR = @SEGMENTCURSOR output, @SEGMENTATIONID = @SEGMENTATIONID;
    fetch next from @SEGMENTCURSOR into @SEGMENTID, @QUANTITY;

    while(@@fetch_status = 0)
    begin
      --Update the segment's rental and exchange override quantities...

      update dbo.[MKTSEGMENTATIONSEGMENTLIST] set
        [EXCHANGEQUANTITY] = (case when [EXCHANGEQUANTITY] > @QUANTITY then @QUANTITY else [EXCHANGEQUANTITY] end),
        [RENTALQUANTITY] = (case when [EXCHANGEQUANTITY] - @QUANTITY > 0 then 0 else @QUANTITY - [EXCHANGEQUANTITY] end),
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = @CURRENTDATE
      where [ID] = @SEGMENTID;

      --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, @QUANTITY;
    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 [SC].[SEGMENTATIONSEGMENTID], [SC].[SEGMENTATIONTESTSEGMENTID], count(distinct [FT].[FINDERNUMBER])' + char(13) +
               '  from dbo.[' + @FINDERFILETABLE + '] as [FT]' + char(13) +
               '  inner join dbo.[MKTSOURCECODEMAP] as [SC] on [FT].[SOURCECODE] = [SC].[SOURCECODE]' + char(13) +
               '  where [SC].[SEGMENTATIONID] = @SEGMENTATIONID and [SC].[SEGMENTATIONTESTSEGMENTID] is not null' + char(13) +
               '  group by [SC].[SEGMENTATIONSEGMENTID], [SC].[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 @SEGMENTID, @TESTSEGMENTID, @QUANTITY;

    while(@@fetch_status = 0)
    begin
      --Update the test segment to the actual quantity from the finder file...

      update dbo.[MKTSEGMENTATIONTESTSEGMENT] set
        [SAMPLESIZE] = @QUANTITY,
        [SAMPLESIZETYPECODE] = 1,
        [CHANGEDBYID] = @CHANGEAGENTID,
 [DATECHANGED] = @CURRENTDATE
      where [ID] = @TESTSEGMENTID
      and [SEGMENTID] = @SEGMENTID;

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

    close @SEGMENTCURSOR;
    deallocate @SEGMENTCURSOR;
  end try

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

  return 0;