Merge branch 'reduce-fixmystreet-app'

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;