blob: 40f033ce5e73fb7bdc17dd9858562842b2d32f82 [file] [log] [blame]
avm9996399bb77c2020-01-27 03:15:08 +01001<?php
2class gtfs {
3 //private static $files = ["calendar_dates", "calendar", "routes", "stop_times", "stops", "trips"];
4
5 private $db;
6
7 private static $dow = ["sunday", "monday", "tuesday", "wednesday", "thursday", "friday", "saturday"];
8
9 function __construct($path = null) {
10 global $conf;
11
12 try {
13 $this->db = new PDO('sqlite:'.($conf["databaseFile"]));
14 } catch (PDOException $e) {
15 die("SQLite error: $e\n");
16 }
17 }
18
19 static function timeSinceMidnight() {
20 //return 8*60*60 + 9*60; //TESTING
21 return (time() - mktime(0, 0, 0));
22 }
23
24 static function today() {
25 return date("Ymd");
26 }
27
Adrià Vilanova Martínez98d0ade2021-10-18 01:10:33 +020028 // Converts a time in the format "HH:MM:SS" to the number of seconds. If
29 // |uniqueRepresentative| is true, when HH >= 24, a representative between 0
30 // and 24*60*60 - 1 of the equivalence class mod 24*60*60 will be returned
31 // instead of returning a number >= 24*60*60.
32 static function time2seconds($time, $uniqueRepresentative=true) {
avm9996399bb77c2020-01-27 03:15:08 +010033 $timeSinceMidnight = self::timeSinceMidnight();
34
35 $boom = explode(":", $time);
36 if (count($boom) != 3) return null;
37
Adrià Vilanova Martínez98d0ade2021-10-18 01:10:33 +020038 $seconds = (($boom[0]*60) + $boom[1])*60 + $boom[2];
39 return $uniqueRepresentative ? $seconds % (24*60*60) : $seconds;
avm9996399bb77c2020-01-27 03:15:08 +010040 }
41
42 private function fetchAll($sql) {
43 $query = $this->db->query($sql);
44 if (!$query) return false;
45
46 return $query->fetchAll(PDO::FETCH_ASSOC);
47 }
48
49 private function fetchAllPrepared($sql, $values) {
50 $query = $this->db->prepare($sql);
51 if (!$query) return false;
52
53 $query->execute($values);
54 return $query->fetchAll(PDO::FETCH_ASSOC);
55 }
56
57 private function fetchTable($table, $filters = [], $orderedField = null) {
58 $order = ($orderedField === null ? "" : " ORDER BY $orderedField");
59
60 if (!count($filters)) {
61 return $this->fetchAll("SELECT * FROM $table".$order);
62 }
63
64 $whereConditions = [];
65 $values = [];
66 foreach ($filters as $filter) {
67 $whereConditions[] = $filter[0]." = ?";
68 $values[] = $filter[1];
69 }
70
71 return $this->fetchAllPrepared("SELECT * FROM $table WHERE ".implode(" AND ", $whereConditions).$order, $values);
72 }
73
74 function getRoutes() {
75 return $this->fetchTable("routes");
76 }
77
78 function getTrips($route) {
79 return $this->fetchTable("trips", [["route_id", $route]]);
80 }
81
82 function getStations($ordered = false) {
83 return $this->fetchTable("stops", [["location_type", Gtfs\Stop\LocationType::STATION]], ($ordered ? "stop_name" : null));
84 }
85
86 function getStop($stop) {
87 $results = $this->fetchTable("stops", [["stop_id", $stop]]);
88
89 if (!count($results)) return null;
90
91 return $results[0];
92 }
93
94 function getPlatforms($stop) {
95 $results = $this->fetchTable("stops", [["parent_station", $stop], ["location_type", Gtfs\Stop\LocationType::STOP]], "stop_id");
96
97 return $results;
98 }
99
avm99963dfd7bd52020-01-27 03:37:54 +0100100 const TIME_LIMIT = 30;
avm9996399bb77c2020-01-27 03:15:08 +0100101 function getStopTimes($stop, $timeLimit = self::TIME_LIMIT) {
avm9996378011ca2020-07-05 13:49:26 +0200102 $platforms = $this->getPlatforms($stop);
avm9996399bb77c2020-01-27 03:15:08 +0100103
104 $values = [];
avm9996378011ca2020-07-05 13:49:26 +0200105 foreach ($platforms as $i => $s) {
106 $values[":stop".(int)$i] = $s["stop_id"]; // Stops
avm9996399bb77c2020-01-27 03:15:08 +0100107 }
108
avm9996378011ca2020-07-05 13:49:26 +0200109 $stopParameters = array_keys($values);
110
111 $rdow = (int)(new DateTime("now"))->format("w");
Adrià Vilanova Martínez0eb0c5a2021-11-28 18:34:17 +0100112 $dow0 = self::$dow[($rdow + 6) % 7]; // Yesterday's day of week
avm9996399bb77c2020-01-27 03:15:08 +0100113 $dow = self::$dow[$rdow]; // Today's day of week
114 $dow2 = self::$dow[($rdow + 1) % 7]; // Tomorrow's day of week
115
Adrià Vilanova Martínez98d0ade2021-10-18 01:10:33 +0200116 $values[":yesterday"] = (int)(new DateTime("yesterday"))->format("Ymd"); // Yesterday's date
avm9996378011ca2020-07-05 13:49:26 +0200117 $values[":today"] = (int)(new DateTime("now"))->format("Ymd"); // Today's date
118 $values[":tomorrow"] = (int)(new DateTime("tomorrow"))->format("Ymd"); // Tomorrow's date
119 $values[":now"] = (new DateTime("now"))->format("H:i:s");
avm9996399bb77c2020-01-27 03:15:08 +0100120
avm9996378011ca2020-07-05 13:49:26 +0200121 if (!count($platforms)) return [];
122
123 $sql = "SELECT st.*, t.*, r.*, c.*, cd.*
avm9996399bb77c2020-01-27 03:15:08 +0100124 FROM stop_times st
125 INNER JOIN trips t
126 ON st.trip_id = t.trip_id
127 INNER JOIN routes r
128 ON t.route_id = r.route_id
129 LEFT JOIN calendar c
130 ON t.service_id = c.service_id
131 LEFT JOIN calendar_dates cd
132 ON t.service_id = cd.service_id
133 WHERE
avm9996378011ca2020-07-05 13:49:26 +0200134 st.stop_id IN (".implode(", ", $stopParameters).") AND
avm9996399bb77c2020-01-27 03:15:08 +0100135 (
136 cd.service_id IS NULL OR
137 cd.exception_type = ".(int)Gtfs\CalendarDate\ExceptionType::ADDED."
138 ) AND
139 (
140 (
141 (
142 (
avm9996378011ca2020-07-05 13:49:26 +0200143 time(:now) < time('00:00:00', '-".(int)$timeLimit." minutes') AND
144 st.departure_time BETWEEN time(:now) AND time(:now, '".(int)$timeLimit." minutes')
avm9996399bb77c2020-01-27 03:15:08 +0100145 ) OR
146 (
avm9996378011ca2020-07-05 13:49:26 +0200147 time(:now) >= time('00:00:00', '-".(int)$timeLimit." minutes') AND
Adrià Vilanova Martínez98d0ade2021-10-18 01:10:33 +0200148 st.departure_time BETWEEN time(:now) AND ((strftime('%H', :now, '".(int)$timeLimit." minutes') + 24) || strftime(':%M:%S', :now, '".(int)$timeLimit." minutes'))
avm9996399bb77c2020-01-27 03:15:08 +0100149 )
150 ) AND
151 (
152 c.service_id IS NULL OR
153 (
avm9996378011ca2020-07-05 13:49:26 +0200154 c.start_date <= :today AND
155 c.end_date >= :today AND
Adrià Vilanova Martínez0eb0c5a2021-11-28 18:34:17 +0100156 c.".$dow." = ".(int)Gtfs\Calendar\CalendarDay::AVAILABLE."
avm9996399bb77c2020-01-27 03:15:08 +0100157 )
158 ) AND
159 (
160 cd.service_id IS NULL OR
avm9996378011ca2020-07-05 13:49:26 +0200161 cd.date = :today
avm9996399bb77c2020-01-27 03:15:08 +0100162 )
163 ) OR
164 (
165 (
166 (
avm9996378011ca2020-07-05 13:49:26 +0200167 time(:now) < time('00:00:00', '-".(int)$timeLimit." minutes') AND
168 st.departure_time BETWEEN ((strftime('%H', :now) + 24) || strftime(':%M:%S', :now)) AND ((strftime('%H', :now, '".(int)$timeLimit." minutes') + 24) || strftime(':%M:%S', :now, '".(int)$timeLimit." minutes'))
avm9996399bb77c2020-01-27 03:15:08 +0100169 ) OR
170 (
avm9996378011ca2020-07-05 13:49:26 +0200171 time(:now) >= time('00:00:00', '-".(int)$timeLimit." minutes') AND
Adrià Vilanova Martínez98d0ade2021-10-18 01:10:33 +0200172 st.departure_time BETWEEN ((strftime('%H', :now) + 24) || strftime(':%M:%S', :now)) AND ((strftime('%H', :now, '".(int)$timeLimit." minutes') + 48) || strftime(':%M:%S', :now, '".(int)$timeLimit." minutes'))
avm9996399bb77c2020-01-27 03:15:08 +0100173 )
174 ) AND
175 (
176 c.service_id IS NULL OR
177 (
Adrià Vilanova Martínez98d0ade2021-10-18 01:10:33 +0200178 c.start_date <= :yesterday AND
179 c.end_date >= :yesterday AND
Adrià Vilanova Martínez0eb0c5a2021-11-28 18:34:17 +0100180 c.".$dow0." = ".(int)Gtfs\Calendar\CalendarDay::AVAILABLE."
Adrià Vilanova Martínez98d0ade2021-10-18 01:10:33 +0200181 )
182 ) AND
183 (
184 cd.service_id IS NULL OR
185 cd.date = :yesterday
186 )
187 ) OR
188 (
189 time(:now) >= time('00:00:00', '-".(int)$timeLimit." minutes') AND
190 st.departure_time BETWEEN time('00:00:00') AND time(:now, '".(int)$timeLimit." minutes') AND
191 (
192 c.service_id IS NULL OR
193 (
avm9996378011ca2020-07-05 13:49:26 +0200194 c.start_date <= :tomorrow AND
195 c.end_date >= :tomorrow AND
Adrià Vilanova Martínez0eb0c5a2021-11-28 18:34:17 +0100196 c.".$dow2." = ".(int)Gtfs\Calendar\CalendarDay::AVAILABLE."
avm9996399bb77c2020-01-27 03:15:08 +0100197 )
198 ) AND
199 (
200 cd.service_id IS NULL OR
avm9996378011ca2020-07-05 13:49:26 +0200201 cd.date = :tomorrow
avm9996399bb77c2020-01-27 03:15:08 +0100202 )
203 )
204 )
205 ORDER BY departure_time ASC";
206
207 $result = $this->fetchAllPrepared($sql, $values);
208 if ($result === false) {
209 echo implode("\n", $this->db->errorInfo());
210 exit();
211 }
212
213 return $result;
214 }
215
216 function __destruct() {
217 $this->db = null;
218 }
219}