cs_emails_trackingsection.prc

来自「community server 源码」· PRC 代码 · 共 122 行

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Emails_TrackingSection]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_Emails_TrackingSection]
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.cs_UserID as UserID,
			U.Email,
			U.EnableHtmlEmail,
			F.SubscriptionType
		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 
			U.EnableThreadTracking = 1 and
			F.SubscriptionType & 3 <> 0
	END
	ELSE BEGIN

		SELECT
			U.cs_UserID as UserID,
			U.Email, 
			U.EnableHtmlEmail,
			F.SubscriptionType
		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
			U.EnableThreadTracking = 1 and
			F.SubscriptionType & 3 <> 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.cs_UserID as UserID,
			U.Email,
			U.EnableHtmlEmail,
			F.SubscriptionType
		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 & 3 = 3

	END
	ELSE BEGIN

		SELECT
			U.cs_UserID as UserID,
			U.Email, 
			U.EnableHtmlEmail,
			F.SubscriptionType
		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 & 3 = 3
	END
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_Emails_TrackingSection to public
go