#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Getopt::Long;
our ($opt_league, $opt_div);


&GetOptions("league=s", "div=s");

print "Working on the $opt_league league, division $opt_div\n";

#connect to database
my $dbh = DBI->connect("DBI:mysql:database=efl",
                      'auser',
                      'apass',
                      ) or die "Can't connect to database";

#set the root directory of the installation
my $rootdir= "/home/dthacker/efl/dev/";


#open teams.dir for reading
open( CLUB, "<$rootdir/teams.dir"  ) or die "Can't open teams.dir : $!";
while (<CLUB>) {
    print $_;
    my $roster_file=$_;
    my $club = substr($_, 0,3);
    my $strsql = <<EOT;

select name, age, nat, st, tk, ps, sh, agg
from players where players.club="$club"
EOT

    my $sth = $dbh->prepare($strsql);


    $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped";

    my ($name, $age, $nat, $st, $tk, $ps, $sh, $agg);

format RF =
@<<<<<<<<<<<  @< @<< @< @< @< @< @<
$name, $age, $nat, $st, $tk, $ps, $sh, $agg
.

format RF_TOP =
Name         Age Nat St Tk Ps Sh Ag KAb TAb PAb SAb Gam Sav Ktk Kps Sht Gls Ass  DP Inj Sus
-------------------------------------------------------------------------------------------
.

    open (RF, ">$roster_file") or die "Can't open roster file $roster_file";

    while ( ($name, $age, $nat, $st, $tk, $ps, $sh, $agg  ) = $sth->fetchrow_array() ) {
        write RF;
    }

    close RF;

}
$dbh->disconnect();


--format of teams.dir--
dthacker@fluffy:~/efl/dev$ more teams.dir
acm.txt
bar.txt
cel.txt
dep.txt
int.txt
lyo.txt
por.txt
ran.txt
rea.txt
val.txt

----end of teams.dir-----

---start of acm.txt (first file produced)----
Name         Age Nat St Tk Ps Sh Ag KAb TAb PAb SAb Gam Sav Ktk Kps Sht Gls Ass  DP Inj Sus
-------------------------------------------------------------------------------------------
P_Pipolo      23 ita 12 1  1  1  22
F_Bikmaz      20 tur 8  1  1  1  16
O_Veigneau    23 fra 1  11 6  1  37

----start of bar.txt (second file produced)    
I_Akinfeev    24 rus 11 1  1  1  25
C_Dinganga    21 cod 8  1  1  1  22
G_Bartolucci  27 ita 1  13 7  1  23
M_Licka       27 cze 1  11 9  1  20
D_Traore      32 fra 1  9  4  2  30