diff options
Diffstat (limited to 'perllib/FixMyStreet/App/Controller')
| -rw-r--r-- | perllib/FixMyStreet/App/Controller/Admin.pm | 153 | ||||
| -rw-r--r-- | perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm | 225 | ||||
| -rw-r--r-- | perllib/FixMyStreet/App/Controller/Admin/Stats.pm | 75 | ||||
| -rw-r--r-- | perllib/FixMyStreet/App/Controller/Dashboard.pm | 381 |
4 files changed, 271 insertions, 563 deletions
diff --git a/perllib/FixMyStreet/App/Controller/Admin.pm b/perllib/FixMyStreet/App/Controller/Admin.pm index 27aeb9e5b..b485ea2dc 100644 --- a/perllib/FixMyStreet/App/Controller/Admin.pm +++ b/perllib/FixMyStreet/App/Controller/Admin.pm @@ -73,7 +73,7 @@ sub index : Path : Args(0) { return $c->cobrand->admin(); } - $c->forward('stats_by_state'); + $c->forward('/admin/stats/state'); my @unsent = $c->cobrand->problems->search( { state => [ FixMyStreet::DB::Result::Problem::open_states() ], @@ -183,39 +183,6 @@ sub timeline : Path( 'timeline' ) : Args(0) { return 1; } -sub questionnaire : Path('stats/questionnaire') : Args(0) { - my ( $self, $c ) = @_; - - my $questionnaires = $c->model('DB::Questionnaire')->search( - { whenanswered => { '!=', undef } }, - { group_by => [ 'ever_reported' ], - select => [ 'ever_reported', { count => 'me.id' } ], - as => [ qw/reported questionnaire_count/ ] } - ); - - my %questionnaire_counts = map { - ( defined $_->get_column( 'reported' ) ? $_->get_column( 'reported' ) : -1 ) - => $_->get_column( 'questionnaire_count' ) - } $questionnaires->all; - $questionnaire_counts{1} ||= 0; - $questionnaire_counts{0} ||= 0; - $questionnaire_counts{total} = $questionnaire_counts{0} + $questionnaire_counts{1}; - $c->stash->{questionnaires} = \%questionnaire_counts; - - $c->stash->{state_changes_count} = $c->model('DB::Questionnaire')->search( - { whenanswered => \'is not null' } - )->count; - $c->stash->{state_changes} = $c->model('DB::Questionnaire')->search( - { whenanswered => \'is not null' }, - { - group_by => [ 'old_state', 'new_state' ], - columns => [ 'old_state', 'new_state', { c => { count => 'id' } } ], - }, - ); - - return 1; -} - sub bodies : Path('bodies') : Args(0) { my ( $self, $c ) = @_; @@ -504,7 +471,7 @@ sub fetch_contacts : Private { my $contacts = $c->stash->{body}->contacts->search(undef, { order_by => [ 'category' ] } ); $c->stash->{contacts} = $contacts; - $c->stash->{live_contacts} = $contacts->search({ state => { '!=' => 'deleted' } }); + $c->stash->{live_contacts} = $contacts->not_deleted; $c->stash->{any_not_confirmed} = $contacts->search({ state => 'unconfirmed' })->count; if ( $c->get_param('text') && $c->get_param('text') eq '1' ) { @@ -1660,122 +1627,6 @@ sub flagged : Path('flagged') : Args(0) { return 1; } -sub stats_by_state : Path('stats/state') : Args(0) { - my ( $self, $c ) = @_; - - my $problems = $c->cobrand->problems->summary_count; - - my %prob_counts = - map { $_->state => $_->get_column('state_count') } $problems->all; - - %prob_counts = - map { $_ => $prob_counts{$_} || 0 } - ( FixMyStreet::DB::Result::Problem->all_states() ); - $c->stash->{problems} = \%prob_counts; - $c->stash->{total_problems_live} += $prob_counts{$_} ? $prob_counts{$_} : 0 - for ( FixMyStreet::DB::Result::Problem->visible_states() ); - $c->stash->{total_problems_users} = $c->cobrand->problems->unique_users; - - my $comments = $c->cobrand->updates->summary_count; - - my %comment_counts = - map { $_->state => $_->get_column('state_count') } $comments->all; - - $c->stash->{comments} = \%comment_counts; -} - -sub stats_fix_rate : Path('stats/fix-rate') : Args(0) { - my ( $self, $c ) = @_; - - $c->stash->{categories} = $c->cobrand->problems->categories_summary(); -} - -sub stats : Path('stats') : Args(0) { - my ( $self, $c ) = @_; - - my $selected_body; - if ( $c->user->is_superuser ) { - $c->forward('fetch_all_bodies'); - $selected_body = $c->get_param('body'); - } else { - $selected_body = $c->user->from_body->id; - } - - if ( $c->cobrand->moniker eq 'zurich' ) { - return $c->cobrand->admin_stats(); - } - - if ( $c->get_param('getcounts') ) { - - my ( $start_date, $end_date, @errors ); - my $parser = DateTime::Format::Strptime->new( pattern => '%d/%m/%Y' ); - - $start_date = $parser-> parse_datetime ( $c->get_param('start_date') ); - - push @errors, _('Invalid start date') unless defined $start_date; - - $end_date = $parser-> parse_datetime ( $c->get_param('end_date') ) ; - - push @errors, _('Invalid end date') unless defined $end_date; - - $c->stash->{errors} = \@errors; - $c->stash->{start_date} = $start_date; - $c->stash->{end_date} = $end_date; - - $c->stash->{unconfirmed} = $c->get_param('unconfirmed') eq 'on' ? 1 : 0; - - return 1 if @errors; - - my $bymonth = $c->get_param('bymonth'); - $c->stash->{bymonth} = $bymonth; - - $c->stash->{selected_body} = $selected_body; - - my $field = 'confirmed'; - - $field = 'created' if $c->get_param('unconfirmed'); - - my $one_day = DateTime::Duration->new( days => 1 ); - - - my %select = ( - select => [ 'state', { 'count' => 'me.id' } ], - as => [qw/state count/], - group_by => [ 'state' ], - order_by => [ 'state' ], - ); - - if ( $c->get_param('bymonth') ) { - %select = ( - select => [ - { extract => \"year from $field", -as => 'c_year' }, - { extract => \"month from $field", -as => 'c_month' }, - { 'count' => 'me.id' } - ], - as => [qw/c_year c_month count/], - group_by => [qw/c_year c_month/], - order_by => [qw/c_year c_month/], - ); - } - - my $p = $c->cobrand->problems->to_body($selected_body)->search( - { - -AND => [ - $field => { '>=', $start_date}, - $field => { '<=', $end_date + $one_day }, - ], - }, - \%select, - ); - - # in case the total_report count is 0 - $c->stash->{show_count} = 1; - $c->stash->{states} = $p; - } - - return 1; -} - =head2 set_allowed_pages Sets up the allowed_pages stash entry for checking if the current page is diff --git a/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm b/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm deleted file mode 100644 index 2058ea872..000000000 --- a/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm +++ /dev/null @@ -1,225 +0,0 @@ -package FixMyStreet::App::Controller::Admin::AreaStats; -use Moose; -use namespace::autoclean; -use List::Util qw(sum); - -BEGIN { extends 'Catalyst::Controller'; } - -sub index : Path : Args(0) { - my ( $self, $c ) = @_; - - my $user = $c->user; - - if ($user->is_superuser) { - $c->forward('/admin/fetch_all_bodies'); - } elsif ( $user->from_body ) { - $c->forward('load_user_body', [ $user->from_body->id ]); - $c->stash->{body_id} = $user->from_body->id; - if ($user->area_id) { - $c->stash->{area_id} = $user->area_id; - $c->forward('setup_area'); - $c->visit( 'stats' ); - } else { - # visit body_stats so we load the list of child areas - $c->visit( 'body_stats', [ $user->from_body->id], [] ); - } - } else { - $c->detach( '/page_error_404_not_found' ); - } -} - -sub check_user : Private { - my ( $self, $c, $body_id, $area_id ) = @_; - - my $user = $c->user; - - return if $user->is_superuser; - - if ($body_id and $user->from_body->id eq $body_id) { - if (not $user->area_id) { - return; - } elsif ($area_id and $user->area_id eq $area_id) { - return; - } - } - - $c->detach( '/page_error_404_not_found' ); -} - -sub setup_area : Private { - my ($self, $c) = @_; - - my $area = mySociety::MaPit::call('area', $c->stash->{area_id} ); - $c->detach( '/page_error_404_not_found' ) if $area->{error}; - $c->stash->{area} = $area; -} - -sub body_base : Chained('/') : PathPart('admin/areastats') : CaptureArgs(1) { - my ($self, $c, $body_id) = @_; - - $c->forward('/admin/lookup_body', $body_id); - $c->stash->{areas} = mySociety::MaPit::call('area/children', [ $c->stash->{body_id} ] ); -} - -sub body_stats : Chained('body_base') : PathPart('') : Args(0) { - my ($self, $c) = @_; - - if ($c->get_param('area')) { - $c->forward('check_user', [$c->stash->{body_id}, $c->get_param('area')]); - $c->stash->{area_id} = $c->get_param('area'); - $c->forward('setup_area'); - } else { - $c->forward('check_user', [$c->stash->{body_id}]); - } - $c->forward('stats'); -} - -sub stats : Private { - my ($self, $c) = @_; - - my $date = DateTime->now->subtract(days => 30); - # set it to midnight so we get consistent result through the day - $date->truncate( to => 'day' ); - - $c->forward('/admin/fetch_contacts'); - - $c->stash->{template} = 'admin/areastats/area.html'; - - my $dtf = $c->model('DB')->storage->datetime_parser; - my $time = $dtf->format_datetime($date); - - my $params = { - 'me.confirmed' => { '>=', $time }, - }; - - my %area_param = (); - if ($c->stash->{area}) { - my $area_id = $c->stash->{area_id}; - $c->stash->{area_name} = $c->stash->{area}->{name}; - $params->{'problem.areas'} = { like => "%,$area_id,%" }; - %area_param = ( - areas => { like => "%,$area_id,%" }, - ); - } else { - $c->stash->{area_name} = $c->stash->{body}->name; - } - - my %by_category = map { $_->category => {} } $c->stash->{contacts}->all; - my %recent_by_category = map { $_->category => 0 } $c->stash->{contacts}->all; - - my $state_map = {}; - - $state_map->{$_} = 'open' foreach FixMyStreet::DB::Result::Problem->open_states; - $state_map->{$_} = 'closed' foreach FixMyStreet::DB::Result::Problem->closed_states; - $state_map->{$_} = 'fixed' foreach FixMyStreet::DB::Result::Problem->fixed_states; - $state_map->{$_} = 'scheduled' foreach ('planned', 'action scheduled'); - - # current problems by category and state - my $problems = $c->model('DB::Problem')->to_body( - $c->stash->{body} - )->search( - \%area_param, - { - group_by => [ 'category', 'state' ], - select => [ 'category', 'state', { count => 'me.id' } ], - as => [ qw/category state state_count/ ], - } - ); - - while (my $p = $problems->next) { - my $meta_state = $state_map->{$p->state}; - $by_category{$p->category}->{$meta_state} += $p->get_column('state_count'); - } - $c->stash->{by_category} = \%by_category; - - # problems this month by state - $c->stash->{$_} = 0 for values %$state_map; - - $c->stash->{open} = $c->model('DB::Problem')->to_body( - $c->stash->{body} - )->search( - { - %area_param, - confirmed => { '>=' => $time }, - } - )->count; - - my $comments = $c->model('DB::Comment')->to_body( - $c->stash->{body} - )->search( - { - %$params, - 'me.id' => { 'in' => \"(select min(id) from comment where me.problem_id=comment.problem_id and problem_state not in ('', 'confirmed') group by problem_state)" }, - }, - { - join => 'problem', - group_by => [ 'problem_state' ], - select => [ 'problem_state', { count => 'me.id' } ], - as => [ qw/problem_state state_count/ ], - } - ); - - while (my $comment = $comments->next) { - my $meta_state = $state_map->{$comment->problem_state}; - $c->stash->{$meta_state} += $comment->get_column('state_count'); - } - - $params = { - %area_param, - 'me.confirmed' => { '>=', $time }, - }; - - # problems this month by category - my $recent_problems = $c->model('DB::Problem')->to_body( - $c->stash->{body} - )->search( - $params, - { - group_by => [ 'category' ], - select => [ 'category', { count => 'me.id' } ], - as => [ qw/category category_count/ ], - } - ); - - while (my $p = $recent_problems->next) { - $recent_by_category{$p->category} += $p->get_column('category_count'); - } - $c->stash->{recent_by_category} = \%recent_by_category; - - # average time to state change in last month - $params = { - %area_param, - 'problem.confirmed' => { '>=', $time }, - }; - - $comments = $c->model('DB::Comment')->to_body( - $c->stash->{body} - )->search( - { %$params, - 'me.id' => \"= (select min(id) from comment where me.problem_id=comment.problem_id)", - 'me.problem_state' => { '!=' => 'confirmed' }, - }, - { - select => [ - { avg => { extract => "epoch from me.confirmed-problem.confirmed" } }, - ], - as => [ qw/time/ ], - join => 'problem' - } - )->first; - my $raw_average = $comments->get_column('time'); - if (defined $raw_average) { - $c->stash->{average} = int( $raw_average / 60 / 60 / 24 + 0.5 ); - } else { - $c->stash->{average} = -1; - } -} - -sub load_user_body : Private { - my ($self, $c, $body_id) = @_; - - $c->stash->{body} = $c->model('DB::Body')->find($body_id) - or $c->detach( '/page_error_404_not_found' ); -} - -1; diff --git a/perllib/FixMyStreet/App/Controller/Admin/Stats.pm b/perllib/FixMyStreet/App/Controller/Admin/Stats.pm new file mode 100644 index 000000000..2860b3531 --- /dev/null +++ b/perllib/FixMyStreet/App/Controller/Admin/Stats.pm @@ -0,0 +1,75 @@ +package FixMyStreet::App::Controller::Admin::Stats; +use Moose; +use namespace::autoclean; + +BEGIN { extends 'Catalyst::Controller'; } + +sub index : Path : Args(0) { + my ( $self, $c ) = @_; + return $c->cobrand->admin_stats() if $c->cobrand->moniker eq 'zurich'; +} + +sub state : Local : Args(0) { + my ( $self, $c ) = @_; + + my $problems = $c->cobrand->problems->summary_count; + + my %prob_counts = + map { $_->state => $_->get_column('state_count') } $problems->all; + + %prob_counts = + map { $_ => $prob_counts{$_} || 0 } + ( FixMyStreet::DB::Result::Problem->all_states() ); + $c->stash->{problems} = \%prob_counts; + $c->stash->{total_problems_live} += $prob_counts{$_} ? $prob_counts{$_} : 0 + for ( FixMyStreet::DB::Result::Problem->visible_states() ); + $c->stash->{total_problems_users} = $c->cobrand->problems->unique_users; + + my $comments = $c->cobrand->updates->summary_count; + + my %comment_counts = + map { $_->state => $_->get_column('state_count') } $comments->all; + + $c->stash->{comments} = \%comment_counts; +} + +sub fix_rate : Path('fix-rate') : Args(0) { + my ( $self, $c ) = @_; + + $c->stash->{categories} = $c->cobrand->problems->categories_summary(); +} + +sub questionnaire : Local : Args(0) { + my ( $self, $c ) = @_; + + my $questionnaires = $c->model('DB::Questionnaire')->search( + { whenanswered => { '!=', undef } }, + { group_by => [ 'ever_reported' ], + select => [ 'ever_reported', { count => 'me.id' } ], + as => [ qw/reported questionnaire_count/ ] } + ); + + my %questionnaire_counts = map { + ( defined $_->get_column( 'reported' ) ? $_->get_column( 'reported' ) : -1 ) + => $_->get_column( 'questionnaire_count' ) + } $questionnaires->all; + $questionnaire_counts{1} ||= 0; + $questionnaire_counts{0} ||= 0; + $questionnaire_counts{total} = $questionnaire_counts{0} + $questionnaire_counts{1}; + $c->stash->{questionnaires} = \%questionnaire_counts; + + $c->stash->{state_changes_count} = $c->model('DB::Questionnaire')->search( + { whenanswered => \'is not null' } + )->count; + $c->stash->{state_changes} = $c->model('DB::Questionnaire')->search( + { whenanswered => \'is not null' }, + { + group_by => [ 'old_state', 'new_state' ], + columns => [ 'old_state', 'new_state', { c => { count => 'id' } } ], + }, + ); + + return 1; +} + +1; diff --git a/perllib/FixMyStreet/App/Controller/Dashboard.pm b/perllib/FixMyStreet/App/Controller/Dashboard.pm index 264845d40..834d9c8d6 100644 --- a/perllib/FixMyStreet/App/Controller/Dashboard.pm +++ b/perllib/FixMyStreet/App/Controller/Dashboard.pm @@ -3,8 +3,9 @@ use Moose; use namespace::autoclean; use DateTime; -use File::Slurp; use JSON::MaybeXS; +use Path::Tiny; +use Time::Piece; BEGIN { extends 'Catalyst::Controller'; } @@ -20,43 +21,21 @@ Catalyst Controller. =cut +sub auto : Private { + my ($self, $c) = @_; + $c->stash->{filter_states} = $c->cobrand->state_groups_inspect; + return 1; +} + sub example : Local : Args(0) { my ( $self, $c ) = @_; $c->stash->{template} = 'dashboard/index.html'; - $c->stash->{filter_states} = $c->cobrand->state_groups_inspect; - - $c->stash->{children} = {}; - for my $i (1..3) { - $c->stash->{children}{$i} = { id => $i, name => "Ward $i" }; - } - - # TODO Set up manual version of what the below would do - #$c->forward( '/report/new/setup_categories_and_bodies' ); - - # See if we've had anything from the dropdowns - perhaps vary results if so - $c->stash->{ward} = $c->get_param('ward'); - $c->stash->{category} = $c->get_param('category'); - $c->stash->{q_state} = $c->get_param('state'); + $c->stash->{group_by} = 'category+state'; eval { - my $data = File::Slurp::read_file( - FixMyStreet->path_to( 'data/dashboard.json' )->stringify - ); - my $j = decode_json($data); - if ( !$c->stash->{ward} && !$c->stash->{category} ) { - $c->stash->{problems} = $j->{counts_all}; - } else { - $c->stash->{problems} = $j->{counts_some}; - } - $c->stash->{council} = $j->{council}; - $c->stash->{children} = $j->{wards}; - $c->stash->{category_options} = $j->{category_options}; - if ( lc($c->stash->{q_state}) eq 'all' or !$c->stash->{q_state} ) { - $c->stash->{lists} = $j->{lists}->{all}; - } else { - $c->stash->{lists} = $j->{lists}->{filtered}; - } + my $j = decode_json(path(FixMyStreet->path_to('data/dashboard.json'))->slurp_utf8); + $c->stash($j); }; if ($@) { my $message = _("There was a problem showing this page. Please try again later.") . ' ' . @@ -77,14 +56,20 @@ sub check_page_allowed : Private { $c->detach( '/auth/redirect' ) unless $c->user_exists; $c->detach( '/page_error_404_not_found' ) - unless $c->user_exists && $c->user->from_body; + unless $c->user->from_body || $c->user->is_superuser; + + my $body = $c->user->from_body; + if (!$body && $c->get_param('body')) { + # Must be a superuser, so allow query parameter if given + $body = $c->model('DB::Body')->find({ id => $c->get_param('body') }); + } - return $c->user->from_body; + return $body; } =head2 index -Show the dashboard table. +Show the summary statistics table. =cut @@ -95,127 +80,224 @@ sub index : Path : Args(0) { $c->authenticate(undef, "access_token"); } - my $body = $c->forward('check_page_allowed'); - $c->stash->{body} = $body; + my $body = $c->stash->{body} = $c->forward('check_page_allowed'); - # Set up the data for the dropdowns - $c->stash->{filter_states} = $c->cobrand->state_groups_inspect; + if ($body) { + $c->stash->{body_name} = $body->name; - # Just take the first area ID we find - my $area_id = $body->body_areas->first->area_id; + my $area_id = $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; - my $council_detail = mySociety::MaPit::call('area', $area_id ); - $c->stash->{council} = $council_detail; + $c->forward('/admin/fetch_contacts'); + $c->stash->{contacts} = [ $c->stash->{contacts}->all ]; - my $children = mySociety::MaPit::call('area/children', $area_id, - type => $c->cobrand->area_types_children, - ); - $c->stash->{children} = $children; + # See if we've had anything from the body dropdowns + $c->stash->{category} = $c->get_param('category'); + $c->stash->{ward} = $c->get_param('ward'); + if ($c->user->area_id) { + $c->stash->{ward} = $c->user->area_id; + $c->stash->{body_name} = join "", map { $children->{$_}->{name} } grep { $children->{$_} } $c->user->area_id; + } + } else { + $c->forward('/admin/fetch_all_bodies'); + } - $c->stash->{all_areas} = { $area_id => $council_detail }; - $c->forward( '/report/new/setup_categories_and_bodies' ); + $c->stash->{start_date} = $c->get_param('start_date'); + $c->stash->{end_date} = $c->get_param('end_date'); + $c->stash->{q_state} = $c->get_param('state') || ''; - # See if we've had anything from the dropdowns + $c->forward('construct_rs_filter'); - $c->stash->{ward} = $c->get_param('ward'); - $c->stash->{category} = $c->get_param('category'); + if ( $c->get_param('export') ) { + $self->export_as_csv($c); + } else { + $self->generate_data($c); + } +} - my %where = ( - 'problem.state' => [ FixMyStreet::DB::Result::Problem->visible_states() ], - ); +sub construct_rs_filter : Private { + my ($self, $c) = @_; + + my %where; $where{areas} = { 'like', '%,' . $c->stash->{ward} . ',%' } if $c->stash->{ward}; $where{category} = $c->stash->{category} if $c->stash->{category}; - $c->stash->{where} = \%where; - my $prob_where = { %where }; - $prob_where->{'me.state'} = $prob_where->{'problem.state'}; - delete $prob_where->{'problem.state'}; - $c->stash->{prob_where} = $prob_where; - - my $dtf = $c->model('DB')->storage->datetime_parser; - my %counts; - my $now = DateTime->now( time_zone => FixMyStreet->local_time_zone ); - my $t = $now->clone->truncate( to => 'day' ); - $counts{wtd} = $c->forward( 'updates_search', - [ $dtf->format_datetime( $t->clone->subtract( days => $t->dow - 1 ) ) ] ); - $counts{week} = $c->forward( 'updates_search', - [ $dtf->format_datetime( $now->clone->subtract( weeks => 1 ) ) ] ); - $counts{weeks} = $c->forward( 'updates_search', - [ $dtf->format_datetime( $now->clone->subtract( weeks => 4 ) ) ] ); - $counts{ytd} = $c->forward( 'updates_search', - [ $dtf->format_datetime( $t->clone->set( day => 1, month => 1 ) ) ] ); - - $c->stash->{problems} = \%counts; + my $state = $c->stash->{q_state}; + if ( FixMyStreet::DB::Result::Problem->fixed_states->{$state} ) { # Probably fixed - council + $where{'me.state'} = [ FixMyStreet::DB::Result::Problem->fixed_states() ]; + } elsif ( $state ) { + $where{'me.state'} = $state; + } else { + $where{'me.state'} = [ FixMyStreet::DB::Result::Problem->visible_states() ]; + } - # List of reports underneath summary table + my $dtf = $c->model('DB')->storage->datetime_parser; + my $date = DateTime->now( time_zone => FixMyStreet->local_time_zone )->subtract(days => 30); + $date->truncate( to => 'day' ); - $c->stash->{q_state} = $c->get_param('state') || ''; - if ( $c->stash->{q_state} eq 'fixed - council' ) { - $prob_where->{'me.state'} = [ FixMyStreet::DB::Result::Problem->fixed_states() ]; - } elsif ( $c->stash->{q_state} ) { - $prob_where->{'me.state'} = $c->stash->{q_state}; - } - my $params = { - %$prob_where, - 'me.confirmed' => { '>=', $dtf->format_datetime( $now->clone->subtract( days => 30 ) ) }, - }; + $where{'me.confirmed'} = { '>=', $dtf->format_datetime($date) }; - if ( $c->get_param('start_date') or $c->get_param('end_date') ) { + my $start_date = $c->stash->{start_date}; + my $end_date = $c->stash->{end_date}; + if ($start_date or $end_date) { my @parts; - if ($c->get_param('start_date')) { - my $date = $dtf->parse_datetime( $c->get_param('start_date') ); + if ($start_date) { + my $date = $dtf->parse_datetime($start_date); push @parts, { '>=', $dtf->format_datetime( $date ) }; - $c->stash->{start_date} = $c->get_param('start_date'); } - if ($c->get_param('end_date')) { + if ($end_date) { my $one_day = DateTime::Duration->new( days => 1 ); - my $date = $dtf->parse_datetime( $c->get_param('end_date') ); + my $date = $dtf->parse_datetime($end_date); push @parts, { '<', $dtf->format_datetime( $date + $one_day ) }; - $c->stash->{end_date} = $c->get_param('end_date'); } if (scalar @parts == 2) { - $params->{'me.confirmed'} = [ -and => $parts[0], $parts[1] ]; + $where{'me.confirmed'} = [ -and => $parts[0], $parts[1] ]; } else { - $params->{'me.confirmed'} = $parts[0]; + $where{'me.confirmed'} = $parts[0]; } } - my $problems_rs = $c->cobrand->problems->to_body($body)->search( $params ); - my @problems = $problems_rs->all; + $c->stash->{params} = \%where; + $c->stash->{problems_rs} = $c->cobrand->problems->to_body($c->stash->{body})->search( \%where ); +} - my %problems; - foreach (@problems) { - if ($_->confirmed >= $now->clone->subtract(days => 7)) { - push @{$problems{1}}, $_; - } elsif ($_->confirmed >= $now->clone->subtract(days => 14)) { - push @{$problems{2}}, $_; - } else { - push @{$problems{3}}, $_; +sub generate_data { + my ($self, $c) = @_; + + my $state_map = $c->stash->{state_map} = {}; + $state_map->{$_} = 'open' foreach FixMyStreet::DB::Result::Problem->open_states; + $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); + $self->generate_summary_figures($c); +} + +sub generate_grouped_data { + my ($self, $c) = @_; + my $state_map = $c->stash->{state_map}; + + my $group_by = $c->get_param('group_by') || ''; + my (%grouped, @groups, %totals); + if ($group_by eq 'category') { + %grouped = map { $_->category => {} } @{$c->stash->{contacts}}; + @groups = qw/category/; + } elsif ($group_by eq 'state') { + @groups = qw/state/; + } elsif ($group_by eq 'month') { + @groups = ( + { extract => \"month from confirmed", -as => 'c_month' }, + { extract => \"year from confirmed", -as => 'c_year' }, + ); + } elsif ($group_by eq 'device+site') { + @groups = qw/cobrand service/; + } else { + $group_by = 'category+state'; + @groups = qw/category state/; + %grouped = map { $_->category => {} } @{$c->stash->{contacts}}; + } + my $problems = $c->stash->{problems_rs}->search(undef, { + group_by => [ map { ref $_ ? $_->{-as} : $_ } @groups ], + select => [ @groups, { count => 'me.id' } ], + as => [ @groups == 2 ? qw/key1 key2 count/ : qw/key1 count/ ], + } ); + $c->stash->{group_by} = $group_by; + + my %columns; + while (my $p = $problems->next) { + my %cols = $p->get_columns; + my ($col1, $col2) = ($cols{key1}, $cols{key2}); + if ($group_by eq 'category+state') { + $col2 = $state_map->{$cols{key2}}; + } elsif ($group_by eq 'month') { + $col1 = Time::Piece->strptime("2017-$cols{key1}-01", '%Y-%m-%d')->fullmonth; } + $grouped{$col1}->{$col2} += $cols{count} if defined $col2; + $grouped{$col1}->{total} += $cols{count}; + $totals{$col2} += $cols{count} if defined $col2; + $totals{total} += $cols{count}; + $columns{$col2} = 1 if defined $col2; } - $c->stash->{lists} = \%problems; - if ( $c->get_param('export') ) { - $self->export_as_csv($c, $problems_rs, $body); + my @columns = keys %columns; + my @rows = keys %grouped; + if ($group_by eq 'month') { + my %months; + my @months = qw/January February March April May June + July August September October November December/; + @months{@months} = (0..11); + @rows = sort { $months{$a} <=> $months{$b} } @rows; + } elsif ($group_by eq 'state') { + my $state_map = $c->stash->{state_map}; + my %map = (confirmed => 0, open => 1, fixed => 2, closed => 3); + @rows = sort { + my $am = $map{$a} // $map{$state_map->{$a}}; + my $bm = $map{$b} // $map{$state_map->{$b}}; + $am <=> $bm; + } @rows; + } else { + @rows = sort @rows; + } + $c->stash->{rows} = \@rows; + $c->stash->{columns} = \@columns; + + $c->stash->{grouped} = \%grouped; + $c->stash->{totals} = \%totals; +} + +sub generate_summary_figures { + my ($self, $c) = @_; + my $state_map = $c->stash->{state_map}; + + # problems this month by state + $c->stash->{"summary_$_"} = 0 for values %$state_map; + + $c->stash->{summary_open} = $c->stash->{problems_rs}->count; + + my $params = $c->stash->{params}; + $params = { map { my $n = $_; s/me\./problem\./ unless /me\.confirmed/; $_ => $params->{$n} } keys %$params }; + + my $comments = $c->model('DB::Comment')->to_body( + $c->stash->{body} + )->search( + { + %$params, + 'me.id' => { 'in' => \"(select min(id) from comment where me.problem_id=comment.problem_id and problem_state not in ('', 'confirmed') group by problem_state)" }, + }, + { + join => 'problem', + group_by => [ 'problem_state' ], + select => [ 'problem_state', { count => 'me.id' } ], + as => [ qw/problem_state count/ ], + } + ); + + while (my $comment = $comments->next) { + my $meta_state = $state_map->{$comment->problem_state}; + next if $meta_state eq 'open'; + $c->stash->{"summary_$meta_state"} += $comment->get_column('count'); } } sub export_as_csv { - my ($self, $c, $problems_rs, $body) = @_; + my ($self, $c) = @_; require Text::CSV; - my $problems = $problems_rs->search( + my $problems = $c->stash->{problems_rs}->search( {}, { prefetch => 'comments', order_by => 'me.confirmed' }); my $filename = do { my %where = ( - body => $body->id, 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 { @@ -308,88 +390,13 @@ sub export_as_csv { $c->res->body( join "", @body ); } -sub updates_search : Private { - my ( $self, $c, $time ) = @_; - - my $body = $c->stash->{body}; - - my $params = { - %{$c->stash->{where}}, - 'me.confirmed' => { '>=', $time }, - }; - - my $comments = $c->model('DB::Comment')->to_body($body)->search( - $params, - { - group_by => [ 'problem_state' ], - select => [ 'problem_state', { count => 'me.id' } ], - as => [ qw/state state_count/ ], - join => 'problem' - } - ); - - my %counts = - map { ($_->state||'-') => $_->get_column('state_count') } $comments->all; - %counts = - map { $_ => $counts{$_} || 0 } - ('confirmed', 'investigating', 'in progress', 'closed', 'fixed - council', - 'fixed - user', 'fixed', 'unconfirmed', 'hidden', - 'partial', 'action scheduled', 'planned'); - - $counts{'action scheduled'} += $counts{planned} || 0; - - for my $vars ( - [ 'time_to_fix', 'fixed - council' ], - [ 'time_to_mark', 'in progress', 'action scheduled', 'investigating', 'closed' ], - ) { - my $col = shift @$vars; - my $substmt = "select min(id) from comment where me.problem_id=comment.problem_id and problem_state in ('" - . join("','", @$vars) . "')"; - $comments = $c->model('DB::Comment')->to_body($body)->search( - { %$params, - problem_state => $vars, - 'me.id' => \"= ($substmt)", - }, - { - select => [ - { count => 'me.id' }, - { avg => { extract => "epoch from me.confirmed-problem.confirmed" } }, - ], - as => [ qw/state_count time/ ], - join => 'problem' - } - )->first; - $counts{$col} = int( ($comments->get_column('time')||0) / 60 / 60 / 24 + 0.5 ); - } - - $counts{fixed_user} = $c->model('DB::Comment')->to_body($body)->search( - { %$params, mark_fixed => 1, problem_state => undef }, { join => 'problem' } - )->count; - - $params = { - %{$c->stash->{prob_where}}, - 'me.confirmed' => { '>=', $time }, - }; - $counts{total} = $c->cobrand->problems->to_body($body)->search( $params )->count; - - $params = { - %{$c->stash->{prob_where}}, - 'me.confirmed' => { '>=', $time }, - state => 'confirmed', - '(select min(id) from comment where me.id=problem_id and problem_state is not null)' => undef, - }; - $counts{not_marked} = $c->cobrand->problems->to_body($body)->search( $params )->count; - - return \%counts; -} - =head1 AUTHOR Matthew Somerville =head1 LICENSE -Copyright (c) 2012 UK Citizens Online Democracy. All rights reserved. +Copyright (c) 2017 UK Citizens Online Democracy. All rights reserved. Licensed under the Affero GPL. =cut |