#!/usr/bin/perl -w
#
# This script will take data outputed from F2W and import
# Users, Tickets, and some other data.
#
# It is loosely based on the RT-3.0 importer
#
# RT is (c) 2003 Jesse Vincent <jesse@bestpractical.com>
#
# Written 2003.02.13 by Matt Simonsen <matt@careercast.com>
#
# This script will require some configuration - I've tried to note when and where inline
#
# Version .1

#
#
# F2W Export commands:
# pg_dump f2w -t request | sort -n | egrep '^[0-9]+' > request
# pg_dump f2w -t notes | egrep  '^[0-9]' | sort -n --key=2 --key=1  > notes
# pg_dump f2w -t contact  > contact

# Find empty tickets for empty ticket hash... these will throw off numbering
# seq 1 $lastTicketNumberInF2W > ticketNumsSeq
# cat request | awk '{print $1}' > ticketNums
# for num in `diff -u ticketNums* | egrep '^\+[0-9]' | cut -b 2-` ; do echo $num ; done

package RT;
use strict;

BEGIN { $RT::DontCacheSearchBuilderRecords = 1; }
use vars qw($dbh $debug);
$debug = 0;

use vars qw/$DIR/;

my $clean_scrips   = 1;
my $import_users   = 1;
my $import_groups  = 1;
my $import_tickets = 1;
use lib ( "/usr/local/rt-3.0/lib");

use Getopt::Long;
use Date::Calc qw(Add_Delta_DHMS) ;

use RT::Interface::CLI qw(CleanEnv GetCurrentUser GetMessageContent loc);
use RT::Tickets;
use RT::Template;
use RT::CustomFields;
use RT::Principals;

#Clean out all the nasties from the environment
CleanEnv();

# Load the config file
RT::LoadConfig();

#Connect to the database and get RT::SystemUser and RT::Nobody loaded
RT::Init();

my $DIR = shift @ARGV || die "$0 requires that you specify a directory containing your F2W dump";

my $user_map;
my $group_map;

#MODIFY for use with your F2W priorities
my %priority_map = ( S => 100 , I => 30 , L => 10 , H => 40 , C => 50 , N => 9 ) ;
#MODIFY for use with your F2W categories
my %cat_map = ( "billing" => "Billing", bug => "Bug", "data management" => "Data Management", dev => "Development", dynalook => "Dynalook", "email blast" => "email Blast", feature =>  "Custom Development", feed => "Feed", "quality control" => "Quality Control", quote => "Time Estimate", support => "Support", training => "Training", wraps => "Wraps") ;
#MODIFY - should have $emptyTicketNumber => 0 for each empty record
my %emptyTickets = ( 88 => 0 , 89 => 0 , 90 => 0 , 91 => 0 , 92 => 0 , 93 => 0 , 101 => 0 , 126 => 0 , 174 => 0 , 175 => 0 , 247 => 0 , 260 => 0 , 261 => 0 , 788 => 0 , 1006 => 0 , 1256 => 0 , 1257 => 0 , 1258 => 0 , 1259 => 0, 1291 => 0, 1301 => 0) ;

my @errors;

print "\n\n\n\n\n\n\n\n";
print "Do not create scrips unless you want mass mails to go out\n" ;
print "This means you have to edit /usr/local/rt-3.0/sbin/rt-setup-database \n" ;
print "I found make regression-reset-db to work well after make install" ;
print "CTRL + C to cancel \n\n" ;
sleep 20;


if ($import_users) {
  open (USERS, "$DIR/contact") or die "couldn't open file" ;
  my $start = undef ;
  while (<USERS>) {
    #The fields we are working with
    #"k_id","k_name","k_email","k_phone_1","k_ext","k_phone_2","k_location","k_fax","k_web","k_next_avail","k_comment","k_company"
    ($start = 1 ) if m/COPY \"contact\" FROM stdin/ ;
    next if m/(COPY)|(\\\.)/ ;
    next unless $start ;
    my $user_obj = RT::User->new($RT::SystemUser);
    my $user = {} ;
    bless ($user) ;
    my @user = split /\t/ ;
    #Build hashes from user array
    my $old_id = $user[0] ;
    $user->{'Name'} = $user[1] ;
    $user->{'EmailAddress'} = $user[2] ;
    $user->{'EmailAddress'} = $user[0] unless ($user->{'EmailAddress'} =~ m/\@/ );
    $user->{'WorkPhone'} = "$user[3] $user[4]" ;
    $user->{'MobilePhone'} = $user[5] ;
    $user->{'Comments'} = "$user[10] IMPORTED from F2W" ;
    $user->{'Organization'} = $user[11];
    my ($uid, $umsg) = $user_obj->Create( %{$user} );
    my $id = $user_obj->Id();
    warn "FAILED to create user for" . $user[0] . "\n$umsg\n" unless ($id);
    #make sure hash has the old ID as a key and the new ID as the value, useful for remapping ticket owners,etc
    $user_map->{$old_id} = $id;
    my ($v2,$m2) = $user_obj->SetPrivileged(1);
    print "Set privileged did not suceed for $user->{'Name'} $m2\n" unless ($v2 == 1) ;
  }
}

