cs_procedures.sql

解压即可使用

	P.PostID,
	P.Subject,
	P.Body,P.FormattedBody,
	UserName = T.PostAuthor,
	HasRead =  CASE
		WHEN @UserID = 0 THEN 0
		WHEN @UserID > 0 THEN (dbo.HasReadPost(@UserID, P.PostID, P.SectionID))
		END
FROM 
	cs_Posts P,
	cs_Threads T,
	cs_vw_Users_FullUser U
WHERE 
	P.PostID = P.ParentID AND
	P.ThreadID = @ThreadID AND
	T.ThreadID = P.ThreadID AND
	P.UserID = U.cs_UserID and P.SettingsID = @SettingsID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_forums_Thread_Get to public
go
/***********************************************
* SP: cs_forums_Thread_Rate_Get
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_forums_Thread_Rate_Get'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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





create procedure [dbo].cs_Thread_Rate_Get
(
	@ThreadID int,
	@SettingsID int
)
AS
BEGIN
	SELECT
		*
	FROM
		cs_vw_Users_FullUser U,
		cs_PostRating R
	WHERE
		R.UserID = U.cs_UserID AND
		R.ThreadID = @ThreadID AND U.SettingsID = @SettingsID and R.SettingsID = @SettingsID

END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on dbo.cs_Thread_Rate_Get to public
go
/***********************************************
* SP: cs_gallery_GetPortalPictures
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_gallery_GetPortalPictures'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE    procedure [dbo].cs_gallery_GetPortalPictures
(
	@SectionID int = -1,
	@PageSize int,
	@SortBy int,
	@SettingsID int
)
AS

DECLARE @TotalThreads int

-- First set the rowcount
SET ROWCOUNT @PageSize

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	ThreadID int
)


if @SortBy = 1
begin
	-- order by rating
	INSERT INTO #PageIndex (ThreadID)
	SELECT
		jT.ThreadID
	FROM cs_Threads jT
	JOIN cs_Sections jS ON jS.SectionID = jT.SectionID
	JOIN cs_SiteSettings jSS ON jSS.SettingsID = jT.SettingsID
	JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = jSS.ApplicationName
	JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = jS.ApplicationType AND jP.SettingsID = @SettingsID)
	LEFT OUTER JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
	WHERE
		jS.IsActive = 1
		AND jS.ApplicationType = 2
		AND (jT.SectionID = @SectionID OR -1 = @SectionID)
		AND jT.SettingsID = @SettingsID
		AND isnull(jP2.allowmask,jP.allowmask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001)
	ORDER BY
		case when jT.TotalRatings > 0 then (convert(decimal,jT.RatingSum) / convert(decimal,jT.TotalRatings)) else 0 end DESC
end
else if @SortBy = 2
begin
	-- order by total views
	INSERT INTO #PageIndex (ThreadID)
	SELECT
		jT.ThreadID
	FROM cs_Threads jT
	JOIN cs_Sections jS ON jS.SectionID = jT.SectionID
	JOIN cs_SiteSettings jSS ON jSS.SettingsID = jS.SettingsID
	JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = jSS.ApplicationName
	JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = jS.ApplicationType AND jP.SettingsID = @SettingsID)
	LEFT OUTER JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
	WHERE
		jS.IsActive = 1
		AND jS.ApplicationType = 2
		AND (jT.SectionID = @SectionID OR -1 = @SectionID)
		AND jT.SettingsID = @SettingsID
		AND isnull(jP2.allowmask,jP.allowmask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001)
	ORDER BY
		jT.TotalViews DESC
end
else if @SortBy = 4
begin
	-- order by total replies
	INSERT INTO #PageIndex (ThreadID)
	SELECT
		jT.ThreadID
	FROM cs_Threads jT
	JOIN cs_Sections jS ON jS.SectionID = jT.SectionID
	JOIN cs_SiteSettings jSS ON jSS.SettingsID = jS.SettingsID
	JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = jSS.ApplicationName
	JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = jS.ApplicationType AND jP.SettingsID = @SettingsID)
	LEFT OUTER JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
	WHERE
		jS.IsActive = 1
		AND jS.ApplicationType = 2
		AND (jT.SectionID = @SectionID OR -1 = @SectionID)
		AND jT.SettingsID = @SettingsID
		AND isnull(jP2.allowmask,jP.allowmask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001)
	ORDER BY
		jT.TotalReplies DESC
end
else
begin
	-- order by postdate
	INSERT INTO #PageIndex (ThreadID)
	SELECT
		jT.ThreadID
	FROM cs_Threads jT
	JOIN cs_Sections jS ON jS.SectionID = jT.SectionID
	JOIN cs_SiteSettings jSS ON jSS.SettingsID = jS.SettingsID
	JOIN cs_vw_EveryOne_Role jE ON jE.LoweredApplicationName = jSS.ApplicationName
	JOIN cs_ProductPermissions jP ON (jP.RoleID = jE.RoleId and jP.ApplicationType = jS.ApplicationType AND jP.SettingsID = @SettingsID)
	LEFT OUTER JOIN cs_SectionPermissions jP2 ON (jP2.RoleID = jE.RoleId and jP2.SectionID = jT.SectionID)
	WHERE
		jS.IsActive = 1
		AND jS.ApplicationType = 2
		AND (jT.SectionID = @SectionID OR -1 = @SectionID)
		AND jT.SettingsID = @SettingsID
		AND isnull(jP2.allowmask,jP.allowmask) & convert(bigint,0x0000000000000001) = convert(bigint,0x0000000000000001)
	ORDER BY
		jT.PostDate DESC
end


SELECT 
	jT.*,
	SortOrder = 0,
	jP.PostID,
	jP.Subject,
	jP.Body,
	jP.FormattedBody,
	UserName = jT.PostAuthor,
	jp.PropertyNames as PostPropertyNames, jp.PropertyValues as PostPropertyValues
FROM 
	#PageIndex jPI
	JOIN cs_Threads jT ON jPI.ThreadID = jT.ThreadID
	JOIN cs_Posts jP ON jPI.ThreadID = jP.ThreadID
WHERE 
	jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID
ORDER BY
	jPI.IndexID	-- this is the ordering system we're using populated from the @sqlPopulate

DROP TABLE #PageIndex
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_GetPortalPictures to public
go
/***********************************************
* SP: cs_gallery_Post_GetSortOrder
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_gallery_Post_GetSortOrder'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].cs_gallery_Post_GetSortOrder
	@PostID int,
	@sqlPopulate nvarchar(4000),
	@SettingsID int
AS

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	ThreadID int
)

CREATE INDEX page_index ON #PageIndex(IndexID)

INSERT INTO #PageIndex (ThreadID)
EXEC (@sqlPopulate)

SELECT IndexID
	FROM #PageIndex I
	LEFT JOIN cs_Posts P ON P.ThreadID = I.ThreadID
	WHERE P.PostID = @PostID
		AND P.SettingsID = @SettingsID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Post_GetSortOrder to public
go
/***********************************************
* SP: cs_gallery_Search
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_gallery_Search'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

CREATE    procedure [dbo].cs_gallery_Search (
	@SearchSQL nvarchar(4000),
	@PageIndex int = 0,
	@PageSize int = 25,
	@SettingsID int
)
AS
BEGIN

	DECLARE @StartTime datetime
	DECLARE @RowsToReturn int
	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	DECLARE @Count int

	-- Used to calculate cost of query
	SET @StartTime = GetDate()

	-- Set the rowcount
	SET @RowsToReturn = @PageSize * (@PageIndex + 1)
	--SET ROWCOUNT @RowsToReturn

	-- Calculate the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1

	-- Create a temp table to store the results in
	CREATE TABLE #SearchResults
	(
		IndexID int IDENTITY (1, 1) NOT NULL,
		PostID int,
		SectionID int,
		Weight int,
		PostDate datetime
	)

	-- Fill the temp table
	INSERT INTO #SearchResults (PostID, SectionID, Weight, PostDate)
	exec (@SearchSQL)

	-- SELECT actual search results from this table
	SELECT
		P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
		P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews,
		P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
		U.*,
		T.ThreadDate,
		T.IsLocked,
		T.IsSticky, T.StickyDate, T.RatingSum, T.TotalRatings, 
		AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
		Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
		IsModerator = (SELECT count(*) from cs_Moderators where UserID = P.UserID),
		HasRead = 0, -- not used,
		Convert(bit,0) as IsTracked -- Convert(bit, (Select Count(*) FROM cs_TrackedThreads where UserID = @UserID and ThreadID = P.ThreadID))
	FROM 
		cs_Posts P,
		cs_vw_Users_FullUser U,
		cs_Threads T,
		#SearchResults R
	WHERE
		P.PostID = R.PostID AND
		T.ThreadID = P.ThreadID AND
		U.cs_UserID = P.UserID AND
		R.IndexID > @PageLowerBound AND
		R.IndexID < @PageUpperBound AND
		P.SettingsID = @SettingsID and
		U.SettingsID = @SettingsID

	Select Count(*) FROM #SearchResults

	DROP Table #SearchResults

	SELECT Duration = GetDate() - @StartTime
END

















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Search to public
go
/***********************************************
* SP: cs_gallery_Search_PostReindex
* File Date: 2/23/2005 12:22:15 AM
***********************************************/
Print 'Creating...cs_gallery_Search_PostReindex'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE  PROCEDURE [dbo].cs_gallery_Search_PostReindex 
(
	@RowCount int,
	@SettingsID int
)
AS

