#!/usr/bin/perl -w

# Usage: load.arthurs2005.pl <INFIL> <OUTFIL> <starting-serial-number>
#
# Example:  load.arthurs2005.pl arthurs2005.txt arthurs2005.sql 4480
#
# David Harris, Version of 3 Jan 2006 05:06 PST

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

# Check the number of arguments on the command line:
if ($#ARGV+1 != 3) {
  die "load.arthurs2005.pl needs <INFIL> <OUTFIL> <starting-serial-number> as arguments";
};
# Check existence of the textfile and email listing file:
(open INFIL, "<$ARGV[0]") or die "load.arthurs2005.pl INFIL file $ARGV[0] not found.\n";
(open OUTFIL, ">$ARGV[1]") or die "load.arthurs2005.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;
  @allfields = split  /\|/, $lineread ;
  for ($i=0; $i < (scalar @allfields); $i++) {  # Preserve O'Connor etc.
    if ( ! $allfields[$i]) {$allfields[$i] = NULL} ;
    $allfields[$i] =~ s/'/\\'/g ;
    #Could remove " chars here;
  } ;
  if ($allfields[6] eq NULL) { $allfields[6] = 892 }   #   United States
  elsif ($allfields[6] eq 'Canada') { $allfields[6] = 152 }
  elsif ($allfields[6] eq 'Australia') { $allfields[6] = 52 }
  elsif ($allfields[6] eq 'UK') { $allfields[6] = 888 }
  elsif ($allfields[6] eq 'South Africa') { $allfields[6] = 772 };
  $email = $allfields[8];
  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\`,\`streetaddress1\`,
            \`cityaddress\`,\`stateprovetc\`,\`postalcode\`,\`country\`,
            \`email\`)
        VALUES ($personsernum,\'$allfields[0]\',\'$allfields[2]\',
            \'$allfields[3]\',\'$allfields[4]\',\'$allfields[5]\',$allfields[6],
            \'$allfields[8]\');\n" ;

    print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
      VALUES ($personsernum,\'sciclubpres\',\'$allfields[1]\',\'NULL');\n" ;
  }
  if ($lastemail eq $email) {
    #$personsernum-- ;
    print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
      VALUES ($personsernum,\'sciclubpres\',\'$allfields[1]\',\'NULL');\n" ;   
  }
  $lastemail = $email;
}
close OUTFIL;
close INFIL;
exit;