if ($import_groups) {
  #This allows all people imported from users to create and modify tickets
  #  needed so that ticket import happens - you can delete the ImportedUsers
  #  group later if you need to secure those accounts
  my @members = undef;
  my $group_obj = RT::Group->new($RT::SystemUser);
  my $group = {} ;
  bless ($group) ;
  $group->{'Name'} = 'ImportedUsers' ;
  $group_obj->CreateUserDefinedGroup ( %{$group} ) ;
  my $ImportedUsersGroup = $group_obj->Id() ;
  my $princ_obj = RT::Principal->new($RT::SystemUser);
  $princ_obj->Load($ImportedUsersGroup);
  $princ_obj->GrantRight ( Object => $RT::System,
                           Right => 'CreateTicket' ) ;
  $princ_obj->GrantRight ( Object => $RT::System,
                           Right => 'OwnTicket' ) ;
  while (my ($key, $value) = each ( %{$user_map} )) {
    $group_obj->AddMember( $value ) ;
  }
}

#Create master keyword for F2W categories
my $cf_obj = RT::CustomField->new($RT::SystemUser) ;
my $cf = {} ;
bless ($cf) ;
$cf->{'Name'} = 'Categories' ;
$cf->{'Type'} = 'SelectSingle' ;
$cf->{'Queue'} = '0' ;
$cf->{'Disabled'} = '0' ;
$cf_obj -> Create ( %{$cf} ) ;
unless ( $cf_obj->Id ) {
  die "Couldn't create custom field " . $cf->{'Name'} ;
}
foreach my $key ( keys %cat_map ) {
  $cf_obj->AddValue (Name => $cat_map{$key}) ;
}


my $ticketNum = 0; #Counter for ticket number processing


if ($import_tickets) {
  open (REQUESTS, "$DIR/request") or die "couldn't open file" ;
  while (<REQUESTS>) {
      #"r_req","r_cat","r_pri","r_due","r_originator","r_desc",
      #"r_entered","r_entered_by","r_status","r_assign_to","r_assign_team"
    my $tick = {} ;
    bless ($tick) ;
    my @line = split /\t/ ;
    #Skip lines that extend past one ticket (don't think there are any)
    next unless ($line[0] =~ m/^\d+$/) ;
    $ticketNum++ ;
    #Skips empties
    while (exists $emptyTickets{$ticketNum}) {
      $ticketNum++ && &addEmpty ;
    }
    my $tick_object = RT::Ticket->new($RT::SystemUser) ;
    my $old_id = $line[0] ;
    my $due = "$line[3] 19:00:00" ;
    my $content = "$line[5]" ;
    $content =~ s/(\\r)?\\n/\n/g ;
    my $subject =  substr($content, 0, 120) ;
    my $created = "$line[6]"; 
    $created =~ s/\.\d+// ;
    $created = &PacificToGMT($created) if &validDate($created) ;
    if ($line[8] eq 't') {
      $tick->{'Status'} = 'open' ;
    } elsif ($line[8] eq 'f') {
      $tick->{'Status'} = 'resolved' ;
    } else {
      print "can't find status of ticket $line[0] \n" ;
    }
    if ($line[2] eq 'S') {
      $tick->{'Status'} = 'stalled' ;
    } 
    $tick->{'Subject'} = $subject ;
    $tick->{'Queue'} = '1' ;
    $tick->{'Due'} = $due if &validDate($due) ;
    $tick->{'InitialPriority'} = $priority_map{$line[2]} ;
    $tick->{'Created'} =  $created if &validDate($created) ;
    $tick->{'Requestor'} = $user_map->{$line[4]};
    $tick->{'Creator'} = $user_map->{$line[7]};
    $tick->{'Owner'} = $user_map->{$line[9]} ;
    $tick_object->Create( %{$tick} ) ;
    my $tick_id = $tick_object->Id() ;
    my ($cfv, $cfm) =$tick_object->AddCustomFieldValue(Field => "1", Value => "$cat_map{$line[1]}") ;

    my $trans = {} ;
    bless ($trans) ;
    my $trans_obj = RT::Transaction->new($RT::SystemUser);
    $trans->{'Ticket'} = $tick_id ;
    $trans->{'Creator'} = $user_map->{$line[7]};
    $trans->{'Created'} = $created if &validDate($created) ;
    $trans->{'Type'} = 'Comment' ;
    $trans_obj->Create ( %{$trans} ) ;
    my $trans_id = $trans_obj->Id () ;
 
    my $att_obj = RT::Attachment->new($RT::SystemUser);
    my $att = {} ;
    bless ($trans) ;
    $att->{'TransactionId'} = $trans_id ;
    $att->{'ContentType'} = 'text/plain' ;
    $att->{'Content'} = $content ;
    $att->{'Subject'} = 'Initial Comments' ;
    $att_obj->Import ( %{$att} ) ;
  }
}

