diff options
Diffstat (limited to 'perllib/FixMyStreet')
| -rw-r--r-- | perllib/FixMyStreet/App/Controller/Dashboard.pm | 177 | ||||
| -rw-r--r-- | perllib/FixMyStreet/App/Controller/Reports.pm | 133 | ||||
| -rw-r--r-- | perllib/FixMyStreet/Cobrand/FixMyStreet.pm | 5 | ||||
| -rw-r--r-- | perllib/FixMyStreet/DB/Result/Body.pm | 29 | ||||
| -rwxr-xr-x | perllib/FixMyStreet/Script/UpdateAllReports.pm | 27 |
5 files changed, 265 insertions, 106 deletions
diff --git a/perllib/FixMyStreet/App/Controller/Dashboard.pm b/perllib/FixMyStreet/App/Controller/Dashboard.pm index 5fe473c54..90f3866ec 100644 --- a/perllib/FixMyStreet/App/Controller/Dashboard.pm +++ b/perllib/FixMyStreet/App/Controller/Dashboard.pm @@ -5,6 +5,7 @@ use namespace::autoclean; use DateTime; use JSON::MaybeXS; use Path::Tiny; +use Text::CSV; use Time::Piece; BEGIN { extends 'Catalyst::Controller'; } @@ -112,7 +113,7 @@ sub index : Path : Args(0) { $c->forward('construct_rs_filter'); if ( $c->get_param('export') ) { - $self->export_as_csv($c); + $c->forward('export_as_csv'); } else { $self->generate_data($c); } @@ -175,15 +176,15 @@ sub generate_data { $state_map->{$_} = 'closed' foreach FixMyStreet::DB::Result::Problem->closed_states; $state_map->{$_} = 'fixed' foreach FixMyStreet::DB::Result::Problem->fixed_states; - $self->generate_grouped_data($c); + $c->forward('generate_grouped_data'); $self->generate_summary_figures($c); } -sub generate_grouped_data { +sub generate_grouped_data : Private { my ($self, $c) = @_; my $state_map = $c->stash->{state_map}; - my $group_by = $c->get_param('group_by') || ''; + my $group_by = $c->get_param('group_by') || $c->stash->{group_by_default} || ''; my (%grouped, @groups, %totals); if ($group_by eq 'category') { %grouped = map { $_->category => {} } @{$c->stash->{contacts}}; @@ -197,6 +198,8 @@ sub generate_grouped_data { ); } elsif ($group_by eq 'device+site') { @groups = qw/cobrand service/; + } elsif ($group_by eq 'device') { + @groups = qw/service/; } else { $group_by = 'category+state'; @groups = qw/category state/; @@ -285,28 +288,22 @@ sub generate_summary_figures { } } -sub export_as_csv { +sub generate_body_response_time : Private { + my ( $self, $c ) = @_; + + my $avg = $c->stash->{body}->calculate_average; + $c->stash->{body_average} = $avg ? int($avg / 60 / 60 / 24 + 0.5) : 0; +} + +sub export_as_csv : Private { my ($self, $c) = @_; - require Text::CSV; - my $problems = $c->stash->{problems_rs}->search( - {}, { prefetch => 'comments', order_by => 'me.confirmed' }); - - my $filename = do { - my %where = ( - category => $c->stash->{category}, - state => $c->stash->{q_state}, - ward => $c->stash->{ward}, - ); - $where{body} = $c->stash->{body}->id if $c->stash->{body}; - join '-', - $c->req->uri->host, - map { - my $value = $where{$_}; - (defined $value and length $value) ? ($_, $value) : () - } sort keys %where }; - my $csv = Text::CSV->new({ binary => 1, eol => "\n" }); - $csv->combine( + my $csv = $c->stash->{csv} = { + problems => $c->stash->{problems_rs}->search_rs({}, { + prefetch => 'comments', + order_by => 'me.confirmed' + }), + headers => [ 'Report ID', 'Title', 'Detail', @@ -324,68 +321,116 @@ sub export_as_csv { 'Easting', 'Northing', 'Report URL', + ], + columns => [ + 'id', + 'title', + 'detail', + 'user_name_display', + 'category', + 'created', + 'confirmed', + 'acknowledged', + 'fixed', + 'closed', + 'state', + 'latitude', 'longitude', + 'postcode', + 'wards', + 'local_coords_x', + 'local_coords_y', + 'url', + ], + filename => do { + my %where = ( + category => $c->stash->{category}, + state => $c->stash->{q_state}, + ward => $c->stash->{ward}, ); + $where{body} = $c->stash->{body}->id if $c->stash->{body}; + join '-', + $c->req->uri->host, + map { + my $value = $where{$_}; + (defined $value and length $value) ? ($_, $value) : () + } sort keys %where + }, + }; + $c->forward('generate_csv'); +} + +=head2 generate_csv + +Generates a CSV output, given a 'csv' stash hashref containing: +* filename: filename to be used in output +* problems: a resultset of the rows to output +* headers: an arrayref of the header row strings +* columns: an arrayref of the columns (looked up in the row's as_hashref, plus +the following: user_name_display, acknowledged, fixed, closed, wards, +local_coords_x, local_coords_y, url). + +=cut + +sub generate_csv : Private { + my ($self, $c) = @_; + + my $csv = Text::CSV->new({ binary => 1, eol => "\n" }); + $csv->combine(@{$c->stash->{csv}->{headers}}); my @body = ($csv->string); my $fixed_states = FixMyStreet::DB::Result::Problem->fixed_states; my $closed_states = FixMyStreet::DB::Result::Problem->closed_states; + my $wards = 0; + my $comments = 0; + foreach (@{$c->stash->{csv}->{columns}}) { + $wards = 1 if $_ eq 'wards'; + $comments = 1 if $_ eq 'acknowledged'; + } + + my $problems = $c->stash->{csv}->{problems}; while ( my $report = $problems->next ) { - my $external_body; - my $body_name = ""; - if ( $external_body = $report->body($c) ) { - # seems to be a zurich specific thing - $body_name = $external_body->name if ref $external_body; - } my $hashref = $report->as_hashref($c); - $hashref->{user_name_display} = $report->anonymous? - '(anonymous)' : $report->user->name; - - for my $comment ($report->comments) { - my $problem_state = $comment->problem_state or next; - next unless $comment->state eq 'confirmed'; - next if $problem_state eq 'confirmed'; - $hashref->{acknowledged} //= $comment->confirmed; - $hashref->{fixed} //= $fixed_states->{ $problem_state } || $comment->mark_fixed ? - $comment->confirmed : undef; - if ($closed_states->{ $problem_state }) { - $hashref->{closed} = $comment->confirmed; - last; + $hashref->{user_name_display} = $report->anonymous + ? '(anonymous)' : $report->user->name; + + if ($comments) { + for my $comment ($report->comments) { + my $problem_state = $comment->problem_state or next; + next unless $comment->state eq 'confirmed'; + next if $problem_state eq 'confirmed'; + $hashref->{acknowledged} //= $comment->confirmed; + $hashref->{fixed} //= $fixed_states->{ $problem_state } || $comment->mark_fixed ? + $comment->confirmed : undef; + if ($closed_states->{ $problem_state }) { + $hashref->{closed} = $comment->confirmed; + last; + } } } - my $wards = join ', ', - map { $c->stash->{children}->{$_}->{name} } - grep {$c->stash->{children}->{$_} } - split ',', $hashref->{areas}; + if ($wards) { + $hashref->{wards} = join ', ', + map { $c->stash->{children}->{$_}->{name} } + grep {$c->stash->{children}->{$_} } + split ',', $hashref->{areas}; + } - my @local_coords = $report->local_coords; + ($hashref->{local_coords_x}, $hashref->{local_coords_y}) = + $report->local_coords; + $hashref->{url} = join '', $c->cobrand->base_url_for_report($report), $report->url; $csv->combine( @{$hashref}{ - 'id', - 'title', - 'detail', - 'user_name_display', - 'category', - 'created', - 'confirmed', - 'acknowledged', - 'fixed', - 'closed', - 'state', - 'latitude', 'longitude', - 'postcode', - }, - $wards, - $local_coords[0], - $local_coords[1], - (join '', $c->cobrand->base_url_for_report($report), $report->url), + @{$c->stash->{csv}->{columns}} + }, ); push @body, $csv->string; } + + my $filename = $c->stash->{csv}->{filename}; $c->res->content_type('text/csv; charset=utf-8'); $c->res->header('content-disposition' => "attachment; filename=${filename}.csv"); $c->res->body( join "", @body ); diff --git a/perllib/FixMyStreet/App/Controller/Reports.pm b/perllib/FixMyStreet/App/Controller/Reports.pm index b6281f0ca..ec7a192b3 100644 --- a/perllib/FixMyStreet/App/Controller/Reports.pm +++ b/perllib/FixMyStreet/App/Controller/Reports.pm @@ -69,19 +69,8 @@ sub index : Path : Args(0) { } } - my $dashboard = eval { - my $data = FixMyStreet->config('TEST_DASHBOARD_DATA'); - # uncoverable branch true - unless ($data) { - my $fn = '../data/all-reports-dashboard'; - if ($c->stash->{body}) { - $fn .= '-' . $c->stash->{body}->id; - } - $data = decode_json(path(FixMyStreet->path_to($fn . '.json'))->slurp_utf8); - } - $c->stash($data); - return 1; - }; + my $dashboard = $c->forward('load_dashboard_data'); + my $table = !$c->stash->{body} && eval { my $data = path(FixMyStreet->path_to('../data/all-reports.json'))->slurp_utf8; $c->stash(decode_json($data)); @@ -425,6 +414,105 @@ sub ward_check : Private { $c->detach( 'redirect_body' ); } +=head2 summary + +This is the summary page used on fixmystreet.com + +=cut + +sub summary : Private { + my ($self, $c) = @_; + my $dashboard = $c->forward('load_dashboard_data'); + + eval { + my $data = path(FixMyStreet->path_to('../data/all-reports-dashboard.json'))->slurp_utf8; + $data = decode_json($data); + $c->stash( + top_five_bodies => $data->{top_five_bodies}, + average => $data->{average}, + ); + }; + + my $dtf = $c->model('DB')->storage->datetime_parser; + my $period = $c->stash->{period} = $c->get_param('period') || ''; + my $start_date; + if ($period eq 'ever') { + $start_date = DateTime->new(year => 2007); + } elsif ($period eq 'year') { + $start_date = DateTime->now->subtract(years => 1); + } elsif ($period eq '3months') { + $start_date = DateTime->now->subtract(months => 3); + } elsif ($period eq 'week') { + $start_date = DateTime->now->subtract(weeks => 1); + } else { + $c->stash->{period} = 'month'; + $start_date = DateTime->now->subtract(months => 1); + } + + # required to stop errors in generate_grouped_data + $c->stash->{q_state} = ''; + $c->stash->{ward} = $c->get_param('ward'); + $c->stash->{start_date} = $dtf->format_date($start_date); + $c->stash->{end_date} = $c->get_param('end_date'); + + $c->stash->{group_by_default} = 'category'; + + my $area_id = $c->stash->{body}->body_areas->first->area_id; + my $children = mySociety::MaPit::call('area/children', $area_id, + type => $c->cobrand->area_types_children, + ); + $c->stash->{children} = $children; + + $c->forward('/admin/fetch_contacts'); + $c->stash->{contacts} = [ $c->stash->{contacts}->all ]; + + $c->forward('/dashboard/construct_rs_filter'); + + if ( $c->get_param('csv') ) { + $c->detach('export_summary_csv'); + } + + $c->forward('/dashboard/generate_grouped_data'); + $c->forward('/dashboard/generate_body_response_time'); + + $c->stash->{template} = 'reports/summary.html'; +} + +sub export_summary_csv : Private { + my ( $self, $c ) = @_; + + $c->stash->{csv} = { + problems => $c->stash->{problems_rs}->search_rs({}, { + rows => 100, + order_by => { '-desc' => 'me.confirmed' }, + }), + headers => [ + 'Report ID', + 'Title', + 'Category', + 'Created', + 'Confirmed', + 'Status', + 'Latitude', 'Longitude', + 'Query', + 'Report URL', + ], + columns => [ + 'id', + 'title', + 'category', + 'created_pp', + 'confirmed_pp', + 'state', + 'latitude', 'longitude', + 'postcode', + 'url', + ], + filename => 'fixmystreet-data.csv', + }; + $c->forward('/dashboard/generate_csv'); +} + =head2 check_canonical_url Given an already found (case-insensitively) body, check what URL @@ -441,6 +529,25 @@ sub check_canonical_url : Private { $c->detach( 'redirect_body' ) unless $body_short eq $url_short; } +sub load_dashboard_data : Private { + my ($self, $c) = @_; + my $dashboard = eval { + my $data = FixMyStreet->config('TEST_DASHBOARD_DATA'); + # uncoverable branch true + unless ($data) { + my $fn = '../data/all-reports-dashboard'; + if ($c->stash->{body}) { + $fn .= '-' . $c->stash->{body}->id; + } + $data = decode_json(path(FixMyStreet->path_to($fn . '.json'))->slurp_utf8); + } + $c->stash($data); + return 1; + }; + + return $dashboard; +} + sub load_and_group_problems : Private { my ( $self, $c ) = @_; diff --git a/perllib/FixMyStreet/Cobrand/FixMyStreet.pm b/perllib/FixMyStreet/Cobrand/FixMyStreet.pm index 2153ca33b..591234877 100644 --- a/perllib/FixMyStreet/Cobrand/FixMyStreet.pm +++ b/perllib/FixMyStreet/Cobrand/FixMyStreet.pm @@ -81,14 +81,13 @@ sub council_dashboard_hook { } $c->forward('/admin/fetch_contacts'); - $c->stash->{display_contacts} = 1; - return if $c->user->is_superuser; + $c->detach('/reports/summary') if $c->user->is_superuser; my $body = $c->user->from_body || _user_to_body($c); if ($body) { # Matching URL and user's email body - return if $body->id eq $c->stash->{body}->id; + $c->detach('/reports/summary') if $body->id eq $c->stash->{body}->id; # Matched /a/ body, redirect to its summary page $c->stash->{body} = $body; diff --git a/perllib/FixMyStreet/DB/Result/Body.pm b/perllib/FixMyStreet/DB/Result/Body.pm index da5c38168..e5cd2b907 100644 --- a/perllib/FixMyStreet/DB/Result/Body.pm +++ b/perllib/FixMyStreet/DB/Result/Body.pm @@ -183,4 +183,33 @@ sub get_cobrand_handler { return FixMyStreet::Cobrand->body_handler($self->areas); } +sub calculate_average { + my ($self) = @_; + + 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)"; + my $subquery = FixMyStreet::DB->resultset('Comment')->to_body($self)->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'); + return $avg; +} + 1; diff --git a/perllib/FixMyStreet/Script/UpdateAllReports.pm b/perllib/FixMyStreet/Script/UpdateAllReports.pm index f4f444d5b..d6f3eb64b 100755 --- a/perllib/FixMyStreet/Script/UpdateAllReports.pm +++ b/perllib/FixMyStreet/Script/UpdateAllReports.pm @@ -199,7 +199,7 @@ sub generate_dashboard { if ($body) { calculate_top_five_wards(\%data, $rs, $body); } else { - calculate_top_five_bodies(\%data, $rs_c); + calculate_top_five_bodies(\%data); } my $week_ago = $dtf->format_datetime(DateTime->now->subtract(days => 7)); @@ -247,34 +247,13 @@ sub stuff_by_day_or_year { } sub calculate_top_five_bodies { - my ($data, $rs_c) = @_; + my ($data) = @_; 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'); + my $avg = $body->calculate_average; push @top_five_bodies, { name => $body->name, days => int($avg / 60 / 60 / 24 + 0.5) } if defined $avg; } |