--- android/TrainInfoService/src/dk/thoerup/traininfoservice/SavedStatistics.java	2010/07/07 09:12:32	961
+++ android/TrainInfoService/src/dk/thoerup/traininfoservice/SavedStatistics.java	2013/12/06 08:52:00	2091
@@ -15,6 +15,8 @@
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 
+import dk.thoerup.traininfoservice.db.DBConnection;
+
 
 @WebServlet(urlPatterns={"/SavedStatistics"})
 public class SavedStatistics extends HttpServlet {
@@ -24,7 +26,7 @@
 	protected SavedStatBean convertRow(ResultSet res) throws SQLException {
 		SavedStatBean bean = new SavedStatBean();
 
-		bean.statisticsdate = res.getString(1);
+		bean.label = res.getString(1);
 		bean.station = res.getString(2);
 		bean.location = res.getString(3);
 		bean.name = res.getString(4);
@@ -40,6 +42,16 @@
 		
 	}
 	
+	List<SavedStatBean> convertResultset(ResultSet res) throws SQLException{
+		List<SavedStatBean> stats = new ArrayList<SavedStatBean>();
+		while (res.next()) {
+			SavedStatBean bean = convertRow(res);								
+			stats.add(bean);
+			
+		}		
+		return stats;
+	}
+	
 	
 	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 		
@@ -47,18 +59,30 @@
 		try {
 			conn = DBConnection.getConnection();
 			Statement stmt = conn.createStatement();
-			List<SavedStatBean> stats = new ArrayList<SavedStatBean>();
+			
+			
 			
 			
 			String sqlAvg = "SELECT count(*) AS count, round(avg(location+name+favorites),1) AS station, round(avg(location),1) as location, round(avg(name),1) as name, round(avg(favorites),1) as favorites, " +
 			"round(avg(departure),1) as departure, round(avg(depcache),1) as depcache, round(avg(deperror),1) as deperror, " +
 			"round(avg(timetable),1) as timetable, round(avg(timecache),1) as timecache, round(avg(timeerror),1) as timeerror " +
-			"FROM trainstatistics";
+			"FROM trainstatistics " +
+			"WHERE statisticsdate <> now()::date ";
 			
 			ResultSet resAvg = stmt.executeQuery(sqlAvg);
 			resAvg.next();
 			SavedStatBean avgBean = convertRow(resAvg);
-			stats.add(avgBean);
+
+			//////
+			String sql30Avg = "SELECT count(*) AS count, round(avg(location+name+favorites),1) AS station, round(avg(location),1) as location, round(avg(name),1) as name, round(avg(favorites),1) as favorites, " +
+			"round(avg(departure),1) as departure, round(avg(depcache),1) as depcache, round(avg(deperror),1) as deperror, " +
+			"round(avg(timetable),1) as timetable, round(avg(timecache),1) as timecache, round(avg(timeerror),1) as timeerror " +
+			"FROM trainstatistics " +
+			"WHERE statisticsdate < now()::date AND statisticsdate >= (now()::date - interval '30 days')";
+			
+			ResultSet res30Avg = stmt.executeQuery(sql30Avg);
+			res30Avg.next();
+			SavedStatBean avg30Bean = convertRow(res30Avg);			
 			
 			////////////////////////////////////////////////
 			
@@ -67,20 +91,25 @@
 			
 			
 			ResultSet res = stmt.executeQuery(SQL);
+			List<SavedStatBean> stats = convertResultset(res);					
 			
+
+			//////////////////////////////////////////////
 			
-		
-			
-			while (res.next()) {
-				SavedStatBean bean = convertRow(res);								
-				stats.add(bean);
-				
-			}
+			SQL = "select statisticsdate,location+name+favorites AS station,location,name,favorites,departure,depcache,deperror,timetable,timecache,timeerror " +
+			" rom trainstatistics order by departure - deperror desc limit 10";			
+			res = stmt.executeQuery(SQL);
 			
+			List<SavedStatBean> top10 = convertResultset(res); 
+
+
+			conn.close();			
 			
 			
-//			request.setAttribute("avg", avgBean);
+			request.setAttribute("avg", avgBean);
+			request.setAttribute("avg30", avg30Bean);
 			request.setAttribute("stats", stats);
+			request.setAttribute("top10", top10);
 			request.getRequestDispatcher("/savedstatistics.jsp").forward(request, response);