#!/usr/bin/perl -w

# Usage: load.pastcontacts.pl <INFIL> <OUTFIL> <table name>
#
# Example:  load.pastcontacts.pl unsubs.9nov05.txt unsubs.9nov05.sql unsubscribers
#
# David Harris, Version of 31 Dec 2005 19:10 GMT

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

# Check the number of arguments on the command line:
if ($#ARGV+1 != 3) {
  die "load.pastcontacts.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.pastcontacts.pl INFIL file $ARGV[0] not found.\n";
(open OUTFIL, ">$ARGV[1]") or die "load.pastcontacts.pl SQL file not made.\n";
$tablename = $ARGV[2];
chomp $tablename;
$lastemail = '';
$personsernum = 1;  # SET EACH TIME
$personsernum-- ;
#Read through INFIL file:
while ($lineread = <INFIL>) {
  chomp $lineread;
  ($eventsernum,$personname,$email,$eventyear) = split(/\|/, $lineread);
  $personname =~ s/'/\\'/g;  #Deal with O'Connor etc.
  $personname =~ s/"//g;  #Deal with quotes around names.
  #$personname =~ s/,/\\,/g;  #Deal with commas.
  #$personname =~ s/;/\\;/g;  #Deal with semicolons etc.
  if ($lastemail ne $email) {  #Skip duplicated emails!  Assumes email sorted list
    $personsernum++ ;
    # The format of the SQL INSERT line:  [In future generalize the table list?]
    #print OUTFIL "INSERT INTO `$tablename` (`email`) VALUES (\'$lineread\');\n" ;
    print OUTFIL "INSERT INTO `PERSON` (`personsernum`,`personname`,`email`)
        VALUES (\'$personsernum\',\'$personname\',\'$email\');\n" ;
    print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
        VALUES (\'$personsernum\',\'waseventcontact\',\'$eventyear\',\'$eventsernum\');\n" ;    
  }
  if ($lastemail eq $email) {
    #$personsernum-- ;
    print OUTFIL "INSERT INTO `PERSONFACTS` (`personsernum`,`factname`,`factvalue1`,`factvalue2`)
        VALUES (\'$personsernum\',\'waseventcontact\',\'$eventyear\',\'$eventsernum\');\n" ;        
  }
  $lastemail = $email;
}
close OUTFIL;
close INFIL;
exit;