#!/usr/bin/perl -w
#This perl script attempts to convert a Microsoft Outlook 2000 email
#address list that has been exported as Comma Separated Values (DOS)
#into Pronto mail that is stored in an SQL database.
#
#Copyright Adam Warner and distributed under the terms of the
#GNU General Public License.

use DBI;

if ($#ARGV<3)
	{
	print "\nMSOutlookAddresses2SQLPronto.pl Version 1.01\n\nThis perl script attempts to convert a Microsoft Outlook 2000 email\naddress list that has been exported as Comma Separated Values (DOS)\ninto Pronto mail that is stored in an SQL database.\n\nCopyright Adam Warner and distributed under the terms of the GNU General Public License.\n";
	print "\nYou must supply four to seven arguments:\n";
	print "  (1) The filename to import\n";
	print "  (2) The type of database (e.g. mysql)\n";
	print "  (3) The name of the database (i.e. your username)\n";
	print "(4-7) the parts of the name and the order to import:\n";
	print "      Where \"T\" is the Title\n";
	print "            \"F\" is the First Name\n";
	print "            \"M\" is the Middle Name\n";
	print "        and \"L\" is the Last Name\n";
	print "\nFor example:\n";
	print "\$ ./MSOutlookAddresses2SQLPronto.pl addresses.CSV mysql bob T F M L\n\n";
	exit;
	}

$filename=$ARGV[0];
$database="DBI:$ARGV[1]:$ARGV[2]";

#Arrays start at zero, thus five values are initialized even though a maximum of 1-4 elements are used
@nameord = ("","","","",""); @nameordvar = ("","","","","");

#Read in T, F, M, L arguments
$i=3;
while ($ARGV[$i])
	{
	$nameord[$i-2]=uc(substr($ARGV[$i],0,1));
	$i++;
	}

$nameparts=$i-3;

$firstnamepos=0; $lastnamepos=0; #Used for adding a comma after lastname, if necessary




for ($i=1; $i<=$nameparts; $i++)
	{ 
	if ($nameord[$i] eq "T") { $nameordvar[$i] = '$title'; }
	if ($nameord[$i] eq "F") { $nameordvar[$i] = '$firstname'; $firstnamepos=$i; }
	if ($nameord[$i] eq "M") { $nameordvar[$i] = '$middlename'; }
	if ($nameord[$i] eq "L") { $nameordvar[$i] = '$lastname'; $lastnamepos=$i; }
	}

if ($lastnamepos<$firstnamepos) { $nameordvar[$lastnamepos] = '$lastname,'; }
$constructfullname = "$nameordvar[1] $nameordvar[2] $nameordvar[3] $nameordvar[4]";




#Read database and determine current maximum index number
$dbh = DBI->connect($database);
$statement = "select id from addresses";
$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
$rv = $sth->execute or die "Can't execute $statement: $sth->errstr\n";


$highid=0;
while ($id = $sth->fetchrow_array)
	{
	if ($highid<$id) { $highid=$id; }
	}




open ($addresses,$filename) || die "Can't open $filename\n";
$line=<$addresses>; #Kill the first column headings entry




$id=$highid+1;
$addalladdresses = "n";

while ($line=<$addresses>) # $line is non-zero until EOF
	{
	$_= $line;
	my $title = $1 if /\"?(.*?)\"?,/;		

	$_= $line;
	my $firstname = $1 if /,\"?(.*?)\"?,/;

	$_= $line;
	my $middlename = $1 if /,.*?,\"?(.*?)\"?,/;

	$_= $line;
	my $lastname = $1 if /,.*?,.*?,\"?(.*?)\"?,/;

	$_= $line;
	my $email = $1 if /,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,.*?,\"?(.*?)\"?,/;		
	
	#Let's formulate the name as desired
	$fullname = $constructfullname;
	$fullname =~ s/(\$\w+)/$1/eeg;  

	#And eliminate any beginning or trailing spaces
	$fullname =~ s/^\s+//;
        $fullname =~ s/\s+$//;
	#And eliminate any double or triple spaces
	$fullname =~ s/  / /;
	$fullname =~ s/  / /;

	#Time to update the database
	$statement = "insert into addresses (id,alias,address) values (\"$id\",\"$fullname\",\"$email\")";
	if ($addalladdresses eq "n")
		{
		print "\n$statement\nExecute above statement/add email address [y(es)/n(no)/a(ll)]? ";
		$update=lc(substr(<STDIN>,0,1));
		if ($update eq "a") { $addalladdresses = "y"; }
		}

	if ($update eq "y" | $addalladdresses eq "y")
		{
		$sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
		$rv = $sth->execute or die "Can't execute $statement: $sth->errstr\n";
		$id=$id+1;
		}
	}

print "Done.\n";