#!/usr/bin/perl
#############################################################################
# bugzilla2mantis.pl                                                        #
# Script to convert data in bugzilla (2.16-18) to Mantis (0.19.0)           #
# Probably works with other versions of bugzilla as its datamodel is fairly #
# stable. Likely to work with many versions of Mantis as well, as recent    #
# functionality is either not supported by Bugzilla or not imported.        #
# Relationships require at least Mantis 0.19.0. Everything else should work #
# on Mantis 0.18.3 (and all of 0.18.x theoretically). If you have luck using#
# specific releases of either Mantis or Bugzilla, please send them to       #
# cris@crisdaniluk.com.                                                     #
#                                                                           #
#############################################################################

# -------- begin -------
# The original web site for this script doesn't exist anymore, but
# I rediscovered this script here:
# http://web.archive.org/web/*/http://crisdaniluk.com/bugzilla2mantis/bugzilla2mantis.pl
# 
# I made some small changes, and added the possibility to
# import attachments. Also I had to add a conversion from 
# utf8 to latin1, see end of script.
# I prefixed all my changes with # Marc:
# 
# I used this script successfully to convert a Bugzilla 2.16.7 database
# to Mantis 1.0.7.
# 
# Marc, yoda at schli dot ch
# -------- end --------

# LICENSE                                                                   
# 1 You may use this code freely and without any obligation to me. 
# 2 You may use it in commercial applications. 
# 3 You may embed it. You may change it. 
# 4 You may print out thousands of copies to wallpaper your bedroom.
# 5 You may even hide the fact that this was written by someone else
# 6 BUT - You may NOT say you wrote it
# 7 AND - You may spontaneously lose all of your information in Bugzilla and
#   have unforseen problems in Mantis. I do not represent that this code 
#   works. You can make that determination on your own, after reviewing the 
#   code.
# 8 You should let me know what versions it does and does not work with
# 9 You should (but aren't obligated to)send me any enhancements you make. 
#   If you choose to, you understand your enhancements will be subject to 
#   these terms. You will be given credit.
# 

# Things not imported
# Attachments - Because this does not directly load into Mantis, but
#               instead creates a SQL dump file for you, it can't really
#               handle the attachment blobs. I'm working on a separate
#               script to handle the attachment migration.
#     (Marc: this works now)
# Milestones - mantis only uses versions. Versions can easily include
# milestones, which is I think what most people do in Bugzilla as well.
# If you need milestones that exist separate from versions, I recommend
# either adding a custom field for milestone in mantis (a GREAT mantis
# feature), or appending them to versions (i.e. "1.0 M1", "1.0 M2", ...)
# Field Defs - This is a funky feature that doesn't have a direct match in
#              Mantis. If someone thinks this might be useful to massage
#              and import, please let me know, but its really useless...
# Flags
# Named Queries - These are user level saved queries. Bugzilla uses a weird
#                 format to store these... not worth it.
# Quips - I love quips, but I REALLY love not having people ask me if I
#         spent company time writing all of them
# Series 
# Tokens
# Votes - what a stupid feature


# Things sort of imported
# Users - permissions just don't match from Bugzilla to Mantis. Bugzilla
#         has a lot more "horizontal" permissions but Mantis has a lot of
#         vertical control (project specific access). Unfortunately you'll
#         have to update permissions by hand, but this shouldn't be a big
#         deal unless this is a huge migration. If so, look at emailflags
#         ALSO, passwords are not imported so the DEFAULT mantis password
#         is used. This is 'root'. 
#         If you have a user with ID 1 in bugzilla, it will become the
#         admin user, overwriting the existing admin account. There is no
#         easy way around this since this preserves all IDs

# IMPORT SETTINGS

# Import relationships?
$importRelationships = 1; # 1/0

# Merge - You can choose to merge Bugzilla into your existing
# Mantis project. Note that at present time, consolidation is not
# done. Users, projects, etc will be duplicated. If you need to have
# this not happen, it is fairly easy to do by manually adding the
# users and/or projects and circumventing that part of the import.
# THIS IS NOT COMPLETELY IMPLEMENTED YET.
#$preserveIds = 1; # 1/0

$sqlDumpFile = 'bugzilla2mantis.sql';

# DATABASE CONNECTION SETTINGS
# This was designed for MySQL. MySQL automatically
# updates the sequences if they're bypassed. Postgres
# and Oracle won't. Be careful if you're not using MySQL.
#b_dsn must look like dbi:mysql:<database_name>:<ip_address>:<port>
$b_dsn = 'dbi:mysql:<database_name>:<ip_address>:<port>';
$b_user = '';
$b_password = '';