SET ROWCOUNT @RowCount

SELECT
	T.*,
	SortOrder = 1,
	P.PostID,
	P.Subject,
	P.Body,
	P.FormattedBody,
	P.PostLevel,
	P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
	Username = P.PostAuthor,
	AttachmentFilename = ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
	Replies = (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1)
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_Sections F
WHERE
	F.SectionID = P.SectionID AND
	F.IsActive = 1 AND
	F.IsSearchable = 1 AND 
	P.IsApproved = 1 AND
	T.ThreadID = P.ThreadID and 
	P.IsIndexed = 0 and
	F.SettingsID = @SettingsID and
	F.ApplicationType = 2 AND
	P.PostLevel = 1
ORDER BY
	T.ThreadDate DESC
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Search_PostReindex  to public
go
/***********************************************
* SP: cs_gallery_Threads_GetAllThreads
* File Date: 2/23/2005 12:22:13 AM
***********************************************/
Print 'Creating...cs_gallery_Threads_GetAllThreads'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_gallery_Threads_GetAllThreads
(
	@SectionID int,
	@SettingsID int
)
AS

SELECT 
	jT.*,
	SortOrder = 1,
	jP.PostID,
	jP.Subject,
	jP.Body,
	jP.FormattedBody,
	UserName = jT.PostAuthor,
	jp.PropertyNames as PostPropertyNames, jp.PropertyValues as PostPropertyValues
FROM 
	cs_Threads jT
	JOIN cs_Posts jP ON jP.ThreadID = jT.ThreadID
WHERE 
	jP.PostLevel = 1 	-- PostLevel=1 should mean it's a top-level thread starter
	AND jT.SectionID = @SectionID AND jP.SectionID = @SectionID
	AND jp.SettingsID = @SettingsID AND jT.SettingsID = @SettingsID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_gallery_Threads_GetAllThreads to public
go
/***********************************************
* SP: cs_gallery_Threads_GetThreadSet
* File Date: 2/23/2005 12:22:14 AM
***********************************************/
Print 'Creating...cs_gallery_Threads_GetThreadSet'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE    procedure [dbo].cs_gallery_Threads_GetThreadSet
(
	@SectionID int,
	@PageIndex int, 
	@PageSize int,
	@sqlCount nvarchar(4000),
	@sqlPopulate nvarchar(4000),
	@UserID int,
	@SettingsID int,
	@ReturnRecordCount bit
)
AS
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalThreads int

-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	ThreadID int
)

CREATE INDEX page_index ON #PageIndex(IndexID)