#!/usr/bin/perl
###################################################################
# mantis_pop
# Reads emails from a POP3 account and enter bugs to Mantis BT
# Leonardo Herrera (leus@epublish.cl)
# Feel free to use it. All disclaimers apply, use at your own risk,
# etc. 
###################################################################
  use strict;
  use DBI();
  use Mail::POP3Client;
  use Email::Simple;
  use MIME::Words qw(:all);

  my $dbh;


# Values.
my $mantis_dbserver = 'localhost';
my $mantis_username = 'mantis';
my $mantis_password = 'mantis';
my $mantis_database = 'mantis';

my $pop_server		= 'my.mail.server';
my $pop_username	= 'bugs';
my $pop_password	= 'boogieman';

# These values must be present in your Mantis bugtracker.
my $project_name	= 'Incoming Email';
my $reporter_name	= 'email';
my $custom_field	= 'From';

sub addBugo() {
  ##########################################################################
  # This values have to be present in your bug tracker.
  # All incoming emails are going to be assigned to this project.
  ##########################################################################
  my $project = $dbh->quote( $project_name );

  ##########################################################################
  # the user that appears as the author of the report.
  ##########################################################################
  my $reporter = $dbh->quote( $reporter_name );

  my $project_id = -1;
  my $reporter_id = -1;
  my $bug_text_id = -1;

  my $from = $dbh->quote( shift );
  my $summary = $dbh->quote( "$project_name: " . shift );
  my $desc = $dbh->quote( shift );

  # Obtain the id of the default project.
  my $sth = $dbh->prepare("SELECT * FROM mantis_project_table where name = $project" );
  $sth->execute();
  while (my $ref = $sth->fetchrow_hashref()) {
    $project_id = $ref->{'id'};
  }
  $sth->finish();

  print "Project id: $project_id\n";

  # Obtain the default user id
  my $sth = $dbh->prepare("SELECT * FROM mantis_user_table where username = $reporter" );
  $sth->execute();
  my $reporter_id = -1;
  while (my $ref = $sth->fetchrow_hashref()) {
    $reporter_id = $ref->{'id'};
  }
  $sth->finish();

  print "Reporter id: $reporter_id\n";

  # Insert text.
  $sth = $dbh->do( "insert into mantis_bug_text_table (description) values ($desc)" );
  $bug_text_id = $dbh->{'mysql_insertid'};

  print "Last inserted text id: $bug_text_id\n";

  # Insert the actual bug
  my $sqlst =<<EOF;
  insert into mantis_bug_table(
	project_id,
	reporter_id,
	summary,
	bug_text_id,
	category,
	date_submitted,
	last_updated,
	os, os_build, platform, version, build )
  values ( 
	$project_id,
        $reporter_id,
        $summary,
        $bug_text_id,
	 '', NOW(), NOW(), '', '', '', '', '' )
EOF

  $sth = $dbh->do( $sqlst );
  my $last_id = $dbh->{'mysql_insertid'};

  ##########################################################################
  # Obtain the custom field id
  if ($last_id != -1) {
    $sth = $dbh->prepare( "select id from mantis_custom_field_table where name = '$custom_field'" );
    $sth->execute();

    my $custom_field_id = -1;
    while (my $ref = $sth->fetchrow_hashref()) {
      $custom_field_id = $ref->{'id'};
    }
    $sth->finish();

    if ($custom_field_id != -1) {
      # Ingresar el texto.
      $sth = $dbh->do( "insert into mantis_custom_field_string_table (field_id, bug_id, value) " .
                          "values ($custom_field_id, $last_id, $from )" );
    }


    # Register the event into the historic table.
    $dbh->do( "INSERT INTO mantis_bug_history_table ( user_id, bug_id, date_modified, type, old_value, new_value ) " .
              "VALUES ( $reporter_id, $last_id, NOW(), 1, '', '')" );

  }

  print "Last inserted bug id: $last_id\n";
  return $last_id;
}
########################################################
my $pop = new Mail::POP3Client( USER     => $pop_username,
                                PASSWORD => $pop_password,
                                HOST     => $pop_server,
                                AUTH_MODE => 'PASS',
                                DEBUG => 0 );

# Oops, nice error management...
die if not $pop;

my @messages = ();
for( my $i = 1; $i <= $pop->Count(); $i++ ) {
        my %envelope;
        foreach( $pop->Head( $i ) ) {
                if( /^(From|Subject|Date|Content\-type):\s+(.*)$/i) {
                        $envelope{lc $1} = decode_mimewords( $2 );
		}
        }
	$envelope{body} .= decode_mimewords( '' . $pop->Body($i) );

	# This step is optional.
	# In my installation, our mantis server is located in a intranet, thus isolated
	# from the web server. This is why I had to implement this. However, I don't want
	# to accept any incoming email, so I added this little check: if a bug report doesn't
	# contain the string "[bug]" we just don't process it.
	if ($envelope{subject} =~ /\[bug\]/ ) {
        	push @messages, \%envelope;
		$pop->Delete( $i );
	} else {
                print " *** NO ES VALIDO (\"$envelope{subject}\") ***\n";
	}
}
$pop->Close();

########################################################
# Connect to the database.
$dbh = DBI->connect("DBI:mysql:database=$mantis_database;host=$mantis_dbserver",
                         "$mantis_username", "$mantis_password",
                         {'RaiseError' => 1});

########################################################
while (my $hash_ref = pop @messages) {
        print "From: $hash_ref->{from}\n";
	print "Subject: $hash_ref->{subject}\n--\n";

	# Check if the [bug] tag is located at the beginning of the subject.
	# This is a lame attemp to avoid inserting replies.
        if ($hash_ref->{subject} =~ /^\[bug\]/) {
		##############################################################
		# Another check. In my installation, I use an extra string when
		# replying to a bug entered via the email interface (Case ###)
		##############################################################
		if ($hash_ref->{subject} =~ /^\(Case (\d+)\)/) {
			# Ignore it. A very neat feature would be to add it
			# as a "reply" in the historic events.
			print "Reply to case $1, ignoring...\n";
			next;
		}
		&addBugo( $hash_ref->{from}, $hash_ref->{subject}, $hash_ref->{body} );
        } else {
		# We still have the "[bug]" tag, just not at the beginning.
	}
}
# Disconnect from the database.
$dbh->disconnect();