Merge branch 'issues/672-dashboard-export-csv-report'

6 files changed, 660 insertions, 529 deletions

diff --git a/cpanfile b/cpanfile
index 388e9f12b..e5f71cdbe 100644
--- a/cpanfile
+++ b/cpanfile
@@ -70,6 +70,7 @@ requires 'SOAP::Lite';
 requires 'Statistics::Distributions';
 requires 'Storable';
 requires 'Template::Plugin::Comma';
+requires 'Text::CSV';
 requires 'URI';
 requires 'URI::Escape';
 requires 'URI::QueryParam';
diff --git a/cpanfile.snapshot b/cpanfile.snapshot
index 0d57aaea2..16f4f3b95 100644
--- a/cpanfile.snapshot
+++ b/cpanfile.snapshot
@@ -5589,6 +5589,17 @@ DISTRIBUTIONS
       Test::Builder 0
       Test::Builder::Tester 0.09
       perl 5.007003
+  Text-CSV-1.32
+    pathname: M/MA/MAKAMAKA/Text-CSV-1.32.tar.gz
+    provides:
+      Text::CSV 1.32
+      Text::CSV::ErrorDiag 1.31
+      Text::CSV_PP 1.31
+    requirements:
+      ExtUtils::MakeMaker 0
+      IO::Handle 0
+      Test::Harness 0
+      Test::More 0
   Text-Diff-1.41
     pathname: O/OV/OVID/Text-Diff-1.41.tar.gz
     provides:
