import java.awt.*; import java.io.*; import java.sql.*; import java.text.*; import java.util.*; import javax.servlet.*; import java.awt.image.*; import javax.servlet.http.*; import com.sun.image.codec.jpeg.*; public class VapraStatistika extends HttpServlet { public void doGet(HttpServletRequest kysimus, HttpServletResponse vastus) throws IOException, ServletException { try{ ResourceBundle rb = ResourceBundle.getBundle("LocalStrings",kysimus.getLocale()); vastus.setContentType("text/html"); PrintWriter out = vastus.getWriter(); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection cn=DriverManager.getConnection("jdbc:odbc:Baas1"); Statement st=cn.createStatement(); out.println(""); out.println(""); out.println(" Statistika "); out.println(""); out.println(""); out.println(""); out.println(""); String id = kysimus.getParameter("id"); String paring = "SELECT * FROM vaprad WHERE id=" + id + ";"; ResultSet rs=st.executeQuery(paring); rs.next(); String laul = rs.getString("laul"); String paring1 = "SELECT COUNT(*) as viisi FROM vaprate_hinded WHERE hinne=5 AND laulu_id=" + id + ";"; ResultSet viis=st.executeQuery(paring1); viis.next(); int viisi = viis.getInt("viisi"); String paring2 = "SELECT COUNT(*) as nelju FROM vaprate_hinded WHERE hinne=4 AND laulu_id=" + id + ";"; ResultSet neli=st.executeQuery(paring2); neli.next(); int neljasi = neli.getInt("nelju"); String paring3 = "SELECT COUNT(*) as kolmi FROM vaprate_hinded WHERE hinne=3 AND laulu_id=" + id + ";"; ResultSet kolm=st.executeQuery(paring3); kolm.next(); int kolmesi = kolm.getInt("kolmi"); String paring4 = "SELECT COUNT(*) as kahtesi FROM vaprate_hinded WHERE hinne=2 AND laulu_id=" + id + ";"; ResultSet kaks=st.executeQuery(paring4); kaks.next(); int kahtesi = kaks.getInt("kahtesi"); String paring5 = "SELECT COUNT(*) as yhtesi FROM vaprate_hinded WHERE hinne=1 AND laulu_id=" + id + ";"; ResultSet yks=st.executeQuery(paring5); yks.next(); int yhtesi = yks.getInt("yhtesi"); String paring6 = "SELECT AVG(hinne) as keskmine from vaprate_hinded WHERE laulu_id = " + id + ";"; ResultSet avg=st.executeQuery(paring6); avg.next(); double keskmine = avg.getDouble("keskmine"); keskmine = keskmine * 100; keskmine = (int)keskmine; keskmine = keskmine / 100; String paring7 = "SELECT COUNT(*) as kokku FROM vaprate_hinded WHERE laulu_id=" + id + ";"; ResultSet sum=st.executeQuery(paring7); sum.next(); int kokku = sum.getInt("kokku"); cn.close(); out.println(""); out.println(""); out.println(""); out.println(""); out.println("

Laulu \"" + laul + "\" hindamise statistika:

"); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println("

Viisi	" + viisi + "
Nelju	" + neljasi + "
Kolmi	" + kolmesi + "
Kahtesi	" + kahtesi + "
Ühtesi	" + yhtesi + "
Kokku:	" + kokku + "

"); out.println("

Keskmine hinne: " + keskmine + "

<< Tagasi edetabelisse

| Vaata graafiliselt >>

"); out.println(""); out.println(""); }catch (Exception viga){viga.printStackTrace();} } }