<?php
set_time_limit(2400);
function url_exists($url) {
    // Version 4.x supported
    $handle   = curl_init($url);
    if (false === $handle)
    {
        return false;
    }
    curl_setopt($handle, CURLOPT_HEADER, false);
    curl_setopt($handle, CURLOPT_FAILONERROR, true);  // this works
    curl_setopt($handle, CURLOPT_HTTPHEADER, Array("User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.15) Gecko/20080623 Firefox/2.0.0.15") ); // request as if Firefox
    curl_setopt($handle, CURLOPT_NOBODY, true);
    curl_setopt($handle, CURLOPT_RETURNTRANSFER, false);
    $connectable = curl_exec($handle);
    curl_close($handle);
    return $connectable;
}

function insertPitchFX($inf, $inning, $state, $gameid, $connection) {
	$ab = $inf->attributes();
	$ab_num = $ab->num;
	$b = $ab->b;
	$s = $ab->s;
	$o = $ab->o;
	$batter = $ab->batter;
	$stand = $ab->stand;
	$b_height = $ab->b_height;
	$pitcher = $ab->pitcher;
	$p_throws = $ab->p_throws;
	$des = $ab->des;
	$event = $ab->event;
	$numPitches = $inf->pitch->count();
	if ($numPitches > 0) {
		if (isset($inf->pitch[0]->attributes()->on_1b)) { $on_1B = $inf->pitch[0]->attributes()->on_1b; } else { $on_1B = ""; }
		if (isset($inf->pitch[0]->attributes()->on_2b)) { $on_2B = $inf->pitch[0]->attributes()->on_2b; } else { $on_2B = ""; }
		if (isset($inf->pitch[0]->attributes()->on_3b)) { $on_3B = $inf->pitch[0]->attributes()->on_3b; } else { $on_3B = ""; }
	} else {
		$on_1B = "";
		$on_2B = "";
		$on_3B = "";
	}
	$SQL = "INSERT INTO `PitchFX`.`AtBats` (`gameid`, `atbat_num`, `inning`, `state`, `b`, `s`, `o`, `batter`, `stand`, `b_height`, `pitcher`, `p_throws`, `des`, `event`, `numPitches`, `on_1B`, `on_2B`, `on_3B`) VALUES ('$gameid', '$ab_num', '$inning', '$state', '$b', '$s', '$o', '$batter', '$stand', '$b_height', '$pitcher', '$p_throws', '$des', '$event', '$numPitches', '$on_1B', '$on_2B', '$on_3B');";
	mysql_query($SQL, $connection);
	foreach ($inf->pitch as $pitch) {
		$p = $pitch->attributes();
		if (isset($p->on_1b)) { $on_1B = $p->on_1b; } else { $on_1B = ""; }
		if (isset($p->on_2b)) { $on_1B = $p->on_2b; } else { $on_2B = ""; }
		if (isset($p->on_3b)) { $on_1B = $p->on_3b; } else { $on_3B = ""; }
		if (isset($p->des)) { $des = $p->des; } else { $des = ""; }
		if (isset($p->id)) { $pitch_id = $p->id; } else { $pitch_id = ""; }
		if (isset($p->type)) { $type = $p->type; } else { $type = ""; }
		if (isset($p->x)) { $x = $p->x; } else { $x = ""; }
		if (isset($p->y)) { $y = $p->y; } else { $y = ""; }
		if (isset($p->start_speed)) { $start_speed = $p->start_speed; } else { $start_speed = ""; }
		if (isset($p->end_speed)) { $end_speed = $p->end_speed; } else { $end_speed = ""; }
		if (isset($p->sz_top)) { $sz_top = $p->sz_top; } else { $sz_top = ""; }
		if (isset($p->sz_bot)) { $sz_bot = $p->sz_bot; } else { $sz_bot = ""; }
		if (isset($p->pfx_x)) { $pfx_x = $p->pfx_x; } else { $pfx_x = ""; }
		if (isset($p->pfx_z)) { $pfx_z = $p->pfx_z; } else { $pfx_z = ""; }
		if (isset($p->px)) { $px = $p->px; } else { $px = ""; }
		if (isset($p->pz)) { $pz = $p->pz; } else { $pz = ""; }
		if (isset($p->x0)) { $x0 = $p->x0; } else { $x0 = ""; }
		if (isset($p->y0)) { $y0 = $p->y0; } else { $y0 = ""; }
		if (isset($p->z0)) { $z0 = $p->z0; } else { $z0 = ""; }
		if (isset($p->vx0)) { $vx0 = $p->vx0; } else { $vx0 = ""; }
		if (isset($p->vy0)) { $vy0 = $p->vy0; } else { $vy0 = ""; }
		if (isset($p->vz0)) { $vz0 = $p->vz0; } else { $vz0 = ""; }
		if (isset($p->ax)) { $ax = $p->ax; } else { $ax = ""; }
		if (isset($p->ay)) { $ay = $p->ay; } else { $ay = ""; }
		if (isset($p->az)) { $az = $p->az; } else { $az = ""; }
		if (isset($p->break_y)) { $break_y = $p->break_y; } else { $break_y = ""; }
		if (isset($p->break_angle)) { $break_angle = $p->break_angle; } else { $break_angle = ""; }
		if (isset($p->break_length)) { $break_length = $p->break_length; } else { $break_length = ""; }
		if (isset($p->pitch_type)) { $pitch_type = $p->pitch_type; } else { $pitch_type = ""; }
		if (isset($p->type_confidence)) { $type_confidence = $p->type_confidence; } else { $type_confidence = ""; }
		if (isset($p->zone)) { $zone = $p->zone; } else { $zone = ""; }
		if (isset($p->nasty)) { $nasty = $p->nasty; } else { $nasty = ""; }
		if (isset($p->spin_dir)) { $spin_dir = $p->spin_dir; } else { $spin_dir = ""; }
		if (isset($p->spin_rate)) { $spin_rate = $p->spin_rate; } else { $spin_rate = ""; }
		if (isset($p->cc)) { $cc = $p->cc; } else { $cc = ""; }
		if (isset($p->mt)) { $mt = $p->mt; } else { $mt = ""; }
		$SQL = "INSERT INTO `PitchFX`.`Pitches` (`gameid`, `pitch_id`, `pitcher`, `batter`, `atbat_num`, `inning`, `state`, `on_1B`, `on_2B`, `on_3B`, `des`, `type`, `x`, `y`, `start_speed`, `end_speed`, `sz_top`, `sz_bot`, `pfx_x`, `pfx_z`, `px`, `pz`, `x0`, `y0`, `z0`, `vx0`, `vy0`, `vz0`, `ax`, `ay`, `az`, `break_y`, `break_angle`, `break_length`, `pitch_type`, `type_confidence`, `zone`, `nasty`, `spin_dir`, `spin_rate`, `cc`, `mt`) VALUES ('$gameid', '$pitch_id', '$pitcher', '$batter', '$ab_num', '$inning', '$state', '$on_1B', '$on_2B', '$on_3B', '$des', '$type', '$x', '$y', '$start_speed', '$end_speed', '$sz_top', '$sz_bot', '$pfx_x', '$pfx_z', '$px', '$pz', '$x0', '$y0', '$z0', '$vx0', '$vy0', '$vz0', '$ax', '$ay', '$az', '$break_y', '$break_angle', '$break_length', '$pitch_type', '$type_confidence', '$zone', '$nasty', '$spin_dir', '$spin_rate', '$cc', '$mt');";
		mysql_query($SQL, $connection);
	}
}

