aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rwxr-xr-xbin/populate_bing_cache52
-rwxr-xr-xbin/send-reports2
-rw-r--r--db/schema.sql3
-rw-r--r--db/schema_0012-add_gecode_column_to_problem.sql7
-rwxr-xr-xperllib/FixMyStreet/App/Controller/Rss.pm10
-rw-r--r--perllib/FixMyStreet/Cobrand/Default.pm59
-rw-r--r--perllib/FixMyStreet/DB/Result/Problem.pm27
-rw-r--r--perllib/FixMyStreet/DB/ResultSet/AlertType.pm41
-rw-r--r--t/app/controller/alert_new.t3
-rw-r--r--t/app/controller/rss.t125
-rw-r--r--t/app/model/alert_type.t69
11 files changed, 377 insertions, 21 deletions
diff --git a/bin/populate_bing_cache b/bin/populate_bing_cache
new file mode 100755
index 000000000..bb5d4e77a
--- /dev/null
+++ b/bin/populate_bing_cache
@@ -0,0 +1,52 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+require 5.8.0;
+
+use Data::Dumper;
+
+use FixMyStreet::App;
+use FixMyStreet::Geocode::Bing;
+
+my $reports = FixMyStreet::App->model('DB::Problem')->search(
+ {
+ geocode => undef,
+ confirmed => { '!=', undef },
+ latitude => { '!=', 0 },
+ longitude => { '!=', 0 },
+ },
+ { select => [qw/id geocode confirmed latitude longitude/] }
+);
+
+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 );
+
+ $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/send-reports b/bin/send-reports
index 3d5243990..427d02ec8 100755
--- a/bin/send-reports
+++ b/bin/send-reports
@@ -92,7 +92,7 @@ while (my $row = $unsent->next) {
}
if ( $row->used_map ) {
- $h{closest_address} = $cobrand->find_closest( $h{latitude}, $h{longitude} );
+ $h{closest_address} = $cobrand->find_closest( $h{latitude}, $h{longitude}, $row );
}
my (@to, @recips, $template, $areas_info, @open311_councils);
diff --git a/db/schema.sql b/db/schema.sql
index fcd137919..395d1c07b 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -188,7 +188,8 @@ create table problem (
whensent timestamp,
send_questionnaire boolean not null default 't',
extra text, -- extra fields required for open311
- flagged boolean not null default 'f'
+ flagged boolean not null default 'f',
+ geocode bytea
);
create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude);
create index problem_user_id_idx on problem ( user_id );
diff --git a/db/schema_0012-add_gecode_column_to_problem.sql b/db/schema_0012-add_gecode_column_to_problem.sql
new file mode 100644
index 000000000..730212ead
--- /dev/null
+++ b/db/schema_0012-add_gecode_column_to_problem.sql
@@ -0,0 +1,7 @@
+
+begin;
+
+ALTER table problem
+ ADD column geocode BYTEA;
+
+commit;
diff --git a/perllib/FixMyStreet/App/Controller/Rss.pm b/perllib/FixMyStreet/App/Controller/Rss.pm
index 7fddbed97..822780b81 100755
--- a/perllib/FixMyStreet/App/Controller/Rss.pm
+++ b/perllib/FixMyStreet/App/Controller/Rss.pm
@@ -251,6 +251,12 @@ sub add_row : Private {
(my $link = $alert_type->item_link) =~ s/{{(.*?)}}/$row->{$1}/g;
(my $desc = _($alert_type->item_description)) =~ s/{{(.*?)}}/$row->{$1}/g;
my $url = $c->uri_for( $link );
+
+ if ( $row->{postcode} ) {
+ my $pc = $c->cobrand->format_postcode( $row->{postcode} );
+ $title .= ", $pc";
+ }
+
my %item = (
title => ent($title),
link => $url,
@@ -266,8 +272,8 @@ sub add_row : Private {
}
if ( $row->{used_map} ) {
- #my $address = $c->cobrand->find_closest_address_for_rss( $row->{latitude}, $row->{longitude} );
- #$item{description} .= ent("\n<br>$address");
+ my $address = $c->cobrand->find_closest_address_for_rss( $row->{latitude}, $row->{longitude}, $row );
+ $item{description} .= ent("\n<br>$address") if $address;
}
my $recipient_name = $c->cobrand->contact_name;
diff --git a/perllib/FixMyStreet/Cobrand/Default.pm b/perllib/FixMyStreet/Cobrand/Default.pm
index 2900497c4..b5a1cd8d3 100644
--- a/perllib/FixMyStreet/Cobrand/Default.pm
+++ b/perllib/FixMyStreet/Cobrand/Default.pm
@@ -545,10 +545,15 @@ Used by send-reports to attach nearest things to the bottom of the report
=cut
sub find_closest {
- my ( $self, $latitude, $longitude ) = @_;
+ my ( $self, $latitude, $longitude, $problem ) = @_;
my $str = '';
if ( my $j = FixMyStreet::Geocode::Bing::reverse( $latitude, $longitude ) ) {
+ # cache the bing results for use in alerts
+ if ( $problem ) {
+ $problem->geocode( $j );
+ $problem->update;
+ }
if ($j->{resourceSets}[0]{resources}[0]{name}) {
$str .= sprintf(_("Nearest road to the pin placed on the map (automatically generated by Bing Maps): %s"),
$j->{resourceSets}[0]{resources}[0]{name}) . "\n\n";
@@ -576,23 +581,55 @@ Used by rss feeds to provide a bit more context
=cut
sub find_closest_address_for_rss {
- my ( $self, $latitude, $longitude ) = @_;
+ my ( $self, $latitude, $longitude, $problem ) = @_;
my $str = '';
- if ( my $j = FixMyStreet::Geocode::Bing::reverse( $latitude, $longitude, 1 ) ) {
- if ($j->{resourceSets}[0]{resources}[0]{name}) {
- my $address = $j->{resourceSets}[0]{resources}[0]{address};
- my @address;
- push @address, $address->{addressLine} if $address->{addressLine} ne 'Street';
- push @address, $address->{locality};
- $str .= sprintf(_("Nearest road to the pin placed on the map (automatically generated by Bing Maps): %s"),
- join( ', ', @address ) );
- }
+ my $j;
+ if ( $problem && ref($problem) =~ /FixMyStreet/ && $problem->can( 'geocode' ) ) {
+ $j = $problem->geocode;
+ } else {
+ $problem = FixMyStreet::App->model('DB::Problem')->find( { id => $problem->{id} } );
+ $j = $problem->geocode;
+ }
+
+ # if we've not cached it then we don't want to look it up in order to avoid
+ # hammering the bing api
+ # if ( !$j ) {
+ # $j = FixMyStreet::Geocode::Bing::reverse( $latitude, $longitude, 1 );
+
+ # $problem->geocode( $j );
+ # $problem->update;
+ # }
+
+ if ($j && $j->{resourceSets}[0]{resources}[0]{name}) {
+ my $address = $j->{resourceSets}[0]{resources}[0]{address};
+ my @address;
+ push @address, $address->{addressLine} if $address->{addressLine} and $address->{addressLine} !~ /^Street$/i;
+ push @address, $address->{locality} if $address->{locality};
+ $str .= sprintf(_("Nearest road to the pin placed on the map (automatically generated by Bing Maps): %s"),
+ join( ', ', @address ) ) if @address;
}
return $str;
}
+=head2 format_postcode
+
+Takes a postcode string and if it looks like a valid postcode then transforms it
+into the canonical postcode.
+
+=cut
+
+sub format_postcode {
+ my ( $self, $postcode ) = @_;
+
+ if ( $postcode ) {
+ $postcode = mySociety::PostcodeUtil::canonicalise_postcode($postcode)
+ if $postcode && mySociety::PostcodeUtil::is_valid_postcode($postcode);
+ }
+
+ return $postcode;
+}
=head2 council_check
Paramters are COUNCILS, QUERY, CONTEXT. Return a boolean indicating whether
diff --git a/perllib/FixMyStreet/DB/Result/Problem.pm b/perllib/FixMyStreet/DB/Result/Problem.pm
index 9ff19efb6..7ceabf1da 100644
--- a/perllib/FixMyStreet/DB/Result/Problem.pm
+++ b/perllib/FixMyStreet/DB/Result/Problem.pm
@@ -82,6 +82,8 @@ __PACKAGE__->add_columns(
{ data_type => "text", is_nullable => 1 },
"flagged",
{ data_type => "boolean", default_value => \"false", is_nullable => 0 },
+ "geocode",
+ { data_type => "bytea", is_nullable => 1 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many(
@@ -104,8 +106,8 @@ __PACKAGE__->has_many(
);
-# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-07-29 16:26:23
-# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ifvx9FOlbui66hPyzNIAPA
+# Created by DBIx::Class::Schema::Loader v0.07010 @ 2011-09-19 14:38:43
+# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:nq8Ufn/SEoDGSrrGlHIxag
# Add fake relationship to stored procedure table
__PACKAGE__->has_one(
@@ -134,6 +136,27 @@ __PACKAGE__->filter_column(
},
}
);
+
+__PACKAGE__->filter_column(
+ geocode => {
+ filter_from_storage => sub {
+ my $self = shift;
+ my $ser = shift;
+ return undef unless defined $ser;
+ my $h = new IO::String($ser);
+ return RABX::wire_rd($h);
+ },
+ filter_to_storage => sub {
+ my $self = shift;
+ my $data = shift;
+ my $ser = '';
+ my $h = new IO::String($ser);
+ RABX::wire_wr( $data, $h );
+ return $ser;
+ },
+ }
+);
+
use DateTime::TimeZone;
use Image::Size;
use Moose;
diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
index c1a5d65c9..c3d4a3672 100644
--- a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
@@ -9,6 +9,8 @@ use mySociety::EmailUtil;
use mySociety::Gaze;
use mySociety::Locale;
use mySociety::MaPit;
+use IO::String;
+use RABX;
# Child must have confirmed, id, email, state(!) columns
# If parent/child, child table must also have name and text
@@ -82,12 +84,21 @@ sub email_alerts ($) {
}
my $url = $cobrand->base_url_for_emails( $row->{alert_cobrand_data} );
+ # this is currently only for new_updates
if ($row->{item_text}) {
$data{problem_url} = $url . "/report/" . $row->{id};
$data{data} .= $row->{item_name} . ' : ' if $row->{item_name} && !$row->{item_anonymous};
$data{data} .= $row->{item_text} . "\n\n------\n\n";
+ # this is ward and council problems
} else {
- $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n";
+ my $postcode = $cobrand->format_postcode( $row->{postcode} );
+ $postcode = ", $postcode" if $postcode;
+ $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}$postcode\n\n";
+ if ( exists $row->{geocode} && $row->{geocode} && $ref =~ /ward|council/ ) {
+ my $nearest_st = _get_address_from_gecode( $row->{geocode} );
+ $data{data} .= $nearest_st if $nearest_st;
+ }
+ $data{data} .= "\n\n------\n\n";
}
if (!$data{alert_user_id}) {
%data = (%data, %$row);
@@ -134,7 +145,7 @@ sub email_alerts ($) {
};
my $states = "'" . join( "', '", FixMyStreet::DB::Result::Problem::visible_states() ) . "'";
my %data = ( template => $template, data => '', alert_id => $alert->id, alert_email => $alert->user->email, lang => $alert->lang, cobrand => $alert->cobrand, cobrand_data => $alert->cobrand_data );
- my $q = "select problem.id, problem.title from problem_find_nearby(?, ?, ?) as nearby, problem, users
+ my $q = "select problem.id, problem.postcode, problem.geocode, problem.title from problem_find_nearby(?, ?, ?) as nearby, problem, users
where nearby.problem_id = problem.id
and problem.user_id = users.id
and problem.state in ($states)
@@ -150,7 +161,14 @@ sub email_alerts ($) {
alert_id => $alert->id,
parameter => $row->{id},
} );
- $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n";
+ my $postcode = $cobrand->format_postcode( $row->{postcode} );
+ $postcode = ", $postcode" if $postcode;
+ $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}$postcode\n\n";
+ if ( exists $row->{geocode} && $row->{geocode} ) {
+ my $nearest_st = _get_address_from_gecode( $row->{geocode} );
+ $data{data} .= $nearest_st if $nearest_st;
+ }
+ $data{data} .= "\n\n------\n\n";
}
_send_aggregated_alert_email(%data) if $data{data};
}
@@ -210,4 +228,21 @@ sub _send_aggregated_alert_email(%) {
}
}
+sub _get_address_from_gecode {
+ my $geocode = shift;
+
+ return '' unless defined $geocode;
+ my $h = new IO::String($geocode);
+ my $data = RABX::wire_rd($h);
+
+ my $address = $data->{resourceSets}[0]{resources}[0]{address};
+ my @address;
+ push @address, $address->{addressLine} if $address->{addressLine} ne 'Street';
+ push @address, $address->{locality};
+ my $str .= sprintf(_("Nearest road to the pin placed on the map (automatically generated by Bing Maps): %s\n\n"),
+ join( ', ', @address ) );
+
+ return $str;
+}
+
1;
diff --git a/t/app/controller/alert_new.t b/t/app/controller/alert_new.t
index 580a5ad9a..950666d2d 100644
--- a/t/app/controller/alert_new.t
+++ b/t/app/controller/alert_new.t
@@ -452,8 +452,9 @@ subtest "Test normal alert signups and that alerts are sent" => sub {
$count++ if $_->body =~ /The following updates have been left on this problem:/;
$count++ if $_->body =~ /The following new problems have been reported to City of\s*Edinburgh Council:/;
$count++ if $_->body =~ /The following nearby problems have been added:/;
+ $count++ if $_->body =~ / - Testing, EH1 1BB/;
}
- is $count, 3, 'Three emails with the right things in them';
+ is $count, 5, 'Five emails with the right things in them';
my $email = $emails[0];
like $email->body, qr/Other User/, 'Update name given';
diff --git a/t/app/controller/rss.t b/t/app/controller/rss.t
new file mode 100644
index 000000000..c6ab20574
--- /dev/null
+++ b/t/app/controller/rss.t
@@ -0,0 +1,125 @@
+use strict;
+use warnings;
+use Test::More;
+
+use FixMyStreet::TestMech;
+
+my $mech = FixMyStreet::TestMech->new;
+
+my $dt = DateTime->new(
+ year => 2011,
+ month => 10,
+ day => 10
+);
+
+my $user1 = FixMyStreet::App->model('DB::User')
+ ->find_or_create( { email => 'reporter@example.com', name => 'Reporter User' } );
+
+my $report = FixMyStreet::App->model('DB::Problem')->find_or_create( {
+ postcode => 'eh1 1BB',
+ council => '2651',
+ areas => ',11808,135007,14419,134935,2651,20728,',
+ category => 'Street lighting',
+ title => 'Testing',
+ detail => 'Testing Detail',
+ used_map => 1,
+ name => $user1->name,
+ anonymous => 0,
+ state => 'confirmed',
+ confirmed => $dt,
+ lastupdate => $dt,
+ whensent => $dt->clone->add( minutes => 5 ),
+ lang => 'en-gb',
+ service => '',
+ cobrand => 'default',
+ cobrand_data => '',
+ send_questionnaire => 1,
+ latitude => '55.951963',
+ longitude => '-3.189944',
+ user_id => $user1->id,
+} );
+
+
+$mech->get_ok("/rss/pc/EH11BB/2");
+$mech->content_contains( "Testing, 10th October, EH1 1BB" );
+$mech->content_lacks( 'Nearest road to the pin' );
+
+$report->geocode(
+{
+ 'traceId' => 'ae7c4880b70b423ebc8ab4d80961b3e9|LTSM001158|02.00.71.1600|LTSMSNVM002010, LTSMSNVM001477',
+ 'statusDescription' => 'OK',
+ 'brandLogoUri' => 'http://dev.virtualearth.net/Branding/logo_powered_by.png',
+ 'resourceSets' => [
+ {
+ 'resources' => [
+ {
+ 'geocodePoints' => [
+ {
+ 'calculationMethod' => 'Interpolation',
+ 'coordinates' => [
+ '55.9532357007265',
+ '-3.18906001746655'
+ ],
+ 'usageTypes' => [
+ 'Display',
+ 'Route'
+ ],
+ 'type' => 'Point'
+ }
+ ],
+ 'entityType' => 'Address',
+ 'name' => '18 N Bridge, Edinburgh EH1 1',
+ 'point' => {
+ 'coordinates' => [
+ '55.9532357007265',
+ '-3.18906001746655'
+ ],
+ 'type' => 'Point'
+ },
+ 'bbox' => [
+ '55.9493729831558',
+ '-3.19825819222605',
+ '55.9570984182972',
+ '-3.17986184270704'
+ ],
+ 'matchCodes' => [
+ 'Good'
+ ],
+ 'address' => {
+ 'countryRegion' => 'United Kingdom',
+ 'adminDistrict2' => 'Edinburgh City',
+ 'adminDistrict' => 'Scotland',
+ 'addressLine' => '18 North Bridge',
+ 'formattedAddress' => '18 N Bridge, Edinburgh EH1 1',
+ 'postalCode' => 'EH1 1',
+ 'locality' => 'Edinburgh'
+ },
+ 'confidence' => 'Medium',
+ '__type' => 'Location:http://schemas.microsoft.com/search/local/ws/rest/v1'
+ }
+ ],
+ 'estimatedTotal' => 1
+ }
+ ],
+ 'copyright' => "Copyright \x{a9} 2011 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.",
+ 'statusCode' => 200,
+ 'authenticationResultCode' => 'ValidCredentials'
+ }
+);
+$report->postcode('eh11bb');
+$report->update();
+
+$mech->get_ok("/rss/pc/EH11BB/2");
+$mech->content_contains( "Testing, 10th October, EH1 1BB" );
+$mech->content_contains( '18 North Bridge, Edinburgh' );
+
+$report->postcode('Princes St, Edinburgh');
+$report->update();
+
+$mech->get_ok("/rss/pc/EH11BB/2");
+$mech->content_contains( "Testing, 10th October, Princes St, Edinburgh" );
+
+$report->delete();
+$user1->delete();
+
+done_testing();
diff --git a/t/app/model/alert_type.t b/t/app/model/alert_type.t
index c7bfe171c..e88aeea5f 100644
--- a/t/app/model/alert_type.t
+++ b/t/app/model/alert_type.t
@@ -140,5 +140,74 @@ for my $test (
};
}
+my $now = DateTime->now();
+$report->confirmed( $now->ymd . ' ' . $now->hms );
+$report->update();
+
+my $council_alert = FixMyStreet::App->model('DB::Alert')->find_or_create(
+ {
+ user => $user2,
+ parameter => 2504,
+ parameter2 => 2504,
+ alert_type => 'council_problems',
+ whensubscribed => $dt->ymd . ' ' . $dt->hms,
+ confirmed => 1,
+ }
+);
+
+for my $test (
+ {
+ postcode => 'SW1A 1AA',
+ expected_postcode => 'SW1A 1AA',
+ },
+ {
+ postcode => 'sw1a 1AA',
+ expected_postcode => 'SW1A 1AA',
+ },
+ {
+ postcode => 'SW1A 1aa',
+ expected_postcode => 'SW1A 1AA',
+ },
+ {
+ postcode => 'SW1A1AA',
+ expected_postcode => 'SW1A 1AA',
+ },
+ {
+ postcode => 'Buckingham Gate',
+ expected_postcode => 'Buckingham Gate',
+ },
+ {
+ postcode => 'Buckingham gate',
+ expected_postcode => 'Buckingham gate',
+ },
+) {
+ subtest "correct text for postcode $test->{postcode}" => sub {
+ $mech->clear_emails_ok;
+
+ my $sent = FixMyStreet::App->model('DB::AlertSent')->search(
+ {
+ alert_id => $council_alert->id,
+ parameter => $report->id,
+ }
+ )->delete;
+
+ $report->postcode( $test->{postcode} );
+ $report->update;
+
+ FixMyStreet::App->model('DB::AlertType')->email_alerts();
+
+ $mech->email_count_is( 1 );
+ my $email = $mech->get_email;
+ my $pc = $test->{expected_postcode};
+ my $title = $report->title;
+ my $body = $email->body;
+
+ like $body, qr#report/$report_id - $title, $pc#, 'email contains expected postcode';
+ };
+}
+
+$report->postcode( 'SW1A 1AA' );
+$report->update;
+
done_testing();
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-26 19:52:27 +0000