From 65b958cc2bc7d073678677d06cfbd47a2dbc4a40 Mon Sep 17 00:00:00 2001 From: Emmanuel Lacour Date: Wed, 12 Jun 2013 11:57:07 +0200 Subject: [PATCH] Import a script that exports tickets, attachments and history as xls and xml file --- rt2xls.pl | 233 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 233 insertions(+) create mode 100755 rt2xls.pl diff --git a/rt2xls.pl b/rt2xls.pl new file mode 100755 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 +# +# 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(); + -- 2.11.0