add itnro page

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>