cs_favorites_getposts.prc

来自「community server 源码」· PRC 代码 · 共 99 行

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Favorites_GetPosts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Favorites_GetPosts]
GO

CREATE procedure dbo.cs_Favorites_GetPosts
(
	@UserID		int,
	@SettingsID	int
)
AS
	SET Transaction Isolation Level Read UNCOMMITTED

SELECT
	FP.ApplicationType,
	U.*,
	jP.TotalRatings AS PostTotalRatings,
	jP.RatingSum AS PostRatingSum,
	jP.PostID, 
	jP.ThreadID, 
	jP.ParentID, 
	jP.PostAuthor, 
	jP.UserID, 
	jP.SectionID, 
	jP.PostLevel, 
	jP.SortOrder, 
	jP.Subject, 
	jP.PostDate, 
	jP.IsApproved,
	jP.IsLocked as IsLocked,
	jP.IsIndexed, 
	jP.TotalViews as PostTotalViews,  --Conflicts with the Threads table which has precidence
	jP.Body, 
	jP.FormattedBody, 
	jP.IPAddress, 
	jP.PostType, 
	jP.PostMedia, 
	jP.EmoticonID, 
	jP.SettingsID, 
	jP.AggViews,
	jP.PostPropertyNames, 
	jP.PostPropertyValues,
	jP.PostConfiguration,
	jP.Points AS PostPoints,
	jP.PostStatus,
	jP.SpamScore,
	
	jp.AttachmentFilename,jp.ContentType, jp.IsRemote, jp.FriendlyFileName, jp.ContentSize, jp.[FileName],jp.Created, jP.Height, jP.Width,
	jP.PostName, 
	jP.ApplicationPostType, 
	jP.UserTime, 
	HasRead = 1,
	EditNotes = null, --(SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),

	jT.PostAuthor as UserName,
	jT.TotalReplies as Replies, -- (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = jP.PostID AND P2.PostLevel != 1)

        jT.UserID as ThreadUserID,
        jT.PostAuthor as ThreadPostAuthor,
        jT.PostDate as ThreadPostDate,
        jT.ThreadDate,
        jT.LastViewedDate,
        jT.StickyDate,
        jT.TotalViews as TotalViews,
        jT.TotalReplies,
        jT.MostRecentPostAuthorID,
        jT.MostRecentPostAuthor,
        jT.MostRecentPostID,
        jT.IsLocked as ThreadIsLocked,
        jT.IsSticky,
        jT.IsApproved as ThreadIsApproved,
        jT.RatingSum,
        jT.TotalRatings,
        jT.ThreadEmoticonID,
        jT.ThreadStatus,
        jT.SettingsID
FROM
	cs_FavoritePosts FP
	JOIN cs_vw_PostsWithAttachmentDetails jP ON FP.PostID = jP.PostID
	JOIN cs_vw_Users_FullUser U ON jP.userID = U.cs_UserID
	JOIN cs_Threads jT ON jP.ThreadID = jT.ThreadID
WHERE
	FP.OwnerID = @UserID
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID AND U.SettingsID = @SettingsID
ORDER BY
	jP.Subject

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXECUTE on [dbo].[cs_Favorites_GetPosts] to public