if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_ModerateForumGetByModeratorThreads') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Forum_ModerateForumGetByModeratorThreads
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_ModerateForumGetByModerator') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Forum_ModerateForumGetByModerator
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_ForumUserViewsUpdate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Forum_ForumUserViewsUpdate
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_AA_UserThreadViewUpdate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Forum_AA_UserThreadViewUpdate
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_AA_StatisticsSiteUpdate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Forum_AA_StatisticsSiteUpdate
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_AA_ForumPostCountUpdate') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Forum_AA_ForumPostCountUpdate
	GO
	if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}Forum_StatisticsGet') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}Forum_StatisticsGet
	GO


/***** REMOVE BLOG ITEMS, Leave a few items for possible future migration (Change foldername to avoid loosing forums data if user deletes Blog definition) *****/

DELETE FROM {databaseOwner}{objectQualifier}ModuleCONTROLS 
WHERE
ControlSrc = 'DesktopModules/Forum/Blog_Container.ascx'
GO

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls 
WHERE
ControlSrc = 'DesktopModules/Forum/Blog_BlogEdit.ascx'
GO

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls 
WHERE
ControlSrc = 'DesktopModules/Forum/Blog_Search.ascx'
GO

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls 
WHERE
ControlSrc = 'DesktopModules/Forum/Blog_CategoryManage.ascx'
GO

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls 
WHERE
ControlSrc = 'DesktopModules/Forum/Blog_Settings.ascx'
GO

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls 
WHERE
ControlSrc = 'DesktopModules/Forum/Blog_AddComment.ascx'
GO

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls  
WHERE
ControlSrc = 'DesktopModules/Forum/Forum_BadWords.ascx'
GO

DELETE FROM {databaseOwner}{objectQualifier}ModuleControls 
WHERE
ControlSrc = 'DesktopModules/Forum/Forum_BadWordEdit.ascx'
GO

UPDATE {databaseOwner}{objectQualifier}DesktopModules
SET
FolderName = 'Legacy Core Blog',
Description = 'Legacy Core Blog, it is safe to delete this definition ONLY if you have no intentions of migrating data in future.  This was only left for future migration possibility'
WHERE
ModuleName = 'DNN_Blog'
AND 
FOlderName = 'Forum'
GO

/***** END REGION *****/

CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ModerateForumGetByModeratorThreads
(
@UserID int
)
 AS

-- if Moderators.ForumID = 0 for this user, then they can moderate ALL forums
IF EXISTS(SELECT NULL FROM {databaseOwner}{objectQualifier}Forum_Moderators (nolock) WHERE [UserID] = @UserID AND [ForumID] = 0)
BEGIN
-- return all forums
	SELECT
		G.[GroupID],
		G.[ModuleID],
		F.[ForumID],
		F.[GroupID],			
		F.[Name],
		F.[Description],
		F.[CreatedDate],
		F.[IsModerated],
		F.[PostsToModerate],			
		F.[IsActive],
		F.[SortOrder]
	FROM 
		{databaseOwner}{objectQualifier}Forum_Forums F (nolock), {databaseOwner}{objectQualifier}Forum_Groups G (nolock)
	WHERE 	
		F.[GroupID] = G.[GroupID] AND
		F.[IsActive] = 1 AND
		F.[IsModerated] = 1
	ORDER BY 
		G.[SortOrder], F.[SortOrder]
END
ELSE
BEGIN
	-- return only forums this user can moderate
	SELECT
		G.[GroupID],
		G.[ModuleID],
		F.[ForumID],
		F.[GroupID],			
		F.[Name],
		F.[Description],
		F.[CreatedDate],
		F.[IsModerated],
		F.[PostsToModerate],			
		F.[IsActive],
		F.[SortOrder]

	FROM 
		{databaseOwner}{objectQualifier}Forum_Forums F (nolock) , {databaseOwner}{objectQualifier}Forum_Groups G (nolock) 
	WHERE 
		F.[GroupID] = G.[GroupID] AND
		F.[IsActive] = 1 AND 
		F.[PostsToModerate] > 0 AND
		F.[IsModerated] = 1 AND
		F.[ForumID] IN (SELECT [ForumID] FROM {databaseOwner}{objectQualifier}Forum_Moderators (nolock) WHERE [UserID] = @UserID)

	ORDER BY 
		G.[SortOrder], F.[SortOrder]
