Merge branch 'master' of github.com:maxious/ACTBus-ui
[busui.git] / include / db / route-dao.inc.php
blob:a/include/db/route-dao.inc.php -> blob:b/include/db/route-dao.inc.php
--- a/include/db/route-dao.inc.php
+++ b/include/db/route-dao.inc.php
@@ -29,6 +29,7 @@
     }
     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";
@@ -45,8 +46,11 @@
 
 function getRouteHeadsigns($routeID) {
     global $conn;
-    $query = "select distinct stops.stop_name, trip_headsign,direction_id 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";
+    $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);
@@ -55,9 +59,14 @@
         databaseError($conn->errorInfo());
         return Array();
     }
-return $query->fetchAll();
-}
-
+    return $query->fetchAll();
+}
+function getRouteDescription($routeID) {
+    $trip = getRouteNextTrip($routeID);
+    $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";
@@ -85,7 +94,7 @@
     return $query->fetchAll();
 }
 
-function getRoutesByNumber($routeNumber = "") {
+function getRoutesByNumber($routeNumber = "", $directionID = "",$service_period = "") {
     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 =
@@ -134,15 +143,16 @@
 
 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
+   
+    $query = "select routes.route_id,direction_id,trips.trip_id,departure_time 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
 arrival_time limit 1";
     debug($query, "database");
     $query = $conn->prepare($query);
     $query->bindParam(":currentTime", current_time());
     $query->bindParam(":routeID", $routeID);
     $query->execute();
+    databaseError($conn->errorInfo());
     if (!$query) {
         databaseError($conn->errorInfo());
         return Array();
@@ -179,40 +189,61 @@
     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 getRoutesTrips($routeIDs, $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 = :routeIDA OR routes.route_id = :routeIDB) " . $directionSQL . " and stop_sequence = '1' order by
+arrival_time ";
+    debug($query, "database");
+    $query = $conn->prepare($query);
+    $query->bindParam(":routeIDA", $routeIDs[0]);
+    $query->bindParam(":routeIDB", $routeIDs[1]);
+    $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
 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";
     } 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);
+WHERE (service_id=:service_periodA OR service_id=:service_periodB)
+ order by route_long_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();
@@ -226,29 +257,34 @@
 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 == "")
         $service_period = service_period();
-        $service_ids = service_ids($service_period);
+    $service_ids = service_ids($service_period);
     $sidA = $service_ids[0];
     $sidB = $service_ids[1];
     if ($limit != "")