#!/usr/bin/perl
#
# Converts data from the Sourceforge bug/request tracker
# to the Flyspray tracker. 
# You need to grab the export.xml file yourself first, from:
#    http://sourceforge.net/export/xml_export.php?group_id=$project
# (You have to be logged in.)
#
# You also need the following components:
#  curl (program)
#  XML::Simple (perl module)
#  DBI (perl module)
#
############################################################################
#
# This script is published under the GNU General Public License version 2.0.
# See http://bjorn.haxx.se/gpl/COPYING for full license details.
#
# Copyright (C) 2006 Björn Stenberg <bjorn@haxx.se>
#
# Version 1.2
#
# History:
#
# 1.2: Fixed bug in handling of deleted attachments
# 1.1: Added UTF-8 conversion
#
############################################################################
#
# Known limitations/quirks of this script:
#
# 1: Only converts SF 'artifacts'. Not 'documents', 'tasks' or 'news'.
#
# 2: Sourceforges "artifact_group" is translated into Flyspray "version"
#
# 3: SF has priority 1-9, while Flyspray has 1-6. However due to the
#    naming in Flyspray, priorities are mapped SF:4-9 => FS:1-6
#
# 4: SF only has status 'Open', 'Closed' and 'Deleted'.
#
# 5: Assumes this is the only project in the database. EMPTIES SOME TABLES!
#
# 6: Converts all users, but naturally not their passwords. All users'
#    email address is <user>@users.sourceforge.net to allow password recovery.
#
# 7: Does not convert task history.
#
# 8: Skips any text/html attachments (treated as missing).
#

# STUFF YOU NEED TO CONFIGURE:
# ----------------------------

my $sf_group_id = 0; # needed for downloading attachments from sf.net
my $admin_user_name = ''; # someone must be admin, or you'll be stuck

my $dbpath = "dbi:mysql:flyspray";
my $dbuser = "flyspray";
my $dbpasswd = "xxx";

my $xml_file = "export.xml";

# ----------------------------

%user = ( nobody => 0 );
%resolution = ( None => 0 );
%category = ( None => 0 );
%group = ( None => 0 );

use XML::Simple;
use DBI;
use utf8;

$xml = XMLin($xml_file);

my $db = DBI->connect($dbpath, $dbuser, $dbpasswd) or die $DBI::errstr;

&create_translation_hashes;
&create_flyspray_tasks;
&create_flyspray_attachments;
&create_flyspray_comments;
&create_flyspray_list_tasktype;
&create_flyspray_list_version;
&create_flyspray_list_resolution;
&create_flyspray_list_category;
&create_flyspray_users;
&create_flyspray_users_in_groups;
exit;

sub create_flyspray_list_tasktype
{
    $db->do("DELETE FROM flyspray_list_tasktype");
    my $sth = $db->prepare("INSERT INTO flyspray_list_tasktype ".
                           "(tasktype_id, tasktype_name, ".
                           "list_position, show_in_list, project_id) ".
                           "VALUES (?,?,?,?,?)");
    my $count = 1;
    foreach (sort keys %type) {
        print "Adding tasktype $type{$_}: $_\n";
        my $rows = $sth->execute($type{$_}, $_, $count, 1, 1);
        last if ($rows < 1);
    }
}

sub create_flyspray_list_version
{
    $db->do("DELETE FROM flyspray_list_version");
    my $sth = $db->prepare("INSERT INTO flyspray_list_version ".
                           "(version_id, project_id, version_name, ".
                           "list_position, show_in_list, version_tense) ".
                           "VALUES (?,?,?,?,?,?)");
    my $count = 1;
    foreach (sort keys %group) {
        next if (not $group{$_});
        print "Adding version $group{$_}: $_\n";
        my $rows = $sth->execute($group{$_}, 1, $_, $count++, 1, 1);
        last if ($rows < 1);
    }
}

