/*******************************************************************************
* Copyright 2013-2016 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.controlServer;
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;
import at.alladin.rmbt.shared.model.SpeedItems;
import at.alladin.rmbt.shared.model.SpeedItems.SpeedItem;
import com.google.gson.Gson;
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_uuid", //csv 1
"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",
"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, " +
" ('P' || t.open_uuid) open_uuid," + //csv 1:open_uuid, UUID prefixed with 'P'
" ('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'
" 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)
" speed.items speed_items" + // json representation of individual up+down speed items
" 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" +
" LEFT JOIN speed ON speed.open_test_uuid=t.open_test_uuid" +
" WHERE " +
" t.deleted = false " +
" AND t.status = 'FINISHED' " +
" AND t.open_test_uuid = ? ";
//System.out.println(sql);
PreparedStatement ps = null;
ResultSet rs = null;
final JSONObject response = new JSONObject();
try
{
ps = conn.prepareStatement(sql);
//insert filter for accuracy
double accuracy = Double.parseDouble(settings.getString("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(7, 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"), capabilities.getClassificationCapability().getCount()));
response.put("upload_classification", Classification.classify(Classification.THRESHOLD_UPLOAD, rs.getLong("upload_kbit"), capabilities.getClassificationCapability().getCount()));
response.put("ping_classification", Classification.classify(Classification.THRESHOLD_PING, rs.getLong("ping_ms")*1000000, capabilities.getClassificationCapability().getCount()));
//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"),capabilities.getClassificationCapability().getCount()));
} else { // RSSI for Mobile
response.put(
"signal_classification",
Classification
.classify(
Classification.THRESHOLD_SIGNAL_MOBILE,
rs.getLong("signal_strength"),capabilities.getClassificationCapability().getCount()));
}
} else // RSRP for LTE
response.put("signal_classification", Classification
.classify(Classification.THRESHOLD_SIGNAL_RSRP,
rs.getLong("lte_rsrp"), capabilities.getClassificationCapability().getCount()));
} 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();
// speed data
final Gson gson = getGson(false);
final SpeedItems speedItems = gson.fromJson(rs.getString("speed_items"), SpeedItems.class);
if (speedItems != null)
{
long lastTime = -1;
for (SpeedItem item : speedItems.getAccumulatedSpeedItemsUpload()) {
JSONObject obj = new JSONObject();
final long time = Math.round((double)item.getTime() / 1000000);
if (time == lastTime)
continue;
obj.put("time_elapsed", time);
obj.put("bytes_total", item.getBytes());
uploadSpeeds.put(obj);
lastTime = time;
}
lastTime = -1;
for (SpeedItem item : speedItems.getAccumulatedSpeedItemsDownload()) {
JSONObject obj = new JSONObject();
final long time = Math.round((double)item.getTime() / 1000000);
if (time == lastTime)
continue;
obj.put("time_elapsed", time);
obj.put("bytes_total", item.getBytes());
downloadSpeeds.put(obj);
lastTime = time;
}
}
//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(settings.getString("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 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;
}
}
}
}