Merge tag 'v2.3' into fiksgatami-dev

1 files changed, 32 insertions, 2 deletions

diff --git a/perllib/FixMyStreet/DB/Result/Body.pm b/perllib/FixMyStreet/DB/Result/Body.pm
index 6481d5cfc..e5cd2b907 100644
--- a/perllib/FixMyStreet/DB/Result/Body.pm
+++ b/perllib/FixMyStreet/DB/Result/Body.pm
@@ -156,12 +156,13 @@ sub areas {
 }
 
 sub first_area_children {
-    my ( $self, $c ) = @_;
+    my ( $self ) = @_;
 
     my $area_id = $self->body_areas->first->area_id;
 
+    my $cobrand = $self->result_source->schema->cobrand;
     my $children = mySociety::MaPit::call('area/children', $area_id,
-        type => $c->cobrand->area_types_children,
+        type => $cobrand->area_types_children,
     );
 
     return $children;
@@ -182,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;