sub create_flyspray_list_resolution
{
    $db->do("DELETE FROM flyspray_list_resolution");
    my $sth = $db->prepare("INSERT INTO flyspray_list_resolution ".
                           "(resolution_id, resolution_name, ".
                           "list_position, show_in_list, project_id) ".
                           "VALUES (?,?,?,?,?)");
    my $count = 1;
    foreach (sort keys %resolution) {
        next if (not $resolution{$_});
        print "Adding resolution $resolution{$_}: $_\n";
        my $rows = $sth->execute($resolution{$_}, $_, $count++, 1, 1);
        last if ($rows < 1);
    }
}

sub create_flyspray_list_category
{
    $db->do("DELETE FROM flyspray_list_category");
    my $sth = $db->prepare("INSERT INTO flyspray_list_category ".
                           "(category_id, project_id, category_name, ".
                           "list_position, show_in_list) ".
                           "VALUES (?,?,?,?,?)");
    my $count = 1;
    foreach (sort keys %category) {
        next if (not $category{$_});
        print "Adding category $category{$_}: $_\n";
        my $rows = $sth->execute($category{$_}, 1, $_, $count++, 1);
        last if ($rows < 1);
    }
}

sub create_flyspray_users
{
    $db->do("DELETE FROM flyspray_users");
    my $sth = $db->prepare("INSERT INTO flyspray_users ".
                           "(user_id, user_name, real_name, ".
                           "email_address, account_enabled) ".
                           "VALUES (?,?,?,?,?)");
    foreach my $nick (sort keys %user) {
        next if ($nick eq "");
        my $realname = "";
        if (-f "namecache/$nick") {
            open FILE, "<namecache/$nick" or 
                die "Can't open namecache/$nick";
            $realname = <FILE>;
            close FILE;
        }
        else {
            my @html = `curl --silent http://sourceforge.net/users/$nick`;
            my $buf = join '', @html;
            $buf =~ /<td>Publicly Displayed Name: <\/td>\s*<td>\s*([^<]+)/;
            $realname = $1;
            $realname =~ s/\s+$//; # strip trailing space
            
            mkdir "namecache" if (not -d "namecache");
            open FILE, ">namecache/$nick";
            print FILE $realname;
            close FILE;
        }
        $realname = "" if (not $realname);

        print "Adding user $user{$nick}: $nick\n";
        utf8::upgrade($realname);
        my $rows = $sth->execute($user{$nick}, $nick, $realname,
                                 "$nick\@users.sourceforge.net", 1);
        last if ($rows < 1);
    }
}

sub create_flyspray_users_in_groups
{
    $db->do("DELETE FROM flyspray_users_in_groups");
    my $sth = $db->prepare("INSERT INTO flyspray_users_in_groups ".
                           "(user_id, group_id) ".
                           "VALUES (?,?)");
    foreach (sort keys %user) {
        next if ($_ eq $admin_user_name);
        print "Adding user $_ to global group \"Basic\"\n";
        my $rows = $sth->execute($user{$_}, 4);
    }
    print "Adding admin user $admin_user_name\n";
    my $rows = $sth->execute($user{$admin_user_name}, 1);
}

sub create_flyspray_comments
{
    $db->do("DELETE FROM flyspray_comments");
    my $array = &arrayref($xml->{artifacts}->{artifact});
    foreach my $t (@$array)
    {
        my $sfid = $t->{field}{artifact_id}{content};
        my $arr = &arrayref($t->{field}{artifact_messages}{message});
        foreach $m (@$arr) {
            my $time = $m->{field}{adddate}{content};
            next if (not $time);
            $comment{$sfid}{$time}{user} = $user{$m->{field}{user_name}{content}};
            $comment{$sfid}{$time}{body} = $m->{field}{body}{content};
            $comment{$sfid}{$time}{body} =~ s/Logged In:.*?\n//s;
            $comment{$sfid}{$time}{body} =~ s/user_id=.*?\n//s;
            $comment{$sfid}{$time}{body} =~ s/Browser:.*?\n//s;
        }
    }

    my $sth = $db->prepare("INSERT INTO flyspray_comments ".
                           "(comment_id, task_id, date_added, user_id, ".
                           "comment_text) ".
                           "VALUES (?,?,?,?,?)");
    my $count = 1;
    foreach my $sfid (keys %comment) {
        printf "Adding %d comments for task $flytask{$sfid}\n", scalar keys %{$comment{$sfid}};
        foreach my $time (keys %{$comment{$sfid}}) {
            utf8::upgrade($comment{$sfid}{$time}{body});
            my $rows = $sth->execute($count++, $flytask{$sfid}, $time,
                                     $comment{$sfid}{$time}{user},
                                     $comment{$sfid}{$time}{body});
            return if ($rows < 1);
        }
    }
}

