avm99963 | 99bb77c | 2020-01-27 03:15:08 +0100 | [diff] [blame] | 1 | $sql = "SELECT st.*, t.*, r.*, strftime('%Y%m%d', '22:34:12') as today, strftime('%Y%m%d', '22:34:12', '1 day') as tomorrow, time('22:34:12') as now |
| 2 | FROM stop_times st |
| 3 | INNER JOIN trips t |
| 4 | ON st.trip_id = t.trip_id |
| 5 | INNER JOIN routes r |
| 6 | ON t.route_id = r.route_id |
| 7 | LEFT JOIN calendar c |
| 8 | ON t.service_id = c.service_id |
| 9 | LEFT JOIN calendar_dates cd |
| 10 | ON t.service_id = cd.service_id |
| 11 | WHERE |
| 12 | st.stop_id IN (".implode(", ", array_fill(0, count($stops), "?")).") AND |
| 13 | ( |
| 14 | ( |
| 15 | time(now) < time('23:30:00') AND |
| 16 | time(st.departure_time) BETWEEN time(now) AND time(now, '30 minutes') AND |
| 17 | ( |
| 18 | c.service_id IS NULL OR |
| 19 | ( |
| 20 | c.start_date <= today AND |
| 21 | c.end_date >= today |
| 22 | ) |
| 23 | ) AND |
| 24 | ( |
| 25 | cd.service_id IS NULL OR |
| 26 | cd.date = today |
| 27 | ) |
| 28 | ) OR |
| 29 | ( |
| 30 | time(now) >= time('23:30:00') AND |
| 31 | ( |
| 32 | ( |
| 33 | time(st.departure_time) >= time(now) AND |
| 34 | ( |
| 35 | c.service_id IS NULL OR |
| 36 | ( |
| 37 | c.start_date <= today AND |
| 38 | c.end_date >= today |
| 39 | ) |
| 40 | ) AND |
| 41 | ( |
| 42 | cd.service_id IS NULL OR |
| 43 | cd.date = today |
| 44 | ) |
| 45 | ) OR |
| 46 | ( |
| 47 | time(st.departure_time) <= time(now, '30 minutes') AND |
| 48 | ( |
| 49 | c.service_id IS NULL OR |
| 50 | ( |
| 51 | c.start_date <= tomorrow AND |
| 52 | c.end_date >= tomorrow |
| 53 | ) |
| 54 | ) AND |
| 55 | ( |
| 56 | cd.service_id IS NULL OR |
| 57 | cd.date = tomorrow |
| 58 | ) |
| 59 | ) |
| 60 | ) |
| 61 | ) |
| 62 | ) AND |
| 63 | ( |
| 64 | c.service_id IS NULL OR |
| 65 | c.$dow = ".(int)Gtfs\Calendar\CalendarDay::AVAILABLE." |
| 66 | ) AND |
| 67 | ( |
| 68 | cd.service_id IS NULL OR |
| 69 | exception_type = ".(int)Gtfs\CalendarDate\ExceptionType::ADDED." |
| 70 | ) |
| 71 | ORDER BY departure_time ASC"; |