#!/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();