diff --git a/perllib/FixMyStreet/App/Controller/Dashboard.pm b/perllib/FixMyStreet/App/Controller/Dashboard.pm
index 028b9aadd..b47a1f54b 100644
--- a/perllib/FixMyStreet/App/Controller/Dashboard.pm
+++ b/perllib/FixMyStreet/App/Controller/Dashboard.pm
@@ -121,16 +121,22 @@ sub index : Path : Args(0) {
         if $c->stash->{category};
     $c->stash->{where} = \%where;
     my $prob_where = { %where };
-    $prob_where->{state} = $prob_where->{'problem.state'};
+    $prob_where->{'me.state'} = $prob_where->{'problem.state'};
     delete $prob_where->{'problem.state'};
     $c->stash->{prob_where} = $prob_where;
 
+    my $dtf = $c->model('DB')->storage->datetime_parser;
+
     my %counts;
     my $t = DateTime->today;
-    $counts{wtd} = $c->forward( 'updates_search', [ $t->subtract( days => $t->dow - 1 ) ] );
-    $counts{week} = $c->forward( 'updates_search', [ DateTime->now->subtract( weeks => 1 ) ] );
-    $counts{weeks} = $c->forward( 'updates_search', [ DateTime->now->subtract( weeks => 4 ) ] );
-    $counts{ytd} = $c->forward( 'updates_search', [ DateTime->today->set( day => 1, month => 1 ) ] );
+    $counts{wtd} = $c->forward( 'updates_search',
+        [ $dtf->format_datetime( $t->subtract( days => $t->dow - 1 ) ) ] );
+    $counts{week} = $c->forward( 'updates_search',
+        [ $dtf->format_datetime( DateTime->now->subtract( weeks => 1 ) ) ] );
+    $counts{weeks} = $c->forward( 'updates_search',
+        [ $dtf->format_datetime( DateTime->now->subtract( weeks => 4 ) ) ] );
+    $counts{ytd} = $c->forward( 'updates_search',
+        [ $dtf->format_datetime( DateTime->today->set( day => 1, month => 1 ) ) ] );
 
     $c->stash->{problems} = \%counts;
 
@@ -138,17 +144,19 @@ sub index : Path : Args(0) {
 
     $c->stash->{q_state} = $c->req->param('state') || '';
     if ( $c->stash->{q_state} eq 'fixed' ) {
-        $prob_where->{state} = [ FixMyStreet::DB::Result::Problem->fixed_states() ];
+        $prob_where->{'me.state'} = [ FixMyStreet::DB::Result::Problem->fixed_states() ];
     } elsif ( $c->stash->{q_state} ) {
-        $prob_where->{state} = $c->stash->{q_state};
-        $prob_where->{state} = { IN => [ 'planned', 'action scheduled' ] }
-            if $prob_where->{state} eq 'action scheduled';
+        $prob_where->{'me.state'} = $c->stash->{q_state};
+        $prob_where->{'me.state'} = { IN => [ 'planned', 'action scheduled' ] }
+            if $prob_where->{'me.state'} eq 'action scheduled';
     }
     my $params = {
         %$prob_where,
-        'me.confirmed' => { '>=', DateTime->now->subtract( days => 30 ) },
+        'me.confirmed' => { '>=', $dtf->format_datetime( DateTime->now->subtract( days => 30 ) ) },
     };
-    my @problems = $c->cobrand->problems->search( $params )->all;
+    my $problems_rs = $c->cobrand->problems->search( $params );
+    my @problems = $problems_rs->all;
+
     my %problems;
     foreach (@problems) {
         if ($_->confirmed >= DateTime->now->subtract(days => 7)) {
@@ -160,6 +168,102 @@ sub index : Path : Args(0) {
         }
     }
     $c->stash->{lists} = \%problems;
+
+    if ( $c->req->params->{export} ) {
+        $self->export_as_csv($c, $problems_rs, $body);
+    }
+}
+
+sub export_as_csv {
+    my ($self, $c, $problems_rs, $body) = @_;
+    require Text::CSV;
+    my $problems = $problems_rs->search(
+        {}, { prefetch => 'comments' });
+
+    my $filename = do {
+        my %where = (
+            body     => $body->id,
+            category => $c->stash->{category},
+            state    => $c->stash->{q_state},
+            ward     => $c->stash->{ward},
+        );
+        join '-',
+            $c->req->uri->host,
+            map {
+                my $value = $where{$_};
+                (defined $value and length $value) ? ($_, $value) : ()
+            } sort keys %where };
+
+    my $csv = Text::CSV->new();
+    $csv->combine(
+            'Report ID',
+            'Title',
+            'Detail',
+            'User Name',
+            'Category',
+            'Created',
+            'Confirmed',
+            'Acknowledged',
+            'Fixed',
+            'Closed',
+            'Status',
+            'Latitude', 'Longitude',
+            'Nearest Postcode',
+            'Report URL',
+            );
+    my @body = ($csv->string);
+
+    my $fixed_states = FixMyStreet::DB::Result::Problem->fixed_states;
+    my $closed_states = FixMyStreet::DB::Result::Problem->closed_states;
+
+    while ( my $report = $problems->next ) {
+        my $external_body;
+        my $body_name = "";
+        if ( $external_body = $report->body($c) ) {
+            # seems to be a zurich specific thing
+            $body_name = $external_body->name if ref $external_body;
+        }
+        my $hashref = $report->as_hashref($c);
+
+        $hashref->{user_name_display} = $report->anonymous?
+            '(anonymous)' : $report->user->name;
+
+        for my $comment ($report->comments) {
+            my $problem_state = $comment->problem_state or next;
+            next if $problem_state eq 'confirmed';
+            $hashref->{acknowledged_pp} //= $c->cobrand->prettify_dt( $comment->created );
+            $hashref->{fixed_pp} //= $fixed_states->{ $problem_state } ?
+                $c->cobrand->prettify_dt( $comment->created ): undef;
+            if ($closed_states->{ $problem_state }) {
+                $hashref->{closed_pp} = $c->cobrand->prettify_dt( $comment->created );
+                last;
+            }
+        }
+
+        $csv->combine(
+            @{$hashref}{
+                'id',
+                'title',
+                'detail',
+                'user_name_display',
+                'category',
+                'created_pp',
+                'confirmed_pp',
+                'acknowledged_pp',
+                'fixed_pp',
+                'closed_pp',
+                'state',
+                'latitude', 'longitude',
+                'postcode',
+                },
+            (join '', $c->cobrand->base_url_for_report($report), $report->url),
+        );
+
+        push @body, $csv->string;
+    }
+    $c->res->content_type('text/csv; charset=utf-8');
+    $c->res->header('content-disposition' => "attachment; filename=${filename}.csv");
+    $c->res->body( join "\n", @body );
 }
 
 sub updates_search : Private {
diff --git a/t/app/controller/dashboard.t b/t/app/controller/dashboard.t
index c1e9c3ace..5e4b4cbeb 100644
--- a/t/app/controller/dashboard.t
+++ b/t/app/controller/dashboard.t
@@ -2,10 +2,6 @@ use strict;
 use warnings;
 use Test::More;
 
-# XXX
-plan skip_all => 'Disabling failing dashboard tests until they can be looked at';
-exit;
-
 use FixMyStreet::TestMech;
 use Web::Scraper;
 
@@ -28,579 +24,592 @@ my $p_user = FixMyStreet::App->model('DB::User')->find_or_create( {
     email => 'p_user@example.com'
 } );
 
-$mech->not_logged_in_ok;
-$mech->get_ok('/dashboard');
-
-$mech->content_contains( 'sign in' );
+FixMyStreet::override_config {
+    ALLOWED_COBRANDS => [ { fixmystreet => '.' } ],
+    MAPIT_URL => 'http://mapit.mysociety.org/',
+}, sub {
 
-$mech->submit_form(
-    with_fields => { email => $test_user, password_sign_in => $test_pass }
-);
+    $mech->not_logged_in_ok;
+    $mech->get_ok('/dashboard');
 
-is $mech->status, '404', 'If not council user get 404';
+    $mech->content_contains( 'sign in' );
 
-$user->from_body( $test_council );
-$user->update;
-
-$mech->log_out_ok;
-$mech->get_ok('/dashboard');
-$mech->submit_form_ok( {
-    with_fields => { email => $test_user, password_sign_in => $test_pass }
-} );
+    $mech->submit_form(
+        with_fields => { email => $test_user, password_sign_in => $test_pass }
+    );
 
-$mech->content_contains( 'City of Edinburgh' );
+    is $mech->status, '404', 'If not council user get 404';
 
-FixMyStreet::App->model('DB::Contact')->search( { body_id => $test_council } )
-  ->delete;
+    $user->from_body( $test_council );
+    $user->update;
 
-delete_problems();
+    $mech->log_out_ok;
+    $mech->get_ok('/dashboard');
+    $mech->submit_form_ok( {
+        with_fields => { email => $test_user, password_sign_in => $test_pass }
+    } );
 
-my @cats = qw( Grafitti Litter Potholes Other );
-for my $contact ( @cats ) {
-    FixMyStreet::App->model('DB::Contact')->create(
-        {
-            body_id    => $test_council,
-            category   => $contact,
-            email      => "$contact\@example.org",
-            confirmed  => 1,
-            whenedited => DateTime->now,
-            deleted    => 0,
-            editor     => 'test',
-            note       => 'test',
-        }
-    );
-}
+    $mech->content_contains( 'City of Edinburgh' );
 
-$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'
-    },
-};
+    FixMyStreet::App->model('DB::Contact')->search( { body_id => $test_council } )
+      ->delete;
 
-my $expected_cats = [ 'All', '-- Pick a category --', @cats ];
-my $res = $categories->scrape( $mech->content );
-is_deeply( $res->{cats}, $expected_cats, 'correct list of categories' );
+    delete_problems();
 
-foreach my $row ( @{ $res->{rows} }[1 .. 11] ) {
-    foreach my $col ( @{ $row->{cols} } ) {
-        is $col, 0;
+    my @cats = qw( Grafitti Litter Potholes Other );
+    for my $contact ( @cats ) {
+        FixMyStreet::App->model('DB::Contact')->create(
+            {
+                body_id    => $test_council,
+                category   => $contact,
+                email      => "$contact\@example.org",
+                confirmed  => 1,
+                whenedited => DateTime->now,
+                deleted    => 0,
+                editor     => 'test',
+                note       => 'test',
+            }
+        );
     }
-}
 
-for my $reports ( @{ $res->{report_lists} } ) {
-    is_deeply $reports, {}, 'No reports';
-}
+    $mech->get_ok('/dashboard');
 
-foreach my $test (
-    {
-        desc => 'confirmed today with no state',
-        dt   => DateTime->now,
-        counts => [1,1,1,1],
-        report_counts => [1, 0, 0],
-    },
-    {
-        desc => 'confirmed last 7 days with no state',
-        dt   => DateTime->now->subtract( days => 6, hours => 23 ),
-        counts => [1,2,2,2],
-        report_counts => [2, 0, 0],
-    },
-    {
-        desc => 'confirmed last 8 days with no state',
-        dt   => DateTime->now->subtract( days => 8 ),
-        counts => [1,2,3,3],
-        report_counts => [2, 1, 0],
-    },
-    {
-        desc => 'confirmed last 4 weeks with no state',
-        dt   => DateTime->now->subtract( weeks => 2 ),
-        counts => [1,2,4,4],
-        report_counts => [2, 1, 1],
-    },
-    {
-        desc => 'confirmed this year with no state',
-        dt   => DateTime->now->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} );
+    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'
+        },
     };
-}
 