# VERSION SETTINGS

# Bugzillas previous to 2.18 did not have their own project id or
# private notes. If you're using 2.18, set these to 1.
$bugzillaHasProjectID = 1;
$bugzillaHasPrivateNotes = 1;

# BUGZILLA => MANTIS ATTRIBUTE MAPPINGS
# Feel free to customize these. Sometimes Mantis or Bugzilla would have
# a unique attribute (e.g. Bugzilla has 9 priorities, but Mantis has 6)
# so I choose some conservative defaults. You probably don't care.
$bgzPriority{'P1'} = 10;
$bgzPriority{'P2'} = 20;
$bgzPriority{'P3'} = 20;
$bgzPriority{'P4'} = 30;
$bgzPriority{'P5'} = 30;
$bgzPriority{'P6'} = 40;
$bgzPriority{'P7'} = 40;
$bgzPriority{'P8'} = 50;
$bgzPriority{'P9'} = 60;

# Mantis has no normal so I demoted minor to trivial to squeeze in normal
# Marc: added all the existing mantis status
$bgzSeverity{'enhancement'} = 10; #feature
$bgzSeverity{'trivial'} = 20; #trivial
#                        30; #text
$bgzSeverity{'minor'} = 40; #tweak
$bgzSeverity{'normal'} = 50; #minor
$bgzSeverity{'major'} = 60; #major
$bgzSeverity{'critical'} = 70; #crash
$bgzSeverity{'blocker'} = 80; #block
# german version
$bgzSeverity{'Erweiterung'} = 10; #feature
$bgzSeverity{'Trivial'} = 20; #trivial
#                        30; #text
$bgzSeverity{'Unkritisch'} = 40; #tweak
$bgzSeverity{'normal'} = 50; #minor
$bgzSeverity{'Wichtig'} = 60; #major
$bgzSeverity{'Kritisch'} = 70; #crash
$bgzSeverity{'Blockierend'} = 80; #block
$bgzSeverity{'Vorschlag'} = 10;

$bgzStatus{'UNCONFIRMED'} = 10; # new
$bgzStatus{'NEW'} = 10; # new
$bgzStatus{'ASSIGNED'} = 50; # assigned
$bgzStatus{'REOPENED'} = 20; # SPECIAL CASE - Changes resolution!
$bgzStatus{'RESOLVED'} = 80; # resolved
$bgzStatus{'VERIFIED'} = 40; # confirmed
$bgzStatus{'CLOSED'} = 90; # closed

$bgzResolution{'FIXED'} = 20; # fixed
$bgzResolution{'INVALID'} = 70; # not a bug
$bgzResolution{'WONTFIX'} = 90; # wont fix
$bgzResolution{'LATER'} = 80; # suspended
$bgzResolution{'REMIND'} = 80; # suspended
$bgzResolution{'DUPLICATE'} = 60; # duplicate
$bgzResolution{'WORKSFORME'} = 40; # unable to duplicate
$bgzResolution{'MOVED'} = 10; # open
$bgzResolution{'REOPENED'} = 30; # reopened


# END OF CONFIGURABLE STUFF

use DBI;
use POSIX;


use 5.004;

open(SQL, ">$sqlDumpFile")or die('Could not open SQL dump file');

my $b_dbh = DBI->connect($b_dsn, $b_user, $b_password);

