| <?php | <?php |
| /* | |
| * Copyright 2010,2011 Alexander Sadleir | |
| 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. | |
| */ | |
| include ('../include/common.inc.php'); | include ('../include/common.inc.php'); |
| include_header("MyWay Delta Calculate", "mywayDeltaCalc"); | include_header("MyWay Delta Calculate", "mywayDeltaCalc"); |
| flush(); | flush(); |
| ob_flush(); | ob_flush(); |
| function abssort($a, $b) | |
| { | function abssort($a, $b) { |
| if ($a['timeDiff'] == $b['timeDiff']) { | if ($a['timeDiff'] == $b['timeDiff']) { |
| return 0; | return 0; |
| } | } |
| return (abs($a['timeDiff']) < abs($b['timeDiff'])) ? -1 : 1; | return (abs($a['timeDiff']) < abs($b['timeDiff'])) ? -1 : 1; |
| } | } |
| //collect all observation not in delta | //collect all observation not in delta |
| $query = "select * from myway_observations INNER JOIN myway_stops | $query = "select * from myway_observations INNER JOIN myway_stops |
| ON myway_observations.myway_stop=myway_stops.myway_stop INNER JOIN myway_routes | ON myway_observations.myway_stop=myway_stops.myway_stop INNER JOIN myway_routes |
| ON myway_observations.myway_route=myway_routes.myway_route | ON myway_observations.myway_route=myway_routes.myway_route |
| WHERE observation_id NOT IN | WHERE observation_id NOT IN |
| ( | ( |
| SELECT observation_id | SELECT observation_id |
| FROM myway_timingdeltas | FROM myway_timingdeltas |
| )"; | )"; |
| debug($query, "database"); | debug($query, "database"); |
| $query = $conn->prepare($query); | $query = $conn->prepare($query); |
| $query->execute(); | $query->execute(); |
| if (!$query) { | if (!$query) { |
| databaseError($conn->errorInfo()); | databaseError($conn->errorInfo()); |
| return Array(); | return Array(); |
| } | } |
| $uncalcdObservations = $query->fetchAll(); | $uncalcdObservations = $query->fetchAll(); |
| //Display count | //Display count |
| echo "<h3>" . sizeof($uncalcdObservations) . " observations not yet processed</h2>"; | echo "<h3>" . sizeof($uncalcdObservations) . " observations not yet processed</h2>"; |
| //foreach observation not in delta | //foreach observation not in delta |
| foreach ($uncalcdObservations as $obsv) { | foreach ($uncalcdObservations as $obsv) { |
| //var_dump($obsv); | //var_dump($obsv); |
| echo "<h3>Observation {$obsv['observation_id']}:</h1> | echo "<h3>Observation {$obsv['observation_id']}:</h1> |
| <small>{$obsv['myway_stop']} @ {$obsv['time']} on {$obsv['myway_route']}</small><br>"; | <small>{$obsv['myway_stop']} @ {$obsv['time']} on {$obsv['myway_route']}</small><br>"; |
| if ($obsv["stop_code"] == "") { | // convert timestamp into time of day and date |
| echo "error, stop '{$obsv['myway_stop']}' unknown"; | |
| continue; | |
| } | |
| if ($obsv["route_full_name"] == "") { | |
| echo "error, route '{$obsv['myway_route']}' unknown"; | |
| continue; | |
| } | |
| // convert timestamp into time of day and date | |
| // timezones from http://www.postgresql.org/docs/8.0/static/datetime-keywords.html | // timezones from http://www.postgresql.org/docs/8.0/static/datetime-keywords.html |
| $time = date("H:i:s", strtotime($obsv['time'])); | $time = date("H:i:s", strtotime($obsv['time'])); |
| $time_tz = date("H:i:s", strtotime($obsv['time']))." AESST"; | $time_tz = date("H:i:s", strtotime($obsv['time'])) . " AESST"; |
| $search_time = date("H:i:s", strtotime($obsv['time'])-(30*60)); // 30 minutes margin | $search_time = date("H:i:s", strtotime($obsv['time']) - (60 * 60)); // 30 minutes margin |
| $date = date("c", strtotime($obsv['time'])); | $date = date("c", strtotime($obsv['time'])); |
| $timing_period = service_period(strtotime($date)); | $timing_period = service_period(strtotime($date)); |
| $potentialStops = getStopsByStopCode($obsv["stop_code"], $obsv["stop_street"]); | |
| //:get myway_stops records | // little hack for public holidays nolonger active; weekdays and 900+ route numbers don't make sense |
| //:search by starts with stopcode and starts with street if street is not null | if ($timing_period == "weekday" && preg_match('/9../',$obsv["route_short_name"])) { |
| //no result, skip and display error | echo "Potential public holiday detected, trying Sunday timetable.<br>"; |
| if (sizeof($potentialStops) < 1) { | |
| echo "error, potential stops for stopcode {$obsv["stop_code"]} street {$obsv["stop_street"]} unknown"; | $timing_period = "sunday"; |
| continue; | } |
| } | |
| //print out stops | if (isset($obsv["stop_id"]) && $obsv["stop_id"] != "" && $obsv["stop_id"] != "*") { |
| echo "Matched stops: "; | $potentialStops = Array(getStop($obsv["stop_id"])); |
| foreach ($potentialStops as $potentialStop) echo $potentialStop['stop_code'] . " "; | } else { |
| echo "<br>"; | echo "No stop_id recorded for this stop_name, potential stops are a bus station<br>"; |
| //:get myway_route record | $potentialStops = getStops("", trim(str_replace(Array("Arrival","Arrivals","Arrive Platform 3 Set down only.","Arrive","Set Down Only"), "", $obsv["myway_stop"]))); |
| //no result, skip and display error | } |
| //print out route | //:get myway_stops records |
| $potentialRoute = getRouteByFullName($obsv["route_full_name"]); | //:search by starts with stopcode and starts with street if street is not null |
| if ($potentialRoute["route_short_name"] == "") { | //no result, skip and display error |
| echo "error, route '{$obsv["route_full_name"]}' unknown"; | if (sizeof($potentialStops) < 1) { |
| continue; | echo "error, potential stops for stopid {$obsv["stop_id"]} unknown"; |
| } | continue; |
| echo "Matched route: {$potentialRoute['route_short_name']}{$potentialRoute['route_long_name']} {$timing_period}<br>"; | } |
| $timeDeltas = Array(); | //print out stops |
| foreach ($potentialStops as $potentialStop) { | echo "Matched stops: "; |
| $stopRoutes = getStopRoutes($potentialStop['stop_id'], $timing_period); | foreach ($potentialStops as $potentialStop) { |
| $foundRoute = Array(); | echo $potentialStop['stop_id'] . " " . $potentialStop['stop_name'] . " "; |
| foreach ($stopRoutes as $stopRoute) { | } |
| //Check if this route stops at each stop | echo "<br>"; |
| if ($stopRoute['route_short_name'] . $stopRoute['route_long_name'] == $obsv["route_full_name"]) { | //:get myway_route record |
| echo "Matching route {$stopRoute['route_id']} found at {$potentialStop['stop_code']}<br>"; | //no result, skip and display error |
| $foundRoute = $stopRoute; | //print out route |
| //if does get tripstoptimes for this route | $potentialRoutes = getRoutesByShortName($obsv["route_short_name"]); |
| $trips = getStopTrips($potentialStop['stop_id'], $timing_period, $search_time); | if (sizeof($potentialRoutes) < 1) { |
| foreach ($trips as $trip) { | echo "error, route '{$obsv["myway_route"]}' unknown"; |
| //echo $trip['route_id']." ".$stopRoute['route_id'].";"; | continue; |
| if ($trip['route_id'] == $stopRoute['route_id']) { | } |
| $timedTrip = getTimeInterpolatedTripAtStop($trip['trip_id'], $trip['stop_sequence']); | $timeDeltas = Array(); |
| $actual_time = strtotime($time); | foreach ($potentialRoutes as $potentialRoute) { |
| $trip_time = strtotime($timedTrip['arrival_time']); | echo "Matched route: {$potentialRoute['route_id']} {$potentialRoute['route_short_name']} {$timing_period}<br>"; |
| $timeDiff = $actual_time - $trip_time; | foreach ($potentialStops as $potentialStop) { |
| //work out time delta, put into array with index of delta | $stopRoutes = getStopRoutes($potentialStop['stop_id'], $timing_period); |
| $timeDeltas[] = Array( | $foundRoute = Array(); |
| "timeDiff" => $timeDiff, | foreach ($stopRoutes as $stopRoute) { |
| "stop_code" => $potentialStop['stop_code'], | //Check if this route stops at each stop |
| "stop_sequence" => $timedTrip['stop_sequence'] | if ($stopRoute['route_id'] == $potentialRoute['route_id']) { |
| ); | echo "Matching route {$stopRoute['route_id']} found at stop #{$potentialStop['stop_id']}<br>"; |
| echo "Found trip {$trip['trip_id']} at stop {$potentialStop['stop_code']} (#{$potentialStop['stop_id']}, sequence #{$trip['stop_sequence']})<br>"; | $foundRoute = $stopRoute; |
| echo "Arriving at {$timedTrip['arrival_time']}, difference of " . round($timeDiff / 60, 2) . " minutes<br>"; | //if does get tripstoptimes for this route |
| } | $trips = getStopTrips($potentialStop['stop_id'], $timing_period, $search_time, 10, $potentialRoute['route_short_name']); |
| } | foreach ($trips as $trip) { |
| break; // because have found route | //echo $trip['route_id']." ".$stopRoute['route_id'].";"; |
| if ($trip['route_id'] == $stopRoute['route_id']) { | |
| } | $timedTrip = getTripAtStop($trip['trip_id'], $trip['stop_sequence']); |
| } | $actual_time = strtotime($time); |
| if (sizeof($foundRoute) < 1) { | $trip_time = strtotime($timedTrip['arrival_time']); |
| //print out that stops/does not stop | $timeDiff = $actual_time - $trip_time; |
| echo "No matching routes found at {$potentialStop['stop_code']}<br>"; | //work out time delta, put into array with index of delta |
| var_dump($stopRoutes); | $timeDeltas[] = Array( |
| flush(); | "timeDiff" => $timeDiff, |
| "stop_id" => $potentialStop['stop_id'], | |
| } | "stop_sequence" => $trip['stop_sequence'], |
| } | "route_name" => "{$trip['route_short_name']} {$trip['trip_headsign']}", |
| // lowest delta is recorded delta | "route_id" => $trip['route_id'] |
| usort($timeDeltas, "abssort"); | ); |
| $lowestDelta = $timeDeltas[0]["timeDiff"]; | echo "Found trip {$trip['trip_id']} at stop {$potentialStop['stop_id']} (#{$potentialStop['stop_name']}, sequence #{$trip['stop_sequence']})<br>"; |
| if (sizeof($timeDeltas) != 0) { | echo "Arriving at {$timedTrip['arrival_time']}, difference of " . round($timeDiff / 60, 2) . " minutes<br>"; |
| echo "Lowest difference of " . round($lowestDelta / 60, 2) . " minutes will be recorded for this observation<br>"; | } else { |
| $observation_id = $obsv['observation_id']; | echo "{$trip['route_id']} != {$stopRoute['route_id']}<br>"; |
| $route_full_name = $obsv['route_full_name']; | } |
| $stop_code = $timeDeltas[0]["stop_code"]; | } |
| $stop_sequence = $timeDeltas[0]["stop_sequence"]; | if (sizeof($timeDeltas) == 0) echo "Error, no trips found.<bR>"; |
| $stmt = $conn->prepare("insert into myway_timingdeltas (observation_id, route_full_name, stop_code, timing_delta, time, date, timing_period, stop_sequence) | break; // because have found route |
| values (:observation_id, :route_full_name, :stop_code, :timing_delta, :time, :date, :timing_period, :stop_sequence)"); | } |
| $stmt->bindParam(':observation_id', $observation_id); | } |
| $stmt->bindParam(':route_full_name', $route_full_name); | if (sizeof($foundRoute) < 1) { |
| $stmt->bindParam(':stop_code', $stop_code); | //print out that stops/does not stop |
| $stmt->bindParam(':timing_delta', $lowestDelta); | echo "No matching routes found at {$potentialStop['stop_id']}<br>"; |
| $stmt->bindParam(':time', $time_tz); | //var_dump($stopRoutes); |
| $stmt->bindParam(':date', $date); | flush(); |
| $stmt->bindParam(':timing_period', $timing_period); | } |
| $stmt->bindParam(':stop_sequence', $stop_sequence); | } |
| // insert a record | } |
| $stmt->execute(); | |
| if ($stmt->rowCount() > 0) { | // lowest delta is recorded delta |
| echo "Recorded.<br>"; | usort($timeDeltas, "abssort"); |
| } | $lowestDelta = $timeDeltas[0]["timeDiff"]; |
| var_dump($conn->errorInfo()); | if (sizeof($timeDeltas) != 0) { |
| flush(); | if (abs($lowestDelta) > 9999) { |
| } | echo "Difference of " . round($lowestDelta / 60, 2) . " minutes is too high. Will not record this observation<br>"; |
| flush(); | } else { |
| echo "Lowest difference of " . round($lowestDelta / 60, 2) . " minutes will be recorded for this observation<br>"; | |
| $observation_id = $obsv['observation_id']; | |
| $route_name = $timeDeltas[0]["route_name"]; | |
| $route_id = $timeDeltas[0]["route_id"]; | |
| $stop_id = $timeDeltas[0]["stop_id"]; | |
| $myway_stop = $obsv["myway_stop"]; | |
| $stop_sequence = $timeDeltas[0]["stop_sequence"]; | |
| $stmt = $conn->prepare("insert into myway_timingdeltas (observation_id, route_id, stop_id, timing_delta, time, date, timing_period, stop_sequence,myway_stop,route_name) | |
| values (:observation_id, :route_id, :stop_id, :timing_delta, :time, :date, :timing_period, :stop_sequence,:myway_stop,:route_name)"); | |
| $stmt->bindParam(':observation_id', $observation_id); | |
| $stmt->bindParam(':route_id', $route_id); | |
| $stmt->bindParam(':route_name', $route_name); | |
| $stmt->bindParam(':stop_id', $stop_id); | |
| $stmt->bindParam(':myway_stop', $myway_stop); | |
| $stmt->bindParam(':timing_delta', $lowestDelta); | |
| $stmt->bindParam(':time', $time_tz); | |
| $stmt->bindParam(':date', $date); | |
| $stmt->bindParam(':timing_period', $timing_period); | |
| $stmt->bindParam(':stop_sequence', $stop_sequence); | |
| // insert a record | |
| $stmt->execute(); | |
| if ($stmt->rowCount() > 0) { | |
| echo "Recorded.<br>"; | |
| } | |
| var_dump($conn->errorInfo()); | |
| flush(); | |
| } | |
| } | |
| flush(); | |
| } | } |