2 # Export tickets as xls and xml file with concatened history. Export each tickets
3 # attachments in a dedicated directory per ticket.
5 # Copyright (C) 2011-2013 Emmanuel Lacour <elacour@home-dn.net>
7 # This file is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by the
9 # Free Software Foundation; either version 2, or (at your option) any
12 # This file is distributed in the hope that it will be
13 # useful, but WITHOUT ANY WARRANTY; without even the implied warranty
14 # of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this file; see the file COPYING. If not, write to the Free
19 # Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
26 use lib "/home/rt/rt/local/lib";
27 use lib "/home/rt/rt/lib";
28 use lib "/home/rt/rt/etc";
30 use RT::Interface::CLI qw( CleanEnv );
31 use Spreadsheet::WriteExcel;
36 # Disable STDOUT buffering
40 my $output_dir = '/tmp';
41 my $tickets_limit = 0;
44 my $ok = GetOptions ( \%opts,
50 if ( !$ok or $opts{'help'} or ! $opts{'queue'} )
58 print "Usage: $0 [OPTION]\n";
60 print " -q , --queue=QUEUE export tickets for the queue QUEUE\n";
61 print " -d , --debug show debugging informations\n";
62 print " -h , --help show this help\n";
65 # RT CLI initialization
71 my @attrs = qw( id QueueObj->Name Subject Status TimeEstimated TimeWorked TimeLeft Priority FinalPriority OwnerObj->Name
72 Requestors->MemberEmailAddressesAsString Cc->MemberEmailAddressesAsString AdminCc->MemberEmailAddressesAsString
73 DueObj->ISO ToldObj->ISO CreatedObj->ISO FirstTake ResolvedObj->ISO LastUpdatedObj->ISO StartedObj->ISO);
76 my $Queue = RT::Queue->new( $RT::SystemUser );
77 $Queue->Load( $opts{'queue'} );
78 unless ( $Queue->Id ) {
79 die "No such queue: ".$opts{'queue'}."\n";
82 $output_dir = $output_dir.'/'.$Queue->Name;
85 my $Tickets = RT::Tickets->new( $RT::SystemUser );
87 $Tickets->LimitQueue( VALUE => $Queue->Id );
88 $Tickets->OrderBy( FIELD => 'id', ORDER => 'ASC' );
90 my $tickets_count = $Tickets->Count;
91 print "Debug: ".$tickets_count." tickets\n" if ( $opts{'debug'} );
94 my $xml_file = IO::File->new(">$output_dir/tickets.xml");
96 my $xml = XML::Writer->new( OUTPUT => $xml_file, NEWLINES => 0, UNSAFE => 1) or die "Can't open $output_dir/tickets.xml: $!\n";
97 my $workbook = Spreadsheet::WriteExcel->new("$output_dir/tickets.xls") or die "Can't open $output_dir/tickets.xls: $!\n";
98 my $worksheet = $workbook->add_worksheet();
99 my $attachments_dir = "$output_dir/attachments";
100 mkdir ( $attachments_dir );
104 my $max_attachments_count = 0;
106 while (my $Ticket = $Tickets->Next) {
108 #next unless ( $i >= $Tickets->Count - $tickets_limit );
109 my $percent = sprintf("%0.2f", 100 * $i / $tickets_count);
112 print "Debug: ticket id ".$Ticket->id."\n" if ( $opts{'debug'} );
114 # Get ticket fields values
115 foreach my $attr (@attrs) {
116 if ( $attr eq 'FirstTake' ) {
117 my $Transactions = $Ticket->Transactions;
118 $Transactions->Limit(FIELD => 'Type', VALUE => 'Take');
119 $Transactions->OrderBy(FIELD => 'Created', ORDER => 'ASC');
120 if ( $Transactions->Count ) {
121 $row->{$attr} = $Transactions->First->CreatedObj->ISO;
126 } elsif ($attr =~ /(.*)->ISO$/ and $Ticket->$1->Unix <= 0) {
129 my $method = '$Ticket->'.$attr.'()';
130 $row->{$attr} = eval $method;
131 if ($@) {die "Failed to find $attr - ". $@};
134 my $cfs = $Ticket->QueueObj->TicketCustomFields();
135 while (my $cf = $cfs->Next) {
137 my $values = $Ticket->CustomFieldValues($cf->Id);
138 while (my $value = $values->Next) {
139 push @content, $value->Content;
141 $row->{'CustomField-'.$cf->Id} = join(', ',@content);
142 if ($row->{'CustomField-'.$cf->Id}) {
143 $known_cfs{$cf->Id} = $cf->Name;
148 my $attachments_count = 0;
149 my $Transactions = $Ticket->Transactions;
150 $Transactions->OrderBy( FIELD => 'id', ORDER => 'ASC' );
151 while (my $Transaction = $Transactions->Next) {
152 next if ( $Transaction->Type =~ /^(Comment)?EmailRecord$/ );
153 my $txn_creator = $Transaction->CreatorObj->EmailAddress || $Transaction->CreatorObj->Name;
154 $row->{'History'} .= "==== ".$Transaction->CreatedObj->AsString." - ".$txn_creator.": ".$Transaction->BriefDescription." ====\n";
155 if ( $Transaction->Content && $Transaction->Content ne "This transaction appears to have no content" ) {
156 $row->{'History'} .= $Transaction->Content(Type => 'text/plain');
157 $row->{'History'} .= "\n\n";
160 # Note: for multiple filenames, we overwrite with newer
161 my $Attachments = $Transaction->Attachments;
162 $Attachments->OrderBy( FIELD => 'id', ORDER => 'ASC' );
163 while ( my $Attachment = $Attachments->Next ) {
164 next unless ( $Attachment->Filename );
165 print "Debug: ".$Attachment->Filename."\n" if ( $opts{'debug'} );
166 my $filename = $Attachment->Filename;
167 $filename =~ s/\//-/g;
168 my $file_path = $attachments_dir."/".$Ticket->id."-".$filename;
169 open( FILE, '>' ,$file_path ) or die "Can't open $file_path: $!\n";
170 print FILE $Attachment->OriginalContent;
172 push @{$row->{"attachments"}}, $filename;
173 $max_attachments_count = $attachments_count if ( $attachments_count > $max_attachments_count );
174 $attachments_count++;
183 foreach my $attr (@attrs) {
185 $label =~ s'Obj-.(?:AsString|Name|ISO)''g;
186 $label =~ s'-\>MemberEmailAddressesAsString''g;
187 push @header, $label;
189 foreach my $id (sort keys %known_cfs) {
190 push @header, "CF-".$known_cfs{$id};
192 push @header, 'History';
194 while ( $i <= $max_attachments_count ) {
195 push @header, "attachment-$i";
199 foreach my $label (@header) {
200 $worksheet->write_string($ws_row, $ws_col, $label);
205 foreach my $row (@rows) {
207 $xml->startTag('ticket', id => $row->{'id'});
208 foreach my $attr(@attrs) {
209 push @row, $row->{"$attr"};
211 foreach my $id (sort keys %known_cfs) {
212 my $val = $row->{'CustomField-'.$id};
215 push @row, $row->{'History'};
216 push @row, @{$row->{"attachments"}} if ( $row->{"attachments"} );
218 foreach my $value (@row) {
219 $xml->startTag($header[$ws_col]);
220 $xml->characters($value);
221 $worksheet->write_string($ws_row, $ws_col, $value);
222 $xml->endTag($header[$ws_col]);
226 $xml->endTag('ticket');