#!/usr/bin/perl -w

# Usage: load.nodisplays.pl <INFIL> <OUTFIL> <table name>
#
# Example:  load.nodisplays.pl nodisplays.31Dec05.csv nodisplays.31Dec05.sql dummy
#
# David Harris, Version of 1 Jan 2005 3:06 GMT

# ------------------------------------------------

# Check the number of arguments on the command line:
if ($#ARGV+1 != 3) {
  die "load.nodisplays.pl needs <INFIL> <OUTFIL> <database table name> as arguments";
};
# Check existence of the textfile and email listing file:
(open INFIL, "<$ARGV[0]") or die "load.nodisplays.pl INFIL file $ARGV[0] not found.\n";
(open OUTFIL, ">$ARGV[1]") or die "load.nodisplays.pl OUTFIL file not made.\n";
$startsernums = $ARGV[2];
chomp $startsernums;
$lastemail = '';
$personsernum = $startsernums;  # SET to beginning value by 3rd argument, EACH TIME!!!
$personsernum-- ;
#Read through INFIL file:
while ($lineread = <INFIL>) {
  chomp $lineread;
  ($oldprofsernum,  #Will be replaced by $personsernum
   $profname,$department,$university,
   $streetaddress,$officeaddress,$cityaddress,$stateaddress,$postalcode,$country,
   $phoneoffice,$phonedept,$email,
   $datevalid,$lastupdated) = split(/\|/, $lineread);
  $profname =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $profname =~ s/"//g;  #Deal with quotes around names.
  $university =~ s/'/\\'/g;  #Deal with St.John's etc.
  $university =~ s/"//g;  #Deal with quotes around names.
  #$profaffil =~ s/'/\\'/g;  #Deal with O'Connor etc.
  #$profaffil =~ s/"//g;  #Deal with quotes around names.
  $streetaddress =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $streetaddress =~ s/"//g;  #Deal with quotes around names.
  #$streetaddress2 =~ s/'/\\'/g;  #Deal with O'Connor etc.
  #$streetaddress2 =~ s/"//g;  #Deal with quotes around names.
  $cityaddress =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $cityaddress =~ s/"//g;  #Deal with quotes around names.
  $stateaddress =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $stateaddress =~ s/"//g;  #Deal with quotes around names.
  #$postalcode =~ s/'/\\'/g;  #Deal with O'Connor etc.
  #$postalcode =~ s/"//g;  #Deal with quotes around names.
  $email =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $email =~ s/"/\\"/g;  #Deal with quotes around names.
  #$website =~ s/'/\\'/g;  #Deal with O'Connor etc.
  #$website =~ s/"//g;  #Deal with quotes around names.

  if ($profname =~ m/^Dr\./) {
    $profname =~ s/^Dr\.//; $persontitle='Dr.';
  }
  else {
    $persontitle='Prof.';
  }
  
  if ($lastemail ne $email) {  #Skip duplicated emails!  Assumes email sorted list
    $personsernum++ ;
    # The format of the SQL INSERT line:
    #print OUTFIL "INSERT INTO `$tablename` (`email`) VALUES (\'$lineread\');\n" ;
print OUTFIL "INSERT INTO `PERSON`
(`personsernum`,`personname`,`persontitle`,`profaffil`,`streetaddress1`,`streetaddress2`,
`cityaddress`,`stateprovetc`,`postalcode`,`country`,`email`,`website`,`password`,
`mayemail`,`maydisplay`,
`datevalid`,`lastupdated`)      
VALUES (\'$personsernum\',\'$profname\',\'$persontitle\',\'\',
\'$streetaddress\',\'\',\'$cityaddress\',
\'$stateaddress\',\'$postalcode\',\'892\',
\'$email\',\'\',\'\',\'y\',\'n\',
\'$datevalid\',\'$lastupdated\');\n" ;

# UNUSED from Professors dataset: \'$officeaddress\',\'$phoneoffice\',\'$phonedept\',

print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
VALUES (\'$personsernum\',\'isprofessor\',\'2005\',\'3500');\n" ;
print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
VALUES (\'$personsernum\',\'atuniversity\',\'$university\',\'$department\');\n" ;
  }
  if ($lastemail eq $email) {
    #$personsernum-- ;
print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
VALUES (\'$personsernum\',\'isprofessor\',\'2005\',\'3500');\n" ;
print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
VALUES (\'$personsernum\',\'atuniversity\',\'$university\',\'$department\');\n" ;
  }
  $lastemail = $email;
}
close OUTFIL;
close INFIL;
exit;