#!/usr/bin/perl -w

# Usage: load.newsignups.pl <INFIL> <OUTFIL>
#
# Example:  load.newsignups.pl newsignups.31Dec05.csv newsignups.31Dec05.sql
#
# David Harris, Version of 12 Jun 2006 2:00 GMT

$personsernum = 75839;  # SET to beginning value EACH TIME!!!
#  Can run this SQL on the mailing db (emailbase) to get the needed value:
####   SELECT 1+MAX(personsernum) FROM person ;
# ------------------------------------------------

# Check the number of arguments on the command line:
if ($#ARGV+1 != 2) {
  die "load.newsignups.pl needs <INFIL> <OUTFIL>  as arguments";
};
# Check existence of the textfile and email listing file:
(open INFIL, "<$ARGV[0]") or die "load.newsignups.pl INFIL file $ARGV[0] not found.\n";
(open OUTFIL, ">$ARGV[1]") or die "load.newsignups.pl OUTFIL file not made.\n";

## SET $personsernum to beginning value ABOVE, EACH TIME that
##  the output from this is inserted into the mailing database!!!

$personsernum-- ;
#Read through INFIL file:
while ($lineread = <INFIL>) {
  chomp $lineread;
  ($oldpersonsernum,  #Will be replaced by $personsernum in the generated SQL.
   $personname,$persontitle,$profaffil,$streetaddress1,$streetaddress2,
   $cityaddress,$stateprovetc,$postalcode,$country,$email,$website,$password,
   $mayemail,$maydisplay,$datevalid,$lastupdated) = split(/\|/, $lineread);
  $personname =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $personname =~ s/"//g;  #Deal with quotes around names.
  $persontitle =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $persontitle =~ s/"//g;  #Deal with quotes around names.
  $profaffil =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $profaffil =~ s/"//g;  #Deal with quotes around names.
  $streetaddress1 =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $streetaddress1 =~ 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.
  $stateprovetc =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $stateprovetc =~ 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.
  $email =~ tr/A-Z/a-z/;  #lowercase all letters in emails
  $website =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $website =~ s/"//g;  #Deal with quotes around names.
  # Extract year of last update:
  ($dateyear = $lastupdated) =~ s/^(....).*$/$1/;
  if ($dateyear eq '0000') { $dateyear = '2005' }

  $personsernum++ ;
  # The format of the SQL INSERT line:
  #print OUTFIL "INSERT INTO `$tablename` (`email`) VALUES (\'$lineread\');\n" ;
  print OUTFIL "INSERT INTO `person`
      VALUES (\'$personsernum\',\'$personname\',\'$persontitle\',
        \'$profaffil\',\'$streetaddress1\',\'$streetaddress2\',
        \'$cityaddress\',\'$stateprovetc\',\'$postalcode\',
        \'$country\',\'$email\',\'$website\',\'$password\',
        \'$mayemail\',\'$maydisplay\',\'$datevalid\',\'$lastupdated\') ;\n" ;

  if ($maydisplay eq 'y') {
    print OUTFIL "INSERT INTO `personfacts` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
      VALUES (\'$personsernum\',\'issupporter\',\'$dateyear\',\'NULL');\n"
  } elsif ($maydisplay eq 'n') {
    print OUTFIL "INSERT INTO `personfacts` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
      VALUES (\'$personsernum\',\'isnodisplay\',\'$dateyear\',\'NULL');\n"
  } else {
      print  STDERR "Hit a maydisplay that was neither y nor n!!! \n";
      exit;
  }
}
close OUTFIL;
close INFIL;
exit;