<% Option Explicit Dim curYear, Conn, sConnString, movieCount, viewerCount, RS Dim pageSum, pageMax, strCol, perDay, strCmt, strDate, login Dim sql, a, strShortCmt Response.Write "<html><head><title>Movie List</title></head>" Response.Write "<body bgcolor=""A0F0B0"" link=""000000"" vlink=""404040"">" Response.Write vbCrLf & vbCrLf & "<!-version 26aug02 sgadd wasoxygen@hotmail.com->" Response.Write vbCrLf & vbCrLf 'title bar Response.Write "<font face=""arial"">" Response.Write "<table width=100% border=0 cellpadding=0 cellspacing=0>" Response.Write "<tr><td align=right><img src=""img\RND_L.gif""></td>" Response.Write "<td width=60% bgcolor=""FFFFFF"" valign=middle>" Response.Write "<br><center><font size=6><b>Movie List</b></font><br>" 'navigation bar %> <!-- #include file="include_nav.inc" --> <% Response.Write "</center></td>" Response.Write "<td align=left><img src=""img\RND_R.gif""></td></tr>" Response.Write "<tr><td colspan=3><center>" 'main section Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/wasoxygen/db/books.mdb") & ";" 'set current year 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 'promo '/promo Response.Write "<font face=""arial""><br>" If CInt(curYear) = CInt(Year(Now)) Then Response.Write "<table border=0><tr><td width=100 align=left>" Response.Write "<font size=-1>today is<br>" Response.Write Day(Now) & " " & MonthName(Month(Now)) & " " Response.Write "</font></td><td>" End If Response.Write "<b><font size=+3> " & curYear & " </font></b>" If CInt(curYear) = CInt(Year(Now)) Then Response.Write "</td><td width=100 align=right><font size=-1>" strDate = "12/31/" & curYear - 1 Response.Write "day " & Int(Now - CDate(strDate)) & "<br>" strDate = "01/01/" & curYear + 1 Response.Write Int(CDate(strDate) - Now) & " to go</td></tr></table>" End If Response.Write "<br>" 'show years available sql = "SELECT DISTINCT Year(movieDate) AS Year " sql = sql & "FROM tblMovies " sql = sql & " ORDER BY Year(movieDate) ASC" set RS = Conn.Execute(sql) Response.Write "<font size=-2><b> - " Do While Not RS.EOF If CStr(RS("Year")) = CStr(curYear) Then Response.Write curYear & " - " Else Response.Write "<a href=""movies.asp?Year=" Response.Write RS("Year") & Chr(34) Response.Write ">" & RS("Year") & "</a> - " & vbCrLf End If RS.MoveNext Loop Response.Write "<br><br>" 'Show Leaderboard: table of readers ordered by total pages sql = "SELECT tblReaders.ReaderName, tblReaders.ReaderID, " sql = sql & "tblReaders.ReaderInfo, " sql = sql & "Max(tblMovies.movieDate) AS MaxOfMovieDate, " sql = sql & "Count(tblMovies.movieID) AS MovieCount " sql = sql & "FROM tblReaders INNER JOIN tblMovies " sql = sql & "ON tblReaders.ReaderID = tblMovies.ReaderID " sql = sql & "WHERE tblMovies.movieDate > #12/31/" & curYear-1 & "# " sql = sql & "AND tblMovies.movieDate < #01/01/" & curYear+1 & "# " sql = sql & "GROUP BY tblReaders.ReaderName, tblReaders.ReaderID, " sql = sql & "tblReaders.ReaderInfo ORDER BY Count(tblMovies.movieID) DESC" set RS = Conn.Execute(sql) If RS.EOF Then Response.Write "<p>No movies have been entered for this year.<p>" Else Response.Write "<table border=0 cellpadding=0 cellspacing=0>" Response.Write "<tr><td align=right></td>" Response.Write "<td align=center> <font size=-1>Viewer</font>" Response.Write " </td>" Response.Write "<td align=right> <font size=-1>Movies</font>" Response.Write " </td><td></td>" If CInt(curYear) = CInt(Year(Now)) Then Response.Write "<td align=right><font size=-1> days since" Response.Write "<br>last movie</font></td>" End If Response.Write "</tr>" & vbCrLf pageMax = 0 viewerCount = 0 Do while not RS.EOF viewerCount = viewerCount + 1 ''' pageSum = RS("SumOfBookPages") 'use pageSum and pageMax to calculate bargraph widths ''' if pageSum > pageMax then pageMax = pageSum Response.Write "<tr><td align=right><b><font size=-1> " & viewerCount Response.Write ".</font> </b></td>" Response.Write "<td><a href=""viewers.asp?Viewer=" Response.Write RS("ReaderID") & "&Year=" & curYear Response.Write """ title=""" & QuoteToTic(RS("ReaderInfo")) & """>" Response.Write RS("ReaderName") & "</a> </td>" Response.Write "<td align=right> " & movieCount 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("MovieCount") Response.Write " </td>" If CInt(curYear) = CInt(Year(Now)) Then Response.Write "<td align=right> " Response.Write Int(Now - RS("MaxOfMovieDate")) 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 'Show table of most recent additions sql = "SELECT tblMovies.MovieDate, tblMovies.MovieRating, " sql = sql & "tblMovies.MovieComment, tblMovies.MovieTitle, " sql = sql & "tblReaders.ReaderName, " sql = sql & "tblReaders.ReaderID FROM tblReaders INNER JOIN " sql = sql & "tblMovies ON tblReaders.ReaderID = tblMovies.ReaderID " sql = sql & "ORDER BY tblMovies.MovieDate DESC, tblMovies.MovieID DESC" set RS = Conn.Execute(sql) If RS.EOF Then Response.Write "<br>No movies have been entered into the database." Else Response.Write "<br><font size=+1><b>recent additions</b></font>" Response.Write " " '<a href=""recentmovies.asp""><font size=-1>" 'Response.Write "<i>more</i></font></a>" Response.Write "<table border=0 cellpadding=0 cellspacing=0><tr>" Response.Write "<td align=center> <font size=-1>VIEWER</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>" Response.Write "<td align=center> <font size=-1>RATING</font>" Response.Write " </td>" Response.Write "<td align=center> <font size=-1>REVIEW</font>" Response.Write " </td>" Response.Write "<td align=center> " 'link Response.Write " </td></tr>" & vbCrLf movieCount = 0 do while not RS.EOF and movieCount < 10 movieCount = movieCount + 1 strCol = Mid("90E0A0AFFFBF", 6 * (movieCount Mod 2) + 1, 6) Response.Write "<tr><td bgcolor=""" & strCol & """> " Response.Write RS("ReaderName") & " </td>" Response.Write "<td align=left bgcolor=""" & strCol & """> " Response.Write RS("MovieTitle") & " </td>" Response.Write "<td align=right bgcolor=""" & strCol & """> " Response.Write RS("MovieDate") & " </td>" Response.Write "<td align=right bgcolor=""" & strCol & """> " Response.Write RS("MovieRating") & " </td>" Response.Write "<td bgcolor=""" & strCol & """>" strCmt = Trim(RS("MovieComment")) strShortCmt = Mid(strCmt, 1, 40) For a = 1 To 40 If Mid(strCmt, a, 1) = " " Then strShortCmt = Mid(strCmt, 1, a) End If Next Response.Write strShortCmt & "... </td>" Response.Write "<td bgcolor=""" & strCol & """>" Response.Write "<a href=""recentmovies.asp"">" Response.Write "<img src=""img/" If strCmt = "" Then Response.Write "NO" Response.Write "QUOTE.gif"" border=0 alt=""" Response.Write QuoteToTic(strCmt) & """></a></td></tr>" & vbCrLf RS.MoveNext Loop Response.Write "</table>" End If Response.Write "<br><br><font size=3>" Response.Write "<a href=""addmovie.asp"">Add</a> a movie.<br>" If Session("UserLoggedIn") = "true" Then 'Show button to logout Response.Write "<form name=logoutform action=movies.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=""moviessrc.htm"">source</a></font>" Response.Write "<br><br><form name=form5 action=login.asp method=post>" Response.Write "<input type=hidden name=login value=""newuser"">" Response.Write "First time here?<br>" Response.Write "<input type=submit value="" Join! ""></form>" Response.Write "</center></tr></table></body></html>" Function QuoteToTic(strIn) QuoteToTic = Replace(strIn, Chr(34), "''") End Function %>