2 files changed, 63 insertions, 5 deletions

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;