You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

index.php 2.3KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697
  1. <?php
  2. require '../../private/config.php';
  3. if (!isset($_SERVER['HTTP_SECRET']) || $_SERVER['HTTP_SECRET'] !== $config['secret']) {
  4. header($_SERVER['SERVER_PROTOCOL'] . ' 403 Forbidden');
  5. exit(0);
  6. }
  7. if (!isset($_GET['stop']) || !isset($_GET['agency'])) {
  8. header($_SERVER['SERVER_PROTOCOL'] . ' 400 Bad Request');
  9. exit(0);
  10. }
  11. // DB connection
  12. $dbh = new PDO('pgsql:host=localhost;dbname=' . $config['dbName'],
  13. $config['dbUser'], $config['dbPassword']);
  14. // our agency_id is null, so use feed_id...
  15. $query = 'SELECT agency_timezone FROM agency WHERE feed_id = :agencyId';
  16. $statement = $dbh->prepare($query);
  17. $agency = $_GET['agency'];
  18. $statement->execute(array(':agencyId' => $agency));
  19. $row = $statement->fetch();
  20. $timezone = $row['agency_timezone'];
  21. date_default_timezone_set($timezone);
  22. // Get current date/time
  23. $d = explode('%', date('Y-m-d%H:i'));
  24. $date = $d[0];
  25. $time = $d[1];
  26. // Stop id
  27. $stop = $_GET['stop'];
  28. // Based on: https://stackoverflow.com/a/51455985
  29. $query = <<<SQL
  30. SELECT departure_time, route_long_name, trip_headsign
  31. FROM
  32. (
  33. SELECT
  34. st.departure_time,
  35. tr.service_id,
  36. rte.route_short_name,
  37. rte.route_long_name,
  38. tr.trip_headsign
  39. FROM stop_times AS st
  40. JOIN trips AS tr ON tr.trip_id = st.trip_id
  41. JOIN routes AS rte ON rte.route_id = tr.route_id
  42. JOIN calendar AS cal ON cal.service_id = tr.service_id
  43. WHERE st.stop_id = :stopId
  44. AND :date between cal.start_date and cal.end_date
  45. AND (CASE EXTRACT(DOW FROM TIMESTAMP '$date')
  46. WHEN 1 THEN monday
  47. WHEN 2 THEN tuesday
  48. WHEN 3 THEN wednesday
  49. WHEN 4 THEN thursday
  50. WHEN 5 THEN friday
  51. WHEN 6 THEN saturday
  52. WHEN 0 THEN sunday
  53. END) = true
  54. AND st.departure_time >= :time
  55. ORDER BY tr.route_id, st.departure_time
  56. ) Q
  57. ORDER BY departure_time
  58. LIMIT 3 OFFSET 0;
  59. SQL;
  60. // TODO: Error handling from here down
  61. $statement = $dbh->prepare($query);
  62. $statement->bindParam(':stopId', $stop, PDO::PARAM_STR);
  63. $statement->bindParam(':date', $date, PDO::PARAM_STR);
  64. $statement->bindParam(':time', $time, PDO::PARAM_STR);
  65. $statement->execute();
  66. $rows = $statement->fetchAll();
  67. error_log(print_r($rows, true));
  68. $results = [];
  69. foreach ($rows as $row) {
  70. $results[] = $row['departure_time'];
  71. }
  72. // convert results to json
  73. $json = json_encode($results);
  74. header('Content-Type: application/json');
  75. echo $json;