function postPitches()
{
$selectPhrase = 'pitches.id, balls, strikes, outs, man_on_first, man_on_second, man_on_third, inning, side, visiting_team_current_runs vscore, home_team_current_runs hscore, visitor, home, batted_ball_angle, batted_ball_distance, (unix_timestamp(game_date)-(6*60*60))*1000 game_date, atbat_desc, px x, pz y, szt, szb, pa_result_id, pitch_results.ball, pitch_results.strike, pitch_results.description pitch_desc, plate_appearance_results.atbat, plate_appearance_results.hit, plate_appearance_results.onbase, plate_appearance_results.bases, plate_appearance_results.description pa_desc, pitch_types.description pitch_type, batter.name batter_name, pitches.batter_id, pitcher.name pitcher_name, pitches.pitcher_id, concat(game_string, \'-\', batter_id, \'-\', pitcher_id, \'-\', times_faced) abslug';
if (Input::has('slug') && Input::has('id')) {
$subquery = " (select * from pitches where id between (? - 30) and (? + 30)) ";
$from = " from " . $subquery . " pitches \n left join pitch_results on pitch_results.id = pitches.pitch_result_id \n left join pitch_types on pitch_types.id = pitches.pitch_type_id \n left join plate_appearance_results on plate_appearance_results.id = pitches.pa_result_id \n left join players as pitcher on pitcher.id = pitches.pitcher_id \n left join players as batter on batter.id = pitches.batter_id";
$where = " where concat(game_string, '-', batter_id, '-', pitcher_id, '-', times_faced) = ? order by `pitches`.`id` asc;";
$query = "select " . $selectPhrase . $from . $where;
$pitches = DB::select(DB::raw($query), array(Input::get('id'), Input::get('id'), Input::get('slug')));
return array('view' => 'ab', 'items' => $pitches, 'total_count' => count($pitches));
}
if (!Input::has('batter_id') && !Input::has('pitcher_id')) {
return array('error_code' => 406, 'message' => 'Please include either a batter or a pitcher (or both)');
}
if (!Input::has('pitch_types')) {
return array('error_code' => 406, 'message' => 'Please select at least one pitch type. Come on.');
}
if (!Input::has('pitch_results')) {
return array('error_code' => 406, 'message' => 'Please select at least one pitch result type. Come on.');
}
if (!Input::has('plate_appearance_results')) {
return array('error_code' => 406, 'message' => 'Please select at least one plate appearance result type. Come on.');
}
$pitch_types = array();
foreach (Input::get('pitch_types') as $pt) {
$pitch_types[] = $pt['id'];
}
$pitch_results = array();
foreach (Input::get('pitch_results') as $pt) {
$pitch_results[] = $pt['id'];
}
$plate_appearance_results = array();
foreach (Input::get('plate_appearance_results') as $pt) {
$plate_appearance_results[] = $pt['id'];
}
$all_pa_results_count = PlateAppearanceResult::count();
$pitches = Pitch::whereIn('pitch_type_id', $pitch_types)->whereIn('pitch_result_id', $pitch_results);
if (count($plate_appearance_results) < $all_pa_results_count) {
$pitches = $pitches->whereIn('pa_result_id', $plate_appearance_results);
}
if (Input::has('batter_id')) {
$pitches = $pitches->where('batter_id', Input::get('batter_id'));
}
if (Input::has('pitcher_id')) {
$pitches = $pitches->where('pitcher_id', Input::get('pitcher_id'));
}
if (Input::has('showBalls') && Input::get('showBalls') == '0') {
$pitches = $pitches->where(function ($query) {
$query->where('pitch_results.ball', 0);
$query->orWhere('plate_appearance_results.description', '<>', '');
});
}
if (Input::has('showStrikes') && Input::get('showStrikes') == '0') {
$pitches = $pitches->where(function ($query) {
$query->where('pitch_results.strike', 0);
$query->orWhere('plate_appearance_results.description', '<>', '');
});
}
if (Input::has('showInPlay') && Input::get('showInPlay') == '0') {
$pitches = $pitches->where('plate_appearance_results.description', '');
}
if (!(Input::get('show2013') == '1' && Input::get('show2014') == '1' && Input::get('show2015') == '1')) {
$show2013 = Input::get('show2013');
$show2014 = Input::get('show2014');
$show2015 = Input::get('show2015');
$pitches = $pitches->where(function ($query) use($show2013, $show2014, $show2015) {
if ($show2013 == '1') {
$query->orWhereBetween('game_date', array('2013-1-1', '2014-1-1'));
}
if ($show2014 == '1') {
$query->orWhereBetween('game_date', array('2014-1-1', '2015-1-1'));
}
if ($show2015 == '1') {
$query->orWhereBetween('game_date', array('2015-1-1', '2016-1-1'));
}
});
}
$pitches = $pitches->leftJoin('pitch_results', 'pitch_results.id', '=', 'pitches.pitch_result_id')->leftJoin('pitch_types', 'pitch_types.id', '=', 'pitches.pitch_type_id')->leftJoin('plate_appearance_results', 'plate_appearance_results.id', '=', 'pitches.pa_result_id')->leftJoin('players as pitcher', 'pitcher.id', '=', 'pitches.pitcher_id')->leftJoin('players as batter', 'batter.id', '=', 'pitches.batter_id')->selectRaw($selectPhrase)->orderBy('pitches.id')->take(1000)->get();
return array('items' => $pitches, 'total_count' => count($pitches));
}
function getTeams()
{
$teams = Pitch::selectRaw('distinct visitor')->get();
return $teams;
}
public function run()
{
$there_is_more = RawDatum::select('id')->whereNull('processed_utc')->first();
if ($there_is_more) {
$raw_data = RawDatum::whereNull('processed_utc')->take(4000)->get();
foreach ($raw_data as $raw_datum) {
$batter = Player::where('mlb_id', $raw_datum->batter_id)->first();
if (!$batter) {
$batter = new Player();
$batter->mlb_id = $raw_datum->batter_id;
$batter->name = $raw_datum->batter;
}
$batter->batter_hand = $raw_datum->batter_hand;
$batter->timestamp_utc = time();
$batter->save();
$pitcher = Player::where('mlb_id', $raw_datum->pitcher_id)->first();
if (!$pitcher) {
$pitcher = new Player();
$pitcher->mlb_id = $raw_datum->pitcher_id;
$pitcher->name = $raw_datum->pitcher;
}
$pitcher->pitcher_hand = $raw_datum->pitcher_hand;
$pitcher->timestamp_utc = time();
$pitcher->save();
$catcher = Player::where('mlb_id', $raw_datum->catcher_id)->first();
if (!$catcher) {
$catcher = new Player();
$catcher->mlb_id = $raw_datum->catcher_id;
$catcher->name = $raw_datum->catcher;
}
$catcher->timestamp_utc = time();
$catcher->save();
$pitch_result = PitchResult::where('slug', $raw_datum->pitch_result)->first();
if (!$pitch_result) {
$pitch_result = new PitchResult();
$pitch_result->slug = $raw_datum->pitch_result;
$pitch_result->timestamp_utc = time();
$pitch_result->save();
}
$pitch_type = PitchType::where('slug', $raw_datum->pitch_type)->first();
if (!$pitch_type) {
$pitch_type = new PitchType();
$pitch_type->slug = $raw_datum->pitch_type;
$pitch_type->timestamp_utc = time();
$pitch_type->save();
}
$plate_appearance_result = PlateAppearanceResult::where('slug', $raw_datum->pa_result)->first();
if (!$plate_appearance_result) {
$plate_appearance_result = new PlateAppearanceResult();
$plate_appearance_result->slug = $raw_datum->pa_result;
$plate_appearance_result->timestamp_utc = time();
$plate_appearance_result->save();
}
$batted_ball_type = BattedBallType::where('slug', $raw_datum->batted_ball_type)->first();
if (!$batted_ball_type) {
$batted_ball_type = new BattedBallType();
$batted_ball_type->slug = $raw_datum->batted_ball_type;
$batted_ball_type->timestamp_utc = time();
$batted_ball_type->save();
}
$pitch = Pitch::where('raw_data_id', $raw_datum->id)->first();
if (!$pitch) {
$pitch = new Pitch();
$pitch->raw_data_id = $raw_datum->id;
}
$pitch->season_year = $raw_datum->season_year;
$pitch->game_string = $raw_datum->game_string;
$pitch->game_date = $raw_datum->game_date;
$pitch->game_type = $raw_datum->game_type;
$pitch->visitor = $raw_datum->visitor;
$pitch->home = $raw_datum->home;
$pitch->visiting_team_final_runs = $raw_datum->visiting_team_final_runs;
$pitch->home_team_final_runs = $raw_datum->home_team_final_runs;
$pitch->inning = $raw_datum->inning;
$pitch->side = $raw_datum->side;
if ($batter->id == 0 || $pitcher->id == 0) {
dd(array('raw_data' => $raw_datum, 'batter' => $batter, 'pitcher' => $pitcher));
}
$pitch->batter_id = $batter->id;
$pitch->pitcher_id = $pitcher->id;
$pitch->inning = $raw_datum->inning;
$pitch->catcher_id = $catcher->id;
$pitch->times_faced = $raw_datum->times_faced;
$pitch->batter_pos = $raw_datum->batter_pos;
$position = Position::where('abbr', $raw_datum->batter_pos)->first();
if ($position) {
$player_position = PlayerPosition::where('player_id', $batter->id)->where('position_id', $position->id)->first();
if (!$player_position) {
$player_position = new PlayerPosition();
$player_position->player_id = $batter->id;
$player_position->position_id = $position->id;
$player_position->save();
}
}
$pitch->balls = $raw_datum->balls;
$pitch->strikes = $raw_datum->strikes;
$pitch->outs = $raw_datum->outs;
$pitch->man_on_first = $raw_datum->man_on_first == "TRUE";
$pitch->man_on_second = $raw_datum->man_on_second == "TRUE";
$pitch->man_on_third = $raw_datum->man_on_third == "TRUE";
$pitch->end_man_on_first = $raw_datum->end_man_on_first == "TRUE";
$pitch->end_man_on_second = $raw_datum->end_man_on_second == "TRUE";
$pitch->end_man_on_third = $raw_datum->end_man_on_third == "TRUE";
$pitch->visiting_team_current_runs = $raw_datum->visiting_team_current_runs;
$pitch->home_team_current_runs = $raw_datum->home_team_current_runs;
$pitch->pitch_result_id = $pitch_result->id;
$pitch->pitch_type_id = $pitch_type->id;
$pitch->release_velocity = $raw_datum->release_velocity;
$pitch->spin_rate = $raw_datum->spin_rate;
$pitch->spin_direction = $raw_datum->spin_direction;
$pitch->px = $raw_datum->px;
$pitch->pz = $raw_datum->pz;
$pitch->szt = $raw_datum->szt;
$pitch->szb = $raw_datum->szb;
$pitch->x0 = $raw_datum->x0;
$pitch->y0 = $raw_datum->y0;
$pitch->z0 = $raw_datum->z0;
$pitch->vx0 = $raw_datum->vx0;
$pitch->vy0 = $raw_datum->vy0;
$pitch->vz0 = $raw_datum->vz0;
$pitch->ax = $raw_datum->ax;
$pitch->ay = $raw_datum->ay;
$pitch->az = $raw_datum->az;
$pitch->pa_result_id = $plate_appearance_result->id;
$pitch->runs_home = $raw_datum->runsHome;
$pitch->batted_ball_type_id = $batted_ball_type->id;
$pitch->batted_ball_angle = $raw_datum->batted_ball_angle;
$pitch->batted_ball_distance = $raw_datum->batted_ball_distance;
$pitch->atbat_desc = $raw_datum->atbat_desc;
$pitch->timestamp_utc = time();
if (!$pitch->save()) {
dd($raw_datum);
}
if ($raw_datum->id % 1000 == 0) {
echo "Processed record number " . $raw_datum->id . "\n\r";
}
$raw_datum->processed_utc = time();
$raw_datum->save();
}
}
}