1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 | <?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_header("MyWay Delta Calculate", "mywayDeltaCalc"); flush(); ob_flush(); function abssort($a, $b) { if ($a['timeDiff'] == $b['timeDiff']) { return 0; } return (abs($a['timeDiff']) < abs($b['timeDiff'])) ? -1 : 1; } //collect all observation not in delta $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_route=myway_routes.myway_route WHERE observation_id NOT IN ( SELECT observation_id FROM myway_timingdeltas )"; debug($query, "database"); $query = $conn->prepare($query); $query->execute(); if (!$query) { databaseError($conn->errorInfo()); return Array(); } $uncalcdObservations = $query->fetchAll(); //Display count echo "<h3>" . sizeof($uncalcdObservations) . " observations not yet processed</h2>"; //foreach observation not in delta foreach ($uncalcdObservations as $obsv) { //var_dump($obsv); echo "<h3>Observation {$obsv['observation_id']}:</h1> <small>{$obsv['myway_stop']} @ {$obsv['time']} on {$obsv['myway_route']}</small><br>"; // convert timestamp into time of day and date // timezones from http://www.postgresql.org/docs/8.0/static/datetime-keywords.html $time = date("H:i:s", strtotime($obsv['time'])); $time_tz = date("H:i:s", strtotime($obsv['time'])) . " AESST"; $search_time = date("H:i:s", strtotime($obsv['time']) - (60 * 60)); // 30 minutes margin $date = date("c", strtotime($obsv['time'])); $timing_period = service_period(strtotime($date)); if (isset($obsv["stop_id"]) && $obsv["stop_id"] != "" && $obsv["stop_id"] != "*") { $potentialStops = Array(getStop($obsv["stop_id"])); } else { echo "Potential stops are a bus station<br>"; $potentialStops = getStops("", trim(str_replace(Array("Arrival","Arrivals","Arrive Platform 3 Set down only.","Arrive","Set Down Only"), "", $obsv["myway_stop"]))); } //:get myway_stops records //:search by starts with stopcode and starts with street if street is not null //no result, skip and display error if (sizeof($potentialStops) < 1) { echo "error, potential stops for stopid {$obsv["stop_id"]} unknown"; continue; } //print out stops echo "Matched stops: "; foreach ($potentialStops as $potentialStop) { echo $potentialStop['stop_id'] . " " . $potentialStop['stop_name'] . " "; } echo "<br>"; //:get myway_route record //no result, skip and display error //print out route $potentialRoutes = getRoutesByShortName($obsv["route_short_name"]); if (sizeof($potentialRoutes) < 1) { echo "error, route '{$obsv["myway_route"]}' unknown"; continue; } $timeDeltas = Array(); foreach ($potentialRoutes as $potentialRoute) { echo "Matched route: {$potentialRoute['route_id']} {$potentialRoute['route_short_name']}{$potentialRoute['route_long_name']} {$timing_period}<br>"; foreach ($potentialStops as $potentialStop) { $stopRoutes = getStopRoutes($potentialStop['stop_id'], $timing_period); $foundRoute = Array(); foreach ($stopRoutes as $stopRoute) { //Check if this route stops at each stop if ($stopRoute['route_id'] == $potentialRoute['route_id']) { echo "Matching route {$stopRoute['route_id']} found at stop #{$potentialStop['stop_id']}<br>"; $foundRoute = $stopRoute; //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) { //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); $trip_time = strtotime($timedTrip['arrival_time']); $timeDiff = $actual_time - $trip_time; //work out time delta, put into array with index of delta $timeDeltas[] = Array( "timeDiff" => $timeDiff, "stop_id" => $potentialStop['stop_id'], "stop_sequence" => $trip['stop_sequence'], "route_name" => "{$trip['route_short_name']} {$trip['trip_headsign']}", "route_id" => $trip['route_id'] ); echo "Found trip {$trip['trip_id']} at stop {$potentialStop['stop_id']} (#{$potentialStop['stop_name']}, sequence #{$trip['stop_sequence']})<br>"; echo "Arriving at {$timedTrip['arrival_time']}, difference of " . round($timeDiff / 60, 2) . " minutes<br>"; } else { echo "{$trip['route_id']} != {$stopRoute['route_id']}<br>"; } } if (sizeof($timeDeltas) == 0) echo "Error, no trips found.<bR>"; break; // because have found route } } if (sizeof($foundRoute) < 1) { //print out that stops/does not stop echo "No matching routes found at {$potentialStop['stop_id']}<br>"; //var_dump($stopRoutes); flush(); } } } // lowest delta is recorded delta usort($timeDeltas, "abssort"); $lowestDelta = $timeDeltas[0]["timeDiff"]; if (sizeof($timeDeltas) != 0) { if (abs($lowestDelta) > 9999) { echo "Difference of " . round($lowestDelta / 60, 2) . " minutes is too high. Will not record this observation<br>"; } 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-> |