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.

1 files changed, 11 insertions, 0 deletions

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 });