move area stats page problems by status calc into database

Doing this calculation in code turns out to be much too slow.

As part of this also fix an issue where if a report changed state last
month but had a further comment this month that was counted as being
a state change this month.

Also tweak the 'last month' start date to be midnight so the stats don't
change throughout the day.

2 files changed, 22 insertions, 9 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm b/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm
index 932631cba..2357e0025 100644
--- a/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin/AreaStats.pm
@@ -78,6 +78,8 @@ 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');
 
@@ -145,21 +147,21 @@ sub stats : Private {
     my $comments = $c->model('DB::Comment')->to_body(
         $c->stash->{body}
     )->search(
-        $params,
         {
-            join     => 'problem'
+            %$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/ ],
         }
     );
 
-    # you can have multiple comments with the same problem state so need to only count
-    # one instance.
-    my %state_seen = ();
     while (my $comment = $comments->next) {
         my $meta_state = $state_map->{$comment->problem_state};
-        my $key = $comment->problem->id . "-$meta_state";
-        next if $state_seen{$key};
-        $c->stash->{$meta_state} += 1;
-        $state_seen{$key} = 1;
+        $c->stash->{$meta_state} += $comment->get_column('state_count');
     }
 
     $params = {
diff --git a/t/app/controller/area_stats.t b/t/app/controller/area_stats.t
index 6ea03cabf..36e532c0e 100644
--- a/t/app/controller/area_stats.t
+++ b/t/app/controller/area_stats.t
@@ -183,6 +183,17 @@ FixMyStreet::override_config {
         $mech->text_contains('Traffic lights3730');
     };
 
+    subtest 'ignores second state change if first was last month' => sub {
+        my $comment = $scheduled_problems[0]->comments->search({}, { order_by => { '-asc' => 'id' } } )->first;
+        $comment->update({ confirmed => DateTime->now->subtract(days => 40) });
+        $mech->get_ok("/admin/areastats/$body_id?area=20720");
+
+        $mech->content_contains('15 opened, 6 scheduled, 3 closed, 4 fixed');
+        $mech->text_contains('Potholes2004');
+        $mech->text_contains('Traffic lights3730');
+        $comment->update({ confirmed => DateTime->now });
+    };
+
     subtest 'average is only to first state change' => sub {
         for my $i (0..4) {
             $scheduled_problems[$i]->comments->first->update({ confirmed => $scheduled_problems[$i]->confirmed });