END
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ModerateForumGetByModerator
(
@UserID int
)
 AS

-- if Moderators.ForumID = 0 for this user, then they can moderate ALL forums
IF EXISTS(SELECT NULL FROM {databaseOwner}{objectQualifier}Forum_Moderators (nolock) WHERE [UserID] = @UserID AND [ForumID] = 0)
BEGIN
-- return all forums
	SELECT
		G.[GroupID],
		G.[ModuleID],
		F.[ForumID],
		F.[GroupID],			
		F.[Name],
		F.[Description],
		F.[CreatedDate],
		F.[IsModerated],
		F.[PostsToModerate],			
		F.[IsActive],
		F.[SortOrder]
	FROM 
		{databaseOwner}{objectQualifier}Forum_Forums F (nolock), {databaseOwner}{objectQualifier}Forum_Groups G (nolock)
	WHERE 	
		F.[GroupID] = G.[GroupID] AND
		F.[IsActive] = 1 AND
		F.[IsModerated] = 1
	ORDER BY 
		G.[SortOrder], F.[SortOrder]
END
ELSE
BEGIN
	-- return only forums this user can moderate
	SELECT
		G.[GroupID],
		G.[ModuleID],
		F.[ForumID],
		F.[GroupID],			
		F.[Name],
		F.[Description],
		F.[CreatedDate],
		F.[IsModerated],
		F.[PostsToModerate],			
		F.[IsActive],
		F.[SortOrder]

	FROM 
		{databaseOwner}{objectQualifier}Forum_Forums F (nolock) , {databaseOwner}{objectQualifier}Forum_Groups G (nolock) 
	WHERE 
		F.[GroupID] = G.[GroupID] AND
		F.[IsActive] = 1 AND 
		F.[IsModerated] = 1 AND
		F.[ForumID] IN (SELECT [ForumID] FROM {databaseOwner}{objectQualifier}Forum_Moderators (nolock) WHERE [UserID] = @UserID)

	ORDER BY 
		G.[SortOrder], F.[SortOrder]
END
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ForumUserViewsUpdate
(
@UserID int,
@ForumID int
)
AS
UPDATE {databaseOwner}{objectQualifier}Forum_Users
SET   
	[LastForumsView] = getdate()
WHERE  
	[UserID] = @UserID   

-- If read/unread tracking record for this user has not been created yet, create      
IF NOT EXISTS(SELECT UserID FROM {databaseOwner}{objectQualifier}Forum_ThreadsRead (nolock) WHERE ForumID = @ForumID AND UserID = @UserID)
BEGIN
print 'insert'
	INSERT {databaseOwner}{objectQualifier}Forum_ThreadsRead
		(UserID, ForumID, ReadThreads, LastPurseDate)
	VALUES
		(@UserID, @ForumID, '', '01/01/2000')
END
GO

-- Update thread view info for
-- Forum User
-- Forum Thread
-- Thread-User read/unread tracking
CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_AA_UserThreadViewUpdate
(
@UserID int,
@ThreadID int,
@ForumID int,
@Read	bit
)
AS
DECLARE @ReadThread nvarchar (50)
SET @ReadThread = CONVERT(varchar, @ThreadID) + ';'

