#!/usr/local/bin/perl

sub query
	{
	my ($q) = @_;
	$r = $DB->prepare($q);
	$r->execute;
	return $r;
	}

sub fix
	{
	my ($var, $saws) = @_;
	if ($saws == 1) { $var =~ s/(\s)//g; }
	else 
		{
		$var =~ s/(\s+)$//;
		$var =~ s/^(\s+)//;
		}
	$var =~ s/\'/\\\'/g;
	return $var;
	}

sub getNext
	{
	my ($nws, $nnw, $nnd) = @_;	# no whitespace, no nonword, no nondigit

	my $begin = 0, $end = 0;
	my $rval = "";
	if (substr($str, 0, 1) eq ",")
		{
		$rval = "";
		$str = substr($str, 1);
		}
	else
		{
		$str =~ s/\"{2}/"/g;
		$begin = index($str, "\"") + 1;
		$end = index($str, "\"", $begin);
		$length = $end - $begin;
		$rval = substr($str, $begin, $length);
		$str = substr($str, $end + 2);
		if ($nws == 1) { $rval =~ s/\s//g; }
		if ($nnw == 1) { $rval =~ s/\W//g; }
		if ($nnd == 1) { $rval =~ s/\D//g; }

		$rval =~ s/^(\s+)//;
		$rval =~ s/(\s+)$//;
		$rval =~ s/\'/\\\'/g;
		}
	return $rval;
	}

use DBI;

if (!open(IN, "experts.txt"))	{ die "\nCouldn't open experts.txt!\n"; }
$DB = DBI->connect("DBI:mysql:faculty:webdb.uvm.edu", "mstorer", "duckman");

print "DB = \"$DB\"\n";
print "Dropping Expert: ";
query("drop table Expert");
print "Done.\nRecreating Expert: ";
query("create table Expert (ID varchar(70) not null primary key, Fname varchar(30) not null, MI varchar(1), Lname varchar(30) not null, Title varchar(70), Degree varchar(30), Phone varchar(10), Email varchar(80), School varchar(60), PRContact varchar(60), Specialty varchar(255), Keywords varchar(255), Bio mediumtext, Dept varchar(60))");

print "Done.\n";

print "Status:\n";
while (!eof(IN))
	{
	$str = <IN>;

#	getNext (no whitespace, no nonword, no nondigit)

	$Fname = getNext(1,0,0);
	$MI = getNext(1,1,0);
	$Lname = getNext(1,0,0);
	$Dept = getNext(0,0,0);
	$Title = getNext(0,0,0);
	$Degree = getNext(0,0,0);
	$Phone = getNext(1,1,1);
	$Email = getNext(1,0,0);
	$Bio = getNext(0,0,0);
	$Specialty = getNext(0,0,0);
	$PRContact = getNext(0,0,0);
	$School = getNext(0,0,0);

	if ($MI ne "") { $ID = "$Fname.$MI.$Lname"; } 
	else { $ID = "$Fname.$Lname"; }

	print "Adding $ID ... ";
	query("insert into Expert (ID, Fname, MI, Lname, Dept, Title, Degree, Phone, Email, Bio, Specialty, PRContact, School) values('$ID', '$Fname', '$MI', '$Lname', '$Dept', '$Title', '$Degree', '$Phone', '$Email', '$Bio', '$Specialty', '$PRContact', '$School')");
	print "Done.\n";

#	query("insert into ExpertPW (ID) values('$ID')");
	}

$DB->disconnect;
close(IN);

print "\nDone.\n";