Merge branch '1919-consolidate-statistics'

2 files changed, 75 insertions, 225 deletions

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;