From 5bc95476543793c188ba2ad373fb6ab8a41f552d Mon Sep 17 00:00:00 2001
From: Daniel Friesel <derf@finalrewind.org>
Date: Sat, 7 Mar 2015 22:44:33 +0100
Subject: move filter->where code into separate helper function

---
 index.pl | 115 +++++++++++++++++++++++++++++++++++++++++++--------------------
 1 file changed, 78 insertions(+), 37 deletions(-)

diff --git a/index.pl b/index.pl
index 648d685..a6c48c7 100644
--- a/index.pl
+++ b/index.pl
@@ -190,6 +190,52 @@ helper globalstats => sub {
 	return $ret;
 };
 
+helper parse_filter_args => sub {
+	my $self         = shift;
+	my $where_clause = q{};
+
+	my %filter = (
+		line        => scalar $self->param('filter_line'),
+		train_type  => scalar $self->param('filter_train_type'),
+		station     => scalar $self->param('filter_station'),
+		destination => scalar $self->param('filter_destination'),
+		delay_min   => scalar $self->param('filter_delay_min'),
+		delay_max   => scalar $self->param('filter_delay_max'),
+	);
+
+	for my $key ( keys %filter ) {
+		$filter{$key} =~ tr{a-zA-Z0-9öäüÖÄÜß }{}cd;
+	}
+
+	$filter{delay_min}
+	  = length( $filter{delay_min} ) ? int( $filter{delay_min} ) : undef;
+	$filter{delay_max}
+	  = length( $filter{delay_max} ) ? int( $filter{delay_max} ) : undef;
+
+	if ( $filter{line} ) {
+		my ( $train_type, $line_no ) = split( / /, $filter{line} );
+		$where_clause
+		  .= " and train_type = '$train_type' and line_no = '$line_no'";
+	}
+	if ( $filter{train_type} ) {
+		$where_clause .= " and train_type = '$filter{train_type}'";
+	}
+	if ( $filter{station} ) {
+		$where_clause .= " and station = '$filter{station}'";
+	}
+	if ( $filter{destination} ) {
+		$where_clause .= " and destination = '$filter{destination}'";
+	}
+	if ( defined $filter{delay_min} ) {
+		$where_clause .= " and delay >= $filter{delay_min}";
+	}
+	if ( defined $filter{delay_max} ) {
+		$where_clause .= " and delay <= $filter{delay_max}";
+	}
+
+	return ( \%filter, $where_clause );
+};
+
 get '/by_hour.json' => sub {
 	my $self = shift;
 
@@ -221,23 +267,7 @@ get '/2ddata.tsv' => sub {
 	my $metric    = $self->param('metric') // 'avg_delay';
 	my $msgnum    = int( $self->param('msgnum') // 0 );
 
-	my %filter = (
-		line        => scalar $self->param('filter_line'),
-		train_type  => scalar $self->param('filter_train_type'),
-		station     => scalar $self->param('filter_station'),
-		destination => scalar $self->param('filter_destination'),
-		delay_min   => scalar $self->param('filter_delay_min'),
-		delay_max   => scalar $self->param('filter_delay_max'),
-	);
-
-	for my $key ( keys %filter ) {
-		$filter{$key} =~ tr{a-zA-Z0-9öäüÖÄÜß }{}cd;
-	}
-
-	$filter{delay_min}
-	  = length( $filter{delay_min} ) ? int( $filter{delay_min} ) : undef;
-	$filter{delay_max}
-	  = length( $filter{delay_max} ) ? int( $filter{delay_max} ) : undef;
+	my ( $filter, $filter_clause ) = $self->parse_filter_args;
 
 	my @weekdays = qw(So Mo Di Mi Do Fr Sa);
 
@@ -271,26 +301,7 @@ get '/2ddata.tsv' => sub {
 		}
 	}
 
-	if ( $filter{line} ) {
-		my ( $train_type, $line_no ) = split( / /, $filter{line} );
-		$where_clause
-		  .= " and train_type = '$train_type' and line_no = '$line_no'";
-	}
-	if ( $filter{train_type} ) {
-		$where_clause .= " and train_type = '$filter{train_type}'";
-	}
-	if ( $filter{station} ) {
-		$where_clause .= " and station = '$filter{station}'";
-	}
-	if ( $filter{destination} ) {
-		$where_clause .= " and destination = '$filter{destination}'";
-	}
-	if ( defined $filter{delay_min} ) {
-		$where_clause .= " and delay >= $filter{delay_min}";
-	}
-	if ( defined $filter{delay_max} ) {
-		$where_clause .= " and delay <= $filter{delay_max}";
-	}
+	$where_clause .= $filter_clause;
 
 	given ($metric) {
 		when ('avg_delay') {
@@ -428,6 +439,36 @@ get '/bar' => sub {
 	return;
 };
 
+get '/top10' => sub {
+	my $self         = shift;
+	my $where_clause = '1=1';
+
+	my ( $filter, $filter_clause ) = $self->parse_filter_args;
+
+	my @rates;
+	my $dbh = $self->app->dbh;
+
+	$where_clause .= $filter_clause;
+
+	my $total = $dbh->selectall_arrayref(
+		"select count() from $table where $where_clause")->[0][0];
+
+	for my $msgnum ( 1 .. 99 ) {
+		my $query = qq{
+			select count()
+			from $table
+			join msg_$msgnum as msgtable using
+			(scheduled_time, train_id) where $where_clause
+		};
+		$rates[$msgnum] = $self->app->dbh->selectall_arrayref($query)->[0][0];
+
+		say $rates[$msgnum] / $total;
+	}
+
+	$self->render( 'intro', );
+	return;
+};
+
 app->config(
 	hypnotoad => {
 		accepts  => 10,
-- 
cgit v1.2.3