USP_FILEVIEWDATA_ADDFILEVIEW

create procedure dbo.USP_FILEVIEWDATA_ADDFILEVIEW
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
      @FILEVIEWXMLDATA xml = '',
    @MAXRECORDS int = 0
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

  declare @IsUserGuid bit
  set @IsUserGuid = (select 1 from dbo.ClientUsers CU where CU.[GUID] = @ID)

  declare @XMLDATA xml
  if @IsUserGuid = 1
  begin
    set @XMLDATA = (select [XMLDATA] from [dbo].[FILEVIEWDATA] where [ID] = @ID)
  end
  else
  begin    
    declare @TEMP table (BinaryData image, TextData ntext)
    insert into @TEMP
    exec [dbo].[spTempDataFetch] @ID    

    set @XMLDATA = (select top 1 TEXTDATA from @TEMP)
    declare @TEXTDATA nvarchar(max)
    declare @EXPIRATION datetime
    set @EXPIRATION = DATEADD(d,30, GetUTCDate())
  end    

  if @XMLDATA is null
  begin
    set @XMLDATA = '<FileViews />'


    if @IsUserGuid = 1
    begin
      insert into [dbo].[FILEVIEWDATA]
          (ID, XMLData, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      values
          (@ID, @XMLDATA, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
    end
    else
    begin    
      set @TEXTDATA = CAST(@XMLDATA as nvarchar(max))
      exec [dbo].[spTempDataAdd] @ID, null, @TEXTDATA, @EXPIRATION
    end  


  end

  declare @HASTIME bit
  set @HASTIME = (select 1 from @FILEVIEWXMLDATA.nodes('Fl[1]/@T') FL(T))
  if @HASTIME is null
  begin
    --Set the date/time of the file view
    declare @T datetime
    set @T = GETUTCDATE()
    set @FILEVIEWXMLDATA.modify('insert attribute T {sql:variable("@T")} into (/Fl)[1]')
  end

  set @XMLDATA.modify('insert sql:variable("@FILEVIEWXMLDATA") as first into (/FileViews)[1]')

  if @MAXRECORDS > 0
    set @XMLDATA.modify('delete /FileViews/Fl[position()>sql:variable("@MAXRECORDS")]')

  if @IsUserGuid = 1
  begin
    update [dbo].[FILEVIEWDATA] with (rowlock)
    set [XMLDATA] = @XMLDATA
    where [ID] = @ID
  end
  else
  begin      
      set @TEXTDATA = CAST(@XMLDATA as nvarchar(max))
      exec [dbo].[spTempDataAdd] @ID, null, @TEXTDATA, @EXPIRATION
  end
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0