Admin: 4 revisions imported

2016-04-06T20:37:47Z

4 revisions imported

New page

= Spreadsheet Export - Displayed Fields =

Posted by Stephen Turner, MIT on Friday, June 10, 2005.

This mod allows a spreadsheet download of the fields displayed on the search results screen. The core RT spreadsheet download includes all ticket fields, irrespective of the fields shown on the screen.

There was a bug in an earlier version of this contrib. On our Linux RT installations, it worked fine. On Solaris, however, a string of end-of-line characters appeared in the spreadsheet before the real content. This meant you had to scroll down to see the content. See the two possible fixes below:

-- I tracked this down in 3.2 to a series of superfluous newlines in [[ColumnMap]]. Remove '''all''' blank lines in the file and the blank lines in the spreadsheet should disappear. (nick.humphries at pipex net)

-- Thanks Nick! I found something in this code that also fixes the problem. The m->comp() calls write their output to the output stream, and we aren't interested in the output here; we're only interested in the return values. So I added this argument to each comp() call:

{ store => \$mason_output }

This causes the output to be stored in the $mason_output variable and not to be written to the output stream. (Steve Turner)

In summary, the changes are:

* A new Mason component to format the results (/Search/[[ScreenResults]].tsv).
* Modification of /Elements/[[ScrubHTML]] to allow all HTML to be removed from a string.
* Modification of /Search/Results.html to add a link to the new download.

The diffs below are based on RT 3.4.2 versions.

=== /Search/ScreenResults.tsv (new component) ===

====Version 3.4.x====

This component uses the same data-retrieval mechanism as the search results process.

<nowiki><%INIT>

my $Tickets = RT::Tickets->new($session{'CurrentUser'});
$Tickets->FromSQL($ARGS{'Query'});

$Format ||= $RT::DefaultSearchResultFormat;
# Scrub the html of the format string to remove any potential nasties.
$Format = $m->comp('/Elements/ScrubHTML', Content => $Format, DenyAll => 1);
my (@Format) = $m->comp('/Elements/CollectionAsTable/ParseFormat', Format => $Format);

$r->content_type('application/vnd.ms-excel');

my @rows=();
my @header=();
my @cols=();

# used to store m->comp output - prevents the output from being
# written to the output stream. That's because we are not interested in the
# output for these comp() calls, we are interested in the return value.
my $mason_output;

foreach my $column (@Format) {
next if $column->{title} eq 'NEWLINE';

# Extract the column names from the Format array
foreach my $subcol ( @{ $column->{output} } ) {
if ( $subcol =~ /^__(.*?)__$/o ) {
my $col = $1;
push (@cols, $col);
}
}

# Determine the column titles
my $title = $column->{title};
$title =~ s/^__(.*)__$/$1/o;
my $ret = undef;
$ret =
$m->comp( { store => \$mason_output },
'/Elements/RT__Ticket/ColumnMap',
Name => $title,
Attr => 'title'
)
;
$title = $ret ? $ret : $title;
push @header, $title;
}

while ( my $Ticket = $Tickets->Next()) {
my $row;
foreach my $column (@cols) {
my $value = $m->comp({ store => \$mason_output },
'/Elements/RT__Ticket/ColumnMap',
Name => $column, Attr => 'value');
if ( $value && ref($value)) {
my @x = &{ $value }( $Ticket, 0 );
my $i=0;
$row->{ $column } = "";
foreach my $x (@x) {
$row->{ $column } .= ", " if $i > 0; # separating multivalues
if (ref ($x)) {
$row->{ $column } .= $$x;
} else {
$row->{ $column } .= $x;
}
$i++;
}
} else {
$row->{ $column } = $value ;
}
$row->{ $column } =~ s/, //g; # ColumnMap adds tags, which we don't want
}
push @rows, $row;
}

$m->out(join("\t", @header));
$m->out("\n");

foreach my $row (@rows) {
my @row;
foreach my $col(@cols) {
push @row, $row->{"$col"};
}
$m->out(join("\t",@row));
$m->out("\n");
}

$m->abort();
</%INIT>
<%ARGS>
$Query => undef
$Format => undef
$HideResults => 0
$Rows => 50
$Page => 1
$OrderBy => 'id'
$Order => 'ASC'
</%ARGS>
</nowiki>

==== Version 3.8.x ====

This is adapted from the core <code>Search/Results.tsv</code>, but instead of using the hard-coded set of fields, it uses the logic from the previous version of ScreenResults.tsv to parse the header format. The header and value formatting has been modified to use <code>ProcessColumnMapValue()</code> to take advantage of the column mapping already defined in RT.

<nowiki>
%# BEGIN BPS TAGGED BLOCK {{{
%#
%# COPYRIGHT:
%#
%# This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC
%# <jesse@bestpractical.com>
%#
%# (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>
$Format => undef
$Query => ''
$OrderBy => 'id'
$Order => 'ASC'
</%ARGS>
<%INIT>

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 );
}

# Use the user's displayed fields
$Format ||= RT->Config->Get('DefaultSearchResultFormat');
# Scrube the html of the format string to remove any potential nasties.
$Format = $m->comp('/Elements/ScrubHTML', Content => $Format, DenyAll => 1);
my (@Format) = $m->comp('/Elements/CollectionAsTable/ParseFormat', Format => $Format);

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
);

