[Oxfordshire] Add Exor RDI file download feature

The RDI file format encapsulates information about inspections that have taken
place, and can be uploaded into Exor to create defects in bulk.

This commit adds a page to the Oxfordshire cobrand's admin allowing RDI files
to be generated and downloaded from FMS.

For mysociety/fixmystreetforcouncils#127

6 files changed, 255 insertions, 3 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm
new file mode 100644
index 000000000..164a4b42d
--- /dev/null
+++ b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm
@@ -0,0 +1,219 @@
+package FixMyStreet::App::Controller::Admin::ExorDefects;
+use Moose;
+use namespace::autoclean;
+
+use Text::CSV;
+use DateTime;
+use mySociety::Random qw(random_bytes);
+
+BEGIN { extends 'Catalyst::Controller'; }
+
+
+sub begin : Private {
+    my ( $self, $c ) = @_;
+
+    $c->forward('/admin/begin');
+}
+
+sub index : Path : Args(0) {
+    my ( $self, $c ) = @_;
+
+    foreach (qw(error_message start_date end_date user_id)) {
+        if ( defined $c->flash->{$_} ) {
+            $c->stash->{$_} = $c->flash->{$_};
+        }
+    }
+
+    my @inspectors = $c->cobrand->users->search({
+        'user_body_permissions.permission_type' => 'report_inspect'
+    }, {
+            join => 'user_body_permissions',
+            distinct => 1,
+        }
+    )->all;
+    $c->stash->{inspectors} = \@inspectors;
+
+    # Default start/end date is today
+    my $now = DateTime->now( time_zone => 
+        FixMyStreet->time_zone || FixMyStreet->local_time_zone );
+    $c->stash->{start_date} ||= $now;
+    $c->stash->{end_date} ||= $now;
+
+}
+
+sub download : Path('download') : Args(0) {
+    my ( $self, $c ) = @_;
+
+    if ( !$c->cobrand->can('exor_rdi_link_id') ) {
+        # This only works on the Oxfordshire cobrand currently.
+        $c->detach( '/page_error_404_not_found', [] );
+    }
+
+    my $parser = DateTime::Format::Strptime->new( pattern => '%d/%m/%Y' );
+    my $start_date = $parser-> parse_datetime ( $c->get_param('start_date') );
+    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' ],
+            '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 ) {
+            $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->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 $description = sprintf("%s %s", $report->external_id || "", $report->get_extra_metadata('detailed_information') || "");
+            $csv->combine(
+                "I", # beginning of defect record
+                "MC", # 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
+                "${eastings}E ${northings}N", # defect location field, which we don't capture from inspectors
+                $report->inspection_log_entry->whenedited->strftime("%H%M"), # defect time raised
+                "","","","","","","", # empty fields
+                $report->get_extra_metadata('traffic_information') ? 'TM required' : 'TM none', # further description
+                $description, # defect description
+            );
+            push @body, $csv->string;
+
+            $csv->combine(
+                "J", # georeferencing record
+                $report->get_extra_metadata('defect_type') || 'SFP2', # 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;
\ No newline at end of file
diff --git a/perllib/FixMyStreet/App/Controller/Report.pm b/perllib/FixMyStreet/App/Controller/Report.pm
index 5c34630c4..35d7afd5b 100644
--- a/perllib/FixMyStreet/App/Controller/Report.pm
+++ b/perllib/FixMyStreet/App/Controller/Report.pm
@@ -339,7 +339,7 @@ sub inspect : Private {
         my %update_params = ();
 
         if ($permissions->{report_inspect}) {
-            foreach (qw/detailed_information traffic_information duplicate_of/) {
+            foreach (qw/detailed_information traffic_information duplicate_of defect_type/) {
                 $problem->set_extra_metadata( $_ => $c->get_param($_) );
             }
 
diff --git a/perllib/FixMyStreet/Cobrand/Default.pm b/perllib/FixMyStreet/Cobrand/Default.pm
index b8767ab73..63db8b64a 100644
--- a/perllib/FixMyStreet/Cobrand/Default.pm
+++ b/perllib/FixMyStreet/Cobrand/Default.pm
@@ -665,7 +665,6 @@ sub admin_pages {
         $pages->{responsepriorities} = [ _('Priorities'), 4 ];
         $pages->{responsepriority_edit} = [ undef, undef ];
     };
-
     if ( $user->has_body_permission_to('user_edit') ) {
         $pages->{users} = [ _('Users'), 6 ];
         $pages->{user_edit} = [ undef, undef ];
diff --git a/perllib/FixMyStreet/Cobrand/Oxfordshire.pm b/perllib/FixMyStreet/Cobrand/Oxfordshire.pm
index cf9d6a9a4..7fa548406 100644
--- a/perllib/FixMyStreet/Cobrand/Oxfordshire.pm
+++ b/perllib/FixMyStreet/Cobrand/Oxfordshire.pm
@@ -129,6 +129,31 @@ sub traffic_management_options {
     ];
 }
 
+sub admin_pages {
+    my $self = shift;
+
+    my $user = $self->{c}->user;
+
+    my $pages = $self->next::method();
+
+    # Oxfordshire have a custom admin page for downloading reports in an Exor-
+    # friendly format which anyone with report_instruct permission can use.
+    if ( $user->is_superuser || $user->has_body_permission_to('report_instruct') ) {
+        $pages->{exordefects} = [ _('Download Exor RDI'), 10 ];
+    }
+
+    return $pages;
+}
+
+sub defect_types {
+    {
+        SFP2 => "SFP2: sweep and fill <1m2",
+        POT2 => "POT2",
+    };
+}
+
+sub exor_rdi_link_id { 1989169 }
+sub exor_rdi_link_length { 50 }
 
 sub reputation_increment_states {
     return {
diff --git a/perllib/FixMyStreet/Cobrand/UKCouncils.pm b/perllib/FixMyStreet/Cobrand/UKCouncils.pm
index 4e900e653..e0b6b5298 100644
--- a/perllib/FixMyStreet/Cobrand/UKCouncils.pm
+++ b/perllib/FixMyStreet/Cobrand/UKCouncils.pm
@@ -76,7 +76,7 @@ sub users_restriction {
 
     my $or_query = [
         from_body => $self->council_id,
-        id => [ { -in => $problem_user_ids }, { -in => $update_user_ids } ],
+        'me.id' => [ { -in => $problem_user_ids }, { -in => $update_user_ids } ],
     ];
     if ($self->can('admin_user_domain')) {
         my $domain = $self->admin_user_domain;
diff --git a/perllib/FixMyStreet/DB/Result/Problem.pm b/perllib/FixMyStreet/DB/Result/Problem.pm
index f469c4275..d78eda78f 100644
--- a/perllib/FixMyStreet/DB/Result/Problem.pm
+++ b/perllib/FixMyStreet/DB/Result/Problem.pm
@@ -1100,4 +1100,13 @@ has traffic_management_options => (
     },
 );
 
+has inspection_log_entry => (
+    is => 'ro',
+    lazy => 1,
+    default => sub {
+        my $self = shift;
+        return $self->admin_log_entries->search({ action => 'inspected' }, { order_by => { -desc => 'whenedited' } })->first;
+    },
+);
+
 1;