USP_GETCMSUSERTAGINTEREST

CREATE procedure dbo.USP_GETCMSUSERTAGINTEREST(@USERGUID uniqueidentifier, @INCLUDEDTAGS xml, @USERINTERESTS xml, @CONSTANTS xml)
as
begin    

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

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

    --Declare the point scaler constants


    declare @InterestedPoints int
  set @InterestedPoints = (select ISNULL(c.c.value('@InterestedPoints', 'nvarchar(255)'),20) from @CONSTANTS.nodes('/constants') c(c))
  ------------------------------------

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


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

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

  --Now select out the included tags

  declare @IncludedTagsTable table ([ID] uniqueidentifier)    
  insert into @IncludedTagsTable
  select
  AOG.G.value('.','uniqueidentifier')
  from @INCLUDEDTAGS.nodes('/ArrayOfGuid/guid') AOG(G)
  ------------------------------------

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


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

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

  --Now select out the interests for the current user

  declare @UserInterestsTable table (ID uniqueidentifier)    
  insert into @UserInterestsTable
  select
  ITT.ID
  from @USERINTERESTS.nodes('/ArrayOfString/string') AOS(S)
  inner join dbo.TAGCODE TC on TC.[DESCRIPTION] = AOS.S.value('.','nvarchar(100)')
  inner join @IncludedTagsTable ITT on ITT.ID = TC.ID
  ------------------------------------

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


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

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

  --Next compute each tag's points


  --Select the tags points for being associated with the parts

  declare @ViewTagPoints table (TagID uniqueidentifier, points [float], type int) 

  insert into @ViewTagPoints
  select ITT.ID, SUM(ISNULL(PV.viewPoints,0)), 0
  from @IncludedTagsTable ITT
  left outer join dbo.SITECONTENTTAG SCT on SCT.TAGCODEID = ITT.ID
  left outer join #PartViews PV on SCT.SITECONTENTID = PV.partID
  group by ITT.ID

  --Select the tags points for the user being interested in them

  declare @InterestTagPoints table (TagID uniqueidentifier, points [float], type int)

  insert into @InterestTagPoints
  select UIT.ID, @InterestedPoints, 1
  from @UserInterestsTable UIT

  --Select the output

  select p.TagID, SUM(p.points) points
  from
  (
    select TagID, points, 1 as Type
    from @ViewTagPoints
    union 
    select TagID, points, 2 as Type
    from @InterestTagPoints  
   )p 
  group by p.TagID

end