-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},
-            }
-        );
+    my $expected_cats = [ 'All', '-- Pick a category --', @cats ];
+    my $res = $categories->scrape( $mech->content );
+    is_deeply( $res->{cats}, $expected_cats, 'correct list of categories' );
 
-        $mech->get_ok('/dashboard');
-        $res = $categories->scrape( $mech->content );
-
-        foreach my $row ( keys %{ $test->{counts} } ) {
-            check_row( $res, $row, $test->{counts}->{$row} );
+    foreach my $row ( @{ $res->{rows} }[1 .. 11] ) {
+        foreach my $col ( @{ $row->{cols} } ) {
+            is $col, 0;
         }
-    };
-}
+    }
 
-delete_problems();
+    for my $reports ( @{ $res->{report_lists} } ) {
+        is_deeply $reports, {}, 'No reports';
+    }
 
-for my $test (
-    {
-        desc => 'Selecting no category does nothing',
-        p1 => {
-                state   => 'confirmed',
-                conf_dt => DateTime->now(),
-                category => 'Potholes',
+    foreach my $test (
+        {
+            desc => 'confirmed today with no state',
+            dt   => DateTime->now,
+            counts => [1,1,1,1],
+            report_counts => [1, 0, 0],
         },
-        p2 => {
-                state   => 'confirmed',
-                conf_dt => DateTime->now(),
-                category => 'Litter',
+        {
+            desc => 'confirmed last 7 days with no state',
+            dt   => DateTime->now->subtract( days => 6, hours => 23 ),
+            counts => [1,2,2,2],
+            report_counts => [2, 0, 0],
+        },
+        {
+            desc => 'confirmed last 8 days with no state',
+            dt   => DateTime->now->subtract( days => 8 ),
+            counts => [1,2,3,3],
+            report_counts => [2, 1, 0],
         },
-        category => '',
-        counts => {
-            totals => [2,2,2,2],
+        {
+            desc => 'confirmed last 4 weeks with no state',
+            dt   => DateTime->now->subtract( weeks => 2 ),
+            counts => [1,2,4,4],
+            report_counts => [2, 1, 1],
         },
-        counts_after => {
-            totals => [2,2,2,2],
+        {
+            desc => 'confirmed this year with no state',
+            dt   => DateTime->now->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} );
+        };
+    }
+
+    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],
+            }
         },
-        report_counts => [2,0,0],
-        report_counts_after => [2,0,0],
-    },
-    {
-        desc => 'Limit display by category',
-        category => 'Potholes',
-        counts => {
-            totals => [2,2,2,2],
+        {
+            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],
+            }
         },
