1 files changed, 74 insertions, 0 deletions
diff --git a/bin/fixmystreet.com/showcouncilrates b/bin/fixmystreet.com/showcouncilrates
new file mode 100755
index 000000000..1dacae597
--- /dev/null
+++ b/bin/fixmystreet.com/showcouncilrates
@@ -0,0 +1,74 @@
+#!/usr/bin/perl
+
+use warnings;
+use strict;
+
+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;
+use FixMyStreet::Geocode::OSM;
+
+BEGIN {
+    mySociety::Config::set_file("$FindBin::Bin/../conf/general");
+    mySociety::DBHandle::configure(
+        Name => mySociety::Config::get('FMS_DB_NAME'),
+        User => mySociety::Config::get('FMS_DB_USER'),
+        Password => mySociety::Config::get('FMS_DB_PASS'),
+        Host => mySociety::Config::get('FMS_DB_HOST', undef),
+        Port => mySociety::Config::get('FMS_DB_PORT', undef)
+    );
+}
+
+my $query = "SELECT council, COUNT(*) AS total, SUM(fixed) AS fixed
+  FROM (SELECT council,
+        CASE WHEN state in ('fixed','fixed - user', 'fixed - council')
+             THEN 1 ELSE 0 END AS fixed
+        FROM problem WHERE confirmed IS NOT NULL AND
+        state IN ('fixed', 'fixed - user', 'fixed - council', 'confirmed') AND
+        whensent < NOW() - INTERVAL '4 weeks') AS a
+  GROUP BY 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);
+my %adminsum;
+my %adminfixed;
+foreach my $row (@$stats){
+    if ($row->{council}) {
+        for my $councilid (@{$row->{council}}) {
+            $adminsum{$councilid} += $row->{total};
+            $adminfixed{$councilid} += $row->{fixed};
+        }
+    }
+}
+
+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($areas_info->{$a}->{name},
+                      $areas_info->{$b}->{name}) unless $retval;
+    return $retval;
+}