USP_MKTSEGMENT_UPDATEDATEREFRESHED

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

  declare @ISCHILDLISTSEGMENT bit;
  declare @SEGMENTLISTID uniqueidentifier;
  declare @SEGMENTVIEW nvarchar(60);
  declare @COUNTSQL nvarchar(max);
  declare @NUMROWS int;

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

    select
      @ISCHILDLISTSEGMENT = (case when [MKTSEGMENTLIST].[PARENTSEGMENTID] is null then 0 else 1 end),
      @SEGMENTLISTID = [MKTSEGMENTLIST].[ID]
    from dbo.[MKTSEGMENT]
    inner join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    where [MKTSEGMENT].[ID] = @ID;

    --Update the date refreshed for the segment...

    update dbo.[MKTSEGMENT] set
      [DATEREFRESHED] = getdate(),
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where [ID] = @ID;

    --Get the segment view row count...

    set @SEGMENTVIEW = dbo.[UFN_MKTSEGMENT_MAKEVIEWNAME](@ID);
    set @COUNTSQL = 'select @NUMROWS = count(*) from dbo.[' + @SEGMENTVIEW + ']';
    exec sp_executeSQL @COUNTSQL, N'@NUMROWS int output', @NUMROWS = @NUMROWS output;

    --Update the row count for the segment ID set record...

    update dbo.[IDSETREGISTER] set
      [NUMROWS] = @NUMROWS,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = getdate()
    where [DBOBJECTNAME] = @SEGMENTVIEW;

    --If we are refreshing a child list segment, then we need to update the rental and exchange quantities too...

    if @ISCHILDLISTSEGMENT = 1 and @SEGMENTLISTID is not null
      update dbo.[MKTSEGMENTLIST] set
        [RENTALQUANTITY] = (case when [EXCHANGEQUANTITY] - @NUMROWS > 0 then 0 else @NUMROWS - [EXCHANGEQUANTITY] end),
        [EXCHANGEQUANTITY] = (case when [EXCHANGEQUANTITY] > @NUMROWS then @NUMROWS else [EXCHANGEQUANTITY] end),
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = getdate()
      where [ID] = @SEGMENTLISTID;
  end try

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

  return 0;