split up stats by month instead of state

2 files changed, 54 insertions, 8 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Admin.pm b/perllib/FixMyStreet/App/Controller/Admin.pm
index af1aeda71..3282ae831 100644
--- a/perllib/FixMyStreet/App/Controller/Admin.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin.pm
@@ -865,7 +865,9 @@ sub stats : Path('stats') : Args(0) {
 
         return 1 if @errors;
 
-        my %council;
+        my $bymonth = $c->req->param('bymonth');
+        $c->stash->{bymonth} = $bymonth;
+        my ( %council, %dates );
         $council{council} = { like => $c->req->param('council') } 
             if $c->req->param('council');
 
@@ -877,20 +879,37 @@ sub stats : Path('stats') : Args(0) {
 
         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->req->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->model('DB::Problem')->search(
             {
                 -AND => [
                     $field => { '>=', $start_date},
                     $field => { '<=', $end_date + $one_day },
                 ],
-                %council
+                %council,
+                %dates,
             },
-            {
-                select => [ 'state', { 'count' => 'me.id' } ],
-                as => [qw/state count/],
-                group_by => [ 'state' ],
-                order_by => [ 'state' ],
-            }
+            \%select,
         );
 
         # in case the total_report count is 0
diff --git a/templates/web/default/admin/stats.html b/templates/web/default/admin/stats.html
index 600cf6ae2..3b9f4a264 100644
--- a/templates/web/default/admin/stats.html
+++ b/templates/web/default/admin/stats.html
@@ -4,6 +4,28 @@
 <p>
 <strong>[% tprintf( loc( '%sreports between %s and %s' ), ( unconfirmed ? loc( 'All' ) _ ' ' : loc( 'Confirmed' ) _ ' ' ), start_date.ymd, end_date.ymd ) | html %]</strong>
 </p>
+[% IF bymonth %]
+<table>
+    <thead>
+        <td style="width: 8em"><strong>[% loc('Year') %]</strong></td>
+        <td style="width: 8em"><strong>[% loc('Month') %]</strong></td>
+        <td><strong>[% loc('Count') %]</strong></td>
+    </thead>
+    [% total = 0 %]
+    [% WHILE ( state = states.next ) %]
+    [% total = total + state.get_column( 'count' ) %]
+    <tr>
+        <td>[% state.get_column( 'c_year') | html %]</td>
+        <td>[% state.get_column( 'c_month') | html %]</td>
+        <td>[% state.get_column( 'count' ) %]</td>
+    </tr>
+    [% END %]
+    <tr>
+        <td colspan="2"><strong>[% loc( 'Total' ) %]</strong></td>
+        <td><strong>[% total %]</strong></td>
+    </tr>
+</table>
+[% ELSE %]
 <table>
     <thead>
         <td style="width: 8em"><strong>[% loc('Current state') %]</strong></td>
@@ -22,6 +44,7 @@
         <td><strong>[% total %]</strong></td>
     </tr>
 </table>
+[% END %]
 
 [% IF unconfirmed %]
     <p>
@@ -54,6 +77,10 @@
     </p>
 
     <p>
+    <input type="checkbox" name="bymonth" id="bymonth"[% bymonth ? ' checked' : '' %] /><label for="bymonth">[% loc('By Date') %]</label>
+    </p>
+
+    <p>
     [% loc('Council:') %] <select id='council' name='council'>
         <option value=''>[% loc('No council') %]</option>
         [% FOR council IN council_ids %]