#!/usr/bin/perl -w # AUTHORS # Sean Forman # Hans Van Slooten # $Date: 2017-03-13 16:08:21 -0400 (Mon, 13 Mar 2017) $ # $Author: sean $ # $Rev: 49867 $ # $HeadURL: http://svn.sports-reference.com/svn/br_repos/br/trunk/leagues/scripts/daily.fcgi $ # Copyright 2000-2017, SPORTS REFERENCE, LLC. All rights reserved. use Carp; use CGI::Fast qw(:standard); use FindBin qw($Bin); use Modern::Perl '2010'; # Include our local and global SR modules. use lib "$Bin/../lib"; use SR::Cache; use SR::Defaults; use SR::Formatting; use SR::Statline; use SRlocal::Constants; use SRlocal::DB; use SRlocal::Defaults; use SRlocal::Formatting; use SRlocal::Stats; use SRlocal::Templates; our $IP_per_game_qual = 0.5; our $PA_per_game_qual = 2.0; $STATLINE_DEFAULTS{inplay_gb_total}{header} = 'GB/FB'; chomp($0); #### [] Starting: "$Bin/$0 " . join(' ',@ARGV) MAIN: { # Connect to the database. our $dbh = get_site_db_connection($Bin); our $site_params_ref = get_site_params($Bin); our $srtemplates = new SRlocal::Templates; $site_params_ref->{serve_as_cgi_script} = $TRUE; ##################################################################################### ##################################################################################### # set up memcached and fastcgi $dbh->{mysql_auto_reconnect} = $TRUE; our $memd = get_memcached_connection($Bin); LOOPER: while ( my $q = new CGI::Fast ) { # build a unique id for this page's data, and retrieve from # mysql or memcached if available. Print if it is returned, # build if not and then add to cache. my $memd_key = $ENV{'REQUEST_URI'}; my $page_data = SR::Cache::get_data_from_cache( { dbh => $dbh, memd => $memd, key => $memd_key } ); ################################################################### # check to see if we got a result from the cache ################################################################### if ( is_nonempty($page_data) && !is_empty_zero( $q->param('bust_cache') ) ) { print "SR-SRC: memcached\n"; print $q->header( -type => 'text/html', -charset => 'utf-8' ); print $page_data; next LOOPER; } else { print "SR-SRC: build\n"; } my $params_ref = _read_and_set_script_params( $dbh, $q ); my $url = '/leagues/daily.fcgi'; my @yah = ( 'Leagues', qq{Daily Gamelog Finder}, ); my %model = ( header => { page_title => 'Daily Gamelog Finder', you_are_here => generate_you_are_here( \@yah ), page_url => $SRlocal::Constants::SITE_URL . $url, page_label => '###FIXME###', page_description => '###FIXME###', qi_section => 'leagues', }, description => $params_ref->{title}, form_id => 'daily_logs', form_output => _get_selection_form( $dbh, defined $q->param('type') ), ); if ( defined $q->param('type') ) { # get the sth and the stats array. sth is the first entry # returned and the list of stats follows. my @stats = _get_sth_and_stats_array( $dbh, $params_ref, \%model ); my $sth = shift(@stats); # See if our query returned any rows. If it didn't, we're done. if ( $sth->rows ) { $params_ref->{rows} = $sth->rows; $model{results_output} = _get_query_results( $dbh, $sth, $params_ref, \@stats ); } else { $model{results_output} = qq{

Sorry no results for this request