# HEADER: Map the fields from Format to proper column names

my @header = ();
my @cols = ();
foreach my $column (@Format)
{
next if (!defined ($column->{"attribute"}) || !$column->{"attribute"});
next if ($column->{"title"} && $column->{"title"} eq "NEWLINE");

# Extract the column names from the Format array
my $ret = $m->comp (
"/Elements/ColumnMap",
Class => "RT__Ticket",
Name => $column->{"attribute"},
Attr => "title",
);

my $colname = ProcessColumnMapValue ($ret, Arguments => [ $column->{"title"} ] );
push (@header, $colname);

$ret = $m->comp (
"/Elements/ColumnMap",
Class => "RT__Ticket",
Name => $column->{"attribute"},
Attr => "value",
);
push (@cols, $ret);
}

$r->content_type('application/vnd.ms-excel');
{
$m->out(join("\t", @header));
$m->out("\n");
$m->flush_buffer;
}

my $i = 0;
while ( my $Ticket = $Tickets->Next()) {
my @row;
foreach my $attr (@cols) {
push @row, ProcessColumnMapValue ($attr, Arguments => [ $Ticket, 0 ], Escape => 0);
}

# remove tabs from all field values, they screw up the tsv
for (@row) {
$_ = '' unless defined;
$_ =~ s/(?:\n|\r)//g;
$_ =~ s{\t}{ }g;
}

$m->out(join("\t",@row));
$m->out("\n");

unless (++$i%10) {
$i = 0;
$m->flush_buffer;
}
}

$m->abort();
</%INIT>
</nowiki>

=== Elements/ScrubHTML (modified) ===

Minor modification - diff -u output:

--- share/html/Elements/ScrubHTML Tue Feb 1 09:20:40 2005
+++ local/html/Elements/ScrubHTML Fri Jun 10 15:50:27 2005
@@ -62,10 +62,12 @@

$scrubber->deny(qw[*]);
$scrubber->allow(
- qw[A B U P BR I HR BR SMALL EM FONT SPAN DIV UL OL LI DL DT DD]);
+ qw[A B U P BR I HR BR SMALL EM FONT SPAN DIV UL OL LI DL DT DD])
+ unless $DenyAll;
$scrubber->comment(0);
return ( $scrubber->scrub($Content) );
</%init>
<%args>
$Content => undef
+$DenyAll => undef
</%args>

=== /Search/Results.html ===

Minor modification - diff -u output:

--- share/html/Search/Results.html Tue Feb 1 09:20:40 2005
+++ local/htmlSearch/Results.html Fri Jun 10 16:00:38 2005
@@ -79,7 +79,8 @@
<div align=right>
<a href="<%$RT::WebPath%>/Search/Bulk.html<%$QueryString%>"><&|/l&>Update multiple tickets</&></a> 
<a href="<%$RT::WebPath%>/Search/Results.html<%$QueryString%>"><&|/l&>Bookmarkable link</&></a> 
-<a href="<%$RT::WebPath%>/Search/Results.tsv<%$QueryString%>"><&|/l&>spreadsheet</&></a> |
+<a href="<%$RT::WebPath%>/Search/Results.tsv<%$QueryString%>"><&|/l&>spreadsheet (all ticket fields)</&></a> |
+<a href="<%$RT::WebPath%>/Search/ScreenResults.tsv<%$QueryString%>"><&|/l&>spreadsheet (displayed ticket fields)</&></a> |
<a href="<%$RT::WebPath%>/Search/Results.rdf<%$QueryString%>"><&|/l&>RSS</&></a> |
<a href="<%$RT::WebPath%>/Tools/Offline.html<%$QueryString%>"><&|/l&>Work offline</&></a> 
<& /Elements/Callback, _CallbackName => 'SearchActions', QueryString => $QueryString&>

Note: The default [[ColumnMap]] entries for date/time should be changed to ISO and not [[AsString]] for Excel to parse them correctly.

== User Notes ==

* Updated to work with v3.8.x by GinoLedesma
* This seems to indicate that it works well on 3.4.4 also: [[DuncanShannon]] 12/19/2005

http://archives.free.net.ph/message/20051019.135851.95ff58c9.en.html

* This seems to work fine on 3.4.5 as well.
* This only partially works on 3.6.x. The columns come out, but not in the correct order and the Scrub routines aren't working.

* Got it working with the following diff:

--- ScreenResults.tsv.orig Wed Jul 11 17:30:53 2007
+++ ScreenResults.tsv Wed Jul 11 17:29:57 2007
@@ -67,7 +67,8 @@
} else {
$row->{ $column } = $value ;
}
- $row->{ $column } =~ s/, //g; # ColumnMap adds tags, which we don't want
+ $row->{ $column } =~ s/, //g; # ColumnMap adds tags, which we don't want
+ $row->{ $column } =~ s/, (pending other Collection), <\/em>//g;
}
push @rows, $row;
}

SpreadsheetDisplayedFields - Revision history

Admin: 4 revisions imported