Make the app independent from the server timezone
When getting local time in SQLite, the local time is generated taking
into account the timezone set in the server, not in PHP. Therefore,
this made it compulsory to set the "Europe/Madrid" timezone for the
whole server.
This change fixes this by generating local timestamps directly in PHP
instead than in SQLite, therefore allowing the server timezone to be
set freely without this app malfunctioning.
Change-Id: I5345772d9b7a2521c98143a90e4154eaa4cd35f6
diff --git a/inc/gtfs.php b/inc/gtfs.php
index 38af838..ae33d50 100644
--- a/inc/gtfs.php
+++ b/inc/gtfs.php
@@ -94,20 +94,26 @@
const TIME_LIMIT = 30;
function getStopTimes($stop, $timeLimit = self::TIME_LIMIT) {
- $stops = $this->getPlatforms($stop);
+ $platforms = $this->getPlatforms($stop);
$values = [];
- foreach ($stops as $s) {
- $values[] = $s["stop_id"]; // Stops
+ foreach ($platforms as $i => $s) {
+ $values[":stop".(int)$i] = $s["stop_id"]; // Stops
}
- $rdow = (int)date("w");
+ $stopParameters = array_keys($values);
+
+ $rdow = (int)(new DateTime("now"))->format("w");
$dow = self::$dow[$rdow]; // Today's day of week
$dow2 = self::$dow[($rdow + 1) % 7]; // Tomorrow's day of week
- if (!count($stops)) return [];
+ $values[":today"] = (int)(new DateTime("now"))->format("Ymd"); // Today's date
+ $values[":tomorrow"] = (int)(new DateTime("tomorrow"))->format("Ymd"); // Tomorrow's date
+ $values[":now"] = (new DateTime("now"))->format("H:i:s");
- $sql = "SELECT st.*, t.*, r.*, c.*, cd.*, strftime('%Y%m%d', 'now', 'localtime') as today, strftime('%Y%m%d', 'now', 'localtime', '1 day') as tomorrow, time('now', 'localtime') as now
+ if (!count($platforms)) return [];
+
+ $sql = "SELECT st.*, t.*, r.*, c.*, cd.*
FROM stop_times st
INNER JOIN trips t
ON st.trip_id = t.trip_id
@@ -118,7 +124,7 @@
LEFT JOIN calendar_dates cd
ON t.service_id = cd.service_id
WHERE
- st.stop_id IN (".implode(", ", array_fill(0, count($stops), "?")).") AND
+ st.stop_id IN (".implode(", ", $stopParameters).") AND
(
cd.service_id IS NULL OR
cd.exception_type = ".(int)Gtfs\CalendarDate\ExceptionType::ADDED."
@@ -127,49 +133,49 @@
(
(
(
- time(now) < time('00:00:00', '-".(int)$timeLimit." minutes') AND
- st.departure_time BETWEEN time(now) AND time(now, '".(int)$timeLimit." minutes')
+ time(:now) < time('00:00:00', '-".(int)$timeLimit." minutes') AND
+ st.departure_time BETWEEN time(:now) AND time(:now, '".(int)$timeLimit." minutes')
) OR
(
- time(now) >= time('00:00:00', '-".(int)$timeLimit." minutes') AND
- st.departure_time BETWEEN time(now) AND strftime('24:%M:%S', now, '".(int)$timeLimit." minutes')
+ time(:now) >= time('00:00:00', '-".(int)$timeLimit." minutes') AND
+ st.departure_time BETWEEN time(:now) AND strftime('24:%M:%S', :now, '".(int)$timeLimit." minutes')
)
) AND
(
c.service_id IS NULL OR
(
- c.start_date <= today AND
- c.end_date >= today AND
+ c.start_date <= :today AND
+ c.end_date >= :today AND
c.$dow = ".(int)Gtfs\Calendar\CalendarDay::AVAILABLE."
)
) AND
(
cd.service_id IS NULL OR
- cd.date = today
+ cd.date = :today
)
) OR
(
(
(
- time(now) < time('00:00:00', '-".(int)$timeLimit." minutes') AND
- 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'))
+ time(:now) < time('00:00:00', '-".(int)$timeLimit." minutes') AND
+ 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'))
) OR
(
- time(now) >= time('00:00:00', '-".(int)$timeLimit." minutes') AND
- st.departure_time BETWEEN time(now) AND strftime('24:%M:%S', now, '".(int)$timeLimit." minutes')
+ time(:now) >= time('00:00:00', '-".(int)$timeLimit." minutes') AND
+ st.departure_time BETWEEN time(:now) AND strftime('24:%M:%S', :now, '".(int)$timeLimit." minutes')
)
) AND
(
c.service_id IS NULL OR
(
- c.start_date <= tomorrow AND
- c.end_date >= tomorrow AND
+ c.start_date <= :tomorrow AND
+ c.end_date >= :tomorrow AND
c.$dow2 = ".(int)Gtfs\Calendar\CalendarDay::AVAILABLE."
)
) AND
(
cd.service_id IS NULL OR
- cd.date = tomorrow
+ cd.date = :tomorrow
)
)
)