#! /usr/bin/perl

### database connect ###
$DATABASE = "stats_hockey";
&connect;

### vars ###
$league_id = 1;

&sql2("update `ho_box_basic` set full=0 WHERE period_1='' and date>'2004-11-01' and league_id=1;");

&sql2("select id, url, home, visitor from ho_box_basic where full<>'1' and league_id='$league_id';");
$total = $sth2->rows;


while (@data = $sth2->fetchrow_array) {
	$id = $data[0];
	$url = $data[1];
	$home = $data[2];
	$visitor = $data[3];

	&sql("delete from ho_box_players where box_id='$id'");
	&sql("delete from ho_box_goal where box_id='$id'");

	$count++;
	print "Adding record $count of $total ($id)... ";

	# This is the file from Howe that you need
	my $neededfile = 'http://howesportsdata.com/'.$url;
	
	# 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;


	@lines = split(/\r\n/, $fullText);

	$p1 = "";
	$p2 = "";
	$p3 = "";
	$p4 = "";
	$h_p = "";
	$v_p = "";
	$pp = "";
	$h_sog = "";
	$v_sog = "";
	$flag_pp = 0;

	foreach $line (@lines) {
print $line;
		$line =~ s/\'/\\\'/g;

		if ($line =~ /FIRST PERIOD --/) { $flag_p1 = 1; }
		if ($line =~ /SECOND PERIOD --/) { $flag_p2 = 1; }
		if ($line =~ /THIRD PERIOD --/) { $flag_p3 = 1; }
		if ($line =~ /OVERTIME --/) { $flag_p4 = 1; }
		if ($line =~ /Power Play Conversions/) { $flag_pp = 1; }

		### add to pp data ###
		if ($flag_pp) {
			if (length($line) < 1) { $flag_pp = 0; }
			else {
				$pp .= $line." ";
				}
			}

		### add to p1 data ###
		if ($flag_p1) {
			if (length($line) < 1) { $flag_p1 = 0; }
			else {
				$p1 .= $line." ";
				}
			}

		### add to p2 data ###
		if ($flag_p2) {
			if (length($line) < 1) { $flag_p2 = 0; }
			else {
				$p2 .= $line." ";
				}
			}

		### add to p3 data ###
		if ($flag_p3) {
			if (length($line) < 1) { $flag_p3 = 0; }
			else {
				$p3 .= $line." ";
				}
			}

		### add to overtime data ###
		if ($flag_p4) {
			if (length($line) < 1) { $flag_p4 = 0; }
			else {
				$p4 .= $line." ";
				}
			}

		### period score data ###
		if ($flag_p) {
			if (length($line) < 1) { $flag_p = 0; }
			else {
				$scores = substr($line, 27, length($line) - 27);
				if (!$v_p) { $v_p = $scores; }
				else { $h_p = $scores; }
				}
			}

		### visitor player stats ###
		if ($flag_pvis) {
			if ($line =~ /BENCH/) { $flag_pvis = 0; }
			else {
				parsePlayerLine($line, "0");
				$vis_players = 1;
				}
			}

		### home player stats ###
		if ($flag_phom) {
			if ($line =~ /BENCH/) { $flag_phom = 0; }
			else {
				parsePlayerLine($line, "1");
				$home_players = 1;
				}
			}

		### home goal stats ###
		if ($flag_ghom) {
			if ($line =~ /TOTALS/) { $flag_ghom = 0; }
			else {
				parseGoalLine($line, "1");
				}
			}


		### visitor goal stats ###
		if ($flag_gvis) {
			if ($line =~ /TOTALS/) { $flag_gvis = 0; }
			else {
				parseGoalLine($line, "0");
				}
			}

		### shots on goal stats ###
		if ($flag_sog) {
			if (length($line) < 2) { $flag_sog = 0; }
			else {
				$scores = substr($line, 27, length($line) - 27);
				if (!$v_sog) { $v_sog = $scores; }
				else { $h_sog = $scores; }
				}
			}

		if ($line =~ /SCORE BY PERIODS/) { $flag_p = 1; }
		if ($line =~ /SHOTS ON GOAL/) { $flag_sog = 1; }
		if ($line =~ /G  A/ && $line =~ /VISITING/) { $flag_pvis = 1; }
		if ($line =~ /G  A/ && $line =~ /HOME/) { $flag_phom = 1; }

		if ($line =~ /S1  S2/ && $line =~ /VISITING/) { $flag_gvis = 1; }
		if ($line =~ /S1  S2/ && $line =~ /HOME/) { $flag_ghom = 1; }

		}
	print " done\n";
	if ($home_players && $vis_players){
		$setfull = 1;
		}
	&sql("update ho_box_basic set pp='$pp', h_sog='$h_sog', v_sog='$v_sog', full='$setfull', period_1='$p1', period_2='$p2', period_3='$p3', overtime='$p4', home_p='$h_p', visitor_p='$v_p' where id='$id';");
	$setfull = 0;
	}


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

sub parseGoalLine() {
	my $line = $_[0];

	$name = substr($line, 0, 23);
	$name = despace($name);

	$d = substr($line, 23, 3);
	$d = despace($d);

	$min = substr($line, 26, 8);
	$min = despace($min);

	$ga = substr($line, 35, 3);
	$ga = despace($ga);

	$en = substr($line, 39, 3);
	$en = despace($en);

	$s1 = substr($line, 42, 4);
	$s1 = despace($s1);

	$s2 = substr($line, 46, 4);
	$s2 = despace($s2);

	$s3 = substr($line, 50, 4);
	$s3 = despace($s3);

	$ot = substr($line, 54, 5);
	$ot = despace($ot);

	$sot = substr($line, 59, 5);
	$sot = despace($sot);

	$sql = "insert into ho_box_goal (id, box_id, home, d, min, ga, en, s1, s2, s3, ot, sot, name) values ('', '$id', '$_[1]', '$d', '$min', '$ga', '$en', '$s1', '$s2', '$s3', '$ot', '$sot', '$name');";
	
	&sql($sql);

#	print "($_[1])($name)($d)($min)($ga)($en)($s1)($s2)($s3)($ot)($sot)\n";
	}

sub parsePlayerLine() {
	my $line = $_[0];

	$player = substr($line, 0, 23);
	$player = despace($player);

	$g = substr($line, 23, 3);
	$g = despace($g);

	$a = substr($line, 26, 3);
	$a = despace($a);

	$pm = substr($line, 30, 4);
	$pm = despace($pm);

	$sh = substr($line, 35, 3);
	$sh = despace($sh);

	$pim = substr($line, 40, 3);
	$pim = despace($pim);

	$min = substr($line, 45, 3);
	$min = despace($min);

	$sql = "insert into ho_box_players (id, box_id, home, player, g, a, sh, pm, pim, min) values ('', '$id', '$_[1]', '$player', '$g', '$a', '$sh', '$pm', '$pim', '$min');";
	&sql($sql);

	}

# 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);
	}