From a373d2bdc4f9f2ccd0720956d2ba2be4fdfe5002 Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Mon, 23 Feb 2015 00:22:10 +0100
Subject: arrange most things by most things! (also, fugly code at the moment)

---
 index.pl               | 17 +++++++++++++----
 templates/main.html.ep | 12 ++++++++++++
 2 files changed, 25 insertions(+), 4 deletions(-)

diff --git a/index.pl b/index.pl
index ba58c9f..ff1cf65 100644
--- a/index.pl
+++ b/index.pl
@@ -60,6 +60,8 @@ get '/2ddata.tsv' => sub {
 		$msgnum = 0;
 	}
 
+	my $where_clause = '1 = 1';
+
 	my $res = "x\ty\n";
 
 	my $query;
@@ -72,25 +74,32 @@ get '/2ddata.tsv' => sub {
 		when ('weekhour') {
 			$format = 'strftime("%w%H", scheduled_time, "unixepoch")';
 		}
+		when ('line') {
+			$format = 'train_type || " " || line_no';
+			$where_clause = 'line_no is not null';
+		}
+		when ('train_type') {
+			$format = 'train_type';
+		}
 	}
 
 	given ($metric) {
 		when ('delay') {
 			$query = qq{
 				select $format as aggregate,
-				avg(delay) from $table where not is_canceled group by aggregate
+				avg(delay) from $table where not is_canceled and $where_clause group by aggregate
 			};
 		}
 		when ('cancel_num') {
 			$query = qq{
 				select $format as aggregate,
-				count(is_canceled) from $table group by aggregate
+				count(is_canceled) from $table where $where_clause group by aggregate
 			};
 		}
 		when ('cancel_percent') {
 			$query = qq{
 				select $format as aggregate,
-				avg(is_canceled) * 100 from $table group by aggregate
+				avg(is_canceled) * 100 from $table where $where_clause group by aggregate
 			};
 		}
 		when ('message_percent') {
@@ -98,7 +107,7 @@ get '/2ddata.tsv' => sub {
 				select $format as aggregate,
 				avg(msgtable.train_id is not null) * 100 from departures
 				left outer join msg_$msgnum as msgtable using
-				(scheduled_time, train_id) group by aggregate
+				(scheduled_time, train_id) where $where_clause group by aggregate
 			};
 		}
 	}
diff --git a/templates/main.html.ep b/templates/main.html.ep
index e0e6205..d0bc8fa 100644
--- a/templates/main.html.ep
+++ b/templates/main.html.ep
@@ -104,6 +104,18 @@ show_bargraph('/2ddata.tsv?aggregate=weekday&metric=cancel_percent', 'Anteil Zug
 show_bargraph('/2ddata.tsv?aggregate=weekhour&metric=delay', 'Durchschnittliche Verspätung nach Wochentag und Uhrzeit',
 	'Tag/Stunde', 'Minuten');
 
+show_bargraph('/2ddata.tsv?aggregate=line&metric=delay', 'Durchschnittliche Verspätung nach Linie',
+	'Typ', 'Minuten');
+
+show_bargraph('/2ddata.tsv?aggregate=line&metric=cancel_percent', 'Anteil Zugausfälle nach Linie',
+	'typ', 'Ausfälle [%]');
+
+show_bargraph('/2ddata.tsv?aggregate=train_type&metric=delay', 'Durchschnittliche Verspätung nach Zugtyp',
+	'Typ', 'Minuten');
+
+show_bargraph('/2ddata.tsv?aggregate=train_type&metric=cancel_percent', 'Anteil Zugausfälle nach Zugtyp',
+	'typ', 'Ausfälle [%]');
+
 msglist = [[2, 'Polizeiliche Ermittlung'],
 	[3, 'Feuerwehreinsatz neben der Strecke'],
 	[5, 'Aerztliche Versorgung eines Fahrgastes'],
-- 
cgit v1.2.3