-        counts_after => {
-            totals => [1,1,1,1],
+        {
+            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]
+            }
         },
-        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],
+        {
+            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]
+            }
         },
-        counts_after => {
-            totals => [0,0,0,0],
+        {
+            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]
+            }
         },
-        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',
+        {
+            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]
+            }
         },
-        p2 => {
-                state   => 'fixed - council',
-                conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                category => 'Litter',
+        {
+            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]
+            }
         },
-        category => 'Potholes',
-        counts => {
-            council => [0,0,2,2],
-            totals => [2,2,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]
+            }
         },
-        counts_after => {
-            council => [0,0,1,1],
-            totals => [1,1,2,2],
+        {
+            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]
+            }
         },
-        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',
+        {
+            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]
+            }
         },
-        p2 => {
-                state   => 'fixed - user',
-                conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                category => 'Litter',
+        {
+            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]
+            }
         },
-        category => 'Potholes',
-        counts => {
-            user => [0,0,2,2],
-            council => [0,0,2,2],
-            totals => [2,2,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} );
+            }
+        };
+    }
+
+    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],
         },
-        counts_after => {
-            user => [0,0,1,1],
-            council => [0,0,1,1],
-            totals => [1,1,3,3],
+        {
+            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],
         },
-        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,',
+        {
+            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],
         },
