Import a script that exports tickets, attachments and history as xls and xml file

diff --git a/rt2xls.pl b/rt2xls.pl
new file mode 100755 (executable)
index 0000000..65355b8
--- /dev/null
+++ b/rt2xls.pl
@@ -0,0 +1,233 @@
+#!/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();
+