• Home page
• Technical Articles
• Family
• News
• Sounds
• Blog
• Art
• How to contact me
• Photography
• How I build this site
• Football prediction system
• build-import.clubs.pl
• Perplex City

#! /perl/bin/perl

#### Import football club names into football database
use strict;
use DOM_DB;
$|=1;

my $dbh=&db_connect('premiership');

my ($fh,$tag,$rank,$file,$id);
my (%fhn);
my (@filec, @fs);
# build master all 12 seasons all divisions
open (FILEINDEX,"fileindex.txt");
my @findex=;
close FILEINDEX;

# purge database
my $sql="DELETE FROM fixtures";         &exec_sql($sql);
   $sql="DELETE FROM results";          &exec_sql($sql);
   $sql="DELETE FROM teams";            &exec_sql($sql);

# reset all incremental counters   
$sql="ALTER TABLE teams AUTO_INCREMENT = 1";    &exec_sql($sql);
$sql="ALTER TABLE results AUTO_INCREMENT = 1";  &exec_sql($sql);

my (%tc)=();

# Get default rankings for team page
my (%start_rank,%code_id)=();
$sql="SELECT tag, start_rank, id FROM competitions";
my $csr=&select_sql($sql);
while (($tag,$rank,$id)=$csr->fetchrow) {
        $start_rank{$tag}=$rank;
        $code_id{$tag}=$id;
}
$csr->finish;

foreach $file (@findex) {
        chomp $file;
        open (FIND,$file);
        @filec=;
        close FIND;

        my @fields=split(/\,/,$filec[0]);
        my $fc=0;

        # flush the key hash so we're not using out of date headers
        %fhn=();

        foreach $fh (@fields) {
                $fhn{$fh}=$fc;
                $fc++;
        }
        GAME: foreach $fh (@filec) {
                chomp $fh;
                @fs=split(/\,/,$fh);
                if (not defined $fs[0]) {next GAME;}
                if ($fs[0]eq'Div'|| $fs[0]eq'') { next GAME; }
                # Date HomeTeam FTHG - FTAG AwayTeam
                my($a,$b,$c);
                $a=$fs[$fhn{'Date'}];
                my @date=split(/\//,$a);
                if ($date[2]>50) {$date[2]+=1900;} else {$date[2]+=2000;}
                $a="$date[2]-$date[1]-$date[0]";
                $b=$fs[$fhn{'HomeTeam'}];
                $c=$fs[$fhn{'Div'}];

                if (defined $tc{$b}) { $tc{$b}=$code_id{$c}; }
                if (not defined $tc{$b}) {
                        $sql=q{
                        INSERT INTO teams 
                        (name, start_rate, first_match,competition)
                        VALUES (?,?,?,?) };
                        # because these 2 clubs are synonyms
                        &exec_sql($sql,$b,$start_rank{$c},$a,$code_id{$c})
                                unless ($b eq 'Milton Keynes Dons' || $b eq 'Middlesbr');
                        $tc{$b}=$code_id{$c};
                }
        }
}
$sql='UPDATE teams SET competition=? WHERE name=?';
foreach $fh (keys %tc) {
        &exec_sql($sql,$tc{$fh},$fh)
                unless ($b eq 'Milton Keynes Dons' || $b eq 'Middlesbr');
}

$sql = "UPDATE teams SET rate = start_rate";
&exec_sql($sql);

# hard code the known synonyms.... *sigh*
$sql="UPDATE teams SET alt_name= ? WHERE name=?";
&exec_sql($sql,'Milton Keynes Dons','Wimbledon');
&exec_sql($sql,'Middlesbr','Middlesboro');