# BUGZILLA PROFILES => MANTIS USERS
# id <= userid
# username <= login_name
# realname <= realname
# email <= login_name
# password <= cryptpassword
# date_created = blank
# last_visit = blank
# enabled = 1
# protected = 0
# access_level = 10
# login_count = 0
# cookie_string = blank
my @users;
{ 

  my $cookieCounter = 1; # This is dumb. Even for Mantis..
	my $strQuery = "SELECT userid, login_name, realname, cryptpassword FROM profiles";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($id, $name, $realname, $passwd) = $qry->fetchrow) {
    
    my %user;
    $user{'id'} = $id;
    $user{'username'} = $name;
    $user{'email'} = $name;
    $user{'realname'} = $realname;
    $user{'password'} = '63a9f0ea7bb98050796b649e85481845';
    $user{'date_created'} = 'current_date';
    $user{'last_visit'} = 'current_date';
    $user{'enabled'} = 1;
    $user{'protected'} = 0;
    $user{'access_level'} = 10;
    $user{'login_count'} = 0;
    $user{'cookie_string'} = 'cookiestring'.++$cookieCounter;

    # Admin override..
    if ($id == 1) {
      $user{'access_level'} = 90;
    } else {
      push(@users, \%user);
    }

  }

  foreach $user (@users) {
    print SQL "INSERT INTO mantis_user_table (id, username, realname, email, password, date_created, last_visit, enabled, protected, access_level, login_count, cookie_string) VALUES(";
    print SQL $b_dbh->quote(${$user}{'id'}).", ";
    print SQL $b_dbh->quote(${$user}{'username'}).", ";
    print SQL $b_dbh->quote(${$user}{'realname'}).", ";
    print SQL $b_dbh->quote(${$user}{'email'}).", ";
    print SQL $b_dbh->quote(${$user}{'password'}).", ";
    print SQL $b_dbh->quote(${$user}{'date_created'}).", ";
    print SQL $b_dbh->quote(${$user}{'last_visit'}).", ";
    print SQL $b_dbh->quote(${$user}{'enabled'}).", ";
    print SQL $b_dbh->quote(${$user}{'protected'}).", ";
    print SQL $b_dbh->quote(${$user}{'access_level'}).", ";
    print SQL $b_dbh->quote(${$user}{'login_count'}).", ";
    print SQL $b_dbh->quote(${$user}{'cookie_string'}).");\n";
  }
}

# Milestoning and voting are not portable concepts in Mantis
# Milestones will be ported, but not the default project settings.
# enabled <= disallownew
# access_min = 10 (public)
# view_state = 10 (public)
# file_path = nil
# status = 10
my @projects;
my %projectNameIdMap;

{
 
  my $projectId = 0;
  my $strQuery;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT id, name, description, disallownew FROM products";
  } else {
    $strQuery = "SELECT 1, product AS name, description, disallownew FROM products";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($id, $name, $desc, $disallow) = $qry->fetchrow) {

    my %project;
    if ($bugzillaHasProjectID == 1) {
      $project{'id'} = $id;
    } else {
      $project{'id'} = ++$projectId;
    }
    $projectNameIdMap{$name} = $project{'id'};

    $project{'name'} = $name;
    $project{'description'} = $desc;
    # Marc: fixed, was: = disallow;
    $project{'enabled'} = !$disallow;
    $project{'access_min'} = 10;
    $project{'view_state'} = 10;
    $project{'file_path'} = '';
    $project{'status'} = 10;
    push(@projects, \%project);

  }

  foreach $project (@projects) {
    print SQL "INSERT INTO mantis_project_table (id, name, status, enabled, view_state, access_min, file_path, description) VALUES(";
    print SQL $b_dbh->quote(${$project}{'id'}).", ";
    print SQL $b_dbh->quote(${$project}{'name'}).", ";
    print SQL $b_dbh->quote(${$project}{'status'}).", ";
    print SQL $b_dbh->quote(${$project}{'enabled'}).", ";
    print SQL $b_dbh->quote(${$project}{'view_state'}).", ";
    print SQL $b_dbh->quote(${$project}{'access_min'}).", ";
    print SQL $b_dbh->quote(${$project}{'file_path'}).", ";
    print SQL $b_dbh->quote(${$project}{'description'}).");\n";
  }
}

# BUGZILLA COMPONENTS => MANTIS CATEGORIES
# project_id <= product_id*
# category <= name
# user_id <= initialowner*
# Mantis does not use IDs for category names, so category names within
# a project MUST be unique, which is not the case in Bugzilla. To make
# sure this is not a problem, we will append the Bugzilla id to the name.
# Note that this ID will not have any intrinsic meaning in Mantis itself,
# but will allow an administrator to rename components if necessary.
my @components;
my %categoryNameIdMap;
{

  my $strQuery;
  my $categoryId = 0;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT id, name, product_id, initialowner FROM components ORDER BY product_id";
  } else {
    $strQuery = "SELECT 1, value, program, initialowner FROM components ORDER BY program";
  }
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $lastProduct = -1;
  my %categoryNames;

  while (my ($id, $name, $product, $owner) = $qry->fetchrow) {

    # If new product(ject), reset the dupe check
    if (($bugzillaHasProjectID == 1) && ($lastProduct != $product)) {
      %categoryNames = {};
    } elsif (($bugzillaHasProjectID) == 0 && ($lastProduct ne $product)) {
      %categoryNames = {};
    }
    # Check to see that this name doesn't already exist
    if (exists $categoryNames{$name}) {
     $name .= ' '.$id;
    }

    my %category; 
    if ($bugzillaHasProjectID == 1) {
      $category{'project_id'} = $product;
    } else {
      $category{'project_id'} = $projectNameIdMap{$product};
    }
    $category{'category'} = $name;
    $category{'user_id'} = $owner;

    $categoryNameIdMap{$category{'name'}} = $category{'id'};

    # Store grouping info for dupe check
    $categoryNames{$name} = $name;
    $lastProduct = $product;

    push(@categories, \%category);

  }

	my %projuser;
  foreach $category (@categories) {
    print SQL "INSERT INTO mantis_project_category_table (project_id, category, user_id) VALUES(";
    print SQL $b_dbh->quote(${$category}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$category}{'category'}).", ";
    print SQL $b_dbh->quote(${$category}{'user_id'}).");\n";

	if ($projuser{${$category}{'project_id'}."-".${$category}{'user_id'}} != '1') {
    print SQL "INSERT INTO mantis_project_user_list_table (project_id, user_id, access_level) VALUES(";
    print SQL $b_dbh->quote(${$category}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$category}{'user_id'}).", ";
	# developer
    print SQL $b_dbh->quote('55').");\n";

	$projuser{${$category}{'project_id'}."-".${$category}{'user_id'}} = '1';
	}
  } 
}

