#!/usr/bin/perl -w

#!!! DIFFERS FROM DOMESTIC ONLY IN HAVING A COUNTRY FIELD ON THE LOCATION LINE!!
# Usage: load.international.pl <INFIL> <OUTFIL>
#
# Example:  load.international.pl international.31Dec05.csv international.31Dec05.sql
#
# David Harris, Version of 9 Feb 2007 16:36 GMT

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

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

@moduloarray = ('X','X','X','X','X');  #5 slots spans the # of lines needed
                                        # to identify a church listing.
#Read through INFIL file:
$numberlinesread = -1 ;
$churches = 0 ;  #A counter for the churches
READALINE:
while ($lineread = <INFIL>) {
  chomp $lineread;
  $numberlinesread++ ;
  #Read into modulo array
  $moduloarray[$numberlinesread%5] = $lineread;
  # If is blank and -2 lines is blank, is a statename.  So
  #              bail processing.
  if (($moduloarray[($numberlinesread-0)%5] eq '')
    && ($moduloarray[($numberlinesread-2)%5] eq '')) {
    next READALINE ;
  }
  # If blank and -4 is blank, then have a 3 line address
  elsif (($moduloarray[$numberlinesread%5] eq '')
    && $moduloarray[($numberlinesread-4)%5] eq '') {
    # Line1 is churchname
    $church = $moduloarray[($numberlinesread-3)%5] ;
    # Line2 is City, state
    ($city,$stateprovetc,$country) = split(/\,/, ,$moduloarray[($numberlinesread-2)%5]) ;
    # Line 3 is clergynames
    $clergy = $moduloarray[($numberlinesread-1)%5] ;
    $clergy =~ s/'/\\'/g;  #Deal with O'Connor etc.
    $clergy =~ s/"//g;  #Deal with quotes around names.
    $church =~ s/'/\\'/g;  #Deal with O'Connor etc.
    $church =~ s/"//g;  #Deal with quotes around names.
    $city =~ s/'/\\'/g;  #Deal with O'Connor etc.
    $city =~ s/"//g;  #Deal with O'Connor etc.
    $stateprovetc =~ s/ //;  #Remove blank from Clergy Letter layout
    $stateprovetc =~ s/'/\\'/g;  #Deal with quotes around names
    $stateprovetc =~ s/"//g;  #Deal with Foreign namesetc.
print OUTFIL "INSERT INTO `event`
(`stateprovetc`, `city`, `public`, `sponsor`,
`eventname`, `eventactivities`,
`eventdatetime`, `country`,
`contact`, `email`, `eventurl`)
VALUES (\'$stateprovetc\',\'$city\',\'y\',\'$church\',
'\"Evolution Sunday\" at $church\',\'Contact local church for correct time, full address, and sermon title!

$clergy is/are participating in the \"Evolution Sunday\" event near Darwin\\\'s birthday, February 12.
The clergy of more than 535 churches are preaching sermons exploring the relationship of science and religion.
The participating clergy are among more than 10,500 who have signed \"The Clergy Letter\" affirming that 
evolution and religious faith are compatible.  That is, one doesn\\\'t have to choose EITHER God OR evolution.
A person of faith can believe in both.

For more information about the worldwide Clergy Letter Project project and Evolution Sunday, see http://www.uwosh.edu/colleges/cols/rel_evol_sun.htm .
For exact time, location, and topic of this church\\\'s sermon, use your telephone directory or a Web directory
like http://yellowpages.superpages.com/yp.basic.jsp to contact this local church for details.\',
\'2007-02-11 09:00:00\',\'$country\',
\'$clergy\',\'Contact the church!\',\'http://www.butler.edu/clergyproject/rel_evol_sun2007.htm#CA\');\n" ;
    $churches++ ;  #Count a church
    next READALINE ;
  }
}
#print OUTFIL "TOTAL CHURCHES:  $churches\n" ;
close OUTFIL;
close INFIL;
exit;