diff options
Diffstat (limited to 'perllib')
| -rw-r--r-- | perllib/FixMyStreet/App/Controller/Reports.pm | 29 | ||||
| -rw-r--r-- | perllib/FixMyStreet/Integrations/ExorRDI.pm | 96 | ||||
| -rwxr-xr-x | perllib/FixMyStreet/Script/UpdateAllReports.pm | 163 |
3 files changed, 238 insertions, 50 deletions
diff --git a/perllib/FixMyStreet/App/Controller/Reports.pm b/perllib/FixMyStreet/App/Controller/Reports.pm index ed851f71f..024a23872 100644 --- a/perllib/FixMyStreet/App/Controller/Reports.pm +++ b/perllib/FixMyStreet/App/Controller/Reports.pm @@ -54,6 +54,15 @@ sub index : Path : Args(0) { $c->detach( 'redirect_body' ); } + if (my $body = $c->get_param('body')) { + $body = $c->model('DB::Body')->find( { id => $body } ); + if ($body) { + $body = $c->cobrand->short_name($body); + $c->res->redirect("/reports/$body"); + $c->detach; + } + } + # Fetch all bodies my @bodies = $c->model('DB::Body')->search({ deleted => 0, @@ -67,15 +76,21 @@ sub index : Path : Args(0) { $c->stash->{bodies} = \@bodies; $c->stash->{any_empty_bodies} = any { $_->get_column('area_count') == 0 } @bodies; - eval { + my $dashboard = eval { + my $data = File::Slurp::read_file( + FixMyStreet->path_to( '../data/all-reports-dashboard.json' )->stringify + ); + $c->stash(decode_json($data)); + return 1; + }; + my $table = eval { my $data = File::Slurp::read_file( FixMyStreet->path_to( '../data/all-reports.json' )->stringify ); - my $j = decode_json($data); - $c->stash->{fixed} = $j->{fixed}; - $c->stash->{open} = $j->{open}; + $c->stash(decode_json($data)); + return 1; }; - if ($@) { + if (!$dashboard && !$table) { my $message = _("There was a problem showing the All Reports page. Please try again later."); if ($c->config->{STAGING_SITE}) { $message .= '</p><p>Perhaps the bin/update-all-reports script needs running. Use: bin/update-all-reports</p><p>' @@ -88,7 +103,7 @@ sub index : Path : Args(0) { $c->response->header('Cache-Control' => 'max-age=3600'); } -=head2 index +=head2 body Show the summary page for a particular body. @@ -99,7 +114,7 @@ sub body : Path : Args(1) { $c->detach( 'ward', [ $body ] ); } -=head2 index +=head2 ward Show the summary page for a particular ward. diff --git a/perllib/FixMyStreet/Integrations/ExorRDI.pm b/perllib/FixMyStreet/Integrations/ExorRDI.pm index b0e581f84..dab307fff 100644 --- a/perllib/FixMyStreet/Integrations/ExorRDI.pm +++ b/perllib/FixMyStreet/Integrations/ExorRDI.pm @@ -5,12 +5,21 @@ with 'Throwable'; has message => (is => 'ro'); +package FixMyStreet::Integrations::ExorRDI::CSV; + +use parent 'Text::CSV'; + +sub add_row { + my ($self, $data, @data) = @_; + $self->combine(@data); + push @$data, $self->string; +} + package FixMyStreet::Integrations::ExorRDI; use DateTime; use Moo; use Scalar::Util 'blessed'; -use Text::CSV; use FixMyStreet::DB; use namespace::clean; @@ -23,7 +32,8 @@ has user => ( is => 'ro', coerce => sub { return $_[0] if blessed($_[0]) && $_[0]->isa('FixMyStreet::DB::Result::User'); - FixMyStreet::DB->resultset('User')->find( { id => $_[0] } ); + FixMyStreet::DB->resultset('User')->find( { id => $_[0] } ) + if $_[0]; }, ); @@ -67,40 +77,21 @@ sub construct { } } - my $csv = Text::CSV->new({ binary => 1, eol => "" }); + my $csv = FixMyStreet::Integrations::ExorRDI::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 $body = []; + $csv->add_row($body, "1", "1.8", "1.0.0.0", "ENHN", ""); 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; - + my %body_by_activity_code; foreach my $report (@$inspections) { my ($eastings, $northings) = $report->local_coords; @@ -119,8 +110,9 @@ sub construct { my $traffic_information = $report->get_extra_metadata('traffic_information') ? 'TM ' . $report->get_extra_metadata('traffic_information') : 'TM none'; + $body_by_activity_code{$activity_code} ||= []; - $csv->combine( + $csv->add_row($body_by_activity_code{$activity_code}, "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) @@ -131,12 +123,11 @@ sub construct { $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( + $csv->add_row($body_by_activity_code{$activity_code}, "J", # georeferencing record $defect_type, # defect type - SFP2: sweep and fill <1m2, POT2 also seen $report->response_priority ? @@ -147,31 +138,51 @@ sub construct { $northings, # northings "","","","","" # empty fields ); - push @body, $csv->string; - $csv->combine( + $csv->add_row($body_by_activity_code{$activity_code}, "M", # bill of quantities record "resolve", # permanent repair "","", # empty fields - "/CMC", # /C + activity code + "/C$activity_code", # /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++; + foreach my $activity_code (sort keys %body_by_activity_code) { + $csv->add_row($body, + "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 + ); + + $csv->add_row($body, + "H", # initial inspection type + $activity_code # e.g. MC = minor carriageway + ); + + # List of I/J/M entries from above + push @$body, @{$body_by_activity_code{$activity_code}}; + + # end this group of defects with a P record + $csv->add_row($body, + "P", # end of area/sequence + 0, # always 0 + 999999, # charging code, always 999999 in OCC + ); + $p_count++; + } } # end the RDI file with an X record my $record_count = $i; - $csv->combine( + $csv->add_row($body, "X", # end of inspection record $p_count, $p_count, @@ -183,11 +194,10 @@ sub construct { $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; + return join "", map { "\"$_\"\r\n" } @$body; } has filename => ( diff --git a/perllib/FixMyStreet/Script/UpdateAllReports.pm b/perllib/FixMyStreet/Script/UpdateAllReports.pm index 7b5c34290..107143aa2 100755 --- a/perllib/FixMyStreet/Script/UpdateAllReports.pm +++ b/perllib/FixMyStreet/Script/UpdateAllReports.pm @@ -10,6 +10,7 @@ use File::Path (); use File::Slurp; use JSON::MaybeXS; use List::MoreUtils qw(zip); +use List::Util qw(sum); my $fourweeks = 4*7*24*60*60; @@ -77,4 +78,166 @@ sub generate { File::Slurp::write_file( FixMyStreet->path_to( '../data/all-reports.json' )->stringify, \$body ); } +sub generate_dashboard { + my %data; + + my $now = DateTime->now; + my $min_confirmed = FixMyStreet::DB->resultset('Problem')->search({ + state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + }, { + select => [ { min => 'confirmed' } ], + as => [ 'confirmed' ], + })->first->confirmed; + + my ($group_by, @problem_periods); + if (DateTime::Duration->compare($now - $min_confirmed, DateTime::Duration->new(months => 1)) < 0) { + $group_by = 'day'; + while ($min_confirmed < $now) { + push @problem_periods, $min_confirmed->day; + $min_confirmed->add(days => 1); + } + } elsif (DateTime::Duration->compare($now - $min_confirmed, DateTime::Duration->new(years => 1)) < 0) { + $group_by = 'month'; + while ($min_confirmed < $now) { + push @problem_periods, $min_confirmed->month_abbr; + $min_confirmed->add(months => 1); + } + } else { + $group_by = 'year'; + @problem_periods = ($min_confirmed->year..$now->year); + } + + my %problems_reported_by_period = stuff_by_day_or_year( + $group_by, 'Problem', + state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + ); + my %problems_fixed_by_period = stuff_by_day_or_year( + $group_by, 'Problem', + state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], + ); + + my (@problems_reported_by_period, @problems_fixed_by_period); + foreach (@problem_periods) { + push @problems_reported_by_period, ($problems_reported_by_period[-1]||0) + ($problems_reported_by_period{$_}||0); + push @problems_fixed_by_period, ($problems_fixed_by_period[-1]||0) + ($problems_fixed_by_period{$_}||0); + } + $data{problem_periods} = \@problem_periods; + $data{problems_reported_by_period} = \@problems_reported_by_period; + $data{problems_fixed_by_period} = \@problems_fixed_by_period; + + my %last_seven_days = ( + problems => [], + updated => [], + fixed => [], + ); + $data{last_seven_days} = \%last_seven_days; + + %problems_reported_by_period = stuff_by_day_or_year('day', + 'Problem', + state => [ FixMyStreet::DB::Result::Problem->visible_states() ], + confirmed => { '>=', \"current_timestamp-'8 days'::interval" }, + ); + %problems_fixed_by_period = stuff_by_day_or_year('day', + 'Comment', + confirmed => { '>=', \"current_timestamp-'8 days'::interval" }, + problem_state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], + ); + my %problems_updated_by_period = stuff_by_day_or_year('day', + 'Comment', + confirmed => { '>=', \"current_timestamp-'8 days'::interval" }, + ); + + my $date = DateTime->today->subtract(days => 7); + while ($date < DateTime->today) { + push @{$last_seven_days{problems}}, $problems_reported_by_period{$date->day} || 0; + push @{$last_seven_days{fixed}}, $problems_fixed_by_period{$date->day} || 0; + push @{$last_seven_days{updated}}, $problems_updated_by_period{$date->day} || 0; + $date->add(days => 1); + } + $last_seven_days{problems_total} = sum @{$last_seven_days{problems}}; + $last_seven_days{fixed_total} = sum @{$last_seven_days{fixed}}; + $last_seven_days{updated_total} = sum @{$last_seven_days{updated}}; + + my(@top_five_bodies); + $data{top_five_bodies} = \@top_five_bodies; + + my $bodies = FixMyStreet::DB->resultset('Body')->search; + my $substmt = "select min(id) from comment where me.problem_id=comment.problem_id and (problem_state in ('fixed', 'fixed - council', 'fixed - user') or mark_fixed)"; + while (my $body = $bodies->next) { + my $subquery = FixMyStreet::DB->resultset('Comment')->to_body($body)->search({ + -or => [ + problem_state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], + mark_fixed => 1, + ], + 'me.id' => \"= ($substmt)", + 'me.state' => 'confirmed', + }, { + select => [ + { extract => "epoch from me.confirmed-problem.confirmed", -as => 'time' }, + ], + as => [ qw/time/ ], + rows => 100, + order_by => { -desc => 'me.confirmed' }, + join => 'problem' + })->as_subselect_rs; + my $avg = $subquery->search({ + }, { + select => [ { avg => "time" } ], + as => [ qw/avg/ ], + })->first->get_column('avg'); + push @top_five_bodies, { name => $body->name, days => int($avg / 60 / 60 / 24 + 0.5) } + if defined $avg; + } + @top_five_bodies = sort { $a->{days} <=> $b->{days} } @top_five_bodies; + $data{average} = @top_five_bodies + ? int((sum map { $_->{days} } @top_five_bodies) / @top_five_bodies + 0.5) : undef; + + @top_five_bodies = @top_five_bodies[0..4] if @top_five_bodies > 5; + + + my $last_seven_days = FixMyStreet::DB->resultset("Problem")->search({ + confirmed => { '>=', \"current_timestamp-'7 days'::interval" }, + })->count; + my @top_five_categories = FixMyStreet::DB->resultset("Problem")->search({ + confirmed => { '>=', \"current_timestamp-'7 days'::interval" }, + category => { '!=', 'Other' }, + }, { + select => [ 'category', { count => 'id' } ], + as => [ 'category', 'count' ], + group_by => 'category', + rows => 5, + order_by => { -desc => 'count' }, + }); + $data{top_five_categories} = [ map { + { category => $_->category, count => $_->get_column('count') } + } @top_five_categories ]; + foreach (@top_five_categories) { + $last_seven_days -= $_->get_column('count'); + } + $data{other_categories} = $last_seven_days; + + my $body = encode_json( \%data ); + File::Path::mkpath( FixMyStreet->path_to( '../data/' )->stringify ); + File::Slurp::write_file( FixMyStreet->path_to( '../data/all-reports-dashboard.json' )->stringify, \$body ); +} + +sub stuff_by_day_or_year { + my $period = shift; + my $table = shift; + my %params = @_; + my $results = FixMyStreet::DB->resultset($table)->search({ + %params + }, { + select => [ { extract => \"$period from confirmed", -as => $period }, { count => 'id' } ], + as => [ $period, 'count' ], + group_by => [ $period ], + }); + my %out; + while (my $row = $results->next) { + my $p = $row->get_column($period); + $out{$p} = $row->get_column('count'); + } + return %out; +} + 1; |