# BUGZILLA VERSIONS => MANTIS VERSIONS
# id = generated
# project_id <= product_id*
# version <= value
# date_order = now
# description = blank
# released = 0

my @versions;
{
  my $strQuery;
  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT value, product_id FROM versions";
  } else {
    $strQuery = "SELECT value, program FROM versions";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $verID = 1;

  while (my ($value, $product) = $qry->fetchrow) {

    my %version;
    $version{'id'} = $verID++;
    if ($bugzillaHasProjectID == 1) {
      $version{'project_id'} = $product;
    } else {
      $version{'project_id'} = $projectNameIdMap{$product};
    }

    $version{'version'} = $value;
    $version{'description'} = '';
    $version{'date_order'} = 'current_date';
    $version{'released'} = '0';
 
    push(@versions, \%version);

  }

  foreach $version (@versions) {
    print SQL "INSERT INTO mantis_project_version_table (id, project_id, version, date_order, description, released) VALUES(";
    print SQL $b_dbh->quote(${$version}{'id'}).", ";
    print SQL $b_dbh->quote(${$version}{'project_id'}).", ";
    print SQL $b_dbh->quote(${$version}{'version'}).", ";
    print SQL $b_dbh->quote(${$version}{'date_order'}).", ";
    print SQL $b_dbh->quote(${$version}{'description'}).", ";
    print SQL $b_dbh->quote(${$version}{'released'}).");\n";
  }

}

# BUGZILLA BUGS => MANTIS BUGS
# id <= bug_id
# project_id <= product_id*
# reporter_id <= reporter
# handler_id <= assigned_to 
# duplicate_id <= blank
# priority <= priority
# severity <= bug_severity
# reproducibility <= 10
# status <= bug_status
# resolution <= resolution
# projection <= 10
# category <= component_id
# date_submitted <= creation_ts
# last_updated <= delta_ts
# eta <= 10
# bug_text_id <= -1 (set later)
# os <= op_sys
# os_build <= blank
# platform <= rep_platform
# version <= version
# fixed_in_version <= target_milestone
# build <= blank
# profile_id <= null
# view_state <= 10
# summary <= short_desc
# sponsorship_total <= null
my %bugs;
{
  my $strQuery;

  if ($bugzillaHasProjectID == 1) {
    $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product_id, rep_platform, reporter, version, (select name from components where id = component_id), resolution, target_milestone, short_desc FROM bugs";
  } else {
    $strQuery = "SELECT bug_id, assigned_to, bug_file_loc, bug_severity, bug_status, creation_ts, delta_ts, short_desc, op_sys, priority, product, rep_platform, reporter, version, component_id, resolution, target_milestone, short_desc FROM bugs";
  }


  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;


  while (my ($bug_id, $assigned_to, $bug_file_loc, $bug_severity, $bug_status, $creation_ts, $delta_ts, $short_desc, $op_sys, $priority, $product_id, $rep_platform, $reporter, $version, $component_id, $resolution, $target_milestone, $short_desc) = $qry->fetchrow) {

    my %bug;
    
    $bug{'id'} = $bug_id;
    
    if ($bugzillaHasProjectID == 1) {
      $bug{'project_id'} = $product_id;
      $bug{'category'} = $component_id;
    } else {
      $bug{'project_id'} = $projectNameIdMap{$product_id};
      $bug{'category'} = $categoryNameIdMap{$component_id};
    }

    $bug{'reporter_id'} = $reporter;
    $bug{'handler_id'} = $assigned_to;
    $bug{'duplicate_id'} = ''; # null
    $bug{'priority'} = $bgzPriority{$priority};
    if (!(exists $bgzPriority{$priority})) { 
      $bug{'priority'} = 50;
    }
    $bug{'severity'} = $bgzSeverity{$bug_severity};
    $bug{'reproducibility'} = 10;
    $bug{'status'} = $bgzStatus{$bug_status};
    $bug{'resolution'} = $bgzResolution{$resolution};
    $bug{'projection'} = 10;
    $bug{'date_submitted'} = $creation_ts;
    $bug{'last_updated'} = $delta_ts;
    $bug{'eta'} = 10;
    $bug{'bug_text_id'} = -1;
    $bug{'os'} = $op_sys;
    $bug{'os_build'} = '';
    $bug{'platform'} = $rep_platform;
    $bug{'version'} = $version;
    $bug{'fixed_in_version'} = $target_milestone;
    $bug{'build'} = '';
    $bug{'profile_id'} = ''; # null
    $bug{'view_state'} = 10;
    # Marc: convert to iso
    $bug{'summary'} = toISO($short_desc);
    $bug{'sponsorship_total'} = ''; # null
    
    # If status is reopened, status should be assigned
    # and resolution should be REOPENED
    # Marc: changed, didn't make sense.
    if ($bug_status eq 'REOPENED') {
      $bug{'resolution'} = $bgzResolution{'REOPENED'};
    }
    if ($bug{'resolution'} == '') {
      $bug{'resolution'} = 10;
    }

    $bugs{$bug_id} = \%bug;

  }
  
# We don't iterate bugs out to the db yet to prevent a RI problem
}

