/* Add Index on ModuleID to Forum_Groups */
/**************************************/

IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = N'IX_{objectQualifier}Forum_Groups_ModuleID')
        CREATE NONCLUSTERED INDEX IX_{objectQualifier}Forum_Groups_ModuleID ON {databaseOwner}{objectQualifier}Forum_Groups (ModuleID) ON [PRIMARY]
GO

drop procedure {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll
GO

drop procedure {databaseOwner}{objectQualifier}Forum_ThreadSearch
GO

CREATE procedure {databaseOwner}{objectQualifier}Forum_WhatsNewGetAll
(
@ModuleID int,
@NumberOfThread int, 
@UserID int, 
@FromDate datetime,
@ToDate datetime,
@Filter nvarchar(500)
)
AS

-- Create a temp table to store the select results

CREATE TABLE #PageIndex 
(

IndexID int IDENTITY (1, 1) NOT NULL,
PostID int)

DECLARE @sql nvarchar(1000)

SELECT @sql = 'INSERT INTO #PageIndex (PostID)' +

' SELECT Max(PostID) As PostID' +
' FROM {objectQualifier}Forum_Posts, {objectQualifier}Forum_Threads, {objectQualifier}Forum_Forums, {objectQualifier}Forum_Groups (nolock) ' + 
' WHERE ModuleID = ' + CONVERT(varchar, @ModuleID) +
' AND {objectQualifier}Forum_Groups.GroupID = {objectQualifier}Forum_Forums.GroupID' +
' AND {objectQualifier}Forum_Forums.ForumID = {objectQualifier}Forum_Threads.ForumID' +
' AND {objectQualifier}Forum_Threads.ThreadID = {objectQualifier}Forum_Posts.ThreadID' + 
' AND {objectQualifier}Forum_Posts.IsApproved = 1' +
' AND {objectQualifier}Forum_Posts.IsLocked = 0' +
' AND ({objectQualifier}Forum_Threads.ForumID NOT IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums) OR' + 
' {objectQualifier}Forum_Threads.ForumID IN (SELECT ForumID FROM {objectQualifier}Forum_PrivateForums (nolock) ' +
' WHERE RoleID IN (SELECT RoleID FROM {objectQualifier}UserRoles (nolock) WHERE UserID =' + CONVERT(varchar, @UserID) + 
')))' +

@Filter + 

' GROUP BY {objectQualifier}Forum_Posts.ThreadID' +
' ORDER BY Max({objectQualifier}Forum_Posts.PostID) DESC'

Print @sql

EXEC(@sql)

SELECT 

G.ModuleID, 
T.ForumID,
F.Name AS ForumName,
P1.ThreadID,
P1.PostID As LastPostID,
P2.UserID As StartedByUserID,
U2.Alias As StartedByAlias,
P2.Subject As StartedSubject,
P1.Subject As LastSubject, 
P1.UserID As LastPostUserID,
U1.Alias As LastPostAlias,
P1.Body As LastPostBody,
P1.CreatedDate As LastCreatedDate

FROM 

#PageIndex PageIndex (nolock), {objectQualifier}Forum_Posts P1 (nolock), {objectQualifier}Forum_Posts P2 (nolock), {objectQualifier}Forum_Threads T (nolock), {objectQualifier}Forum_Forums F (nolock), {objectQualifier}Forum_Groups G (nolock), {objectQualifier}Modules M (nolock), {objectQualifier}Forum_Users U1 (nolock), {objectQualifier}Forum_Users U2 (nolock)

WHERE 

P1.PostID = PageIndex.PostID AND
P1.CreatedDate > @FromDate AND
P1.CreatedDate < @ToDate AND
P1.ThreadID = T.ThreadID AND
P1.ThreadID = P2.PostID AND 
P1.UserID = U1.UserID AND 
P2.UserID = U2.UserID AND
T.ForumID = F.ForumID AND
F.GroupID = G.GroupID AND
G.ModuleID = M.ModuleID AND
PageIndex.IndexID <= @NumberOfThread

ORDER BY

PageIndex.IndexID

GO

DELETE FROM {databaseOwner}{objectQualifier}Forum_TrackedForums
GO

DELETE FROM {databaseOwner}{objectQualifier}Forum_TrackedWhatsNew
GO

ALTER TABLE {databaseOwner}{objectQualifier}Forum_Posts WITH NOCHECK 
	ADD [DateApproved] [datetime] NULL
GO


UPDATE {databaseOwner}{objectQualifier}Forum_Posts
	SET [DateApproved] = GetDate()
		WHERE
		IsApproved = 1
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}Forum_ThreadSearch
(
@ModuleID int,
@StartDate datetime
)
AS
SELECT
	P.[ThreadID],
	P.[PostID],
	P.[Subject],
	P.[Body],
	P.[CreatedDate],	
	P.[UserID] AS CreatedByUser,	
	P.[UpdatedDate],	
	P.[UpdatedByUser],	
	T.[ForumID],
	P.[DateApproved]
FROM
	{objectQualifier}Forum_Posts P (nolock)  
	INNER JOIN {objectQualifier}Forum_Threads T ON P.ThreadID = T.ThreadID
	INNER JOIN {objectQualifier}Forum_Forums F ON T.ForumID = F.ForumID
	INNER JOIN {objectQualifier}Forum_Groups G ON F.GroupID = G.GroupID
WHERE
	P.ThreadID = T.ThreadID AND
	T.ForumID = F.ForumID AND
	F.GroupID = G.GroupID AND
	G.ModuleID = @ModuleID AND
	F.IsPrivate = 0 AND 
	P.IsApproved = 1 AND
	P.IsLocked = 0 AND
   	( P.[DateApproved] >= @StartDate or @StartDate is null )

GO