1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
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;
}
|
