cs_procedures.sql

解压即可使用

			F.SectionID IN (-1, 0) AND U.SettingsID = @SettingsID and F.SettingsID = @SettingsID and
			F.SubscriptionType = 2

	END
	ELSE BEGIN

		SELECT
			U.Email, 
			U.EnableHtmlEmail
		FROM 
			cs_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID			
		WHERE
			F.SectionID = @SectionID AND U.SettingsID = @SettingsID and F.SettingsID = @SettingsID and 
			F.SubscriptionType = 2
	END
END

GO
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
SET Transaction Isolation Level Read UNCOMMITTED
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
SET Transaction Isolation Level Read UNCOMMITTED
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

SET Transaction Isolation Level Read UNCOMMITTED

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

	UPDATE
		cs_Exceptions
	SET
		DateLastOccurred = GetDate(),
		Frequency = Frequency + 1
	WHERE
		ExceptionHash = @ExceptionHash and SettingsID = @SettingsID
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
SET Transaction Isolation Level Read UNCOMMITTED

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
SET Transaction Isolation Level Read UNCOMMITTED
	-- 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
SET Transaction Isolation Level Read UNCOMMITTED
	-- 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
SET Transaction Isolation Level Read UNCOMMITTED
	-- 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_GetUnmoderatedPostStatus
* File Date: 4/25/2005 11:12:27 AM
***********************************************/
Print 'Creating...cs_forums_GetUnmoderatedPostStatus'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].cs_forums_GetUnmoderatedPostStatus
(
  @SectionID int = null,
  @UserName varchar,
  @SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
IF(@SectionID = 0)
     SET @SectionID  = null

IF(@SectionID IS Null)
BEGIN
     SELECT OldestPostAgeInMinutes = DateDiff(mi, IsNull(MIN(postdate),GetDate()),GetDate()),TotalPostsInModerationQueue = Count(PostID)
     FROM cs_Posts P 
     WHERE P.IsApproved = 0 
     AND P.SettingsID = @SettingsID
END
ELSE
BEGIN
     SELECT OldestPostAgeInMinutes = DateDiff(mi, IsNull(MIN(postdate),GetDate()),GetDate()),TotalPostsInModerationQueue = Count(PostID)
     FROM cs_Posts P 
     WHERE P.IsApproved = 0 
     AND P.SettingsID = @SettingsID
     AND P.SectionID = @SectionID
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_forums_GetUnmoderatedPostStatus] 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
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

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

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