From 4d44ea5530a7dc25122e5135c19d89b4cebc5f40 Mon Sep 17 00:00:00 2001 From: Dave Arter Date: Wed, 7 Dec 2016 11:39:59 +0000 Subject: [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 --- .../App/Controller/Admin/ExorDefects.pm | 219 +++++++++++++++++++++ 1 file changed, 219 insertions(+) create mode 100644 perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm (limited to 'perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm') 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 -- cgit v1.2.3 From 5414433c85749c2baed87c3e6ba4a60bc8103577 Mon Sep 17 00:00:00 2001 From: Dave Arter Date: Fri, 17 Feb 2017 16:32:50 +0000 Subject: Reports need external_id to be included in RDI download Reports in the generated Exor RDI file need to have an external_id so they can be linked to existing enquiries (PEMs) when the RDI is imported into Exor. There is a small window of opportunity for a report to be created, inspected and downloaded in an RDI before it's been sent to Exor via Open311 and had an external_id assigned. This commit closes that window by excluding reports from the RDI that don't have an external_id. Fixes mysociety/fixmystreetforcouncils#168 --- perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm | 1 + 1 file changed, 1 insertion(+) (limited to 'perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm') diff --git a/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm index 164a4b42d..013d710af 100644 --- a/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm +++ b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm @@ -57,6 +57,7 @@ sub download : Path('download') : Args(0) { 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 }, -- cgit v1.2.3 From 3f21a9742d89c3e4fda47a0be6ec2a17f802c99a Mon Sep 17 00:00:00 2001 From: Dave Arter Date: Mon, 13 Feb 2017 15:13:12 +0000 Subject: Add customisable defect types. Problems can have an associated defect type, that can be assigned during an inspection. Include an admin interface for managing these types, that can also be assigned on a per-category basis, currently available to the Oxfordshire cobrand. (Also include 'TM' in traffic management Exor RDI output.) --- perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm | 16 +++++++++++++--- 1 file changed, 13 insertions(+), 3 deletions(-) (limited to 'perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm') diff --git a/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm index 013d710af..201742c81 100644 --- a/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm +++ b/perllib/FixMyStreet/App/Controller/Admin/ExorDefects.pm @@ -141,22 +141,32 @@ sub download : Path('download') : Args(0) { foreach my $report (@$inspections) { my ($eastings, $northings) = $report->local_coords; 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 - "MC", # activity code - minor carriageway, also FC (footway) + $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 "${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 + $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 - $report->get_extra_metadata('defect_type') || 'SFP2', # defect type - SFP2: sweep and fill <1m2, POT2 also seen + $defect_type, # defect type - SFP2: sweep and fill <1m2, POT2 also seen $report->response_priority ? $report->response_priority->external_id : "2", # priority of defect -- cgit v1.2.3