import java.io.*; import java.sql.*; import java.text.*; import java.util.*; import javax.servlet.*; import javax.servlet.http.*; public class SeitseVaprat extends HttpServlet { public void doGet(HttpServletRequest kysimus, HttpServletResponse vastus) throws IOException, ServletException { PrintWriter out = vastus.getWriter(); try{ ResourceBundle rb = ResourceBundle.getBundle("LocalStrings",kysimus.getLocale()); vastus.setContentType("text/html"); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection cn=DriverManager.getConnection("jdbc:odbc:Baas1"); Statement st=cn.createStatement(); Statement tt=cn.createStatement(); out.println(""); out.println(""); out.println(" 7 Vaprat edetabel "); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); if(i == 9){ out.println(""); }else{ out.println(" | Lisa laul"); } if(kysimus.getParameter("action")!=null && kysimus.getParameter("action").equals("lisa")){ out.println(""); out.println(""); out.println(""); out.println(""); out.println(""); } out.println("

7 Vaprat

"); out.println(""); out.println(""); String paring4 = "DELETE FROM abi"; st.executeUpdate(paring4); String paring1 = "INSERT INTO abi select l.id, l.laul, l.esitaja, l.hinnatud, AVG(h.hinne) AS keskmine FROM vaprad AS l LEFT JOIN vaprate_hinded AS h ON l.id=h.laulu_id GROUP BY l.id, l.laul, l.esitaja, l.hinnatud"; st.executeUpdate(paring1); String paring2 = "SELECT * FROM abi ORDER BY keskmine DESC, esitaja ASC;"; ResultSet rs=st.executeQuery(paring2); String taust = "white"; int i = 0; while(rs.next()){ i = i + 1; String id = rs.getString("id"); String laul = rs.getString("laul"); String esitaja = rs.getString("esitaja"); int hinnatud = rs.getInt("hinnatud"); double keskmine = rs.getDouble("keskmine"); keskmine = keskmine * 100; keskmine = (int)keskmine; keskmine = keskmine/100; String paring3 = "SELECT COUNT(*) as hindeid from vaprate_hinded WHERE laulu_id = " + id + ";"; ResultSet hin=tt.executeQuery(paring3); hin.next(); int hindeid = hin.getInt("hindeid"); if(i >= 8 && hindeid != 0){ taust = "red"; } else if(hindeid == 0){ taust = "yellow"; } else{ taust = "white"; } out.println(""); if(hinnatud == 0){ out.println(""); }else if(hinnatud == 1){ out.println(""); } out.println(""); } cn.close(); out.println("
KeskmineHääliLaulu nimiEsitajaHinda lauluStatistika
" + keskmine + "" + hindeid + "" + laul + "" + esitaja + "1|2|3|4|51|2|3|4|5Vaata...
"); out.println("
Uus hindamine"); }else{ out.println("
Uus hindamine"); } if(i >= 7 && i <9){ out.println(" | Lisa laul
Laulu nimiEsitaja
"); out.println("
"); out.println(""); out.println(""); }catch (Exception viga){viga.printStackTrace(out);} } }