Incident report

'$starttime' AND EndTime < '$endtime' ORDER BY StartTime") or die(mysql_error()); while ($race = mysql_fetch_assoc($mq_races)) { $start_arr = split(" ", $race["StartTime"]); $end_arr = split(" ", $race["EndTime"]); $start_date_arr = split("-", $start_arr[0]); $start_time_arr = split(":", $start_arr[1]); $end_date_arr = split("-", $end_arr[0]); $end_time_arr = split(":", $end_arr[1]); $start_in_s = mktime($start_time_arr[0], $start_time_arr[1], $start_time_arr[2], $start_date_arr[1], $start_date_arr[2], $start_date_arr[0]); $end_in_s = mktime($end_time_arr[0], $end_time_arr[1], $end_time_arr[2], $end_date_arr[1], $end_date_arr[2], $end_date_arr[0]); $race_length = $end_in_s - $start_in_s; #Find marks $sql = "SELECT RaceObject.UUID FROM Routes INNER JOIN RouteControlPoints ON Routes.UUID = RouteControlPoints.Route INNER JOIN ControlPoints ON ControlPoints.UUID = RouteControlPoints.ControlPoint INNER JOIN RaceObject ON RaceObject.TrackedObjectId = ControlPoints.UUID WHERE Routes.UUID = '" . $race['DefaultRoute'] . "'"; $mark_ros = array(); $mq_marks = mysql_query($sql) or die(mysql_error()); while ($mark = mysql_fetch_assoc($mq_marks)) { $mark_ros[] = $mark['UUID']; } $markros = join("','",$mark_ros); #loop through race objects for race $mq_race_objects = mysql_query("SELECT * FROM RaceObject WHERE RaceId = '" . $race['UUID'] . "' OR UUID IN ('$markros')"); while ($ro = mysql_fetch_assoc($mq_race_objects)) { #loop though each tracking device session $mq_tds = mysql_query("SELECT * FROM TrackingDeviceSessions WHERE RaceObjectId = '" . $ro['UUID'] . "'"); while ($tds = mysql_fetch_assoc($mq_tds)) { if ($ro['TrackedObjectType'] == "CONTROLPOINT") { if ($tds['StartTime'] > $race['EndTime']) continue; if ($tds['EndTime'] < $race['StartTime']) continue; if ($tds['StartTime'] < $race['StartTime']) $tds['StartTime'] = $race['StartTime']; if ($tds['EndTime'] > $race['EndTime']) $tds['EndTime'] = $race['EndTime']; } $incident = array(); $start_arr = split(" ", $tds["StartTime"]); $end_arr = split(" ", $tds["EndTime"]); $start_date_arr = split("-", $start_arr[0]); $start_time_arr = split(":", $start_arr[1]); $end_date_arr = split("-", $end_arr[0]); $end_time_arr = split(":", $end_arr[1]); $start_in_s = mktime($start_time_arr[0], $start_time_arr[1], $start_time_arr[2], $start_date_arr[1], $start_date_arr[2], $start_date_arr[0]); $end_in_s = mktime($end_time_arr[0], $end_time_arr[1], $end_time_arr[2], $end_date_arr[1], $end_date_arr[2], $end_date_arr[0]); $expected = ($end_in_s - $start_in_s) / $interval; # Count positions $mq_count_pos = mysql_query("SELECT count(*) FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "'") or die(mysql_error()); $count = mysql_fetch_row($mq_count_pos); if ($completeness_per_unit[$tds['TrackingDeviceId']] == null) $completeness_per_unit[$tds['TrackingDeviceId']] = array(); $completeness_per_unit[$tds['TrackingDeviceId']][$tds["UUID"]] = $count[0] / $expected; #Calculate delay $mq_avg_delay = mysql_query("SELECT AVG(UNIX_TIMESTAMP(ReceivedTime) - UNIX_TIMESTAMP(SampleTime)) FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "'") or die(mysql_error()); $delay = mysql_fetch_row($mq_avg_delay); $first_ts = 0; $last_ts = 0; # Get the first position $mq_first_pos = mysql_query("SELECT *, UNIX_TIMESTAMP(SampleTime) AS ts FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "' ORDER BY SampleTime ASC LIMIT 1") or die(mysql_error()); if ($first_pos = mysql_fetch_assoc($mq_first_pos)) { $incident['actual_first_pos'] = $first_pos["SampleTime"]; $incident['battery_first_pos'] = round($first_pos["Voltage"]); $first_ts = $first_pos['ts']; } else { $incident['actual_first_pos'] = "N/A"; $incident['battery_first_pos'] = "N/A"; } # Get the last position $mq_last_pos = mysql_query("SELECT *, UNIX_TIMESTAMP(SampleTime) AS ts FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "' ORDER BY SampleTime DESC LIMIT 1") or die(mysql_error()); if ($last_pos = mysql_fetch_assoc($mq_last_pos)) { $incident['actual_last_pos'] = $last_pos['SampleTime']; $incident['battery_last_pos'] = round($last_pos['Voltage']); $incident['last_received'] = $last_pos['ReceivedTime']; $last_ts = $last_pos['ts']; } else { $incident['actual_last_pos'] = "N/A"; $incident['battery_last_pos'] = "N/A"; $incident['last_received'] = "N/A"; } if ($first_ts > 0 && $last_ts > 0 && $first_ts != $last_ts) { if ($incident['battery_first_pos'] != $incident['battery_last_pos']) { $time_used = $last_ts - $first_ts; $battery_used = $incident['battery_first_pos'] - $incident['battery_last_pos']; $secons_per_pct = $time_used / $battery_used; if ($battery_usage[$tds['TrackingDeviceId']] == null) $battery_usage[$tds['TrackingDeviceId']] = array(); $battery_usage[$tds['TrackingDeviceId']][$tds['UUID']] = $secons_per_pct; } } #If completeness is below 95% make an incident report if (($count[0] / $expected < $criteriapct) || $delay[0] > $delayMax) { $incident['race'] = $race['Name']; if ($ro['TrackedObjectType'] == "COMPETITOR") { #Get competitor name $mq_comp = mysql_query("SELECT * FROM Competitors WHERE UUID = '" . $ro['TrackedObjectId'] . "'") or die(mysql_error()); if ($comp = mysql_fetch_assoc($mq_comp)) { $incident['tracked_object'] = $comp['FirstName'] . " " . $comp['LastName'] . " (" . $comp['Initials'] . ")"; } else { $incident['tracked_object'] = "N/A"; } } else { #Get controlpoint name $mq_cp = mysql_query("SELECT * FROM ControlPoints WHERE UUID = '" . $ro['TrackedObjectId'] . "'") or die(mysql_error()); if ($cp = mysql_fetch_assoc($mq_cp)) { $incident['tracked_object'] = $cp['Name'] . " (" . $cp['ShortName'] . ")"; } else { $incident['tracked_object'] = "N/A"; } } $incident['expected_count'] = $expected; $incident['pos_count'] = $count[0]; $incident['expected_last'] = $tds['EndTime']; $incident['expected_first'] = $tds['StartTime']; $incident['avg_delay'] = $delay[0]; if ($incident['avg_delay'] == "") $incident['avg_delay'] = "N/A"; #Start of day $corr_time = $start_in_s + $timezone * 3600; $midnight = $corr_time - date("H", $corr_time) * 3600 - $timezone * 3600 + 3600; $start_of_day = date("Y-m-d H:m:s", $midnight); $mq_sod = mysql_query("SELECT * FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime > '$start_of_day' ORDER BY SampleTime ASC LIMIT 1") or die(mysql_error()); if ($sod = mysql_fetch_assoc($mq_sod)) { $incident["bat_start_day"] = round($sod['Voltage']) . " - " . $sod["SampleTime"]; } else { $incident["bat_start_day"] = "N/A"; } #Get average sats $mq_sats = mysql_query("SELECT AVG(Satellites) FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "'") or die(mysql_error()); if ($sats = mysql_fetch_row($mq_sats)) { $incident['avg_sats'] = $sats[0]; if ($sats[0] == "") $incident['avg_sats'] = "N/A"; } else { $incident['avg_sats'] = "N/A"; } if ($incidents_per_unit[$tds['TrackingDeviceId']] == null) $incidents_per_unit[$tds['TrackingDeviceId']] = array(); $incidents_per_unit[$tds['TrackingDeviceId']][$tds['UUID']] = $incident; } } } } function sortIncidents($a, $b) { if (sizeof($a) == sizeof($b)) return 0; if (sizeof($a) > sizeof($b)) return -1; else return 1; } uasort($incidents_per_unit, "sortIncidents"); ?> $incident_arr) { ?>

