From f3cd3aadcbc246ab457f7aea9d7326008a35b13e Mon Sep 17 00:00:00 2001 From: mark Date: Fri, 22 Apr 2011 02:38:00 +0000 Subject: [PATCH] improved XLS and CSV download, #10855 --- .../html/Search/Elements/ResultsStructuredView | 162 ++++++++++++++++++++ rt/share/html/Search/Results.csv | 165 +++++---------------- rt/share/html/Search/Results.xls | 150 +++++++------------ 3 files changed, 255 insertions(+), 222 deletions(-) create mode 100644 rt/share/html/Search/Elements/ResultsStructuredView diff --git a/rt/share/html/Search/Elements/ResultsStructuredView b/rt/share/html/Search/Elements/ResultsStructuredView new file mode 100644 index 000000000..88821ab9f --- /dev/null +++ b/rt/share/html/Search/Elements/ResultsStructuredView @@ -0,0 +1,162 @@ +%# BEGIN BPS TAGGED BLOCK {{{ +%# +%# COPYRIGHT: +%# +%# This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC +%# +%# +%# (Except where explicitly superseded by other copyright notices) +%# +%# +%# LICENSE: +%# +%# This work is made available to you under the terms of Version 2 of +%# the GNU General Public License. A copy of that license should have +%# been provided with this software, but in any event can be snarfed +%# from www.gnu.org. +%# +%# This work is distributed in the hope that it will be useful, but +%# WITHOUT ANY WARRANTY; without even the implied warranty of +%# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +%# General Public License for more details. +%# +%# You should have received a copy of the GNU General Public License +%# along with this program; if not, write to the Free Software +%# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA +%# 02110-1301 or visit their web page on the internet at +%# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html. +%# +%# +%# CONTRIBUTION SUBMISSION POLICY: +%# +%# (The following paragraph is not intended to limit the rights granted +%# to you to modify and distribute this software under the terms of +%# the GNU General Public License and is only of importance to you if +%# you choose to contribute your changes and enhancements to the +%# community by submitting them to Best Practical Solutions, LLC.) +%# +%# By intentionally submitting any modifications, corrections or +%# derivatives to this work, or any other work intended for use with +%# Request Tracker, to Best Practical Solutions, LLC, you confirm that +%# you are the copyright holder for those contributions and you grant +%# Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable, +%# royalty-free, perpetual, license to use, copy, create derivative +%# works based on those contributions, and sublicense and distribute +%# those contributions and any derivatives thereof. +%# +%# END BPS TAGGED BLOCK }}} +<%ARGS> +$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'); '' } + +<%INIT> + +use HTML::TreeBuilder; +use HTML::FormatText; + +my $Tickets = RT::Tickets->new( $session{'CurrentUser'} ); +$Tickets->FromSQL( $Query ); +if ( $OrderBy =~ /\|/ ) { + # Multiple Sorts + my @OrderBy = split /\|/, $OrderBy; + my @Order = split /\|/, $Order; + $Tickets->OrderByCols( + map { { FIELD => $OrderBy[$_], ORDER => $Order[$_] } } + ( 0 .. $#OrderBy ) + ); +} +else { + $Tickets->OrderBy( FIELD => $OrderBy, ORDER => $Order ); +} + +# Convert the format string to column info +$Format = $m->comp('/Elements/ScrubHTML', Content => $Format); +my @Format = $m->comp('/Elements/CollectionAsTable/ParseFormat', + Format => $Format); + +# Generate the header row +my $item = 0; +my @header; +foreach my $column (@Format) { +# see /Element/CollectionAsTable/Header + my $title = $column->{'title'} || ''; + if ( $title eq 'NEWLINE' ) { + # ignore these for now + undef $column; + next; + } + $title = '' if $title eq 'NBSP'; + + if ( !defined $column->{'title'} ) { + my $attr = $column->{'attribute'} || $column->{'last_attribute'}; + my $tmp = $m->comp( '/Elements/ColumnMap', + Class => 'RT__Ticket', + Name => $attr, + Attr => 'title' + ); + $title = ProcessColumnMapValue($tmp, Arguments => [ $attr ]); + } + + push @header, $title; + +} #foreach $column + +&{ $WriteHeader }(@header); + +my $plaintext = HTML::FormatText->new; +my $row = 1; +my $ColumnMap = {}; +while ( my $Ticket = $Tickets->Next()) { + my $height = 0; + my @row = (); + foreach my $column ( @Format ) { + next if !defined $column; + + my $value = ''; + my @out; + # Ignore almost all formatting here. + foreach my $subcol ( @{ $column->{output} } ) { + my ($col) = ($subcol =~ /^__(.*?)__$/ ); + + if ( !$col ) { + push @out, $subcol; + next; + } + + if ( !exists $ColumnMap->{$col}{'value'} ) { + $ColumnMap->{$col}{'value'} = $m->comp( + '/Elements/ColumnMap', + Class => 'RT__Ticket', + Name => $col, + Attr => 'value', + ); + } + + push @out, ProcessColumnMapValue( + $ColumnMap->{$col}{'value'}, + Arguments => [ $Ticket, $row ], + ); + use DDS; + } #foreach $subcol + warn Dump(\@out); + $value .= join('', @out); + # strip out all HTML except line breaks + my $tree = HTML::TreeBuilder->new->parse($value); + my $text = $plaintext->format($tree); + # along with leading/trailing whitespace + my @lines = map { s/^\s*//; s/\s*$//; $_ } split("\n", $text); + + # then provide separate lines to the callback + push @row, \@lines; + } #foreach $column + + &{ $WriteRow }(@row); + +} # while $Tickets->Next + + diff --git a/rt/share/html/Search/Results.csv b/rt/share/html/Search/Results.csv index 8551c9b80..bca757ae9 100644 --- a/rt/share/html/Search/Results.csv +++ b/rt/share/html/Search/Results.csv @@ -1,40 +1,40 @@ %# BEGIN BPS TAGGED BLOCK {{{ -%# +%# %# COPYRIGHT: -%# -%# This software is Copyright (c) 1996-2011 Best Practical Solutions, LLC -%# -%# +%# +%# This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC +%# +%# %# (Except where explicitly superseded by other copyright notices) -%# -%# +%# +%# %# LICENSE: -%# +%# %# This work is made available to you under the terms of Version 2 of %# the GNU General Public License. A copy of that license should have %# been provided with this software, but in any event can be snarfed %# from www.gnu.org. -%# +%# %# This work is distributed in the hope that it will be useful, but %# WITHOUT ANY WARRANTY; without even the implied warranty of %# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU %# General Public License for more details. -%# +%# %# You should have received a copy of the GNU General Public License %# along with this program; if not, write to the Free Software %# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA %# 02110-1301 or visit their web page on the internet at %# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html. -%# -%# +%# +%# %# CONTRIBUTION SUBMISSION POLICY: -%# +%# %# (The following paragraph is not intended to limit the rights granted %# to you to modify and distribute this software under the terms of %# the GNU General Public License and is only of importance to you if %# you choose to contribute your changes and enhancements to the %# community by submitting them to Best Practical Solutions, LLC.) -%# +%# %# By intentionally submitting any modifications, corrections or %# derivatives to this work, or any other work intended for use with %# Request Tracker, to Best Practical Solutions, LLC, you confirm that @@ -43,130 +43,47 @@ %# royalty-free, perpetual, license to use, copy, create derivative %# works based on those contributions, and sublicense and distribute %# those contributions and any derivatives thereof. -%# +%# %# END BPS TAGGED BLOCK }}} <%ARGS> -$Query => '' +$Query => undef $OrderBy => 'id' $Order => 'ASC' +$Format => undef <%INIT> -eval "use Text::CSV_XS"; -if ( $@ ) { - $m->comp('/Error', Why => "Error loading Text::CSV_XS.\n$@"); - $m->abort; - return; -} - -my $csv = Text::CSV_XS->new( { eol => "\n" } ); - -my $Tickets = RT::Tickets->new( $session{'CurrentUser'} ); -$Tickets->FromSQL( $Query ); -if ( $OrderBy =~ /\|/ ) { - # Multiple Sorts - my @OrderBy = split /\|/, $OrderBy; - my @Order = split /\|/, $Order; - $Tickets->OrderByCols( - map { { FIELD => $OrderBy[$_], ORDER => $Order[$_] } } - ( 0 .. $#OrderBy ) - ); -} -else { - $Tickets->OrderBy( FIELD => $OrderBy, ORDER => $Order ); -} - -my %cf_id_to_name; -my %cf_name_to_pos; -{ - my $cfs = RT::SQL::PossibleCustomFields( - Query => $Query, CurrentUser => $session{'CurrentUser'}, - ); - while ( my $cf = $cfs->Next ) { - my $name = $cf->Name; - $cf_id_to_name{ $cf->id } = $name; - next if $cf_name_to_pos{ $name }; - - $cf_name_to_pos{ $name } = - (sort { $b <=> $a } values %cf_name_to_pos)[0] + 1; - } -} - -my @attrs = qw( - id QueueObj->Name Subject Status - TimeEstimated TimeWorked TimeLeft - Priority FinalPriority - OwnerObj->Name - Requestors->MemberEmailAddressesAsString - Cc->MemberEmailAddressesAsString - AdminCc->MemberEmailAddressesAsString - DueObj->ISO ToldObj->ISO CreatedObj->ISO - ResolvedObj->ISO LastUpdatedObj->ISO LastUpdatedByObj->Name -); +use Text::CSV_XS; #does anyone not have this? $r->content_type('text/csv'); $r->header_out('Content-Disposition' => 'attachment;filename="Results.csv"'); -{ - my @header; - foreach my $attr (@attrs) { - my $label = $attr; - $label =~ s'Obj-.(?:AsString|Name|ISO)''g; - $label =~ s'-\>MemberEmailAddressesAsString''g; - push @header, $label; - } - $_ += @header - 1 foreach values %cf_name_to_pos; +my $csv = Text::CSV_XS->new( { eol => "\n" } ); - foreach my $name ( sort { $cf_name_to_pos{$a} <=> $cf_name_to_pos{$b} } keys %cf_name_to_pos ) { - push @header, "CF-". $name; - } +my $WriteHeader = sub { + my @header = @_; $csv->combine(@header); - $m->out($csv->string()); - $m->flush_buffer; -} - -my $i = 0; -while ( my $Ticket = $Tickets->Next()) { - my @row; - foreach my $attr (@attrs) { - my $value; - if ($attr =~ /(.*)->ISO$/ and $Ticket->$1->Unix <= 0) { - $value = ''; - } else { - my $method = '$Ticket->'.$attr.'()'; - $method =~ s/->ISO\(\)$/->ISO( Timezone => 'user' )/; - $value = eval $method; - if ($@) {die "Failed to find $attr - ". $@}; - } - push @row, $value; - } - - my $values = $Ticket->CustomFieldValues; - $values->OrderByCols; # don't sort them - while (my $value = $values->Next) { - my $pos = $cf_name_to_pos{ $cf_id_to_name{ $value->CustomField } }; - next unless $pos; - - $row[$pos] = '' unless defined $row[$pos]; - $row[$pos] .= ', ' if $row[$pos]; - $row[$pos] .= $value->Content; - } - - # remove tabs from all field values, they screw up the tsv - for (@row) { - $_ = '' unless defined; - $_ =~ s/(?:\n|\r)//g; - $_ =~ s{\t}{ }g; - } + $m->out($csv->string); +}; - $csv->combine(@row); - $m->out($csv->string()); - - unless (++$i%10) { - $i = 0; - $m->flush_buffer; - } -} +my $WriteRow = sub { + my @row = @_; + $csv->combine( + map { join('; ', @$_) } @row + ); + $m->out($csv->string); +}; + +$m->comp('Elements/ResultsStructuredView', + Query => $Query, + OrderBy => $OrderBy, + Order => $Order, + Format => $Format, + WriteHeader => $WriteHeader, + WriteRow => $WriteRow, +); +$m->flush_buffer; $m->abort(); + diff --git a/rt/share/html/Search/Results.xls b/rt/share/html/Search/Results.xls index 8b3f11b64..8146376db 100644 --- a/rt/share/html/Search/Results.xls +++ b/rt/share/html/Search/Results.xls @@ -46,128 +46,82 @@ %# %# END BPS TAGGED BLOCK }}} <%ARGS> -$Query => '' +$Query => undef $OrderBy => 'id' $Order => 'ASC' +$Format => undef <%INIT> use Spreadsheet::WriteExcel; +use List::Util qw( max ); + +$r->content_type('application/vnd.ms-excel'); +$r->header_out('Content-Disposition' => 'attachment;filename="Results.xls"'); + my $xls; my $fh; open ($fh, ">", \$xls) or die "$!"; my $workbook = Spreadsheet::WriteExcel->new($fh) or die $!; my $worksheet = $workbook->add_worksheet(); -my $Tickets = RT::Tickets->new( $session{'CurrentUser'} ); -$Tickets->FromSQL( $Query ); -if ( $OrderBy =~ /\|/ ) { - # Multiple Sorts - my @OrderBy = split /\|/, $OrderBy; - my @Order = split /\|/, $Order; - $Tickets->OrderByCols( - map { { FIELD => $OrderBy[$_], ORDER => $Order[$_] } } - ( 0 .. $#OrderBy ) - ); -} -else { - $Tickets->OrderBy( FIELD => $OrderBy, ORDER => $Order ); -} - -my %cf_id_to_name; -my %cf_name_to_pos; -{ - my $cfs = RT::SQL::PossibleCustomFields( - Query => $Query, CurrentUser => $session{'CurrentUser'}, - ); - while ( my $cf = $cfs->Next ) { - my $name = $cf->Name; - $cf_id_to_name{ $cf->id } = $name; - next if $cf_name_to_pos{ $name }; - - $cf_name_to_pos{ $name } = - (sort { $b <=> $a } values %cf_name_to_pos)[0] + 1; - } -} - -my @attrs = qw( - id QueueObj->Name Subject Status - TimeEstimated TimeWorked TimeLeft - Priority FinalPriority - OwnerObj->Name - Requestors->MemberEmailAddressesAsString - Cc->MemberEmailAddressesAsString - AdminCc->MemberEmailAddressesAsString - DueObj->ISO ToldObj->ISO CreatedObj->ISO - ResolvedObj->ISO LastUpdatedObj->ISO +my $default_format = $workbook->add_format( + color => 'black', + size => 10, +); +my $title_format = $workbook->add_format( + color => 'black', + size => 10, + bold => 1, + bg_color=> 'silver', ); -$r->content_type('application/vnd.ms-excel'); -$r->header_out('Content-Disposition' => 'attachment;filename="Results.xls"'); -{ - my @header; - foreach my $attr (@attrs) { - my $label = $attr; - $label =~ s'Obj-.(?:AsString|Name|ISO)''g; - $label =~ s'-\>MemberEmailAddressesAsString''g; - push @header, $label; - } - - $_ += @header - 1 foreach values %cf_name_to_pos; - - foreach my $name ( sort { $cf_name_to_pos{$a} <=> $cf_name_to_pos{$b} } keys %cf_name_to_pos ) { - push @header, "CF-". $name; - } - my $ws_col = 0; - foreach my $ws_val ( @header ) { - $worksheet->write(0, $ws_col, $ws_val); - $ws_col++; - } -} +my @width; +my $row = 1; -my $i = 0; -my $ws_row = 1; -while ( my $Ticket = $Tickets->Next()) { - my @row; - foreach my $attr (@attrs) { - my $value; - if ($attr =~ /(.*)->ISO$/ and $Ticket->$1->Unix <= 0) { - $value = ''; - } else { - my $method = '$Ticket->'.$attr.'()'; - $method =~ s/->ISO\(\)$/->ISO( Timezone => 'user' )/; - $value = eval $method; - if ($@) {die "Failed to find $attr - ". $@}; - } - push @row, $value; +my $WriteHeader = sub { + my @header = @_; + @width = (); + for ( my $item = 0; $item < scalar @header; $item++ ) { + my $title = $header[$item]; + $worksheet->write(0, $item, $title, $title_format); + $width[$item] = 1.1 * length($title); } +}; - my $values = $Ticket->CustomFieldValues; - $values->OrderByCols; # don't sort them - while (my $value = $values->Next) { - my $pos = $cf_name_to_pos{ $cf_id_to_name{ $value->CustomField } }; - next unless $pos; - - $row[$pos] = '' unless defined $row[$pos]; - $row[$pos] .= ', ' if $row[$pos]; - $row[$pos] .= $value->Content; +my $WriteRow = sub { + my @row = @_; + my $height = 0; + for ( my $item = 0; $item < scalar @row; $item++ ) { + my @lines = @{ $row[$item] }; + # record row/column sizes + $height = max( $height, scalar(@lines) ); + $width[$item] = max( $width[$item], map {length} @lines ); + $worksheet->write($row, $item, join("\n", @lines), $default_format); } + $worksheet->set_row($row, $height * 14); + $row++; +}; - my $ws_col = 0; - foreach my $ws_val ( @row ) { - $worksheet->write($ws_row, $ws_col, $ws_val); - $ws_col++; - } - $ws_row++; +# Write everything to the worksheet +$m->comp('Elements/ResultsStructuredView', + Query => $Query, + OrderBy => $OrderBy, + Order => $Order, + Format => $Format, + WriteHeader => $WriteHeader, + WriteRow => $WriteRow, +); - unless (++$i%10) { - $i = 0; - $m->flush_buffer; - } +# Set column widths +for( my $item = 0; $item < scalar @width; $item++ ) { + $worksheet->set_column($item, $item, $width[$item]); } +# and then write everything out. $workbook->close; close($fh); $m->print($xls); $m->abort(); + -- 2.11.0