Merge branch '1919-consolidate-statistics'

1 files changed, 133 insertions, 682 deletions

diff --git a/t/app/controller/dashboard.t b/t/app/controller/dashboard.t
index 83833ee7d..b53056968 100644
--- a/t/app/controller/dashboard.t
+++ b/t/app/controller/dashboard.t
@@ -1,600 +1,150 @@
 use Test::MockTime ':all';
+use strict;
+use warnings;
 
 use FixMyStreet::TestMech;
 use Web::Scraper;
 
-my $mech = FixMyStreet::TestMech->new;
-
-my $test_user = 'council_user@example.com';
-my $test_pass = 'password';
-my $test_council = 2651;
-my $test_ward = 20723;
-
-my $body = $mech->create_body_ok($test_council, 'City of Edinburgh Council');
+set_absolute_time('2014-02-01T12:00:00');
 
-$mech->delete_user( $test_user );
-my $user = $mech->create_user_ok($test_user, password => $test_pass);
+my $mech = FixMyStreet::TestMech->new;
 
-my $p_user = $mech->create_user_ok('p_user@example.com');
+my $other_body = $mech->create_body_ok(1234, 'Some Other Council');
+my $body = $mech->create_body_ok(2651, 'City of Edinburgh Council');
+my @cats = ('Litter', 'Other', 'Potholes', 'Traffic lights');
+for my $contact ( @cats ) {
+    $mech->create_contact_ok(body_id => $body->id, category => $contact, email => "$contact\@example.org");
+}
 