# Insert notes
open (NOTES, "$DIR/notes") or die "couldn't open notes\n" ;
while (<NOTES>) {
  #         "n_id" "n_req" "n_dlm" "n_user" "n_note" "n_type"
  my @line = split /\t/ ;
  if ($line[5] =~ m/^$/) {  #Notes that are only comments
    my $trans = {} ;
    bless ($trans) ;
    my $trans_obj = RT::Transaction->new($RT::SystemUser);
    my $created = "$line[2]"; 
    $created =~ s/\.\d+// ;
    my $content = "$line[4]" ;
    $content =~ s/(\\r)?\\n/\n/g ;
    $created = &PacificToGMT($created) if &validDate($created) ;
    $trans->{'Ticket'} = $line[1] ;
    $trans->{'Creator'} = $user_map->{$line[3]};
    $trans->{'Created'} = $created if &validDate($created) ;
    $trans->{'Type'} = 'Comment' ;
    $trans_obj->Create ( %{$trans} ) ;
    my $trans_id = $trans_obj->Id () ;
    my $att_obj = RT::Attachment->new($RT::SystemUser);
    my $att = {} ;
    bless ($trans) ;
    $att->{'TransactionId'} = $trans_id ;
    $att->{'ContentType'} = 'text/plain' ;
    $att->{'Content'} = $content ;
    $att->{'Subject'} = 'Imported Comments' ;
    $att_obj->Import ( %{$att} ) ;
  } elsif ($line[5] =~ m/ASSIGN/) { #Notes that are reassignments
     my $trans = {} ;
     bless ($trans) ;
     my $assignedTo = $line[4] ;
     $assignedTo =~ s/,.*// ;
     my $created = $line[2] ;
     $created =~ s/\.\d+// ;
     $created = &PacificToGMT($created) if &validDate($created) ;
     my $trans_obj = RT::Transaction->new($RT::SystemUser);
     $trans->{'Ticket'} = $line[1] ;
     $trans->{'Created'} = $created if &validDate($created) ;
     $trans->{'Type'} = 'Force' ;
     $trans->{'NewValue'} = $user_map->{$assignedTo} ;
     $trans_obj->Create ( %{$trans} ) ;
  } elsif ($line[5] =~ m/CLOSE/) { #Notes that close tickets
     my $trans = {} ;
     bless ($trans) ;
     my $closedBy = $line[4] ;
     my $created = $line[2] ;
     $created =~ s/\.\d+// ;
     $created = &PacificToGMT($created) if &validDate($created) ;
     my $trans_obj = RT::Transaction->new($RT::SystemUser);
     $trans->{'Ticket'} = $line[1] ;
     $trans->{'Created'} = $created if &validDate($created) ;
     $trans->{'Type'} = 'Close' ;
     $trans->{'NewValue'} = $user_map->{$closedBy} ;
     $trans_obj->Create ( %{$trans} ) ;
  }
}

print "Setup log rotation\n" ;
print "Change log dir so it's accessable\n" ;

sub PacificToGMT {
  $_[0] =~ m/(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)/ ;
  my ($year, $month, $day, $hour, $mm, $ss) = Add_Delta_DHMS(
      $1, $2, $3, $4, $5, $6,
               0,  7,  0 , 0) ;
  return "$year-$month-$day $hour:$mm:$ss" ;
}

sub validDate {
  my $valid = ($_[0] =~ m/\d\d\d\d-\d\d?-\d\d? \d\d?:\d\d?:\d\d?/) ;
  return $valid;
}

sub addEmpty {
    # This creates an empty ticket to auto-increment the counter
    # so blank ones in F2W don't screw up ticket numbers.
    my $tick_object2 = RT::Ticket->new($RT::SystemUser) ;
    my $tick2 = {} ;
    bless ($tick2) ;
    $tick2->{'Status'} = 'deleted' ;
    $tick2->{'Subject'} = 'placeholder' ;
    $tick2->{'Queue'} = '1' ;
    $tick2->{'InitialPriority'} = '0' ;
    my ($msg2, $err2) = $tick_object2->Create( %{$tick2} ) ;
}