From 88b2d48a63e815be9c36c8f611f453400f603c2b Mon Sep 17 00:00:00 2001 From: mark Date: Sat, 30 Apr 2011 20:28:54 +0000 Subject: [PATCH] Excel date formatting, #12659 --- rt/FREESIDE_MODIFIED | 1 + rt/share/html/Elements/ColumnMap | 2 ++ rt/share/html/Elements/RT__Ticket/ColumnMap | 5 +++++ .../html/Search/Elements/ResultsStructuredView | 25 ++++++++++++++++------ rt/share/html/Search/Results.csv | 1 + rt/share/html/Search/Results.xls | 21 ++++++++++++++++++ 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 } <%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 -- 2.11.0