USP_DATALIST_SUGGESTEDCONTENT
Returns a list of pages and descriptions with content related to the specified users stated interests and browsing history.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @USERGUID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @CLIENTSITESID | int | IN | Client Sites ID |
| @INCLUDEDTAGS | xml | IN | Tags to include |
| @INCLUDEDINTERESTS | xml | IN | Interests to include |
| @USERINTERESTS | xml | IN | User Interests |
| @MAXNUMBEROFROWS | int | IN | Maximum number of rows to return |
| @CONSTANTS | xml | IN | Constants to tune the suggestions |
| @CURRENTLANGUAGE | nvarchar(256) | IN | Language to filter the parts based on |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_SUGGESTEDCONTENT(@USERGUID uniqueidentifier, @CLIENTSITESID int, @INCLUDEDTAGS xml, @INCLUDEDINTERESTS xml, @USERINTERESTS xml, @MAXNUMBEROFROWS int, @CONSTANTS xml, @CURRENTLANGUAGE nvarchar(256))
as
set nocount on;
declare @XMLDATA xml
set @XMLDATA = (select [XMLDATA] from [dbo].[PAGEVIEWDATA] 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 @InterestedPoints int
set @InterestedPoints = (select ISNULL(c.c.value('@InterestedPoints', 'nvarchar(255)'),20) from @CONSTANTS.nodes('/constants') c(c))
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))
------------------------------------
------------------------------------
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)
------------------------------------
------------------------------------
------------------------------------
------------------------------------
---------Now select out the included tags
declare @IncludedTagsTable table (ID int)
insert into @IncludedTagsTable
select
AOI.I.value('.','int')
from @INCLUDEDTAGS.nodes('/ArrayOfInt/int') AOI(I)
------------------------------------
------------------------------------
------------------------------------
------------------------------------
---------Now select out the included interests
declare @IncludedInteretsTable table (ID int)
insert into @IncludedInteretsTable
select
AOI.I.value('.','int')
from @INCLUDEDINTERESTS.nodes('/ArrayOfInt/int') AOI(I)
------------------------------------
------------------------------------
------------------------------------
------------------------------------
---------Select out the part views for the current user
declare @baseTable table (partID int, Time datetime, timepercentViewPoints float, timepercentViewedPenalty float)
declare @partViews table (partGuid uniqueidentifier, viewPoints float, viewedPenalty float)
insert into @baseTable
select
T2.P.value('@ID', 'int') as PartID,
PVS.PV.value('@T', 'datetime') as Time,
0,
0
from @XMLDATA.nodes('/PageViews/Pg') as PVS(PV)
cross apply PVS.PV.nodes('./Pt') as T2(P)
order by partid
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 @partViews
select SC.Guid, p.viewPoints, p.viewedPenalty
from
(select partID, SUM(@OldViewsPoints + timepercentViewPoints * @ViewsPointsRange) as viewPoints, SUM(@OldViewedPenalty + timepercentViewedPenalty * @ViewedPenaltyRange) as viewedPenalty
from @baseTable
group by partID) p
inner join dbo.SiteContent SC on SC.ID = p.partID
------------------------------------
------------------------------------
------------------------------------
------------------------------------
---------Now select out the interests for the current user
declare @UserInterestsTable table (interestID int)
insert into @UserInterestsTable
select
IIT.ID
from @USERINTERESTS.nodes('/ArrayOfInt/int') AOI(I)
inner join @IncludedInteretsTable IIT on IIT.ID = AOI.I.value('.','int')
------------------------------------
------------------------------------
------------------------------------
------------------------------------
------- Next compute each tag's points
declare @RegularTagPoints table (TagID int, points [float])
declare @InterestTagPoints table (TagID int, points [float])
--Select the traditional tags associated with the parts
insert into @RegularTagPoints
select OT.CodeTableTagID, SUM(ISNULL(PV.viewPoints,0))
from @IncludedTagsTable ITT
inner join dbo.ObjectTags OT on ITT.ID = OT.CodeTableTagID
left outer join @partViews PV on OT.ObjectGuid = PV.partGuid
group by OT.CodeTableTagID
--Select the interests tags associated with the parts
insert into @InterestTagPoints
select i.interestID, SUM(I.points)
from
(
select OT.INTERESTID as interestID, ISNULL(PV.viewPoints, 0) as points, 1 as type
from @IncludedInteretsTable IIT
inner join dbo.CMSOBJECTINTERESTTAG OT on IIT.ID = OT.INTERESTID
left outer join @partViews PV on OT.ObjectGuid = PV.partGuid
union
select UIT.interestID, @InterestedPoints, 2 as type from @UserInterestsTable UIT
)I
group by i.interestID
------------------------------------
------------------------------------
------------------------------------
------------------------------------
------ Now choose find the suggested part points
declare @partPoints table (partID int, points float)
declare @CLIENTUSERSID int
set @CLIENTUSERSID = (select top 1 CU.ID from dbo.clientUsers CU where CU.Guid = @USERGUID)
declare @TIME datetime
set @TIME = (select GETUTCDATE())
insert into @partPoints
select SC.ID, ALLPOINTS.points
from
(
select TP.ObjectGuid, SUM(TP.points) as points
from
(
select OT.ObjectGuid, RPT.points, OT.CodeTableTagID as ID, 1 as type
from dbo.ObjectTags OT
inner join @RegularTagPoints RPT on RPT.TagID = OT.CodeTableTagID
union
select OT.ObjectGuid, IPT.points, OT.INTERESTID as ID, 2 as type
from dbo.CMSOBJECTINTERESTTAG OT
inner join @InterestTagPoints IPT on IPT.TagID = OT.INTERESTID
union
select PV.partGuid, PV.viewedPenalty as points, PV.viewedPenalty as ID, 3 as type
from @partViews PV
left outer join (
select OT.ObjectGuid
from dbo.ObjectTags OT
inner join @IncludedTagsTable ITT on ITT.ID = OT.CodeTableTagID
)PartTags on PartTags.ObjectGuid = PV.partGuid
left outer join (
select OT.ObjectGuid
from dbo.CMSOBJECTINTERESTTAG OT
inner join @IncludedInteretsTable IIT on IIT.ID = OT.INTERESTID
)PartInterests on PartInterests.ObjectGuid = PV.partGuid
where PartTags.ObjectGuid is not null or PartInterests.OBJECTGUID is not null
group by PV.partGuid, PV.viewedPenalty
) TP
group by TP.ObjectGuid
) ALLPOINTS
inner join dbo.UFN_CLIENTUSERS_OBJECTSWITHTASKRIGHT(@CLIENTUSERSID, 1, 0, '6AFA81B1-0B5F-454B-B810-A17D72EB70B6') F on F.ObjectGuid = ALLPOINTS.ObjectGuid
--The guid above is the Has All Part Related Rights task guid
inner join dbo.vwSiteContent SC on SC.ID = F.RECORDID
where (SC.IsTimePublished = 0 Or (SC.CONTENTEXPIREDATE > @TIME AND SC.CONTENTPUBLISHDATE < @TIME))
and @CURRENTLANGUAGE like ISNULL(SC.LanguageTarget, '') + '%'
and SC.Status = 2
declare @RESULTS table (PAGEID int, TITLE nvarchar(255), SUMMARY nvarchar(4000), POINTS float, UPDATEDATE datetime)
insert into @RESULTS
select partPages.PageID, SC.DiscoverableTitle as Title, SC.DiscoverableSummary as Summary, partPages.points, SC.UpdateDate
from
(
select MAX(PC.SitePagesID) as PageID, SC.ID as PartID, pp.points
from @partPoints pp
inner join dbo.PageContent PC on pc.SiteContentID = pp.partID
inner join dbo.SiteContent SC on SC.ID = PC.SiteContentID
inner join dbo.UFN_CLIENTUSERS_OBJECTSWITHTASKRIGHT(@CLIENTUSERSID, 2, 0, '546AD356-A750-49B7-B612-67857C6F1A00') p on p.RECORDID = PC.SitePagesID
inner join dbo.sitePages SP on SP.ID = p.RECORDID
and (SP.ClientSitesID = @CLIENTSITESID or @CLIENTSITESID is null)
and SC.Discoverable = 1
group by SC.ID, pp.points
) partPages
inner join dbo.SiteContent SC on SC.ID = partPages.PartID
order by partPages.points desc
------------------------------------
------------------------------------
set ROWCOUNT @MAXNUMBEROFROWS
select * from @RESULTS
set ROWCOUNT 0
end