From: mark Date: Fri, 15 Apr 2011 21:08:43 +0000 (+0000) Subject: RT search results in Excel format, #10855 X-Git-Tag: freeside_2_3_0~385 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=67ea829b418b1c7a97a51793ed920dba6ff36393 RT search results in Excel format, #10855 --- diff --git a/rt/FREESIDE_MODIFIED b/rt/FREESIDE_MODIFIED index 6bef539be..5ab5b1d74 100644 --- a/rt/FREESIDE_MODIFIED +++ b/rt/FREESIDE_MODIFIED @@ -126,3 +126,6 @@ share/html/Callbacks/SearchCustomerFields/* share/html/Callbacks/RTx-Statistics/* share/html/RTx/Statistics/* +share/html/Callbacks/Results-XLS/* +share/html/Search/Results.xls +lib/RT/Extension/SearchResults/XLS.pm diff --git a/rt/lib/RT/Extension/SearchResults/XLS.pm b/rt/lib/RT/Extension/SearchResults/XLS.pm new file mode 100644 index 000000000..b5d242b97 --- /dev/null +++ b/rt/lib/RT/Extension/SearchResults/XLS.pm @@ -0,0 +1,82 @@ +package RT::Extension::SearchResults::XLS; + +use warnings; +use strict; + +=head1 NAME + +RT::Extension::SearchResults::XLS - Add Excel format export to RT search results + +=head1 VERSION + +Version 0.06 + +=cut + +our $VERSION = '0.06'; + + +=head1 SYNOPSIS + +This RT Extension allow users to download search results in Microsoft Excel +binary format. This typically fix encoding problems for non-ascii chars with +the standard TSV export included in RT. + +=head1 AUTHOR + +Emmanuel Lacour, C<< >> + +=head1 BUGS + +Please report any bugs or feature requests to C, or through +the web interface at L. I will be notified, and then you'll +automatically be notified of progress on your bug as I make changes. + + + + +=head1 SUPPORT + +You can find documentation for this module with the perldoc command. + + perldoc RT::Extension::SearchResults::XLS + + +You can also look for information at: + +=over 4 + +=item * RT: CPAN's request tracker + +L + +=item * AnnoCPAN: Annotated CPAN documentation + +L + +=item * CPAN Ratings + +L + +=item * Search CPAN + +L + +=back + + +=head1 ACKNOWLEDGEMENTS + + +=head1 COPYRIGHT & LICENSE + +Copyright 2008-2010 Emmanuel Lacour, all rights reserved. + +This program is free software; you can redistribute it and/or modify it +under the same terms as Perl itself. + +Request Tracker (RT) is Copyright Best Practical Solutions, LLC. + +=cut + +1; # End of RT::Extension::SearchResults::XLS diff --git a/rt/share/html/Callbacks/Results-XLS/Search/Elements/ResultViews/AfterTools b/rt/share/html/Callbacks/Results-XLS/Search/Elements/ResultViews/AfterTools new file mode 100644 index 000000000..c84e6602d --- /dev/null +++ b/rt/share/html/Callbacks/Results-XLS/Search/Elements/ResultViews/AfterTools @@ -0,0 +1,4 @@ +
  • <&|/l&>XLS
    • +<%ARGS> +$QueryString => undef + diff --git a/rt/share/html/Search/Results.xls b/rt/share/html/Search/Results.xls new file mode 100644 index 000000000..8b3f11b64 --- /dev/null +++ b/rt/share/html/Search/Results.xls @@ -0,0 +1,173 @@ +%# 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 => '' +$OrderBy => 'id' +$Order => 'ASC' + +<%INIT> + +use Spreadsheet::WriteExcel; +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 +); + +$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 $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 $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 $ws_col = 0; + foreach my $ws_val ( @row ) { + $worksheet->write($ws_row, $ws_col, $ws_val); + $ws_col++; + } + $ws_row++; + + unless (++$i%10) { + $i = 0; + $m->flush_buffer; + } +} + +$workbook->close; +close($fh); +$m->print($xls); +$m->abort(); +