at.alladin.rmbt.statisticServer.OpenTestResource.java Source code

Java tutorial

Introduction

Here is the source code for at.alladin.rmbt.statisticServer.OpenTestResource.java

Source

/*******************************************************************************
 * 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.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.logging.Level;
import java.util.logging.Logger;

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

import at.alladin.rmbt.shared.Classification;

public class OpenTestResource extends ServerResource {
    //maximum of rows sent in one single request
    public final int MAXROWS = 400;

    //all fields that should be displayed in a detailed request
    private final String[] openDataFieldsFull = { "open_test_uuid", //csv 2
            "time", //csv 3
            "cat_technology", //csv 4
            "network_type", //csv 5
            "lat", //csv 6
            "long", //csv 7
            "loc_src", //csv 8
            "loc_accuracy", "public_ip_as_name", "zip_code", //csv 9
            "download_kbit", //csv 10
            "upload_kbit", //csv 11
            "wifi_link_speed", "ping_ms", //csv 12
            "signal_strength", //csv 13
            "lte_rsrp", //csv 29
            "lte_rsrq", "server_name", //csv 14
            "implausible", //csv 28
            "test_duration", //csv 15
            "num_threads_requested", "num_threads", //csv 16
            "num_threads_ul", "platform", //csv 17
            "model", //csv 18
            "model_native", "product", "client_version", //csv 19
            "network_mcc_mnc", //csv 20
            "network_country", "roaming_type", "network_name", //csv 21
            "sim_mcc_mnc", //csv 22           
            "sim_country", "provider_name", "connection", //csv 23
            "asn", //csv 24
            "ip_anonym", //csv 25
            "ndt_download_kbit", //csv 26
            "ndt_upload_kbit", //csv 27
            "country_geoip", "country_location", "country_asn", "region", "municipality", "settlement", "whitespot",
            "bytes_download", "bytes_upload", "test_if_bytes_download", "test_if_bytes_upload",
            "testdl_if_bytes_download", "testdl_if_bytes_upload", "testul_if_bytes_download",
            "testul_if_bytes_upload", "duration_download_ms", "duration_upload_ms", "time_dl_ms", "time_ul_ms" };

    //all fields that are numbers (and are formatted as numbers in json)
    private final HashSet<String> openDataNumberFields = new HashSet<>(Arrays.asList(new String[] { "time", "lat",
            "long", "loc_accuracy", "zip_code", "download_kbit", "upload_kbit", "ping_ms", "signal_strength",
            "lte_rsrp", "lte_rsrq", "test_duration", "num_threads", "ndt_download_kbit", "ndt_upload_kbit", "asn",
            "bytes_download", "bytes_upload", "test_if_bytes_download", "test_if_bytes_upload",
            "testdl_if_bytes_download", "testdl_if_bytes_upload", "testul_if_bytes_download",
            "testul_if_bytes_upload", "duration_download_ms", "duration_upload_ms", "time_dl_ms", "time_ul_ms",
            "roaming_type", "num_threads_ul", "num_threads_requested" }));

    //all fields that are boolean
    private final HashSet<String> openDataBooleanFields = new HashSet<>(
            Arrays.asList(new String[] { "implausible" }));

    @Get("json")
    public String request(final String entity) {
        addAllowOrigin();

        //routing should be in a way in which open_test_uuid is always set
        String openUUID = getRequest().getAttributes().get("open_test_uuid").toString();
        return getSingleOpenTest(openUUID);

    }

    /**
     * Gets the JSON-Representation of all open-data-fields for one specific
     * open-test-uuid
     * @param openTestUUID
     * @return the json-string
     * 
     * 
     * columns in csv ("open data")
     * 1:open_uuid,
     * 2:open_test_uuid,
     * 3:time,
     * 4:cat_technology,
     * 5:network_type,
     * 6:lat,
     * 7:long,
     * 8:loc_src,
     * 9:zip_code,
     * 10:download_kbit,
     * 11:upload_kbit,
     * 12:ping_ms,
     * 13:signal_strength,
     * 14:server_name,
     * 15:test_duration,
     * 16:num_threads,
     * 17:platform,
     * 18:model,
     * 19:client_version,
     * 20:network_mcc_mnc,
     * 21:network_name,
     * 22:sim_mcc_mnc,
     * 23:connection,
     * 24:asn,
     * 25:ip_anonym,
     * 26:ndt_download_kbit,
     * 27:ndt_upload_kbit,
     * 28:implausible,
     * 29:lte_rsrp
     * 
     * 
     * Columns in test table
     *   uid (internal)
     *   uuid (private)
     *   client_id
     *   client_version
     *   client_name
     *   client_language (private)
     *   token (private, obsolete)
     *   server_id
     *   port
     *   use_ssl *
     *   time
     *   speed_upload
     *   speed_download
     *   ping_shortest
     *   encryption *
     *   client_public_ip (private)
     *   plattform 
     *   os_version (internal)
     *   api_level (internal)
     *   device
     *   model
     *   product
     *   phone_type (internal)
     *   data_state (internal)
     *   network_country (internal)
     *   network_operator
     *   network_operator_name
     *   network_sim_country (internal)
     *   network_sim_operator
     *   network_sim_operator_name
     *   wifi_ssid (private)
     *   wifi_bssid (private)
     *   wifi_network_id (private)
     *   duration
     *   num_threads
     *   status
     *   timezone (private)
     *   bytes_download
     *   bytes_upload
     *   nsec_download
     *   nsec_upload
     *   server_ip
     *   client_software_version
     *   geo_lat
     *   geo_long
     *   network_type
     *   location
     *   signal_strength
     *   software_revision
     *   client_test_counter
     *   nat_type
     *   client_previous_test_status
     *   public_ip_asn
     *   speed_upload_log
     *   speed_download_log
     *   total_bytes_download
     *   total_bytes_upload
     *   wifi_link_speed
     *   public_ip_rdns
     *   public_ip_as_name
     *   test_slot
     *   provider_id
     *   network_is_roaming (internal)
     *   ping_shortest_log
     *   run_ndt (internal)
     *   num_threads_requested
     *   client_public_ip_anonymized
     *   zip_code
     *   geo_provider
     *   geo_accuracy
     *   deleted (internal)
     *   comment (internal)
     *   open_uuid
     *   client_time (internal)
     *   zip_code_geo (internal)
     *   mobile_provider_id
     *   roaming_type
     *   open_test_uuid
     *   country_asn
     *   country_location
     *   test_if_bytes_download
     *   test_if_bytes_upload
     *   implausible
     *   testdl_if_bytes_download
     *   testdl_if_bytes_upload
     *   testul_if_bytes_download
     *   testul_if_bytes_upload
     *   country_geoip
     *   location_max_distance
     *   location_max_distance_gps
     *   network_group_name
     *   network_group_type
     *   time_dl_ns
     *   time_ul_ns
     *   num_threads_ul 
     *   lte_rsrp
     *   lte_rsrq
     *   mobile_network_id
     *   mobile_sim_id
     *   dist_prev
     *   speed_prev
     *   tag
     *   ping_median
     *   ping_median_log
     *   client_ip_local_type (private)
     *
     *   private: visible to user only (not public)
     *   internal: not visible (neither user nor public)
     * 
     */

    private String getSingleOpenTest(String openTestUUID) {
        final String sql = "SELECT t.uid as test_uid, " + " ('O' || t.open_test_uuid) open_test_uuid," + //csv  open_test_uuid, UUID prefixed with 'O'
                " to_char(t.time AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS') \"time\"," + " t.time full_time," + //csv: 3:time server time-stamp of start of measurement
                " t.client_time client_time," + //(internal) client time-stamp of start of measure
                " t.network_group_name cat_technology," + //csv 4:cat_technology
                " t.network_group_type network_type," + //csv 5:network_type

                " t.geo_lat lat," + //csv 6:lat
                " t.geo_long long," + // csv 7:long
                " t.geo_provider loc_src," + //csv 8:loc_src android: 'gps'/'network'; browser/iOS: '' (empty string)
                " t.geo_accuracy loc_accuracy, " + //accuracy of geo location in m

                /*
                //csv 6:lat
                " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" +
                " t.geo_lat" +
                " WHEN (t.geo_accuracy < ?) THEN" +
                " ROUND(t.geo_lat*1111)/1111" + // approx 100m
                " ELSE null" +
                " END) lat," +
                // csv 7:long
                " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') THEN" +
                " t.geo_long" +
                " WHEN (t.geo_accuracy < ?) THEN" +
                " ROUND(t.geo_long*741)/741 " + //approx 100m
                " ELSE null" +
                " END) long," +
                // csv 8:loc_src android: 'gps'/'network'; browser/iOS: '' (empty string)
                " (CASE WHEN ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN" +
                " 'rastered'" + //make raster transparent
                " ELSE t.geo_provider" +
                " END) loc_src," +
                // accuracy of geo location in m
                " (CASE WHEN (t.geo_accuracy < ?) AND (t.geo_provider != 'manual') AND (t.geo_provider != 'geocoder') " +
                " THEN t.geo_accuracy " +
                " WHEN (t.geo_accuracy < 100) AND ((t.geo_provider = 'manual') OR (t.geo_provider = 'geocoder')) THEN 100" + // limit accuracy to 100m
                " WHEN (t.geo_accuracy < ?) THEN t.geo_accuracy" +
                " ELSE null END) loc_accuracy, " +
                */

                //csv 9:zip-code - defined as integer in data base, only meaningful for measurements in Austria
                " (CASE WHEN (t.zip_code < 1000 OR t.zip_code > 9999) THEN null ELSE t.zip_code END) zip_code," +
                // " zip_code_geo," + //(internal) zip-code, integer derived from geo_location, Austria only
                " t.speed_download download_kbit," + //csv 10:download_kbit
                " t.speed_upload upload_kbit," + //csv 11: upload_kbit
                " t.wifi_link_speed," + // nominal speed of wifi-link in mbit/s , Android-only
                " (t.ping_median::float / 1000000) ping_ms," + //median ping-time in ms (stored in ns in data base)
                " signal_strength," + //csv 13:signal_strength RSSI, mainly GSM/UMTS and Wifi, Android only, in dBm
                " lte_rsrp," + // csv 29: signal_strength RSRP, Android only, in dBm
                " lte_rsrq," + // signal quality RSRQ, Android only, in dB
                " ts.name server_name," + //csv 14:server_name, name of the test server used for download/upload (not applicable for JStest)
                " implausible, " + //csv 28:implausible, measurement not shown in map nor used in statistics, normally not visible
                " public_ip_as_name, " + //name of AS (not number)
                " duration test_duration," + //csv 15:test_duration, nominal duration of downlink and uplink throughput tests in seconds
                " num_threads_requested," + // number of threads requested by control-server
                " num_threads," + //csv 16:num_threads, number of threads used in downlink throughput test (uplink may differ)
                " num_threads_ul," + // number of threads used in uplink test
                " COALESCE(t.plattform, t.client_name) as platform," + //csv 17:platform; currently used: 'CLI'/'Android'/Applet/iOS/[from client_name: RMBTws, RMBTjs](null); (null) is used for RMBTjs 
                " COALESCE(adm.fullname, t.model) model," + //csv 18:model, translated t.model (model_native) to human readable form
                " t.model model_native," + //device used for test; Android API 'model'; iOS:'product'; Browser: Browser-name (zB Firefox)
                " t.product product," + // product used for test; Android APO 'product'; iOS: '' (not used); Browser: same as for model (browser name)
                " t.client_software_version client_version," + //csv 19:client_version, SW-version of client software (not RMBT-client-version), eg. '1.3'
                " t.network_operator network_mcc_mnc," + //csv 20:network_mcc_mnc, mobile country and network code of current network (Android only), string, eg "232-12'
                " network_country," + //(internal) Android-only, country code derived by client from mobile network code
                // " network_is_roaming," + //(internal) roaming-status of mobile network, boolean or (null); Android-only (obsolete)
                " roaming_type," + //roaming-status of mobile network, integer: 0:not roaming,1:national,2:international,(null):unknown (eg. iOS)
                " t.network_operator_name network_name," + //csv 21:network_name, name of current mobile network as displayed on device (eg: '3likeHome')
                " t.network_sim_operator sim_mcc_mnc," + //csv 22:sim_mcc_mnc, home network of SIM (initial 5 digits from IMSI), eg '232-01'
                " t.network_sim_country sim_country," + //(internal) Android-only, country derived by client from SIM (country of home network)
                " COALESCE(mprov.name,msim.shortname,msim.name,prov.name) provider_name," + //pre-defined list of providers (based on provider_id) //TODO replace provider
                " t.nat_type \"connection\"," + //csv 23:connection, translation-mechanism in NAT, eg. nat_local_to_public_ipv4
                " t.public_ip_asn asn," + //csv 24:asn, AS (autonomous system) number, number of public IP network
                " t.client_public_ip_anonymized ip_anonym," + //csv 25:ip_anonym, anonymized IP of client (IPv4: 8 bits removed, IPv6: 72 bits removed)
                " (ndt.s2cspd*1000)::int ndt_download_kbit," + //csv 26:ndt_download_kbit, result of NDT downlink throughput test kbit/s
                " (ndt.c2sspd*1000)::int ndt_upload_kbit," + //csv 27 ndt_uoload_kbit, result of NDT uplink throughput test in kbit/s
                " country_geoip," + // country-code derived from public IP-address, eg. 'AT'
                " country_location," + // country-code derived from geo_location, eg. 'DE'
                " country_asn," + // country_code derived from AS, eg. 'EU'
                " data->>'region' region," + // si - region from geo location
                " data->>'municipality' municipality," + // si - municipality from geo location
                " data->>'settlement' settlement," + // si - settlement from geo location
                " data->>'whitespace' whitespot," + // si - whitespace from geo location
                " bytes_download," + // number of bytes downloaded during test (download and upload) (obsolete)
                " bytes_upload," + // number of bytes uploaded during test (download and upload) (obsolete)
                " test_if_bytes_download," + //downloaded bytes on interface during total test (inc. training, ping, without NDT) (obsolete)
                " test_if_bytes_upload," + //uploaded bytes on interface during total test (inc. training, ping, without NDT) (obsolete)
                " testdl_if_bytes_download," + //downloaded bytes on interface during download-test (without training-seq)
                " testdl_if_bytes_upload," + //uploaded bytes on interface during download-test (without training-seq)
                " testul_if_bytes_download," + //downloaded bytes on interface during upload-test (without training-seq)
                " testul_if_bytes_upload," + //downloaded bytes on interface during upload-test (without training-seq)
                " (t.nsec_download::float / 1000000) duration_download_ms," + //duration of download-test in ms
                " (t.nsec_upload::float / 1000000) duration_upload_ms," + //duration of upload-test in ms
                " (t.time_dl_ns::float / 1000000) time_dl_ms," + //relative start time of download-test in ms (ignoring training-phase)
                " (t.time_ul_ns::float / 1000000) time_ul_ms" + //relative start time of download-test in ms (ignoring training-phase)
                // " phone_type" + //(internal) radio type of phone: 0 no mobile radio, 1 GSM (incl. UMTS,LTE) 2 CDMA (obsolete)

                " FROM test t" + " LEFT JOIN device_map adm ON adm.codename=t.model"
                + " LEFT JOIN test_server ts ON ts.uid=t.server_id" + " LEFT JOIN test_ndt ndt ON t.uid=ndt.test_id"
                + " LEFT JOIN provider prov ON t.provider_id=prov.uid"
                + " LEFT JOIN provider mprov ON t.mobile_provider_id=mprov.uid"
                + " LEFT JOIN mccmnc2name msim ON t.mobile_sim_id=msim.uid" + " WHERE " + " t.deleted = false "
                + " AND t.status = 'FINISHED' " + " AND t.open_test_uuid = ? ";

        //System.out.println(sql);

        final String[] columns;
        PreparedStatement ps = null;
        ResultSet rs = null;
        final JSONObject response = new JSONObject();
        try {
            ps = conn.prepareStatement(sql);

            //insert filter for accuracy
            /*
            double accuracy = Double.parseDouble(getSetting("rmbt_geo_accuracy_detail_limit"));
            ps.setDouble(1, accuracy);
            ps.setDouble(2, accuracy);
            ps.setDouble(3, accuracy);
            ps.setDouble(4, accuracy);
            ps.setDouble(5, accuracy);
            ps.setDouble(6, accuracy);
            */

            //openTestIDs are starting with "O"
            if (openTestUUID != null && openTestUUID.startsWith("O")) {
                openTestUUID = openTestUUID.substring(1);
            }
            ps.setObject(1, openTestUUID, Types.OTHER);

            if (!ps.execute())
                return null;
            rs = ps.getResultSet();

            if (rs.next()) {
                //fetch data for every field
                for (int i = 0; i < openDataFieldsFull.length; i++) {

                    //convert data to correct json response
                    final Object obj = rs.getObject(openDataFieldsFull[i]);
                    if (openDataBooleanFields.contains(openDataFieldsFull[i])) {
                        if (obj == null) {
                            response.put(openDataFieldsFull[i], false);
                        } else {
                            response.put(openDataFieldsFull[i], obj);
                        }
                    } else if (obj == null) {
                        response.put(openDataFieldsFull[i], JSONObject.NULL);
                    } else if (openDataNumberFields.contains(openDataFieldsFull[i])) {
                        final String tmp = obj.toString().trim();
                        if (tmp.isEmpty())
                            response.put(openDataFieldsFull[i], JSONObject.NULL);
                        else
                            response.put(openDataFieldsFull[i], JSONObject.stringToValue(tmp));
                    } else {
                        final String tmp = obj.toString().trim();
                        if (tmp.isEmpty())
                            response.put(openDataFieldsFull[i], JSONObject.NULL);
                        else
                            response.put(openDataFieldsFull[i], tmp);
                    }

                }
                /* obsolete (now in database)
                //special threatment for lat/lng: if too low accuracy -> do not send back to client
                double accuracy = rs.getDouble("loc_accuracy");
                if (accuracy > Double.parseDouble(settings.getString("RMBT_GEO_ACCURACY_DETAIL_LIMIT"))) {
                   response.put("loc_accuracy", JSONObject.NULL);
                   response.put("long", JSONObject.NULL);
                   response.put("lat", JSONObject.NULL);
                }
                    
                try {
                // do not output invalid zip-codes, must be 4 digits 
                int zip_code = rs.getInt("zip_code");
                if (zip_code <= 999 || zip_code > 9999)
                   response.put("zip_code", JSONObject.NULL);
                }
                catch (final SQLException e) {
                   System.out.println("Error on zip_code: " + e.toString());
                };
                */

                //classify download, upload, ping, signal
                response.put("download_classification",
                        Classification.classify(classification.THRESHOLD_DOWNLOAD, rs.getLong("download_kbit")));
                response.put("upload_classification",
                        Classification.classify(classification.THRESHOLD_UPLOAD, rs.getLong("upload_kbit")));
                response.put("ping_classification",
                        Classification.classify(classification.THRESHOLD_PING, rs.getLong("ping_ms") * 1000000));
                //classify signal accordingly
                if ((rs.getString("signal_strength") != null || rs.getString("lte_rsrp") != null)
                        && rs.getString("network_type") != null) { // signal available
                    if (rs.getString("lte_rsrp") == null) { // use RSSI
                        if (rs.getString("network_type").equals("WLAN")) { // RSSI for Wifi
                            response.put("signal_classification", Classification
                                    .classify(classification.THRESHOLD_SIGNAL_WIFI, rs.getLong("signal_strength")));
                        } else { // RSSI for Mobile
                            response.put("signal_classification", Classification.classify(
                                    classification.THRESHOLD_SIGNAL_MOBILE, rs.getLong("signal_strength")));
                        }
                    } else // RSRP for LTE
                        response.put("signal_classification", Classification
                                .classify(classification.THRESHOLD_SIGNAL_RSRP, rs.getLong("lte_rsrp")));
                } else { // no signal available
                    response.put("signal_classification", JSONObject.NULL);
                }

                //also load download/upload-speed-data, signal data and location data if possible
                JSONObject speedCurve = new JSONObject();
                JSONArray downloadSpeeds = new JSONArray();
                JSONArray uploadSpeeds = new JSONArray();
                JSONArray locArray = new JSONArray();
                JSONArray signalArray = new JSONArray();

                //Load speed data from database
                SpeedGraph speedGraph = new SpeedGraph(rs.getLong("test_uid"),
                        Math.max(rs.getInt("num_threads"), rs.getInt("num_threads_ul")), conn);
                for (SpeedGraph.SpeedGraphItem item : speedGraph.getUpload()) {
                    JSONObject obj = new JSONObject();
                    obj.put("time_elapsed", item.getTimeElapsed());
                    obj.put("bytes_total", item.getBytesTotal());
                    uploadSpeeds.put(obj);
                }
                for (SpeedGraph.SpeedGraphItem item : speedGraph.getDownload()) {
                    JSONObject obj = new JSONObject();
                    obj.put("time_elapsed", item.getTimeElapsed());
                    obj.put("bytes_total", item.getBytesTotal());
                    downloadSpeeds.put(obj);
                }

                //Load signal strength from database
                SignalGraph sigGraph = new SignalGraph(rs.getLong("test_uid"),
                        rs.getTimestamp("client_time").getTime(), conn);
                for (SignalGraph.SignalGraphItem item : sigGraph.getSignalList()) {
                    JSONObject json = new JSONObject();
                    json.put("time_elapsed", item.getTimeElapsed());
                    json.put("network_type", item.getNetworkType());
                    json.put("signal_strength", item.getSignalStrength());
                    json.put("lte_rsrp", item.getLteRsrp());
                    json.put("lte_rsrq", item.getLteRsrq());
                    json.put("cat_technology", item.getCatTechnology());
                    signalArray.put(json);
                }

                //Load gps coordinates from database
                LocationGraph locGraph = new LocationGraph(rs.getLong("test_uid"),
                        rs.getTimestamp("client_time").getTime(), conn);
                double totalDistance = locGraph.getTotalDistance();
                for (LocationGraph.LocationGraphItem item : locGraph.getLocations()) {
                    JSONObject json = new JSONObject();
                    json.put("time_elapsed", item.getTimeElapsed());
                    json.put("lat", item.getLatitude());
                    json.put("long", item.getLongitude());
                    json.put("loc_accuracy", (item.getAccuracy() > 0) ? item.getAccuracy() : JSONObject.NULL);
                    locArray.put(json);
                }

                speedCurve.put("upload", uploadSpeeds);
                speedCurve.put("download", downloadSpeeds);
                speedCurve.put("signal", signalArray);
                speedCurve.put("location", locArray);
                response.put("speed_curve", speedCurve);
                //add total distance during test - but only if within bounds
                if ((totalDistance > 0)
                        && totalDistance <= Double.parseDouble(getSetting("rmbt_geo_distance_detail_limit")))
                    response.put("distance", totalDistance);
                else
                    response.put("distance", JSONObject.NULL);

            } else {
                //invalid open_uuid
                setStatus(Status.CLIENT_ERROR_NOT_FOUND);
                response.put("error", "invalid open-uuid");
            }
        } catch (final JSONException e) {
            Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e);
        } catch (SQLException ex) {
            try {
                setStatus(Status.CLIENT_ERROR_NOT_FOUND);
                response.put("error", "invalid open-uuid");
            } catch (JSONException ex1) {
                Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex1);
            }
            Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
            } catch (final SQLException e) {
                Logger.getLogger(OpenTestResource.class.getName()).log(Level.SEVERE, null, e);
            }
        }

        return response.toString();
    }

    /**
     * Calculate the rough distance in meters between two points
     * taken from http://stackoverflow.com/questions/120283/working-with-latitude-longitude-values-in-java
     * @param lat1 
     * @param lng1
     * @param lat2
     * @param lng2
     * @return
     */
    private static double distFrom(double lat1, double lng1, double lat2, double lng2) {
        double earthRadius = 6371000;
        double dLat = Math.toRadians(lat2 - lat1);
        double dLng = Math.toRadians(lng2 - lng1);
        double sindLat = Math.sin(dLat / 2);
        double sindLng = Math.sin(dLng / 2);
        double a = Math.pow(sindLat, 2)
                + Math.pow(sindLng, 2) * Math.cos(Math.toRadians(lat1)) * Math.cos(Math.toRadians(lat2));
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        double dist = earthRadius * c;

        return dist;
    }

    public static class SpeedGraph {
        private ArrayList<SpeedGraphItem> upload = new ArrayList<>();
        private ArrayList<SpeedGraphItem> download = new ArrayList<>();

        /**
         * Load download and upload speed details
         * @param testUID the test uid
         * @param threads the max number of threads used in the test
         * @throws SQLException
         */
        public SpeedGraph(long testUID, int threads, java.sql.Connection conn) throws SQLException {
            PreparedStatement psSpeed = conn.prepareStatement(
                    "SELECT upload, thread, bytes, (time::float /1000/1000) as time FROM test_speed WHERE test_id = ? ORDER BY upload, time ASC");
            psSpeed.setLong(1, testUID);
            // Prepare arrays (bytes cumulated per thread)
            long bytes[] = new long[threads];
            ResultSet rsSpeed = psSpeed.executeQuery();
            long bytesCum = 0;
            boolean upload = false;
            double lastMs = -1;
            SpeedGraphItem lastObj = null; // the last object => if there are more
            // than one entries for one timestamp
            while (rsSpeed.next()) {
                int thread = rsSpeed.getInt("thread");
                double ms = rsSpeed.getDouble("time");

                // if its the first time a upload => clear array
                if (!upload && rsSpeed.getBoolean("upload")) {
                    for (int i = 0; i < bytes.length; i++) {
                        bytes[i] = 0;
                    }
                    bytesCum = 0;
                    upload = true;
                }

                // bytesCum = bytesCum - old + new
                bytesCum = bytesCum - bytes[thread];
                bytes[thread] = rsSpeed.getLong("bytes");
                bytesCum = bytesCum + bytes[thread];

                // put the object in the json-response
                ArrayList<SpeedGraphItem> array = (rsSpeed.getBoolean("upload")) ? this.upload : this.download;

                // if it is a new timestamp => make new array
                if (lastMs != ms) {
                    SpeedGraphItem obj = new SpeedGraphItem((long) ms, bytesCum);
                    array.add(obj);
                    lastObj = obj;
                    lastMs = ms;
                } else {
                    // if it is the same time => update the previous timestamp
                    lastObj.setBytesTotal(bytesCum);
                }
            }

            rsSpeed.close();
            psSpeed.close();
        }

        public ArrayList<SpeedGraphItem> getUpload() {
            return this.upload;
        }

        public ArrayList<SpeedGraphItem> getDownload() {
            return this.download;
        }

        private class SpeedGraphItem {
            private long timeElapsed;
            private long bytesTotal;

            public SpeedGraphItem(long timeElapsed, long bytesTotal) {
                this.timeElapsed = timeElapsed;
                this.bytesTotal = bytesTotal;
            }

            /**
             * @return The time elapsed since the begin of the test
             */
            public long getTimeElapsed() {
                return this.timeElapsed;
            }

            /**
             * @return The total bytes transmitted in all threads since the begin of the test 
             */
            public long getBytesTotal() {
                return this.bytesTotal;
            }

            public void setBytesTotal(long bytesTotal) {
                this.bytesTotal = bytesTotal;
            }
        }

    }

    public static class LocationGraph {
        private double totalDistance;
        private ArrayList<LocationGraphItem> locations = new ArrayList<>();

        /**
         * Gets all distinctive locations of a client during a test
         * @param testUID the uid of the test
         * @param testTime the begin of the test
         * @throws SQLException
         */
        public LocationGraph(long testUID, long testTime, java.sql.Connection conn) throws SQLException {
            PreparedStatement psLocation = conn.prepareStatement(
                    "SELECT test_id, g.geo_lat lat, g.geo_long long, g.accuracy loc_accuracy, time "
                            + "FROM geo_location g " + "WHERE g.test_id = ? and provider != 'network' " //do not mix with network-locations  (iOS: provider = '')
                            + "ORDER BY time;");
            psLocation.setLong(1, testUID);
            ResultSet rsLocation = psLocation.executeQuery();

            boolean first = true;
            boolean usedCurrentItem = false;
            LocationGraphItem item = null;

            double lastLat = 0;
            double lastLong = 0;
            double lastAcc = 0;
            this.totalDistance = 0;
            while (rsLocation.next()) {
                long timeElapsed = rsLocation.getTimestamp("time").getTime() - testTime;
                //there could be measurements taken before a test started
                //in this case, only return the last one
                if (first && timeElapsed > 0 && item != null) {
                    this.locations.add(item);
                    lastLat = item.getLatitude();
                    lastLong = item.getLongitude();
                    lastAcc = item.getAccuracy();
                    first = false;
                }

                item = new LocationGraphItem(Math.max(timeElapsed, 0), rsLocation.getDouble("long"),
                        rsLocation.getDouble("lat"), rsLocation.getDouble("loc_accuracy"));
                usedCurrentItem = false;

                //put triplet in the array if it is not the first one
                if (!first) {
                    //only put the point in the resulting array, if there is a significant
                    //distance from the last point
                    //therefore (difference in m) > (tolerance last point + tolerance new point)
                    double diff = OpenTestResource.distFrom(lastLat, lastLong, item.getLatitude(),
                            item.getLongitude());
                    //System.out.println("dist: " + diff);
                    double maxDiff = item.getAccuracy() + lastAcc;
                    //System.out.println("Distance: " + diff + "; tolTotal " + maxDiff + "; tol1 " + lastAcc + "; tol2 " + json.getDouble("loc_accuracy"));
                    if (diff > maxDiff) {
                        this.locations.add(item);
                        lastLat = item.getLatitude();
                        lastLong = item.getLongitude();
                        lastAcc = item.getAccuracy();
                        this.totalDistance += diff;
                    } else {
                        //if not, replace the old point, if the new is more accurate
                        if (item.getAccuracy() < lastAcc) {
                            this.locations.remove(this.locations.size() - 1);
                            this.locations.add(item);
                            lastLat = item.getLatitude();
                            lastLong = item.getLongitude();
                            lastAcc = item.getAccuracy();
                        }
                    }

                    usedCurrentItem = true;
                }
            }

            //always use the last item to connect the path with the end point
            if (!usedCurrentItem && this.locations.size() > 0) {
                //replace the last set point with it
                //since it is in the same inaccurracy area

                this.locations.remove(this.locations.size() - 1);
                this.locations.add(item);
                lastLat = item.getLatitude();
                lastLong = item.getLongitude();
                lastAcc = item.getAccuracy();

            }

            //System.out.println("called w id: " + testUID + ", ct: " + testTime + ", d: " + this.totalDistance + ", " + this.getTotalDistance());

            rsLocation.close();
            psLocation.close();
        }

        public double getTotalDistance() {
            return this.totalDistance;
        }

        public ArrayList<LocationGraphItem> getLocations() {
            return this.locations;
        }

        private class LocationGraphItem {
            private double longitude;
            private double latitude;
            private double accuracy;
            private long timeElapsed;

            public LocationGraphItem(long timeElapsed, double longitude, double latitude, double accuracy) {
                this.longitude = longitude;
                this.latitude = latitude;
                this.timeElapsed = timeElapsed;
                this.accuracy = accuracy;
            }

            /**
             * @return The time elapsed since the begin of the test
             */
            public long getTimeElapsed() {
                return this.timeElapsed;
            }

            public double getLongitude() {
                return this.longitude;
            }

            public double getLatitude() {
                return this.latitude;
            }

            /**
             * @return The accuracy of the measurement in meters
             */
            public double getAccuracy() {
                return this.accuracy;
            }
        }
    }

    public static class SignalGraph {
        private static int LOWER_BOUND = -1500;
        private static int MAX_TIME = 60000;

        private ArrayList<SignalGraphItem> signalList = new ArrayList<>();

        /**
         * Gets all signal measurements from a test
         * @param testUID the test uid
         * @param testTime the begin of the test
         * @throws SQLException
         */
        public SignalGraph(long testUID, long testTime, java.sql.Connection conn) throws SQLException {
            PreparedStatement psSignal = conn.prepareStatement(
                    "SELECT test_id, nt.name network_type, nt.group_name cat_technology, signal_strength, lte_rsrp, lte_rsrq, wifi_rssi, time "
                            + "FROM signal " + "JOIN network_type nt " + "ON nt.uid = network_type_id "
                            + "WHERE test_id = ? " + "ORDER BY time;");
            psSignal.setLong(1, testUID);

            ResultSet rsSignal = psSignal.executeQuery();

            boolean first = true;
            SignalGraphItem item = null;
            while (rsSignal.next()) {
                long timeElapsed = rsSignal.getTimestamp("time").getTime() - testTime;
                //there could be measurements taken before a test started
                //in this case, only return the last one
                if (first && timeElapsed > 0 && item != null) {
                    this.signalList.add(item);
                    first = false;
                }

                //ignore measurements after a threshold of one minute
                if (timeElapsed > MAX_TIME)
                    break;

                int signalStrength = rsSignal.getInt("signal_strength");
                int lteRsrp = rsSignal.getInt("lte_rsrp");
                int lteRsrq = rsSignal.getInt("lte_rsrq");
                if (signalStrength == 0)
                    signalStrength = rsSignal.getInt("wifi_rssi");

                if (signalStrength > LOWER_BOUND)
                    item = new SignalGraphItem(Math.max(timeElapsed, 0), rsSignal.getString("network_type"),
                            signalStrength, lteRsrp, lteRsrq, rsSignal.getString("cat_technology"));

                //put 5-let in the array if it is not the first one
                if (!first || rsSignal.isLast()) {
                    if (timeElapsed < 0) {
                        item.timeElapsed = 1000;
                    }
                    this.signalList.add(item);
                }
            }

            rsSignal.close();
            psSignal.close();
        }

        public ArrayList<SignalGraphItem> getSignalList() {
            return this.signalList;
        }

        private class SignalGraphItem {
            private long timeElapsed;
            private String networkType;
            private int signalStrength;
            private int lteRsrp;
            private int lteRsrq;
            private String catTechnology;

            public SignalGraphItem(long timeElapsed, String networkType, int signalStrength, int lteRsrp,
                    int lteRsrq, String catTechnology) {
                this.timeElapsed = timeElapsed;
                this.networkType = networkType;
                this.signalStrength = signalStrength;
                this.lteRsrp = lteRsrp;
                this.lteRsrq = lteRsrq;
                this.catTechnology = catTechnology;
            }

            /**
             * @return The time elapsed since the begin of the test
             */
            public long getTimeElapsed() {
                return this.timeElapsed;
            }

            /**
             * @return The type of the network, e.g. 
             */
            public String getNetworkType() {
                return this.networkType;
            }

            /**
            * @return The signal strength RSSI in dBm
            */
            public int getSignalStrength() {
                return this.signalStrength;
            }

            /**
            * @return The signal strength RSRP in dBm
            */
            public int getLteRsrp() {
                return this.lteRsrp;
            }

            /**
            * @return The signal quality RSRQ in dB
            */
            public int getLteRsrq() {
                return this.lteRsrq;
            }

            public String getCatTechnology() {
                return this.catTechnology;
            }

        }
    }
}