From 17afa11078c4a35eecf50b0668a26bb33635b95c Mon Sep 17 00:00:00 2001 From: Petter Reinholdtsen Date: Sun, 1 May 2011 07:49:42 +0200 Subject: Add script to show council solve rates. --- bin/showcouncilrates | 61 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 61 insertions(+) create mode 100755 bin/showcouncilrates (limited to 'bin') diff --git a/bin/showcouncilrates b/bin/showcouncilrates new file mode 100755 index 000000000..71c7cee35 --- /dev/null +++ b/bin/showcouncilrates @@ -0,0 +1,61 @@ +#!/usr/bin/perl + +use warnings; +use strict; + +use FindBin; +use lib "$FindBin::Bin/../perllib"; +use lib "$FindBin::Bin/../commonlib/perllib"; + +use mySociety::DBHandle qw(dbh); + +use mySociety::MaPit; +use FixMyStreet::Geocode::OSM; + +BEGIN { + mySociety::Config::set_file("$FindBin::Bin/../conf/general"); + mySociety::DBHandle::configure( + Name => mySociety::Config::get('BCI_DB_NAME'), + User => mySociety::Config::get('BCI_DB_USER'), + Password => mySociety::Config::get('BCI_DB_PASS'), + Host => mySociety::Config::get('BCI_DB_HOST', undef), + Port => mySociety::Config::get('BCI_DB_PORT', undef) + ); +} + +my $query = "SELECT council, COUNT(*) AS total, + 100.0 * SUM(fixed) / COUNT(*) AS fraq_fixed + FROM (SELECT council, + CASE WHEN state = 'fixed' THEN 1 ELSE 0 END AS fixed + FROM problem WHERE confirmed IS NOT NULL AND + state IN ('fixed', 'confirmed') AND + whensent < NOW() - INTERVAL '4 weeks') AS a + GROUP BY council ORDER BY fraq_fixed DESC, total DESC, council"; + +my $stats = dbh()->selectall_arrayref($query, { Slice => {} }); + +my @councils; +foreach my $row (@$stats) { + if ($row->{council}) { + $row->{council} =~ s/\|.*//g; + my @council_ids = split(/,/, $row->{council}); + push(@councils, @council_ids); + $row->{council} = \@council_ids; + } +} +my $areas_info = mySociety::MaPit::call('areas', \@councils); +foreach my $row (@$stats){ + if ($row->{council}) { + my @council_names = map { $areas_info->{$_}->{name} } @{$row->{council}} ; + $row->{council} = join(',', @council_names); + } +} + +foreach my $row (@$stats) { + my $council = $row->{council}; + my $total = $row->{total}; + my $fraq_fixed = $row->{fraq_fixed}; + + my @councils = split(/,/, $council); + printf("%5.1f %4d %s\n", $fraq_fixed, $total, $council); +} -- cgit v1.2.3 From d098c0fe0ee47fef044f9800e94140e314694a47 Mon Sep 17 00:00:00 2001 From: Petter Reinholdtsen Date: Mon, 2 May 2011 22:23:45 +0200 Subject: Report info on individual councils, and not grouped when several got the problem report. --- bin/showcouncilrates | 28 ++++++++++++++++++---------- 1 file changed, 18 insertions(+), 10 deletions(-) (limited to 'bin') diff --git a/bin/showcouncilrates b/bin/showcouncilrates index 71c7cee35..9eece34b2 100755 --- a/bin/showcouncilrates +++ b/bin/showcouncilrates @@ -23,14 +23,13 @@ BEGIN { ); } -my $query = "SELECT council, COUNT(*) AS total, - 100.0 * SUM(fixed) / COUNT(*) AS fraq_fixed +my $query = "SELECT council, COUNT(*) AS total, SUM(fixed) AS fixed FROM (SELECT council, CASE WHEN state = 'fixed' THEN 1 ELSE 0 END AS fixed FROM problem WHERE confirmed IS NOT NULL AND state IN ('fixed', 'confirmed') AND whensent < NOW() - INTERVAL '4 weeks') AS a - GROUP BY council ORDER BY fraq_fixed DESC, total DESC, council"; + GROUP BY council"; my $stats = dbh()->selectall_arrayref($query, { Slice => {} }); @@ -44,18 +43,27 @@ foreach my $row (@$stats) { } } my $areas_info = mySociety::MaPit::call('areas', \@councils); +my %adminsum; +my %adminfixed; foreach my $row (@$stats){ if ($row->{council}) { my @council_names = map { $areas_info->{$_}->{name} } @{$row->{council}} ; - $row->{council} = join(',', @council_names); + for my $council (@council_names) { + $adminsum{$council} = $row->{total}; + $adminfixed{$council} = $row->{fixed}; + } } } -foreach my $row (@$stats) { - my $council = $row->{council}; - my $total = $row->{total}; - my $fraq_fixed = $row->{fraq_fixed}; +foreach my $council (sort sort_councils keys %adminsum) { + my $total = $adminsum{$council}; + my $fixed = $adminfixed{$council}; + printf("%5.1f %4d %s\n", 100 * $fixed / $total, $total, $council); +} - my @councils = split(/,/, $council); - printf("%5.1f %4d %s\n", $fraq_fixed, $total, $council); +sub sort_councils { + my $retval = ($adminfixed{$b} / $adminsum{$b}) <=> + ($adminfixed{$a} / $adminsum{$a}); + $retval = $adminsum{$b} <=> $adminsum{$a} unless $retval; + return $retval; } -- cgit v1.2.3 From 40d90de0b3f5508d57e035a885f3a31a6a90871d Mon Sep 17 00:00:00 2001 From: Petter Reinholdtsen Date: Mon, 2 May 2011 22:27:11 +0200 Subject: Typo. --- bin/showcouncilrates | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'bin') diff --git a/bin/showcouncilrates b/bin/showcouncilrates index 9eece34b2..fff3d11d8 100755 --- a/bin/showcouncilrates +++ b/bin/showcouncilrates @@ -49,8 +49,8 @@ foreach my $row (@$stats){ if ($row->{council}) { my @council_names = map { $areas_info->{$_}->{name} } @{$row->{council}} ; for my $council (@council_names) { - $adminsum{$council} = $row->{total}; - $adminfixed{$council} = $row->{fixed}; + $adminsum{$council} += $row->{total}; + $adminfixed{$council} += $row->{fixed}; } } } -- cgit v1.2.3 From 81d8b8fc9d58d1093464588928dc95145b517d52 Mon Sep 17 00:00:00 2001 From: Petter Reinholdtsen Date: Mon, 2 May 2011 22:35:41 +0200 Subject: Improve sorting. --- bin/showcouncilrates | 2 ++ 1 file changed, 2 insertions(+) (limited to 'bin') diff --git a/bin/showcouncilrates b/bin/showcouncilrates index fff3d11d8..cdfc26ce3 100755 --- a/bin/showcouncilrates +++ b/bin/showcouncilrates @@ -7,6 +7,7 @@ use FindBin; use lib "$FindBin::Bin/../perllib"; use lib "$FindBin::Bin/../commonlib/perllib"; +use POSIX qw(strcoll); use mySociety::DBHandle qw(dbh); use mySociety::MaPit; @@ -65,5 +66,6 @@ sub sort_councils { my $retval = ($adminfixed{$b} / $adminsum{$b}) <=> ($adminfixed{$a} / $adminsum{$a}); $retval = $adminsum{$b} <=> $adminsum{$a} unless $retval; + $retval = strcoll($a, $b) unless $retval; return $retval; } -- cgit v1.2.3 From 8546c2f5feabf345b78dd1b6bb688e8451ea01d0 Mon Sep 17 00:00:00 2001 From: Petter Reinholdtsen Date: Mon, 2 May 2011 22:42:35 +0200 Subject: Index by council ID not name, to handle two with the same name. --- bin/showcouncilrates | 20 +++++++++++--------- 1 file changed, 11 insertions(+), 9 deletions(-) (limited to 'bin') diff --git a/bin/showcouncilrates b/bin/showcouncilrates index cdfc26ce3..9ae5c3e7b 100755 --- a/bin/showcouncilrates +++ b/bin/showcouncilrates @@ -48,24 +48,26 @@ my %adminsum; my %adminfixed; foreach my $row (@$stats){ if ($row->{council}) { - my @council_names = map { $areas_info->{$_}->{name} } @{$row->{council}} ; - for my $council (@council_names) { - $adminsum{$council} += $row->{total}; - $adminfixed{$council} += $row->{fixed}; + for my $councilid (@{$row->{council}}) { + $adminsum{$councilid} += $row->{total}; + $adminfixed{$councilid} += $row->{fixed}; } } } -foreach my $council (sort sort_councils keys %adminsum) { - my $total = $adminsum{$council}; - my $fixed = $adminfixed{$council}; - printf("%5.1f %4d %s\n", 100 * $fixed / $total, $total, $council); +foreach my $councilid (sort sort_councils keys %adminsum) { + my $council = $areas_info->{$councilid}->{name}; + my $total = $adminsum{$councilid}; + my $fixed = $adminfixed{$councilid}; + printf("%5.1f %4d %s (%d)\n", 100 * $fixed / $total, $total, + $council, $councilid); } sub sort_councils { my $retval = ($adminfixed{$b} / $adminsum{$b}) <=> ($adminfixed{$a} / $adminsum{$a}); $retval = $adminsum{$b} <=> $adminsum{$a} unless $retval; - $retval = strcoll($a, $b) unless $retval; + $retval = strcoll($areas_info->{$a}->{name}, + $areas_info->{$b}->{name}) unless $retval; return $retval; } -- cgit v1.2.3