diff options
Diffstat (limited to 'perllib/FixMyStreet/DB/ResultSet')
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/AlertType.pm | 46 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Body.pm | 17 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Nearby.pm | 7 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Problem.pm | 157 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm | 4 |
5 files changed, 155 insertions, 76 deletions
diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm index 468df2654..545b54c60 100644 --- a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm +++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm @@ -29,6 +29,7 @@ sub email_alerts ($) { $query .= " $item_table.id as item_id, $item_table.text as item_text, $item_table.name as item_name, $item_table.anonymous as item_anonymous, + $item_table.confirmed as item_confirmed, $head_table.* from alert inner join $item_table on alert.parameter::integer = $item_table.${head_table}_id @@ -58,6 +59,7 @@ sub email_alerts ($) { while (my $row = $query->fetchrow_hashref) { my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->{alert_cobrand})->new(); + $cobrand->set_lang_and_domain( $row->{alert_lang}, 1, FixMyStreet->path_to('locale')->stringify ); # Cobranded and non-cobranded messages can share a database. In this case, the conf file # should specify a vhost to send the reports for each cobrand, so that they don't get sent @@ -89,13 +91,14 @@ sub email_alerts ($) { } my $url = $cobrand->base_url( $row->{alert_cobrand_data} ); - if ( $hashref_restriction && $hashref_restriction->{council} && $row->{council} ne $hashref_restriction->{council} ) { + if ( $hashref_restriction && $hashref_restriction->{bodies_str} && $row->{bodies_str} ne $hashref_restriction->{bodies_str} ) { $url = mySociety::Config::get('BASE_URL'); } # this is currently only for new_updates if ($row->{item_text}) { - if ( $row->{alert_user_id} == $row->{user_id} ) { + if ( $cobrand->moniker ne 'zurich' && $row->{alert_user_id} == $row->{user_id} ) { # This is an alert to the same user who made the report - make this a login link + # Don't bother with Zurich which has no accounts my $user = FixMyStreet::App->model('DB::User')->find( { id => $row->{alert_user_id} } ); @@ -112,6 +115,27 @@ sub email_alerts ($) { $data{problem_url} = $url . "/report/" . $row->{id}; } $data{data} .= $row->{item_name} . ' : ' if $row->{item_name} && !$row->{item_anonymous}; + if ( $cobrand->include_time_in_update_alerts ) { + # this is basically recreating the code from the inflate wrapper + # in the database model. + my $tz; + if ( FixMyStreet->config('TIME_ZONE') ) { + $tz = FixMyStreet->config('TIME_ZONE'); + } + + my $parser = DateTime::Format::Pg->new(); + my $dt = $parser->parse_timestamp( $row->{item_confirmed} ); + my $l_tz = DateTime::TimeZone->new( name => "local" ); + # We need to always set this otherwise we end up with the DateTime + # object being in the floating timezone in which case applying a + # subsequent timezone set will have no effect. + $dt->set_time_zone( $l_tz ); + if ( $tz ) { + my $tz_obj = DateTime::TimeZone->new( name => $tz ); + $dt->set_time_zone( $tz_obj ); + } + $data{data} .= $cobrand->prettify_dt( $dt, 'alert' ) . "\n\n"; + } $data{data} .= $row->{item_text} . "\n\n------\n\n"; # this is ward and council problems } else { @@ -155,6 +179,7 @@ sub email_alerts ($) { while (my $alert = $query->next) { my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($alert->cobrand)->new(); next unless $cobrand->email_host; + next if $alert->is_from_abuser; my $longitude = $alert->parameter; my $latitude = $alert->parameter2; @@ -166,7 +191,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.council, problem.postcode, problem.geocode, problem.title from problem_find_nearby(?, ?, ?) as nearby, problem, users + my $q = "select problem.id, problem.bodies_str, 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) @@ -183,7 +208,7 @@ sub email_alerts ($) { parameter => $row->{id}, } ); my $url = $cobrand->base_url( $alert->cobrand_data ); - if ( $hashref_restriction && $hashref_restriction->{council} && $row->{council} ne $hashref_restriction->{council} ) { + if ( $hashref_restriction && $hashref_restriction->{bodies_str} && $row->{bodies_str} ne $hashref_restriction->{bodies_str} ) { $url = mySociety::Config::get('BASE_URL'); } $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n"; @@ -202,7 +227,7 @@ sub _send_aggregated_alert_email(%) { my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($data{cobrand})->new(); - $cobrand->set_lang_and_domain( $data{lang}, 1 ); + $cobrand->set_lang_and_domain( $data{lang}, 1, FixMyStreet->path_to('locale')->stringify ); if (!$data{alert_email}) { my $user = FixMyStreet::App->model('DB::User')->find( { @@ -211,6 +236,11 @@ sub _send_aggregated_alert_email(%) { $data{alert_email} = $user->email; } + my ($domain) = $data{alert_email} =~ m{ @ (.*) \z }x; + return if FixMyStreet::App->model('DB::Abuse')->search( { + email => [ $data{alert_email}, $domain ] + } )->first; + my $token = FixMyStreet::App->model("DB::Token")->new_result( { scope => 'alert', data => { @@ -230,13 +260,12 @@ sub _send_aggregated_alert_email(%) { unless -e $template; $template = Utils::read_file($template); - my $sender = $cobrand->contact_email; - (my $from = $sender) =~ s/team/fms-DO-NOT-REPLY/; # XXX + my $sender = FixMyStreet->config('DO_NOT_REPLY_EMAIL'); my $result = FixMyStreet::App->send_email_cron( { _template_ => $template, _parameters_ => \%data, - From => [ $from, _($cobrand->contact_name) ], + From => [ $sender, _($cobrand->contact_name) ], To => $data{alert_email}, }, $sender, @@ -255,6 +284,7 @@ sub _get_address_from_gecode { my $geocode = shift; return '' unless defined $geocode; + utf8::encode($geocode) if utf8::is_utf8($geocode); my $h = new IO::String($geocode); my $data = RABX::wire_rd($h); diff --git a/perllib/FixMyStreet/DB/ResultSet/Body.pm b/perllib/FixMyStreet/DB/ResultSet/Body.pm new file mode 100644 index 000000000..6802ed604 --- /dev/null +++ b/perllib/FixMyStreet/DB/ResultSet/Body.pm @@ -0,0 +1,17 @@ +package FixMyStreet::DB::ResultSet::Body; +use base 'DBIx::Class::ResultSet'; + +use strict; +use warnings; + +sub for_areas { + my ( $rs, @areas ) = @_; + + my $result = $rs->search( + { 'body_areas.area_id' => \@areas }, + { join => 'body_areas' } + ); + return $result; +} + +1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm index 191223572..91c44d5f4 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Nearby.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Nearby.pm @@ -21,12 +21,7 @@ sub nearby { } if $c->cobrand->problems_clause; my $attrs = { - join => 'problem', - columns => [ - 'problem.id', 'problem.title', 'problem.latitude', - 'problem.longitude', 'distance', 'problem.state', - 'problem.confirmed', { 'problem.photo' => 'problem.photo is not null' }, - ], + prefetch => 'problem', bind => [ $mid_lat, $mid_lon, $dist ], order_by => [ 'distance', { -desc => 'created' } ], rows => $limit, diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm index 6da383d6c..c108f7e29 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm @@ -84,15 +84,16 @@ sub _recent { my $key = $photos ? 'recent_photos' : 'recent'; $key .= ":$site_key:$num"; + # unconfirmed might be returned for e.g. Zurich, but would mean in moderation, so no photo + my @states = grep { $_ ne 'unconfirmed' } FixMyStreet::DB::Result::Problem->visible_states(); my $query = { non_public => 0, - state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + state => \@states, }; $query->{photo} = { '!=', undef } if $photos; my $attrs = { - columns => [ 'id', 'title', 'confirmed' ], - order_by => { -desc => 'confirmed' }, + order_by => { -desc => 'coalesce(confirmed, created)' }, rows => $num, }; @@ -134,10 +135,6 @@ sub around_map { my ( $rs, $min_lat, $max_lat, $min_lon, $max_lon, $interval, $limit ) = @_; my $attr = { order_by => { -desc => 'created' }, - columns => [ - 'id', 'title', 'latitude', 'longitude', 'state', 'confirmed', - { photo => 'photo is not null' }, - ], }; $attr->{rows} = $limit if $limit; @@ -159,7 +156,7 @@ sub around_map { sub timeline { my ( $rs ) = @_; - my $prefetch = + my $prefetch = FixMyStreet::App->model('DB')->schema->storage->sql_maker->quote_char ? [ qw/user/ ] : []; @@ -219,46 +216,74 @@ sub categories_summary { return \%categories; } +sub get_admin_url { + my ($rs, $cobrand, $row) = @_; + return $cobrand->admin_base_url . '/report_edit/' . $row->id; +} + sub send_reports { + my ( $rs, $site_override ) = @_; + # Set up site, language etc. - my ($verbose, $nomail) = CronFns::options(); + my ($verbose, $nomail, $debug_mode) = CronFns::options(); + my $base_url = mySociety::Config::get('BASE_URL'); - my $site = CronFns::site($base_url); + my $site = $site_override || CronFns::site($base_url); + my $states = [ 'confirmed', 'fixed' ]; + $states = [ 'unconfirmed', 'confirmed', 'in progress', 'planned', 'closed' ] if $site eq 'zurich'; my $unsent = FixMyStreet::App->model("DB::Problem")->search( { - state => [ 'confirmed', 'fixed' ], + state => $states, whensent => undef, - council => { '!=', undef }, + bodies_str => { '!=', undef }, } ); my (%notgot, %note); my $send_report = FixMyStreet::SendReport->new(); my $senders = $send_report->get_senders; - my %sending_skipped_by_method; + my $debug_unsent_count = 0; + debug_print("starting to loop through unsent problem reports...") if $debug_mode; while (my $row = $unsent->next) { my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->cobrand)->new(); - # Cobranded and non-cobranded messages can share a database. In this case, the conf file - # should specify a vhost to send the reports for each cobrand, so that they don't get sent + if ($debug_mode) { + $debug_unsent_count++; + print "\n"; + debug_print("state=" . $row->state . ", bodies_str=" . $row->bodies_str . ($row->cobrand? ", cobrand=" . $row->cobrand : ""), $row->id); + } + + # Cobranded and non-cobranded messages can share a database. In this case, the conf file + # should specify a vhost to send the reports for each cobrand, so that they don't get sent # more than once if there are multiple vhosts running off the same database. The email_host # call checks if this is the host that sends mail for this cobrand. - next unless $cobrand->email_host(); + if (! $cobrand->email_host()) { + debug_print("skipping because this host does not send reports for cobrand " . $cobrand->moniker, $row->id) if $debug_mode; + next; + } + $cobrand->set_lang_and_domain($row->lang, 1); - if ( $row->is_from_abuser ) { + if ( $row->is_from_abuser) { + $row->update( { state => 'hidden' } ); + debug_print("hiding because its sender is flagged as an abuser", $row->id) if $debug_mode; + next; + } elsif ( $row->title =~ /app store test/i ) { $row->update( { state => 'hidden' } ); + debug_print("hiding because it is an app store test message", $row->id) if $debug_mode; next; } # Template variables for the email my $email_base_url = $cobrand->base_url_for_report($row); my %h = map { $_ => $row->$_ } qw/id title detail name category latitude longitude used_map/; - map { $h{$_} = $row->user->$_ } qw/email phone/; - $h{confirmed} = DateTime::Format::Pg->format_datetime( $row->confirmed->truncate (to => 'second' ) ); + map { $h{$_} = $row->user->$_ || '' } qw/email phone/; + $h{confirmed} = DateTime::Format::Pg->format_datetime( $row->confirmed->truncate (to => 'second' ) ) + if $row->confirmed; $h{query} = $row->postcode; $h{url} = $email_base_url . $row->url; + $h{admin_url} = $rs->get_admin_url($cobrand, $row); $h{phone_line} = $h{phone} ? _('Phone:') . " $h{phone}\n\n" : ''; if ($row->photo) { $h{has_photo} = _("This web page also contains a photo of the problem, provided by the user.") . "\n\n"; @@ -302,39 +327,42 @@ sub send_reports { my ( $sender_count ); if ($site eq 'emptyhomes') { - my $council = $row->council; - my $areas_info = mySociety::MaPit::call('areas', $council); + my $body = $row->bodies_str; + $body = FixMyStreet::App->model("DB::Body")->find($body); my $sender = "FixMyStreet::SendReport::EmptyHomes"; $reporters{ $sender } = $sender->new() unless $reporters{$sender}; - $reporters{ $sender }->add_council( $council, $areas_info->{$council} ); + $reporters{ $sender }->add_body( $body ); + $sender_count = 1; } else { # XXX Needs locks! - my @all_councils = split /,|\|/, $row->council; - my ($councils, $missing) = $row->council =~ /^([\d,]+)(?:\|([\d,]+))?/; - my @councils = split(/,/, $councils); - my $areas_info = mySociety::MaPit::call('areas', \@all_councils); + # XXX Only copes with at most one missing body + my ($bodies, $missing) = $row->bodies_str =~ /^([\d,]+)(?:\|(\d+))?/; + my @bodies = split(/,/, $bodies); + $bodies = FixMyStreet::App->model("DB::Body")->search( + { id => \@bodies }, + { order_by => 'name' }, + ); + $missing = FixMyStreet::App->model("DB::Body")->find($missing) if $missing; my @dear; - foreach my $council (@councils) { - my $name = $areas_info->{$council}->{name}; - - my $sender_info = $cobrand->get_council_sender( $council, $areas_info->{$council}, $row->category ); + while (my $body = $bodies->next) { + my $sender_info = $cobrand->get_body_sender( $body, $row->category ); my $sender = "FixMyStreet::SendReport::" . $sender_info->{method}; if ( ! exists $senders->{ $sender } ) { - warn "No such sender [ $sender ] for council $name ( $council )"; + warn "No such sender [ $sender ] for body $body->name ( $body->id )"; next; } $reporters{ $sender } ||= $sender->new(); if ( $reporters{ $sender }->should_skip( $row ) ) { - $sending_skipped_by_method{ $sender }++ if - $reporters{ $sender }->skipped; + debug_print("skipped by sender " . $sender_info->{method} . " (might be due to previous failed attempts?)", $row->id) if $debug_mode; } else { - push @dear, $name; - $reporters{ $sender }->add_council( $council, $areas_info->{$council}, $sender_info->{config} ); + debug_print("OK, adding recipient body " . $body->id . ":" . $body->name . ", " . $body->send_method, $row->id) if $debug_mode; + push @dear, $body->name; + $reporters{ $sender }->add_body( $body, $sender_info->{config} ); } } @@ -352,7 +380,7 @@ sub send_reports { $h{subcategory_line} = "\n\n"; } - $h{councils_name} = join(_(' and '), @dear); + $h{bodies_name} = join(_(' and '), @dear); if ($h{category} eq _('Other')) { $h{multiple} = @dear>1 ? "[ " . _("This email has been sent to both councils covering the location of the problem, as the user did not categorise it; please ignore it if you're not the correct council to deal with the issue, or let us know what category of problem this is so we can add it to our system.") . " ]\n\n" : ''; @@ -360,11 +388,10 @@ sub send_reports { $h{multiple} = @dear>1 ? "[ " . _("This email has been sent to several councils covering the location of the problem, as the category selected is provided for all of them; please ignore it if you're not the correct council to deal with the issue.") . " ]\n\n" : ''; } - $h{missing} = ''; + $h{missing} = ''; if ($missing) { - my $name = $areas_info->{$missing}->{name}; $h{missing} = '[ ' - . sprintf(_('We realise this problem might be the responsibility of %s; however, we don\'t currently have any contact details for them. If you know of an appropriate contact address, please do get in touch.'), $name) + . sprintf(_('We realise this problem might be the responsibility of %s; however, we don\'t currently have any contact details for them. If you know of an appropriate contact address, please do get in touch.'), $missing->name) . " ]\n\n"; } @@ -375,16 +402,16 @@ sub send_reports { die 'Report not going anywhere for ID ' . $row->id . '!'; } - next unless $sender_count; + if (! $sender_count) { + debug_print("can't send because sender count is zero", $row->id) if $debug_mode; + next; + } - if (mySociety::Config::get('STAGING_SITE')) { - # on a staging server send emails to ourselves rather than the councils - my @testing_councils = split( '\|', mySociety::Config::get('TESTING_COUNCILS') ); - unless ( grep { $row->council eq $_ } @testing_councils ) { - %reporters = map { $_ => $reporters{$_} } grep { /FixMyStreet::SendReport::(Email|NI)/ } keys %reporters; - unless (%reporters) { - %reporters = ( 'FixMyStreet::SendReport::Email' => FixMyStreet::SendReport::Email->new() ); - } + if (mySociety::Config::get('STAGING_SITE') && !mySociety::Config::get('SEND_REPORTS_ON_STAGING')) { + # on a staging server send emails to ourselves rather than the bodies + %reporters = map { $_ => $reporters{$_} } grep { /FixMyStreet::SendReport::(Email|NI|EmptyHomes)/ } keys %reporters; + unless (%reporters) { + %reporters = ( 'FixMyStreet::SendReport::Email' => FixMyStreet::SendReport::Email->new() ); } } @@ -392,6 +419,7 @@ sub send_reports { my $result = -1; for my $sender ( keys %reporters ) { + debug_print("sending using " . $sender, $row->id) if $debug_mode; $result *= $reporters{ $sender }->send( $row, \%h ); if ( $reporters{ $sender }->unconfirmed_counts) { foreach my $e (keys %{ $reporters{ $sender }->unconfirmed_counts } ) { @@ -414,6 +442,7 @@ sub send_reports { if ( $cobrand->report_sent_confirmation_email && !$h{anonymous_report}) { _send_report_sent_email( $row, \%h, $nomail ); } + debug_print("send successful: OK", $row->id) if $debug_mode; } else { my @errors; for my $sender ( keys %reporters ) { @@ -422,34 +451,35 @@ sub send_reports { } } $row->update_send_failed( join( '|', @errors ) ); + debug_print("send FAILED: " . join( '|', @errors ), $row->id) if $debug_mode; + } + } + if ($debug_mode) { + print "\n"; + if ($debug_unsent_count) { + debug_print("processed all unsent reports (total: $debug_unsent_count)"); + } else { + debug_print("no unsent reports were found (must have whensent=null and suitable bodies_str & state) -- nothing to send"); } } - if ($verbose) { + if ($verbose || $debug_mode) { print "Council email addresses that need checking:\n" if keys %notgot; foreach my $e (keys %notgot) { foreach my $c (keys %{$notgot{$e}}) { print " " . $notgot{$e}{$c} . " problem, to $e category $c (" . $note{$e}{$c}. ")\n"; } } - if (keys %sending_skipped_by_method) { - my $c = 0; - print "\nProblem reports that send-reports did not attempt to send the following:\n"; - foreach my $send_method (sort keys %sending_skipped_by_method) { - printf " %-24s %4d\n", "$send_method:", $sending_skipped_by_method{$send_method}; - $c+=$sending_skipped_by_method{$send_method}; - } - printf " %-24s %4d\n", "Total:", $c; - } my $sending_errors = ''; my $unsent = FixMyStreet::App->model("DB::Problem")->search( { state => [ 'confirmed', 'fixed' ], whensent => undef, - council => { '!=', undef }, + bodies_str => { '!=', undef }, send_fail_count => { '>', 0 } } ); while (my $row = $unsent->next) { - $sending_errors .= "* http://www.fixmystreet.com/report/" . $row->id . ", failed " + my $base_url = mySociety::Config::get('BASE_URL'); + $sending_errors .= "* " . $base_url . "/report/" . $row->id . ", failed " . $row->send_fail_count . " times, last at " . $row->send_fail_timestamp . ", reason " . $row->send_fail_reason . "\n"; } @@ -485,4 +515,11 @@ sub _send_report_sent_email { ); } +sub debug_print { + my $msg = shift; + my $id = shift || ''; + $id = "report $id: " if $id; + print "[] $id$msg\n"; +} + 1; diff --git a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm index d6b3eb5cb..6f2c19b5e 100644 --- a/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm +++ b/perllib/FixMyStreet/DB/ResultSet/Questionnaire.pm @@ -50,6 +50,7 @@ sub send_questionnaires_period { # Not all cobrands send questionnaires next unless $cobrand->send_questionnaires; + next if $row->is_from_abuser; # Cobranded and non-cobranded messages can share a database. In this case, the conf file # should specify a vhost to send the reports for each cobrand, so that they don't get sent @@ -89,9 +90,8 @@ sub send_questionnaires_period { } ); $h{url} = $cobrand->base_url($row->cobrand_data) . '/Q/' . $token->token; - my $sender = $cobrand->contact_email; + my $sender = FixMyStreet->config('DO_NOT_REPLY_EMAIL'); my $sender_name = _($cobrand->contact_name); - $sender =~ s/team/fms-DO-NOT-REPLY/; print "Sending questionnaire " . $questionnaire->id . ", problem " . $row->id . ", token " . $token->token . " to " |