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) { ?>

-