diff options
| -rwxr-xr-x | bin/update-all-reports | 32 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/Result/Body.pm | 5 | ||||
| -rwxr-xr-x | perllib/FixMyStreet/Script/UpdateAllReports.pm | 143 |
3 files changed, 121 insertions, 59 deletions
diff --git a/bin/update-all-reports b/bin/update-all-reports index 24192d5be..0f9231f87 100755 --- a/bin/update-all-reports +++ b/bin/update-all-reports @@ -16,16 +16,18 @@ BEGIN { require "$d/../setenv.pl"; } +use FixMyStreet::DB; use FixMyStreet::Script::UpdateAllReports; -use File::Path (); -use File::Slurp; +use Path::Tiny; use Getopt::Long::Descriptive; use JSON::MaybeXS; my ($opt, $usage) = describe_options( '%c %o', [ 'table', "Output JSON for old table-style page." ], - [ 'areas', "Include area IDs in output JSON." ], + [ 'body=i', "Restrict results to a particular body (dashboard-style)." ], + [ 'all-bodies', "Generate set of results for all bodies." ], + [ 'areas', "Include area IDs in output JSON (table-style)." ], [ 'help', "print usage message and exit", { shortcircuit => 1 } ], ); print($usage->text), exit if $opt->help; @@ -33,12 +35,26 @@ print($usage->text), exit if $opt->help; my ($data, $filename); if ($opt->table) { $data = FixMyStreet::Script::UpdateAllReports::generate($opt->areas); - $filename = 'all-reports.json'; + output('all-reports', $data); +} elsif ($opt->all_bodies) { + my $bodies = FixMyStreet::DB->resultset("Body")->search({ deleted => 0 }); + while (my $body = $bodies->next) { + my $data = FixMyStreet::Script::UpdateAllReports::generate_dashboard($body->id); + output("all-reports-dashboard-" . $body->id, $data); + } +} elsif (my $body_id = $opt->body) { + my $body = FixMyStreet::DB->resultset("Body")->find({ id => $body_id }); + die "Could not find body $body_id" unless $body; + $data = FixMyStreet::Script::UpdateAllReports::generate_dashboard($body); + output("all-reports-dashboard-$body_id", $data); } else { $data = FixMyStreet::Script::UpdateAllReports::generate_dashboard(); - $filename = 'all-reports-dashboard.json'; + output("all-reports-dashboard", $data); } -my $json = encode_json($data); -File::Path::mkpath(FixMyStreet->path_to('../data/')->stringify); -File::Slurp::write_file(FixMyStreet->path_to("../data/$filename")->stringify, \$json); +sub output { + my ($filename, $data) = @_; + my $json = encode_json($data); + path(FixMyStreet->path_to('../data/'))->mkpath; + path(FixMyStreet->path_to("../data/$filename.json"))->spew_utf8($json); +} diff --git a/perllib/FixMyStreet/DB/Result/Body.pm b/perllib/FixMyStreet/DB/Result/Body.pm index 6481d5cfc..da5c38168 100644 --- a/perllib/FixMyStreet/DB/Result/Body.pm +++ b/perllib/FixMyStreet/DB/Result/Body.pm @@ -156,12 +156,13 @@ sub areas { } sub first_area_children { - my ( $self, $c ) = @_; + my ( $self ) = @_; my $area_id = $self->body_areas->first->area_id; + my $cobrand = $self->result_source->schema->cobrand; my $children = mySociety::MaPit::call('area/children', $area_id, - type => $c->cobrand->area_types_children, + type => $cobrand->area_types_children, ); return $children; diff --git a/perllib/FixMyStreet/Script/UpdateAllReports.pm b/perllib/FixMyStreet/Script/UpdateAllReports.pm index f459b7b12..f4f444d5b 100755 --- a/perllib/FixMyStreet/Script/UpdateAllReports.pm +++ b/perllib/FixMyStreet/Script/UpdateAllReports.pm @@ -4,6 +4,7 @@ use strict; use warnings; use FixMyStreet; +use FixMyStreet::Cobrand; use FixMyStreet::DB; use List::MoreUtils qw(zip); @@ -18,6 +19,11 @@ if ( FixMyStreet->config('BASE_URL') =~ /zurich|zueri/ ) { $age_column = 'created'; } +my $dtf = FixMyStreet::DB->schema->storage->datetime_parser; + +my $cobrand = FixMyStreet::Cobrand->get_class_for_moniker('default')->new; +FixMyStreet::DB->schema->cobrand($cobrand); + sub generate { my $include_areas = shift; @@ -101,10 +107,18 @@ sub loop_period { } sub generate_dashboard { + my $body = shift; + my %data; + my $rs = FixMyStreet::DB->resultset('Problem'); + $rs = $rs->to_body($body) if $body; + + my $rs_c = FixMyStreet::DB->resultset('Comment'); + $rs_c = $rs_c->to_body($body) if $body; + my $end_today = end_period('day'); - my $min_confirmed = FixMyStreet::DB->resultset('Problem')->search({ + my $min_confirmed = $rs->search({ state => [ FixMyStreet::DB::Result::Problem->visible_states() ], }, { select => [ { min => 'confirmed' } ], @@ -128,11 +142,11 @@ sub generate_dashboard { my @problem_periods = loop_period($min_confirmed, $group_by, $extra); my %problems_reported_by_period = stuff_by_day_or_year( - $group_by, 'Problem', + $group_by, $rs, state => [ FixMyStreet::DB::Result::Problem->visible_states() ], ); my %problems_fixed_by_period = stuff_by_day_or_year( - $group_by, 'Problem', + $group_by, $rs, state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], ); @@ -152,24 +166,23 @@ sub generate_dashboard { ); $data{last_seven_days} = \%last_seven_days; - my $dtf = FixMyStreet::DB->schema->storage->datetime_parser; my $eight_ago = $dtf->format_datetime(DateTime->now->subtract(days => 8)); %problems_reported_by_period = stuff_by_day_or_year('day', - 'Problem', + $rs, state => [ FixMyStreet::DB::Result::Problem->visible_states() ], - confirmed => { '>=', $eight_ago }, + 'me.confirmed' => { '>=', $eight_ago }, ); %problems_fixed_by_period = stuff_by_day_or_year('day', - 'Comment', - confirmed => { '>=', $eight_ago }, + $rs_c, + 'me.confirmed' => { '>=', $eight_ago }, -or => [ problem_state => [ FixMyStreet::DB::Result::Problem->fixed_states() ], mark_fixed => 1, ], ); my %problems_updated_by_period = stuff_by_day_or_year('day', - 'Comment', - confirmed => { '>=', $eight_ago }, + $rs_c, + 'me.confirmed' => { '>=', $eight_ago }, ); my $date = DateTime->today->subtract(days => 7); @@ -183,47 +196,17 @@ sub generate_dashboard { $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; + if ($body) { + calculate_top_five_wards(\%data, $rs, $body); + } else { + calculate_top_five_bodies(\%data, $rs_c); } - @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 $week_ago = $dtf->format_datetime(DateTime->now->subtract(days => 7)); - my $last_seven_days = FixMyStreet::DB->resultset("Problem")->search({ + my $last_seven_days = $rs->search({ confirmed => { '>=', $week_ago }, })->count; - my @top_five_categories = FixMyStreet::DB->resultset("Problem")->search({ + my @top_five_categories = $rs->search({ confirmed => { '>=', $week_ago }, category => { '!=', 'Other' }, }, { @@ -246,12 +229,12 @@ sub generate_dashboard { sub stuff_by_day_or_year { my $period = shift; - my $table = shift; + my $rs = shift; my %params = @_; - my $results = FixMyStreet::DB->resultset($table)->search({ + my $results = $rs->search({ %params }, { - select => [ { extract => \"$period from confirmed", -as => $period }, { count => 'id' } ], + select => [ { extract => \"$period from me.confirmed", -as => $period }, { count => 'me.id' } ], as => [ $period, 'count' ], group_by => [ $period ], }); @@ -263,4 +246,66 @@ sub stuff_by_day_or_year { return %out; } +sub calculate_top_five_bodies { + my ($data, $rs_c) = @_; + + my(@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 = $rs_c->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; + $data->{top_five_bodies} = \@top_five_bodies; +} + +sub calculate_top_five_wards { + my ($data, $rs, $body) = @_; + + my $children = $body->first_area_children; + die $children->{error} if $children->{error}; + + my $week_ago = $dtf->format_datetime(DateTime->now->subtract(days => 7)); + my $last_seven_days = $rs->search({ confirmed => { '>=', $week_ago } }); + my $last_seven_days_count = $last_seven_days->count; + $last_seven_days = $last_seven_days->search(undef, { select => 'areas' }); + + while (my $row = $last_seven_days->next) { + $children->{$_}{reports}++ foreach grep { $children->{$_} } split /,/, $row->areas; + } + my @wards = sort { $b->{reports} <=> $a->{reports} } grep { $_->{reports} } values %$children; + @wards = @wards[0..4] if @wards > 5; + + my $sum_five = (sum map { $_->{reports} } @wards) || 0; + $data->{other_wards} = $last_seven_days_count - $sum_five; + $data->{wards} = \@wards; +} + 1; |