#!/usr/bin/perl
 
# mantis-mail-gateway.pl
 
# Mantis Mail Gateway
# Shen Cheng-Da (cdsheen AT gmail.com)
# require DBI to connect MySQL database
# http://blog.urdada.net/2008/11/11/95/
 
use DBI;
use POSIX qw(strftime);
 
my $db_host    = 'localhost';
my $db_name    = 'mantis';
my $db_user    = 'monitor';
my $db_pass    = 'monitorpass';
 
my $debug      = 0;
 
my $db = "dbi:mysql:dbname=${db_name};host=${db_host}";
 
die "Usage: $0 [project-name] [reporter]\n" unless @ARGV > 1;
 
my $project_name  = $ARGV[0];
my $reporter_name = $ARGV[1];
 
my $dbh;
my $sql;
my $sth;
my $project  = -1;
my $reporter = -1;
 
$dbh = DBI->connect($db, $db_user, $db_pass)
        || die 'ERROR: '.$dbh->errstr;
 
$sql = "SELECT id,name FROM mantis_project_table
        WHERE name LIKE '$project_name'";
 
$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute() || die 'ERROR: '.$dbh->errstr;
while( @data = $sth->fetchrow_array() ) {
        $project = $data[0];
}
$sth->finish;
 
die "ERROR: project \`$project_name' does not exist\n" unless $project > 0;
 
print "project: $project_name ($project)\n" if $debug;
 
$sql = "SELECT id,username FROM mantis_user_table
        WHERE username = '$reporter_name'";
 
$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute() || die 'ERROR: '.$dbh->errstr;
while( @data = $sth->fetchrow_array() ) {
        $reporter = $data[0];
}
$sth->finish;
 
die "ERROR: user \`$reporter_name' does not exist\n" unless $reporter > 0;
 
print "reporter: $reporter_name ($reporter)\n" if $debug;
 
my $subject = '';
my $content = '';
 
while(<STDIN>) {
        s/\s+$//;
        last if $_ eq '';
        $subject = $1 if /^Subject: (.+)$/;
}
while(<STDIN>) {
        $content .= $_;
}
 
$sql = 'INSERT INTO mantis_bug_text_table (description) VALUES (?)';
 
$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute($content) || die 'ERROR: '.$dbh->errstr;
$sth->finish;
 
my $textid = $dbh->{ q{mysql_insertid} };
 
print "bug text id: $textid\n" if $debug;
 
$sql = 'INSERT INTO mantis_bug_table
                ( project_id, reporter_id,
                  date_submitted, last_updated,
                  bug_text_id, summary )
        VALUES (?,?,?,?,?,?)';
 
my $now = strftime('%Y-%m-%d %H:%M:%S', localtime(time));
 
$sth = $dbh->prepare($sql) || die 'ERROR: '.$dbh->errstr;
$sth->execute($project, $reporter, $now, $now, $textid, $subject)
        || die 'ERROR: '.$dbh->errstr;
$sth->finish;
 
$dbh->disconnect;