IF @Read = 1 -- Add thread read info 
BEGIN
	-- Begin update threads table first
	UPDATE	{databaseOwner}{objectQualifier}Forum_Users
	SET   
		LastThreadView = getdate()
	WHERE
		UserID = @UserID

	-- Insert thread read record into read/unread tracking table
	IF NOT EXISTS(SELECT ForumID FROM {databaseOwner}{objectQualifier}Forum_ThreadsRead  (nolock) WHERE ForumID = @ForumID AND UserID = @UserID)
	BEGIN
		INSERT {databaseOwner}{objectQualifier}Forum_ThreadsRead 
			(ForumID, UserID, ReadThreads, LastPurseDate)
		VALUES
			(@ForumID, @UserID, @ReadThread, '01/01/2000')
		-- Add thread view count here (new from 3.1 version, since we don't call ThreadViewsIncrement from BCC
		EXEC {databaseOwner}{objectQualifier}Forum_ThreadViewsIncrement @ThreadID
	END
	ELSE
	BEGIN
		IF NOT EXISTS(SELECT ForumID FROM {databaseOwner}{objectQualifier}Forum_ThreadsRead  (nolock) WHERE ForumID = @ForumID AND UserID = @UserID AND ReadThreads LIKE '%' +  @ReadThread + '%')
		BEGIN
			UPDATE {databaseOwner}{objectQualifier}Forum_ThreadsRead
			SET
				ReadThreads = (SELECT ReadThreads FROM {databaseOwner}{objectQualifier}Forum_ThreadsRead  (nolock) WHERE ForumID = @ForumID AND UserID = @UserID) + @ReadThread
			WHERE			
 				ForumID = @ForumID AND
				UserID = @UserID
			-- Add thread view count here (new from 3.1 version, since we don't call ThreadViewsIncrement from BCC
			EXEC {databaseOwner}{objectQualifier}Forum_ThreadViewsIncrement @ThreadID
		END
	END
END
ELSE -- mark thread as unread by remove readthread string
BEGIN	
	DECLARE @ForumUserID int
	DECLARE @ReadThreadString nvarchar(4000)

	DECLARE threadread_cursor CURSOR FOR
	SELECT UserID, ReadThreads FROM {databaseOwner}{objectQualifier}Forum_ThreadsRead (nolock)
	WHERE ForumID = @ForumID -- AND UserID <> @UserID -- This user is the author of the post which cause unread for other user

	OPEN threadread_cursor

	FETCH NEXT FROM threadread_cursor INTO @ForumUserID, @ReadThreadString
	WHILE @@FETCH_STATUS = 0 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
	BEGIN
		UPDATE {databaseOwner}{objectQualifier}Forum_ThreadsRead
		SET			
			ReadThreads = REPLACE(@ReadThreadString, @ReadThread, '')
		WHERE			
			ForumID = @ForumID AND
			UserID = @ForumUserID
  
		FETCH NEXT FROM threadread_cursor  INTO @ForumUserID, @ReadThreadString
	END

CLOSE threadread_cursor
DEALLOCATE threadread_cursor		

END
GO



CREATE  PROCEDURE {databaseOwner}{objectQualifier}Forum_AA_StatisticsSiteUpdate 
(
@UpdateUserPostRank bit = 0,
@UpdateMostActiveUserList bit = 0,	
@ModuleID int, 
@TabID int	
)
AS
-- Get summary information - Total Users, Total Posts, TotalTopics, DaysPosts, and DaysTopics
DECLARE @TotalUsers int
DECLARE @TotalPosts int
DECLARE @TotalTopics int
DECLARE @TotalModerators int
DECLARE @TotalModeratedPosts int
DECLARE @NewThreadsInPast24Hours int
DECLARE @NewPostsInPast24Hours int	
DECLARE @MostViewsThreadID int
DECLARE @MostActiveThreadID int		
DECLARE @MostActiveUserID int
DECLARE @NewUsersInPast24Hours int 
DECLARE @MostReadThreadID int 
DECLARE @TotalAnonymousUsers int 
DECLARE @NewestUserID int 
SET NOCOUNT ON
-- Total Anonymous Users 
SET @TotalAnonymousUsers = 0
-- Total Moderators 	
SET @TotalModerators = ISNULL((
				SELECT 
					count(DISTINCT UserID) 
				FROM 
					{databaseOwner}{objectQualifier}Forum_Moderators FM (nolock)					
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON FM.ForumID = F.ForumID
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID
				WHERE 
					G.ModuleID = @ModuleID	
				), 0)	
-- Total Moderated Posts 	
SET @TotalModeratedPosts = ISNULL((
				SELECT 
					count(*) 
				FROM 
					{databaseOwner}{objectQualifier}Forum_ModerationAudit MA (nolock)
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Posts P (nolock) ON MA.PostID = P.PostID	
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Threads T (nolock) ON P.ThreadID = T.ThreadID
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON T.ForumID = F.ForumID
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID
				WHERE 
					G.ModuleID = @ModuleID
				),0)	
-- Most viewed thread 	
SET @MostViewsThreadID = ISNULL((
				SELECT 
					TOP 1 T.ThreadID 
				FROM 
					{databaseOwner}{objectQualifier}Forum_Threads T (nolock)					
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON T.ForumID = F.ForumID
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID							
				WHERE 
					G.ModuleID = @ModuleID AND
					F.IsPrivate = 0
				ORDER By 
					T.[Views] DESC
				), 0)
-- Most active thread 	
SET @MostActiveThreadID = ISNULL(( 
				SELECT 
					TOP 1 T.ThreadID 
				FROM 
					{databaseOwner}{objectQualifier}Forum_Threads T	(nolock)						
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON T.ForumID = F.ForumID
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID							
				WHERE 
					G.ModuleID = @ModuleID AND			
					F.IsPrivate = 0
					AND
					F.IsActive = 1
				ORDER BY 
					T.Replies DESC
				), 0)
-- Most active user	
SET @MostActiveUserID = ISNULL(( 
				SELECT 
					TOP 1 FU.UserID
				FROM 
					{databaseOwner}{objectQualifier}Forum_Users FU (nolock)						
					INNER JOIN {databaseOwner}{objectQualifier}Users U (nolock) ON FU.UserID = U.UserID	
					INNER JOIN {databaseOwner}{objectQualifier}UserPortals UP (nolock) ON UP.UserID = U.UserID
					INNER JOIN {databaseOwner}{objectQualifier}Tabs DT (nolock) ON UP.PortalID = DT.PortalID
					INNER JOIN {databaseOwner}{objectQualifier}TabModules TM (nolock) ON DT.TabID = TM.TabID
					INNER JOIN {databaseOwner}{objectQualifier}Modules DM (nolock) ON TM.ModuleID = DM.ModuleID
				WHERE 
					DM.ModuleID = @ModuleID AND 
					FU.EnableDisplayInMemberList = 1 
				ORDER BY 
					FU.PostCount DESC
				), 0)
-- Newest user - no need in DNN	
SET @NewestUserID = 0
-- Most read posts
SET @MostReadThreadID = 0	
SELECT
	-- Total Users		
	@TotalUsers = ISNULL((
				SELECT 
					COUNT(*) 
				FROM 
					{databaseOwner}{objectQualifier}Forum_Users FU (nolock)					
					INNER JOIN {databaseOwner}{objectQualifier}UserPortals UP (nolock) ON UP.UserID = FU.UserID
					INNER JOIN {databaseOwner}{objectQualifier}Tabs DT (nolock) ON UP.PortalID = DT.PortalID
					INNER JOIN {databaseOwner}{objectQualifier}TabModules TM (nolock) ON DT.TabID = TM.TabID
					INNER JOIN {databaseOwner}{objectQualifier}Modules DM (nolock) ON TM.ModuleID = DM.ModuleID									
				WHERE 
					DM.ModuleID = @ModuleID AND
					DT.TabId = @TabId
				) ,0) ,
	-- Total Posts
	@TotalPosts = ISNULL((
				SELECT 
					-- SUM(Replies + 1) 
					Count(PostID)
				FROM 
					{databaseOwner}{objectQualifier}Forum_Posts P (nolock)
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Threads T (nolock)  ON P.ThreadID = T.ThreadID					
					--{databaseOwner}{objectQualifier}Forum_Threads T (nolock)

					INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON T.ForumID = F.ForumID
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID							
				WHERE 
					G.ModuleID = @ModuleID
					AND
					F.IsActive = 1

				), 0),
	-- Total Topics
	@TotalTopics = ISNULL((
				SELECT 
					COUNT(*) 
				FROM 
					{databaseOwner}{objectQualifier}Forum_Threads T (nolock)							
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON T.ForumID = F.ForumID
					INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID							
				WHERE 
					G.ModuleID = @ModuleID
					AND
					F.IsActive = 1
				), 0),
		
	-- Total Posts in past 24 hours 		
	@NewPostsInPast24Hours = ISNULL(( 
					SELECT 
						COUNT(*) 
					FROM 
						{databaseOwner}{objectQualifier}Forum_Posts P (nolock)							
						Inner JOIN {databaseOwner}{objectQualifier}Forum_Threads T (nolock) ON T.ThreadID = P.ThreadID							
						INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON T.ForumID = F.ForumID
						INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID							
					WHERE 
						G.ModuleID = @ModuleID AND						
						P.IsApproved = 1 AND 
						P.IsLocked = 0 AND 
						P.CreatedDate > DATEADD(dd,-1,getdate())
					), 0),
	-- Total Users in past 24 hours 
	@NewUsersInPast24Hours = 0,	
	-- Total Topics in past 24 hours	
	@NewThreadsInPast24Hours = ISNULL((
					SELECT 
						COUNT(*) 
					FROM 
						{databaseOwner}{objectQualifier}Forum_Posts P (nolock) 						
						Inner JOIN {databaseOwner}{objectQualifier}Forum_Threads T (nolock) ON T.ThreadID = P.ThreadID							
						INNER JOIN {databaseOwner}{objectQualifier}Forum_Forums F (nolock) ON T.ForumID = F.ForumID
						INNER JOIN {databaseOwner}{objectQualifier}Forum_Groups G (nolock) ON F.GroupID = G.GroupID							
					WHERE 
						G.ModuleID = @ModuleID AND 
						P.IsApproved = 1 AND 
						P.ParentPostID = 0 AND 
						P.CreatedDate > DATEADD(dd,-1,getdate())
					), 0)
	
	INSERT INTO {databaseOwner}{objectQualifier}Forum_statistics_Site
	SELECT 
		CreatedDate = GetDate(),
		TotalUsers = @TotalUsers,
		TotalPosts = @TotalPosts,
		TotalModerators = @TotalModerators,
		TotalModeratedPosts = @TotalModeratedPosts,	
		TotalAnonymousUsers = @TotalAnonymousUsers,	
		TotalTopics = @TotalTopics,		
		NewPostsInPast24Hours = @NewPostsInPast24Hours,
		NewThreadsInPast24Hours = @NewThreadsInPast24Hours,
		NewUsersInPast24Hours = @NewUsersInPast24Hours,		
		MostViewsThreadID = @MostViewsThreadID,
		MostActiveThreadID = @MostActiveThreadID,
		MostActiveUserID = @MostActiveUserID,		
		MostReadThreadID = @MostReadThreadID,
		NewestUserID = @NewestUserID,	
		ModuleID = @ModuleID
	SET NOCOUNT OFF
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_StatisticsGet
(	
	@ModuleID int, 
	@UpdateWindow int = 12,
	@TabId int
)
AS
-- Do we need to update the statistics?
DECLARE @LastUpdate datetime
DECLARE @DateWindow datetime
SET @LastUpdate = ISNULL((SELECT MAX(CreatedDate) FROM {databaseOwner}{objectQualifier}Forum_Statistics_Site), '1/1/1797')
SET @DateWindow = DATEADD(hh, -@UpdateWindow, GetDate())
if (@LastUpdate <  @DateWindow)
	BEGIN
		EXEC {databaseOwner}{objectQualifier}Forum_AA_StatisticsSiteUpdate 0, 0, @ModuleID, @TabId		
	END
-- SELECT current statistics
SELECT TOP 1
	S.*,
	MostReadSubject = (SELECT Subject FROM {databaseOwner}{objectQualifier}Forum_Posts (nolock) WHERE PostID = S.MostReadThreadID),
	MostViewsSubject = (SELECT Subject FROM {databaseOwner}{objectQualifier}Forum_Posts (nolock) WHERE PostID = S.MostViewsThreadID),
	MostViewsThreadForumID = (SELECT ForumID FROM {databaseOwner}{objectQualifier}Forum_Threads (nolock) WHERE ThreadID = S.MostViewsThreadID),
	MostActiveSubject = (SELECT Subject FROM {databaseOwner}{objectQualifier}Forum_Posts (nolock) WHERE PostID = S.MostActiveThreadID),
	MostActiveThreadForumID = (SELECT ForumID FROM {databaseOwner}{objectQualifier}Forum_Threads (nolock) WHERE ThreadID = S.MostActiveThreadID),
	MostActiveUserAlias = (SELECT Alias FROM {databaseOwner}{objectQualifier}Forum_Users (nolock) WHERE UserID = S.MostActiveUserID)
FROM
	{databaseOwner}{objectQualifier}Forum_Statistics_Site S (nolock)
WHERE
	ModuleID = @ModuleID
ORDER BY CreatedDate DESC
-- SELECT TOP 10 Users
SELECT DISTINCT TOP 10
	U.*,	
	FUR.Alias,	
	FUR.LastForumsView,
	FUR.LastThreadView,
	FUR.LastActivity,	
	FUR.IsTrusted,
	FUR.PostCount,
	FUR.EnableOnlineStatus
FROM	
	{databaseOwner}{objectQualifier}Forum_Users FUR (nolock),
	{databaseOwner}{objectQualifier}Users U	(nolock),	
	{databaseOwner}{objectQualifier}UserPortals UP	(nolock),	
	{databaseOwner}{objectQualifier}Tabs DT (nolock),	
	{databaseOwner}{objectQualifier}TabModules TM (nolock),	
	{databaseOwner}{objectQualifier}Modules DM (nolock)	
	WHERE 
	FUR.UserID = U.UserID AND
	U.UserID = UP.UserID AND	
	UP.PortalID = DT.PortalID AND	
	DT.TabID = TM.TabID AND
	TM.ModuleID = DM.ModuleID AND
	DM.ModuleID = @ModuleID AND	
	U.UserName <> 'NNTP_NEWSGROUP_USER' AND
	FUR.PostCount > 0	
ORDER BY
	FUR.PostCount  DESC
-- SELECT top 10 Moderators
SELECT TOP 10
	U.*,	
	FUR.Alias,	
	FUR.LastForumsView,
	FUR.LastThreadView,
	FUR.LastActivity,	
	FUR.IsTrusted,
	FUR.PostCount,	
	M.PostsModerated
FROM
	{databaseOwner}{objectQualifier}Users U	(nolock),	
	{databaseOwner}{objectQualifier}UserPortals UP (nolock),
	{databaseOwner}{objectQualifier}Forum_Users FUR (nolock),
	{databaseOwner}{objectQualifier}Forum_Moderators M (nolock),
	{databaseOwner}{objectQualifier}Tabs DT (nolock),	
	{databaseOwner}{objectQualifier}TabModules TM (nolock),	
	{databaseOwner}{objectQualifier}Modules DM (nolock)	
	WHERE 
	U.UserID = FUR.UserID AND
	FUR.UserID = M.UserID AND
	U.UserID = UP.UserID AND
	UP.PortalID = DT.PortalID AND	
	DT.TabID = TM.TabID AND
	TM.TabID = DM.ModuleID AND
	DM.ModuleID = @ModuleID AND	
	M.PostsModerated > 0 	
ORDER BY
	PostsModerated DESC
-- SELECT Moderator actions
SELECT
	Description,
	TotalActions
FROM
	{databaseOwner}{objectQualifier}Forum_ModerationAction (nolock)
ORDER BY 
	TotalActions DESC
GO



--SP's

-- This procedure is to be called by 
-- Forum_PostAdd 
-- Forum_ModeratePostApprove

CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_AA_ForumPostCountUpdate
(
	@ForumID int,
	@ThreadID int,
	@PostID int,	
	@PostAuthorID int,
	@Action nvarchar(50) 	
)
AS

DECLARE @ForumThreadCount int
DECLARE @ForumLastPostID int

-- Post add non-moderate forum
IF @Action = 'add'
BEGIN
	-- If this is the first post of thread, also increase forum thread count
	IF @ThreadID = @PostID
	BEGIN
		SET @ForumThreadCount = ((SELECT TotalThreads FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) + 1)
	END
	ELSE
	BEGIN
		SELECT @ForumThreadCount = [TotalThreads] FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID
	END
	-- it's enough, update forum info
	UPDATE
		{databaseOwner}{objectQualifier}Forum_Forums
	SET
		TotalPosts = ((SELECT TotalPosts FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) + 1),
		TotalThreads = @ForumThreadCount,
		MostRecentThreadID = @ThreadID,
		MostRecentPostID = @PostID,
		MostRecentPostAuthorID = @PostAuthorID,		
		MostRecentPostDate = (SELECT CreatedDate FROM {databaseOwner}{objectQualifier}Forum_Posts (nolock) WHERE PostID = @PostID)
	WHERE
		ForumID = @ForumID
END

-- Post submit moderate forum
ELSE IF @Action = 'submit'
BEGIN
	UPDATE
		{databaseOwner}{objectQualifier}Forum_Forums
	SET
		PostsToModerate = ((SELECT PostsToModerate FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) + 1)
	WHERE
		ForumID = @ForumID
END

-- Post approved by moderator
ELSE IF @Action = 'approve'
BEGIN
	-- If this is the first post of thread, also increase forum thread count
	IF @ThreadID = @PostID
	BEGIN
		SET @ForumThreadCount = ((SELECT TotalThreads FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) + 1)
	END
	ELSE
	BEGIN
		SELECT @ForumThreadCount = [TotalThreads] FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID
	END
	UPDATE
		{databaseOwner}{objectQualifier}Forum_Forums
	SET
		PostsToModerate = ((SELECT PostsToModerate FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) - 1),
		TotalPosts = ((SELECT TotalPosts FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) + 1),
		TotalThreads = @ForumThreadCount,
		MostRecentThreadID = @ThreadID,
		MostRecentPostID = @PostID,
		MostRecentPostAuthorID = @PostAuthorID,		
		MostRecentPostDate = (SELECT CreatedDate FROM {databaseOwner}{objectQualifier}Forum_Posts (nolock) WHERE PostID = @PostID)
	WHERE
		ForumID = @ForumID
END

-- Post rejected or deleted by moderator
ELSE IF @Action = 'reject'
BEGIN
	UPDATE
		{databaseOwner}{objectQualifier}Forum_Forums
	SET
		PostsToModerate = ((SELECT PostsToModerate FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) - 1)
	WHERE
		ForumID = @ForumID
END

ELSE IF @Action = 'delete'
BEGIN
	UPDATE
		{databaseOwner}{objectQualifier}Forum_Forums
	SET
		PostsToModerate = ((SELECT PostsToModerate FROM {databaseOwner}{objectQualifier}Forum_Forums (nolock) WHERE ForumID = @ForumID) - 1)
	WHERE
		ForumID = @ForumID
END
GO