#! /usr/bin/perl

# URL TO CONNECT TO
#$url = "http://stats.howesportsdata.com/HOCKEY/ECHGSKED.HTM";
#$fullText = `/usr/local/bin/lynx --source -accept_all_cookies -auth=Augusta%20echl:HCK-531 $url`;

# This is the file from Howe that you need
my $neededfile = 'http://howesportsdata.com/howesportsdata/hockey/echgsked.htm';

# Form the cURL request
my $req = "/data/www/dev/lib/share/howegrabber/bin/howegrabber -u 'Augusta echl' -p 'HCK-531' -l '$neededfile'";
#my $req = "/usr/local/bin/curl -L --silent ".
#	"-d ReturnURL='$neededfile' ".
#	"-d ESPNUser='Augusta echl' ".
#	"-d ESPNPass='HCK-531' ".
#	"'http://stats.howesportsdata.com/howesportsdata/login/dbvalidate.jsp'";

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

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

# INITIALIZE DATABASE CONNECTION
&connect;

# SET GLOBAL ARRAYS FOR FIELDS
$league_id = 1;

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

@lines = split(/\r\n/, $fullText);
foreach $line (@lines) {
	if ($line =~ /HREF/ && $line !~ /Back to/) {
		$line_old = $line; 

		$line =~ s/<LI><A HREF=\"//g;
		$line =~ s/\">//g;
		$line =~ s/<\/A>//g;
		
	print $line."\n";

		$url = substr($line, 0, 47);
		$url =~ s/\"//g;
		$url =~ s/>//g;
		$url = despace($url);
		
	print $url."\n";
		
		$line =~ s/$url//g;
		$line =~ s/SO//g;
		$line =~ s/OT//g;
		$line = despace($line);
		
	print $line."\n";

		$game_count = substr($line,0,3);
		$game_count = despace($game_count);
		$line = substr($line, 3, length($line) -3);
		$line = despace($line);
		
	print $game_count."\n";

		$date = substr($line, 0, 8);
		$date = despace($date);
		$line =~ s/$date//g;
		$line = despace($line);
		
	print $date."\n";
	print $line."\n";
		
		@team_line = split(/\@/, $line);
		$team_line[0] = despace($team_line[0]);
		$team_line[1] = despace($team_line[1]);
		
	print $team_line[0]."\n";
	print $team_line[1]."\n";


		$visitor = substr($team_line[0], 0, length($team_line[0]) -2);
		$visitor = despace($visitor);
		if ($visitor eq "7eoria"){
			$visitor = "Peoria";
		}

		$visitor_score = substr($team_line[0], length($team_line[0]) -2, 2);
		$visitor_score = despace($visitor_score);

		$home = substr($team_line[1], 0, length($team_line[1]) - 2);
		$home = despace($home);
		if ($home eq "7eoria"){
			$home = "Peoria";
		}

		$home_score = substr($team_line[1], length($line[1]) -2, 2);
		$home_score = despace($home_score);

	print $visitor."\n";
	print $visitor_score."\n";
	print $home."\n";
	print $home_score."\n";

		### resolve visitor to id ###
		&sql("select team_id from ho_teams where team_home='$visitor' and league_id='$league_id'");
		$visitor_old = $visitor;
		$visitor = $sth->fetchrow_array;
		
		### resolve home to id ###
		&sql("select team_id from ho_teams where team_home='$home' and league_id='$league_id'");
		$home = $sth->fetchrow_array;
		
		@date = split(/\//, $date);
		$date = "20".$date[2]."-".$date[0]."-".$date[1];

		if ($url && $date && $visitor && $home) {
                        &sql("select id from ho_box_basic where league_id='$league_id' and date='$date' and visitor='$visitor' and home='$home'");
                        $id = $sth->fetchrow_array;
                        if (!$id) {
				&sql("insert into ho_box_basic (id, league_id, home, home_score, visitor, visitor_score, date, url) values ('', '$league_id', '$home', '$home_score', '$visitor', '$visitor_score', '$date', '$url');");
				}
			else {
				print "Game already in database\n";
				}
			}
		else {
			print "$date - $visitor, $home not found...\n";
			}
		}

	}


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

# 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:db1.dfw1.infinityprosports.com", "hockeystats", "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);
	}

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