--- a/include/db/stop-dao.inc.php +++ b/include/db/stop-dao.inc.php @@ -30,11 +30,9 @@ return $query->fetch(PDO :: FETCH_ASSOC); } -function getStops($timingPointsOnly = false, $firstLetter = "", $startsWith = "") { +function getStops($firstLetter = "", $startsWith = "") { global $conn; $conditions = Array(); - if ($timingPointsOnly) - $conditions[] = "substr(stop_code,1,2) != 'Wj'"; if ($firstLetter != "") $conditions[] = "substr(stop_name,1,1) = :firstLetter"; if ($startsWith != "") @@ -48,6 +46,7 @@ } } $query .= " order by stop_name;"; + debug($query, "database"); $query = $conn->prepare($query); if ($firstLetter != "") $query->bindParam(":firstLetter", $firstLetter); @@ -90,7 +89,7 @@ $query = "Select * from stops where stop_name LIKE :name;"; debug($query, "database"); $query = $conn->prepare($query); - $name = "%" . $name . ";%"; + $name = $name . "%"; $query->bindParam(":name", $name); $query->execute(); if (!$query) { @@ -102,10 +101,10 @@ function getStopsBySuburb($suburb) { global $conn; - $query = "Select * from stops where zone_id LIKE :suburb order by stop_name;"; - debug($query, "database"); - $query = $conn->prepare($query); - $suburb = "%" . $suburb . ";%"; + $query = "Select * from stops where stop_desc LIKE :suburb order by stop_name;"; + debug($query, "database"); + $query = $conn->prepare($query); + $suburb = "%
Suburb: %" . $suburb . "%"; $query->bindParam(":suburb", $suburb); $query->execute(); if (!$query) { @@ -140,15 +139,21 @@ } function getStopRoutes($stopID, $service_period) { - if ($service_period == "") + if ($service_period == "") { $service_period = service_period(); + } + $service_ids = service_ids($service_period); + $sidA = $service_ids[0]; + $sidB = $service_ids[1]; global $conn; $query = "SELECT distinct service_id,trips.route_id,route_short_name,route_long_name FROM stop_times join trips on trips.trip_id = -stop_times.trip_id join routes on trips.route_id = routes.route_id WHERE stop_id = :stopID AND service_id=:service_period"; - debug($query, "database"); - $query = $conn->prepare($query); - $query->bindParam(":service_period", $service_period); +stop_times.trip_id join routes on trips.route_id = routes.route_id WHERE stop_id = :stopID +AND (service_id=:service_periodA OR service_id=:service_periodB)"; + debug($query, "database"); + $query = $conn->prepare($query); + $query->bindParam(":service_periodA", $sidA); + $query->bindParam(":service_periodB", $sidB); $query->bindParam(":stopID", $stopID); $query->execute(); if (!$query) { @@ -158,14 +163,20 @@ return $query->fetchAll(); } -function getStopTrips($stopID, $service_period = "", $afterTime = "", $limit = "") { - if ($service_period == "") +function getStopTrips($stopID, $service_period = "", $afterTime = "", $limit = "", $route_short_name = "") { + if ($service_period == "") { $service_period = service_period(); + } + $service_ids = service_ids($service_period); + $sidA = $service_ids[0]; + $sidB = $service_ids[1]; + $limitSQL = ""; if ($limit != "") - $limitSQL = " LIMIT :limit "; + $limitSQL .= " LIMIT :limit "; + global $conn; if ($afterTime != "") { - $query = " SELECT stop_times.trip_id,stop_times.arrival_time,stop_times.stop_id,stop_sequence,service_id,trips.route_id,route_short_name,route_long_name, end_times.arrival_time as end_time + $query = " SELECT stop_times.trip_id,stop_times.arrival_time,stop_times.stop_id,stop_sequence,service_id,trips.route_id,trips.direction_id,trips.trip_headsign,route_short_name,route_long_name,end_times.arrival_time as end_time FROM stop_times join trips on trips.trip_id = stop_times.trip_id @@ -173,7 +184,7 @@ WHERE stop_times.arrival_time IS NOT NULL group by trip_id) as end_times WHERE stop_times.stop_id = :stopID AND stop_times.trip_id = end_times.trip_id -AND service_id=:service_period +AND (service_id=:service_periodA OR service_id=:service_periodB) " . ($route_short_name != "" ? " AND route_short_name = :route_short_name " : "") . " AND end_times.arrival_time > :afterTime ORDER BY end_time $limitSQL"; } else { @@ -183,17 +194,20 @@ stop_times.trip_id join routes on trips.route_id = routes.route_id WHERE stop_times.stop_id = :stopID -AND service_id=:service_period +AND (service_id=:service_periodA OR service_id=:service_periodB) " . ($route_short_name != "" ? " AND route_short_name = :route_short_name " : "") . " ORDER BY arrival_time $limitSQL"; } debug($query, "database"); $query = $conn->prepare($query); - $query->bindParam(":service_period", $service_period); + $query->bindParam(":service_periodA", $sidA); + $query->bindParam(":service_periodB", $sidB); $query->bindParam(":stopID", $stopID); if ($limit != "") $query->bindParam(":limit", $limit); if ($afterTime != "") $query->bindParam(":afterTime", $afterTime); + if ($route_short_name != "") + $query->bindParam(":route_short_name", $route_short_name); $query->execute(); if (!$query) { databaseError($conn->errorInfo()); @@ -220,7 +234,7 @@ $timedTrips[] = $trip; } } else { - $timedTrip = getTimeInterpolatedTripAtStop($trip['trip_id'], $trip['stop_sequence']); + $timedTrip = getTripAtStop($trip['trip_id'], $trip['stop_sequence']); if ($timedTrip['arrival_time'] > $time and strtotime($timedTrip['arrival_time']) < (strtotime($time) + $time_range)) { $timedTrips[] = $timedTrip; } @@ -233,4 +247,3 @@ return $timedTrips; } -?>