\n}; } } ## Output the file $page_data = $srtemplates->process( 'Pages/PlayIndex.tt2', \%model ); ## Stuff the page into our cache. SR::Cache::put_data_in_cache( { dbh => $dbh, memd => $memd, key => $memd_key, data => $page_data, } ); print $q->header( -type => 'text/html', -charset => 'utf-8' ); print $page_data; } # close up LOOPER $dbh->disconnect; } ################################################################### # Usage : # Purpose : # Returns : # Parameters : # Throws : ################################################################### sub _get_query_results { my ( $dbh, $sth, $ref, $stats_ref ) = @_; my $return = $EMPTY_STR; # We have some rows, so moving on... # Set the table's id and class. my $tmp_blank; $tmp_blank->{LINE_class} = 'spacer partial_table'; my $table_id = 'daily'; my $table_title = 'Query Results'; my %table_defn = ( table_id => $table_id, title => $table_title, caption => $table_title, table_cols_to_freeze => 2, comment_by_default => $FALSE, hide_long => $TRUE, include_non_qual_switch => $ref->{filter_low_playing_time}, qual_label_text => "Hide non-qualifiers for rate stats" . ( $ref->{type} eq 'p' ? sprintf( qq{ (%.1f IP/estim. TmG)}, $IP_per_game_qual ) : sprintf( qq{ (%.1f PA/estim. TmG)}, $PA_per_game_qual ) ), ); my $table = generate_statline_header_wrap( \%table_defn ); ## First Headers $table .= generate_statline_header( $stats_ref, { suppress_all_ids_as_headers => $TRUE } ); my $line_count = 0; my $last_blank_diff = 0; # Loop through the query results. while ( my $tmp = $sth->fetchrow_hashref ) { # Add in blank lines when appropriate. if ( $line_count && ( $line_count % 25 == 0 ) && ( $ref->{rows} - $line_count >= 10 ) ) { $table .= generate_statline_header( $stats_ref, { suppress_thead_output => $TRUE, suppress_all_ids_as_headers => $TRUE } ); } # output a blank row if appropriate. if ( is_nonempty( $ref->{blank_diff} ) && $last_blank_diff && ( is_empty_zero( $tmp->{ $ref->{blank_diff} } ) ne $last_blank_diff ) ) { $table .= generate_statline( $stats_ref, $tmp_blank ); } $last_blank_diff = is_empty_zero( $tmp->{ $ref->{blank_diff} } ); $line_count++; $tmp->{ranker} = $line_count; # add non-qual rows as needed. The threshold is set # earlier in the script in _read_and_set_params. we have # to check the type of output and then also the IPouts or # PA vs our set thresholds. if ( $ref->{filter_low_playing_time} ) { $tmp->{LINE_class} = 'non_qual' if ( ( ( $ref->{type} eq 'p' ) && ( $tmp->{IP} < $ref->{filter_threshold_IP} ) ) || ( ( $ref->{type} eq 'b' ) && ( $tmp->{PA} < $ref->{filter_threshold_PA} ) ) ); } # Use Statline to add the season stat lines. $table .= generate_statline( $stats_ref, $tmp ); } ## End Table $table .= generate_statline_footer_wrap( \%table_defn ); return scalar $table; } ################################################################### # Usage : # Purpose : # Returns : # Parameters : # Throws : ################################################################### sub _get_selection_form { my ( $dbh, $have_request ) = @_; my $return = $EMPTY_STR; my $first_of_this_season = `date -d "today " +%Y-03-01`; my $first_of_this_month = `date -d "today " +%Y-%m-01`; my $first_of_last_month = `date -d "today -1 month" +%Y-%m-01`; my $last_of_this_month = `date -d "+1 month $first_of_this_month -1 day" +%Y-%m-%d`; my $last_of_last_month = `date -d " $first_of_this_month -1 day" +"%Y-%m-%d"`; my $this_month = `date -d "today " +%B`; my $last_month = `date -d "today -1 month" +%B`; chomp($first_of_this_season); chomp($first_of_this_month); chomp($first_of_last_month); chomp($last_of_this_month); chomp($last_of_last_month); chomp($this_month); chomp($last_month); my $sunday = `date -d date -d "last-sunday" +%Y-%m-%d`; my $prev_sunday = `date -d "sunday-14 days" +%Y-%m-%d`; my $sunday_clean = `date -d "last-sunday" +"%b %e"`; my $prev_sunday_clean = `date -d "sunday-14 days" +"%b %e"`; chomp($sunday_clean); chomp($sunday); chomp($prev_sunday); chomp($prev_sunday_clean); my $saturday = `date -d date -d "last-saturday" +%Y-%m-%d`; my $prev_saturday = `date -d "saturday-14 days" +%Y-%m-%d`; my $saturday_clean = `date -d "last-saturday" +"%b %e"`; my $prev_saturday_clean = `date -d "saturday-14 days" +"%b %e"`; chomp($saturday_clean); chomp($saturday); chomp($prev_saturday); chomp($prev_saturday_clean); my $monday = `date -d "last-monday" +%Y-%m-%d`; my $prev_monday = `date -d "monday-14 days" +%Y-%m-%d`; my $monday_clean = `date -d "last-monday" +"%b %e"`; my $prev_monday_clean = `date -d "monday-14 days" +"%b %e"`; chomp($monday_clean); chomp($monday); chomp($prev_monday); chomp($prev_monday_clean); my $sunday_to_saturday_clean = sprintf qq{%s to %s}, $prev_sunday_clean, $saturday_clean; my $monday_to_sunday_clean = sprintf qq{%s to %s}, $prev_monday_clean, $sunday_clean; ## Get the past ASG my $sql = <<"..."; SELECT year_game, date_game FROM tgl_as ORDER BY date_game DESC limit 1 ... my @all_star_games; my $sth = db_prepare_and_execute( $dbh, $sql ); while ( my $row = $sth->fetchrow_hashref ) { $row->{date_name} = convert_sql_date_to_full_date( $row->{date_game}, { type => 'mmm dd' } ); push @all_star_games, $row; } my $display_form = $have_request ? 'hasrequest' : 'open'; ## Build up the form $return = <<"END_HTML";
Batting Gamelogs
Pitching Gamelogs