sub create_flyspray_attachments
{
    # look up the trackers on sourceforge.net
    my @html = `curl --silent "http://sourceforge.net/tracker/?group_id=$sf_group_id"`;
    my @atid = grep /&amp;atid=/, @html;
    %tracker = ();
    foreach my $line (@atid) {
        $line =~ /atid=(\d+)\">([^<]+)</;
        $tracker{$2} = $1;
    }

    my $add_count = 0;

    my $array = &arrayref($xml->{artifacts}->{artifact});
    foreach my $t (@$array)
    {
        # added files
        my $arr = &arrayref($t->{field}{artifact_history}{history});
        foreach my $h (@$arr)
        {
            if ($h->{field}{field_name}{content} eq "File Added")
            {
                # http://sourceforge.net/tracker/download.php?group_id=44306&atid=439120&file_id=31058&aid=608919
                # group_id: project number
                # atid: tracker id (patches/bugs etc) rockbox patches is 439120
                # file_id: $filenum above
                # aid: artifact id
                $h->{field}{old_value}{content} =~ /(\d+): (.*)/;
                my ($fileid, $filename) = ($1,$2);
                my $aid = $t->{field}{artifact_id}{content};
                my $atid = $tracker{$t->{field}{artifact_type}{content}};
                my $destname = "$aid-$fileid";
                $attachment{$aid}{$fileid}{atid} = $atid;
                $attachment{$aid}{$fileid}{file} = $destname;
                $attachment{$aid}{$fileid}{name} = $filename;
                $attachment{$aid}{$fileid}{user} = $h->{field}{mod_by}{content};
                $attachment{$aid}{$fileid}{time} = $h->{field}{entrydate}{content};
                $add_count++;
            }
        }

        # remove deleted files
        foreach my $h (@$arr)
        {
            if ($h->{field}{field_name}{content} eq "File Deleted")
            {
                my $aid = $t->{field}{artifact_id}{content};
                my $fileid = $h->{field}{old_value}{content} + 0;
                delete $attachment{$aid}{$fileid};
            }
        }
    }

    printf "$add_count attachments to process:\n";

    $db->do("DELETE FROM flyspray_attachments");
    my $sth = $db->prepare("INSERT INTO flyspray_attachments ".
                           "(task_id, orig_name, file_name, file_size, ".
                           "file_type, added_by, date_added) ".
                           "VALUES (?,?,?,?,?,?,?)");

    foreach my $aid (keys %attachment) {
        foreach my $fileid (keys %{$attachment{$aid}}) {
            my $file = $attachment{$aid}{$fileid}{file};
            my $name = $attachment{$aid}{$fileid}{name};
            my $who = $attachment{$aid}{$fileid}{user};
            my $when = $attachment{$aid}{$fileid}{time};
            my $atid = $attachment{$aid}{$fileid}{atid};
            
            print "Adding attachment $file\n";

            if (not -f "attachments/$file") {
                mkdir "attachments" if (not -d "attachments");
                system "curl --silent -o \"attachments/$file\" \"http://sourceforge.net/tracker/download.php?group_id=$sf_group_id&atid=$atid&file_id=$fileid&aid=$aid\"";
                die "'$file' failed download" if (not -f "attachments/$file");
            }

            my $type = `file --brief --mime "attachments/$file"`;
            if ($type =~ /(.*?);/) {
                $type = $1;
            }
            next if ($type eq "text/html");
            if ($type =~ /^text/) {
                $type = "text/plain";
            }
            $sth->execute($flytask{$aid}, $name, $file, -s "attachments/$file",
                          $type, $user{$who}, $when);
        }
    }
}

