OpenTestStatisticsResource.java

/*******************************************************************************
 * Copyright 2016 Thomas Schreiber
 *
 * 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.opendata;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Objects;

import org.json.JSONArray;
import org.json.JSONObject;
import org.restlet.data.Form;
import org.restlet.data.Status;
import org.restlet.resource.Get;

import com.google.gson.GsonBuilder;
import com.google.gson.JsonObject;

import at.alladin.rmbt.shared.cache.CacheHelper;
import at.alladin.rmbt.statisticServer.ServerResource;

public class OpenTestStatisticsResource extends ServerResource {
    private static final int CACHE_EXP = 300;
    private final CacheHelper cache = CacheHelper.getInstance();
    
    //Intervals returned by the resource. Have to be ordered ascending
    private final int[] intervalsMins = {
    		5,
    		30,
    		60,
    		12*60, //12h
    		60*24, //24h
    		60*24*7 //7d
    		};

	@Get("json")
	public String request(final String entity) {
		addAllowOrigin();
		final Form getParameters = getRequest().getResourceRef()
				.getQueryAsForm();
		final QueryParser qp = new QueryParser();

		// set transformator for time to allow for broader caching (a few
		// minutes)
		qp.registerSingleParameterTransformator("time",
				new QueryParser.SingleParameterTransformator() {
					private final static int FEW_MINUTES = 60 * 1 * 1000;

					@Override
					public void transform(QueryParser.SingleParameter param) {
						// round to 1h
						long timestamp = Long.parseLong(param.getValue());
						timestamp = timestamp - (timestamp % FEW_MINUTES);
						param.setValue(Long.toString(timestamp));
					}
				});

		qp.parseQuery(getParameters);

		// try cache first
		String cacheString = (String) cache.get("opentest-statistics-"
				+ qp.hashCode());
		if (cacheString != null) {
			// System.out.println("cache hit");
			return cacheString;
		}

		try {
			String json = this.getStatistics(qp);
			// put in cache
			cache.set("opentest-statistics-" + qp.hashCode(), CACHE_EXP, json);

			return json;
		} catch (SQLException e) {
			e.printStackTrace();
			setStatus(Status.SERVER_ERROR_INTERNAL);
			return "\"error\":\"internal error\"";
		}
	}

	/**
	 * Get JSON array containing conducted tests at different time intervals
	 * 
	 * @param qp
	 * @return
	 * @throws SQLException
	 */
	private String getStatistics(QueryParser qp) throws SQLException {
		// build the sql query
		StringBuilder selectStatement = new StringBuilder();
		selectStatement.append("SELECT CASE ");
		for (int i = 0; i < intervalsMins.length; i++) {
			selectStatement
					.append(String
							.format("WHEN (time > (current_timestamp - interval '%d minutes')) THEN '%d' ",
									intervalsMins[i], i));
		}
		selectStatement.append(" END as ident ");

		// concatenate sql with the CASE statement
		String query = "SELECT ident, count(ident) as cnt FROM ("
				+ selectStatement.toString() + "FROM test t "
				+ "LEFT JOIN network_type nt ON nt.uid=t.network_type"
				+ " LEFT JOIN device_map adm ON adm.codename=t.model"
				+ " LEFT JOIN test_server ts ON ts.uid=t.server_id"
				+ " LEFT JOIN provider prov ON provider_id = prov.uid "
				+ " LEFT JOIN provider mprov ON mobile_provider_id = mprov.uid"
				+ " WHERE" + " t.deleted = false"
				+ " AND status = 'FINISHED' " + qp.getWhereClause("AND")
				+ "AND time > (current_timestamp - interval '"
				+ intervalsMins[intervalsMins.length - 1] + " minutes'" + ")"
				+ ") a GROUP BY ident ORDER BY ident ASC;";

		PreparedStatement stmt = conn.prepareStatement(query);
		qp.fillInWhereClause(stmt, 1);
		ResultSet rs = stmt.executeQuery();

		
		HashMap<Integer, Long > map = new HashMap<>();
		for (int i=0;i<intervalsMins.length;i++) {
			map.put(intervalsMins[i], 0l);
		}
		
		long tests = 0;
		while (rs.next()) {
			int interval = intervalsMins[rs.getInt("ident")];
			tests += rs.getLong("cnt");
			map.put(interval, tests);
		}
		
		//now, change keys to different time measurements depending on #mins for better readability
		HashMap<String, Long > retMap = new HashMap<>();
		for (Integer interval : map.keySet()) {
			long t = map.get(interval);
			
			//use different time measurements depending on #mins
			if (interval%(60*24) == 0 && (interval/(60*24))>1) {
				retMap.put((interval/(60*24)) + "d", t);				
			}
			else if ((interval%60) == 0 && (interval/60)>1) {
				retMap.put((interval/60) + "h", t);
			}
			else {
				retMap.put(interval + "min", t);
			}
		}

		return new GsonBuilder().create().toJson(retMap);
	}
}