# BUGZILLA LONGDESCS => MANTIS BUGTEXTS/BUGNOTES
# Mantis has a separate table for the first bug note, which it considers
# to be the bug description. Bugzilla doesn't have the idea of a
# description. In order to make Mantis work as it should, the first 
# longdesc for each bug will be the bug description. Subsequent ones
# will be bugnotes.
#
# DESCS
# id <= generated
# description <= thetext
# steps_to_reproduce <= blank
# additional_information <= blank
# 
# NOTES
# id <= generated
# bug_id <= bug_id
# reporter_id <= who
# bugnote_text_id <= generated
# view_state <= isprivate (10/50)
# date_submitted <= bug_when
# last_modified <= nil
#
# NOTES (text ref)
# id <= generated
# note <= thetext

my @bugdescs;
my @bugnotes;
{
  my $strQuery;
  if ($bugzillaHasPrivateNotes == 1) {
    $strQuery = "SELECT bug_id, who, bug_when, thetext, isprivate FROM longdescs ORDER BY bug_id";
  } else {
    $strQuery = "SELECT bug_id, who, bug_when, thetext, 10 FROM longdescs ORDER BY bug_id";
  }

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $lastBug = -1;
  my $bugNoteId = 0;
  my $bugDescId = 0;

  while (my ($bug_id, $who, $bug_when, $thetext, $isprivate) = $qry->fetchrow) {


    if ($lastBug == $bug_id) {
      # Bugnote
      my %note;
      $note{'id'} = ++$bugNoteId;
      $note{'bug_id'} = $bug_id;
      $note{'reporter_id'} = $who;
      $note{'bugnote_text_id'} = $bugNoteId;
      $note{'view_state'} = $isprivate==0?50:10;
      $note{'date_submitted'} = $bug_when;
      $note{'last_modified'} = 'current_date';
      $note{'note'} = toISO($thetext);
      push(@bugnotes, \%note);
    } else {
      my %desc;
      $desc{'id'} = ++$bugDescId;
      $desc{'description'} = toISO($thetext);
      push(@bugdescs, \%desc);
   
      # We also have to update the bug...
      $bug = $bugs{$bug_id};
      if ($bug != null) {
        # Marc: didn't work
        #${$bug}{'bug_text_id'} = $bugDescId;
        #$bugs{$bug_id} = \%bug;
        $$bug{'bug_text_id'} = $bugDescId;
      }
    }

    $lastBug = $bug_id;

  }

  foreach $desc (@bugdescs) {
    print SQL "INSERT INTO mantis_bug_text_table (id, description, steps_to_reproduce, additional_information) VALUES(";
    print SQL $b_dbh->quote(${$desc}{'id'}).", ";
    print SQL $b_dbh->quote(${$desc}{'description'}).", ";
#    print SQL $b_dbh->quote(${$desc}{'steps_to_reproduce'}).", ";
#    print SQL $b_dbh->quote(${$desc}{'additional_information'}).");\n";
    print SQL $b_dbh->quote("").", ";
    print SQL $b_dbh->quote("").");\n";
  } 

  foreach $note (@bugnotes) {
    print SQL "INSERT INTO mantis_bugnote_text_table (id, note) VALUES(";
    print SQL $b_dbh->quote(${$note}{'id'}).", ";
    print SQL $b_dbh->quote(${$note}{'note'}).");\n";

    print SQL "INSERT INTO mantis_bugnote_table (id, bug_id, reporter_id, bugnote_text_id, view_state, date_submitted, last_modified) VALUES(";
    print SQL $b_dbh->quote(${$note}{'id'}).", ";
    print SQL $b_dbh->quote(${$note}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'reporter_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'bugnote_text_id'}).", ";
    print SQL $b_dbh->quote(${$note}{'view_state'}).", ";
    print SQL $b_dbh->quote(${$note}{'date_submitted'}).", ";
    print SQL $b_dbh->quote(${$note}{'last_modified'}).");\n";


  } 
}

# now iterate out the bugs themselves..
foreach $bug (values %bugs) {
#  $bug = $bugs{$bugid};
  print SQL "INSERT INTO mantis_bug_table (id, project_id, reporter_id, handler_id, duplicate_id, priority, severity, reproducibility, status, resolution, projection, category, date_submitted, last_updated, eta, bug_text_id, os, os_build, platform, version, fixed_in_version, build, profile_id, view_state, summary, sponsorship_total) VALUES(";
  print SQL $b_dbh->quote(${$bug}{'id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'project_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'reporter_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'handler_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'duplicate_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'priority'}).", ";
  print SQL $b_dbh->quote(${$bug}{'severity'}).", ";
  print SQL $b_dbh->quote(${$bug}{'reproducibility'}).", ";
  print SQL $b_dbh->quote(${$bug}{'status'}).", ";
  print SQL $b_dbh->quote(${$bug}{'resolution'}).", ";
  print SQL $b_dbh->quote(${$bug}{'projection'}).", ";
  print SQL $b_dbh->quote(${$bug}{'category'}).", ";
  print SQL $b_dbh->quote(${$bug}{'date_submitted'}).", ";
  print SQL $b_dbh->quote(${$bug}{'last_updated'}).", ";
  print SQL $b_dbh->quote(${$bug}{'eta'}).", ";
  print SQL $b_dbh->quote(${$bug}{'bug_text_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'os'}).", ";
  print SQL $b_dbh->quote(${$bug}{'os_build'}).", ";
  print SQL $b_dbh->quote(${$bug}{'platform'}).", ";
  print SQL $b_dbh->quote(${$bug}{'version'}).", ";
  print SQL $b_dbh->quote(${$bug}{'fixed_in_version'}).", ";
  print SQL $b_dbh->quote(${$bug}{'build'}).", ";
  print SQL $b_dbh->quote(${$bug}{'profile_id'}).", ";
  print SQL $b_dbh->quote(${$bug}{'view_state'}).", ";
  print SQL $b_dbh->quote(${$bug}{'summary'}).", ";
  print SQL $b_dbh->quote(${$bug}{'sponsorship_total'}).");\n";
}

# BUGZILLA DEPENDENCIES => MANTIS RELATIONSHIPS
# id = generated
# source_bug_id <= blocked
# destination_bug_id <= dependson
# relationship_type = 1 - This is a related to, not child of. 
# Mantis doesn't have a specific blocker relationship type now.

my @relationships;

if ($importRelationships == 1) {

  my $strQuery = "SELECT blocked, dependson FROM dependencies";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $relID = 1;

  while (my ($blocked, $dependson) = $qry->fetchrow) {

    my %relationship;
    $relationship{'id'} = $relID++;
    $relationship{'source_bug_id'} = $blocked;
    $relationship{'destination_bug_id'} = $dependson;
    $relationship{'relationship_type'} = '1';
 
    push(@relationships, \%relationship);

  }

  foreach $relationship (@relationships) {
    print SQL "INSERT INTO mantis_bug_relationship_table (id, source_bug_id, destination_bug_id, relationship_type) VALUES(";
    print SQL $b_dbh->quote(${$relationship}{'id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'source_bug_id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'destination_bug_id'}).", ";
    print SQL $b_dbh->quote(${$relationship}{'relationship_type'}).");\n";
  }

}

# Need to cache Bugzilla fields for activity
my %fieldNameIdMap;
{
  my $strQuery = "SELECT id, description FROM fielddefs";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;
  
  while (my ($id, $desc) = $qry->fetchrow) {
    
    $fieldNameIdMap{$id} = $desc;

  }
}

# BUGZILLA ACTIVITY => MANTIS HISTORY
# id <= generated
# user_id <= wo
# bug_id <= bug_id
# date_modified <= bug_when
# field_name <= fieldid
# new_value <= added
# old_value <= removed
# type <= 
my @history;
{ 

  my $activityId = 0;

  my $strQuery = "SELECT bug_id, who, bug_when, fieldid, added, removed FROM bugs_activity";
  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  while (my ($bug_id, $who, $bug_when, $fieldid, $added, $removed) = $qry->fetchrow) {

    my %elem;
    $elem{'id'} = ++$activityId;
    $elem{'bug_id'} = $bug_id;
    $elem{'date_modified'} = $bug_when;
    $elem{'user_id'} = $who;
    $elem{'field_name'} = $fieldNameIdMap{$fieldid};
    $elem{'new_value'} = $added;
    $elem{'old_value'} = $removed;
    $elem{'type'} = 0;

    push(@history, \%elem);

  }

  foreach $elem (@history) {
    print SQL "INSERT INTO mantis_bug_history_table (id, user_id, bug_id, date_modified, field_name, old_value, new_value, type) VALUES(";
    print SQL $b_dbh->quote(${$elem}{'id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'user_id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$elem}{'date_modified'}).", ";
    print SQL $b_dbh->quote(${$elem}{'field_name'}).", ";
    print SQL $b_dbh->quote(${$elem}{'old_value'}).", ";
    print SQL $b_dbh->quote(${$elem}{'new_value'}).", ";
    print SQL $b_dbh->quote(${$elem}{'type'}).");\n";
  }
}


# Marc: convert attachments

# BUGZILLA ATTACHMENTS => MANTIS BUG_FILE
# id = generated
# bug_id <= bug_id*
# title <= blank
# description = description
# diskfile = ????
# filename = filename
# folder = blank
# filesize = generated
# file_type = mimetype
# date_added = creation_ts
# content = thedata

my @files;
{
  my $strQuery;
  $strQuery = "SELECT attachments.bug_id, attachments.description, attachments.filename, attachments.mimetype, attachments.creation_ts, attach_data.thedata FROM attachments,attach_data where attach_data.id=attachments.attach_id ";

  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $fileID = 1;

  while (my ($bug_id, $description, $filename, $mimetype, $creation_ts, $thedata) = $qry->fetchrow) {

    my %file;
    $file{'id'} = $fileID++;
    $file{'bug_id'} = $bug_id;

    $file{'title'} = $description;
    $file{'description'} = '';
    $file{'diskfile'} = '';
    $file{'filename'} = $filename;
    $file{'folder'} = '';
    $file{'filesize'} = length($thedata);
    $file{'file_type'} = $mimetype;
    $file{'date_added'} = $creation_ts;
    $file{'content'} = $thedata;

    push(@files, \%file);

  }

  foreach $file (@files) {
    print SQL "INSERT INTO mantis_bug_file_table (id, bug_id, title, description, diskfile, filename, folder, filesize, file_type, date_added, content) VALUES(";
    print SQL $b_dbh->quote(${$file}{'id'}).", ";
    print SQL $b_dbh->quote(${$file}{'bug_id'}).", ";
    print SQL $b_dbh->quote(${$file}{'title'}).", ";
    print SQL $b_dbh->quote(${$file}{'description'}).", ";
    print SQL $b_dbh->quote(${$file}{'diskfile'}).", ";
    print SQL $b_dbh->quote(${$file}{'filename'}).", ";
    print SQL $b_dbh->quote(${$file}{'folder'}).", ";
    print SQL $b_dbh->quote(${$file}{'filesize'}).", ";
    print SQL $b_dbh->quote(${$file}{'file_type'}).", ";
    print SQL $b_dbh->quote(${$file}{'date_added'}).", ";
    print SQL $b_dbh->quote(${$file}{'content'}).");\n";
  }

}

#Mapping of custom fields

my @cf;
{
  my $strQuery;
  $strQuery = "SELECT fielddefs.name, fielddefs.description, fielddefs.type, fielddefs.enter_bug FROM fielddefs WHERE fielddefs.custom = 1 and fielddefs.obsolete = 0";


  my $qry = $b_dbh->prepare($strQuery);
  $qry->execute;

  my $fileID = 1;

  while (my ($field_name, $description, $type, $on_enter_bug) = $qry->fetchrow) 
  {

    my $possValues = "|";
 
    if ($type != 1)
    {
      my $subQuery = "SELECT cf.value FROM " . $field_name . " cf";
      my $query = $b_dbh->prepare($subQuery);
      $query->execute;
      while (my ($value) = $query->fetchrow)
      {
        $possValues = $possValues . $value . "|";
      }
    }
    chop($possValues);
    

    my %file;
    $file{'id'} = $fileID++;
    $file{'bugzilla_name'} = $field_name;
    $file{'name'} = $description;
    if ($type == 1) 
    {
      $file{'type'} = $type;
    }
    else 
    {
      $file{'type'} = '6';
    }
    $file{'possible_values'} = $possValues;
    $file{'default_value'} = '';
    $file{'valid_regexp'} = '';
    $file{'access_level_r'} = '10';
    $file{'access_level_rw'} = '55';
    $file{'lenght_min'} = '0';
    if ($type != 1)
    {
      $file{'lenght_max'} = '0';
    }
    else
    {
      $file{'lenght_max'} = '250';
    }
    $file{'advanced'} = '0';
    $file{'require_report'} = '0';
    $file{'require_update'} = '0';
    $file{'display_report'} = '0';
    $file{'diplay_update'} = '0';
    $file{'require_resolved'} = '0';
    $file{'diplay_resolved'} = '0';
    $file{'diplay_closed'} = '0';
    $file{'require_closed'} = '0';


    push(@cf, \%file);
    
  }

  foreach $file (@cf) {
    print SQL "INSERT INTO mantis_custom_field_table (id, name, type, possible_values, default_value, valid_regexp, access_level_r, access_level_rw, lenght_min, lenght_max, advanced, required_report, require_update, display_report, diplay_update, require_resolved, diplay_resolved, diplay_closed, require_closed ) VALUES(";
    print SQL $b_dbh->quote(${$file}{'id'}).", ";
    print SQL $b_dbh->quote(${$file}{'name'}).", ";
    print SQL $b_dbh->quote(${$file}{'type'}).", ";
    print SQL $b_dbh->quote(${$file}{'possible_values'}).", ";
    print SQL $b_dbh->quote(${$file}{'default_value'}).", ";
    print SQL $b_dbh->quote(${$file}{'valid_regexp'}).", ";
    print SQL $b_dbh->quote(${$file}{'access_level_r'}).", ";
    print SQL $b_dbh->quote(${$file}{'access_level_rw'}).", ";
    print SQL $b_dbh->quote(${$file}{'lenght_min'}).", ";
    print SQL $b_dbh->quote(${$file}{'lenght_max'}).", ";
    print SQL $b_dbh->quote(${$file}{'advanced'}).", ";
    print SQL $b_dbh->quote(${$file}{'require_report'}).", ";
    print SQL $b_dbh->quote(${$file}{'require_update'}).", ";
    print SQL $b_dbh->quote(${$file}{'display_report'}).", ";
    print SQL $b_dbh->quote(${$file}{'diplay_update'}).", ";
    print SQL $b_dbh->quote(${$file}{'require_resolved'}).", ";
    print SQL $b_dbh->quote(${$file}{'diplay_resolved'}).", ";
    print SQL $b_dbh->quote(${$file}{'diplay_closed'}).", ";
    print SQL $b_dbh->quote(${$file}{'require_closed'}).");\n";

    my $strQuery;
    $strQuery = "SELECT bugs.bug_id, bugs.${$file}{'bugzilla_name'} FROM bugs";


    my $qry = $b_dbh->prepare($strQuery);
    $qry->execute;

    while (my ($bug_id, $value) = $qry->fetchrow) 
    {
     print SQL "INSERT INTO mantis_custom_field_string_table (field_id, bug_id, value) VALUES (";
     print SQL $b_dbh->quote(${$file}{'id'}).", ";
     print SQL $b_dbh->quote($bug_id).",";
     print SQL $b_dbh->quote($value).");\n";
    }
  }

}

# Marc: convert some text to iso

use Unicode::String qw(latin1 utf8);

sub toISO($) {
        my $text = $_[0];
        # if this host was UTF-8 encoded:
        my $text_iso  = (utf8($text))->latin1;
        my $text_utf8 = (latin1($text_iso))->utf8; # reverse check

        if ($text ne $text_utf8) {
#                print STDERR "Unequal reverse check! It seems your input data \"$text\" is ",
#                        "ISO encoded already, so you don't need the latin1 encoding stuff here!\n";
                # I'm going to fix this
                $text_iso = $text;
        }
        $text_iso;
}