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 tracking_device_sessions WHERE RaceObjectId = '" . $ro['UUID'] . "'");
while ($tds = mysql_fetch_assoc($mq_tds)) {
if ($ro['TrackedObjectType'] == "POSITIONED_ITEM") {
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) {
?>
-