Licence and jqmobile beta 3 upgrade
[busui.git] / include / db / stop-dao.inc.php
blob:a/include/db/stop-dao.inc.php -> blob:b/include/db/stop-dao.inc.php
--- 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);
@@ -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 = "%<br>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,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;
                 }