- 0 incident(s)



Completeness: "> NAN% ( / 0)
Avg delay: ">
First pos:
Expected:
Last pos:
Expected:
Las pos recv:
Battery F/L: /
Bat st. day:
Avg sats:

Delay report

'$starttime' AND EndTime < '$endtime' ORDER BY StartTime") or die(mysql_error()); while ($race = mysql_fetch_assoc($mq_races)) { $start_arr = split(" ", $race["StartTime"]); $end_arr = split(" ", $race["EndTime"]); $start_date_arr = split("-", $start_arr[0]); $start_time_arr = split(":", $start_arr[1]); $end_date_arr = split("-", $end_arr[0]); $end_time_arr = split(":", $end_arr[1]); $start_in_s = mktime($start_time_arr[0], $start_time_arr[1], $start_time_arr[2], $start_date_arr[1], $start_date_arr[2], $start_date_arr[0]); $end_in_s = mktime($end_time_arr[0], $end_time_arr[1], $end_time_arr[2], $end_date_arr[1], $end_date_arr[2], $end_date_arr[0]); $race_length = $end_in_s - $start_in_s; #Find marks $sql = "SELECT RaceObject.UUID FROM Routes INNER JOIN RouteControlPoints ON Routes.UUID = RouteControlPoints.Route INNER JOIN ControlPoints ON ControlPoints.UUID = RouteControlPoints.ControlPoint INNER JOIN RaceObject ON RaceObject.TrackedObjectId = ControlPoints.UUID WHERE Routes.UUID = '" . $race['DefaultRoute'] . "'"; $mark_ros = array(); $mq_marks = mysql_query($sql) or die(mysql_error()); while ($mark = mysql_fetch_assoc($mq_marks)) { $mark_ros[] = $mark['UUID']; } $markros = join("','",$mark_ros); #loop through race objects for race $mq_race_objects = mysql_query("SELECT * FROM RaceObject WHERE RaceId = '" . $race['UUID'] . "' OR UUID IN ('$markros')"); while ($ro = mysql_fetch_assoc($mq_race_objects)) { #loop though each tracking device session $mq_tds = mysql_query("SELECT * FROM TrackingDeviceSessions WHERE RaceObjectId = '" . $ro['UUID'] . "'"); while ($tds = mysql_fetch_assoc($mq_tds)) { if ($ro['TrackedObjectType'] == "CONTROLPOINT") { if ($tds['StartTime'] > $race['EndTime']) continue; if ($tds['EndTime'] < $race['StartTime']) continue; if ($tds['StartTime'] < $race['StartTime']) $tds['StartTime'] = $race['StartTime']; if ($tds['EndTime'] > $race['EndTime']) $tds['EndTime'] = $race['EndTime']; } $start_in_s = mktime($start_time_arr[0], $start_time_arr[1], $start_time_arr[2], $start_date_arr[1], $start_date_arr[2], $start_date_arr[0]); $end_in_s = mktime($end_time_arr[0], $end_time_arr[1], $end_time_arr[2], $end_date_arr[1], $end_date_arr[2], $end_date_arr[0]); $expected = ($end_in_s - $start_in_s) / $interval; # Count positions $mq_count_pos = mysql_query("SELECT count(*) FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "'") or die(mysql_error()); $count = mysql_fetch_row($mq_count_pos); if ($completeness_per_unit[$tds['TrackingDeviceId']] == null) $completeness_per_unit[$tds['TrackingDeviceId']] = array(); $completeness_per_unit[$tds['TrackingDeviceId']][$tds["UUID"]] = $count[0] / $expected; $sql = "SELECT AVG(UNIX_TIMESTAMP(ReceivedTime) - UNIX_TIMESTAMP(SampleTime)) AS avg, MAX(UNIX_TIMESTAMP(ReceivedTime) - UNIX_TIMESTAMP(SampleTime)) as max, MIN(UNIX_TIMESTAMP(ReceivedTime) - UNIX_TIMESTAMP(SampleTime)) AS min, STDDEV(UNIX_TIMESTAMP(ReceivedTime) - UNIX_TIMESTAMP(SampleTime)) AS stddev FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "'"; $mq_delay = mysql_query($sql); if ($row = mysql_fetch_assoc($mq_delay)) { if ($row['avg'] != "") { if ($td_delays["" . $tds['TrackingDeviceId']] == null) $td_delays[$tds['TrackingDeviceId']] = array(); $td_delays[$tds['TrackingDeviceId']][$race['UUID']] = $row; } } $first_ts = 0; $last_ts = 0; # Get the first position $mq_first_pos = mysql_query("SELECT *, UNIX_TIMESTAMP(SampleTime) AS ts FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "' ORDER BY SampleTime ASC LIMIT 1") or die(mysql_error()); if ($first_pos = mysql_fetch_assoc($mq_first_pos)) { $actual_first_pos = $first_pos["SampleTime"]; $battery_first_pos = round($first_pos["Voltage"]); $first_ts = $first_pos['ts']; } else { $actual_first_pos = "N/A"; $battery_first_pos = "N/A"; } # Get the last position $mq_last_pos = mysql_query("SELECT *, UNIX_TIMESTAMP(SampleTime) AS ts FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "' ORDER BY SampleTime DESC LIMIT 1") or die(mysql_error()); if ($last_pos = mysql_fetch_assoc($mq_last_pos)) { $actual_last_pos = $last_pos['SampleTime']; $battery_last_pos = round($last_pos['Voltage']); $last_received = $last_pos['ReceivedTime']; $last_ts = $last_pos['ts']; } else { $actual_last_pos = "N/A"; $battery_last_pos = "N/A"; $last_received = "N/A"; } if ($first_ts > 0 && $last_ts > 0 && $first_ts != $last_ts) { if ($battery_first_pos != $battery_last_pos) { $time_used = $last_ts - $first_ts; $battery_used = $battery_first_pos - $battery_last_pos; $secons_per_pct = $time_used / $battery_used; if ($battery_usage[$tds['TrackingDeviceId']] == null) $battery_usage[$tds['TrackingDeviceId']] = array(); $battery_usage[$tds['TrackingDeviceId']][$tds['UUID']] = $secons_per_pct; } } /* # Guess at configuration $sql = "SELECT Count(*) as cnt, (UNIX_TIMESTAMP(ReceivedTime)-UNIX_TIMESTAMP(SampleTime)) AS Delay FROM GPSData WHERE TrackingDeviceId = " . $tds['TrackingDeviceId'] . " AND SampleTime BETWEEN '" . $tds['StartTime'] . "' AND '" . $tds['EndTime'] . "' GROUP BY Delay ORDER BY Delay"; $mq_config = mysql_query($sql) or die(mysql_error()); $gprs_sending = 0; $last = 1; $i = 1; $sum = 0; while ($row = mysql_fetch_assoc($mq_config)) { if ($row['Delay'] > 4) { if ($i > 4 && $sum > 0) { if (($row['cnt'] / ($sum/$i) < 0.1)) { $gprs_sending = $row['Delay'] - 2; break; } } $sum += $row['cnt']; $i++; } } if ($gprs_sending > 0) echo $gprs_sending . "
"; */ } } } $td_delays_avg = array(); $total_avgs = 0; $total_min = 99999999; $total_max = 0; $total_stddevs = 0; foreach ($td_delays as $unitid => $unit_arr) { $avgs = 0; $min = 9999999999; $max = 0; $stddevs = 0; foreach ($unit_arr AS $report) { $avgs += $report['avg']; if ($report['min'] < $min) $min = $report['min']; if ($report['max'] > $max) $max = $report['max']; $stddevs += $report['stddev']; } $total_avgs += $avgs / sizeof($unit_arr); if ($min < $total_min) $total_min = $min; if ($max > $total_max) $total_max = $max; $total_stddevs += $stddevs / sizeof($unit_arr); $td_delays_avg[$unitid] = array('avg' => $avgs / sizeof($unit_arr), 'min' => $min, 'max' => $max, 'stddev' => $stddevs / sizeof($unit_arr), 'id' => $unitid); } $total_avg = $total_avgs / sizeof($td_delays); $total_stddev = $total_stddevs / sizeof($td_delays); if ($_REQUEST['delaySort'] != "") $_SESSION['delaySort'] = $_REQUEST['delaySort']; if ($_REQUEST['delayDir'] != "") $_SESSION['delayDir'] = $_REQUEST['delayDir']; if ($_SESSION['delaySort'] == "") $_SESSION['delaySort'] = 'avg'; if ($_SESSION['delayDir'] == "") $_SESSION['delayDir'] = 'asc'; global $f; $f = 1; if ($_SESSION['delayDir'] == "asc") { $delayDir = "desc"; } else { $f = -1; $delayDir = "asc"; } function sortTdDelays($a,$b) { global $f; if ($_SESSION['delaySort'] == "avg") { if ($a['avg']*1 == $b['avg']*1) return 0; elseif ($a['avg']*1 > $b['avg']*1) return 1*$f; else return -1*$f; } elseif ($_SESSION['delaySort'] == "min") { if ($a['min']*1 == $b['min']*1) return 0; elseif ($a['min']*1 > $b['min']*1) return 1*$f; else return -1*$f; } elseif ($_SESSION['delaySort'] == "max") { if ($a['max']*1 == $b['max']*1) return 0; elseif ($a['max']*1 > $b['max']*1) return 1*$f; else return -1*$f; } elseif ($_SESSION['delaySort'] == "stddev") { if ($a['stddev']*1 == $b['stddev']*1) return 0; elseif ($a['stddev']*1 > $b['stddev']*1) return 1*$f; else return -1*$f; } else { return strcmp($a['id'],$b['id'])*$f; } } uasort($td_delays_avg,"sortTdDelays"); $baseurl = "?type=" . $_REQUEST['type'] . "&StartTime_Date=" . $_REQUEST['StartTime_Date'] . "&StartTime_Time=" . urlencode($_REQUEST['StartTime_Time']) . "&EndTime_Date=" . $_REQUEST['EndTime_Date'] . "&EndTime_Time=" . urlencode($_REQUEST['EndTime_Time']) . "&interval=" . $_REQUEST['interval'] . "&criteriaPct=" . $_REQUEST['criteriapct'] . "&delayMax=" . $_REQUEST['delayMax'] . "&eventid=" . $_REQUEST['eventid']; ?> $report) { ?>
Tracking device Average Minimum Maximum Std. dev.
Overall 0 0
0 0
$compArr) { $this_stat = array(); $i = 0; $sum = 0; $max = 0; $min = 9999999; foreach ($compArr as $tdsid => $comp) { if ($comp < $min) $min = $comp; if ($comp > $max) $max = $comp; $i++; $sum += $comp; } $this_stat['avg'] = ($sum / $i); $this_stat['min'] = $min; $this_stat['max'] = $max; $this_stat['td'] = $td; $completeness_stat[$td] = $this_stat; } if ($_REQUEST['completenessSort'] != "") $_SESSION['completenessSort'] = $_REQUEST['completenessSort']; if ($_REQUEST['completenessDir'] != "") $_SESSION['completenessDir'] = $_REQUEST['completenessDir']; if ($_SESSION['completenessSort'] == "") $_SESSION['completenessSort'] = 'avg'; if ($_SESSION['completenessDir'] == "") $_SESSION['completenessDir'] = 'asc'; global $h; $h = 1; if ($_SESSION['completenessDir'] == "asc") { $completenessDir = "desc"; } else { $h = -1; $completenessDir = "asc"; } function sortTdCompleteness($a,$b) { global $h; if ($_SESSION['completenessSort'] == "avg") { if ($a['avg']*1 == $b['avg']*1) return 0; elseif ($a['avg']*1 > $b['avg']*1) return 1*$h; else return -1*$h; } elseif ($_SESSION['completenessSort'] == "min") { if ($a['min']*1 == $b['min']*1) return 0; elseif ($a['min']*1 > $b['min']*1) return 1*$h; else return -1*$h; } elseif ($_SESSION['completenessSort'] == "max") { if ($a['max']*1 == $b['max']*1) return 0; elseif ($a['max']*1 > $b['max']*1) return 1*$h; else return -1*$h; } else { return strcmp($a['td'],$b['td'])*$h; } } uasort($completeness_stat,"sortTdCompleteness"); ?>

Completeness report


$cs) { ?>
Tracking device Avg Min Max
0% 0% 0%
$b['discharge']*1) return 1*$g; else return -1*$g; } elseif ($_SESSION['batterySort'] == "completeness") { if ($a['completeness']*1 == $b['completeness']*1) return 0; elseif ($a['completeness']*1 > $b['completeness']*1) return 1*$g; else return -1*$g; } else { return strcmp($a['td'],$b['td'])*$g; } } ?>

Battery report


$usages) { $this_stat = array(); $i = 0; $sum = 0; foreach ($usages as $tdsid => $value) { $sum += $value; $i++; } $this_stat['discharge'] = ($sum / $i); $this_stat['completeness'] = $completeness_stat[$td]['avg']; $this_stat['td'] = $td; $battery_stat[$td] = $this_stat; } uasort($battery_stat,"sortTdBatteries"); foreach ($battery_stat as $td => $sps) { ?>
Tracking device Discharge Completeness
0 s/% 0%