Refactor stop page
[busui.git] / include / db / trip-dao.inc.php
blob:a/include/db/trip-dao.inc.php -> blob:b/include/db/trip-dao.inc.php
<?php <?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.
  */
   
function getTrip($tripID) { function getTrip($tripID) {
global $conn; global $conn;
$query = "Select * from trips where trip_id = '$tripID' join routes on trips.route_id = routes.route_id LIMIT 1"; $query = "Select * from trips
debug($query,"database"); join routes on trips.route_id = routes.route_id
$result = pg_query($conn, $query); where trip_id = :tripID
if (!$result) { LIMIT 1";
databaseError(pg_result_error($result)); debug($query, "database");
return Array(); $query = $conn->prepare($query);
} $query->bindParam(":tripID", $tripID);
return pg_fetch_assoc($result); $query->execute();
} if (!$query) {
function getTripShape() { databaseError($conn->errorInfo());
/* def handle_json_GET_tripstoptimes(self, params):  
schedule = self.server.schedule  
try:  
trip = schedule.GetTrip(params.get('trip'))  
except KeyError:  
# if a non-existent trip is searched for, the return nothing  
return  
time_stops = trip.GetTimeInterpolatedStops()  
stops = []  
times = []  
for arr,ts,is_timingpoint in time_stops:  
stops.append(StopToTuple(ts.stop))  
times.append(arr)  
return [stops, times]  
   
def handle_json_GET_tripshape(self, params): return Array();
schedule = self.server.schedule }
try: return $query->fetch(PDO :: FETCH_ASSOC);
trip = schedule.GetTrip(params.get('trip'))  
except KeyError:  
# if a non-existent trip is searched for, the return nothing  
return  
points = []  
if trip.shape_id:  
shape = schedule.GetShape(trip.shape_id)  
for (lat, lon, dist) in shape.points:  
points.append((lat, lon))  
else:  
time_stops = trip.GetTimeStops()  
for arr,dep,stop in time_stops:  
points.append((stop.stop_lat, stop.stop_lon))  
return points*/  
} }
function getTimeInterpolatedTrip($tripID) { function getTripStops($tripID) {
/* rv = [] global $conn;
  $query = "SELECT stop_id, stop_name, ST_AsKML(position) as positionkml,
  stop_sequence, trips.trip_id
  FROM stop_times
  join trips on trips.trip_id = stop_times.trip_id
  join stops on stops.stop_id = stop_times.stop_id
  WHERE trips.trip_id = :tripID ORDER BY stop_sequence";
  debug($query, "database");
  $query = $conn->prepare($query);
  $query->bindParam(":tripID", $tripID);
  $query->execute();
  if (!$query) {
  databaseError($conn->errorInfo());
  return Array();
  }
  return $query->fetchColumn(0);
  }
  function getTripShape($tripID) {
  // todo, use shapes table if shape_id specified
  global $conn;
  $query = "SELECT ST_AsKML(ST_MakeLine(geometry(a.shape_pt))) as the_route
  FROM (SELECT shapes.shape_id,shape_pt from shapes
  inner join trips on shapes.shape_id = trips.shape_id
  WHERE trips.trip_id = :tripID ORDER BY shape_pt_sequence) as a group by a.shape_id";
  debug($query, "database");
  $query = $conn->prepare($query);
  $query->bindParam(":tripID", $tripID);
  $query->execute();
  if (!$query) {
  databaseError($conn->errorInfo());
  return Array();
  }
  return $query->fetchColumn(0);
  }
   
stoptimes = self.GetStopTimes() function getTripStopTimes($tripID) {
# If there are no stoptimes [] is the correct return value but if the start global $conn;
# or end are missing times there is no correct return value. $query = "SELECT stop_times.trip_id,trip_headsign,arrival_time,stop_times.stop_id
if not stoptimes: ,stop_lat,stop_lon,stop_name,stop_desc,stop_code,
return [] stop_sequence,service_id,trips.route_id,route_short_name,route_long_name
if (stoptimes[0].GetTimeSecs() is None or FROM stop_times
stoptimes[-1].GetTimeSecs() is None): join trips on trips.trip_id = stop_times.trip_id
raise ValueError("%s must have time at first and last stop" % (self)) join routes on trips.route_id = routes.route_id
  join stops on stops.stop_id = stop_times.stop_id
  WHERE trips.trip_id = :tripID $range ORDER BY stop_sequence";
  debug($query, "database");
  $query = $conn->prepare($query);
  $query->bindParam(":tripID", $tripID);
  $query->execute();
  if (!$query) {
  databaseError($conn->errorInfo());
  return Array();
  }
  $stopTimes = $query->fetchAll();
  return $stopTimes;
  }
   
cur_timepoint = None function getTripAtStop($tripID, $stop_sequence) {
next_timepoint = None global $conn;
distance_between_timepoints = 0 foreach (getTripStopTimes($tripID) as $tripStop) {
distance_traveled_between_timepoints = 0 if ($tripStop['stop_sequence'] == $stop_sequence)
  return $tripStop;
for i, st in enumerate(stoptimes): }
if st.GetTimeSecs() != None: return Array();
cur_timepoint = st  
distance_between_timepoints = 0  
distance_traveled_between_timepoints = 0  
if i + 1 < len(stoptimes):  
k = i + 1  
distance_between_timepoints += util.ApproximateDistanceBetweenStops(stoptimes[k-1].stop, stoptimes[k].stop)  
while stoptimes[k].GetTimeSecs() == None:  
k += 1  
distance_between_timepoints += util.ApproximateDistanceBetweenStops(stoptimes[k-1].stop, stoptimes[k].stop)  
next_timepoint = stoptimes[k]  
rv.append( (st.GetTimeSecs(), st, True) )  
else:  
distance_traveled_between_timepoints += util.ApproximateDistanceBetweenStops(stoptimes[i-1].stop, st.stop)  
distance_percent = distance_traveled_between_timepoints / distance_between_timepoints  
total_time = next_timepoint.GetTimeSecs() - cur_timepoint.GetTimeSecs()  
time_estimate = distance_percent * total_time + cur_timepoint.GetTimeSecs()  
rv.append( (int(round(time_estimate)), st, False) )  
   
return rv*/  
}  
function getTimeInterpolatedTripAtStop($trip_id, $stop_sequence) {  
foreach(getTimeInterpolatedTrip($tripID) as $tripStop) {  
if ($tripStop['stop_sequence'] == $stop_sequence) return $tripStop;  
}  
return Array();  
} }
   
function getTripStartTime($tripID) { function getTripStartTime($tripID) {
$query = 'SELECT arrival_secs,departure_secs FROM stop_times WHERE trip_id=? ORDER BY stop_sequence LIMIT 1'; global $conn;
  $query = "Select * from stop_times
  where trip_id = :tripID
  AND arrival_time IS NOT NULL
  AND stop_sequence = '1'";
  debug($query, "database");
  $query = $conn->prepare($query);
  $query->bindParam(":tripID", $tripID);
  $query->execute();
  if (!$query) {
  databaseError($conn->errorInfo());
  return Array();
  }
  $r = $query->fetch(PDO :: FETCH_ASSOC);
  return $r['arrival_time'];
} }
   
function viaPointNames($tripid, $stopid) function getTripEndTime($tripID) {
{  
global $conn; global $conn;
$query = "SELECT stop_name $query = "SELECT trip_id,max(arrival_time) as arrival_time from stop_times
  WHERE stop_times.arrival_time IS NOT NULL and trip_id = :tripID group by trip_id";
  debug($query, "database");
  $query = $conn->prepare($query);
  $query->bindParam(":tripID", $tripID);
  $query->execute();
  if (!$query) {
  databaseError($conn->errorInfo());
  return Array();
  }
  $r = $query->fetch(PDO :: FETCH_ASSOC);
  return $r['arrival_time'];
  }
   
  function getTripDestination($tripID) {
  global $conn;
  $query = "SELECT stops.stop_id, stops.stop_name, stops.stop_desc
  from stop_times inner join stops on stop_times.stop_id = stops.stop_id
  WHERE trip_id = :tripID order by stop_sequence desc limit 1";
  debug($query, "database");
  $query = $conn->prepare($query);
  $query->bindParam(":tripID", $tripID);
  $query->execute();
  if (!$query) {
  databaseError($conn->errorInfo());
  return Array();
  }
  $r = $query->fetch(PDO :: FETCH_ASSOC);
  return $r;
  }
   
  function getActiveTrips($time) {
  global $conn;
  if ($time == "")
  $time = current_time();
  $query = "Select distinct stop_times.trip_id, start_times.arrival_time as start_time, end_times.arrival_time as end_time from stop_times, (SELECT trip_id,arrival_time from stop_times WHERE stop_times.arrival_time IS NOT NULL
  AND stop_sequence = '1') as start_times, (SELECT trip_id,max(arrival_time) as arrival_time from stop_times WHERE stop_times.arrival_time IS NOT NULL group by trip_id) as end_times
  WHERE start_times.trip_id = end_times.trip_id AND stop_times.trip_id = end_times.trip_id AND :time > start_times.arrival_time AND :time < end_times.arrival_time";
  debug($query, "database");
  $query = $conn->prepare($query);
  $query->bindParam(":time", $time);
  $query->execute();
  if (!$query) {
  databaseError($conn->errorInfo());
  return Array();
  }
  return $query->fetchAll();
  }
   
  function viaPoints($tripID, $stop_sequence = "") {
  global $conn;
  $query = "SELECT stops.stop_id, stop_name, arrival_time
FROM stop_times join stops on stops.stop_id = stop_times.stop_id FROM stop_times join stops on stops.stop_id = stop_times.stop_id
WHERE stop_times.trip_id = '$tripid' WHERE stop_times.trip_id = :tripID
AND stop_sequence > '$stop_sequence' " . ($stop_sequence != "" ? " AND stop_sequence > :stop_sequence " : "") . " ORDER BY stop_sequence";
AND substr(stop_code,1,2) != 'Wj' ORDER BY stop_sequence"; debug($query, "database");
debug($query,"database"); $query = $conn->prepare($query);
$result = pg_query($conn, $query); if ($stop_sequence != "")
if (!$result) { $query->bindParam(":stop_sequence", $stop_sequence);
databaseError(pg_result_error($result)); $query->bindParam(":tripID", $tripID);
return Array(); $query->execute();
} if (!$query) {
$pointNames = pg_fetch_all($result); databaseError($conn->errorInfo());
return r_implode(", ", $pointNames); return Array();
  }
  return $query->fetchAll();
} }
   
   
?> ?>