# create flyspray_tasks
sub create_flyspray_tasks
{
    $db->do("DELETE FROM flyspray_tasks");
    my $sth =
        $db->prepare("INSERT INTO flyspray_tasks ".
                     "(task_id, attached_to_project, task_type, date_opened, ".
                     "opened_by, is_closed, date_closed, closed_by, ".
                     "closure_comment, item_summary, detailed_desc, ".
                     "item_status, assigned_to, resolution_reason, ".
                     "product_category, product_version, closedby_version, ".
                     "operating_system, task_severity, task_priority, ".
                     "last_edited_by, last_edited_time, percent_complete, ".
                     "mark_private, due_date) ".
                     "VALUES (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ".
                     "?,?,?,?,?, ?,?,?,?,?)");

    $array = &arrayref($xml->{artifacts}->{artifact});
    foreach my $t (@$array)
    {
        %fly = ();
        my $sfid = $t->{field}{artifact_id}{content};
        $fly{attached_to_project} = 1;
        $fly{task_type} = $type{$t->{field}{artifact_type}{content}};
        $fly{date_opened} = $t->{field}{open_date}{content};
        $fly{opened_by} = $user{$t->{field}{submitted_by}{content}};
        $fly{is_closed} = 0;
        if ($t->{field}{status}{content} ne "Open") {
            $fly{is_closed} = 1;
        }
        $fly{date_closed} = "";
        $fly{closed_by} = "";
        $fly{closure_comment} = "";

        $array = &arrayref($t->{field}{artifact_history}{history});
        foreach my $h (@$array) {
            
            if (($h->{field}{field_name}{content} eq "close_date") and
                $fly{is_closed})
            {
                $fly{date_closed} = $h->{field}{entrydate}{content};
                $fly{closed_by} = $user{$h->{field}{mod_by}{content}};
                
                my $arr = &arrayref($t->{field}{artifact_messages}{message});
                foreach my $m (@$arr) {
                    if ($m->{field}{adddate}{content} == $fly{date_closed}) {
                        $fly{closure_comment} = $m->{field}{body}{content};
                        last;
                    }
                }
                last;
            }
        }
        $fly{item_summary} = $t->{field}{summary}{content};
        $fly{detailed_desc} = $t->{field}{details}{content};
        $fly{detailed_desc} = "" if (not $fly{detailed_desc});
        
        # SourceForge 'Status' is not the same, so we set everything to 'New'
        $fly{item_status} = 2; 
        
        if ($t->{field}{assigned_to}{content} ne "nobody") {
            $fly{assigned_to} = $user{$t->{field}{assigned_to}{content}};
        }
        else {
            $fly{assigned_to} = 0;
        }
        
        $fly{resolution_reason} = $resolution{$t->{field}{resolution}{content}};
        $fly{product_category} = $category{$t->{field}{category}{content}};
        $fly{product_version} = $group{$t->{field}{artifact_group_id}{content}};
        $fly{closedby_version} = 0; # unknown
        $fly{operating_system} = 0; # no operating_system in SF
        $fly{task_severity} = $t->{field}{priority}{content} - 3;
        $fly{task_severity} = 1 if ($fly{task_severity} < 1);
        $fly{task_severity} = 5 if ($fly{task_severity} > 5);
        $fly{task_priority} = $t->{field}{priority}{content} - 3;
        $fly{task_priority} = 1 if ($fly{task_priority} < 1);
        $fly{last_edited_time} = $t->{field}{open_date}{content};
        $fly{last_edited_by} = $user{$t->{field}{submitted_by}{content}};
        $array = &arrayref($t->{field}{artifact_history}{history});
        foreach my $h (@$array) {
            if ($h->{field}{entrydate}{content} > $fly{last_edited_time})
            {
                $fly{last_edited_time} = $h->{field}{entrydate}{content};
                $fly{last_edited_by} = $user{$h->{field}{mod_by}{content}};
            }
        }
        
        $array = &arrayref($t->{field}{artifact_messages}{message});
        foreach my $m (@$array) {
            if ($m->{field}{adddate}{content} > $fly{last_edited_time})
            {
                $fly{last_edited_time} = $m->{field}{adddate}{content};
                $fly{last_edited_by} = $user{$m->{field}{user_name}{content}};
            }
        }
        
        if ($fly{is_closed} and 
            ($fly{resolution_reason} == $resolution{Fixed}))
        {
            $fly{percent_complete} = 100;
        }
        else
        {
            $fly{percent_complete} = 0;
        }
        $fly{mark_private} = 0;
        $fly{due_date} = 0;

        utf8::upgrade($fly{closure_comment});
        utf8::upgrade($fly{item_summary});
        utf8::upgrade($fly{detailed_desc});

        print "Adding task $flytask{$sfid}: $fly{item_summary}\n";
        my $lines = $sth->execute($flytask{$sfid},
                     $fly{attached_to_project},
                     $fly{task_type},
                     $fly{date_opened},
                     $fly{opened_by},
                     $fly{is_closed},
                     $fly{date_closed},
                     $fly{closed_by},
                     $fly{closure_comment},
                     $fly{item_summary},
                     $fly{detailed_desc},
                     $fly{item_status},
                     $fly{assigned_to},
                     $fly{resolution_reason},
                     $fly{product_category},
                     $fly{product_version},
                     $fly{closedby_version},
                     $fly{operating_system},
                     $fly{task_severity},
                     $fly{task_priority},
                     $fly{last_edited_by},
                     $fly{last_edited_time},
                     $fly{percent_complete},
                     $fly{mark_private},
                     $fly{due_date});
        last if ($lines < 1);
    }
}

