summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--index.pl55
-rw-r--r--public/css/default.css4
-rw-r--r--templates/intro.html.ep29
3 files changed, 86 insertions, 2 deletions
diff --git a/index.pl b/index.pl
index 24519bf..8912c69 100644
--- a/index.pl
+++ b/index.pl
@@ -25,6 +25,50 @@ app->attr(
}
);
+helper count_unique_column => sub {
+ my ($self, $column) = @_;
+ my $dbh = $self->app->dbh;
+
+ if (not $column) {
+ return scalar $dbh->selectall_arrayref("select count() from $table")->[0][0];
+ }
+ return scalar $dbh->selectall_arrayref("select count(distinct $column) from $table")->[0][0];
+};
+
+helper single_query => sub {
+ my ($self, $query) = @_;
+
+ return scalar $self->app->dbh->selectall_arrayref($query)->[0][0];
+};
+
+helper globalstats => sub {
+ my ($self) = @_;
+ my $dbh = $self->app->dbh;
+
+ my $stations = [ map { Travel::Status::DE::IRIS::Stations::get_station($_)->[1] }
+ @{ $self->app->dbh->selectcol_arrayref(
+ "select distinct station from $table") } ];
+
+ my $ret = {
+ departures => $self->count_unique_column(),
+ stationlist => $stations,
+ stations => $self->count_unique_column('station'),
+ realtime => $self->single_query("select count() from $table where delay is not null"),
+ realtime_rate => $self->single_query("select avg(delay is not null) from $table"),
+ ontime => $self->single_query("select count() from $table where delay < 1"),
+ ontime_rate => $self->single_query("select avg(delay < 1) from $table"),
+ days => $self->count_unique_column('strftime("%Y%m%d", scheduled_time, "unixepoch")'),
+ delayed => $self->single_query("select count() from $table where delay > 5"),
+ delayed_rate => $self->single_query("select avg(delay > 5) from $table"),
+ canceled => $self->single_query("select count() from $table where is_canceled > 0"),
+ canceled_rate => $self->single_query("select avg(is_canceled > 0) from $table"),
+ delay_sum => $self->single_query("select sum(delay) from $table"),
+ delay_avg => $self->single_query("select avg(delay) from $table"),
+ };
+
+ return $ret;
+};
+
get '/by_hour.json' => sub {
my $self = shift;
@@ -107,7 +151,7 @@ get '/2ddata.tsv' => sub {
when ('message_rate') {
$query = qq{
select $format as aggregate,
- avg(msgtable.train_id is not null) from departures
+ avg(msgtable.train_id is not null) from $table
left outer join msg_$msgnum as msgtable using
(scheduled_time, train_id) where $where_clause group by aggregate
};
@@ -115,7 +159,7 @@ get '/2ddata.tsv' => sub {
when ('realtime_rate') {
$query = qq{
select $format as aggregate,
- avg(delay is not null) from departures
+ avg(delay is not null) from $table
where $where_clause group by aggregate
};
}
@@ -147,6 +191,13 @@ get '/2ddata.tsv' => sub {
get '/' => sub {
my $self = shift;
+
+ $self->render('intro');
+ return;
+};
+
+get '/all' => sub {
+ my $self = shift;
my $dbh = $self->app->dbh;
my $num_departures = $dbh->selectall_arrayref(
diff --git a/public/css/default.css b/public/css/default.css
index fae8452..57cf602 100644
--- a/public/css/default.css
+++ b/public/css/default.css
@@ -1,4 +1,8 @@
body {
+ font-family: sans-serif;
+}
+
+svg {
font: 10px sans-serif;
}
diff --git a/templates/intro.html.ep b/templates/intro.html.ep
new file mode 100644
index 0000000..c524ff8
--- /dev/null
+++ b/templates/intro.html.ep
@@ -0,0 +1,29 @@
+% my $stats = globalstats();
+
+<div class="intro">
+dbdb fragt regelmäßig (ca. alle 10 Minuten) die Haltestellen
+<%= join(', ', @{$stats->{stationlist} // ['???']}) %> ab und berechnet Statistiken auf
+Basis der jeweiligen Zugabfahrten. Die Statistiken sind nicht offiziell
+und ein reines Freizeitprojekt, es gibt keine Garantie für Korrektheit oder
+Vollständigkeit. (Die Seite ist aus guten Gründen nicht-öffentlich).
+</div>
+
+<div class="globalstats">
+Datengrundlage: <b><%= $stats->{departures} %></b> Fahrten
+an <b><%= $stats->{days} %></b> Tagen,
+davon
+<b><%= $stats->{realtime} %></b>
+(<b><%= sprintf('%.1f%%', $stats->{realtime_rate} * 100) %></b>) mit Echtzeitdaten.
+Es gab
+<b><%= $stats->{delayed} %></b> (<%= sprintf('%.1f%%', $stats->{delayed_rate} * 100) %>)
+Züge mit mehr als 5 Minuten Verspätung und
+<b><%= $stats->{canceled} %></b> (<%= sprintf('%.1f%%', $stats->{canceled_rate} * 100) %>)
+Zugausfälle. Pünktlich auf die Minute waren
+<b><%= $stats->{ontime} %></b> (<%= sprintf('%.1f%%', $stats->{ontime_rate} * 100) %>)
+Abfahrten.
+<br/>
+Insgesamt wurden
+<b><%= sprintf('%d', $stats->{delay_sum} / 60) %> Stunden</b> an Verspätungen eingefahren,
+dabei ist ein Zug durchschnittlich
+<b><%= sprintf('%.2f', $stats->{delay_avg}) %> Minuten</b> zu spät.
+</div>