<% Dim curYear, Conn, sConnString, bookCount Dim pageSum, pageMax, strCol, perDay, strCmt Response.Write "<html><head><title>Book List</title></head>" Response.Write "<body bgcolor=""A0B0F0"" link=""000000"" vlink=""404040"">" Response.Write vbCrLf & vbCrLf & "<!-version 14mar02 sgadd wasoxygen@hotmail.com->" Response.Write vbCrLf & vbCrLf 'title bar Response.Write "<table width=100% border=0 cellpadding=0 cellspacing=0>" Response.Write "<tr><td align=right><img src=""img\round_l.gif""></td>" Response.Write "<td width=60% bgcolor=""FFFFFF"" valign=middle>" Response.Write "<center><img src=""img\BL" & Day(Now) Response.Write ".gif"" width=170 height=40 " Response.Write "alt=""book list""></center></td>" Response.Write "<td align=left><img src=""img\round_r.gif""></td></tr>" Response.Write "<tr><td colspan=3><center>" 'main section curYear = Request.QueryString("Year") If Not IsDate("01/01/" & curYear) Then curYear = Year(Now) End If login = Request.Form("login") If login = "false" Then Session("UserLoggedIn") = "" End If Response.Write "<font face=""arial""><br>" Response.Write "<b><font size=+3>" & curYear & "</font>" Response.Write "<br><b>LEADERBOARD</b>" Response.Write "<br><br>" Set Conn = Server.CreateObject("ADODB.Connection") sConnString = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=" sConnString = sConnString & Server.MapPath("\wasoxygen\db\books.mdb") & ";" Conn.Open(sConnString) 'Show Leaderboard: table of readers ordered by total pages sql = "SELECT tblReaders.ReaderName, tblReaders.ReaderID, " sql = sql & "Sum(tblBooks.BookPages) AS SumOfBookPages, " sql = sql & "Max(tblBooks.BookDate) AS MaxOfBookDate, " sql = sql & "Count(tblBooks.BookID) AS BookCount " sql = sql & "FROM tblReaders INNER JOIN tblBooks " sql = sql & "ON tblReaders.ReaderID = tblBooks.ReaderID " sql = sql & "WHERE tblBooks.BookDate > #12/31/" & curYear-1 & "# " sql = sql & "AND tblBooks.BookDate < #01/01/" & curYear+1 & "# " sql = sql & "GROUP BY tblReaders.ReaderName, tblReaders.ReaderID " sql = sql & "ORDER BY Sum(tblBooks.BookPages) DESC" set RS = Conn.Execute(sql) If RS.EOF Then Response.Write "<p>No books have been entered for this year.<p>" Else Response.Write "<table border=0 cellpadding=0 cellspacing=0>" Response.Write "<tr><td align=center> <font size=-1>READER</font>" Response.Write " </td>" Response.Write "<td align=center> <font size=-1>PAGES</font>" Response.Write " </td><td></td>" Response.Write "<td align=center> <font size=-1>BOOKS</font>" Response.Write " </td>" If CInt(curYear) = CInt(Year(Now)) Then Response.Write "<td align=center><font size=-1> DAYS SINCE " Response.Write "<br>LAST BOOK</font></td>" Response.Write "<td align=center><font size=-1>PAGES<br>" Response.Write " PER DAY </font></td>" Response.Write "<td align=center><font size=-1> " & curYear Response.Write " <br>Est.</font></td>" End If Response.Write "</tr>" & vbCrLf pageMax = 0 do while not RS.EOF pageSum = RS("SumOfBookPages") if pageSum > pageMax then pageMax = pageSum Response.Write "<tr><td> <a href=""readers.asp?Reader=" Response.Write RS("ReaderID") & "&Year=" & curYear Response.Write Chr(34) & ">" & RS("ReaderName") Response.Write "</a> </td>" Response.Write "<td align=right> " & pageSum Response.Write " </td>" Response.Write "<td><img src=" & Chr(34) & "img\BAR.gif" & Chr(34) & " width=" Response.Write Int(100 * pageSum/pageMax) Response.Write " height=12 alt=" & Chr(34) Response.Write pageSum & Chr(34) & "></td>" Response.Write "<td align=right> " & RS("BookCount") Response.Write " </td>" If CInt(curYear) = CInt(Year(Now)) Then Response.Write "<td align=right> " Response.Write Int(Now - RS("MaxOfBookDate")) Response.Write " </td>" perDay = (CInt(RS("SumOfBookPages"))/(Now - CDate("01/01/" & curYear))) Response.Write "<td align=right> " & Int(perDay) Response.Write " </td>" Response.Write "<td align=right> " & Int(perDay * 365) Response.Write " </td>" End If Response.Write "</tr>" & vbCrLf RS.MoveNext Loop Response.Write "</table>" & vbCrLf & "<br>" End If 'add links to previous and following years Response.Write "<font size=-2><b>go to</b><br>" Response.Write "<a href=""books.asp?Year=" & curYear-1 & Chr(34) & ">" Response.Write curYear-1 & "</a> " Response.Write "<a href=""books.asp?Year=" & curYear+1 & Chr(34) & ">" Response.Write curYear+1 & "</a></font><br>" 'Show table of most recent additions sql = "SELECT tblBooks.BookDate, tblBooks.BookAuthor, " sql = sql & "tblBooks.BookComment, tblBooks.BookTitle, " sql = sql & "tblBooks.BookPages, tblReaders.ReaderName, " sql = sql & "tblReaders.ReaderID FROM tblReaders INNER JOIN " sql = sql & "tblBooks ON tblReaders.ReaderID = tblBooks.ReaderID " sql = sql & "ORDER BY tblBooks.BookDate DESC, tblBooks.BookID DESC" set RS = Conn.Execute(sql) if RS.EOF Then Response.Write "<br>No books have been entered into the database." Else Response.Write "<br><b>recent additions</b>" Response.Write "<table border=0 cellpadding=0 cellspacing=0><tr>" Response.Write "<td align=center> <font size=-1>READER</font>" Response.Write " </td>" Response.Write "<td align=center> <font size=-1>PAGES</font>" Response.Write " </td>" Response.Write "<td align=center> <font size=-1>AUTHOR</font>" Response.Write " </td>" Response.Write "<td align=center> <font size=-1>TITLE</font>" Response.Write " </td>" Response.Write "<td align=center> <font size=-1>DATE</font>" Response.Write " </td></tr>" & vbCrLf bookCount = 0 do while not RS.EOF and bookCount < 10 bookCount = bookCount + 1 strCol = Mid("90A0E0AFBFFF", 6 * (bookCount Mod 2) + 1, 6) Response.Write "<tr><td bgcolor=""" & strCol & """> " Response.Write RS("ReaderName") & " </td>" Response.Write "<td align=right bgcolor=""" & strCol & """> " Response.Write RS("BookPages") & " </td>" Response.Write "<td bgcolor=""" & strCol & """> " Response.Write RS("BookAuthor") & " </td>" Response.Write "<td bgcolor=""" & strCol & """> " strCmt = Trim(RS("BookComment")) Response.Write "<img src=""img/" If strCmt = "" Then Response.Write "NO" Response.Write "QUOTE.gif"" alt=""" Response.Write QuoteToTic(strCmt) & """> " Response.Write RS("BookTitle") & " </td>" Response.Write "<td align=right bgcolor=""" & strCol & """> " Response.Write RS("BookDate") & " </td></tr>" & vbCrLf RS.MoveNext Loop Response.Write "</table>" End If Response.Write "<br><br><a href=""add.asp"">Add</a> a book.<br>" If Session("UserLoggedIn") = "true" Then 'Show button to logout Response.Write "<form name=logoutform action=books.asp method=post>" Response.Write "<input type=hidden name=login value=false>" Response.Write "<input type=submit value="" Logout " & Chr(34) &">" Response.Write "</form>" End If Conn.Close Set Conn = Nothing Response.Write "<br><br><font size=-3>" Response.Write "<A href=""whatsnew.htm"">what's new</a> " Response.Write "<a href=""bookssrc.htm"">source</a></font>" Response.Write "</center></tr></table></body></html>" Function QuoteToTic(strIn) QuoteToTic = Replace(strIn, Chr(34), "''") End Function %>