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/README.md b/README.md
index a3ea40f..a8859bb 100644
--- a/README.md
+++ b/README.md
@@ -9,4 +9,3 @@
## Notes
- As I've been able to see when using this app around Barcelona, the schedules provided by TMB are a very vague approximation of the departure times for all lines except for the automated lines. In the case of the automated lines, the timing is precise within a second.
-- As of now, the timezone set in the server must be Europe/Madrid (because of the way sqlite3 treats timezones). Otherwise, the app will not work properly.
diff --git a/config.default.php b/config.default.php
index a53ef00..ac64fd1 100644
--- a/config.default.php
+++ b/config.default.php
@@ -1,8 +1,13 @@
<?php
$conf = [];
+// TMB API credentials
$conf["tmbApi"] = [];
$conf["tmbApi"]["appId"] = "";
$conf["tmbApi"]["appKey"] = "";
+// Location where the site will save the database file
$conf["databaseFile"] = "";
+
+// Timezone of the subway system
+$conf["timezone"] = "Europe/Madrid";
diff --git a/core.php b/core.php
index 89c2ec0..05c9411 100644
--- a/core.php
+++ b/core.php
@@ -12,5 +12,5 @@
require_once(__DIR__."/config.php");
// Setting timezone and locale accordingly
-date_default_timezone_set("Europe/Madrid");
+if (isset($conf["timezone"])) date_default_timezone_set($conf["timezone"]);
setlocale(LC_TIME, 'es_ES.UTF-8', 'es_ES', 'es');
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
)
)
)