if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_forum_reply_index]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_forum_reply_index]
GO
CREATE TABLE [dbo].[tbl_forum_reply_index] (
	[topic_id] [int] NULL ,
	[reply_id] [int] NULL ,
	[chunkID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[data_Chunk] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE 
  INDEX [indx_reply_index] ON [dbo].[tbl_forum_reply_index] ([reply_id], [data_Chunk])
WITH
    DROP_EXISTING
ON [PRIMARY]

CREATE PROCEDURE dbo.Build_forum_index AS

--Lets declare the cursor for the records we are going to slam into the index table.
declare all_forum_messages cursor for 
	select 
		
		reply_id, 
		cast(r_message as varchar(8000)) as r_message ,
		topic_id
	from 
		dbo.tbl_forum_reply 
	union
	select 
		
		null, 
		cast(t_message as varchar(8000)) as r_message ,
		topic_id
	from 
		dbo.tbl_forum_topics
	

declare @reply_ID int
declare @r_message varchar(8000)
declare @stringchunk varchar(255)
declare @stringpos int
declare @topic_id int

set @stringchunk = '';
set @stringpos = 255;

--Clear out the old records from the index table
delete from tbl_forum_reply_index;
-- Re-set the autonumber field to 0
DBCC CHECKIDENT (tbl_forum_reply_index,reseed,0);


open all_forum_messages
fetch next from all_forum_messages into  @reply_id, @r_message, @topic_id
while @@fetch_status = 0  --Loop through the reply records, and add chunks to the index table.
begin
	while @stringpos < len(@r_message) --Loop through the string.
	begin
		set @stringpos = @stringpos + 255
		if @stringpos > len(@r_message) 
		begin
			set @stringpos = len(@r_message);
		end 
		set @stringchunk = right(left(@r_message,@stringpos),255)
		insert into tbl_forum_reply_index (topic_id, reply_id, data_chunk) values(@topic_id, @reply_id,@stringchunk) --Insert the chunk to the index table.
		set @stringchunk = '';
	end;
	set @stringpos = 0;

	fetch next from all_forum_messages into @reply_id, @r_message, @topic_id
end;

close all_forum_messages
GO

strsql= "SELECT DISTINCT  C.CAT_STATUS, C.CAT_SUBSCRIPTION, C.CAT_NAME, C.CAT_ORDER" & _
	", F.F_ORDER, F.FORUM_ID, F.F_SUBJECT, F.CAT_ID" & _
	", F.F_SUBSCRIPTION, F.F_STATUS" & _
	", T.TOPIC_ID, T.T_AUTHOR, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST" & _
	", T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, T.T_REPLIES, T.T_UREPLIES, T.T_VIEW_COUNT" & _
	", M.MEMBER_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME" & _
	", F.F_PRIVATEFORUMS, F.F_PASSWORD_NEW " & _
	" FROM " & _
	strTablePrefix & "forum f, " & _
	strTablePrefix & "topics t, " & _
	strTablePrefix & "reply_index r, " & _
	strTablePrefix & "members m, " & _
	strTablePrefix & "category C, " & _
	strTablePrefix & "members members_1 " & _
	"where " & _
	"f.forum_id = t.forum_id " & _
	"and t.topic_id = r.topic_id " & _
	"and t.t_author = m.member_id " & _
	"and t.cat_id = c.cat_id " & _
	"and members_1.member_id = t.t_last_post_author " 
	if Request.Form("Search") <> "" then
		'User submitted a search
		strsql = strsql & "and (" 
		if Request.Form("SearchMessage") = 1 then
			'They are sarching for subject only
			if strAndOr = "phrase" then
				'They want to search for the entire string.
				strsql = strsql & "t.t_subject like '%"& ChkString(Request.Form("Search"), "SQLString") & "%'"	
			else
				cnt = 0
				For Each word in keywords
					SearchWord = ChkString(word, "SQLString")
					strsql = strsql & "t.t_subject like '%" & searchword & "%'"
					if cnt < keycnt then 
					    strSql = strSql & strAndOr
					end if
						cnt = cnt + 1
				next
			end if
		else
				if strAndOr = "phrase" then
					'They want to search for the whole string
					strsql = strsql  & "t.t_subject like '%"& ChkString(Request.Form("Search"), "SQLString") & "%'" & _
						"or r.data_chunk like '%"& ChkString(Request.Form("Search"), "SQLString") & "%'"	
				else
					'They want to search for any of the words
					cnt = 0
				For Each word in keywords
					SearchWord = ChkString(word, "SQLString")
					strsql = strsql & "(t.t_subject like '%" & searchword & "%' or r.data_chunk like '%" & searchword & "%')"
					if cnt < keycnt then 
					    strSql = strSql & strAndOr
					end if
						cnt = cnt + 1
				next
				end if
		end if
		strsql = strsql  & ")" 'End of the string search criteria
		
		if cLng(Request.Form("SearchDate")) <> 0 then
			'User wants to search by date
			dt = cLng(Request.Form("SearchDate"))
			strSql = strSql & " AND (T.T_LAST_POST > '" & DateToStr(dateadd("d", -dt, strForumTimeAdjust)) & "')"
			strSql = strSql & " AND (T.T_LAST_POST > '" & DateToStr(dateadd("d", -dt, strForumTimeAdjust)) & "')"
		end if
		
		if strUseMemberDropDownBox = 0 then
			'User selected a member from a drop down box.
			intSearchMember = getMemberID(chkString(Request.Form("SearchMember"),"SQLString"))
			if intSearchMember <> 0 then
				strSql = strSql & " AND M.MEMBER_ID = " & cLng(intSearchMember) & " "
			end if
		else
			if cLng(Request.Form("SearchMember")) <> 0 then
				strSql = strSql & " AND M.MEMBER_ID = " & cLng(Request.Form("SearchMember")) & " "
			end if
		end if
		
		if cLng(Request.Form("Forum")) <> 0 then
			'User wants to search by forum.
            if strPrivateForums = "1" and mLev < 4 then
                strSql = strSql & " AND F.FORUM_ID = " & cLng(Request.Form("Forum")) & " AND F.FORUM_ID IN (" & allAllowedForums & ")"			    
			Else
			  strSql = strSql & " AND F.FORUM_ID = " & cLng(Request.Form("Forum")) & " "
			end If			
		Else
		   if strPrivateForums = "1" and mLev < 4 then
			  strSql = strSql & " AND F.FORUM_ID IN (" & allAllowedForums & ")"
            end if
		end if

		
	end if