Merge branch 'master' of ssh://git.mysociety.org/data/git/public/fixmystreet into open311-consumer

2 files changed, 170 insertions, 0 deletions

diff --git a/bin/export-norwegian-contacts b/bin/export-norwegian-contacts
new file mode 100644
index 000000000..8a7d438e5
--- /dev/null
+++ b/bin/export-norwegian-contacts
@@ -0,0 +1,97 @@
+#!/usr/bin/perl
+
+# export-norwegian-contacts:
+# Export initial contact list from fiksgatami in a format usable by
+# load-norwegian-contact.
+#
+# The format is
+# ID;Name;email-address;Category1,Category2,...
+#
+# Based on script load-contacts copyright (c) 2006 UK Citizens Online
+# Democracy and script load-norwegian-contacts copyright (c) 2011
+# Petter Reinholdtsen.
+# Copyright 2011 Petter Reinholdtsen.
+#
+# $Id: load-norwegian-contacts,v 1.0 2007-08-02 11:44:59 matthew Exp $
+
+use strict;
+use warnings;
+require 5.8.0;
+use open OUT => ':utf8';
+
+# Horrible boilerplate to set up appropriate library paths.
+use FindBin;
+use lib "$FindBin::Bin/../perllib";
+use lib "$FindBin::Bin/../commonlib/perllib";
+
+use mySociety::Config;
+use mySociety::DBHandle qw(dbh select_all);
+use mySociety::MaPit;
+
+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 $datafile = shift;
+
+open(my $fh, '>', $datafile) or die "Unable to write to $datafile";
+
+# Categories used by more than half the areas is used as the default
+# list.
+my $sql =
+    "SELECT category FROM (SELECT category, COUNT(*) from contacts ".
+    "                      WHERE confirmed = 'true' AND deleted = 'false' ".
+    "                      GROUP BY category) as c ".
+    "  WHERE count > (SELECT COUNT(*)/2 FROM contacts ".
+    "                 WHERE category = 'Annet') ".
+    "  ORDER BY category";
+my $defcategoriesref = dbh()->selectcol_arrayref($sql);
+print $fh "0000;default;;", join(',', @{$defcategoriesref}), "\n";
+
+my %categorygroup;
+$sql = "SELECT area_id, email, category FROM contacts ORDER BY category";
+my $contactref = dbh()->selectall_arrayref($sql, { Slice => {} });
+my @area_ids;
+for my $row (@{$contactref}) {
+    my $key = $row->{area_id} .':'. $row->{email};
+    push(@area_ids, $row->{area_id});
+    if (exists $categorygroup{$key}) {
+        push(@{$categorygroup{$key}}, $row->{category});
+    } else {
+        $categorygroup{$key} = [ $row->{category} ];
+    }
+}
+
+my $areas_info = mySociety::MaPit::call('areas', \@area_ids);
+
+my @list;
+for my $key (keys %categorygroup) {
+    my ($area_id, $email) = split(/:/, $key);
+    my $categoriesref = $categorygroup{$key};
+    my $areaname = $areas_info->{$area_id}->{name};
+    my $areadigits = ($area_id < 100) ? 2 : 4;
+    if (identical_lists($defcategoriesref, $categoriesref)) {
+        push(@list,sprintf("%0${areadigits}d;%s;%s\n",
+                           $area_id, $areaname, $email));
+    } else {
+        push(@list, sprintf("%0${areadigits}d;%s;%s;%s\n",
+                            $area_id, $areaname, $email,
+                            join(',', @{$categoriesref})));
+    }
+}
+
+print $fh sort @list;
+
+sub identical_lists {
+    my ($a, $b) = @_;
+    return !(join(',', @{$a}) cmp join(',', @{$b}));
+}
+
+exit 0;
diff --git a/bin/showcouncilrates b/bin/showcouncilrates
new file mode 100755
index 000000000..9ae5c3e7b
--- /dev/null
+++ b/bin/showcouncilrates
@@ -0,0 +1,73 @@
+#!/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('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, 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";
+
+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;
+}