2005_4_2.sql

解压即可使用

(
	@SettingsID int
)
 AS
	SELECT TOP 1 
		TotalPosts 
	FROM 
		forums_Statistics where SettingsID = @SettingsID






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE         PROCEDURE [dbo].cs_GetUserIDByAppToken
(
	@AppUserToken varchar(128),
	@SettingsID int
)
AS

SELECT
	U.UserID
FROM 
	cs_vw_Users_FullUser U (nolock)
WHERE 
	U.cs_AppUserToken = @AppUserToken and SettingsID = @SettingsID






























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE  PROCEDURE [dbo].cs_Groups_Get
(
	@SettingsID int,
	@ApplicationType smallint = 0,
	@RequireModeration bit = 0

)
AS
BEGIN

	IF (@RequireModeration = 0)
		SELECT
			*
		FROM
			cs_Groups
		WHERE
			(SettingsID = @SettingsID OR SettingsID = 0) and ApplicationType = @ApplicationType
	ELSE
		SELECT DISTINCT
			FG.*
		FROM
			cs_Sections S,
			cs_Groups FG
		WHERE
			S.GroupID = FG.GroupID AND
			S.IsActive = 1 AND
			(SELECT Count(PostID) FROM cs_Posts P WHERE SectionID = S.SectionID AND P.IsApproved = 0) > 0 AND
			(FG.SettingsID = @SettingsID OR FG.SettingsID = 0) AND
			FG.ApplicationType = @ApplicationType

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE       PROCEDURE [dbo].cs_Group_CreateUpdateDelete
(
	@GroupID	int out,
	@Name		nvarchar(256),
	@ApplicationType smallint = 0,
	@Action 	int,
	@SettingsID 	int
)
AS

-- CREATE
IF @Action = 0
BEGIN
	DECLARE @SortOrder int

	SELECT @SortOrder = coalesce(MAX(SortOrder) + 1, 1) FROM cs_Groups where SettingsID = @SettingsID

	-- Create a new forum group
	INSERT INTO 
		cs_Groups 
		(
			Name,
			SortOrder,
			ApplicationType,
			SettingsID
		)
	VALUES 
		(
			@Name,
			@SortOrder,
			@ApplicationType,
			@SettingsID
		)
	
	SET @GroupID = @@IDENTITY
END


-- UPDATE
ELSE IF @Action = 1
BEGIN

	IF EXISTS(SELECT GroupID FROM cs_Groups WHERE GroupID = @GroupID)
	BEGIN
		UPDATE
			cs_Groups
		SET
			Name = @Name
		WHERE
			GroupID = @GroupID and SettingsID = @SettingsID
	END

END

-- DELETE
ELSE IF @Action = 2
BEGIN
	DELETE cs_Groups WHERE GroupID = @GroupID and SettingsID = @SettingsID
END


























GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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






CREATE         PROCEDURE [dbo].cs_guestbook_GetPosts
(
	@PageIndex int,
	@PageSize int,
	@SortOrder bit,
	@SectionID int,
	@SettingsID int
)
AS
BEGIN

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @ThreadID int

-- First set the rowcount
DECLARE @RowsToReturn int
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,
	PostID int
)

-- Sort by Post Date
IF  @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID

ELSE 
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostID DESC


-- Select the individual posts
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,
	T.*, U.*, #PageIndex.*,
	T.IsLocked,
	T.IsSticky,
	Username = P.PostAuthor,
	EditNotes = (SELECT EditNotes FROM cs_PostEditNotes WHERE PostID = P.PostID),
	AttachmentFilename = '',
	Replies = 0,
	IsModerator = 0,
	HasRead = 0 -- not used
FROM 
	cs_Posts P (nolock),
	cs_Threads T,
	cs_vw_Users_FullUser U,
	#PageIndex
WHERE 
	P.PostID = #PageIndex.PostID AND
	P.UserID = U.cs_UserID AND
	T.ThreadID = P.ThreadID AND
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound and U.SettingsID = @SettingsID
ORDER BY
	IndexID
END


  SELECT Count(PostID) FROM cs_Posts (nolock) WHERE IsApproved = 1 AND SectionID = @SectionID and SettingsID = @SettingsID

DROP Table #PageIndex




















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE   PROCEDURE [dbo].cs_guestbook_Post_Create
(
	@SectionID int,
	@Subject nvarchar(256),
	@UserID int,
	@PostAuthor nvarchar(64) = null,
	@Body ntext,
	@FormattedBody ntext,
	@EmoticonID int = 0,
	@PostType int = 0,
	@PostDate datetime = null,
	@UserHostAddress nvarchar(32),
	@PropertyNames ntext = null,
	@PropertyValues ntext = null,
	@SettingsID int,
	@PostID int out
) 
AS
BEGIN
SET NOCOUNT ON
DECLARE @ThreadID int

-- set the PostDate
IF @PostDate IS NULL
	SET @PostDate = GetDate()

-- set the username
IF @PostAuthor IS NULL
	SELECT 
		@PostAuthor = UserName
	FROM 
		cs_vw_Users_FullUser 
	WHERE 
		cs_UserID = @UserID

-- Do we care about duplicates?

Select @ThreadID = ThreadID FROM cs_Threads Where SectionID = @SectionID


SET NOCOUNT ON
BEGIN TRAN


IF @ThreadID is null
BEGIN


	INSERT cs_Threads 	
	( 
		SectionID,
		PostDate, 
		UserID, 
		PostAuthor, 
		ThreadDate, 
		MostRecentPostAuthor, 
		MostRecentPostAuthorID, 	
		MostRecentPostID, 
		IsLocked, 
		IsApproved,
		IsSticky, 
		StickyDate, 
		ThreadEmoticonID,
		SettingsID 
	)
	VALUES
	( 
		@SectionID, 
		@PostDate, 
		@UserID, 
		@PostAuthor,
		@PostDate,
		@PostAuthor,
		@UserID, 
		0,	-- MostRecentPostID, which we don't know until after post INSERT below.
		0,
		1,
		0,
		@PostDate,
		-1,
		@SettingsID 
	)

	-- Get the new ThreadID
	SELECT 
		@ThreadID = @@IDENTITY
	FROM
		cs_Threads
		
	-- Now we add the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 

		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		PostDate, 
		IPAddress, 
		EmoticonID,
		PropertyNames,
		PropertyValues,
		SettingsID
	        )
	VALUES 
		( @SectionID, 
		@ThreadID, 
		0, 	-- ParentID, which we don't know until after INSERT
		1, 	-- PostLevel, 1 marks start/top/first post in thread.
		1, 	-- SortOrder (not in use at this time)
		@Subject, 
		@UserID, 
		@PostAuthor,
		1, 
		0, 
		@Body, 
		@FormattedBody, 
		@PostType, 
		@PostDate, 
		@UserHostAddress, 
		-1,
		@PropertyNames,
		@PropertyValues,
		@SettingsID )
		

	-- Get the new PostID
	SELECT 
		@PostID = @@IDENTITY

	-- Update the new Thread with the new PostID
	UPDATE 
		cs_Threads
	SET 
		MostRecentPostID = @PostID
	WHERE 
		ThreadID = @ThreadID

END
ELSE BEGIN	-- @ParentID <> 0 means there is a reply to an existing post

	-- Insert the new post
	INSERT cs_Posts 
		( SectionID, 
		ThreadID, 
		ParentID, 
		PostLevel, 
		SortOrder, 
		Subject, 
		UserID, 
		PostAuthor, 
		IsApproved, 
		IsLocked, 
		Body, 
		FormattedBody, 
		PostType, 
		P