|
|
<%
Dim cnDB ' As ADODB.Connection
CheckSecurity
Set cnDB = OpenDB()
Dim rsData ' As ADODB.Recordset
Dim strSQL ' As String
Dim strURL ' used for Actions links
Dim lngGrandParentTopicID
Dim lngParentTopicID
Dim strParentTopicName
Dim lngCurrentTopicID
Dim strCurrentTopicName
Dim lngChildTopicID
Dim strChildTopicName
Dim strGroup
Dim strSearch
Dim strSearch2
Dim strSearchText
Dim lngAccountID
Dim i ' Used for alternating row colors
Dim iMod
strSearch2 = ""
i = 0
lngAccountID = Request.Cookies("mynetwork")("AID")
DBC "lngAccountID = " & lngAccountID
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Check to see if more than one word was passed with the
' search string.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If InStr(Request.querystring("q"), " ") > 0 Then
strSearch = Left(Request.querystring("q"), (InStr(Request.querystring("q"), " ")-1))
strSearch2 = Mid(Request.querystring("q"), (InStr(Request.querystring("q"), " ")+1), 50)
Else
strSearch = Request.querystring("q")
strSearch2 = ""
End If
If strSearch <> "" Then
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Changes the SQL string to select by search text
' based on whether one or two words was entered.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
If strSearch2 <> "" Then
strSQL = "SELECT * FROM tblNotes " _
& "WHERE (pkAccountID Like " & lngAccountID & " AND (Name Like " & DQ & "%" & strSearch & "%" & DQ _
& "OR Name Like " & DQ & "%" & strSearch2 & "%" & DQ _
& "OR Description Like " & DQ & "%" & strSearch & "%" & DQ _
& "OR Description Like " & DQ & "%" & strSearch2 & "%" & DQ & "))" _
& "ORDER BY Name"
' strSQL = "SELECT tblTopics.TopicName, " _
' & "tblNotes.Name, tblNotes.Description, " _
' & "tblNotes.pkNoteID, tblTopics.pkTopicID " _
' & "FROM tblNotes INNER JOIN tblTopics ON " _
' & "tblNotes.pkTopicID = tblTopics.pkTopicID " _
' & "WHERE pkAccountID Like " & lngAccountID & "AND (tblNotes.Name Like " _
' & DQ & "%" & strSearch & "%" & DQ _
' & "OR tblNotes.Name Like " & DQ & "%" & strSearch2 & "%" & DQ _
' & "OR tblNotes.Description Like " & DQ & "%" & strSearch & "%" & DQ _
' & "OR tblNotes.Description Like " & DQ & "%" & strSearch2 & "%" & DQ & ")) " _
' & "ORDER BY tblTopics.TopicName, tblNotes.Name"
strSearchText = strSearch & " " & strSearch2
Else
strSQL = "SELECT * FROM tblNotes " _
& "WHERE (pkAccountID Like " & lngAccountID & " AND (Name Like " & DQ & "%" & strSearch & "%" & DQ _
& "OR Description Like " & DQ & "%" & strSearch & "%" & DQ & ")) " _
& "ORDER BY Name"
' strSQL = "SELECT tblTopics.TopicName, " _
' & "tblNotes.Name, tblNotes.Description, " _
' & "tblNotes.pkNoteID, tblTopics.pkTopicID " _
' & "FROM tblNotes INNER JOIN tblTopics ON " _
' & "tblNotes.pkTopicID = tblTopics.pkTopicID " _
' & "WHERE (pkAccountID Like " & lngAccountID & "AND (tblNotes.Name Like " _
' & DQ & "%" & strSearch & "%" & DQ _
' & "OR tblNotes.Description Like " & DQ & "%" & strSearch & "%" & DQ & ")) " _
' & "ORDER BY tblTopics.TopicName, tblNotes.Name"
strSearchText = strSearch
End If
DBC "strSQL = " & strSQL
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' First half of Retrieve where search criteria HAS been
' entered into the Search box.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
%>
Notes Matching the Search Term(s)
"
rsData.Close
Set rsData = Nothing
Else
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Start of the second half of Retrieve where the search
' box is empty.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Define lngParentTopicID and lngCurrentTopicID as the lowest
' pkTopicID if there is no CTID passed to the page.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Request(CTID) <> "" Then
lngParentTopicID = Request(PTID)
lngCurrentTopicID = Request(CTID)
Else
Dim rsMin ' As ADODB.Recordset
Dim strSQL2 ' As String
strSQL2 = "SELECT Min(tblTopics.ParentTopicID) " _
& "FROM tblTopics WHERE tblTopics.pkAccountID like " _
& lngAccountID
Set rsMin = cnDB.Execute(strSQL2)
lngParentTopicID = rsMin(0).Value
lngCurrentTopicID = rsMin(0).Value
rsMin.Close
Set rsMin = Nothing
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Select the Parent Level Topic
' WHERE pkTopicID = pti
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
strSQL = "SELECT * FROM tblTopics " _
& "WHERE pkTopicID = " & lngParentTopicID _
& " AND pkAccountID like " & lngAccountID
Set rsData = cnDB.Execute(strSQL)
strParentTopicName = rsData("TopicName")
lngGrandParentTopicID = rsData("ParentTopicID")
rsData.Close
set rsData = Nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Select the Current Topic
' WHERE ParentTopicID = pti AND pkTopicID = cti
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
strSQL = "SELECT * FROM tblTopics " _
& "WHERE ParentTopicID = " & lngParentTopicID _
& " AND pkTopicID = " & lngCurrentTopicID _
& " AND pkAccountID like " & lngAccountID
Set rsData = cnDB.Execute(strSQL)
strCurrentTopicName = rsData("TopicName")
rsData.Close
set rsData = Nothing
''''''''''''''''''''''''''''''''''''''''''''''
' Display the Create New Note and Topic links
' and the Search Box.
'
''''''''''''''''''''''''''''''''''''''''''''''
WL ""
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Create table for Topics and Notes
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
WL ""
''''''''''''''''''''''''''''''''''''''''''''''
' Show Parent Level Topic
'
'''''''''''''''''''''''''''''''''''''''''''''''
WL ""
WL " "
WL "" & strParentTopicName & " | "
WL " | "
''''''''''''''''''''''''''''''''''''''''''''''
' Show Current Level Topic
'
'''''''''''''''''''''''''''''''''''''''''''''''
WL ""
WL "| "
WL ""
WL "" & strCurrentTopicName & " | "
WL " | "
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Select the Child Level Topics
' WHERE ParentTopicID = cti
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
strSQL = "SELECT * FROM tblTopics " _
& "WHERE ParentTopicID = " & lngCurrentTopicID _
& " AND pkAccountID like " & lngAccountID _
& " ORDER BY TopicName"
Set rsData = cnDB.Execute(strSQL)
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Cycle through all of the Child Level topics indented
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
Do Until rsData.EOF
If rsData("pkTopicID") <> lngParentTopicID Then
''''''''''''''''''''''''''''
' set row background colors
''''''''''''''''''''''''''''
i = i + 1
iMod = i Mod 2
If iMod = 0 Then
WL ""
Else
WL " "
End If
WL " "
WL ""
WL ""
WL "" & rsData("TopicName") & " | "
WL ""
WL "U | "
WL "D"
WL " | "
WL " "
End If
rsData.MoveNext
Loop
rsData.Close
Set rsData = Nothing
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Show Notes based on Current Topic Level
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Selects all of the Notes where the pkTopicID
' is the same as the lngCurrentTopicID
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
strSQL = "SELECT * FROM tblNotes " _
& "WHERE pkTopicID = " & lngCurrentTopicID _
& " AND pkAccountID like " & lngAccountID _
& " ORDER BY Name"
Set rsData = cnDB.Execute(strSQL)
'''''''''''''''''''''''''''''''''''''''''''''''''
' Cycle through all of the Notes at this level
'
'''''''''''''''''''''''''''''''''''''''''''''''''
Do Until rsData.EOF
''''''''''''''''''''''''''''
' set row background colors
''''''''''''''''''''''''''''
i = i + 1
iMod = i Mod 2
If iMod = 0 Then
WL ""
Else
WL " "
End If
WL ""
WL ""
WL ""
WL " "
If Len(rsData("Name")) >= 35 Then
WL "" _
& Left(rsData("Name"), 35) & "..." _
& ""
Else
WL "" _
& rsData("Name") & ""
End If
WL " | "
%>
&CTID=<% = rsData("pkTopicID") %>">U |
&CTID=<% = rsData("pkTopicID") %>">D
|
<%
rsData.MoveNext
Loop
WL " "
rsData.Close
Set rsData = Nothing
WL " |
"
WL "