#! /usr/bin/perl
 
# This script is made freely available for non-commerical use by Mike Fast
# February 2008
# http://fastballs.wordpress.com/
# Attribution is appreciated but not required.
 
# MySQL database connection statement
use DBI;
$dbh = DBI->connect("DBI:mysql:database=pbp;host=localhost", 'root', 'package') 
or die $DBI::errstr;
 
# Get all pitch info from database
$all_pitches_query = 'SELECT pitch_id, ab_id, des, type FROM pitches WHERE (ball IS NULL) ORDER BY pitch_id ASC';
$sth= $dbh->prepare($all_pitches_query) or die $DBI::errstr;
$sth->execute();
 
# Process each pitch and store result in an array
$old_ab_id = 0;
$ball = 0;
$strike = 0;
while ( ($pitch, $ab, $des, $type) = $sth->fetchrow_array( ) )  {
    # Store count from previous pitch into ball/strike hashes for this pitch
    if ($ab > $old_ab_id || $ab < $old_ab_id) {
    $ball = 0;
    $strike = 0;
    } else {
    }
    $store_ball{$pitch} = $ball;
    $store_strike{$pitch} = $strike;
 
    # Process ball, strike, or foul (no strike) for new pitch
    if ("B" eq $type) {
    $ball++;
    }
    if ("S" eq $type) {
    # Do not count a strike if a pitch is fouled off with two strikes in the count
    if (2 == $strike && ("Foul" eq $des || "Foul (Runner Going)" eq $des)) {
        # don't increment
    } else {
        $strike++;
    }
    }
 
    $old_ab_id = $ab;
}
$sth->finish();
 
print "\nFinished accounting for balls and strikes.  Beginning database update...\n";
 
for my $pitch_id (keys %store_ball) {
    my $update_ball = $store_ball{$pitch_id};
    my $update_strike = $store_strike{$pitch_id};
#    print "$pitch_id: $update_ball-$update_strike.\n";
    $update_count_query = 'UPDATE pitches SET ball = ' . $update_ball . ', strike = ' . $update_strike . ' WHERE pitch_id = ' . $pitch_id;
#    print "yo\n";
    $sth= $dbh->prepare($update_count_query) or die $DBI::errstr;
    $sth->execute();
}