cs_procedures.sql

解压即可使用

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].cs_Emails_TrackingThread
(
	@PostID INT,
	@SettingsID int
)
AS

DECLARE @SectionID INT
DECLARE @UserID INT
DECLARE @PostLevel INT
DECLARE @ThreadID INT

-- First get the post info
SELECT 
	@SectionID = SectionID, 
	@UserID = UserID,
	@PostLevel = PostLevel,
	@ThreadID = ThreadID
FROM 
	cs_Posts (nolock) 
WHERE 
	PostID = @PostID and SettingsID = @SettingsID


-- Check if this is a PM message
IF (@SectionID = 0)
BEGIN
	
	-- we have to bind to the PM users for this ThreadID
	SELECT
		U.Email,
		U.EnableHtmlEmail
	FROM
		cs_TrackedThreads T
		JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = T.UserID
				JOIN cs_PrivateMessages PM ON PM.UserID = T.UserID AND PM.ThreadID = @ThreadID
	WHERE
		T.ThreadID = @ThreadID and T.SettingsID = @SettingsID and U.SettingsID = @SettingsID and PM.SettingsID = @SettingsID

END
ELSE BEGIN

	SELECT
		U.Email, 
		U.EnableHtmlEmail
	FROM 
		cs_TrackedThreads T
		JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = T.UserID			
	WHERE
		T.ThreadID = @ThreadID and T.SettingsID = @SettingsID and U.SettingsID = @SettingsID 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE  procedure [dbo].cs_Exceptions_Get
(
	@SettingsID int,
	@ExceptionType int = 0,
	@MinFrequency int = 10
)
AS
BEGIN

	SELECT TOP 100
		E.*
	FROM
		cs_Exceptions E
	WHERE
		E.SettingsID = @SettingsID AND
		((@ExceptionType > 0 and E.Category = @ExceptionType ) or @ExceptionType <= 0 ) AND
		E.Frequency >= @MinFrequency
	ORDER BY
		E.Frequency DESC
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE procedure [dbo].cs_Exceptions_Log
(
	@SettingsID int,
	@ExceptionHash varchar(128),
	@Category int,
	@Exception nvarchar(2000),
	@ExceptionMessage nvarchar(500),
	@UserAgent nvarchar(64),
	@IPAddress varchar(15),
	@HttpReferrer nvarchar (256),
	@HttpVerb nvarchar(24),
	@PathAndQuery nvarchar(512)
)
AS
BEGIN

IF EXISTS (SELECT ExceptionID FROM cs_Exceptions WHERE ExceptionHash = @ExceptionHash)

	UPDATE
		cs_Exceptions
	SET
		DateLastOccurred = GetDate(),
		Frequency = Frequency + 1
	WHERE
		ExceptionHash = @ExceptionHash
ELSE
	INSERT INTO 
		cs_Exceptions
	(
		ExceptionHash,
		SettingsID,
		Category,
		Exception,
		ExceptionMessage,
		UserAgent,
		IPAddress,
		HttpReferrer,
		HttpVerb,
		PathAndQuery,
		DateCreated,
		DateLastOccurred,
		Frequency
	)
	VALUES
	(
		@ExceptionHash,
		@SettingsID,
		@Category,
		@Exception,
		@ExceptionMessage,
		@UserAgent,
		@IPAddress,
		@HttpReferrer,
		@HttpVerb,
		@PathAndQuery,
		GetDate(),
		GetDate(),
		1
	)

END











GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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


CREATE procedure [dbo].cs_forums_GetForumMessages
(
	@MessageID int = 0,
	@SettingsID int
)
AS

IF @MessageID = 0
	SELECT 
		*
	FROM
		cs_Messages where SettingsID = @SettingsID
ELSE
	SELECT 
		*
	FROM
		cs_Messages
	WHERE
		MessageID = @MessageID and SettingsID = @SettingsID






















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE  PROCEDURE [dbo].cs_forums_GetForumModerators
(
	@SectionID	int,
	@SettingsID int
)
 AS
	-- get a list of forum moderators
	SELECT 
		UserName, EmailNotification, DateCreated
	FROM 
		Moderators (nolock)
	WHERE 
		(SectionID = @SectionID OR SectionID = 0) and SettingsID = @SettingsID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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






create procedure [dbo].cs_forums_GetForumsModeratedByUser
(
	@UserName	nvarchar(50),
	@SettingsID int
)
 AS
	-- determine if this user can moderate ALL forums
	IF EXISTS(SELECT NULL FROM Moderators (nolock) WHERE SectionID = 0 AND Username = @UserName and SettingsID = @SettingsID)
		SELECT SectionID, ForumName = 'All Forums', EmailNotification, DateCreated FROM Moderators (nolock)
		WHERE SectionID = 0 AND Username = @UserName and SettingsID = @SettingsID
	ELSE
		-- get all of the forums moderated by this particular user
		SELECT
			M.SectionID,
			EmailNotification,
			ForumName = F.Name,
			M.DateCreated
		FROM Moderators M (nolock)
			INNER JOIN Forums F (nolock) ON
				F.SectionID = M.SectionID
		WHERE Username = @UserName and M.SettingsID = @SettingsID  and F.SettingsID = @SettingsID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE  PROCEDURE [dbo].cs_forums_GetForumsNotModeratedByUser
(
	@UserName	nvarchar(50),
	@SettingsID int
)
 AS
	-- determine if this user can moderate ALL forums
	IF NOT EXISTS(SELECT NULL FROM Moderators (nolock) WHERE SectionID = 0 AND Username = @UserName and SettingsID = @SettingsID)
		-- get all of the forums NOT moderated by this particular user
		SELECT SectionID =  0, ForumName =  'All Forums'
		UNION
		SELECT
			SectionID,
			ForumName = F.Name
		FROM Forums F (nolock) 
		WHERE SettingsID = @SettingsID and  SectionID NOT IN (SELECT SectionID FROM Moderators (nolock) WHERE Username = @UserName and SettingsID = @SettingsID)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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






CREATE       PROCEDURE [dbo].cs_forums_Moderate_PostSet
(
	@SectionID		int,
	@PageIndex 		int,
	@PageSize 		int,
	@SortBy 		int,
	@SortOrder 		bit,
	@UserID 		int,
	@ReturnRecordCount 	bit,
	@SettingsID		int
)
AS
BEGIN

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

-- 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 @SortBy = 0 AND @SortOrder = 0
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate

ELSE IF @SortBy = 0 AND @SortOrder = 1
    INSERT INTO #PageIndex (PostID)
    SELECT PostID FROM cs_Posts P (nolock) WHERE IsApproved = 0 AND SectionID = @SectionID and SettingsID = @SettingsID ORDER BY PostDate 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 = 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(UserID) from cs_Moderators where UserID = @UserID),
	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


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE        PROCEDURE [dbo].cs_forums_Post
/*

	Procedure for getting basic information on a single post.

*/
(
	@PostID int,
	@UserID int,
	@TrackViews bit,
	@SettingsID int
) AS
DECLARE @NextThreadID int
DECLARE @PrevThreadID int
DECLARE @ThreadID int