-# Dashboard tests assume we are not too early in year, to allow reporting
-# within same year, as a convenience.
-set_absolute_time('2014-03-01T12:00:00');
-FixMyStreet::override_config {
-    ALLOWED_COBRANDS => [ { fixmystreet => '.' } ],
-    MAPIT_URL => 'http://mapit.uk/',
-}, sub {
+my $superuser = $mech->create_user_ok('superuser@example.com', name => 'Super User', is_superuser => 1);
+my $counciluser = $mech->create_user_ok('counciluser@example.com', name => 'Council User', from_body => $body);
+my $normaluser = $mech->create_user_ok('normaluser@example.com', name => 'Normal User');
 
-    $mech->not_logged_in_ok;
-    $mech->get_ok('/dashboard');
+my $body_id = $body->id;
+my $area_id = '60705';
+my $alt_area_id = '62883';
 
-    $mech->content_contains( 'sign in' );
+my $last_month = DateTime->now->subtract(months => 2);
+$mech->create_problems_for_body(2, $body->id, 'Title', { areas => ",$area_id,2651,", category => 'Potholes', cobrand => 'fixmystreet' });
+$mech->create_problems_for_body(3, $body->id, 'Title', { areas => ",$area_id,2651,", category => 'Traffic lights', cobrand => 'fixmystreet', dt => $last_month });
+$mech->create_problems_for_body(1, $body->id, 'Title', { areas => ",$alt_area_id,2651,", category => 'Litter', cobrand => 'fixmystreet' });
 
-    $mech->submit_form(
-        with_fields => { username => $test_user, password_sign_in => $test_pass }
-    );
+my @scheduled_problems = $mech->create_problems_for_body(7, $body->id, 'Title', { areas => ",$area_id,2651,", category => 'Traffic lights', cobrand => 'fixmystreet' });
+my @fixed_problems = $mech->create_problems_for_body(4, $body->id, 'Title', { areas => ",$area_id,2651,", category => 'Potholes', cobrand => 'fixmystreet' });
+my @closed_problems = $mech->create_problems_for_body(3, $body->id, 'Title', { areas => ",$area_id,2651,", category => 'Traffic lights', cobrand => 'fixmystreet' });
 
-    is $mech->status, '404', 'If not council user get 404';
+foreach my $problem (@scheduled_problems) {
+    $problem->update({ state => 'action scheduled' });
+    $mech->create_comment_for_problem($problem, $counciluser, 'Title', 'text', 0, 'confirmed', 'action scheduled');
+}
 
-    $user->from_body( $body->id );
-    $user->update;
+foreach my $problem (@fixed_problems) {
+    $problem->update({ state => 'fixed - council' });
+    $mech->create_comment_for_problem($problem, $counciluser, 'Title', 'text', 0, 'confirmed', 'fixed');
+}
 
-    $mech->log_out_ok;
-    $mech->get_ok('/dashboard');
-    $mech->submit_form_ok( {
-        with_fields => { username => $test_user, password_sign_in => $test_pass }
-    } );
+foreach my $problem (@closed_problems) {
+    $problem->update({ state => 'closed' });
+    $mech->create_comment_for_problem($problem, $counciluser, 'Title', 'text', 0, 'confirmed', 'closed', { confirmed => \'current_timestamp' });
+}
 
-    $mech->content_contains( 'Area 2651' );
+my $categories = scraper {
+    process "select[name=category] > option", 'cats[]' => 'TEXT',
+    process "table[id=overview] > tr", 'rows[]' => scraper {
+        process 'td', 'cols[]' => 'TEXT'
+    },
+};
 
-    FixMyStreet::App->model('DB::Contact')->search( { body_id => $body->id } )
-      ->delete;
+FixMyStreet::override_config {
+    ALLOWED_COBRANDS => [ { fixmystreet => '.' } ],
+    MAPIT_URL => 'http://mapit.uk/',
+}, sub {
 
-    delete_problems();
+    subtest 'not logged in, redirected to login' => sub {
+        $mech->not_logged_in_ok;
+        $mech->get_ok('/dashboard');
+        $mech->content_contains( 'sign in' );
+    };
 
-    my @cats = qw( Grafitti Litter Potholes Other );
-    for my $contact ( @cats ) {
-        FixMyStreet::App->model('DB::Contact')->create(
-            {
-                body_id    => $body->id,
-                category   => $contact,
-                email      => "$contact\@example.org",
-                state => 'confirmed',
-                whenedited => DateTime->now,
-                editor     => 'test',
-                note       => 'test',
-            }
-        );
-    }
+    subtest 'normal user, 404' => sub {
+        $mech->log_in_ok( $normaluser->email );
+        $mech->get('/dashboard');
+        is $mech->status, '404', 'If not council user get 404';
+    };
 
-    $mech->get_ok('/dashboard');
-
-    my $categories = scraper {
-        process "select[name=category] > option", 'cats[]' => 'TEXT',
-        process "select[name=ward] > option", 'wards[]' => 'TEXT',
-        process "table[id=overview] > tr", 'rows[]' => scraper {
-            process 'td', 'cols[]' => 'TEXT'
-        },
-        process "tr[id=total] > td", 'totals[]' => 'TEXT',
-        process "tr[id=fixed_council] > td", 'council[]' => 'TEXT',
-        process "tr[id=fixed_user] > td", 'user[]' => 'TEXT',
-        process "tr[id=total_fixed] > td", 'total_fixed[]' => 'TEXT',
-        process "tr[id=in_progress] > td", 'in_progress[]' => 'TEXT',
-        process "tr[id=action_scheduled] > td", 'action_scheduled[]' => 'TEXT',
-        process "tr[id=investigating] > td", 'investigating[]' => 'TEXT',
-        process "tr[id=marked] > td", 'marked[]' => 'TEXT',
-        process "tr[id=avg_marked] > td", 'avg_marked[]' => 'TEXT',
-        process "tr[id=avg_fixed] > td", 'avg_fixed[]' => 'TEXT',
-        process "tr[id=not_marked] > td", 'not_marked[]' => 'TEXT',
-        process "tr[id=closed] > td", 'closed[]' => 'TEXT',
-        process "table[id=reports] > tr > td", 'report_lists[]' => scraper {
-            process 'ul > li', 'reports[]' => 'TEXT'
-        },
+    subtest 'superuser, body list' => sub {
+        $mech->log_in_ok( $superuser->email );
+        $mech->get_ok('/dashboard');
+        # Contains body name, in list of bodies
+        $mech->content_contains('Some Other Council');
+        $mech->content_contains('Edinburgh Council');
+        $mech->content_lacks('Category:');
+        $mech->get_ok('/dashboard?body=' . $body->id);
+        $mech->content_lacks('Some Other Council');
+        $mech->content_contains('Edinburgh Council');
+        $mech->content_contains('Trowbridge');
+        $mech->content_contains('Category:');
     };
 
-    my $expected_cats = [ 'All', '-- Pick a category --', @cats ];
-    my $res = $categories->scrape( $mech->content );
-    is_deeply( $res->{cats}, $expected_cats, 'correct list of categories' );
+    subtest 'council user, ward list' => sub {
+        $mech->log_in_ok( $counciluser->email );
+        $mech->get_ok('/dashboard');
+        $mech->content_lacks('Some Other Council');
+        $mech->content_contains('Edinburgh Council');
+        $mech->content_contains('Trowbridge');
+        $mech->content_contains('Category:');
+    };
 
-    foreach my $row ( @{ $res->{rows} }[1 .. 11] ) {
-        foreach my $col ( @{ $row->{cols} } ) {
-            is $col, 0;
-        }
-    }
+    subtest 'area user can only see their area' => sub {
+        $counciluser->update({area_id => $area_id});
 
-    for my $reports ( @{ $res->{report_lists} } ) {
-        is_deeply $reports, {}, 'No reports';
-    }
+        $mech->get_ok("/dashboard");
+        $mech->content_contains('<h1>Trowbridge</h1>');
+        $mech->get_ok("/dashboard?body=" . $other_body->id);
+        $mech->content_contains('<h1>Trowbridge</h1>');
+        $mech->get_ok("/dashboard?ward=$alt_area_id");
+        $mech->content_contains('<h1>Trowbridge</h1>');
 
-    my $now = DateTime->now(time_zone => 'local');
-    foreach my $test (
-        {
-            desc => 'confirmed today with no state',
-            dt   => $now,
-            counts => [1,1,1,1],
-            report_counts => [1, 0, 0],
-        },
-        {
-            desc => 'confirmed last 7 days with no state',
-            dt   => $now->clone->subtract( days => 6, hours => 23 ),
-            counts => [1,2,2,2],
-            report_counts => [2, 0, 0],
-        },
-        {
-            desc => 'confirmed last 8 days with no state',
-            dt   => $now->clone->subtract( days => 8 ),
-            counts => [1,2,3,3],
-            report_counts => [2, 1, 0],
-        },
-        {
-            desc => 'confirmed last 2 weeks with no state',
-            dt   => $now->clone->subtract( weeks => 2, hours => 1 ),
-            counts => [1,2,4,4],
-            report_counts => [2, 1, 1],
-        },
-        {
-            desc => 'confirmed this year with no state',
-            dt   => $now->clone->subtract( weeks => 7 ),
-            counts => [1,2,4,5],
-            report_counts => [2, 1, 1],
-        },
-    ) {
-        subtest $test->{desc} => sub {
-            make_problem( { state => 'confirmed', conf_dt => $test->{dt} } );
-
-            $mech->get_ok('/dashboard');
-            $res = $categories->scrape( $mech->content );
-
-            check_row( $res, 'totals', $test->{counts} );
-            check_row( $res, 'not_marked', $test->{counts} );
-
-            check_report_counts( $res, $test->{report_counts} );
-        };
-    }
+        $counciluser->update({area_id => undef});
+    };
 
-    delete_problems();
-
-    my $is_monday = DateTime->now->day_of_week == 1 ? 1 : 0;
-
-    foreach my $test (
-        {
-            desc => 'user fixed today',
-            confirm_dt   => DateTime->now->subtract( days => 1 ),
-            mark_dt      => DateTime->now,
-            state => 'fixed - user',
-            counts => {
-                totals => $is_monday ? [0,1,1,1] : [1,1,1,1],
-                user => [1,1,1,1],
-                council => [0,0,0,0],
-                avg_fixed => [0,0,0,0],
-                total_fixed => [1,1,1,1],
-            }
-        },
-        {
-            desc => 'council fixed today',
-            confirm_dt   => DateTime->now->subtract( days => 1 ),
-            mark_dt      => DateTime->now,
-            state => 'fixed - council',
-            counts => {
-                totals => $is_monday ? [0,2,2,2] : [2,2,2,2],
-                user => [1,1,1,1],
-                council => [1,1,1,1],
-                avg_fixed => [1,1,1,1],
-                total_fixed => [2,2,2,2],
-            }
-        },
-        {
-            desc => 'marked investigating today',
-            confirm_dt   => DateTime->now->subtract( days => 1 ),
-            mark_dt      => DateTime->now,
-            state => 'investigating',
-            counts => {
-                totals => $is_monday ? [0,3,3,3] : [3,3,3,3],
-                user => [1,1,1,1],
-                council => [1,1,1,1],
-                total_fixed => [2,2,2,2],
-                avg_marked => [1,1,1,1],
-                investigating => [1,1,1,1],
-                marked => [1,1,1,1]
-            }
-        },
-        {
-            desc => 'marked in progress today',
-            confirm_dt   => DateTime->now->subtract( days => 1 ),
-            mark_dt      => DateTime->now,
-            state => 'in progress',
-            counts => {
-                totals => $is_monday ? [0,4,4,4] : [4,4,4,4],
-                user => [1,1,1,1],
-                council => [1,1,1,1],
-                total_fixed => [2,2,2,2],
-                avg_marked => [1,1,1,1],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                marked => [2,2,2,2]
-            }
-        },
-        {
-            desc => 'marked as action scheduled today',
-            confirm_dt   => DateTime->now->subtract( days => 1 ),
-            mark_dt      => DateTime->now,
-            state => 'action scheduled',
-            counts => {
-                totals => $is_monday ? [ 0,5,5,5] : [5,5,5,5],
-                user => [1,1,1,1],
-                council => [1,1,1,1],
-                total_fixed => [2,2,2,2],
-                avg_marked => [1,1,1,1],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                action_scheduled => [1,1,1,1],
-                marked => [3,3,3,3]
-            }
-        },
-        {
-            desc => 'marked as action scheduled today, confirmed a week ago',
-            confirm_dt   => DateTime->now->subtract( days => 8 ),
-            mark_dt      => DateTime->now,
-            state => 'action scheduled',
-            counts => {
-                totals => $is_monday ? [0,5,6,6] : [5,5,6,6],
-                user => [1,1,1,1],
-                council => [1,1,1,1],
-                total_fixed => [2,2,2,2],
-                avg_marked => [3,3,3,3],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                action_scheduled => [2,2,2,2],
-                marked => [4,4,4,4]
-            }
-        },
-        {
-            desc => 'marked as council fixed today, confirmed a week ago',
-            confirm_dt   => DateTime->now->subtract( days => 8 ),
-            mark_dt      => DateTime->now,
-            state => 'fixed - council',
-            counts => {
-                totals => $is_monday ? [0,5,7,7] : [5,5,7,7],
-                user => [1,1,1,1],
-                council => [2,2,2,2],
-                total_fixed => [3,3,3,3],
-                avg_fixed => [5,5,5,5],
-                avg_marked => [3,3,3,3],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                action_scheduled => [2,2,2,2],
-                marked => [4,4,4,4]
-            }
-        },
-        {
-            desc => 'marked as council fixed a week ago, confirmed 3 weeks ago',
-            confirm_dt   => DateTime->now->subtract( days => 21),
-            mark_dt      => DateTime->now->subtract( days => 8 ),
-            state => 'fixed - council',
-            counts => {
-                totals => $is_monday ? [0,5,8,8] : [5,5,8,8],
-                user => [1,1,1,1],
-                council => [2,2,3,3],
-                total_fixed => [3,3,4,4],
-                avg_fixed => [5,5,7,7],
-                avg_marked => [3,3,3,3],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                action_scheduled => [2,2,2,2],
-                marked => [4,4,4,4]
-            }
-        },
-        {
-            desc => 'marked as user fixed 6 weeks ago, confirmed 7 weeks ago',
-            confirm_dt   => DateTime->now->subtract( weeks => 6 ),
-            mark_dt      => DateTime->now->subtract( weeks => 7 ),
-            state => 'fixed - user',
-            counts => {
-                totals => $is_monday ? [0,5,8,9] : [5,5,8,9],
-                user => [1,1,1,2],
-                council => [2,2,3,3],
-                total_fixed => [3,3,4,5],
-                avg_fixed => [5,5,7,7],
-                avg_marked => [3,3,3,3],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                action_scheduled => [2,2,2,2],
-                marked => [4,4,4,4]
-            }
-        },
-        {
-            desc => 'marked as closed',
-            confirm_dt   => DateTime->now->subtract( days => 1 ),
-            mark_dt      => DateTime->now,
-            state => 'closed',
-            counts => {
-                totals => $is_monday ? [0,6,9,10] : [6,6,9,10],
-                user => [1,1,1,2],
-                council => [2,2,3,3],
-                total_fixed => [3,3,4,5],
-                avg_fixed => [5,5,7,7],
-                avg_marked => [2,2,2,2],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                action_scheduled => [2,2,2,2],
-                closed => [1,1,1,1],
-                marked => [5,5,5,5]
-            }
-        },
-        {
-            desc => 'marked as planned',
-            confirm_dt   => DateTime->now->subtract( days => 1 ),
-            mark_dt      => DateTime->now,
-            state => 'planned',
-            counts => {
-                totals => $is_monday ? [0,7,10,11] : [7,7,10,11],
-                user => [1,1,1,2],
-                council => [2,2,3,3],
-                total_fixed => [3,3,4,5],
-                avg_fixed => [5,5,7,7],
-                avg_marked => [2,2,2,2],
-                investigating => [1,1,1,1],
-                in_progress => [1,1,1,1],
-                action_scheduled => [3,3,3,3],
-                closed => [1,1,1,1],
-                marked => [6,6,6,6]
-            }
-        },
-    ) {
-        subtest $test->{desc} => sub {
-            make_problem(
-                {
-                    state   => $test->{state},
-                    conf_dt => $test->{confirm_dt},
-                    mark_dt => $test->{mark_dt},
-                }
-            );
-
-            $mech->get_ok('/dashboard');
-            $res = $categories->scrape( $mech->content );
-
-            foreach my $row ( keys %{ $test->{counts} } ) {
-                check_row( $res, $row, $test->{counts}->{$row} );
-            }
-        };
-    }
+    subtest 'The correct categories and totals shown by default' => sub {
+        $mech->get_ok("/dashboard");
+        my $expected_cats = [ 'All', @cats ];
+        my $res = $categories->scrape( $mech->content );
+        is_deeply( $res->{cats}, $expected_cats, 'correct list of categories' );
+        # Three missing as more than a month ago
+        test_table($mech->content, 1, 0, 0, 1, 0, 0, 0, 0, 2, 0, 4, 6, 7, 3, 0, 10, 10, 3, 4, 17);
+    };
 
-    delete_problems();
-
-    for my $test (
-        {
-            desc => 'Selecting no category does nothing',
-            p1 => {
-                    state   => 'confirmed',
-                    conf_dt => DateTime->now(),
-                    category => 'Potholes',
-            },
-            p2 => {
-                    state   => 'confirmed',
-                    conf_dt => DateTime->now(),
-                    category => 'Litter',
-            },
-            category => '',
-            counts => {
-                totals => [2,2,2,2],
-            },
-            counts_after => {
-                totals => [2,2,2,2],
-            },
-            report_counts => [2,0,0],
-            report_counts_after => [2,0,0],
-        },
-        {
-            desc => 'Limit display by category',
-            category => 'Potholes',
-            counts => {
-                totals => [2,2,2,2],
-            },
-            counts_after => {
-                totals => [1,1,1,1],
-            },
-            report_counts => [2,0,0],
-            report_counts_after => [1,0,0],
-        },
-        {
-            desc => 'Limit display for category with no entries',
-            category => 'Grafitti',
-            counts => {
-                totals => [2,2,2,2],
-            },
-            counts_after => {
-                totals => [0,0,0,0],
-            },
-            report_counts => [2,0,0],
-            report_counts_after => [0,0,0],
-        },
-        {
-            desc => 'Limit display by category for council fixed',
-            p1 => {
-                    state   => 'fixed - council',
-                    conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                    mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                    category => 'Potholes',
-            },
-            p2 => {
-                    state   => 'fixed - council',
-                    conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                    mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                    category => 'Litter',
-            },
-            category => 'Potholes',
-            counts => {
-                council => [0,0,2,2],
-                totals => [2,2,4,4],
-            },
-            counts_after => {
-                council => [0,0,1,1],
-                totals => [1,1,2,2],
-            },
-            report_counts => [2,2,0],
-            report_counts_after => [1,1,0],
-        },
-        {
-            desc => 'Limit display by category for user fixed',
-            p1 => {
-                    state   => 'fixed - user',
-                    conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                    mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                    category => 'Potholes',
-            },
-            p2 => {
-                    state   => 'fixed - user',
-                    conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                    mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                    category => 'Litter',
-            },
-            category => 'Potholes',
-            counts => {
-                user => [0,0,2,2],
-                council => [0,0,2,2],
-                totals => [2,2,6,6],
-            },
-            counts_after => {
-                user => [0,0,1,1],
-                council => [0,0,1,1],
-                totals => [1,1,3,3],
-            },
-            report_counts => [2,4,0],
-            report_counts_after => [1,2,0],
-        },
-        {
-            desc => 'Limit display by ward',
-            p1 => {
-                    state   => 'confirmed',
-                    conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                    category => 'Potholes',
-                    # in real life it has commas around it and the search
-                    # uses them
-                    areas => ',20720,',
-            },
-            p2 => {
-                    state   => 'fixed - council',
-                    conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                    mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                    category => 'Litter',
-                    areas => ',20720,',
-            },
-            ward => 20720,
-            counts => {
-                user => [0,0,2,2],
-                council => [0,0,3,3],
-                totals => [2,2,8,8],
-            },
-            counts_after => {
-                user => [0,0,0,0],
-                council => [0,0,1,1],
-                totals => [0,0,2,2],
-            },
-            report_counts => [2,6,0],
-            report_counts_after => [0,2,0],
-        },
-    ) {
-        subtest $test->{desc} => sub {
-            make_problem( $test->{p1} ) if $test->{p1};
-            make_problem( $test->{p2} ) if $test->{p2};
-
-            $mech->get_ok('/dashboard');
-
-            $res = $categories->scrape( $mech->content );
-
-            foreach my $row ( keys %{ $test->{counts} } ) {
-                check_row( $res, $row, $test->{counts}->{$row} );
-            }
-
-            check_report_counts( $res, $test->{report_counts} );
-
-            $mech->submit_form_ok( {
-                with_fields => {
-                    category => $test->{category},
-                    ward     => $test->{ward},
-                }
-            } );
-
-            $res = $categories->scrape( $mech->content );
-
-            foreach my $row ( keys %{ $test->{counts_after} } ) {
-                check_row( $res, $row, $test->{counts_after}->{$row} );
-            }
-            check_report_counts( $res, $test->{report_counts_after} );
-        };
-    }
+    subtest 'test filters' => sub {
+        $mech->get_ok("/dashboard");
+        $mech->submit_form_ok({ with_fields => { category => 'Litter' } });
+        test_table($mech->content, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1);
+        $mech->submit_form_ok({ with_fields => { category => '', state => 'fixed - council' } });
+        test_table($mech->content, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 4, 0, 0, 0, 0, 0, 0, 4, 4);
+        $mech->submit_form_ok({ with_fields => { state => 'action scheduled' } });
+        test_table($mech->content, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 7, 0, 0, 7, 7, 0, 0, 7);
+        my $start = DateTime->now->subtract(months => 3)->strftime('%Y-%m-%d');
+        my $end = DateTime->now->subtract(months => 1)->strftime('%Y-%m-%d');
+        $mech->submit_form_ok({ with_fields => { state => '', start_date => $start, end_date => $end } });
+        test_table($mech->content, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0, 3, 3, 0, 0, 3);
+    };
 
-    delete_problems();
-
-    for my $test (
-        {
-            desc => 'Selecting no state does nothing',
-            p1 => {
-                    state   => 'fixed - user',
-                    conf_dt => DateTime->now()->subtract( minutes => 1 ),
-                    category => 'Potholes',
-            },
-            p2 => {
-                    state   => 'confirmed',
-                    conf_dt => DateTime->now()->subtract( minutes => 1 ),
-                    category => 'Litter',
-            },
-            state => '',
-            report_counts => [2,0,0],
-            report_counts_after => [2,0,0],
-        },
-        {
-            desc => 'limit by state works',
-            state => 'fixed - council',
-            report_counts => [2,0,0],
-            report_counts_after => [1,0,0],
-        },
-        {
-            desc => 'All fixed states count as fixed',
-            p1 => {
-                    state   => 'fixed - council',
-                    conf_dt => DateTime->now()->subtract( minutes => 1 ),
-                    category => 'Potholes',
-            },
-            p2 => {
-                    state   => 'fixed',
-                    conf_dt => DateTime->now()->subtract( minutes => 1 ),
-                    category => 'Potholes',
-            },
-            state => 'fixed',
-            report_counts => [4,0,0],
-            report_counts_after => [3,0,0],
-        },
-    ) {
-        subtest $test->{desc} => sub {
-            make_problem( $test->{p1} ) if $test->{p1};
-            make_problem( $test->{p2} ) if $test->{p2};
-
-            $mech->get_ok('/dashboard');
-
-            $res = $categories->scrape( $mech->content );
-
-            check_report_counts( $res, $test->{report_counts} );
-
-            $mech->submit_form_ok( {
-                with_fields => {
-                    state => $test->{state},
-                }
-            } );
-
-            $res = $categories->scrape( $mech->content );
-
-            check_report_counts( $res, $test->{report_counts_after} );
-        };
-    }
+    subtest 'test grouping' => sub {
+        $mech->get_ok("/dashboard?group_by=category");
+        test_table($mech->content, 1, 0, 6, 10, 17);
+        $mech->get_ok("/dashboard?group_by=state");
+        test_table($mech->content, 3, 7, 4, 3, 17);
+        $mech->get_ok("/dashboard?start_date=2000-01-01&group_by=month");
+        test_table($mech->content, 0, 17, 17, 3, 0, 3, 3, 17, 20);
+    };
 
     subtest 'export as csv' => sub {
-        make_problem( {
+        $mech->create_problems_for_body(1, $body->id, 'Title', {
             detail => "this report\nis split across\nseveral lines",
-            state => "confirmed",
-            conf_dt => DateTime->now(),
-            areas => 62883,
-        } );
+            areas => ",$alt_area_id,2651,",
+        });
         $mech->get_ok('/dashboard?export=1');
         open my $data_handle, '<', \$mech->content;
         my $csv = Text::CSV->new( { binary => 1 } );
@@ -602,7 +152,7 @@ FixMyStreet::override_config {
         while ( my $row = $csv->getline( $data_handle ) ) {
             push @rows, $row;
         }
-        is scalar @rows, 6, '1 (header) + 5 (reports) = 6 lines';
+        is scalar @rows, 19, '1 (header) + 18 (reports) = 19 lines';
 
         is scalar @{$rows[0]}, 18, '18 columns present';
 
@@ -629,56 +179,16 @@ FixMyStreet::override_config {
             ],
             'Column headers look correct';
 
-        is $rows[5]->[14], 'Bradford-on-Avon', 'Ward column is name not ID';
-
-        is $rows[5]->[15], '610591', 'Correct Easting conversion';
-        is $rows[5]->[16], '126573', 'Correct Northing conversion';
-    };
-
-    delete_problems();
-    subtest 'check date restriction' => sub {
-        make_problem({ state => 'confirmed', conf_dt => DateTime->now->subtract( 'days' => 1 ) });
-        make_problem({ state => 'confirmed', conf_dt => DateTime->now->subtract( 'days' => 50 ) });
-        make_problem({ state => 'confirmed', conf_dt => DateTime->now->subtract( 'days' => 31 ) });
-
-        $mech->get_ok('/dashboard?export=1');
-        open my $data_handle, '<', \$mech->content;
-        my $csv = Text::CSV->new( { binary => 1 } );
-        my @rows;
-        while ( my $row = $csv->getline( $data_handle ) ) {
-            push @rows, $row;
-        }
-
-        is scalar @rows, 2, '1 (header) + 1 (reports) = 2 lines';
-
-        $mech->get_ok('/dashboard?export=1&start_date=' . DateTime->now->subtract('days' => 32)->ymd);
-
-        open $data_handle, '<', \$mech->content;
-        $csv = Text::CSV->new( { binary => 1 } );
-        @rows = ();
-        while ( my $row = $csv->getline( $data_handle ) ) {
-            push @rows, $row;
-        }
-
-        is scalar @rows, 3, '1 (header) + 2 (reports) = 3 lines';
-
-        $mech->get_ok('/dashboard?export=1&start_date=' . DateTime->now->subtract('days' => 51)->ymd . '&end_date=' . DateTime->now->subtract('days' => 49)->ymd );
-
-        open $data_handle, '<', \$mech->content;
-        $csv = Text::CSV->new( { binary => 1 } );
-        @rows = ();
-        while ( my $row = $csv->getline( $data_handle ) ) {
-            push @rows, $row;
-        }
-
-        is scalar @rows, 2, '1 (header) + 1 (reports) = 2 lines';
+        is $rows[5]->[14], 'Trowbridge', 'Ward column is name not ID';
+        is $rows[5]->[15], '529025', 'Correct Easting conversion';
+        is $rows[5]->[16], '179716', 'Correct Northing conversion';
     };
 
     subtest 'export as csv using token' => sub {
         $mech->log_out_ok;
 
-        $user->set_extra_metadata('access_token', '1234567890abcdefgh');
-        $user->update();
+        $counciluser->set_extra_metadata('access_token', '1234567890abcdefgh');
+        $counciluser->update();
 
         $mech->get_ok('/dashboard?export=1');
         like $mech->res->header('Content-type'), qr'text/html';
@@ -690,78 +200,19 @@ FixMyStreet::override_config {
         $mech->content_contains('Report ID');
     };
 };
-restore_time;
-
-sub make_problem {
-    my $args = shift;
-
-    my $p = FixMyStreet::App->model('DB::Problem')->create( {
-        title => 'a problem',
-        name => 'a user',
-        anonymous => 1,
-        detail => $args->{detail} || 'some detail',
-        state => $args->{state},
-        confirmed => $args->{conf_dt},
-        whensent => $args->{conf_dt},
-        lastupdate => $args->{mark_dt} || $args->{conf_dt},
-        bodies_str => $body->id,
-        postcode => 'EH99 1SP',
-        latitude => '51',
-        longitude => '1',
-        areas      => $args->{areas} || $test_ward,
-        used_map => 0,
-        user_id => $p_user->id,
-        category => $args->{category} || 'Other',
-    } );
-
-    if ( $args->{state} ne 'confirmed' ) {
-        my $c = FixMyStreet::App->model('DB::Comment')->create( {
-            problem => $p,
-            user_id => $p_user->id,
-            state => 'confirmed',
-            problem_state => $args->{state} =~ /^fixed - user|fixed$/ ? undef : $args->{state},
-            confirmed => $args->{mark_dt},
-            text => 'an update',
-            mark_fixed => $args->{state} =~ /fixed/ ? 1 : 0,
-            anonymous => 1,
-        } );
-    }
-}
-
-sub check_row {
-    my $res = shift;
-    my $row = shift;
-    my $totals = shift;
-
-    is $res->{ $row }->[0], $totals->[0], "Correct count in $row for WTD";
-    is $res->{ $row }->[1], $totals->[1], "Correct count in $row for last 7 days";
-    is $res->{ $row }->[2], $totals->[2], "Correct count in $row for last 4 weeks";
-    is $res->{ $row }->[3], $totals->[3], "Correct count in $row for YTD";
-}
 
-sub check_report_counts {
-    my $res = shift;
-    my $counts = shift;
-
-    for my $i ( 0 .. 2 ) {
-        if ( $counts->[$i] == 0 ) {
-            is_deeply $res->{report_lists}->[$i], {}, "No reports for column $i";
-        } else {
-            if ( ref( $res->{report_lists}->[$i]->{reports} ) eq 'ARRAY' ) {
-                is scalar @{ $res->{report_lists}->[$i]->{reports} }, $counts->[$i], "Correct report count for column $i";
-            } else {
-                fail "Correct report count for column $i ( no reports )";
-            }
+sub test_table {
+    my ($content, @expected) = @_;
+    my $res = $categories->scrape( $mech->content );
+    my $i = 0;
+    foreach my $row ( @{ $res->{rows} }[1 .. 11] ) {
+        foreach my $col ( @{ $row->{cols} } ) {
+            is $col, $expected[$i++];
         }
     }
 }
 
-sub delete_problems {
-    FixMyStreet::App->model('DB::Comment')
-      ->search( { 'problem.bodies_str' => $body->id }, { join => 'problem' } )
-      ->delete;
-    FixMyStreet::App->model('DB::Problem')
-      ->search( { bodies_str => $body->id } )->delete();
+END {
+    restore_time;
+    done_testing();
 }
-
-done_testing;