#! /usr/bin/perl
# URL TO CONNECT TO
#$url = "http://stats.howesportsdata.com/STATS/HOCKEY/ECHL/STANDTAB.TXT";

# This is the file from Howe that you need
my $neededfile = 'http://howesportsdata.com/howesportsdata/stats/hockey/echl/standtab.txt';

# Form the cURL request
my $req = "/data/www/dev/lib/share/howegrabber/bin/howegrabber -u 'Augusta echl' -p 'HCK-531' -l '$neededfile'";

# Run cURL
my $res = `$req`;
$fullText = $res;

print "RESPONSE:\n\n\n$fullText\n\n\n";

# DATABASE TO CONNECT TO
$DATABASE = "stats_hockey";
$league_id = 1;

print "connecting to db...\n";

# INITIALIZE DATABASE CONNECTION
&connect;

# SET GLOBAL ARRAYS FOR FIELDS
@standings = ("gp", "w", "l", "t", "pts", "pct", "gf", "ga", "pim", "home_wlt", "road_wlt", "last_wlt", "streak_wlt");
@attendance = ("attendance_lastgame", "attendance_total", "attendance_openings", "attendance_average");

# CALL LYNX AND DUMP URL
#$fullText = `/usr/local/bin/lynx --source -accept_all_cookies -auth=Augusta%20echl:HCK-531 $url`;

# flush the database
&sql("delete from ho_standings where league_id='$league_id';");

print $fullText;
# PARSE TEXT
@fullText = split(/\n/, $fullText);
foreach $line (@fullText) {
#	if ($attendanceFlag) {
#		if ($line =~ /TOTAL/) { $attendanceFlag = 0; }
#		else {
#			&parseAttendanceLine($line);
#			}
#		}
	if ($standingsFlag) {
		if ($line =~ /TOTAL/) { $standingsFlag = 0; }
		else {
			&parseStandingsLine($line);
			}
		}

	if ($line =~ /PACIFIC/) { $standingsFlag = 1; }
	if ($line =~ /ATTENDANCE REPORT/) { $attendanceFlag = 1; }
	}

###############################################################
# SUBS
###############################################################

sub parseAttendanceLine() {
	$line = $_[0];
	chop($line);

	@data = split(/\t/, $line);
	if ($data[0] && $data[0] ne "TEAM") {
		for ($i = 0; $i < scalar(@data); $i++) {
			$value = $data[$i];
			if ($i == 0) {
				my @name = split(/-/, $value);
				if (scalar(@name) == 1) {
					$value = $name[0];
					}
				else {
					$value = $name[1];
					}
				&sql("select team_id from ho_teams where team_home='$value' and league_id='$league_id'");
				$id = $sth->fetchrow_array;
				if (!$id) {
					&sql("insert into ho_teams (team_id, league_id, team_home) values ('', '$league_id', '$value');");
					$id = $sth->{mysql_insertid};
					}
				$vals = "'$league_id', '$id'";
				$cols = "league_id, team_id";
				}
			else {
				$cols .= ", ".$attendance[$i - 1];
				$vals .= ", '$value'";
				}

			}		
		&sql("insert into ho_attendance ($cols) values ($vals);");
		}
	}

sub parseStandingsLine() {
	$line = $_[0];
	chop($line);

	@data = split(/\t/, $line);
	if (scalar(@data) > 5) {
		for ($i = 0; $i < scalar(@data); $i++) {
			$value = $data[$i];
			if ($i == 0) {
				my @name = split(/-/, $value);
				if (scalar(@name) == 1) {
					$value = $name[0];
					}
				else {
					$value = $name[1];
					}
				$value =~ s/\'/\\\'/g;
				if ($value eq "Barre") { $value = "Wilkes-Barre"; }
				&sql("select team_id from ho_teams where team_home='$value' and league_id='$league_id'");
				print "select team_id from ho_teams where team_home='$value' and league_id='$league_id'\n";
				$id = $sth->fetchrow_array;
				if (!$id) {
					&sql("insert into ho_teams (team_id, league_id, team_home) values ('', '$league_id', '$value');");
					$id = $sth->{mysql_insertid};
					}
				$vals = "'', '$league_id', '$id'";
				$cols = "id, league_id, team_id";
				}
			else {
				$cols .= ", ".$standings[$i - 1];
				$vals .= ", '$value'";
				}
			}
		&sql("insert into ho_standings ($cols) values ($vals);");
		print "insert into ho_standings ($cols) values ($vals);\n";
		}
	}



# despace stuff
sub despace() {
        $_[0] =~ s/^\s+//;
        $_[0] =~ s/\s+$//;
        return $_[0];
        }

################################################
# DATABASE CONNECT METHODS for MySQL           #
# -                                            #
# Used as a "require" script, not a standalone #
################################################

# connect to the database
sub connect() {
	use DBI;
	$dbh = DBI->connect("DBI:mysql:$DATABASE:69.94.233.31", "ism3", "kuf532ls") || die "Connect failed.\nDBI::errstr\n" unless $dbh;
	}

# disconnects from ppages db
sub disconnect() {
	$dbh->disconnect;
	}

# perform an SQL command
sub sql() {
	eval {
		$sth = $dbh->prepare($_[0]);
		};
	if ($@) {
		&disconnect();
		print "An error occured while accessing the database.";
		exit;
		}
	else {
		$sth->execute;
		}
	return ($sth);
	}