#!/usr/bin/perl

=pod

=head1 NAME

addclub.pl - transform results of club registration form into a number of SQL statements

=head1 SYNOPSIS

cat text | addclub.pl

=head1 DESCRIPTION

This is a ocmapnion script for register.cgi. It transforms registration card
created by register.cgi into something that can be with minimal massage
fed into SQL

=head1 AUTHOR

Boris Veytsman

=head1 DATE

$Date: 2001/09/12 16:47:32 $

=head1 REVISION

$Revision: 1.1 $

=cut

    use strict;

my %ClubFields = ('Name'=>'Name', 'Address' => 'Address',
		  'URL' => 'URL', 'Phone' => 'Phone',
		  'Fax'=> 'Fax', 'Email' => 'Email',
		  'DoB'=>'DoB', 'Sponsor' => 'Sponsor',
		  'Meetings' => 'Meetings',
		  'AdultTeams' => 'AdultTeams',
		  'KidTeams'=>'KidTeams',
		  'ForeignFests'=>'ForeignFests',
		  'Braglist'=>'Braglist',
		  'OwnFests'=>'OwnFests',
		  );
		  
my %ClubClubFields = ('Parents' => 'Parents', 'Memebers' => 'Members');

my %PeopleFields = ();
my %ClubPeopleFields=();
my @PersonalFields=('Name', 'Address','URL','Phone','Fax','Email');
for (my $i=1; $i<=5; $i++) {
    foreach my $field (@PersonalFields) {
	$PeopleFields{"$field$i"}=$field;
    }
    $ClubPeopleFields{"Position$i"}='Position';
}

my %RegionFields = ('Latitude'=>'Lat', 'Longitude'=>'Lon');

my %AdditionalFields=('IgraA'=>'IgraA','IgraB'=>'IgraB','IgraC'=>'IgraC',
		      'Registrar'=>'Registrar', 
		      'RegistrarEmail'=>'RegistrarEmail');


my %Fields = (%ClubFields, %ClubClubFields, %PeopleFields, 
%RegionFields, %ClubPeopleFields, %AdditionalFields);

#  foreach my $key (keys %Fields) {
#      print "$key: $Fields{$key}\n";
#  }

my %Result=();
my $field="";

while (<>) {
    chomp;
    next if (/^\s*$/);
    s/\"/\\"/g;
    s/\'/\\'/g;
    if (/^([^=]+)=(.*)/ && exists $Fields{$1}) {
	$field=$1;
	$Result{$field}=$2;
    } elsif (/^Submit=Submit/) {
	PrintResults();
    } elsif ($field) {
	$Result{$field} .= " $_";
    }
}

exit 0;

sub PrintResults {

    my @Names=();
    my @Values=();
    foreach my $field (keys %ClubFields) {
	if (exists $Result{$field}) {
	    push @Names, $field;
	    push @Values, "\"$Result{$field}\"";
	}
    }
    print "INSERT INTO Clubs (\n",join(",\n",@Names),"\n) VALUES(\n",
    join(",\n",@Values),"\n);\n\n";

    my @Names=('Name');
    my @Values=("");
    foreach my $field (keys %RegionFields) {
	if (exists $Result{$field}) {
	    push @Names, $field;
	    push @Values, "\"$Result{$field}\"";
	}
    }
    print "SELECT rid,Name from Regions;\n";
    print "INSERT INTO Regions (\n",join(",\n",@Names),"\n) VALUES(\n",
    join(",\n",@Values),"\n);\n\n";
    print "INSERT INTO RegionRegion (Parent,Child) VALUES ();\n\n";
    print "SELECT cid,Name from Clubs;\n";
    print "INSERT INTO ClubRegion (rid,cid) VALUES ();\n\n";


    print "SELECT pid,Name from People;\n";
    for (my $i=1; $i<=5; $i++) {
	@Names=();
	@Values=();
	foreach my $field (@PersonalFields) {
	    if (exists $Result{"$field$i"}) {
		push @Names, $PeopleFields{"$field$i"};
		push @Values, '"'.$Result{"$field$i"}.'"';
	    }
	}
	if (scalar @Names) {
	    print "INSERT INTO People (\n",join(",\n",@Names),"\n) VALUES(\n",
	    join(",\n",@Values),"\n);\n\n";
	}
    }

    print "SELECT pid,Name from People;\n";
    for (my $i=1; $i<=5; $i++) {
	if (exists $Result{"Position$i"}) {
	    print "INSERT INTO ClubPeople (cid,pid,Position,Weight) VALUES (,,", 
	    $Result{"Position$i"}, ",10);\n";
	}
    }

    foreach my $field (keys %ClubClubFields) {
	if (exists $Result{$field}) {
	    print "\n# $field:\n";
	    print "# $Result{$field}\n";
	    print "INSERT INTO ClubClub (Parent, Child) VALUES (,);\n\n";
	}
    }
    %Result=();

}