Excel date formatting, #12659

6 files changed, 49 insertions, 6 deletions

diff --git a/rt/FREESIDE_MODIFIED b/rt/FREESIDE_MODIFIED
index e10917447..1c303469a 100644
--- a/rt/FREESIDE_MODIFIED
+++ b/rt/FREESIDE_MODIFIED
@@ -131,6 +131,7 @@ share/html/RTx/Statistics/*
 share/html/Search/Results.xls
 share/html/Search/Results.csv
 share/html/Search/Elements/ResultViews
+share/html/Elements/ColumnMap
 
 lib/RT/Extension/MobileUI.pm
 share/html/Callbacks/RT-Extension-MobileUI/*
diff --git a/rt/share/html/Elements/ColumnMap b/rt/share/html/Elements/ColumnMap
index 8afe4a163..5e5354ade 100644
--- a/rt/share/html/Elements/ColumnMap
+++ b/rt/share/html/Elements/ColumnMap
@@ -64,6 +64,7 @@ my $COLUMN_MAP = {
     Created => {
         attribute => 'Created',
         title     => 'Created', # loc
+        date      => sub { return $_[0]->CreatedObj },
         value     => sub { return $_[0]->CreatedObj->AsString }
     },
     CreatedRelative => {
@@ -79,6 +80,7 @@ my $COLUMN_MAP = {
     LastUpdated => {
         attribute => 'LastUpdated',
         title     => 'Last Updated', # loc
+        date      => sub { return $_[0]->LastUpdatedObj },
         value     => sub { return $_[0]->LastUpdatedObj->AsString }
     },
     LastUpdatedRelative => {
diff --git a/rt/share/html/Elements/RT__Ticket/ColumnMap b/rt/share/html/Elements/RT__Ticket/ColumnMap
index 411b3e56b..cd38dcb7b 100644
--- a/rt/share/html/Elements/RT__Ticket/ColumnMap
+++ b/rt/share/html/Elements/RT__Ticket/ColumnMap
@@ -220,26 +220,31 @@ $COLUMN_MAP = {
     Starts => {
         title     => 'Starts', # loc
         attribute => 'Starts',
+        date      => sub { return $_[0]->StartsObj },
         value     => sub { return $_[0]->StartsObj->AsString }
     },
     Started => {
         title     => 'Started', # loc
         attribute => 'Started',
+        date      => sub { return $_[0]->StartedObj },
         value     => sub { return $_[0]->StartedObj->AsString }
     },
     Told => {
         title     => 'Told', # loc
         attribute => 'Told',
+        date      => sub { return $_[0]->ToldObj },
         value     => sub { return $_[0]->ToldObj->AsString }
     },
     Due => {
         title     => 'Due', # loc
         attribute => 'Due',
+        date      => sub { return $_[0]->DueObj },
         value     => sub { return $_[0]->DueObj->AsString }
     },
     Resolved => {
         title     => 'Resolved', # loc
         attribute => 'Resolved',
+        date      => sub { return $_[0]->ResolvedObj },
         value     => sub { return $_[0]->ResolvedObj->AsString }
     },
     UpdateStatus => {
diff --git a/rt/share/html/Search/Elements/ResultsStructuredView b/rt/share/html/Search/Elements/ResultsStructuredView
index cf6b2e821..495f0d0c8 100644
--- a/rt/share/html/Search/Elements/ResultsStructuredView
+++ b/rt/share/html/Search/Elements/ResultsStructuredView
@@ -50,9 +50,11 @@ $Query => undef
 $OrderBy => 'id'
 $Order => 'ASC'
 $Format => undef
+
 #Callbacks
 $WriteHeader => sub { $RT::Logger->error('WriteHeader callback required'); '' }
 $WriteRow    => sub { $RT::Logger->error('WriteRow callback required'); '' }
+$FormatDate  => sub { $_[0]->AsString }
 </%ARGS>
 <%INIT>
 
@@ -129,12 +131,23 @@ while ( my $Ticket = $Tickets->Next()) {
             }
 
             if ( !exists $ColumnMap->{$col}{'value'} ) {
-                $ColumnMap->{$col}{'value'} = $m->comp(
-                    '/Elements/ColumnMap',
-                    Class => 'RT__Ticket',
-                    Name  => $col,
-                    Attr  => 'value',
-                );
+                my $map = {};
+                foreach ('attribute', 'value', 'date') {
+                    $map->{$_} = $m->comp(
+                        '/Elements/ColumnMap',
+                        Class => 'RT__Ticket',
+                        Name  => $col,
+                        Attr  => $_,
+                    );
+                }
+                # Canonicalize dates
+                if ( defined $map->{'date'} ) {
+                    $map->{value} = sub { 
+                        my $DateObj = $map->{'date'}->(@_) or return undef;
+                        $FormatDate->($DateObj);
+                    };
+                }
+                $ColumnMap->{$col} = $map;
             }
 
             push @out, ProcessColumnMapValue(
diff --git a/rt/share/html/Search/Results.csv b/rt/share/html/Search/Results.csv
index bca757ae9..f9b4e97c3 100644
--- a/rt/share/html/Search/Results.csv
+++ b/rt/share/html/Search/Results.csv
@@ -81,6 +81,7 @@ $m->comp('Elements/ResultsStructuredView',
           Format        => $Format,
           WriteHeader   => $WriteHeader,
           WriteRow      => $WriteRow,
+          FormatDate    => sub { $_[0]->AsString(Format => 'ISO') },
 );
 
 $m->flush_buffer;
diff --git a/rt/share/html/Search/Results.xls b/rt/share/html/Search/Results.xls
index 8146376db..52a05daed 100644
--- a/rt/share/html/Search/Results.xls
+++ b/rt/share/html/Search/Results.xls
@@ -55,6 +55,7 @@ $Format => undef
 
 use Spreadsheet::WriteExcel;
 use List::Util qw( max );
+use Date::Format qw( time2str );
 
 $r->content_type('application/vnd.ms-excel');
 $r->header_out('Content-Disposition' => 'attachment;filename="Results.xls"');
@@ -69,6 +70,11 @@ my $default_format = $workbook->add_format(
     color   =>  'black',
     size    =>  10,
 );
+my $date_format = $workbook->add_format(
+    color   => 'black',
+    size    => 10,
+    num_format => 'YYYY-MM-DD', #configurable?
+);
 my $title_format = $workbook->add_format(
     color   => 'black',
     size    => 10,
@@ -97,12 +103,26 @@ my $WriteRow = sub {
         # record row/column sizes
         $height = max( $height, scalar(@lines) );
         $width[$item] = max( $width[$item], map {length} @lines );
+
+        if ( scalar(@lines) == 1 and 
+            $lines[0] =~ /^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}$/ ) {
+
+            $worksheet->write_date_time($row, $item, $lines[0], $date_format);
+            next;
+        }
+
         $worksheet->write($row, $item, join("\n", @lines), $default_format);
     }
     $worksheet->set_row($row, $height * 14);
     $row++;
 };
 
+my $FormatDate = sub {
+    my $DateObj = shift;
+    return '' if $DateObj->Unix == 0;
+    return time2str('%Y-%m-%dT%H:%M', $DateObj->Unix);
+};
+
 # Write everything to the worksheet
 $m->comp('Elements/ResultsStructuredView',
           Query         => $Query,
@@ -111,6 +131,7 @@ $m->comp('Elements/ResultsStructuredView',
           Format        => $Format,
           WriteHeader   => $WriteHeader,
           WriteRow      => $WriteRow,
+          FormatDate    => $FormatDate,
 );
 
 # Set column widths