aboutsummaryrefslogtreecommitdiffstats
path: root/bin/fixmystreet.com
diff options
context:
space:
mode:
Diffstat (limited to 'bin/fixmystreet.com')
-rwxr-xr-xbin/fixmystreet.com/canonicalise-csv112
-rwxr-xr-xbin/fixmystreet.com/generate_council_location28
-rwxr-xr-xbin/fixmystreet.com/import-categories78
-rwxr-xr-xbin/fixmystreet.com/load-contacts49
-rwxr-xr-xbin/fixmystreet.com/populate_bing_cache60
-rwxr-xr-xbin/fixmystreet.com/rotate-photos64
-rwxr-xr-xbin/fixmystreet.com/showcouncilrates74
-rwxr-xr-xbin/fixmystreet.com/update-areas46
-rwxr-xr-xbin/fixmystreet.com/update-send-questionnaire29
9 files changed, 540 insertions, 0 deletions
diff --git a/bin/fixmystreet.com/canonicalise-csv b/bin/fixmystreet.com/canonicalise-csv
new file mode 100755
index 000000000..c0a7fc60b
--- /dev/null
+++ b/bin/fixmystreet.com/canonicalise-csv
@@ -0,0 +1,112 @@
+#!/usr/bin/perl -w
+
+# canonicalise-csv:
+# Convert provided CSV file into one with standard names for MaPit
+#
+# Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: canonicalise-csv,v 1.4 2009-02-16 18:56:44 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# 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::MaPit;
+use mySociety::VotingArea;
+
+BEGIN {
+ mySociety::Config::set_file("$FindBin::Bin/../conf/general");
+ mySociety::MaPit::configure();
+}
+
+my %councils;
+open(FP, "$FindBin::Bin/../data/councils.csv")
+ or die "Can't read councils.csv: $!\n";
+while (<FP>) {
+ s/\r?\n//g;
+ my ($name, $email) = split /,/;
+ $email ||= '';
+ $email =~ s/\xa0//g;
+
+ # Canonicalise
+ next if $name eq 'Londonderry'; # Dupe of Derry
+ next if $name eq 'Kingston upon Hull'; # Dupe of Hull
+ next if $name eq 'London' || $name eq 'Greater London'; # Untrustworthy
+
+ # Different
+ $name = 'Corporation of London' if $name eq 'City of London';
+ $name = "Renfrewsh'r" if $name eq 'Renfrewshire';
+ # Shorter
+ $name = 'Blackburn' if $name eq 'Blackburn with Darwen';
+ $name = 'Dungannon' if $name eq 'Dungannon & South Tyrone';
+ $name = 'Staffordshire' if $name eq 'Staffordshire County';
+ $name = 'Armagh' if $name eq 'Armagh City';
+ # Longer
+ $name = 'Kingston upon Hull' if $name eq 'Hull';
+ $name = "City of $name" if $name =~ /^(Edinburgh|Glasgow|York)$/;
+ $name .= ' Islands' if $name eq 'Shetland';
+ $name .= ' & Chelsea' if $name eq 'Kensington';
+ # Wrong
+ $name =~ s/King's Lynn/Kings Lynn/;
+ $name = 'Surrey Heath' if $name eq 'Surrey Health';
+ $name = 'Barking & Dagenham' if $name eq 'Barking-Dagenham';
+ $name = 'Newtownabbey' if $name eq 'Newtonabbey';
+ $name = 'Isles of Scilly' if $name eq 'Isle of Scilly';
+ # Compass
+ $name =~ s/North East /N. E. /;
+ $name =~ s/^North West /N. W. /;
+ $name =~ s/^North (?!Lincolnshire|Norfolk|Somerset)/N. /;
+ $name =~ s/^South (?!Shropshire|Staffordshire|Somerset)/S. /;
+ $name =~ s/^East (?!Staffordshire)/E. /;
+ $name =~ s/^West(ern)? (?!Berkshire|Wiltshire)/W. /;
+ $name =~ s/ W / W. /;
+ # Various
+ $name =~ s/^Great /Gt. /;
+ $name =~ s/^St /St. /;
+ $name =~ s/ and / & /;
+ $name =~ s/ ?Royal$//;
+ $name =~ s/ Borough$//;
+ $name =~ s/-(upon|on|le)-/ $1 /;
+ $councils{$name} = $email;
+}
+close(FP);
+
+my $types = $mySociety::VotingArea::council_parent_types;
+my (%out, @missing);
+foreach my $type (@$types) {
+ my $areas = mySociety::MaPit::get_areas_by_type($type);
+ my $areas_info = mySociety::MaPit::get_voting_areas_info($areas);
+ foreach my $id (keys %$areas_info) {
+ my $area_info = $areas_info->{$id};
+ my $name = $area_info->{name};
+ if ($name eq 'Durham City Council') {
+ $out{$id} = $councils{'Durham City'};
+ next;
+ } elsif ($name eq 'Durham County Council') {
+ $out{$id} = $councils{'Durham County'};
+ next;
+ }
+ $name =~ s/( (Borough|City|District|County))* Council//;
+ if ($councils{$name} && $councils{$name} =~ /@/) {
+ $out{$id} = $councils{$name};
+ } elsif ($councils{$name} || exists($councils{$name})) {
+ push @missing, $id;
+ }
+ }
+}
+
+# Output missing IDs to STDOUT
+print join(',', @missing) . "\n";
+
+# Output emails to canonical CSV
+open(FP, ">$FindBin::Bin/../data/councils_canonical.csv");
+foreach (sort keys %out) {
+ print FP "$_," . $out{$_} . "\n";
+}
+close FP;
diff --git a/bin/fixmystreet.com/generate_council_location b/bin/fixmystreet.com/generate_council_location
new file mode 100755
index 000000000..c7aea8074
--- /dev/null
+++ b/bin/fixmystreet.com/generate_council_location
@@ -0,0 +1,28 @@
+#!/usr/bin/perl
+
+use strict;
+use JSON;
+use LWP::Simple;
+
+my $id = shift;
+
+my $url = sprintf( 'http://mapit.mysociety.org/area/%s/geometry', $id);
+
+my $json = get( $url );
+my $geo = decode_json( $json );
+
+my $max_lat = $geo->{max_lat};
+my $min_lat = $geo->{min_lat};
+my $max_lon = $geo->{max_lon};
+my $min_lon = $geo->{min_lon};
+my $c_lat = $geo->{centre_lat};
+my $c_lon = $geo->{centre_lon};
+
+my $spn_lat = $max_lat - $min_lat;
+my $spn_lon = $max_lon - $min_lon;
+
+print <<"EOT";
+ centre => '$c_lat,$c_lon',
+ span => '$spn_lat,$spn_lon',
+ bounds => [ $min_lat, $min_lon, $max_lat, $max_lon ],
+EOT
diff --git a/bin/fixmystreet.com/import-categories b/bin/fixmystreet.com/import-categories
new file mode 100755
index 000000000..e9008b93f
--- /dev/null
+++ b/bin/fixmystreet.com/import-categories
@@ -0,0 +1,78 @@
+#!/usr/bin/perl -w
+
+# import-categories:
+# Initial choice of categories for councils. Most likely wrong.
+#
+# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: import-categories,v 1.3 2007-08-02 11:44:59 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# 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;
+use mySociety::VotingArea;
+
+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 @district_cats = (
+ 'Abandoned vehicles', 'Car parking', 'Dog fouling', 'Flyposting', 'Flytipping',
+ 'Graffiti', 'Parks/landscapes', 'Public toilets', 'Rubbish (refuse and recycling)',
+ 'Street nameplates', 'Street cleaning', 'Trees'
+);
+my @county_cats = (
+ 'Bus stops', 'Pavements/footpaths', 'Potholes', 'Roads/highways',
+ 'Road traffic signs', 'Street lighting', 'Traffic lights'
+);
+
+my @councils;
+foreach my $type (@$mySociety::VotingArea::council_parent_types) {
+ my $areas = mySociety::MaPit::get_areas_by_type($type);
+ push @councils, @$areas;
+}
+my $councils = mySociety::MaPit::get_voting_areas_info(\@councils);
+foreach my $id (keys %$councils) {
+ my $type = $councils->{$id}->{type};
+ my ($email,$confirmed) = dbh()->selectrow_array(
+ "SELECT email,confirmed FROM contacts WHERE deleted='f'
+ and area_id=? AND category='Other'", {}, $id);
+ next unless $email;
+ if ($type eq 'DIS') {
+ add_categories($id, $email, $confirmed, @district_cats);
+ } elsif ($type eq 'CTY') {
+ add_categories($id, $email, $confirmed, @county_cats);
+ } else {
+ add_categories($id, $email, $confirmed, @district_cats, @county_cats);
+ }
+}
+dbh()->commit();
+
+sub add_categories {
+ my ($id, $email, $confirmed, @cats) = @_;
+ foreach (@cats) {
+ dbh()->do("insert into contacts
+ (area_id, category, email, editor, whenedited, note, confirmed, deleted)
+ values
+ (?, ?, ?, 'import', ms_current_timestamp(), 'Initial copy', ?, 'f')", {},
+ $id, $_, $email, ($confirmed ? 1 : 0)
+ );
+ }
+}
+
diff --git a/bin/fixmystreet.com/load-contacts b/bin/fixmystreet.com/load-contacts
new file mode 100755
index 000000000..b18699db1
--- /dev/null
+++ b/bin/fixmystreet.com/load-contacts
@@ -0,0 +1,49 @@
+#!/usr/bin/perl -w
+
+# canonicalise-csv:
+# Convert provided CSV file into one with standard names for MaPit
+#
+# Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: load-contacts,v 1.3 2007-08-02 11:44:59 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# 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::Random;
+
+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)
+ );
+
+ if (!dbh()->selectrow_array('select secret from secret for update of secret')) {
+ local dbh()->{HandleError};
+ dbh()->do('insert into secret (secret) values (?)', {}, unpack('h*', mySociety::Random::random_bytes(32)));
+ }
+ dbh()->commit();
+}
+
+open(FP, "$FindBin::Bin/../data/councils_canonical.csv");
+while (<FP>) {
+ s/\r?\n//g;
+ my ($id, $email) = split /,/;
+ dbh()->do("INSERT INTO contacts (area_id, email, editor, whenedited, note)
+ VALUES (?, ?, 'import', ms_current_timestamp(), 'Initial import')",
+ {}, $id, $email);
+}
+dbh()->commit();
+close(FP);
diff --git a/bin/fixmystreet.com/populate_bing_cache b/bin/fixmystreet.com/populate_bing_cache
new file mode 100755
index 000000000..17c8911d0
--- /dev/null
+++ b/bin/fixmystreet.com/populate_bing_cache
@@ -0,0 +1,60 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+require 5.8.0;
+
+use Data::Dumper;
+
+use FixMyStreet::App;
+use FixMyStreet::Geocode::Bing;
+
+my $bing_culture = 'en-GB';
+
+my $reports = FixMyStreet::App->model('DB::Problem')->search(
+ {
+ geocode => undef,
+ confirmed => { '!=', undef },
+ latitude => { '!=', 0 },
+ longitude => { '!=', 0 },
+ },
+ {
+ select => [qw/id geocode confirmed latitude longitude/],
+ order_by => { -desc => 'confirmed' }
+ }
+);
+
+my $num_reports = $reports->count();
+print "Found $num_reports lacking geocode information\n";
+
+my $time_to_do = ( $num_reports * 10 ) / 60 / 60;
+if ( $time_to_do > 24 ) {
+ my $days = $time_to_do / 24;
+ my $hours = $time_to_do % 24;
+ printf( "Should take %d days and %d hours to finish\n", $days, $hours );
+}
+elsif ( $time_to_do < 1 ) {
+ printf( "Should take %d minutes to finish\n", $time_to_do * 60 );
+}
+else {
+ my $mins = ( $num_reports * 10 ) / 60 % 60;
+ printf( "Should take %d hours and %d minutes to finish\n",
+ $time_to_do, $mins );
+}
+
+while ( my $report = $reports->next ) {
+ $num_reports--;
+ next unless $report->latitude && $report->longitude;
+ next if $report->geocode;
+
+ my $j = FixMyStreet::Geocode::Bing::reverse( $report->latitude,
+ $report->longitude, $bing_culture );
+
+ $report->geocode($j);
+ $report->update;
+
+ print "$num_reports left to populate\n" unless $num_reports % 100;
+ sleep 10;
+}
+
+print "done\n";
diff --git a/bin/fixmystreet.com/rotate-photos b/bin/fixmystreet.com/rotate-photos
new file mode 100755
index 000000000..7b8109d65
--- /dev/null
+++ b/bin/fixmystreet.com/rotate-photos
@@ -0,0 +1,64 @@
+#!/usr/bin/perl -w
+
+# rotate-photos:
+# Manaully fix any already-uploaded photos that are orientated wrongly.
+#
+# Copyright (c) 2008 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: rotate-photos,v 1.1 2008-12-11 15:18:40 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# Horrible boilerplate to set up appropriate library paths.
+use FindBin;
+use lib "$FindBin::Bin/../perllib";
+use lib "$FindBin::Bin/../commonlib/perllib";
+
+use Digest::SHA qw(sha1_hex);
+
+use Utils;
+use mySociety::Config;
+use mySociety::DBHandle qw(dbh select_all);
+use mySociety::TempFiles;
+
+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 $r = select_all("select id, photo from problem where service='iPhone'");
+foreach (@$r) {
+ my $id = $_->{id};
+ my $photo = $_->{photo};
+
+ if (length($photo) == 40) {
+ # If photo field contains a hash
+ my $filename = mySociety::Config::get('UPLOAD_DIR') . "$photo.jpeg";
+ `jhead -autorot $filename`;
+ } else {
+ my ($fh, $filename) = mySociety::TempFiles::named_tempfile('.jpeg');
+ print $fh $photo;
+ close $fh;
+ my $out = `jhead -autorot $filename`;
+ if ($out) {
+ open(FP, $filename) or die $!;
+ $photo = join('', <FP>);
+ close FP;
+ my $fileid = sha1_hex($photo);
+ rename $filename, mySociety::Config::get('UPLOAD_DIR') . "$fileid.jpeg";
+ dbh()->do('UPDATE problem SET photo=? WHERE id=?', {}, $fileid, $id);
+ dbh()->commit();
+ } else {
+ unlink $filename;
+ }
+ }
+}
+
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;
+}
diff --git a/bin/fixmystreet.com/update-areas b/bin/fixmystreet.com/update-areas
new file mode 100755
index 000000000..a8cc01769
--- /dev/null
+++ b/bin/fixmystreet.com/update-areas
@@ -0,0 +1,46 @@
+#!/usr/bin/perl -w
+
+# update-areas:
+# One-off script to populate the areas column of the problem table
+#
+# Copyright (c) 2007 UK Citizens Online Democracy. All rights reserved.
+# Email: matthew@mysociety.org. WWW: http://www.mysociety.org
+#
+# $Id: update-areas,v 1.1 2007-08-24 12:27:30 matthew Exp $
+
+use strict;
+require 5.8.0;
+
+# 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;
+use mySociety::VotingArea;
+
+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)
+ );
+}
+
+print "Fetching problems...\n";
+my $ids = select_all("select id, latitude, longitude from problem where areas=''");
+print "Updating areas...\n";
+my $c = 0;
+foreach (@$ids) {
+ my $areas = mySociety::MaPit::get_voting_areas_by_location({latitude=>$_->{latitude}, longitude=>$_->{longitude}}, 'polygon');
+ $areas = ',' . join(',', sort keys %$areas) . ',';
+ dbh()->do('update problem set areas=? where id=?', {}, $areas, $_->{id});
+ dbh()->commit();
+ print "Done: " . (++$c) . "/" . @$ids . "\n";
+}
+
diff --git a/bin/fixmystreet.com/update-send-questionnaire b/bin/fixmystreet.com/update-send-questionnaire
new file mode 100755
index 000000000..7a231b919
--- /dev/null
+++ b/bin/fixmystreet.com/update-send-questionnaire
@@ -0,0 +1,29 @@
+#!/usr/bin/env perl
+
+=head1 DESCRIPTION
+
+Ad hoc script to update the send_questionnaire column on the
+reports in cobrands that don't send questionnaires at all.
+
+=cut
+
+use strict;
+use warnings;
+
+use FixMyStreet::App;
+
+my @cobrands;
+foreach my $cobrand ( FixMyStreet::Cobrand->available_cobrand_classes() ) {
+ next unless $cobrand->{class};
+ my $cls = $cobrand->{class}->new();
+ push @cobrands, $cls->moniker if !$cls->send_questionnaires();
+}
+
+my $problems = FixMyStreet::App->model('DB::Problem')->search({
+ cobrand => \@cobrands,
+ send_questionnaire => 1,
+});
+
+$problems->update( {
+ send_questionnaire => 0
+});
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-26 20:37:26 +0000