-        p2 => {
-                state   => 'fixed - council',
-                conf_dt => DateTime->now()->subtract( weeks => 1 ),
-                mark_dt => DateTime->now()->subtract( weeks => 1 ),
-                category => 'Litter',
-                areas => ',20720,',
+        {
+            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],
         },
-        ward => 20720,
-        counts => {
-            user => [0,0,2,2],
-            council => [0,0,3,3],
-            totals => [2,2,8,8],
+        {
+            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],
         },
-        counts_after => {
-            user => [0,0,0,0],
-            council => [0,0,1,1],
-            totals => [0,0,2,2],
+        {
+            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],
         },
-        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};
+    ) {
+        subtest $test->{desc} => sub {
+            make_problem( $test->{p1} ) if $test->{p1};
+            make_problem( $test->{p2} ) if $test->{p2};
 
-        $mech->get_ok('/dashboard');
+            $mech->get_ok('/dashboard');
 
-        $res = $categories->scrape( $mech->content );
+            $res = $categories->scrape( $mech->content );
 
-        foreach my $row ( keys %{ $test->{counts} } ) {
-            check_row( $res, $row, $test->{counts}->{$row} );
-        }
+            foreach my $row ( keys %{ $test->{counts} } ) {
+                check_row( $res, $row, $test->{counts}->{$row} );
+            }
 
-        check_report_counts( $res, $test->{report_counts} );
+            check_report_counts( $res, $test->{report_counts} );
 
-        $mech->submit_form_ok( {
-            with_fields => {
-                category => $test->{category},
-                ward     => $test->{ward},
-            }
-        } );
+            $mech->submit_form_ok( {
+                with_fields => {
+                    category => $test->{category},
+                    ward     => $test->{ward},
+                }
+            } );
 
-        $res = $categories->scrape( $mech->content );
+            $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} );
-    };
-}
+            foreach my $row ( keys %{ $test->{counts_after} } ) {
+                check_row( $res, $row, $test->{counts_after}->{$row} );
+            }
+            check_report_counts( $res, $test->{report_counts_after} );
+        };
+    }
 
-delete_problems();
+    delete_problems();
 
