Java tutorial
/******************************************************************************* * Copyright 2013-2014 alladin-IT GmbH * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. ******************************************************************************/ package at.alladin.rmbt.statisticServer; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.Calendar; import java.util.GregorianCalendar; import java.util.HashMap; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; import org.restlet.data.Form; import org.restlet.resource.Get; //Statistics for internal purpose //breaks the mvvm-pattern public class UsageJSONResource extends ServerResource { @Get("json") public String request(final String entity) { JSONObject result = new JSONObject(); int month = -1; int year = -1; try { //parameters final Form getParameters = getRequest().getResourceRef().getQueryAsForm(); try { if (getParameters.getNames().contains("month")) { month = Integer.parseInt(getParameters.getFirstValue("month")); if (month > 11 || month < 0) { throw new NumberFormatException(); } } if (getParameters.getNames().contains("year")) { year = Integer.parseInt(getParameters.getFirstValue("year")); if (year < 0) { throw new NumberFormatException(); } } } catch (NumberFormatException e) { return "invalid parameters"; } Calendar now = new GregorianCalendar(); Calendar monthBegin = new GregorianCalendar((year > 0) ? year : now.get(Calendar.YEAR), (month >= 0) ? month : now.get(Calendar.MONTH), 1); Calendar monthEnd = new GregorianCalendar((year > 0) ? year : now.get(Calendar.YEAR), (month >= 0) ? month : now.get(Calendar.MONTH), monthBegin.getActualMaximum(Calendar.DAY_OF_MONTH)); //if now -> do not use the last day if (month == now.get(Calendar.MONTH) && year == now.get(Calendar.YEAR)) { monthEnd = now; monthEnd.add(Calendar.DATE, -1); } JSONObject platforms = getPlatforms(new Timestamp(monthBegin.getTimeInMillis()), new Timestamp(monthEnd.getTimeInMillis())); JSONObject usage = getClassicUsage(new Timestamp(monthBegin.getTimeInMillis()), new Timestamp(monthEnd.getTimeInMillis())); JSONObject versionsIOS = getVersions("iOS", new Timestamp(monthBegin.getTimeInMillis()), new Timestamp(monthEnd.getTimeInMillis())); JSONObject versionsAndroid = getVersions("Android", new Timestamp(monthBegin.getTimeInMillis()), new Timestamp(monthEnd.getTimeInMillis())); JSONObject versionsApplet = getVersions("Applet", new Timestamp(monthBegin.getTimeInMillis()), new Timestamp(monthEnd.getTimeInMillis())); JSONObject networkGroupNames = getNetworkGroupName(new Timestamp(monthBegin.getTimeInMillis()), new Timestamp(monthEnd.getTimeInMillis())); JSONObject networkGroupTypes = getNetworkGroupType(new Timestamp(monthBegin.getTimeInMillis()), new Timestamp(monthEnd.getTimeInMillis())); result.put("platforms", platforms); result.put("usage", usage); result.put("versions_ios", versionsIOS); result.put("versions_android", versionsAndroid); result.put("versions_applet", versionsApplet); result.put("network_group_names", networkGroupNames); result.put("network_group_types", networkGroupTypes); } catch (SQLException e) { e.printStackTrace(); } catch (JSONException e) { // TODO Auto-generated catch block e.printStackTrace(); } return result.toString(); } private JSONObject getClassicUsage(Timestamp begin, Timestamp end) throws SQLException, JSONException { JSONObject returnObj = new JSONObject(); JSONArray sums = new JSONArray(); JSONArray values = new JSONArray(); returnObj.put("sums", sums); returnObj.put("values", values); HashMap<String, Long> fieldSums = new HashMap<>(); fieldSums.put("tests", new Long(0)); fieldSums.put("clients", new Long(0)); fieldSums.put("ips", new Long(0)); PreparedStatement ps; ResultSet rs; final String select = "date_trunc('day', time) _day, count(uid) count_tests, count(DISTINCT client_id) count_clients, count(DISTINCT client_public_ip) count_ips"; final String where = "status='FINISHED' AND deleted=false"; final String sql = "SELECT " + select + " FROM test WHERE " + where + " AND time >= ? AND time < ? GROUP BY _day ORDER BY _day ASC"; ps = conn.prepareStatement(sql); ps.setTimestamp(1, begin); ps.setTimestamp(2, end); rs = ps.executeQuery(); while (rs.next()) { JSONObject entry = new JSONObject(); entry.put("day", rs.getDate("_day").getTime()); JSONArray currentEntryValues = new JSONArray(); entry.put("values", currentEntryValues); JSONObject jTests = new JSONObject(); jTests.put("field", "tests"); jTests.put("value", rs.getLong("count_tests")); currentEntryValues.put(jTests); JSONObject jClients = new JSONObject(); jClients.put("field", "clients"); jClients.put("value", rs.getLong("count_clients")); currentEntryValues.put(jClients); JSONObject jIPs = new JSONObject(); jIPs.put("field", "ips"); jIPs.put("value", rs.getLong("count_ips")); currentEntryValues.put(jIPs); fieldSums.put("tests", fieldSums.get("tests") + rs.getLong("count_tests")); fieldSums.put("clients", fieldSums.get("clients") + rs.getLong("count_clients")); fieldSums.put("ips", fieldSums.get("ips") + rs.getLong("count_ips")); //get some structure in there values.put(entry); } rs.close(); ps.close(); //add field sums for (String field : fieldSums.keySet()) { JSONObject obj = new JSONObject(); obj.put("field", field); obj.put("sum", fieldSums.get(field)); sums.put(obj); } return returnObj; } /** * Returns the statistics for used platforms for a specific timespan [begin, end) * @param begin select all tests with time >= begin * @param end select all tests with time < end * @return the structurized JSON object * @throws SQLException * @throws JSONException */ private JSONObject getPlatforms(Timestamp begin, Timestamp end) throws SQLException, JSONException { JSONObject returnObj = new JSONObject(); JSONArray sums = new JSONArray(); JSONArray values = new JSONArray(); returnObj.put("sums", sums); returnObj.put("values", values); HashMap<String, Long> fieldSums = new HashMap<>(); PreparedStatement ps; ResultSet rs; final String sql = "SELECT date_trunc('day', time) _day, COALESCE(plattform,'null') platform, count(plattform) count_platform" + " FROM test" + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ? " + " GROUP BY _day, plattform" + " HAVING count(plattform) > 0" + "ORDER BY _day ASC"; ps = conn.prepareStatement(sql); ps.setTimestamp(1, begin); ps.setTimestamp(2, end); rs = ps.executeQuery(); //one array-item for each day long currentTime = -1; JSONObject currentEntry = null; JSONArray currentEntryValues = null; while (rs.next()) { //new item, of a new day is reached long newTime = rs.getDate("_day").getTime(); if (currentTime != newTime) { currentTime = newTime; currentEntry = new JSONObject(); currentEntryValues = new JSONArray(); currentEntry.put("day", rs.getDate("_day").getTime()); currentEntry.put("values", currentEntryValues); values.put(currentEntry); } //disable null-values String platform = rs.getString("platform"); long count = rs.getLong("count_platform"); if (platform.isEmpty()) { platform = "empty"; } //add value to sum if (!fieldSums.containsKey(platform)) { fieldSums.put(platform, new Long(0)); } fieldSums.put(platform, fieldSums.get(platform) + count); JSONObject current = new JSONObject(); current.put("field", platform); current.put("value", count); currentEntryValues.put(current); } rs.close(); ps.close(); //add field sums for (String field : fieldSums.keySet()) { JSONObject obj = new JSONObject(); obj.put("field", field); obj.put("sum", fieldSums.get(field)); sums.put(obj); } return returnObj; } /** * Returns the statistics for used versions for a specific timespan [begin, end) * @param begin select all tests with time >= begin * @param end select all tests with time < end * @return the structurized JSON object * @throws SQLException * @throws JSONException */ private JSONObject getVersions(String platform, Timestamp begin, Timestamp end) throws SQLException, JSONException { JSONObject returnObj = new JSONObject(); JSONArray sums = new JSONArray(); JSONArray values = new JSONArray(); returnObj.put("sums", sums); returnObj.put("values", values); HashMap<String, Long> fieldSums = new HashMap<>(); PreparedStatement ps; ResultSet rs; final String sql = "SELECT date_trunc('day', time) _day, COALESCE(client_software_version,'null') \"version\", count(client_software_version) count_version" + " FROM test" + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ? AND plattform = ?" + " GROUP BY _day, client_software_version " + " HAVING count(client_software_version) > 0 " + " ORDER BY _day ASC"; ps = conn.prepareStatement(sql); ps.setTimestamp(1, begin); ps.setTimestamp(2, end); ps.setString(3, platform); rs = ps.executeQuery(); //one array-item for each day long currentTime = -1; JSONObject currentEntry = null; JSONArray currentEntryValues = null; while (rs.next()) { //new item, of a new day is reached long newTime = rs.getDate("_day").getTime(); if (currentTime != newTime) { currentTime = newTime; currentEntry = new JSONObject(); currentEntryValues = new JSONArray(); currentEntry.put("day", rs.getDate("_day").getTime()); currentEntry.put("values", currentEntryValues); values.put(currentEntry); } //disable null-values String version = rs.getString("version"); long count = rs.getLong("count_version"); if (version.isEmpty()) { version = "empty"; } //add value to sum if (!fieldSums.containsKey(version)) { fieldSums.put(version, new Long(0)); } fieldSums.put(version, fieldSums.get(version) + count); JSONObject current = new JSONObject(); current.put("field", version); current.put("value", count); currentEntryValues.put(current); } rs.close(); ps.close(); //add field sums for (String field : fieldSums.keySet()) { JSONObject obj = new JSONObject(); obj.put("field", field); obj.put("sum", fieldSums.get(field)); sums.put(obj); } return returnObj; } /** * Returns the statistics for used network name for a specific timespan [begin, end) * @param begin select all tests with time >= begin * @param end select all tests with time < end * @return the structurized JSON object * @throws SQLException * @throws JSONException */ private JSONObject getNetworkGroupName(Timestamp begin, Timestamp end) throws SQLException, JSONException { JSONObject returnObj = new JSONObject(); JSONArray sums = new JSONArray(); JSONArray values = new JSONArray(); returnObj.put("sums", sums); returnObj.put("values", values); HashMap<String, Long> fieldSums = new HashMap<>(); PreparedStatement ps; ResultSet rs; final String sql = "SELECT date_trunc('day', time) _day, COALESCE(network_group_name,'null') \"version\", count(network_group_name) count_group_name" + " FROM test" + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ?" + " GROUP BY _day, network_group_name " + " HAVING count(network_group_name) > 0 " + " ORDER BY _day ASC"; ps = conn.prepareStatement(sql); ps.setTimestamp(1, begin); ps.setTimestamp(2, end); rs = ps.executeQuery(); //one array-item for each day long currentTime = -1; JSONObject currentEntry = null; JSONArray currentEntryValues = null; while (rs.next()) { //new item, of a new day is reached long newTime = rs.getDate("_day").getTime(); if (currentTime != newTime) { currentTime = newTime; currentEntry = new JSONObject(); currentEntryValues = new JSONArray(); currentEntry.put("day", rs.getDate("_day").getTime()); currentEntry.put("values", currentEntryValues); values.put(currentEntry); } //disable null-values String version = rs.getString("version"); long count = rs.getLong("count_group_name"); if (version.isEmpty()) { version = "empty"; } //add value to sum if (!fieldSums.containsKey(version)) { fieldSums.put(version, new Long(0)); } fieldSums.put(version, fieldSums.get(version) + count); JSONObject current = new JSONObject(); current.put("field", version); current.put("value", count); currentEntryValues.put(current); } rs.close(); ps.close(); //add field sums for (String field : fieldSums.keySet()) { JSONObject obj = new JSONObject(); obj.put("field", field); obj.put("sum", fieldSums.get(field)); sums.put(obj); } return returnObj; } /** * Returns the statistics for used network type for a specific timespan [begin, end) * @param begin select all tests with time >= begin * @param end select all tests with time < end * @return the structurized JSON object * @throws SQLException * @throws JSONException */ private JSONObject getNetworkGroupType(Timestamp begin, Timestamp end) throws SQLException, JSONException { JSONObject returnObj = new JSONObject(); JSONArray sums = new JSONArray(); JSONArray values = new JSONArray(); returnObj.put("sums", sums); returnObj.put("values", values); HashMap<String, Long> fieldSums = new HashMap<>(); PreparedStatement ps; ResultSet rs; final String sql = "SELECT date_trunc('day', time) _day, COALESCE(network_group_type,'null') \"version\", count(network_group_type) count_group_type" + " FROM test" + " WHERE status='FINISHED' AND deleted=false AND time >= ? AND time < ?" + " GROUP BY _day, network_group_type " + " HAVING count(network_group_type) > 0 " + " ORDER BY _day ASC"; ps = conn.prepareStatement(sql); ps.setTimestamp(1, begin); ps.setTimestamp(2, end); rs = ps.executeQuery(); //one array-item for each day long currentTime = -1; JSONObject currentEntry = null; JSONArray currentEntryValues = null; while (rs.next()) { //new item, of a new day is reached long newTime = rs.getDate("_day").getTime(); if (currentTime != newTime) { currentTime = newTime; currentEntry = new JSONObject(); currentEntryValues = new JSONArray(); currentEntry.put("day", rs.getDate("_day").getTime()); currentEntry.put("values", currentEntryValues); values.put(currentEntry); } //disable null-values String version = rs.getString("version"); long count = rs.getLong("count_group_type"); if (version.isEmpty()) { version = "empty"; } //add value to sum if (!fieldSums.containsKey(version)) { fieldSums.put(version, new Long(0)); } fieldSums.put(version, fieldSums.get(version) + count); JSONObject current = new JSONObject(); current.put("field", version); current.put("value", count); currentEntryValues.put(current); } rs.close(); ps.close(); //add field sums for (String field : fieldSums.keySet()) { JSONObject obj = new JSONObject(); obj.put("field", field); obj.put("sum", fieldSums.get(field)); sums.put(obj); } return returnObj; } }