--- a/include/db/route-dao.inc.php +++ b/include/db/route-dao.inc.php @@ -30,6 +30,48 @@ return $query->fetch(PDO :: FETCH_ASSOC); } +function getRoutesByShortName($routeShortName) { + global $conn; + $query = "Select distinct route_id, route_short_name from routes where route_short_name = :routeShortName"; + debug($query, "database"); + $query = $conn->prepare($query); + $query->bindParam(":routeShortName", $routeShortName); + $query->execute(); + if (!$query) { + databaseError($conn->errorInfo()); + return Array(); + } + return $query->fetchAll(); +} + +function getRouteHeadsigns($routeID) { + global $conn; + $query = "select stops.stop_name, trip_headsign, direction_id,max(service_id) as service_id, count(*) + from routes join trips on trips.route_id = routes.route_id +join stop_times on stop_times.trip_id = trips.trip_id join stops on +stop_times.stop_id = stops.stop_id where trips.route_id = :routeID +and stop_times.stop_sequence = 1 group by stops.stop_name, trip_headsign, direction_id having count(*) > 2"; + debug($query, "database"); + $query = $conn->prepare($query); + $query->bindParam(":routeID", $routeID); + $query->execute(); + if (!$query) { + databaseError($conn->errorInfo()); + return Array(); + } + $results = $query->fetchAll(); + if (is_array($results)) { + return $results; + } else { + return Array($results); + } +} +function getRouteDescription($routeID, $directionID) { + $trip = getRouteNextTrip($routeID, $directionID); + $start = getTripStartingPoint($trip['trip_id']); + $end = getTripDestination($trip['trip_id']); + return "From ".$start['stop_name']." to ".$end['stop_name']; +} function getRouteByFullName($routeFullName) { global $conn; $query = "Select * from routes where route_short_name||route_long_name = :routeFullName LIMIT 1"; @@ -57,34 +99,10 @@ return $query->fetchAll(); } -function getRoutesByNumber($routeNumber = "") { - global $conn; - if ($routeNumber != "") { - $query = "Select distinct routes.route_id,routes.route_short_name,routes.route_long_name,service_id from routes join trips on trips.route_id = -routes.route_id join stop_times on stop_times.trip_id = trips.trip_id -where route_short_name = :routeNumber OR route_short_name LIKE :routeNumber2 order by route_short_name;"; - } else { - $query = "SELECT DISTINCT route_short_name from routes order by route_short_name"; - } - debug($query, "database"); - $query = $conn->prepare($query); - if ($routeNumber != "") { - $query->bindParam(":routeNumber", $routeNumber); - $routeNumber2 = "% " . $routeNumber; - $query->bindParam(":routeNumber2", $routeNumber2); - } - $query->execute(); - if (!$query) { - databaseError($conn->errorInfo()); - return Array(); - } - return $query->fetchAll(); -} - function getRoutesByNumberSeries($routeNumberSeries = "") { global $conn; if (strlen($routeNumberSeries) == 1) { - return getRoutesByNumber($routeNumberSeries); + return getRoute($routeNumberSeries); } $seriesMin = substr($routeNumberSeries, 0, -1) . "0"; $seriesMax = substr($routeNumberSeries, 0, -1) . "9"; @@ -104,31 +122,43 @@ return $query->fetchAll(); } -function getRouteNextTrip($routeID) { - global $conn; - $query = "select * from routes join trips on trips.route_id = routes.route_id -join stop_times on stop_times.trip_id = trips.trip_id where -arrival_time > :currentTime and routes.route_id = :routeID order by +function getRouteNextTrip($routeID, $directionID) { + global $conn; + + $query = "select routes.route_id,routes.route_url,direction_id,trips.trip_id,trip_headsign,departure_time,service_id from routes join trips on trips.route_id = routes.route_id +join stop_times on stop_times.trip_id = trips.trip_id where arrival_time between :currentTime and :futureTime +and routes.route_id = :routeID and trips.direction_id = :directionID order by arrival_time limit 1"; debug($query, "database"); $query = $conn->prepare($query); $query->bindParam(":currentTime", current_time()); + $futureTime = current_time(strtotime(current_time() ." +2h")); + if (date("h",strtotime(current_time()) > 22)) $futureTime = "23:59:59"; + $query->bindParam(":futureTime", $futureTime); $query->bindParam(":routeID", $routeID); - $query->execute(); + $query->bindParam(":directionID", $directionID); + $query->execute(); + databaseError($conn->errorInfo()); if (!$query) { databaseError($conn->errorInfo()); return Array(); } $r = $query->fetch(PDO :: FETCH_ASSOC); - - // past last trip of the day special case - if (sizeof($r) < 16) { - $query = "select * from routes join trips on trips.route_id = routes.route_id -join stop_times on stop_times.trip_id = trips.trip_id where routes.route_id = :routeID order by + return $r; +} + +function getRouteFirstTrip($routeID,$directionID) { + global $conn; + + $query = "select * from routes join trips on trips.route_id = routes.route_id +join stop_times on stop_times.trip_id = trips.trip_id where routes.route_id = :routeID +and trips.direction_id = :directionID order by arrival_time DESC limit 1"; debug($query, "database"); $query = $conn->prepare($query); $query->bindParam(":routeID", $routeID); + + $query->bindParam(":directionID", $directionID); $query->execute(); if (!$query) { databaseError($conn->errorInfo()); @@ -136,14 +166,13 @@ } $r = $query->fetch(PDO :: FETCH_ASSOC); - } - return $r; -} - -function getTimeInterpolatedRouteAtStop($routeID, $stop_id) { - $nextTrip = getRouteNextTrip($routeID); + return $r; +} + +function getRouteAtStop($routeID, $directionID, $stop_id) { + $nextTrip = getRouteNextTrip($routeID, $directionID); if ($nextTrip['trip_id']) { - foreach (getTimeInterpolatedTrip($nextTrip['trip_id']) as $tripStop) { + foreach (getTripStopTimes($nextTrip['trip_id']) as $tripStop) { if ($tripStop['stop_id'] == $stop_id) return $tripStop; } @@ -151,40 +180,59 @@ return Array(); } -function getRouteTrips($routeID) { - global $conn; - $query = "select routes.route_id,trips.trip_id,service_id,arrival_time, stop_id, stop_sequence from routes join trips on trips.route_id = routes.route_id -join stop_times on stop_times.trip_id = trips.trip_id where routes.route_id = :routeID and stop_sequence = '1' order by -arrival_time "; - debug($query, "database"); - $query = $conn->prepare($query); - $query->bindParam(":routeID", $routeID); - $query->execute(); - if (!$query) { - databaseError($conn->errorInfo()); - return Array(); - } - return $query->fetchAll(); -} - -function getRoutesByDestination($destination = "", $service_period = "") { +function getRouteTrips($routeID, $directionID = "", $service_period = "") { global $conn; if ($service_period == "") $service_period = service_period(); + $service_ids = service_ids($service_period); + $sidA = $service_ids[0]; + $sidB = $service_ids[1]; + $directionSQL = ""; + if ($directionID != "") + $directionSQL = " and direction_id = :directionID "; + $query = "select routes.route_id,trips.trip_id,service_id,arrival_time, stop_id, stop_sequence from routes join trips on trips.route_id = routes.route_id +join stop_times on stop_times.trip_id = trips.trip_id where (service_id=:service_periodA OR service_id=:service_periodB) +AND (routes.route_id = :routeID) " . $directionSQL . " and stop_sequence = '1' order by +arrival_time "; + debug($query, "database"); + $query = $conn->prepare($query); + $query->bindParam(":routeID", $routeID); + $query->bindParam(":service_periodA", $sidA); + $query->bindParam(":service_periodB", $sidB); + if ($directionSQL != "") + $query->bindParam(":directionID", $directionID); + $query->execute(); + if (!$query) { + databaseError($conn->errorInfo()); + return Array(); + } + return $query->fetchAll(); +} + +function getRoutesByDestination($destination = "", $service_period = "") { + global $conn; + if ($service_period == "") + $service_period = service_period(); + $service_ids = service_ids($service_period); + $sidA = $service_ids[0]; + $sidB = $service_ids[1]; if ($destination != "") { - $query = "SELECT DISTINCT trips.route_id,route_short_name,route_long_name, service_id + /* $query = "SELECT DISTINCT trips.route_id,route_short_name,route_long_name, service_id FROM stop_times join trips on trips.trip_id = stop_times.trip_id join routes on trips.route_id = routes.route_id -WHERE route_long_name = :destination AND service_id=:service_period order by route_short_name"; +WHERE route_long_name = :destination AND (service_id=:service_periodA OR service_id=:service_periodB) + order by route_short_name";*/ + $query = "select route_id, direction_id, stop_name, b.trip_id, b.stop_sequence from (select route_id, direction_id, max(stop_sequence) as stop_sequence, max(a.trip_id) as trip_id from stop_times inner join (SELECT route_id, direction_id, max(trip_id) as trip_id + from trips group by route_id,direction_id) as a on stop_times.trip_id = a.trip_id group by route_id, direction_id) as b inner join stop_times on b.trip_id = stop_times.trip_id inner join stops on stop_times.stop_id = stops.stop_id where stop_times.stop_sequence = b.stop_sequence and stop_name = :destination order by route_id;"; } else { - $query = "SELECT DISTINCT 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 service_id= :service_period order by route_long_name"; - } - debug($query, "database"); - $query = $conn->prepare($query); - $query->bindParam(":service_period", $service_period); + $query = "select stop_name from (select route_id, direction_id, max(stop_sequence) as stop_sequence, max(a.trip_id) as trip_id from stop_times inner join (SELECT route_id, direction_id, max(trip_id) as trip_id + from trips group by route_id,direction_id) as a on stop_times.trip_id = a.trip_id group by route_id, direction_id) as b inner join stop_times on b.trip_id = stop_times.trip_id inner join stops on stop_times.stop_id = stops.stop_id where stop_times.stop_sequence = b.stop_sequence group by stop_name order by stop_name;"; + } + debug($query, "database"); + $query = $conn->prepare($query); + + //$query->bindParam(":service_periodA", $sidA); + //$query->bindParam(":service_periodB", $sidB); if ($destination != "") $query->bindParam(":destination", $destination); $query->execute(); @@ -198,44 +246,54 @@ function getRoutesBySuburb($suburb, $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 join stops on stops.stop_id = stop_times.stop_id -WHERE zone_id LIKE ':suburb AND service_id=:service_period ORDER BY route_short_name"; - debug($query, "database"); - $query = $conn->prepare($query); - $query->bindParam(":service_period", $service_period); - $suburb = "%" . $suburb . ";%"; +WHERE stop_desc LIKE :suburb AND (service_id=:service_periodA OR service_id=:service_periodB) + ORDER BY route_short_name"; + debug($query, "database"); + $query = $conn->prepare($query); + $query->bindParam(":service_periodA", $sidA); + $query->bindParam(":service_periodB", $sidB); + $suburb = "%Suburb: %" . $suburb . "%"; $query->bindParam(":suburb", $suburb); $query->execute(); - if (!$query) { - databaseError($conn->errorInfo()); - return Array(); - } + + databaseError($conn->errorInfo()); + return $query->fetchAll(); } function getRoutesNearby($lat, $lng, $limit = "", $distance = 500) { - if ($service_period == "") + // if ($service_period == "") $service_period = service_period(); - if ($limit != "") + $service_ids = service_ids($service_period); + $sidA = $service_ids[0]; + $sidB = $service_ids[1]; + $limitSQL = ""; + if ($limit != "") $limitSQL = " LIMIT :limit "; global $conn; - $query = "SELECT service_id,trips.route_id,route_short_name,route_long_name,min(stops.stop_id) as stop_id, + $query = "SELECT service_id,trips.route_id,trips.direction_id,route_short_name,route_long_name,min(stops.stop_id) as stop_id, min(ST_Distance(position, ST_GeographyFromText('SRID=4326;POINT($lng $lat)'), FALSE)) as distance FROM stop_times join trips on trips.trip_id = stop_times.trip_id join routes on trips.route_id = routes.route_id join stops on stops.stop_id = stop_times.stop_id -WHERE service_id=:service_period +WHERE (service_id=:service_periodA OR service_id=:service_periodB) AND ST_DWithin(position, ST_GeographyFromText('SRID=4326;POINT($lng $lat)'), :distance, FALSE) - group by service_id,trips.route_id,route_short_name,route_long_name + group by service_id,trips.route_id,trips.direction_id,route_short_name,route_long_name order by distance $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(":distance", $distance); if ($limit != "") $query->bindParam(":limit", $limit); @@ -247,4 +305,3 @@ return $query->fetchAll(); } -?>