diff options
| -rw-r--r-- | index.pl | 55 | ||||
| -rw-r--r-- | public/css/default.css | 4 | ||||
| -rw-r--r-- | templates/intro.html.ep | 29 |
3 files changed, 86 insertions, 2 deletions
@@ -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> |