#!/usr/bin/perl
use strict;
use English;
use DBI;

sub trim;

my $dbh = DBI->connect("dbi:SQLite:iata.db","","",
	{ RaiseError => 1, AutoCommit => 0 });

my $cmd="lynx --dump -width=2000";

my $sth = $dbh->prepare("REPLACE INTO wac(code,name,country,abbrev,tel) VALUES(?,?,?,?,?);");
$|=1;
my ($letter,$acode,$airport);
foreach $letter ("a".."z") {
	my $rdsoure=$cmd . " http://www.world-airport-codes.com/alphabetical/airport-code/$letter.html|";
	print "$letter";
#	$rdsoure="a.txt";
	open (RDPIPE, $rdsoure) || die "Can't open $rdsoure for reading: $!\n";
	my $flag=0;
	while (<RDPIPE>) {
		if ($flag==0) {
			$flag++ if (/Airport\ name/);
		} elsif ($flag==1) {
			s/\[\d+\]//g;
			if (/^\s+(\w+)\s+\w+.*\ airport\ information\s{2,}(\w+.*)\s{2,}(\w+.*)\s{2,}(\w{2})\s{2,}(\d+)$/) {
				my @args=($1,$2,$3,$4,$5);
				trim($args[1],$args[2],$args[3],$args[4],$args[5]);
#				print join(",",@args) . "\n";
				$sth->execute($1,$2,$3,$4,$5);
			} elsif (/^References/) {
				$flag++;
			}
		}
	}
	close RDPIPE;
}
print "\n";
$sth->{Active} = 1;
$sth->finish();
undef $sth;
#update wac set name=trim(name);
#update wac set country=trim(country);
#update wac set abbrev=lower(abbrev);
$dbh->commit;
$dbh->disconnect();

##############################################################################
# function trims all parameters (eliminates whitespaces at beginning/end of strings)
# parameters: strings to be trimmed
# return: nothing - changes are made directly
##############################################################################
sub trim {
	for (@_) {
		if (defined $_){
			s/^\s+//;
			s/\s+$//;
		}
	}
}