--- /dev/null
+#!/usr/bin/perl -w
+# Export tickets as xls and xml file with concatened history. Export each tickets
+# attachments in a dedicated directory per ticket.
+#
+# Copyright (C) 2011-2013 Emmanuel Lacour <elacour@home-dn.net>
+#
+# This file is free software; you can redistribute it and/or modify it
+# under the terms of the GNU General Public License as published by the
+# Free Software Foundation; either version 2, or (at your option) any
+# later version.
+#
+# This file 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 file; see the file COPYING. If not, write to the Free
+# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
+# 02110-1301, USA.
+#
+
+
+use strict;
+use Getopt::Long;
+use lib "/home/rt/rt/local/lib";
+use lib "/home/rt/rt/lib";
+use lib "/home/rt/rt/etc";
+use RT;
+use RT::Interface::CLI qw( CleanEnv );
+use Spreadsheet::WriteExcel;
+use XML::Writer;
+use IO::File;
+
+
+# Disable STDOUT buffering
+$| = 1;
+
+
+my $output_dir = '/tmp';
+my $tickets_limit = 0;
+
+my %opts;
+my $ok = GetOptions ( \%opts,
+ 'queue|q=s',
+ 'debug|d',
+ 'help|h',
+);
+
+if ( !$ok or $opts{'help'} or ! $opts{'queue'} )
+{
+ usage ();
+ exit (1);
+}
+
+sub usage
+{
+ print "Usage: $0 [OPTION]\n";
+ print "Options:\n";
+ print " -q , --queue=QUEUE export tickets for the queue QUEUE\n";
+ print " -d , --debug show debugging informations\n";
+ print " -h , --help show this help\n";
+}
+
+# RT CLI initialization
+CleanEnv ();
+RT::LoadConfig ();
+RT::Init ();
+
+
+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 FirstTake ResolvedObj->ISO LastUpdatedObj->ISO StartedObj->ISO);
+
+
+my $Queue = RT::Queue->new( $RT::SystemUser );
+$Queue->Load( $opts{'queue'} );
+unless ( $Queue->Id ) {
+ die "No such queue: ".$opts{'queue'}."\n";
+}
+
+$output_dir = $output_dir.'/'.$Queue->Name;
+mkdir ($output_dir);
+
+my $Tickets = RT::Tickets->new( $RT::SystemUser );
+$Tickets->UnLimit;
+$Tickets->LimitQueue( VALUE => $Queue->Id );
+$Tickets->OrderBy( FIELD => 'id', ORDER => 'ASC' );
+
+my $tickets_count = $Tickets->Count;
+print "Debug: ".$tickets_count." tickets\n" if ( $opts{'debug'} );
+
+
+my $xml_file = IO::File->new(">$output_dir/tickets.xml");
+
+my $xml = XML::Writer->new( OUTPUT => $xml_file, NEWLINES => 0, UNSAFE => 1) or die "Can't open $output_dir/tickets.xml: $!\n";
+my $workbook = Spreadsheet::WriteExcel->new("$output_dir/tickets.xls") or die "Can't open $output_dir/tickets.xls: $!\n";
+my $worksheet = $workbook->add_worksheet();
+my $attachments_dir = "$output_dir/attachments";
+mkdir ( $attachments_dir );
+
+my @rows;
+my %known_cfs;
+my $max_attachments_count = 0;
+my $i = 0;
+while (my $Ticket = $Tickets->Next) {
+ $i++;
+ #next unless ( $i >= $Tickets->Count - $tickets_limit );
+ my $percent = sprintf("%0.2f", 100 * $i / $tickets_count);
+ print "\r$percent%";
+ my $row;
+ print "Debug: ticket id ".$Ticket->id."\n" if ( $opts{'debug'} );
+
+ # Get ticket fields values
+ foreach my $attr (@attrs) {
+ if ( $attr eq 'FirstTake' ) {
+ my $Transactions = $Ticket->Transactions;
+ $Transactions->Limit(FIELD => 'Type', VALUE => 'Take');
+ $Transactions->OrderBy(FIELD => 'Created', ORDER => 'ASC');
+ if ( $Transactions->Count ) {
+ $row->{$attr} = $Transactions->First->CreatedObj->ISO;
+ } else {
+ $row->{$attr} = '';
+ }
+
+ } elsif ($attr =~ /(.*)->ISO$/ and $Ticket->$1->Unix <= 0) {
+ $row->{$attr} = "";
+ } else {
+ my $method = '$Ticket->'.$attr.'()';
+ $row->{$attr} = eval $method;
+ if ($@) {die "Failed to find $attr - ". $@};
+ }
+ }
+ my $cfs = $Ticket->QueueObj->TicketCustomFields();
+ while (my $cf = $cfs->Next) {
+ my @content;
+ my $values = $Ticket->CustomFieldValues($cf->Id);
+ while (my $value = $values->Next) {
+ push @content, $value->Content;
+ }
+ $row->{'CustomField-'.$cf->Id} = join(', ',@content);
+ if ($row->{'CustomField-'.$cf->Id}) {
+ $known_cfs{$cf->Id} = $cf->Name;
+ }
+ }
+
+ # Get ticket history
+ my $attachments_count = 0;
+ my $Transactions = $Ticket->Transactions;
+ $Transactions->OrderBy( FIELD => 'id', ORDER => 'ASC' );
+ while (my $Transaction = $Transactions->Next) {
+ next if ( $Transaction->Type =~ /^(Comment)?EmailRecord$/ );
+ my $txn_creator = $Transaction->CreatorObj->EmailAddress || $Transaction->CreatorObj->Name;
+ $row->{'History'} .= "==== ".$Transaction->CreatedObj->AsString." - ".$txn_creator.": ".$Transaction->BriefDescription." ====\n";
+ if ( $Transaction->Content && $Transaction->Content ne "This transaction appears to have no content" ) {
+ $row->{'History'} .= $Transaction->Content(Type => 'text/plain');
+ $row->{'History'} .= "\n\n";
+ }
+ # Get attachments
+ # Note: for multiple filenames, we overwrite with newer
+ my $Attachments = $Transaction->Attachments;
+ $Attachments->OrderBy( FIELD => 'id', ORDER => 'ASC' );
+ while ( my $Attachment = $Attachments->Next ) {
+ next unless ( $Attachment->Filename );
+ print "Debug: ".$Attachment->Filename."\n" if ( $opts{'debug'} );
+ my $filename = $Attachment->Filename;
+ $filename =~ s/\//-/g;
+ my $file_path = $attachments_dir."/".$Ticket->id."-".$filename;
+ open( FILE, '>' ,$file_path ) or die "Can't open $file_path: $!\n";
+ print FILE $Attachment->OriginalContent;
+ close ( FILE );
+ push @{$row->{"attachments"}}, $filename;
+ $max_attachments_count = $attachments_count if ( $attachments_count > $max_attachments_count );
+ $attachments_count++;
+ }
+ }
+ push @rows, $row;
+}
+
+my $ws_col = 0;
+my $ws_row = 0;
+my @header;
+foreach my $attr (@attrs) {
+ my $label = $attr;
+ $label =~ s'Obj-.(?:AsString|Name|ISO)''g;
+ $label =~ s'-\>MemberEmailAddressesAsString''g;
+push @header, $label;
+}
+foreach my $id (sort keys %known_cfs) {
+ push @header, "CF-".$known_cfs{$id};
+}
+push @header, 'History';
+$i = 0;
+while ( $i <= $max_attachments_count ) {
+ push @header, "attachment-$i";
+ $i++;
+}
+
+foreach my $label (@header) {
+ $worksheet->write_string($ws_row, $ws_col, $label);
+ $ws_col++;
+}
+$ws_row++;
+
+foreach my $row (@rows) {
+ my @row;
+ $xml->startTag('ticket', id => $row->{'id'});
+ foreach my $attr(@attrs) {
+ push @row, $row->{"$attr"};
+ }
+ foreach my $id (sort keys %known_cfs) {
+ my $val = $row->{'CustomField-'.$id};
+ push @row, $val;
+ }
+ push @row, $row->{'History'};
+ push @row, @{$row->{"attachments"}} if ( $row->{"attachments"} );
+ $ws_col = 0;
+ foreach my $value (@row) {
+ $xml->startTag($header[$ws_col]);
+ $xml->characters($value);
+ $worksheet->write_string($ws_row, $ws_col, $value);
+ $xml->endTag($header[$ws_col]);
+ $ws_col++;
+ }
+ $ws_row++;
+ $xml->endTag('ticket');
+ $xml->raw("\n");
+}
+
+$workbook->close;
+$xml->end();
+$xml_file->close();
+