Dates

Yesterday
Last Days
Since
During

Level

Franchise

Only 40-man Roster

Additional Criteria

END_HTML my $old = <<"END_HTML"; ≥
 
END_HTML return scalar $return; } ################################################################### # Usage : _read_and_set_script_params($q) # Purpose : sets up a hash with all of the potential parameters we # have for this scripts. Also sets a title and any other needed # notes. # Returns : a hash # Parameters : $q a new cgi query string parameter # Throws : ################################################################### sub _read_and_set_script_params { my ( $dbh, $q ) = @_; my $ref; # this is a reference to the where clauses we are adding on as we # go. $ref->{title} = $EMPTY_STR; my @where = ('(1 > 0)'); my @having = ('(1 > 0)'); $ref->{where_ref} = \@where; $ref->{having_ref} = \@having; my @b = ('inplay_fb_total'); $ref->{delete_columns} = \@b; $ref->{orderby} = 'player_csk ASC'; $ref->{filter_low_playing_time} = $FALSE; $ref->{blank_diff} = $FALSE; ################################################################ # Note that for the various parameters belowe are listing the # default value first and then the other values in our listing. ################################################################ ################################################################ # set the level of interest # level=mlb||all||milb if ( defined $q->param('level') && ( $q->param('level') =~ m|^all$| ) ) { $ref->{title} .= 'Major and minor league '; } elsif ( defined $q->param('level') && ( $q->param('level') =~ m|^milb$| ) ) { push( @{ $ref->{where_ref} }, qq{(classification!='MLB')} ); $ref->{title} .= 'Minor league '; } elsif ( defined $q->param('level') && ( $q->param('level') =~ m/^(aaa|aa|a|lowa|higha|rookie)$/ ) ) { my %class = ( aaa => 'AAA', aa => 'AA', a => 'A', lowa => 'A-', higha => 'A+', rookie => 'Rk' ); my %class_name = ( aaa => 'AAA', aa => 'AA', a => 'A', lowa => 'Short-Season A', higha => 'High-A', rookie => 'Rookie' ); push( @{ $ref->{where_ref} }, sprintf( qq{(classification=%s)}, $dbh->quote( $class{ $q->param('level') } ) ) ); $ref->{title} .= $class_name{ $q->param('level') } . $SPACE; } else { push( @{ $ref->{where_ref} }, qq{(classification='MLB')} ); $ref->{title} .= 'Major league '; push( @{ $ref->{delete_columns} }, 'affiliation' ); # check to see if we want all of the majors or the NL only if ( defined $q->param('level') && ( $q->param('level') =~ m/^mlb-([an]l)$/ ) ) { my $lg = uc($1); push( @{ $ref->{where_ref} }, sprintf( qq{(register_leagues.name_code='%s')}, $lg ) ); $ref->{title} .= qq{ ($lg) }; } } ################################################################ # set the batting or pitching type. # type=b||p if ( defined $q->param('type') && ( $q->param('type') =~ m|^p| ) ) { $ref->{type} = 'p'; $ref->{title} .= 'pitching logs '; } else { $ref->{type} = 'b'; $ref->{title} .= 'batting logs '; } ################################################################ # set the user_team # user_team=NNNN if ( defined $q->param('user_team') && ( $q->param('user_team') =~ m|^([0-9]+)$| ) ) { my $team = $1; push( @{ $ref->{where_ref} }, sprintf( qq{(register_people.milbID IN (SELECT distinct milbID FROM SR_user_team WHERE SR_user_team.team_ID=%s))}, $dbh->quote($team) ) ); } ################################################################ # set the franchise when we want particular players # franch=(BOS||NYY||...||WSN)&is40=(0||1) if ( defined $q->param('franch') && ( $q->param('franch') =~ m|^([A-Z][A-Z][A-Z])$| ) ) { my $franch = $1; if ( defined $q->param('is40') && ( $q->param('is40') ) ) { $ref->{title} .= 'for players on the ' . $SRlocal::Constants::FRANCHISE_FULL_NAME_OF{$franch} . ' 40-man roster '; push( @{ $ref->{where_ref} }, sprintf( qq{(register_people.key_mlbam IN (SELECT distinct mlb_ID FROM yrCurRoster40 WHERE age=0 AND franch_ID=%s))}, $dbh->quote( $franch ) ) ); push( @{ $ref->{delete_columns} }, 'affiliation' ); } else { $ref->{title} .= 'for players in the ' . $SRlocal::Constants::FRANCHISE_FULL_NAME_OF{$franch} . ' system '; push( @{ $ref->{where_ref} }, sprintf( qq{(register_people.key_mlbam IN (SELECT distinct mlb_ID FROM yrCurBio WHERE org_abb_mlb=%s))}, $dbh->quote( $SRlocal::Constants::BR_FRANCH_ID_TO_MLBAM_ORG_ABB{$franch} ) ) ); } } ################################################################ # set the dates of interest # dates=yesterday||fromandto||lastndays||since if ( defined $q->param('dates') && ( $q->param('dates') =~ m|^lastndays$| ) ) { my $days = is_empty( $q->param('lastndays') ) ? '7' : $q->param('lastndays'); $ref->{title} .= sprintf qq{for the last %d days (%s to %s)}, $days, SR::Defaults::get_date( { days_ago => $days, style => 'en_us' } ), SR::Defaults::get_date( { days_ago => 1, style => 'en_us' } ) ; push( @{ $ref->{where_ref} }, sprintf qq{(date_game>=%s)}, $dbh->quote( SR::Defaults::get_date( { days_ago => $days, style => 'sql' } ) ) ); push( @{ $ref->{delete_columns} }, 'box' ); push( @{ $ref->{delete_columns} }, 'opp_ID' ); push( @{ $ref->{delete_columns} }, 'game_score' ); push( @{ $ref->{delete_columns} }, 'date_game' ); push( @{ $ref->{delete_columns} }, 'homeORvis' ); # for filtering we cap this at essentially 100 days my $days_filter = $days > 100 ? 100 : $days; $ref->{filter_low_playing_time} = $TRUE if ( $days_filter >= 3 ); $ref->{filter_threshold_PA} = ( $PA_per_game_qual * $days_filter * .90 ) if ( $days_filter >= 3 ); $ref->{filter_threshold_IP} = ( $IP_per_game_qual * $days_filter * .90 ) if ( $days_filter >= 3 ); } elsif ( defined $q->param('dates') && ( $q->param('dates') =~ m|^since$| ) ) { my $since = is_empty( $q->param('since') ) || ( $q->param('since') !~ /^[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$/ ) ? $CURRENT_YEAR . '-04-01' : $q->param('since'); my $date_fmt = "%b %e"; if ( $since !~ /^$CURRENT_YEAR-/ ) { $date_fmt = "%b %e, %Y"; } my $date_clean = `date -d "$since" +"$date_fmt"`; chomp($date_clean); $ref->{title} .= qq{since $date_clean}; push( @{ $ref->{where_ref} }, sprintf qq{(date_game>=%s)}, $dbh->quote($since) ); push( @{ $ref->{delete_columns} }, 'box' ); push( @{ $ref->{delete_columns} }, 'opp_ID' ); push( @{ $ref->{delete_columns} }, 'game_score' ); push( @{ $ref->{delete_columns} }, 'date_game' ); push( @{ $ref->{delete_columns} }, 'homeORvis' ); my $days = ( `date +%s -dtoday` - `date +%s -d$since` ) / 86400; my $days_filter = $days > 100 ? 100 : $days; $ref->{filter_low_playing_time} = $TRUE if ( $days_filter >= 3 ); $ref->{filter_threshold_PA} = ( $PA_per_game_qual * $days_filter * .90 ) if ( $days_filter >= 3 ); $ref->{filter_threshold_IP} = ( $IP_per_game_qual * $days_filter * .90 ) if ( $days_filter >= 3 ); } elsif ( defined $q->param('dates') && ( $q->param('dates') =~ m|^fromandto$| ) ) { my ( $from, $to ) = ( $q->param('fromandto') =~ /^([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])\.([0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])$/ ); $from = is_empty($from) ? $CURRENT_YEAR . '-04-01' : $from; $to = is_empty($to) ? $CURRENT_YEAR . '-04-07' : $to; my $date_from_clean = `date -d "$from" +"%b %e"`; my $date_to_clean = `date -d "$to" +"%b %e"`; chomp($date_from_clean); chomp($date_to_clean); $ref->{title} .= qq{from $date_from_clean to $date_to_clean}; push( @{ $ref->{where_ref} }, sprintf qq{(date_game>=%s)}, $dbh->quote($from) ); push( @{ $ref->{where_ref} }, sprintf qq{(date_game<=%s)}, $dbh->quote($to) ); push( @{ $ref->{delete_columns} }, 'box' ); push( @{ $ref->{delete_columns} }, 'opp_ID' ); push( @{ $ref->{delete_columns} }, 'game_score' ); push( @{ $ref->{delete_columns} }, 'date_game' ); push( @{ $ref->{delete_columns} }, 'homeORvis' ); my $days = ( `date +%s -d$from` - `date +%s -d$to` ) / 86400; my $days_filter = $days > 100 ? 100 : $days; $ref->{filter_low_playing_time} = $TRUE if ( $days_filter >= 3 ); $ref->{filter_threshold_PA} = ( $PA_per_game_qual * $days_filter * .90 ) if ( $days_filter >= 3 ); $ref->{filter_threshold_IP} = ( $IP_per_game_qual * $days_filter * .90 ) if ( $days_filter >= 3 ); # TODO } else { # default is yesterday push( @{ $ref->{where_ref} }, sprintf qq{(date_game=%s)}, $dbh->quote( SR::Defaults::get_date( { days_ago => 1, style => 'sql' } ) ) ); $ref->{orderby} = $ref->{type} eq 'b' ? 'game_score DESC, player_csk ASC' : 'game_score DESC, W DESC, S DESC, L DESC, GS DESC'; $ref->{title} .= sprintf qq{for yesterday's games (%s)}, SR::Defaults::get_date( { days_ago => 1, style => 'en_us' } ); push( @{ $ref->{delete_columns} }, 'date_game' ); push( @{ $ref->{delete_columns} }, 'RATE_STATS' ); push( @{ $ref->{delete_columns} }, 'G' ); push( @{ $ref->{delete_columns} }, 'days_since_last_played' ); } ################################################################ # set the stat, note that we always choose STAT_ID >= NNN # stat=STAT_ID&stat_value=NNN my $type = $ref->{type}; if ( defined $q->param('stat') && ( $q->param('stat') =~ m|^[A-z_0-9:]+$| ) && ( $q->param('stat') =~ m|^$type:[A-z_0-9]+$| ) && defined $q->param('stat_value') && ( $q->param('stat_value') =~ m|^[0-9]+$| ) ) { my $stat = $q->param('stat'); $stat =~ s/^.://; my $stat_value = $q->param('stat_value'); push( @{ $ref->{having_ref} }, sprintf( qq{($stat >= %d)}, $stat_value ) ); $ref->{orderby} = "$stat DESC, classification_csk asc, level ASC, game_score DESC"; $ref->{blank_diff} = 'classification_csk'; # TODO, check this, and add form control $ref->{title} .= sprintf( qq{ where %s ≥ %d }, $SRlocal::Stats::STATLINE_DEFAULTS{$stat}{name}, $stat_value ); } return scalar $ref; } ################################################################### # Usage : # Purpose : # Returns : # Parameters : # Throws : ################################################################### sub _get_sth_and_stats_array { my ( $dbh, $ref, $model ) = @_; my $query; my @exec_vars = (); my @stats = (); my @standard_stats = qw(ranker player gl box age days_since_last_played level date_game team_ID affiliation homeORvis opp_ID G); my @standard_stats_end = qw(); my $classification_csk = '(1 > 0)'; $classification_csk = $SRlocal::Stats::defn_of_stat{register_classification_csk}{sum}; my $where = join( ' AND ', @{ $ref->{where_ref} } ); my $having = join( ' AND ', @{ $ref->{having_ref} } ); my $gl_sql = <<"END_SQL"; 'gl' as gl, 'Gamelogs' as gl_link_title, '_blank' as gl_link_target, CONCAT('/minors/player.fcgi?id=',register_people.milbID,'&type=$ref->{type}gl') AS gl_link END_SQL my $box_sql = <<"END_SQL"; 'box' as box, 'Boxscore' as box_link_title, '_blank' as box_link_target, CONCAT('/redirect.fcgi?box=1&mlb_ID=',game_id_mlbam) AS box_link END_SQL my $orderby = $ref->{orderby}; if ( $ref->{type} eq 'p' ) { $query = <<"END_SQL"; SELECT $defn_of_statlines{player_name_mlbam}{select}, $defn_of_statlines{pitching_gamelogs_milb}{select_sum}, (IF(birth_baseball,MAX(year_game - birth_baseball), NULL)) AS age, IF(homeORaway='A','\@','') AS homeORvis, GROUP_CONCAT(DISTINCT CONCAT(classification,'-',register_leagues.name_code)) AS level, GROUP_CONCAT(DISTINCT st.name_short) AS team_ID, GROUP_CONCAT(DISTINCT st.franchID) AS affiliation, GROUP_CONCAT(DISTINCT st2.name_short) AS opp_ID, DATE_FORMAT(MAX(date_game), "%b %e") as last_played, DATEDIFF(CURDATE(), MAX(date_game)) - 1 as days_since_last_played, $classification_csk AS classification_csk, $gl_sql, $box_sql FROM pgl_mlbam AS a INNER JOIN register_people ON pitcher_id=register_people.key_mlbam INNER JOIN register_teams st ON pitching_team_id =st.key_mlbam AND year_game=st.year INNER JOIN register_teams st2 ON batting_team_id=st2.key_mlbam AND year_game=st2.year INNER JOIN register_leagues ON st.key_league=register_leagues.key_league WHERE $where GROUP BY register_people.key_mlbam HAVING $having ORDER BY $orderby END_SQL @stats = ( @standard_stats, qw(GS W L S), @{ $defn_of_statlines{pitching_gamelogs_milb}{list} }, qw(whip batting_avg_bip strikeouts_per_nine strikeouts_per_base_on_balls), @standard_stats_end, ); delete_stat_from_statline( 'player_game_span', \@stats ); delete_stat_from_statline( 'player_game_result', \@stats ); } # default is mlb batting from yesterday. else { $query = <<"END_SQL"; SELECT $defn_of_statlines{player_name_mlbam}{select}, (IF(birth_baseball,MAX(year_game - birth_baseball), NULL)) AS age, $defn_of_statlines{batting_gamelogs_milb}{select_sum}, IF(homeORaway='A','\@','') AS homeORvis, GROUP_CONCAT(DISTINCT CONCAT(classification,'-',register_leagues.name_code)) AS level, GROUP_CONCAT(DISTINCT st.name_short) AS team_ID, GROUP_CONCAT(DISTINCT st2.name_short) AS opp_ID, GROUP_CONCAT(DISTINCT st.franchID) AS affiliation, $classification_csk AS classification_csk, DATE_FORMAT(MAX(date_game), "%b %e") as last_played, DATEDIFF(CURDATE(), MAX(date_game)) - 1 as days_since_last_played, $gl_sql, $box_sql, AVG( (30*(a.HR >= 3)) + (10*(a.HR >= 2)) + (((a.H - a.2B - a.3B - a.HR > 0) && (a.2B > 0) && (a.3B > 0) && (a.HR > 0)) * 40) + a.R + (2.5*a.H) + a.RBI + a.2B + (3*a.3B) + (4*a.HR) + a.SB - a.CS + a.BB + a.HBP - (0.5*a.AB) - (0.2*a.SO) - a.GIDP ) as game_score FROM bgl_mlbam AS a INNER JOIN register_people ON batter_id=register_people.key_mlbam INNER JOIN register_teams st ON batting_team_id =st.key_mlbam AND year_game=st.year INNER JOIN register_teams st2 ON pitching_team_id=st2.key_mlbam AND year_game=st2.year INNER JOIN register_leagues ON st.key_league=register_leagues.key_league WHERE $where GROUP BY register_people.key_mlbam HAVING $having ORDER BY $orderby END_SQL @stats = ( @standard_stats, @{ $defn_of_statlines{batting_gamelogs_milb}{list} }, @standard_stats_end, ); } ################################################################## # Modify the @stats array as needed to clean this up. ################################################################## # add date_game if asked foreach my $col ( @{ $ref->{delete_columns} } ) { # clean up the stats list for single game queries. # remove the rate stats. if ( $col eq 'RATE_STATS' ) { foreach my $stat ( 'batting_avg', 'slugging_perc', 'onbase_perc', 'onbase_plus_slugging', 'earned_run_avg', 'whip', 'batting_avg_bip', 'strikeouts_per_nine', 'strikeouts_per_base_on_balls' ) { delete_stat_from_statline( $stat, \@stats ); } } else { delete_stat_from_statline( $col, \@stats ); } } ## some debugging. if ($FALSE) { use Data::Dumper; my $fhh = $ref->{fh}; my $ref_out = Dumper($ref); my $stats_out = join( ', ', @stats ); print $fhh <
Show Debugging
Hide Debugging
  ref:
     $ref_out

  WHERE:
     $where

  QUERY:
     $query;

  
Stats array: $stats_out
END_HTML my $display_query = $query; $display_query =~ s/\n/
/gm; $model->{query_box} = $display_query; } my $sth = $dbh->prepare($query); $sth->execute(@exec_vars) || print STDERR "### query failed:$query\n### vars: " . join( ',', @exec_vars ) . "\n"; return ( $sth, @stats ); } ################################################################### # Usage : zzzz # Purpose : zzzz # Returns : zzzz # Parameters : zzzz # Throws : zzzz # See Also : zzzz # Comments : zzzz ################################################################### sub _get_other_dates_header { my ($date) = @_; return <<"END_HTML"; /
Month: Day:
END_HTML }