cs_procedures.sql

本系统是在asp版《在线文件管理器》的基础上设计制作

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



create proc dbo.cs_EmailQueue_Delete
	@EmailID uniqueidentifier 
as
SET Transaction Isolation Level Read UNCOMMITTED
	delete from cs_EmailQueue where EmailID = @EmailID





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



create proc dbo.cs_EmailQueue_Failure
	@EmailID uniqueidentifier,
	@FailureInterval int,
	@MaxNumberOfTries int
as
SET Transaction Isolation Level Read UNCOMMITTED
declare @NumberOfTries int
select @NumberOfTries = NumberOfTries + 1 from cs_EmailQueue where EmailID = @EmailID

if @NumberOfTries <= @MaxNumberOfTries
begin
	update cs_EmailQueue set
		NumberOfTries = @NumberOfTries,
		NextTryTime = dateadd(minute, @NumberOfTries * @FailureInterval, getdate())
	where EmailID = @EmailID
end
else
begin
	delete from cs_EmailQueue where EmailID = @EmailID
end
	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE    PROCEDURE [dbo].cs_Emails_Dequeue
(
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	SELECT * FROM cs_EmailQueue Where SettingsID = @SettingsID and NextTryTime < getdate()
	
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  PROCEDURE [dbo].cs_Emails_Enqueue
(
	@EmailTo	nvarchar(2000),
	@EmailCc	ntext,
	@EmailBcc	ntext,
	@EmailFrom	nvarchar(256),
	@EmailSubject	nvarchar(1024),
	@EmailBody	ntext,
	@EmailPriority	int,
	@EmailBodyFormat int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	INSERT INTO
		cs_EmailQueue
		(
			emailTo,
			emailCc,
			emailBcc,
			EmailFrom,
			EmailSubject,
			EmailBody,
			emailPriority,
			emailBodyFormat,
			SettingsID
		)
	VALUES
		(
			@EmailTo,
			@EmailCc,
			@EmailBcc,
			@EmailFrom,
			@EmailSubject,
			@EmailBody,
			@EmailPriority,
			@EmailBodyFormat,
			@SettingsID
		)		
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].cs_Emails_TrackingSection
(
	@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 its a new thread or not
IF (@PostLevel = 1)
BEGIN
	-- this is a new thread (1 & 2)
	
	-- 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_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID
			JOIN cs_PrivateMessages PM ON PM.UserID = F.UserID AND PM.ThreadID = @ThreadID
		WHERE
			F.SectionID IN (-1, 0) AND F.SettingsID = @SettingsID and U.SettingsID = @SettingsID and 
			F.SubscriptionType <> 0

	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 F.SettingsID = @SettingsID and U.SettingsID = @SettingsID
			and F.SubscriptionType <> 0
	END
END
ELSE BEGIN
	-- this is a reply to an existing post (2)

	-- 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_TrackedSections F
			JOIN cs_vw_Users_FullUser U (nolock) ON U.cs_UserID = F.UserID
			JOIN cs_PrivateMessages PM ON PM.UserID = F.UserID AND PM.ThreadID = @ThreadID
		WHERE
			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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
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

SET QUOTED_IDENTIFIER OFF