#!/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=();
}
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>