[Oxfordshire] Send RDI reports by email.

4 files changed, 236 insertions, 182 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm
index 31638662f..1711ecb10 100644
--- a/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm
@@ -2,9 +2,9 @@ package FixMyStreet::App::Controller::Admin::ExorDefects;
 use Moose;
 use namespace::autoclean;
 
-use Text::CSV;
 use DateTime;
-use mySociety::Random qw(random_bytes);
+use Try::Tiny;
+use FixMyStreet::Integrations::ExorRDI;
 
 BEGIN { extends 'Catalyst::Controller'; }
 
@@ -54,186 +54,30 @@ sub download : Path('download') : Args(0) {
     my $end_date = $parser-> parse_datetime ( $c->get_param('end_date') ) ;
     my $one_day = DateTime::Duration->new( days => 1 );
 
-    my %params = (
-        -and => [
-            state => [ 'action scheduled' ],
-            external_id => { '!=' => undef },
-            'admin_log_entries.action' => 'inspected',
-            'admin_log_entries.whenedited' => { '>=', $start_date },
-            'admin_log_entries.whenedited' => { '<=', $end_date + $one_day },
-        ]
-    );
-
-    my $user;
-    if ( $c->get_param('user_id') ) {
-        my $uid = $c->get_param('user_id');
-        $params{'admin_log_entries.user_id'} = $uid;
-        $user = $c->model('DB::User')->find( { id => $uid } );
-    }
-
-    my $problems = $c->cobrand->problems->search(
-        \%params,
-        {
-            join => 'admin_log_entries',
-            distinct => 1,
-        }
-    );
-
-    if ( !$problems->count ) {
-        if ( defined $user ) {
+    my $params = {
+        start_date => $start_date,
+        end_date => $end_date + $one_day,
+        user => $c->get_param('user_id'),
+    };
+    my $rdi = FixMyStreet::Integrations::ExorRDI->new($params);
+
+    try {
+        my $out = $rdi->construct;
+        $c->res->content_type('text/csv; charset=utf-8');
+        $c->res->header('content-disposition' => "attachment; filename=" . $rdi->filename);
+        $c->res->body( $out );
+    } catch {
+        die $_ unless $_ =~ /FixMyStreet::Integrations::ExorRDI::Error/;
+        if ($params->{user}) {
             $c->flash->{error_message} = _("No inspections by that inspector in the selected date range.");
         } else {
             $c->flash->{error_message} = _("No inspections in the selected date range.");
         }
-        $c->flash->{start_date} = $start_date;
-        $c->flash->{end_date} = $end_date;
-        $c->flash->{user_id} = $user->id if $user;
+        $c->flash->{start_date} = $params->{start_date};
+        $c->flash->{end_date} = $params->{end_date};
+        $c->flash->{user_id} = $params->{user};
         $c->res->redirect( $c->uri_for( '' ) );
-    }
-
-    # A single RDI file might contain inspections from multiple inspectors, so
-    # we need to group inspections by inspector within G records.
-    my $inspectors = {};
-    my $inspector_initials = {};
-    while ( my $report = $problems->next ) {
-        my $user = $report->inspection_log_entry->user;
-        $inspectors->{$user->id} ||= [];
-        push @{ $inspectors->{$user->id} }, $report;
-        unless ( $inspector_initials->{$user->id} ) {
-            $inspector_initials->{$user->id} = $user->get_extra_metadata('initials');
-        }
-    }
-
-    my $csv = Text::CSV->new({ binary => 1, eol => "" });
-
-    my $p_count = 0;
-    my $link_id = $c->cobrand->exor_rdi_link_id;
-
-    # RDI first line is always the same
-    $csv->combine("1", "1.8", "1.0.0.0", "ENHN", "");
-    my @body = ($csv->string);
-
-    my $i = 0;
-    foreach my $inspector_id (keys %$inspectors) {
-        my $inspections = $inspectors->{$inspector_id};
-        my $initials = $inspector_initials->{$inspector_id};
-
-        $csv->combine(
-            "G", # start of an area/sequence
-            $link_id, # area/link id, fixed value for our purposes
-            "","", # must be empty
-            $initials || "XX", # inspector initials
-            $start_date->strftime("%y%m%d"), # date of inspection yymmdd
-            "0700", # time of inspection hhmm, set to static value for now
-            "D", # inspection variant, should always be D
-            "INS", # inspection type, always INS
-            "N", # Area of the county - north (N) or south (S)
-            "", "", "", "" # empty fields
-        );
-        push @body, $csv->string;
-
-        $csv->combine(
-            "H", # initial inspection type
-            "MC" # minor carriageway (changes depending on activity code)
-        );
-        push @body, $csv->string;
-
-        foreach my $report (@$inspections) {
-            my ($eastings, $northings) = $report->local_coords;
-
-            my $location = "${eastings}E ${northings}N.";
-            $location = "[DID NOT USE MAP] $location" unless $report->used_map;
-            my $closest_address = $c->cobrand->find_closest($report, 1);
-            if (%$closest_address) {
-                $location .= " Nearest road: $closest_address->{road}." if $closest_address->{road};
-                $location .= " Nearest postcode: $closest_address->{postcode}{postcode}." if $closest_address->{postcode};
-            }
-
-            my $description = sprintf("%s %s", $report->external_id || "", $report->get_extra_metadata('detailed_information') || "");
-            my $activity_code = $report->defect_type ?
-                $report->defect_type->get_extra_metadata('activity_code')
-                : 'MC';
-            my $traffic_information = $report->get_extra_metadata('traffic_information') ?
-                'TM ' . $report->get_extra_metadata('traffic_information')
-                : 'TM none';
-
-            $csv->combine(
-                "I", # beginning of defect record
-                $activity_code, # activity code - minor carriageway, also FC (footway)
-                "", # empty field, can also be A (seen on MC) or B (seen on FC)
-                sprintf("%03d", ++$i), # randomised sequence number
-                $location, # defect location field, which we don't capture from inspectors
-                $report->inspection_log_entry->whenedited->strftime("%H%M"), # defect time raised
-                "","","","","","","", # empty fields
-                $traffic_information,
-                $description, # defect description
-            );
-            push @body, $csv->string;
-
-            my $defect_type = $report->defect_type ?
-                              $report->defect_type->get_extra_metadata('defect_code')
-                              : 'SFP2';
-            $csv->combine(
-                "J", # georeferencing record
-                $defect_type, # defect type - SFP2: sweep and fill <1m2, POT2 also seen
-                $report->response_priority ?
-                    $report->response_priority->external_id :
-                    "2", # priority of defect
-                "","", # empty fields
-                $eastings, # eastings
-                $northings, # northings
-                "","","","","" # empty fields
-            );
-            push @body, $csv->string;
-
-            $csv->combine(
-                "M", # bill of quantities record
-                "resolve", # permanent repair
-                "","", # empty fields
-                "/CMC", # /C + activity code
-                "", "" # empty fields
-            );
-            push @body, $csv->string;
-        }
-
-        # end this group of defects with a P record
-        $csv->combine(
-            "P", # end of area/sequence
-            0, # always 0
-            999999, # charging code, always 999999 in OCC
-        );
-        push @body, $csv->string;
-        $p_count++;
-    }
-
-    # end the RDI file with an X record
-    my $record_count = $i;
-    $csv->combine(
-        "X", # end of inspection record
-        $p_count,
-        $p_count,
-        $record_count, # number of I records
-        $record_count, # number of J records
-        0, 0, 0, # always zero
-        $record_count, # number of M records
-        0, # always zero
-        $p_count,
-        0, 0, 0 # error counts, always zero
-    );
-    push @body, $csv->string;
-
-    my $start = $start_date->strftime("%Y%m%d");
-    my $end = $end_date->strftime("%Y%m%d");
-    my $filename = sprintf("exor_defects-%s-%s.rdi", $start, $end);
-    if ( $user ) {
-        my $initials = $user->get_extra_metadata("initials") || "";
-        $filename = sprintf("exor_defects-%s-%s-%s.rdi", $start, $end, $initials);
-    }
-    $c->res->content_type('text/csv; charset=utf-8');
-    $c->res->header('content-disposition' => "attachment; filename=$filename");
-    # The RDI format is very weird CSV - each line must be wrapped in
-    # double quotes.
-    $c->res->body( join "", map { "\"$_\"\r\n" } @body );
+    };
 }
 
 1;
diff --git a/perllib/FixMyStreet/Cobrand/Default.pm b/perllib/FixMyStreet/Cobrand/Default.pm
index ad84bd496..195978845 100644
--- a/perllib/FixMyStreet/Cobrand/Default.pm
+++ b/perllib/FixMyStreet/Cobrand/Default.pm
@@ -4,6 +4,7 @@ use base 'FixMyStreet::Cobrand::Base';
 use strict;
 use warnings;
 use FixMyStreet;
+use FixMyStreet::DB;
 use FixMyStreet::Geocode::Bing;
 use DateTime;
 use Encode;
@@ -71,7 +72,7 @@ a cobrand that only wants some of the data.
 
 sub problems {
     my $self = shift;
-    return $self->problems_restriction($self->{c}->model('DB::Problem'));
+    return $self->problems_restriction(FixMyStreet::DB->resultset('Problem'));
 }
 
 =head1 problems_on_map
@@ -83,7 +84,7 @@ restricted to a subset if we're on a cobrand that only wants some of the data.
 
 sub problems_on_map {
     my $self = shift;
-    return $self->problems_on_map_restriction($self->{c}->model('DB::Problem'));
+    return $self->problems_on_map_restriction(FixMyStreet::DB->resultset('Problem'));
 }
 
 =head1 updates
@@ -95,7 +96,7 @@ a cobrand that only wants some of the data.
 
 sub updates {
     my $self = shift;
-    return $self->updates_restriction($self->{c}->model('DB::Comment'));
+    return $self->updates_restriction(FixMyStreet::DB->resultset('Comment'));
 }
 
 =head1 problems_restriction/updates_restriction
@@ -149,7 +150,7 @@ a cobrand that only wants some of the data.
 
 sub users {
     my $self = shift;
-    return $self->users_restriction($self->{c}->model('DB::User'));
+    return $self->users_restriction(FixMyStreet::DB->resultset('User'));
 }
 
 =head1 users_restriction
diff --git a/perllib/FixMyStreet/Cobrand/UK.pm b/perllib/FixMyStreet/Cobrand/UK.pm
index 2d4a4d891..46891f906 100644
--- a/perllib/FixMyStreet/Cobrand/UK.pm
+++ b/perllib/FixMyStreet/Cobrand/UK.pm
@@ -121,7 +121,8 @@ sub find_closest {
 
     my $data = $self->SUPER::find_closest($problem, $as_data);
 
-    my $url = "https://mapit.mysociety.org/nearest/4326/" . $problem->longitude . ',' . $problem->latitude;
+    my $mapit_url = FixMyStreet->config('MAPIT_URL');
+    my $url = $mapit_url . "nearest/4326/" . $problem->longitude . ',' . $problem->latitude;
     my $j = LWP::Simple::get($url);
     if ($j) {
         $j = JSON->new->utf8->allow_nonref->decode($j);
diff --git a/perllib/FixMyStreet/Integrations/ExorRDI.pm b/perllib/FixMyStreet/Integrations/ExorRDI.pm
new file mode 100644
index 000000000..b0e581f84
--- /dev/null
+++ b/perllib/FixMyStreet/Integrations/ExorRDI.pm
@@ -0,0 +1,208 @@
+package FixMyStreet::Integrations::ExorRDI::Error;
+
+use Moo;
+with 'Throwable';
+
+has message => (is => 'ro');
+
+package FixMyStreet::Integrations::ExorRDI;
+
+use DateTime;
+use Moo;
+use Scalar::Util 'blessed';
+use Text::CSV;
+use FixMyStreet::DB;
+use namespace::clean;
+
+has [qw(start_date end_date)] => (
+    is => 'ro',
+    required => 1,
+);
+
+has user => (
+    is => 'ro',
+    coerce => sub {
+        return $_[0] if blessed($_[0]) && $_[0]->isa('FixMyStreet::DB::Result::User');
+        FixMyStreet::DB->resultset('User')->find( { id => $_[0] } );
+    },
+);
+
+sub construct {
+    my $self = shift;
+
+    my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker('oxfordshire')->new;
+    my $dtf = $cobrand->problems->result_source->storage->datetime_parser;
+
+    my %params = (
+        -and => [
+            state => [ 'action scheduled' ],
+            external_id => { '!=' => undef },
+            'admin_log_entries.action' => 'inspected',
+            'admin_log_entries.whenedited' => { '>=', $dtf->format_datetime($self->start_date) },
+            'admin_log_entries.whenedited' => { '<=', $dtf->format_datetime($self->end_date) },
+        ]
+    );
+
+    $params{'admin_log_entries.user_id'} = $self->user->id if $self->user;
+
+    my $problems = $cobrand->problems->search(
+        \%params,
+        {
+            join => 'admin_log_entries',
+            distinct => 1,
+        }
+    );
+    FixMyStreet::Integrations::ExorRDI::Error->throw unless $problems->count;
+
+    # A single RDI file might contain inspections from multiple inspectors, so
+    # we need to group inspections by inspector within G records.
+    my $inspectors = {};
+    my $inspector_initials = {};
+    while ( my $report = $problems->next ) {
+        my $user = $report->inspection_log_entry->user;
+        $inspectors->{$user->id} ||= [];
+        push @{ $inspectors->{$user->id} }, $report;
+        unless ( $inspector_initials->{$user->id} ) {
+            $inspector_initials->{$user->id} = $user->get_extra_metadata('initials');
+        }
+    }
+
+    my $csv = Text::CSV->new({ binary => 1, eol => "" });
+
+    my $p_count = 0;
+    my $link_id = $cobrand->exor_rdi_link_id;
+
+    # RDI first line is always the same
+    $csv->combine("1", "1.8", "1.0.0.0", "ENHN", "");
+    my @body = ($csv->string);
+
+    my $i = 0;
+    foreach my $inspector_id (keys %$inspectors) {
+        my $inspections = $inspectors->{$inspector_id};
+        my $initials = $inspector_initials->{$inspector_id};
+
+        $csv->combine(
+            "G", # start of an area/sequence
+            $link_id, # area/link id, fixed value for our purposes
+            "","", # must be empty
+            $initials || "XX", # inspector initials
+            $self->start_date->strftime("%y%m%d"), # date of inspection yymmdd
+            "0700", # time of inspection hhmm, set to static value for now
+            "D", # inspection variant, should always be D
+            "INS", # inspection type, always INS
+            "N", # Area of the county - north (N) or south (S)
+            "", "", "", "" # empty fields
+        );
+        push @body, $csv->string;
+
+        $csv->combine(
+            "H", # initial inspection type
+            "MC" # minor carriageway (changes depending on activity code)
+        );
+        push @body, $csv->string;
+
+        foreach my $report (@$inspections) {
+            my ($eastings, $northings) = $report->local_coords;
+
+            my $location = "${eastings}E ${northings}N";
+            $location = "[DID NOT USE MAP] $location" unless $report->used_map;
+            my $closest_address = $cobrand->find_closest($report, 1);
+            if (%$closest_address) {
+                $location .= " Nearest road: $closest_address->{road}." if $closest_address->{road};
+                $location .= " Nearest postcode: $closest_address->{postcode}{postcode}." if $closest_address->{postcode};
+            }
+
+            my $description = sprintf("%s %s", $report->external_id || "", $report->get_extra_metadata('detailed_information') || "");
+            my $activity_code = $report->defect_type ?
+                $report->defect_type->get_extra_metadata('activity_code')
+                : 'MC';
+            my $traffic_information = $report->get_extra_metadata('traffic_information') ?
+                'TM ' . $report->get_extra_metadata('traffic_information')
+                : 'TM none';
+
+            $csv->combine(
+                "I", # beginning of defect record
+                $activity_code, # activity code - minor carriageway, also FC (footway)
+                "", # empty field, can also be A (seen on MC) or B (seen on FC)
+                sprintf("%03d", ++$i), # randomised sequence number
+                $location, # defect location field, which we don't capture from inspectors
+                $report->inspection_log_entry->whenedited->strftime("%H%M"), # defect time raised
+                "","","","","","","", # empty fields
+                $traffic_information,
+                $description, # defect description
+            );
+            push @body, $csv->string;
+
+            my $defect_type = $report->defect_type ?
+                              $report->defect_type->get_extra_metadata('defect_code')
+                              : 'SFP2';
+            $csv->combine(
+                "J", # georeferencing record
+                $defect_type, # defect type - SFP2: sweep and fill <1m2, POT2 also seen
+                $report->response_priority ?
+                    $report->response_priority->external_id :
+                    "2", # priority of defect
+                "","", # empty fields
+                $eastings, # eastings
+                $northings, # northings
+                "","","","","" # empty fields
+            );
+            push @body, $csv->string;
+
+            $csv->combine(
+                "M", # bill of quantities record
+                "resolve", # permanent repair
+                "","", # empty fields
+                "/CMC", # /C + activity code
+                "", "" # empty fields
+            );
+            push @body, $csv->string;
+        }
+
+        # end this group of defects with a P record
+        $csv->combine(
+            "P", # end of area/sequence
+            0, # always 0
+            999999, # charging code, always 999999 in OCC
+        );
+        push @body, $csv->string;
+        $p_count++;
+    }
+
+    # end the RDI file with an X record
+    my $record_count = $i;
+    $csv->combine(
+        "X", # end of inspection record
+        $p_count,
+        $p_count,
+        $record_count, # number of I records
+        $record_count, # number of J records
+        0, 0, 0, # always zero
+        $record_count, # number of M records
+        0, # always zero
+        $p_count,
+        0, 0, 0 # error counts, always zero
+    );
+    push @body, $csv->string;
+
+    # The RDI format is very weird CSV - each line must be wrapped in
+    # double quotes.
+    return join "", map { "\"$_\"\r\n" } @body;
+}
+
+has filename => (
+    is => 'lazy',
+    default => sub {
+        my $self = shift;
+        my $start = $self->start_date->strftime("%Y%m%d");
+        my $end = $self->end_date->strftime("%Y%m%d");
+        my $filename = sprintf("exor_defects-%s-%s.rdi", $start, $end);
+        if ( $self->user ) {
+            my $initials = $self->user->get_extra_metadata("initials") || "";
+            $filename = sprintf("exor_defects-%s-%s-%s.rdi", $start, $end, $initials);
+        }
+        return $filename;
+    },
+);
+
+1;