-for my $test (
-    {
-        desc => 'Selecting no state does nothing',
-        p1 => {
-                state   => 'fixed - user',
-                conf_dt => DateTime->now(),
-                category => 'Potholes',
-        },
-        p2 => {
-                state   => 'confirmed',
-                conf_dt => DateTime->now(),
-                category => 'Litter',
+    for my $test (
+        {
+            desc => 'Selecting no state does nothing',
+            p1 => {
+                    state   => 'fixed - user',
+                    conf_dt => DateTime->now(),
+                    category => 'Potholes',
+            },
+            p2 => {
+                    state   => 'confirmed',
+                    conf_dt => DateTime->now(),
+                    category => 'Litter',
+            },
+            state => '',
+            report_counts => [2,0,0],
+            report_counts_after => [2,0,0],
         },
-        state => '',
-        report_counts => [2,0,0],
-        report_counts_after => [2,0,0],
-    },
-    {
-        desc => 'limit by state works',
-        state => 'fixed',
-        report_counts => [2,0,0],
-        report_counts_after => [1,0,0],
-    },
-    {
-        desc => 'planned counted as action scheduled',
-        p1 => {
-                state   => 'planned',
-                conf_dt => DateTime->now(),
-                category => 'Potholes',
+        {
+            desc => 'limit by state works',
+            state => 'fixed',
+            report_counts => [2,0,0],
+            report_counts_after => [1,0,0],
         },
-        state => 'action scheduled',
-        report_counts => [3,0,0],
-        report_counts_after => [1,0,0],
-    },
-    {
-        desc => 'All fixed states count as fixed',
-        p1 => {
-                state   => 'fixed - council',
-                conf_dt => DateTime->now(),
-                category => 'Potholes',
+        {
+            desc => 'planned counted as action scheduled',
+            p1 => {
+                    state   => 'planned',
+                    conf_dt => DateTime->now(),
+                    category => 'Potholes',
+            },
+            state => 'action scheduled',
+            report_counts => [3,0,0],
+            report_counts_after => [1,0,0],
         },
-        p2 => {
-                state   => 'fixed',
-                conf_dt => DateTime->now(),
-                category => 'Potholes',
+        {
+            desc => 'All fixed states count as fixed',
+            p1 => {
+                    state   => 'fixed - council',
+                    conf_dt => DateTime->now(),
+                    category => 'Potholes',
+            },
+            p2 => {
+                    state   => 'fixed',
+                    conf_dt => DateTime->now(),
+                    category => 'Potholes',
+            },
+            state => 'fixed',
+            report_counts => [5,0,0],
+            report_counts_after => [3,0,0],
         },
-        state => 'fixed',
-        report_counts => [5,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};
+    ) {
+        subtest $test->{desc} => sub {
+            make_problem( $test->{p1} ) if $test->{p1};
+            make_problem( $test->{p2} ) if $test->{p2};
 
-        $mech->get_ok('/dashboard');
+            $mech->get_ok('/dashboard');
 
-        $res = $categories->scrape( $mech->content );
+            $res = $categories->scrape( $mech->content );
 
-        check_report_counts( $res, $test->{report_counts} );
+            check_report_counts( $res, $test->{report_counts} );
 
-        $mech->submit_form_ok( {
-            with_fields => {
-                state => $test->{state},
-            }
-        } );
+            $mech->submit_form_ok( {
+                with_fields => {
+                    state => $test->{state},
+                }
+            } );
+
+            $res = $categories->scrape( $mech->content );
 
-        $res = $categories->scrape( $mech->content );
+            check_report_counts( $res, $test->{report_counts_after} );
+        };
+    }
 
-        check_report_counts( $res, $test->{report_counts_after} );
+    subtest 'export as csv' => sub {
+        $mech->get_ok('/dashboard?export=1');
+        my @lines = split /\n/, $mech->content;
+        is scalar @lines, 6, '1 (header) + 5 (reports) = 6 lines';
     };
-}
+};
+
 
 sub make_problem {
     my $args = shift;
diff --git a/templates/web/default/dashboard/index.html b/templates/web/default/dashboard/index.html
index f5a3cfba3..b3e1e8426 100644
--- a/templates/web/default/dashboard/index.html
+++ b/templates/web/default/dashboard/index.html
@@ -146,6 +146,7 @@
                 [% END %]
 </select>
 <input type="submit" value="Look up">
+<a class="export_as_csv" href="[% c.req.uri_with({ export => 1 }) %]">Export as CSV</a>
 
 <table width="100%" id="reports">
     <tr>
diff --git a/web/cobrands/fixmystreet/dashboard.scss b/web/cobrands/fixmystreet/dashboard.scss
index bd241617e..ca862aaa4 100644
--- a/web/cobrands/fixmystreet/dashboard.scss
+++ b/web/cobrands/fixmystreet/dashboard.scss
@@ -95,3 +95,8 @@
             font-size:2.25em;
         }
     }
+
+    .export_as_csv {
+        font-size: 0.75em;
+        font-weight: bold;
+    }