[Oxfordshire] Old report archiving script

This script can be used for the bulk closure of old reports that haven't been
updated in a long time.

Reports that haven't been updated in a *really* long time (since 2014, by
default) are simply closed, and those that haven't been updated since 2015 (by
default) are closed and an email explaining the closure is sent to the report
creator.

This script can be used for other cobrands, but they'll need an
archive.{txt,html} template in order for the emails to be sent.

For mysociety/fixmystreetforcouncils#51

5 files changed, 419 insertions, 0 deletions

diff --git a/bin/oxfordshire/archive-old-enquiries b/bin/oxfordshire/archive-old-enquiries
new file mode 100755
index 000000000..7fe66703a
--- /dev/null
+++ b/bin/oxfordshire/archive-old-enquiries
@@ -0,0 +1,29 @@
+#!/usr/bin/env perl
+
+use strict;
+use warnings;
+require 5.8.0;
+
+BEGIN {
+    use File::Basename qw(dirname);
+    use File::Spec;
+    my $d = dirname(File::Spec->rel2abs($0));
+    require "$d/../../setenv.pl";
+}
+
+use FixMyStreet::Script::ArchiveOldEnquiries;
+use Getopt::Long::Descriptive;
+
+my ($opts, $usage) = describe_options(
+    '%c %o',
+    ['commit|c',     "actually close reports and send emails. Omitting this flag will do a dry-run"],
+    ['body|b=s',     "which body ID to close reports for"],
+    ['cobrand=s',     "which cobrand template to use for sent emails"],
+    ['closure-cutoff=s',     "Anything before this will be closed with no email"],
+    ['email-cutoff=s',     "Anything before this will be closed with an email sent to the reporter"],
+    ['limit|l=s',     "limit to a certain number of reports/users to be closed"],
+    ['help|h',       "print usage message and exit" ],
+);
+print($usage->text), exit if $opts->help;
+
+FixMyStreet::Script::ArchiveOldEnquiries::archive($opts);
diff --git a/perllib/FixMyStreet/Script/ArchiveOldEnquiries.pm b/perllib/FixMyStreet/Script/ArchiveOldEnquiries.pm
new file mode 100644
index 000000000..5d1d45379
--- /dev/null
+++ b/perllib/FixMyStreet/Script/ArchiveOldEnquiries.pm
@@ -0,0 +1,141 @@
+package FixMyStreet::Script::ArchiveOldEnquiries;
+
+use strict;
+use warnings;
+require 5.8.0;
+
+use FixMyStreet;
+use FixMyStreet::App;
+use FixMyStreet::DB;
+use FixMyStreet::Cobrand;
+use FixMyStreet::Map;
+use FixMyStreet::Email;
+
+
+my $opts = {
+    commit => 0,
+    body => '2237',
+    cobrand => 'oxfordshire',
+    closure_cutoff => "2015-01-01 00:00:00",
+    email_cutoff => "2016-01-01 00:00:00",
+};
+
+sub query {
+    return {
+        bodies_str => { 'LIKE', "%".$opts->{body}."%"},
+        -and       => [
+          lastupdate => { '<', $opts->{email_cutoff} },
+          lastupdate => { '>', $opts->{closure_cutoff} },
+        ],
+        state      => [ FixMyStreet::DB::Result::Problem->open_states() ],
+    };
+}
+
+sub archive {
+    my $params = shift;
+    if ( $params ) {
+        $opts = {
+            %$opts,
+            %$params,
+        };
+    }
+
+    unless ( $opts->{commit} ) {
+        printf "Doing a dry run; emails won't be sent and reports won't be closed.\n";
+        printf "Re-run with --commit to actually archive reports.\n\n";
+    }
+
+    my @user_ids = FixMyStreet::DB->resultset('Problem')->search(query(),
+    {
+        distinct => 1,
+        columns  => ['user_id'],
+        rows => $opts->{limit},
+    })->all;
+
+    @user_ids = map { $_->user_id } @user_ids;
+
+    my $users = FixMyStreet::DB->resultset('User')->search({
+        id => \@user_ids
+    });
+
+    my $user_count = $users->count;
+    my $problem_count = FixMyStreet::DB->resultset('Problem')->search(query(),
+    {
+        columns  => ['id'],
+        rows => $opts->{limit},
+    })->count;
+
+    printf("%d users will receive closure emails about %d reports which will be closed.\n", $user_count, $problem_count);
+
+    if ( $opts->{commit} ) {
+        my $i = 0;
+        while ( my $user = $users->next ) {
+            printf("%d/%d: User ID %d\n", ++$i, $user_count, $user->id);
+            send_email_and_close($user);
+        }
+    }
+
+    my $problems_to_close = FixMyStreet::DB->resultset('Problem')->search({
+        bodies_str => { 'LIKE', "%".$opts->{body}."%"},
+        lastupdate => { '<', $opts->{closure_cutoff} },
+        state      => [ FixMyStreet::DB::Result::Problem->open_states() ],
+    }, {
+        rows => $opts->{limit},
+    });
+
+    printf("Closing %d old reports, without sending emails: ", $problems_to_close->count);
+
+    if ( $opts->{commit} ) {
+        $problems_to_close->update({ state => 'closed', send_questionnaire => 0 });
+    }
+
+    printf("done.\n")
+}
+
+sub send_email_and_close {
+    my ($user) = @_;
+
+    my $problems = $user->problems->search(query(), {
+        order_by => { -desc => 'confirmed' },
+    });
+
+    my @problems = $problems->all;
+
+    return if scalar(@problems) == 0;
+
+    my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker($opts->{cobrand})->new();
+    $cobrand->set_lang_and_domain($problems[0]->lang, 1);
+    FixMyStreet::Map::set_map_class($cobrand->map_type);
+
+    my %h = (
+      reports => [@problems],
+      report_count => scalar(@problems),
+      site_name => $cobrand->moniker,
+      user => $user,
+      cobrand => $cobrand,
+    );
+
+    # Send email
+    printf("    Sending email about %d reports: ", scalar(@problems));
+    my $email_error = FixMyStreet::Email::send_cron(
+        $problems->result_source->schema,
+        'archive.txt',
+        \%h,
+        {
+            To => [ [ $user->email, $user->name ] ],
+        },
+        undef,
+        undef,
+        $cobrand,
+        $problems[0]->lang,
+    );
+
+    unless ( $email_error ) {
+        printf("done.\n    Closing reports: ");
+
+        $problems->update({ state => 'closed', send_questionnaire => 0 });
+        printf("done.\n");
+    } else {
+        printf("error! Not closing reports for this user.\n")
+    }
+}
diff --git a/t/app/script/archive_old_enquiries.t b/t/app/script/archive_old_enquiries.t
new file mode 100644
index 000000000..e87d6a0f8
--- /dev/null
+++ b/t/app/script/archive_old_enquiries.t
@@ -0,0 +1,163 @@
+use strict;
+use warnings;
+use Test::More;
+use FixMyStreet::TestMech;
+use FixMyStreet::Script::ArchiveOldEnquiries;
+
+mySociety::Locale::gettext_domain( 'FixMyStreet' );
+
+my $mech = FixMyStreet::TestMech->new();
+
+$mech->clear_emails_ok;
+
+my $opts = {
+    commit => 1,
+};
+
+my $user = $mech->create_user_ok('test@example.com', name => 'Test User');
+my $oxfordshire = $mech->create_body_ok(2237, 'Oxfordshire County Council', id => 2237);
+my $west_oxon = $mech->create_body_ok(2420, 'West Oxfordshire District Council', id => 2420);
+
+subtest 'sets reports to the correct status' => sub {
+    FixMyStreet::override_config {
+          ALLOWED_COBRANDS => [ 'oxfordshire' ],
+    }, sub {
+        my ($report) = $mech->create_problems_for_body(1, $oxfordshire->id, 'Test', {
+            areas      => ',2237,',
+            user_id    => $user->id,
+        });
+
+        my ($report1) = $mech->create_problems_for_body(1, $oxfordshire->id . "," .$west_oxon->id, 'Test', {
+            areas      => ',2237,',
+            lastupdate => '2015-12-01 07:00:00',
+            user       => $user,
+        });
+
+        my ($report2) = $mech->create_problems_for_body(1, $oxfordshire->id, 'Test 2', {
+            areas      => ',2237,',
+            lastupdate => '2015-12-01 08:00:00',
+            user       => $user,
+            state      => 'investigating',
+        });
+
+        my ($report3, $report4) = $mech->create_problems_for_body(2, $oxfordshire->id, 'Test', {
+            areas      => ',2237,',
+            lastupdate => '2014-12-01 07:00:00',
+            user       => $user,
+        });
+
+        my ($report5) = $mech->create_problems_for_body(1, $oxfordshire->id . "," .$west_oxon->id, 'Test', {
+            areas      => ',2237,',
+            lastupdate => '2014-12-01 07:00:00',
+            user       => $user,
+            state      => 'in progress'
+        });
+
+        FixMyStreet::Script::ArchiveOldEnquiries::archive($opts);
+
+        $report->discard_changes;
+        $report1->discard_changes;
+        $report2->discard_changes;
+        $report3->discard_changes;
+        $report4->discard_changes;
+        $report5->discard_changes;
+
+        is $report1->state, 'closed', 'Report 1 has been set to closed';
+        is $report2->state, 'closed', 'Report 2 has been set to closed';
+        is $report3->state, 'closed', 'Report 3 has been set to closed';
+        is $report4->state, 'closed', 'Report 4 has been set to closed';
+        is $report5->state, 'closed', 'Report 5 has been set to closed';
+
+        is $report->state, 'confirmed', 'Recent report has been left alone';
+    };
+};
+
+subtest 'sends emails to a user' => sub {
+    FixMyStreet::override_config {
+      ALLOWED_COBRANDS => [ 'oxfordshire' ],
+    }, sub {
+        $mech->clear_emails_ok;
+        $mech->email_count_is(0);
+
+        $mech->create_problems_for_body(1, $oxfordshire->id, 'Shiny new report', {
+            areas      => ',2237,',
+            user       => $user,
+        });
+
+        $mech->create_problems_for_body(1, $oxfordshire->id, 'Problem the first', {
+            areas      => ',2237,',
+            lastupdate => '2015-12-01 07:00:00',
+            user       => $user,
+        });
+
+        $mech->create_problems_for_body(1, $oxfordshire->id, 'Problem the second', {
+            areas      => ',2237,',
+            lastupdate => '2015-12-01 07:00:00',
+            user       => $user,
+        });
+
+        $mech->create_problems_for_body(1, $oxfordshire->id, 'Problem the third', {
+            areas      => ',2237,',
+            lastupdate => '2015-12-01 07:00:00',
+            user       => $user,
+        });
+
+        $mech->create_problems_for_body(1, $oxfordshire->id, 'Really old report', {
+            areas      => ',2237,',
+            lastupdate => '2014-12-01 07:00:00',
+            user       => $user,
+        });
+
+        FixMyStreet::Script::ArchiveOldEnquiries::archive($opts);
+
+        my @emails = $mech->get_email;
+        $mech->email_count_is(1);
+
+        my $email = $emails[0];
+        my $body = $mech->get_text_body_from_email($email);
+
+        like $body, qr/Problem the first/, 'Email body matches report name';
+        like $body, qr/Problem the second/, 'Email body matches report name';
+        like $body, qr/Problem the third/, 'Email body matches report name';
+
+        unlike  $body, qr/Shiny new report/, 'Email body does not have new report';
+        unlike  $body, qr/Really old report/, 'Email body does not have old report';
+    };
+};
+
+subtest 'user with old reports does not get email' => sub {
+  $mech->clear_emails_ok;
+  $mech->email_count_is(0);
+
+  $mech->create_problems_for_body(4, $oxfordshire->id, 'Really old report', {
+      areas      => ',2237,',
+      lastupdate => '2014-12-01 07:00:00',
+      user       => $user,
+  });
+
+  FixMyStreet::Script::ArchiveOldEnquiries::archive($opts);
+
+  my @emails = $mech->get_email;
+  $mech->email_count_is(0);
+};
+
+subtest 'user with new reports does not get email' => sub {
+  $mech->clear_emails_ok;
+  $mech->email_count_is(0);
+
+  $mech->create_problems_for_body(4, $oxfordshire->id, 'Shiny new report', {
+      areas      => ',2237,',
+      user       => $user,
+  });
+
+  FixMyStreet::Script::ArchiveOldEnquiries::archive($opts);
+
+  $mech->email_count_is(0);
+};
+
+done_testing();
+
+END {
+    $mech->delete_user($user);
+    $mech->delete_body($oxfordshire);
+}
diff --git a/templates/email/oxfordshire/archive.html b/templates/email/oxfordshire/archive.html
new file mode 100644
index 000000000..ed48456a2
--- /dev/null
+++ b/templates/email/oxfordshire/archive.html
@@ -0,0 +1,58 @@
+[%
+
+email_summary = "Your reports on " _ site_name;
+
+PROCESS '_email_settings.html';
+
+INCLUDE '_email_top.html';
+
+%]
+
+<th style="[% td_style %][% only_column_style %]">
+  <h1 style="[% h1_style %]">Your [% site_name %] reports on FixMyStreet</h1>
+  <p style="[% p_style %]">
+    Hello [% user.name %],
+  </p>
+  <p style="[% p_style %]">
+    FixMyStreet is being updated in Oxfordshire to
+    improve how problems get fixed.
+  </p>
+  <p style="[% p_style %]">
+    As part of these updates, we are closing old reports that appear to be
+    resolved but remain open in the system.
+  </p>
+  <p style="[% p_style %]">
+    We noticed that you have [% report_count %] old [% nget('report', 'reports', report_count) %] on the system,
+    which we've listed below.
+  </p>
+  <p style="[% p_style %]">
+    If your report is no longer an issue, you don't need to do anything.
+  </p>
+  <p style="[% p_style %]">
+    If you believe that your report is still a problem, you can reopen it by
+    clicking the 'reopen' button by a report.
+  </p>
+
+  [% FOR report IN reports %]
+  <div style="[% list_item_style %]">
+  [% IF report.photo %]
+    <a href="[% cobrand.base_url_for_report( report ) %]/report/[% report.id %]">
+      <img style="[% list_item_photo_style %]" src="[% inline_image(report.get_first_image_fp) %]" alt="">
+    </a>
+  [% END %]
+    <h2 style="[% list_item_h2_style %]">
+      [%~ report.title | html ~%]
+    </h2>
+    <p style="[% list_item_p_style %]">[% report.detail | html %]</p>
+    <p style="[% list_item_date_style %]">
+      Reported [% report.time_ago %] ago.
+    </p>
+    <p>
+      <a style="[% button_style %]" href="[% cobrand.base_url_for_report( report ) %][% report.tokenised_url( user, { reopen => 'true' } ) %]#update_form">Reopen report</a>
+    </p>
+  </div>
+  [% END %]
+
+</th>
+
+[% INCLUDE '_email_bottom.html' %]
diff --git a/templates/email/oxfordshire/archive.txt b/templates/email/oxfordshire/archive.txt
new file mode 100644
index 000000000..6ecf5b02f
--- /dev/null
+++ b/templates/email/oxfordshire/archive.txt
@@ -0,0 +1,28 @@
+Subject: Your reports on [% site_name %]
+
+Hello [% user.name %],
+
+FixMyStreet is being updated in Oxfordshire to improve how problems get fixed.
+
+As part of these updates, we are closing old reports that appear to be resolved but remain open in the system.
+
+We noticed that you have [% report_count %] old [% nget('report', 'reports', report_count) %] on the system, which we've listed below.
+
+If your report is no longer an issue, you don't need to do anything.
+
+If you believe that your report is still a problem, you can reopen it by clicking or copying and pasting
+the link marked 'Reopen?' by a report.
+
+[% FOR report IN reports %]
+
+[% report.title %]
+
+Reported [% report.time_ago %] ago.
+
+Reopen? [% cobrand.base_url_for_report( report ) %][% report.tokenised_url( user, { reopen => 'true' } ) %]#update_form
+
+----
+
+[% END %]
+
+The mySociety team and Oxfordshire County Council