<% 'This function returns a list of message subjects for the search specified 'Input: ' paStrSearchWords: Array of strings with keywors to search for, if not array no search filter is used. ' pintUserID: integer(long) the users ID, used to display subscription status, and read/unread status for messages ' pintMessageID: integer(long) the currently selected message (set to 0 (zero) if no current message. ' paGroupIDs: array of integers(longs), containing the ID's of the groups to list messages for ' pblnSubjects: bool, search the message subjects (disabled if paStrSearchWords is not an array) ' pblnAuthors: bool, search the author names (disabled if paStrSearchWords is not an array) ' pblnBodys: bool, search the message bodies (disabled if paStrSearchWords is not an array) ' pblnShowActive: bool, show unarchived messages ' pblnShowArchived: bool, show archived messages ' pblnShowApproved: bool, show approved messages ' pblnShowUnapproved: bool, show unapproved messages ' pblnHideRead: bool, if true read messages are hidden and only unread messages shown. 'Returns: recordset of message subjects matching search filter as specified. function ListMessagesForSearch(ByRef pobjCon, ByVal paStrSearchWords, ByVal pintUserID, _ ByVal pintMessageID, ByVal paGroupIDs, _ ByVal pblnSubjects, ByVal pblnAuthors, ByVal pblnBodys, _ ByVal pblnShowActive, ByVal pblnShowArchived, _ ByVal pblnShowApproved, ByVal pblnShowUnapproved, _ ByVal pblnHideRead) 'ID, debth, author, authorID, timestamp, subject, thread, anyreplies, anyattachments, subthread, replylocked, isApproved, thread.readonly, readBefore, subscribe, Archived dim strSQL, i, _ strSQLWhereSearch, strSQLWhereArchivedSearch, strWildCard, _ strSQLCombine, strSQLActive, strSQLArchived, _ strSQLWhereApproved , strSQLWhereArchiveApproved, _ strWhereGroups, strWhereArchivedGroups, _ strSQLWhereRead, objRs if not pblnShowUnapproved then strSQLWhereApproved = strSQLWhereApproved & " AND forumMessages.isApproved<>0" strSQLWhereArchiveApproved = strSQLWhereArchiveApproved & " AND forumArchivedMessages.isApproved<>0" end if if not pblnShowApproved then strSQLWhereApproved = strSQLWhereApproved & " AND forumMessages.isApproved=0" strSQLWhereArchiveApproved = strSQLWhereArchiveApproved & " AND forumArchivedMessages.isApproved=0" end if strWildCard = "%" 'if DB_TYPE="Access" and pblnShowActive and pblnShowArchived then strWildCard = "*" 'prepare the where clause for the search if isArray(paStrSearchWords) then for i = LBound(paStrSearchWords) to UBound(paStrSearchWords) strSQLWhereSearch = strSQLWhereSearch & " AND (" strSQLCombine = "" if pblnSubjects then strSQLWhereSearch = strSQLWhereSearch & strSQLCombine & "(forumMessages.subject Like '" & strWildCard & stringForSQLLike(paStrSearchWords(i)) & strWildCard & "')" strSQLCombine = " OR " end if if pblnAuthors then strSQLWhereSearch = strSQLWhereSearch & strSQLCombine & "(forumMessages.author Like '" & strWildCard & stringForSQLLike(paStrSearchWords(i)) & strWildCard & "')" strSQLCombine = " OR " end if if pblnBodys then strSQLWhereSearch = strSQLWhereSearch & strSQLCombine & "(forumMessages.message Like '" & strWildCard & stringForSQLLike(paStrSearchWords(i)) & strWildCard & "')" strSQLCombine = " OR " end if strSQLWhereSearch = strSQLWhereSearch & ")" next 'prepare the where clause for the search in archived messages for i = LBound(paStrSearchWords) to UBound(paStrSearchWords) strSQLWhereArchivedSearch = strSQLWhereArchivedSearch & " AND (" strSQLCombine = "" if pblnSubjects then strSQLWhereArchivedSearch = strSQLWhereArchivedSearch & strSQLCombine & "(forumArchivedMessages.subject Like '" & strWildCard & stringForSQLLike(paStrSearchWords(i)) & strWildCard & "')" strSQLCombine = " OR " end if if pblnAuthors then strSQLWhereArchivedSearch = strSQLWhereArchivedSearch & strSQLCombine & "(forumArchivedMessages.author Like '" & strWildCard & stringForSQLLike(paStrSearchWords(i)) & strWildCard & "')" strSQLCombine = " OR " end if if pblnBodys then strSQLWhereArchivedSearch = strSQLWhereArchivedSearch & strSQLCombine & "(forumArchivedMessages.message Like '" & strWildCard & stringForSQLLike(paStrSearchWords(i)) & strWildCard & "')" strSQLCombine = " OR " end if strSQLWhereArchivedSearch = strSQLWhereArchivedSearch & ")" next end if for i= LBound(paGroupIDs) to UBound(paGroupIDs) if Len(strWhereGroups)=0 then strWhereGroups = " (forumMessages.thegroup=" & paGroupIDs(i) else strWhereGroups = strWhereGroups & " OR forumMessages.thegroup=" & paGroupIDs(i) end if next strWhereGroups = strWhereGroups & ")" strWhereArchivedGroups = Replace(strWhereGroups, "forumMessages", "forumArchivedMessages") if DB_TYPE="Access" then if pblnShowActive and pintUserID>0 then if not pblnHideRead then 'show both read and unread message strSQL = "SELECT forumMessages.ID,debth,author,authorID,timestamp,subject,forumMessages.thread,anyreplies,anyAttachments,forumMessages.subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 1 AS readBefore, 1 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch & " AND forumMessages.ID IN (SELECT message FROM forumMessagesread WHERE userID=" & pintUserID & ") AND forumMessages.thread IN (SELECT thread FROM forumEmailNotifications WHERE userID=" & pintUserID & ") ORDER BY forumMessages.thread DESC, forumMessages.subthread ASC" & _ " UNION SELECT forumMessages.ID,debth,author,authorID,timestamp,subject,forumMessages.thread,anyreplies,anyAttachments,forumMessages.subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 1 AS readBefore, 0 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch & " AND forumMessages.ID IN (SELECT message FROM forumMessagesread WHERE userID=" & pintUserID & ") AND NOT forumMessages.thread IN (SELECT thread FROM forumEmailNotifications WHERE userID=" & pintUserID & ") ORDER BY forumMessages.thread DESC, forumMessages.subthread ASC" & _ " UNION SELECT forumMessages.ID,debth,author,authorID,timestamp,subject,forumMessages.thread,anyreplies,anyAttachments,forumMessages.subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 0 AS readBefore, 1 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch & " AND forumMessages.ID NOT IN (SELECT message FROM forumMessagesread WHERE userID=" & pintUserID & ") AND forumMessages.thread IN (SELECT thread FROM forumEmailNotifications WHERE userID=" & pintUserID & ") ORDER BY forumMessages.thread DESC, forumMessages.subthread ASC" & _ " UNION SELECT forumMessages.ID,debth,author,authorID,timestamp,subject,forumMessages.thread,anyreplies,anyAttachments,forumMessages.subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 0 AS readBefore, 0 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch & " AND forumMessages.ID NOT IN (SELECT message FROM forumMessagesread WHERE userID=" & pintUserID & ") AND NOT forumMessages.thread IN (SELECT thread FROM forumEmailNotifications WHERE userID=" & pintUserID & ") ORDER BY forumMessages.thread DESC, forumMessages.subthread ASC" else 'show unread messages (and the pintMessageID message even if it's read) strSQL = "SELECT forumMessages.ID,debth,author,authorID,timestamp,subject,forumMessages.thread,anyreplies,anyAttachments,forumMessages.subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 0 AS readBefore, 1 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch & " AND (forumMessages.ID NOT IN (SELECT message FROM forumMessagesread WHERE userID=" & pintUserID & ") OR forumMessages.ID=" & pintMessageID & ") AND forumMessages.thread IN (SELECT thread FROM forumEmailNotifications WHERE userID=" & pintUserID & ") ORDER BY forumMessages.thread DESC, forumMessages.subthread ASC" & _ " UNION SELECT forumMessages.ID,debth,author,authorID,timestamp,subject,forumMessages.thread,anyreplies,anyAttachments,forumMessages.subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 0 AS readBefore, 0 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch & " AND (forumMessages.ID NOT IN (SELECT message FROM forumMessagesread WHERE userID=" & pintUserID & ") OR forumMessages.ID=" & pintMessageID & ") AND NOT forumMessages.thread IN (SELECT thread FROM forumEmailNotifications WHERE userID=" & pintUserID & ") ORDER BY forumMessages.thread DESC, forumMessages.subthread ASC" end if elseif pblnShowActive then strSQL = "SELECT forumMessages.ID,debth,author,authorID,timestamp,subject,forumMessages.thread,anyreplies,anyAttachments,forumMessages.subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 1 AS readBefore, 0 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch & " ORDER BY forumMessages.thread DESC, forumMessages.subthread ASC" end if else 'MS SQL Server if pblnHideRead then 'show only unread messages (and the pintMessageID message even if it's read) strSQLWhereRead = " AND (forumMessagesread.ID IS NULL OR forumMessages.ID=" & pintMessageID & ")" end if if pblnShowActive and pintUserID>0 then strSQL = "SELECT forumMessages.ID,debth,author,authorID,[timestamp],subject,forumMessages.thread AS thread,anyreplies,anyAttachments,forumMessages.subthread AS subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, forumMessagesread.ID AS readBefore, forumEmailnotifications.ID AS subscribe, 0 AS Archived FROM forumMessages LEFT JOIN forumMessagesread ON (forumMessagesread.userID=" & pintUserID & " AND forumMessages.ID = forumMessagesread.message) LEFT JOIN forumEmailnotifications ON (forumEmailnotifications.userID=" & pintUserID & " AND forumMessages.thread = forumEmailnotifications.thread) INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereRead & strSQLWhereSearch elseif pblnShowActive then strSQL = "SELECT forumMessages.ID,debth,author,authorID,[timestamp],subject,forumMessages.thread AS thread,anyreplies,anyAttachments,forumMessages.subthread AS subthread,replylocked,isApproved,forumSubthreads.readonly AS threadreadonly, 1 AS readBefore, 0 AS subscribe, 0 AS Archived FROM forumMessages INNER JOIN forumSubthreads ON (forumMessages.thread = forumSubthreads.ID) WHERE " & strWhereGroups & strSQLWhereApproved & strSQLWhereSearch end if end if 'MSSQL & Access if pblnShowArchived then if len(strSQL)>0 then strSQL = strSQL & " UNION " strSQL = strSQL & "SELECT ID,debth,author,authorID,timestamp,subject,thread,anyreplies,anyAttachments,subthread,replylocked,isApproved, 1 AS threadreadonly, 1 AS readBefore, 0 AS subscribe, 1 AS Archived FROM forumArchivedMessages WHERE " & strWhereArchivedGroups & strSQLWhereArchivedSearch & strSQLWhereArchiveApproved & " ORDER BY thread DESC, subthread ASC" elseif DB_TYPE="MSSQL" then strSQL = strSQL & " ORDER BY thread DESC, subthread ASC" end if 'open disconnected static recordset set objRs = Server.CreateObject("ADODB.recordset") objRs.CursorLocation = adUseClient call objRs.Open(strSQL, pobjCon, adOpenStatic, adLockReadOnly, adCmdText) set objRs.ActiveConnection = Nothing set ListMessagesForSearch = objRs end function %>