SourceForge Omphaloskepsis II

Following up on that threat to graph things; here’s a JSP to generate a timeseries graph of file downloads and CVS reads/writes using the Google Annotated Timeline chart API.
You could adapt this to display multiple projects, add annotations for various milestones / releases or to use the SVN statistics if required.
The output looks a bit like this:
Daily SF Download/CVS stats for jvix
SourceforgeStats.jsp
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" errorPage="misc/errorPage.jsp" import="java.util.*,java.util.Date,java.text.*,java.sql.*,javax.sql.*,org.springframework.jdbc.core.*,org.springframework.jdbc.datasource.*" %> <% String JDBC_CONNECTION_STRING = "jdbc:mysql://filament/stats?zeroDateTimeBehavior=convertToNull&autoReconnect=true"; String JDBC_USERNAME = "stats"; String JDBC_PASSWORD = "stats"; int PROJECT_ID = 1; // jvix Connection conn = DriverManager.getConnection( JDBC_CONNECTION_STRING, JDBC_USERNAME, JDBC_PASSWORD); DataSource ds = new SingleConnectionDataSource(conn, true); JdbcTemplate jt = new JdbcTemplate(ds); // MySQL pivot alternatives: http://stackoverflow.com/questions/6605604/mysql-pivot-query-results-with-group-by List result = jt.queryForList( "SELECT date, " + " SUM(IF(statType = 100, value, 0)) AS 'download', " + " SUM(IF(statType = 200 OR statType=201, value, 0)) AS 'cvsRead', " + // 200=dev, 201=anon reads " SUM(IF(statType = 202, value, 0)) AS 'cvsWrite' " + " FROM daily" + " WHERE projectId = " + PROJECT_ID + " GROUP BY date " + " ORDER BY date ASC"); %> <html> <head> <script type='text/javascript' src='http://www.google.com/jsapi'></script> <script type='text/javascript'> google.load('visualization', '1', {'packages':['annotatedtimeline']}); google.setOnLoadCallback(drawChart); function drawChart() { var data = new google.visualization.DataTable(); data.addColumn('date', 'Date'); data.addColumn('number', 'Downloads'); data.addColumn('number', 'CVS reads'); data.addColumn('number', 'CVS writes'); data.addRows([ <% Date d = null; for (int i=0; i<result.size(); i++) { Map row = (Map) result.get(i); d = (Date) row.get("date"); long download = ((Number) row.get("download")).longValue(); long cvsRead = ((Number) row.get("cvsRead")).longValue(); long cvsWrite = ((Number) row.get("cvsWrite")).longValue(); out.print("[new Date(" + d.getTime() + "), " + download + ", " + cvsRead + ", " + cvsWrite + "]"); if (i<result.size()) { out.println(","); }; } %> ]); var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div')); chart.draw(data, { colors: [ "#8888FF", "green", "red" ], displayAnnotations: true, displayZoomButtons: false, zoomEndTime: new Date(<%= d.getTime() %>), zoomStartTime: new Date(<%= 1343313257142L /* d.getTime()-180*24*60*60*1000 */ %>), scaleColumns: [1, 0], scaleType: "allfixed" }); } </script> </head> <body> Daily download / CVS statistics for project <b>jvix</b> <br/><br/> <div id='chart_div' style='width: 700px; height: 240px;'></div> </body> </html> |
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" errorPage="misc/errorPage.jsp" import="java.util.*,java.util.Date,java.text.*,java.sql.*,javax.sql.*,org.springframework.jdbc.core.*,org.springframework.jdbc.datasource.*" %> <% String JDBC_CONNECTION_STRING = "jdbc:mysql://filament/stats?zeroDateTimeBehavior=convertToNull&autoReconnect=true"; String JDBC_USERNAME = "stats"; String JDBC_PASSWORD = "stats"; int PROJECT_ID = 1; // jvix Connection conn = DriverManager.getConnection( JDBC_CONNECTION_STRING, JDBC_USERNAME, JDBC_PASSWORD); DataSource ds = new SingleConnectionDataSource(conn, true); JdbcTemplate jt = new JdbcTemplate(ds); // MySQL pivot alternatives: http://stackoverflow.com/questions/6605604/mysql-pivot-query-results-with-group-by List result = jt.queryForList( "SELECT date, " + " SUM(IF(statType = 100, value, 0)) AS 'download', " + " SUM(IF(statType = 200 OR statType=201, value, 0)) AS 'cvsRead', " + // 200=dev, 201=anon reads " SUM(IF(statType = 202, value, 0)) AS 'cvsWrite' " + " FROM daily" + " WHERE projectId = " + PROJECT_ID + " GROUP BY date " + " ORDER BY date ASC"); %> <html> <head> <script type='text/javascript' src='http://www.google.com/jsapi'></script> <script type='text/javascript'> google.load('visualization', '1', {'packages':['annotatedtimeline']}); google.setOnLoadCallback(drawChart); function drawChart() { var data = new google.visualization.DataTable(); data.addColumn('date', 'Date'); data.addColumn('number', 'Downloads'); data.addColumn('number', 'CVS reads'); data.addColumn('number', 'CVS writes'); data.addRows([ <% Date d = null; for (int i=0; i<result.size(); i++) { Map row = (Map) result.get(i); d = (Date) row.get("date"); long download = ((Number) row.get("download")).longValue(); long cvsRead = ((Number) row.get("cvsRead")).longValue(); long cvsWrite = ((Number) row.get("cvsWrite")).longValue(); out.print("[new Date(" + d.getTime() + "), " + download + ", " + cvsRead + ", " + cvsWrite + "]"); if (i<result.size()) { out.println(","); }; } %> ]); var chart = new google.visualization.AnnotatedTimeLine(document.getElementById('chart_div')); chart.draw(data, { colors: [ "#8888FF", "green", "red" ], displayAnnotations: true, displayZoomButtons: false, zoomEndTime: new Date(<%= d.getTime() %>), zoomStartTime: new Date(<%= 1343313257142L /* d.getTime()-180*24*60*60*1000 */ %>), scaleColumns: [1, 0], scaleType: "allfixed" }); } </script> </head> <body> Daily download / CVS statistics for project <b>jvix</b> <br/><br/> <div id='chart_div' style='width: 700px; height: 240px;'></div> </body> </html>
Related Posts
-
Flowcharts-R-Us
1 Comment | Dec 11, 2021 -
Progress Bars
2 Comments | Oct 31, 2013 -
<%= post.title %>
1 Comment | Jul 4, 2016 -
Maven
1 Comment | Jun 30, 2013