[fixmystreet.com] CSV download from marketing page

Limited to most recent 100 rows and not full data.

Factor the two CSV generations together.

3 files changed, 222 insertions, 105 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Dashboard.pm b/perllib/FixMyStreet/App/Controller/Dashboard.pm
index e1183c247..90f3866ec 100644
--- a/perllib/FixMyStreet/App/Controller/Dashboard.pm
+++ b/perllib/FixMyStreet/App/Controller/Dashboard.pm
@@ -5,6 +5,7 @@ use namespace::autoclean;
 use DateTime;
 use JSON::MaybeXS;
 use Path::Tiny;
+use Text::CSV;
 use Time::Piece;
 
 BEGIN { extends 'Catalyst::Controller'; }
@@ -112,7 +113,7 @@ sub index : Path : Args(0) {
     $c->forward('construct_rs_filter');
 
     if ( $c->get_param('export') ) {
-        $self->export_as_csv($c);
+        $c->forward('export_as_csv');
     } else {
         $self->generate_data($c);
     }
@@ -294,28 +295,15 @@ sub generate_body_response_time : Private {
     $c->stash->{body_average} = $avg ? int($avg / 60 / 60 / 24 + 0.5) : 0;
 }
 
-sub export_as_csv {
+sub export_as_csv : Private {
     my ($self, $c) = @_;
-    require Text::CSV;
-    my $problems = $c->stash->{problems_rs}->search(
-        {}, { prefetch => 'comments', order_by => 'me.confirmed' });
-
-    my $filename = do {
-        my %where = (
-            category => $c->stash->{category},
-            state    => $c->stash->{q_state},
-            ward     => $c->stash->{ward},
-        );
-        $where{body} = $c->stash->{body}->id if $c->stash->{body};
-        join '-',
-            $c->req->uri->host,
-            map {
-                my $value = $where{$_};
-                (defined $value and length $value) ? ($_, $value) : ()
-            } sort keys %where };
 
-    my $csv = Text::CSV->new({ binary => 1, eol => "\n" });
-    $csv->combine(
+    my $csv = $c->stash->{csv} = {
+        problems => $c->stash->{problems_rs}->search_rs({}, {
+            prefetch => 'comments',
+            order_by => 'me.confirmed'
+        }),
+        headers => [
             'Report ID',
             'Title',
             'Detail',
@@ -333,68 +321,116 @@ sub export_as_csv {
             'Easting',
             'Northing',
             'Report URL',
+        ],
+        columns => [
+            'id',
+            'title',
+            'detail',
+            'user_name_display',
+            'category',
+            'created',
+            'confirmed',
+            'acknowledged',
+            'fixed',
+            'closed',
+            'state',
+            'latitude', 'longitude',
+            'postcode',
+            'wards',
+            'local_coords_x',
+            'local_coords_y',
+            'url',
+        ],
+        filename => do {
+            my %where = (
+                category => $c->stash->{category},
+                state    => $c->stash->{q_state},
+                ward     => $c->stash->{ward},
             );
+            $where{body} = $c->stash->{body}->id if $c->stash->{body};
+            join '-',
+                $c->req->uri->host,
+                map {
+                    my $value = $where{$_};
+                    (defined $value and length $value) ? ($_, $value) : ()
+                } sort keys %where
+        },
+    };
+    $c->forward('generate_csv');
+}
+
+=head2 generate_csv
+
+Generates a CSV output, given a 'csv' stash hashref containing:
+* filename: filename to be used in output
+* problems: a resultset of the rows to output
+* headers: an arrayref of the header row strings
+* columns: an arrayref of the columns (looked up in the row's as_hashref, plus
+the following: user_name_display, acknowledged, fixed, closed, wards,
+local_coords_x, local_coords_y, url).
+
+=cut
+
+sub generate_csv : Private {
+    my ($self, $c) = @_;
+
+    my $csv = Text::CSV->new({ binary => 1, eol => "\n" });
+    $csv->combine(@{$c->stash->{csv}->{headers}});
     my @body = ($csv->string);
 
     my $fixed_states = FixMyStreet::DB::Result::Problem->fixed_states;
     my $closed_states = FixMyStreet::DB::Result::Problem->closed_states;
 
+    my $wards = 0;
+    my $comments = 0;
+    foreach (@{$c->stash->{csv}->{columns}}) {
+        $wards = 1 if $_ eq 'wards';
+        $comments = 1 if $_ eq 'acknowledged';
+    }
+
+    my $problems = $c->stash->{csv}->{problems};
     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 unless $comment->state eq 'confirmed';
-            next if $problem_state eq 'confirmed';
-            $hashref->{acknowledged} //= $comment->confirmed;
-            $hashref->{fixed} //= $fixed_states->{ $problem_state } || $comment->mark_fixed ?
-                $comment->confirmed : undef;
-            if ($closed_states->{ $problem_state }) {
-                $hashref->{closed} = $comment->confirmed;
-                last;
+        $hashref->{user_name_display} = $report->anonymous
+            ? '(anonymous)' : $report->user->name;
+
+        if ($comments) {
+            for my $comment ($report->comments) {
+                my $problem_state = $comment->problem_state or next;
+                next unless $comment->state eq 'confirmed';
+                next if $problem_state eq 'confirmed';
+                $hashref->{acknowledged} //= $comment->confirmed;
+                $hashref->{fixed} //= $fixed_states->{ $problem_state } || $comment->mark_fixed ?
+                    $comment->confirmed : undef;
+                if ($closed_states->{ $problem_state }) {
+                    $hashref->{closed} = $comment->confirmed;
+                    last;
+                }
             }
         }
 
-        my $wards = join ', ',
-          map { $c->stash->{children}->{$_}->{name} }
-          grep {$c->stash->{children}->{$_} }
-          split ',', $hashref->{areas};
+        if ($wards) {
+            $hashref->{wards} = join ', ',
+              map { $c->stash->{children}->{$_}->{name} }
+              grep {$c->stash->{children}->{$_} }
+              split ',', $hashref->{areas};
+        }
 
-        my @local_coords = $report->local_coords;
+        ($hashref->{local_coords_x}, $hashref->{local_coords_y}) =
+            $report->local_coords;
+        $hashref->{url} = join '', $c->cobrand->base_url_for_report($report), $report->url;
 
         $csv->combine(
             @{$hashref}{
-                'id',
-                'title',
-                'detail',
-                'user_name_display',
-                'category',
-                'created',
-                'confirmed',
-                'acknowledged',
-                'fixed',
-                'closed',
-                'state',
-                'latitude', 'longitude',
-                'postcode',
-                },
-            $wards,
-            $local_coords[0],
-            $local_coords[1],
-            (join '', $c->cobrand->base_url_for_report($report), $report->url),
+                @{$c->stash->{csv}->{columns}}
+            },
         );
 
         push @body, $csv->string;
     }
+
+    my $filename = $c->stash->{csv}->{filename};
     $c->res->content_type('text/csv; charset=utf-8');
     $c->res->header('content-disposition' => "attachment; filename=${filename}.csv");
     $c->res->body( join "", @body );
diff --git a/perllib/FixMyStreet/App/Controller/Reports.pm b/perllib/FixMyStreet/App/Controller/Reports.pm
index 724fc7fbd..ec7a192b3 100644
--- a/perllib/FixMyStreet/App/Controller/Reports.pm
+++ b/perllib/FixMyStreet/App/Controller/Reports.pm
@@ -468,12 +468,51 @@ sub summary : Private {
 
     $c->forward('/dashboard/construct_rs_filter');
 
+    if ( $c->get_param('csv') ) {
+        $c->detach('export_summary_csv');
+    }
+
     $c->forward('/dashboard/generate_grouped_data');
     $c->forward('/dashboard/generate_body_response_time');
 
     $c->stash->{template} = 'reports/summary.html';
 }
 
+sub export_summary_csv : Private {
+    my ( $self, $c ) = @_;
+
+    $c->stash->{csv} = {
+        problems => $c->stash->{problems_rs}->search_rs({}, {
+            rows => 100,
+            order_by => { '-desc' => 'me.confirmed' },
+        }),
+        headers => [
+            'Report ID',
+            'Title',
+            'Category',
+            'Created',
+            'Confirmed',
+            'Status',
+            'Latitude', 'Longitude',
+            'Query',
+            'Report URL',
+        ],
+        columns => [
+            'id',
+            'title',
+            'category',
+            'created_pp',
+            'confirmed_pp',
+            'state',
+            'latitude', 'longitude',
+            'postcode',
+            'url',
+        ],
+        filename => 'fixmystreet-data.csv',
+    };
+    $c->forward('/dashboard/generate_csv');
+}
+
 =head2 check_canonical_url
 
 Given an already found (case-insensitively) body, check what URL
diff --git a/t/cobrand/fixmystreet.t b/t/cobrand/fixmystreet.t
index b48593593..30d5765a2 100644
--- a/t/cobrand/fixmystreet.t
+++ b/t/cobrand/fixmystreet.t
@@ -23,48 +23,90 @@ FixMyStreet::override_config {
     TEST_DASHBOARD_DATA => $data,
     ALLOWED_COBRANDS => 'fixmystreet',
 }, sub {
-    # Not logged in, redirected
-    $mech->get_ok('/reports/Birmingham/summary');
-    is $mech->uri->path, '/about/council-dashboard';
-
-    $mech->submit_form_ok({ with_fields => { username => 'someone@somewhere.example.org' }});
-    $mech->content_contains('We will be in touch');
-    # XXX Check email arrives
-
-    $mech->log_in_ok('someone@somewhere.example.org');
-    $mech->get_ok('/reports/Birmingham/summary');
-    is $mech->uri->path, '/about/council-dashboard';
-    $mech->content_contains('Ending in .gov.uk');
-
-    $mech->submit_form_ok({ with_fields => { name => 'Someone', username => 'someone@birmingham.gov.uk' }});
-    $mech->content_contains('Now check your email');
-    # XXX Check email arrives, click link
-
-    $mech->log_in_ok('someone@birmingham.gov.uk');
-    # Logged in, redirects
-    $mech->get_ok('/about/council-dashboard');
-    is $mech->uri->path, '/reports/Birmingham/summary';
-    $mech->content_contains('Where we send Birmingham');
-    $mech->content_contains('lights@example.com');
-
-    $body->send_method('Open311');
-    $body->update();
-    $mech->get_ok('/about/council-dashboard');
-    $mech->content_contains('Reports to Birmingham are currently sent directly');
-
-    $body->send_method('Refused');
-    $body->update();
-    $mech->get_ok('/about/council-dashboard');
-    $mech->content_contains('Birmingham currently does not accept');
-
-    $body->send_method('Noop');
-    $body->update();
-    $mech->get_ok('/about/council-dashboard');
-    $mech->content_contains('Reports are currently not being sent');
-
-    $mech->log_out_ok();
-    $mech->get_ok('/reports');
-    $mech->content_lacks('Where we send Birmingham');
+    subtest 'check marketing dashboard access' => sub {
+        # Not logged in, redirected
+        $mech->get_ok('/reports/Birmingham/summary');
+        is $mech->uri->path, '/about/council-dashboard';
+
+        $mech->submit_form_ok({ with_fields => { username => 'someone@somewhere.example.org' }});
+        $mech->content_contains('We will be in touch');
+        # XXX Check email arrives
+
+        $mech->log_in_ok('someone@somewhere.example.org');
+        $mech->get_ok('/reports/Birmingham/summary');
+        is $mech->uri->path, '/about/council-dashboard';
+        $mech->content_contains('Ending in .gov.uk');
+
+        $mech->submit_form_ok({ with_fields => { name => 'Someone', username => 'someone@birmingham.gov.uk' }});
+        $mech->content_contains('Now check your email');
+        # XXX Check email arrives, click link
+
+        $mech->log_in_ok('someone@birmingham.gov.uk');
+        # Logged in, redirects
+        $mech->get_ok('/about/council-dashboard');
+        is $mech->uri->path, '/reports/Birmingham/summary';
+        $mech->content_contains('Where we send Birmingham');
+        $mech->content_contains('lights@example.com');
+    };
+
+    subtest 'check marketing dashboard csv' => sub {
+        $mech->log_in_ok('someone@birmingham.gov.uk');
+        $mech->create_problems_for_body(105, $body->id, 'Title', {
+            detail => "this report\nis split across\nseveral lines",
+            areas => ",2514,",
+        });
+
+        $mech->get_ok('/reports/Birmingham/summary?csv=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, 101, '1 (header) + 100 (reports) = 101 lines';
+
+        is scalar @{$rows[0]}, 10, '10 columns present';
+
+        is_deeply $rows[0],
+            [
+                'Report ID',
+                'Title',
+                'Category',
+                'Created',
+                'Confirmed',
+                'Status',
+                'Latitude',
+                'Longitude',
+                'Query',
+                'Report URL',
+            ],
+            'Column headers look correct';
+
+        my $body_id = $body->id;
+        like $rows[1]->[1], qr/Title Test \d+ for $body_id/, 'problem title correct';
+    };
+
+    subtest 'check marketing dashboard contact listings' => sub {
+        $mech->log_in_ok('someone@birmingham.gov.uk');
+        $body->send_method('Open311');
+        $body->update();
+        $mech->get_ok('/about/council-dashboard');
+        $mech->content_contains('Reports to Birmingham are currently sent directly');
+
+        $body->send_method('Refused');
+        $body->update();
+        $mech->get_ok('/about/council-dashboard');
+        $mech->content_contains('Birmingham currently does not accept');
+
+        $body->send_method('Noop');
+        $body->update();
+        $mech->get_ok('/about/council-dashboard');
+        $mech->content_contains('Reports are currently not being sent');
+
+        $mech->log_out_ok();
+        $mech->get_ok('/reports');
+        $mech->content_lacks('Where we send Birmingham');
+    };
 };
 
 END {