USP_DATALIST_SUGGESTEDCONTENT2

CREATE procedure dbo.USP_DATALIST_SUGGESTEDCONTENT2(@USERGUID uniqueidentifier, @CLIENTSITESID int, @INCLUDEDTAGS xml, @USERINTERESTS xml, @MAXNUMBEROFROWS int, @CONSTANTS xml, @CURRENTLANGUAGE nvarchar(256), @TYPESTOINCLUDE int = 0)
as
    set nocount on;

--    For TYPESTOINCLUDE

--    Parts Only = 0

--    Files Only = 1

--    Parts & Files = 2



--Common to any call



create table #PartViews (partID int, viewPoints float, viewedPenalty float)
create table #FileViews (FileID int, viewPoints float, viewedPenalty float)
declare @TagPoints table (TagID uniqueidentifier, points [float])

--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)

--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

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())

declare @contentPoints table (partID int, fileID int, points float)

--Calculate points for each tag based on these part/file views

    insert into @TagPoints
    exec USP_GETCMSUSERTAGINTEREST @USERGUID, @INCLUDEDTAGS, @USERINTERESTS, @CONSTANTS





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



--Getting Part points

if @TYPESTOINCLUDE in (0,2)
begin

--Load part view data for current user- sets data in #PartViews

    exec USP_GETCMSUSERPARTVIEWS @USERGUID, @CONSTANTS



    --Find the suggested part points

    insert into @contentPoints
    select SC.ID, -1, ALLPOINTS.points
    from
    (
        select TP.SITECONTENTID, SUM(TP.points) as points
        from
            (        
        select SCT.SITECONTENTID, TP.points, 1 as type
        from dbo.SITECONTENTTAG SCT
        inner join @TagPoints TP on TP.TagID = SCT.TAGCODEID

                union

        select PV.partID, SUM(PV.viewedPenalty) as points,  2 as type
        from #PartViews PV
        left outer join
          (
            select SCT.SITECONTENTID
            from dbo.SITECONTENTTAG SCT
            inner join @IncludedTagsTable ITT on ITT.ID = SCT.TAGCODEID
          ) PARTTAGS on PARTTAGS.SITECONTENTID = PV.partID
         where PARTTAGS.SITECONTENTID is not null
         group by PV.partID

            ) TP
            group by TP.SITECONTENTID
        ) ALLPOINTS
    inner join dbo.vwSiteContent SC on SC.ID = ALLPOINTS.SITECONTENTID
        inner join dbo.UFN_CLIENTUSERS_OBJECTSWITHTASKRIGHT(@CLIENTUSERSID, 1, 0, '6AFA81B1-0B5F-454B-B810-A17D72EB70B6') F  on F.ObjectGuid = SC.Guid
    --The guid above is the Has All Part Related Rights task guid    

    where (SC.IsTimePublished = 0 Or (SC.CONTENTEXPIREDATE > @TIME AND SC.CONTENTPUBLISHDATE < @TIME))
    and @CURRENTLANGUAGE like ISNULL(SC.LanguageTarget, '') + '%'
    and SC.Status = 2

end


--Getting File points

if @TYPESTOINCLUDE in (1,2)
begin

--Load part view data for current user- sets data in #PartViews

    exec USP_GETCMSUSERFILEVIEWS @USERGUID, @CONSTANTS


--Get File Points


insert into @contentPoints
    select -1,SF.ID, ALLPOINTS.points
    from
    (
        select TP.FILEID, SUM(TP.points) as points
        from
            (        
        select SFT.FILEID, TP.points, 1 as type
        from dbo.SITEFILETAG SFT
        inner join @TagPoints TP on TP.TagID = SFT.TAGCODEID

                union

        select FV.fileID, SUM(FV.viewedPenalty) as points,  2 as type
        from #FileViews FV
        left outer join
          (
            select SFT.FILEID
            from dbo.SITEFILETAG SFT
          inner join @IncludedTagsTable ITT on ITT.ID = SFT.TAGCODEID
          ) FILETAGS on FILETAGS.FILEID = FV.fileID
         where FILETAGS.FILEID is not null
         group by FV.fileID

            ) TP
            group by TP.FILEID
        ) ALLPOINTS
    inner join(
        select ID, Name, Guid, DISCOVERABLE, Approved, INTRASHBIN from dbo.DocUploadFiles
    )SF on SF.ID = ALLPOINTS.FILEID
        inner join dbo.UFN_CLIENTUSERS_OBJECTSWITHTASKRIGHT(@CLIENTUSERSID, 41, 0, '6A2614A4-23DF-45E6-9668-7483F3464B26') F  on F.ObjectGuid = SF.Guid
    --The guid above is the Has All File Related Rights task guid    

    where SF.DISCOVERABLE = 1
    and SF.Approved = 1
    and SF.INTRASHBIN = 0
end




--Finally, get results from whatever is in contentPoint table (parts/files/combined)

  declare @RESULTS table (PAGEID int, FILEID int, TITLE nvarchar(255), SUMMARY nvarchar(4000), POINTS float, UPDATEDATE datetime)

 if @TYPESTOINCLUDE in (0,2)
 begin 
    insert into @RESULTS
    select partPages.PageID, -1, SC.DiscoverableTitle as Title, SC.DiscoverableSummary as Summary, partPages.points, SC.UpdateDate
    from
    (
        select MAX(PC.SitePagesID) as PageID, SC.ID as PartID, ctp.points
        from @contentPoints ctp
        inner join dbo.PageContent PC on pc.SiteContentID = ctp.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
        where (SP.ClientSitesID = @CLIENTSITESID or @CLIENTSITESID is null)
        and SC.Discoverable = 1
        and SC.Deleted = 0
        and SP.Deleted = 0
        group by SC.ID, ctp.points
    ) partPages
    inner join dbo.SiteContent SC on SC.ID = partPages.PartID
    --order by partPages.points desc

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

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

end

if @TYPESTOINCLUDE in (1,2)
begin

   insert into @RESULTS
   select 
        -1, DUF.ID, DUF.DISCOVERABLETITLE as Title, DUF.DISCOVERABLESUMMARY as Summary, ctp.points, DUF.UploadDate 
   from    @contentPoints ctp
    inner join dbo.DocUploadFiles DUF on DUF.ID = ctp.fileID
    where DUF.INTRASHBIN = 0 and (DUF.ClientSitesID = @CLIENTSITESID or @CLIENTSITESID is null)
    --order by files.points desc

end   


  set ROWCOUNT  @MAXNUMBEROFROWS
  select * from @RESULTS order by POINTS desc
  set ROWCOUNT  0

  drop table #PartViews  
  drop table #FileViews