EA7_spGetRecentBlogPostsForUser

CREATE PROCEDURE dbo.EA7_spGetRecentBlogPostsForUser (@EEID INT, @BBNCID INT, @ClassIDs NVARCHAR(100))
AS
SET NOCOUNT ON

    DECLARE @RealmContextIDs TABLE (
        ContextID INT,
        RealmType INT,
        ContextName VARCHAR(100)
    )

    DECLARE @Blogs TABLE (
        SitePage INT,
        UserPage INT,
        RealmType INT,
        RealmContext INT,
        NewsChannel INT,
        NewsStory INT,
        Author INT,
        PostDate DATETIME,
        RealmContextName NVARCHAR(100),
        PostTitle NVARCHAR(510),
        PostDescription NTEXT
    )

    --Get all classes for user

    INSERT INTO @RealmContextIDs (ContextID, RealmType)
    SELECT  IDs.id, 6  --RealmType of SchoolClass (6)

    FROM dbo.fnMakeIDsTableFromString (@ClassIDs, ',') IDs

    --Get all teams for user

    --When user is a student

    INSERT INTO @RealmContextIDs (ContextID, RealmType)
    SELECT ar.TeamID, 7
    FROM dbo.ATHLETICS_ROSTERS ar
    WHERE ar.PlayerEEID = @EEID
    --When user is faculty

    INSERT INTO @RealmContextIDs (ContextID, RealmType)
    SELECT ac.TeamID, 7
    FROM dbo.ATHLETICS_EECOACHES ac
    WHERE ac.CoachEEID = @EEID

    INSERT INTO @Blogs (SitePage, UserPage, RealmType, RealmContext, NewsChannel)
        --Get all blogs for classes and teams the user is associated with

        (SELECT DISTINCT upmpt.SitePageID, 
                        up.ID, 
                        upmpt.RealmType, 
                        up.RealmContextId, 
                        CONVERT(XML, pcd.XMLData).value('/ClassBlogPersonalContent[1]/NewsChannelID[1]', 'int')
        FROM dbo.UserPageManagerPageTemplates upmpt
            INNER JOIN dbo.UserPages up
                ON upmpt.ID = up.UserPageTemplateId
            INNER JOIN @RealmContextIDs rcid
                ON upmpt.RealmType = rcid.RealmType
                    AND up.RealmContextId = rcid.ContextID
            INNER JOIN PersonalizedContentData pcd
                ON up.ID = pcd.UserPageID
                    AND upmpt.RealmType = pcd.ParentType
                    AND up.RealmContextId = pcd.ParentID
            INNER JOIN dbo.SiteContent sc
                ON pcd.SiteContentID = sc.ID AND sc.ContentTypesID = 9018 --Class/Team Blog

        WHERE up.IsPublished = 1
    UNION
        --Get all blogs on pages user owns or can edit

        SELECT DISTINCT upmpt.SitePageID, 
                        up.ID, 
                        upmpt.RealmType, 
                        up.RealmContextId, 
                        CONVERT(XML, pcd.XMLData).value('/ClassBlogPersonalContent[1]/NewsChannelID[1]', 'int')
        FROM dbo.UserPageManagerPageTemplates upmpt
            INNER JOIN dbo.UserPages up
                ON upmpt.ID = up.UserPageTemplateId
            LEFT JOIN dbo.UserPagesEditors upe
                ON up.ID = upe.UserPagesID
            INNER JOIN PersonalizedContentData pcd
                ON up.ID = pcd.UserPageID
                    AND upmpt.RealmType = pcd.ParentType
                    AND up.RealmContextId = pcd.ParentID
            INNER JOIN dbo.SiteContent sc
                ON pcd.SiteContentID = sc.ID AND sc.ContentTypesID = 9018 --Class/Team Blog

        WHERE (up.ClientUserId = @BBNCID
            OR upe.ClientUsersID = @BBNCID)
            AND up.IsPublished = 1)

    --Get the newest news story for each blog

    UPDATE @Blogs
    SET NewsStory = 
        (SELECT TOP 1 ns.ID
        FROM dbo.NewsStories ns
        WHERE NewsChannel = ns.NewsChannelID
        ORDER BY ns.PublicationDate DESC)
    WHERE NOT NewsChannel IS NULL

    --Set the news story information

    UPDATE @Blogs
    SET    Author = ns.PostId,
        PostDate = ns.PublicationDate,
        PostTitle = ns.Title,
        PostDescription = 
        (CASE LEN(ns.Title)
            WHEN 0 THEN    ns.Description
            ELSE ''
         END)
    FROM dbo.NewsStories ns
    WHERE NewsStory = ns.ID
        AND NOT NewsChannel IS NULL

    --Set RealmContextName for team page blogs (Sport - Division Gender)

    UPDATE @Blogs
    SET RealmContextName =    
        cte1.Description +
        (CASE
            WHEN ((LEN(COALESCE(cte2.description, '')) + LEN(COALESCE(cte3.Description, ''))) > 0)
                THEN ' -' + COALESCE(' ' + cte2.Description, '') + COALESCE(' ' + cte3.Description, '')
            ELSE
                ''
         END)
    FROM dbo.ATHLETICS_TEAMS at 
        LEFT JOIN dbo.SiteCodeTableEntries cte1
            ON at.Sport = cte1.EntryGUID
        LEFT JOIN dbo.SiteCodeTableEntries cte2
            ON at.Division = cte2.EntryGUID
        LEFT JOIN dbo.SiteCodeTableEntries cte3
            ON at.Gender = cte3.EntryGUID
    WHERE RealmContext = at.ID
        AND RealmType = 7

    --Return the page information for the blog    

    SELECT b.UserPage, b.SitePage, 
            b.RealmType, b.RealmContext, 
            b.Author, b.PostDate, 
            b.PostTitle, b.PostDescription, 
            b.RealmContextName
    FROM @Blogs b
    WHERE b.NewsStory > -1
    ORDER BY b.PostDate DESC