aboutsummaryrefslogtreecommitdiffstats
path: root/perllib/FixMyStreet/DB
diff options
context:
space:
mode:
Diffstat (limited to 'perllib/FixMyStreet/DB')
-rw-r--r--perllib/FixMyStreet/DB/Result/Alert.pm12
-rw-r--r--perllib/FixMyStreet/DB/ResultSet/AlertType.pm204
2 files changed, 214 insertions, 2 deletions
diff --git a/perllib/FixMyStreet/DB/Result/Alert.pm b/perllib/FixMyStreet/DB/Result/Alert.pm
index b99c83a45..53cb96ff4 100644
--- a/perllib/FixMyStreet/DB/Result/Alert.pm
+++ b/perllib/FixMyStreet/DB/Result/Alert.pm
@@ -85,10 +85,18 @@ Sets the state of the alert to confirmed.
sub confirm {
my $self = shift;
- return if $self->confirmed and $self->confirmed == 1 and $self->whendisabled ne 'null';
-
$self->confirmed(1);
$self->whendisabled(undef);
+ $self->update;
+
+ return 1;
+}
+
+sub disable {
+ my $self = shift;
+
+ $self->whendisabled( \'ms_current_timestamp()' );
+ $self->update;
return 1;
}
diff --git a/perllib/FixMyStreet/DB/ResultSet/AlertType.pm b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
new file mode 100644
index 000000000..405210861
--- /dev/null
+++ b/perllib/FixMyStreet/DB/ResultSet/AlertType.pm
@@ -0,0 +1,204 @@
+package FixMyStreet::DB::ResultSet::AlertType;
+use base 'DBIx::Class::ResultSet';
+
+use strict;
+use warnings;
+
+use File::Slurp;
+
+use mySociety::DBHandle qw(dbh);
+use mySociety::EmailUtil;
+use mySociety::Gaze;
+use mySociety::Locale;
+use mySociety::MaPit;
+
+# 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 email_alerts ($) {
+ my ( $rs ) = @_;
+
+ 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.email as alert_email, 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.name as item_name, $item_table.text as item_text,
+ $head_table.*
+ from alert
+ inner join $item_table on alert.parameter::integer = $item_table.${head_table}_id
+ inner join $head_table on alert.parameter::integer = $head_table.id
+ inner join users as alert_user on alert.user_id = alert_user.id";
+ } else {
+ $query .= " $item_table.*,
+ $item_table.id as item_id
+ from alert
+ cross join $item_table
+ inner join users as alert_user on alert.user_id = alert_user.id";
+ }
+ $query .= "
+ where alert_type='$ref' and whendisabled is null and $item_table.confirmed >= whensubscribed
+ and $item_table.confirmed >= ms_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 => '' );
+ while (my $row = $query->fetchrow_hashref) {
+
+ my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($row->{alert_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
+ # 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;
+
+ FixMyStreet::App->model('DB::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 => '' );
+ }
+
+ # create problem status message for the templates
+ $data{state_message} =
+ $row->{state} eq 'fixed'
+ ? _("This report is currently marked as fixed.")
+ : _("This report is currently marked as open.");
+
+ my $url = $cobrand->base_url_for_emails( $row->{alert_cobrand_data} );
+ if ($row->{item_text}) {
+ $data{problem_url} = $url . "/report/" . $row->{id};
+ $data{data} .= $row->{item_name} . ' : ' if $row->{item_name};
+ $data{data} .= $row->{item_text} . "\n\n------\n\n";
+ } else {
+ $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\n\n";
+ }
+ if (!$data{alert_email}) {
+ %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 = FixMyStreet::App->model('DB::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;
+
+ my $longitude = $alert->parameter;
+ my $latitude = $alert->parameter2;
+ my $url = $cobrand->base_url_for_emails( $alert->cobrand_data );
+ my ($site_restriction, $site_id) = $cobrand->site_restriction( $alert->cobrand_data );
+ 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 %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
+ where nearby.problem_id = problem.id
+ and problem.user_id = users.id
+ and problem.state in ('confirmed', 'fixed')
+ and problem.confirmed >= ? and problem.confirmed >= ms_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 <> ?
+ $site_restriction
+ 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) {
+ FixMyStreet::App->model('DB::AlertSent')->create( {
+ alert_id => $alert->id,
+ parameter => $row->{id},
+ } );
+ $data{data} .= $url . "/report/" . $row->{id} . " - $row->{title}\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 );
+
+ my $token = FixMyStreet::App->model("DB::Token")->new_result( {
+ scope => 'alert',
+ data => {
+ id => $data{alert_id},
+ type => 'unsubscribe',
+ email => $data{alert_email},
+ }
+ } );
+ $data{unsubscribe_url} = $cobrand->base_url_for_emails( $data{cobrand_data} ) . '/A/' . $token->token;
+
+ my $template = FixMyStreet->path_to(
+ "templates", "email", $cobrand->moniker, "$data{template}.txt"
+ )->stringify;
+ my $template_cobrand = FixMyStreet->path_to(
+ "templates", "email", $cobrand->moniker, $data{lang}, "$data{template}.txt"
+ )->stringify;
+ $template = $template_cobrand if -e $template_cobrand;
+ $template = File::Slurp::read_file($template);
+
+ my $sender = $cobrand->contact_email;
+ (my $from = $sender) =~ s/team/fms-DO-NOT-REPLY/; # XXX
+ my $result = FixMyStreet::App->send_email_cron(
+ {
+ _template_ => $template,
+ _parameters_ => \%data,
+ From => [ $from, _($cobrand->contact_name) ],
+ To => $data{alert_email},
+ },
+ $sender,
+ [ $data{alert_email} ],
+ 0,
+ );
+
+ if ($result == mySociety::EmailUtil::EMAIL_SUCCESS) {
+ $token->insert();
+ } else {
+ print "Failed to send alert $data{alert_id}!";
+ }
+}
+
+1;
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-19 10:49:19 +0000