function PitchCount($gameid, $connection) {
	$query = mysql_query("SELECT * FROM PitchFX.AtBats where gameid = '$gameid' group by pitcher", $connection);
	while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
		$numPitches = 0;
		$numBalls = 0;
		$numGroundouts = 0;
		$numLineouts = 0;
		$numFlyouts = 0;
		$numPopouts = 0;
		$numDP = 0;
		$numSO = 0;
		$numWalks = 0;
		$query2 = mysql_query("SELECT * FROM PitchFX.AtBats where gameid = '$gameid' and pitcher = '" . $row['pitcher'] . "'", $connection);
		while ($row2 = mysql_fetch_array($query2, MYSQL_ASSOC)) {
			$numPitches = $numPitches + (int)$row2['numPitches'];
			$numBalls = $numBalls + (int)$row2['b'];
			If ($row2['event'] == "Pop Out") { $numPopouts++; }
			If ($row2['event'] == "Flyout") { $numFlyouts++; }
			If ($row2['event'] == "Lineout") { $numLineouts++; }
			If ($row2['event'] == "Groundout") { $numGroundouts++; }
			If ($row2['event'] == "Grounded Into DP") { $numGroundouts++; $numDP++; }
			If ($row2['event'] == "Walk") { $numWalks++; }
			If ($row2['event'] == "Strikeout") { $numSO++; }
		}
		$numStrikes = $numPitches - $numBalls;
		mysql_query("INSERT INTO `PitchFX`.`PitchCount` (`gameid`, `pitcher`, `pitches`, `strikes`, `balls`,  `groundouts`, `popouts`, `flyouts`, `lineouts`, `doubleplays`, `walks`, `strikeouts`, `innings`) VALUES ('$gameid', '" . $row['pitcher'] . "', '$numPitches', '$numStrikes', '$numBalls', '$numGroundouts', '$numPopouts', '$numFlyouts', '$numLineouts', '$numDP', '$numWalks', '$numSO', '0')", $connection);
	}
}

$connection = mysql_connect("","","");
for ($x = 2; $x < 3; $x++) {
	$year = date("Y", strtotime("-" . $x . " days"));
	$month = date("m", strtotime("-" . $x . " days"));
	$day = date("d", strtotime("-" . $x . " days"));
	echo $x . "!!<BR>";
	$url = "http://gd2.mlb.com/components/game/mlb/year_" . $year . "/month_" . $month . "/day_" . $day . "/master_scoreboard.xml";
	If (url_exists($url) == TRUE) {
		$xml = simplexml_load_file($url);
		foreach ($xml->game as $xml) {
			$gameID = $xml->attributes()->game_pk;
			$gamedayID = $xml->attributes()->gameday;
			$lnk = "http://gd2.mlb.com/components/game/mlb/year_" . $year . "/month_" . $month . "/day_" . $day . "/gid_" . $gamedayID . "/inning/inning_all.xml";
			echo $lnk . "<BR>";
			If (url_exists($lnk) == TRUE) {
				$xfile = simplexml_load_file($lnk);
				foreach ($xfile->inning as $i) {
					$inning = $i->attributes()->num;
					if (isset($i->top)) { foreach ($i->top->atbat as $t) { insertPitchFX($t, $inning, 'T', $gameID, $connection); } }
					if (isset($i->bottom)) { foreach ($i->bottom->atbat as $b) { insertPitchFX($b, $inning, 'B', $gameID, $connection); } }
				}
				PitchCount($gameID, $connection);
			}
		}
	}
}
?>