USP_GETCMSUSERFILEVIEWS

CREATE procedure dbo.USP_GETCMSUSERFILEVIEWS(@USERGUID uniqueidentifier, @CONSTANTS xml)
as
begin

   --Define output table

  declare @fileViews table (fileID int, viewPoints float, viewedPenalty float)

  --Load the users page view data

  declare @XMLDATA xml
  set @XMLDATA = (select [XMLDATA] from [dbo].[FILEVIEWDATA] where [ID] = @USERGUID)

  if @XMLDATA is null
  begin 
    declare @TEMP table (BinaryData image, TextData ntext)
    insert into @TEMP
    exec [dbo].[spTempDataFetch] @USERGUID    

    set @XMLDATA = (select top 1 TEXTDATA from @TEMP)
  end    

  if @XMLDATA is not null
  begin

    ------------------------------------

      ------------------------------------

      --Declare the point scaler constants


      declare @OldViewsPoints int  
    set @OldViewsPoints = (select ISNULL(c.c.value('@OldViewsPoints', 'nvarchar(255)'),1) from @CONSTANTS.nodes('/constants') c(c))  
    declare @RecentViewsPoints int  
    set @RecentViewsPoints = (select ISNULL(c.c.value('@RecentViewsPoints', 'nvarchar(255)'),5) from @CONSTANTS.nodes('/constants') c(c))  
    declare @ViewsPointsTimeRange int  
    set @ViewsPointsTimeRange = (select ISNULL(c.c.value('@ViewsPointsTimeRange', 'nvarchar(255)'),7) from @CONSTANTS.nodes('/constants') c(c))


      declare @OldViewedPenalty int  
    set @OldViewedPenalty = (select ISNULL(c.c.value('@OldViewedPenalty', 'nvarchar(255)'),5) from @CONSTANTS.nodes('/constants') c(c))  
    declare @RecentViewedPenalty int  
    set @RecentViewedPenalty = (select ISNULL(c.c.value('@RecentViewedPenalty', 'nvarchar(255)'),25) from @CONSTANTS.nodes('/constants') c(c))  
    declare @ViewedPenaltyTimeRange int  
    set @ViewedPenaltyTimeRange = (select ISNULL(c.c.value('@ViewedPenaltyTimeRange', 'nvarchar(255)'),7) from @CONSTANTS.nodes('/constants') c(c))
    ------------------------------------

    ------------------------------------


      ------------------------------------

      ------------------------------------

    --Apply some transformations on these constants

    set @ViewsPointsTimeRange = @ViewsPointsTimeRange * -1  
    set @ViewedPenaltyTimeRange = @ViewedPenaltyTimeRange * -1
    set @OldViewedPenalty = @OldViewedPenalty * -1
    set @RecentViewedPenalty = @RecentViewedPenalty * -1

    declare @ViewsPointsRange int
    set @ViewsPointsRange = @RecentViewsPoints - @OldViewsPoints

    declare @ViewedPenaltyRange int
    set @ViewedPenaltyRange = @RecentViewedPenalty - @OldViewedPenalty
    ------------------------------------

    ------------------------------------


    ------------------------------------

    ------------------------------------

    --Initialize some time vars

    declare @now datetime
    set @now = GETUTCDATE()

    declare @baseDifferenceViewPoints int
    set @baseDifferenceViewPoints = DATEDIFF(s, DATEADD(d, @ViewsPointsTimeRange, @now), @now)

    declare @baseDifferenceViewedPenalty int
    set @baseDifferenceViewedPenalty = DATEDIFF(s, DATEADD(d, @ViewedPenaltyTimeRange, @now), @now)
    ------------------------------------

    ------------------------------------


    -----------------------------------

      ------------------------------------

      --Select out the file views for the current user

      declare @baseTable table (fileID int, Time datetime, timepercentViewPoints float, timepercentViewedPenalty float)

      insert into @baseTable
      select 
      Fvs.Fl.value('@ID', 'int') as FileID,
      Fvs.Fl.value('@T', 'datetime') as Time,
    0,
    0
      from @XMLDATA.nodes('/FileViews/Fl') as Fvs(Fl)
      order by fileid  

    update @baseTable
    set timepercentViewPoints = (@baseDifferenceViewPoints - cast(DATEDIFF(s, BT.Time, @now) as float))/@baseDifferenceViewPoints
    from @baseTable BT
    where BT.Time > DATEADD(d, @ViewsPointsTimeRange, @now)

    update @baseTable
    set timepercentViewedPenalty = (@baseDifferenceViewedPenalty - cast(DATEDIFF(s, BT.Time, @now) as float))/@baseDifferenceViewedPenalty
    from @baseTable BT
    where BT.Time > DATEADD(d, @ViewedPenaltyTimeRange, @now)

      insert into @fileViews
      select DUF.ID, p.viewPoints, p.viewedPenalty
      from
      (select fileID, SUM(@OldViewsPoints + timepercentViewPoints * @ViewsPointsRange) as viewPoints, SUM(@OldViewedPenalty + timepercentViewedPenalty * @ViewedPenaltyRange) as viewedPenalty
      from @baseTable
      group by fileID) p
      inner join dbo.docuploadfiles DUF on DUF.ID = p.fileID
      ------------------------------------

      ------------------------------------


  end

  insert into #FileViews
  select * from @fileViews

end