#!/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 5 Feb 2006 21:06 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\',\'CHECK WITH LOCAL CHURCH FOR CORRECT TIME, FULL ADDRESS, AND SERMON TITLE!

$clergy is participating in the \"Evolution Sunday\" event on Darwin\\\'s birthday, February 12.
The clergy of more than 400 churches are preaching sermons about the compatibility of science and religion.
All the clergy involved are among more than 10,000 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 nationwide Clergy Letter Project project, http://see 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\?STYPE=S to contact this local church.\',
\'2006-02-12 10:00:00\',\'$country\',
\'$clergy\',\'CONTACT CHURCH!\',\'http://www.uwosh.edu/colleges/cols/rel_evol_sun.htm\');\n" ;
    $churches++ ;  #Count a church
    next READALINE ;
  }
}
#print OUTFIL "TOTAL CHURCHES:  $churches\n" ;
close OUTFIL;
close INFIL;
exit;