sub create_translation_hashes
{
    my $count = 1;
    # go through all artifacts and set up some hashes
    $array = &arrayref($xml->{artifacts}->{artifact});
    foreach my $t (sort { $a->{field}{open_date}{content} <=> $b->{field}{open_date}{content}} @$array)
    {
        my $sfid = $t->{field}{artifact_id}{content};
        $flytask{$sfid} = $count++;

        my $x = $t->{field}{artifact_type}{content};
        $type{$x} = (keys %type) + 1 unless (defined $type{$x});

        my $x = $t->{field}{status}{content};
        $status{$x} = (keys %status) + 1 unless (defined $status{$x});

        my $x = $t->{field}{artifact_group_id}{content};
        $group{$x} = (keys %group) unless (defined $group{$x});

        my $x = $t->{field}{resolution}{content};
        $resolution{$x} = (keys %resolution) unless (defined $resolution{$x});

        my $x = $t->{field}{category}{content};
        $category{$x} = (keys %category) unless (defined $category{$x});

        my $x = $t->{field}{submitted_by}{content};
        $user{$x} = (keys %user) unless (defined $user{$x});

        my $x = $t->{field}{assigned_to}{content};
        $user{$x} = (keys %user) unless (defined $user{$x});

        my $arr = &arrayref($t->{field}{artifact_messages}{message});
        foreach $m (@$arr) {
            my $x = $m->{field}{user_name}{content};
            $user{$x} = (keys %user) unless (defined $user{$x});
        }

        $arr = &arrayref($t->{field}{artifact_history}{history});
        foreach my $h (@$arr) {
            my $x = $h->{field}{user_name}{content};
            $user{$x} = (keys %user) unless (defined $user{$x});
        }
    }

    if ($admin_user_name eq "") {
        print "ERROR: You must define one user as admin. Otherwise you'll be locked out.\n";
        exit;
    }
    if (not defined $user{$admin_user_name}) {
        print "ERROR: Admin user '$admin_user_name' not found in XML file.\n";
        exit;
    }
    #printf "Types: %s\n", join ', ', keys %type;
    #printf "Groups: %s\n", join ', ', keys %group;
    #printf "Resolutions: %s\n", join ', ', keys %resolution;
    #printf "Categories:\n";
    #foreach (keys %category) {
    #    print "$_ = $category{$_}\n";
    #}
    #printf "Users: %s\n", join ', ', sort keys %user;
}

sub arrayref
{
    my $ref = shift @_;
    my $array;
    
    if (ref($ref) eq "ARRAY") {
        $array = \@{$ref};
    }
    else {
        $array = [ $ref ];
    }
    
    return $array;
}