[Zurich] add columns to stats export

    -       Original Report description
    -       Amended Report description (unsure what this is)
    -       Media URL (currently one picture, in the future 1-3 pictures)
    -       Interface used (see open311)
    -       Requested datetime (see open311) (seems to be same as
            created)
    -       Agency sent datetime (see open311)
    -       Updated datetime (see open311)
    -       Council-Response
    -       title

as per https://github.com/mysociety/FixMyStreet-Commercial/issues/678

4 files changed, 44 insertions, 11 deletions

diff --git a/perllib/FixMyStreet/App/Controller/Open311.pm b/perllib/FixMyStreet/App/Controller/Open311.pm
index b0052291b..64b21db6b 100644
--- a/perllib/FixMyStreet/App/Controller/Open311.pm
+++ b/perllib/FixMyStreet/App/Controller/Open311.pm
@@ -257,8 +257,7 @@ sub output_requests : Private {
                 $problem->get_extra_metadata('public_response')
             ];
         }
-
-        if ( $c->cobrand->moniker ne 'zurich' ) { # XXX
+        else {
             # FIXME Not according to Open311 v2
             $request->{agency_responsible} = $problem->bodies;
         }
diff --git a/perllib/FixMyStreet/Cobrand/Zurich.pm b/perllib/FixMyStreet/Cobrand/Zurich.pm
index 8d7424328..100349efb 100644
--- a/perllib/FixMyStreet/Cobrand/Zurich.pm
+++ b/perllib/FixMyStreet/Cobrand/Zurich.pm
@@ -735,25 +735,55 @@ sub admin_stats {
                     'latitude', 'longitude',
                     'cobrand',  'category',
                     'state',    'user_id',
-                    'external_body'
-                ]
+                    'external_body',
+                    'title', 'detail',
+                    'photo',
+                    'whensent', 'lastupdate',
+                    'service',
+                    'extra',
+                ],
             }
         );
-        my $body = "ID,Created,E,N,Category,Status,UserID,External Body\n";
+        my $body = "Report ID,Created,Sent to Agency,Last Updated,E,N,Category,Status,UserID,External Body,Title,Detail,Media URL,Interface Used,Council Response\n";
+        require Text::CSV;
+        my $csv = Text::CSV->new({ binary => 1 });
         while ( my $report = $problems->next ) {
             my $external_body;
             my $body_name = "";
             if ( $external_body = $report->body($c) ) {
-                $body_name = $external_body->name;
+                $body_name = $external_body->name || '[Unknown body]';
             }
-            $body .= join( ',',
-                $report->id,           $report->created,
+
+            my $detail = $report->detail;
+            my $public_response = $report->get_extra_metadata('public_response') || '';
+
+            # replace newlines with HTML <br/> element
+            $detail =~ s{\r?\n}{ <br/> }g;
+            $public_response =~ s{\r?\n}{ <br/> }g;
+
+            my @columns = (
+                $report->id,
+                $report->created,
+                $report->whensent,
+                $report->lastupdate,
                 $report->local_coords, $report->category,
                 $report->state,        $report->user_id,
-                "\"$body_name\"" )
-              . "\n";
+                $body_name,
+                $report->title,
+                $detail,
+                $c->cobrand->base_url . $report->get_photo_params->{url},
+                $report->service || 'Web interface',
+                $public_response,
+            );
+            if ($csv->combine(@columns)) {
+                $body .= $csv->string . "\n";
+            }
+            else {
+                $body .= sprintf "{{error emitting CSV line: %s}}\n", $csv->error_diag;
+            }
         }
         $c->res->content_type('text/csv; charset=utf-8');
+        $c->res->header('Content-Disposition' => 'attachment; filename=stats.csv');
         $c->res->body($body);
     }
 
diff --git a/perllib/FixMyStreet/DB/Result/Problem.pm b/perllib/FixMyStreet/DB/Result/Problem.pm
index 93dc16811..bfe87009d 100644
--- a/perllib/FixMyStreet/DB/Result/Problem.pm
+++ b/perllib/FixMyStreet/DB/Result/Problem.pm
@@ -681,6 +681,10 @@ sub local_coords {
         my ($x, $y) = Geo::Coordinates::CH1903::from_latlon($self->latitude, $self->longitude);
         return ( int($x+0.5), int($y+0.5) );
     }
+    else {
+        # return a dummy value until this function is implemented.  useful for testing.
+        return (0, 0);
+    }
 }
 
 =head2 update_from_open311_service_request
diff --git a/t/cobrand/zurich.t b/t/cobrand/zurich.t
index 31aceab28..90a92fb44 100644
--- a/t/cobrand/zurich.t
+++ b/t/cobrand/zurich.t
@@ -651,7 +651,7 @@ subtest "test stats" => sub {
         my $export_count = get_export_rows_count($mech);
         if (defined $export_count) {
             is $export_count - $EXISTING_REPORT_COUNT, 3, 'Correct number of reports';
-            $mech->content_contains(',fixed - council,');
+            $mech->content_contains('fixed - council');
             $mech->content_contains(',hidden,');
         }