diff options
Diffstat (limited to 'perllib/FixMyStreet/Script/Alerts.pm')
| -rw-r--r-- | perllib/FixMyStreet/Script/Alerts.pm | 293 |
1 files changed, 293 insertions, 0 deletions
diff --git a/perllib/FixMyStreet/Script/Alerts.pm b/perllib/FixMyStreet/Script/Alerts.pm new file mode 100644 index 000000000..fea897a24 --- /dev/null +++ b/perllib/FixMyStreet/Script/Alerts.pm @@ -0,0 +1,293 @@ +package FixMyStreet::Script::Alerts; + +use strict; +use warnings; + +use DateTime::Format::Pg; +use IO::String; + +use mySociety::DBHandle qw(dbh); +use mySociety::Gaze; +use mySociety::Locale; +use mySociety::MaPit; +use RABX; + +use FixMyStreet::Cobrand; +use FixMyStreet::DB; +use FixMyStreet::Email; + +FixMyStreet->configure_mysociety_dbhandle; + +# Child must have confirmed, id, email, state(!) columns +# If parent/child, child table must also have name and text +# and foreign key to parent must be PARENT_id +sub send() { + my $rs = FixMyStreet::DB->resultset('AlertType'); + my $schema = $rs->result_source->schema; + + my $q = $rs->search( { ref => { -not_like => '%local_problems%' } } ); + while (my $alert_type = $q->next) { + my $ref = $alert_type->ref; + my $head_table = $alert_type->head_table; + my $item_table = $alert_type->item_table; + my $query = 'select alert.id as alert_id, alert.user_id as alert_user_id, alert.lang as alert_lang, alert.cobrand as alert_cobrand, + alert.cobrand_data as alert_cobrand_data, alert.parameter as alert_parameter, alert.parameter2 as alert_parameter2, '; + if ($head_table) { + $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, $item_table, $head_table + where alert.parameter::integer = $head_table.id + and $item_table.${head_table}_id = $head_table.id + "; + } else { + $query .= " $item_table.*, + $item_table.id as item_id + from alert, $item_table + where 1 = 1"; + } + $query .= " + and alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed + and $item_table.confirmed >= current_timestamp - '7 days'::interval + and (select whenqueued from alert_sent where alert_sent.alert_id = alert.id and alert_sent.parameter::integer = $item_table.id) is null + and $item_table.user_id <> alert.user_id + and " . $alert_type->item_where . " + and alert.confirmed = 1 + order by alert.id, $item_table.confirmed"; + # XXX Ugh - needs work + $query =~ s/\?/alert.parameter/ if ($query =~ /\?/); + $query =~ s/\?/alert.parameter2/ if ($query =~ /\?/); + + $query = dbh()->prepare($query); + $query->execute(); + my $last_alert_id; + my %data = ( template => $alert_type->template, data => '', schema => $schema ); + 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 + # 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; + + # this is for the new_updates alerts + next if $row->{non_public} and $row->{user_id} != $row->{alert_user_id}; + + $schema->resultset('AlertSent')->create( { + alert_id => $row->{alert_id}, + parameter => $row->{item_id}, + } ); + if ($last_alert_id && $last_alert_id != $row->{alert_id}) { + _send_aggregated_alert_email(%data); + %data = ( template => $alert_type->template, data => '', schema => $schema ); + } + + # create problem status message for the templates + if ( FixMyStreet::DB::Result::Problem::fixed_states()->{$row->{state}} ) { + $data{state_message} = _("This report is currently marked as fixed."); + } elsif ( FixMyStreet::DB::Result::Problem::closed_states()->{$row->{state}} ) { + $data{state_message} = _("This report is currently marked as closed.") + } else { + $data{state_message} = _("This report is currently marked as open."); + } + + my $url = $cobrand->base_url_for_report($row); + # this is currently only for new_updates + if ($row->{item_text}) { + 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 = $schema->resultset('User')->find( { + id => $row->{alert_user_id} + } ); + $data{alert_email} = $user->email; + my $token_obj = $schema->resultset('Token')->create( { + scope => 'alert_to_reporter', + data => { + id => $row->{id}, + } + } ); + $data{problem_url} = $url . "/R/" . $token_obj->token; + } else { + $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 ) { + my $parser = DateTime::Format::Pg->new(); + my $dt = $parser->parse_timestamp( $row->{item_confirmed} ); + # 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. + # this is basically recreating the code from the inflate wrapper + # in the database model. + FixMyStreet->set_time_zone($dt); + $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 { + $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\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); + if ($ref eq 'area_problems' || $ref eq 'council_problems' || $ref eq 'ward_problems') { + my $va_info = mySociety::MaPit::call('area', $row->{alert_parameter}); + $data{area_name} = $va_info->{name}; + } + if ($ref eq 'ward_problems') { + my $va_info = mySociety::MaPit::call('area', $row->{alert_parameter2}); + $data{ward_name} = $va_info->{name}; + } + } + $data{cobrand} = $row->{alert_cobrand}; + $data{cobrand_data} = $row->{alert_cobrand_data}; + $data{lang} = $row->{alert_lang}; + $last_alert_id = $row->{alert_id}; + } + if ($last_alert_id) { + _send_aggregated_alert_email(%data); + } + } + + # Nearby done separately as the table contains the parameters + my $template = $rs->find( { ref => 'local_problems' } )->template; + my $query = $schema->resultset('Alert')->search( { + alert_type => 'local_problems', + whendisabled => undef, + confirmed => 1 + }, { + order_by => 'id' + } ); + 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; + my $d = mySociety::Gaze::get_radius_containing_population($latitude, $longitude, 200000); + # Convert integer to GB locale string (with a ".") + $d = mySociety::Locale::in_gb_locale { + sprintf("%f", int($d*10+0.5)/10); + }; + 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, + schema => $schema, + ); + 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) + and problem.non_public = 'f' + and problem.confirmed >= ? and problem.confirmed >= current_timestamp - '7 days'::interval + and (select whenqueued from alert_sent where alert_sent.alert_id = ? and alert_sent.parameter::integer = problem.id) is null + and users.email <> ? + order by confirmed desc"; + $q = dbh()->prepare($q); + $q->execute($latitude, $longitude, $d, $alert->whensubscribed, $alert->id, $alert->user->email); + while (my $row = $q->fetchrow_hashref) { + $schema->resultset('AlertSent')->create( { + alert_id => $alert->id, + parameter => $row->{id}, + } ); + my $url = $cobrand->base_url_for_report($row); + $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\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}; + } +} + +sub _send_aggregated_alert_email(%) { + my %data = @_; + + my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($data{cobrand})->new(); + + $cobrand->set_lang_and_domain( $data{lang}, 1, FixMyStreet->path_to('locale')->stringify ); + + if (!$data{alert_email}) { + my $user = $data{schema}->resultset('User')->find( { + id => $data{alert_user_id} + } ); + $data{alert_email} = $user->email; + } + + my ($domain) = $data{alert_email} =~ m{ @ (.*) \z }x; + return if $data{schema}->resultset('Abuse')->search( { + email => [ $data{alert_email}, $domain ] + } )->first; + + my $token = $data{schema}->resultset("Token")->new_result( { + scope => 'alert', + data => { + id => $data{alert_id}, + type => 'unsubscribe', + email => $data{alert_email}, + } + } ); + $data{unsubscribe_url} = $cobrand->base_url( $data{cobrand_data} ) . '/A/' . $token->token; + + my $template = FixMyStreet->get_email_template($cobrand->moniker, $data{lang}, "$data{template}.txt"); + + my $result = FixMyStreet::Email::send_cron( + $data{schema}, + { + _template_ => $template, + _parameters_ => \%data, + To => $data{alert_email}, + }, + undef, + 0, + $cobrand, + $data{lang} + ); + + unless ($result) { + $token->insert(); + } else { + print "Failed to send alert $data{alert_id}!"; + } +} + +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); + + my $str = ''; + + my $address = $data->{resourceSets}[0]{resources}[0]{address}; + my @address; + push @address, $address->{addressLine} if $address->{addressLine} && $address->{addressLine} ne 'Street'; + push @address, $address->{locality} if $address->{locality}; + $str .= sprintf(_("Nearest road to the pin placed on the map (automatically generated by Bing Maps): %s\n\n"), + join( ', ', @address ) ) if @address; + + return $str; +} + +1; |