#!/usr/bin/perl # AUTHORS # Sean Forman # Hans Van Slooten # $Date: 2017-03-04 00:35:37 -0500 (Sat, 04 Mar 2017) $ # $Author: sean $ # $Rev: 49655 $ # $HeadURL: http://svn.sports-reference.com/svn/br_repos/br/trunk/draft/index.fcgi $ # Copyright 2000-2017, SPORTS REFERENCE, LLC All rights reserved. #http://www.baseball-ref.org/draft/?query_type=year_round&year_ID=2008&draft_round=1&draft_type=junreg #http://www.baseball-ref.org/draft/?year_ID=2008&draft_round=1&draft_type=junreg&query_type=draft_round use Carp; use CGI::Fast qw(:standard); use Data::Dumper; use FindBin qw($Bin); use Modern::Perl '2010'; use lib "$Bin/../lib"; use SR::Cache; use SR::Defaults; use SR::Logging; use SR::Statline; use SRlocal::Constants; use SRlocal::DB; use SRlocal::Defaults; use SRlocal::Draft; use SRlocal::Stats; use SRlocal::Templates; =item UPDATE draft_mlb AS dm SET milbID=(SELECT milbID FROM sabr_people AS mp WHERE mp.bbrefID=dm.player_ID) WHERE milbID IS NULL AND player_ID IS NOT NULL; UPDATE draft_mlb AS dm SET player_ID=(SELECT bbrefID FROM sabr_people AS mp WHERE mp.milbID=dm.milbID) WHERE milbID IS NOT NULL AND player_ID IS NULL; =cut our @DRAFT_STATS = qw(year_ID draft_round draft_abb overall_pick franch_round round_pick team_ID signed player pos WAR G_bat AB HR batting_avg onbase_plus_slugging G_pitch W L earned_run_avg whip SV from_type came_from); our $DRAFT_STATS_SELECT = <<"END_SQL"; d.round AS draft_round, d.year_ID, sabr_people.milbID, if(d.milbID IS NOT NULL, CONCAT(' (minors)'),'') AS player_append, d.draft_type, d.overall_pick, d.round_pick, d.name AS name, d.Pos as pos, d.franch_ID AS team_ID, d.is_void, d.is_supplemental, d.compensation, d.from_ID, CASE WHEN s.signed IS NULL THEN 'Unk' WHEN s.signed = 1 THEN 'Y' ELSE 'N' END AS signed, s.signed AS signed_csk, IFNULL(sabr_people.name_common, d.name) AS player, bio.linkto as player_link, sabr_schools.key_school, sabr_schools.name_display AS came_from, (SELECT ROUND(SUM(WAR),1) AS WAR from majors_appearances ma WHERE ma.player_ID=d.player_ID) AS WAR, a.G as G_bat, a.AB as AB, a.HR as HR, a.SB as SB, ROUND($SRlocal::Stats::batting_avg_a,3) AS batting_avg, ROUND($SRlocal::Stats::onbase_plus_slugging_a,3) AS onbase_plus_slugging, b.G as G_pitch, b.W as W, b.L as L, b.SV as SV, ROUND($SRlocal::Stats::earned_run_avg_b,2) AS earned_run_avg, ROUND($SRlocal::Stats::whip_b,2) AS whip, sabr_schools.school_type as from_type END_SQL our $DRAFT_STATS_FROM = <<"END_SQL"; sabr_draft AS d INNER JOIN sabr_people USING (key_person) LEFT JOIN sabr_schools USING (key_school) LEFT JOIN bio ON sabr_people.bbrefID=bio.player_ID LEFT JOIN players ON sabr_people.bbrefID=players.player_ID LEFT JOIN career_batting AS a ON sabr_people.bbrefID=a.player_ID LEFT JOIN career_pitching AS b ON sabr_people.bbrefID=b.player_ID LEFT JOIN draft_signed AS s USING (key_person, year_id) END_SQL our $DRAFT_STATS_ORDER = qq{year_ID DESC, draft_type ASC, overall_pick ASC, year_ID ASC}; our @supplemental_picks = (); our $line_count = 0; our $majors_count = 0; our $war_sum = 0.0; 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; while ( my $q = new CGI::Fast ) { # reset our counters. $line_count = 0; $majors_count = 0; $war_sum = 0.0; @supplemental_picks = (); my $memd_key = $q->query_string(); my $page_data = cache_get( 'draft:index', $memd_key ); # if we got data, print it. if ( is_nonempty($page_data) ) { print $q->header( -type => 'text/html', -charset => 'utf-8', -SR_SRC => 'memcached' ); print $page_data; next; } ## We always run my $submitted = $TRUE; my $submit_output = $EMPTY_STR; my $page_description = 'Draft Finder'; my $param_ref; # setup_ref is a hash to a bunch of values that we will use when # executing these various queries. sth_ref points to a statement # handle that has already been executed. alt_links gives the text # of the alternate navigational links to show. page_title is the # page title. my $setup_ref; my $meta_header = $EMPTY_STR; my $return_meta = $EMPTY_STR; # DEFAULT overall_pick=2&draft_type=junreg&query_type=overall_pick if ($submitted) { $param_ref->{query_type} = is_nonempty( $q->param('year_ID') ) && is_nonempty( $q->param('round') ) ? 'year_round' : is_nonempty_print( $q->param('query_type') ) eq 'name' ? 'pname' : is_nonempty( $q->param('query_type') ) ? $q->param('query_type') : 'overall_pick'; # Pull out all of the variables, and set defaults for other cases. $param_ref->{callis} = is_nonempty( $q->param('callis') ) ? $TRUE : $FALSE; $param_ref->{year_ID} = is_nonempty( $q->param('year_ID') ) ? $q->param('year_ID') : $CURRENT_YEAR; $param_ref->{draft_round} = is_nonempty( $q->param('draft_round') ) ? $q->param('draft_round') : 1; # Handle the fact that we don't have the correct url from the # player pages. $param_ref->{draft_round} = is_nonempty( $q->param('round') ) ? $q->param('round') : $param_ref->{draft_round}; $param_ref->{draft_round} = 1 if ( $param_ref->{draft_round} eq '1s' ); $param_ref->{overall_pick} = is_nonempty( $q->param('overall_pick') ) ? $q->param('overall_pick') : 1; $param_ref->{exact} = is_nonempty( $q->param('exact') ) ? $q->param('exact') : $FALSE; $param_ref->{draft_type} = is_nonempty( $q->param('draft_type') ) ? $q->param('draft_type') : 'junreg'; $param_ref->{team_ID} = is_nonempty( $q->param('team_ID') ) ? $q->param('team_ID') : 'STL'; $param_ref->{pos} = is_nonempty( $q->param('pos') ) ? $q->param('pos') : $EMPTY_STR; $param_ref->{is_still_active} = is_nonempty_print( $q->param('year_ID') ) eq 'active' ? $TRUE : $FALSE; $param_ref->{came_from} = is_nonempty( $q->param('came_from') ) ? $q->param('came_from') : 'University of Iowa'; $param_ref->{key_school} = is_nonempty( $q->param('key_school') ) ? $q->param('key_school') : ''; $param_ref->{from_type_hs} = is_nonempty( $q->param('from_type_hs') ) ? $q->param('from_type_hs') : $FALSE; $param_ref->{from_type_4y} = is_nonempty( $q->param('from_type_4y') ) ? $q->param('from_type_4y') : $FALSE; $param_ref->{from_type_jc} = is_nonempty( $q->param('from_type_jc') ) ? $q->param('from_type_jc') : $FALSE; $param_ref->{from_type_unk} = is_nonempty( $q->param('from_type_unk') ) ? $q->param('from_type_unk') : $FALSE; $param_ref->{name} = is_nonempty( $q->param('name') ) ? $q->param('name') : 'Jo Smith'; $param_ref->{draft_type} =~ s/ $//g; $param_ref->{team_ID} = $EMPTY_STR if ( $param_ref->{team_ID} eq '.' ); # Check on the data coming in. $param_ref->{year_ID} = $CURRENT_YEAR if ( ( $param_ref->{year_ID} ne '' ) && ( $param_ref->{year_ID} ne 'active' ) && ( $param_ref->{year_ID} !~ /^[0-9]+$/ ) ); $param_ref->{overall_pick} = 1 if ( ( $param_ref->{overall_pick} ne '' ) && ( $param_ref->{overall_pick} !~ /^[0-9]+$/ ) ); $param_ref->{draft_round} = 1 if ( ( $param_ref->{draft_round} ne '' ) && ( $param_ref->{draft_round} !~ /^[0-9s]+$/ ) ); $param_ref->{draft_type} = 'junreg' if ( ( $param_ref->{draft_type} ne '' ) && ( $param_ref->{draft_type} !~ /^[a-z]+$/ ) ); $param_ref->{pos} = 'SS' if ( ( $param_ref->{pos} ne '' ) && ( $param_ref->{draft_type} !~ /^[A-z0-9\-]+$/ ) ); $param_ref->{came_from} = 'IA' if ( ( $param_ref->{came_from} ne '' ) && ( $param_ref->{came_from} !~ /^[A-Za-z0-9,\& \%\'\.\(\)\-]+$/ ) ); $param_ref->{key_school} = '3c35f80b' if ( ( $param_ref->{key_school} ne '' ) && ( $param_ref->{key_school} !~ /^[A-Fa-f0-9]+$/ ) ); $param_ref->{key_school} = 'Sean' if ( ( $param_ref->{name} ne '' ) && ( $param_ref->{name} !~ /^[A-Za-z0-9,\' \.\(\)\-]+$/ ) ); $param_ref->{team_ID} = 'PHI' if ( ( $param_ref->{team_ID} ne '' ) && ( $param_ref->{team_ID} !~ /^[A-Z]+$/ ) && ( $param_ref->{team_ID} !~ /^[A-Z]+ \(minors\)$/ ) ); # Based on what values we get we thru run through a wide variety # of different outputs. # The different query_types are # overall_pick get all the players ever at this slot # year_round get the picks for a year and a round # franch_round get the picks for a year and a round # franch_year get the picks by a franchise for a year # pos_year get all of the picks at a position for a year. # pos_round get all of the picks at a position picked in a particular round # player_name return the players matching a name. # came_from get the players from an area. # This outcome is all of the still active draft picks from a # particular franchise and year. my @DRAFT_STATS_LOCAL = @DRAFT_STATS; if ( $param_ref->{is_still_active} ) { $setup_ref = _setup_is_still_active_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); } elsif ( $param_ref->{callis} ) { $setup_ref = _setup_year_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); } elsif ( $param_ref->{query_type} eq 'overall_pick' ) { $setup_ref = _setup_overall_pick_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); $setup_ref->{meta_label} = 'year_ID'; $meta_header = 'Year-by-Year Picks: '; } elsif ( $param_ref->{query_type} eq 'franch_round' ) { $setup_ref = _setup_franch_round_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); $setup_ref->{meta_label} = 'year_ID'; $meta_header = 'Year-by-Year Picks: '; } elsif ( $param_ref->{query_type} eq 'franch_year' ) { $setup_ref = _setup_franch_year_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); $setup_ref->{meta_label} = 'draft_round'; $meta_header = 'Round-by-Round Picks: '; } elsif ( $param_ref->{query_type} eq 'pos_year' ) { $setup_ref = _setup_pos_year_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); $setup_ref->{meta_label} = 'overall_pick'; $meta_header = 'Overall Picks: '; } elsif ( $param_ref->{query_type} eq 'pos_round' ) { $setup_ref = _setup_pos_round_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); #$setup_ref->{meta_label} = 'overall_pick'; #$meta_header = 'Overall Picks: '; $setup_ref->{meta_label} = 'year_ID'; $meta_header = 'Year-by-Year Picks: '; } elsif ( $param_ref->{query_type} eq 'key_school' || $param_ref->{query_type} eq 'came_from' ) { $setup_ref = _setup_came_from_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); $setup_ref->{meta_label} = 'year_ID'; $meta_header = 'Year-by-Year Picks: '; } elsif ( $param_ref->{query_type} eq 'pname' ) { $setup_ref = _setup_name_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); $setup_ref->{meta_label} = 'year_ID'; $meta_header = 'Year-by-Year Picks: '; } elsif ( $param_ref->{query_type} eq 'year_round' ) { $setup_ref = _setup_year_round_search( $dbh, $param_ref, \@DRAFT_STATS_LOCAL ); $setup_ref->{meta_label} = 'round_pick'; $meta_header = 'Round Picks: '; } $submit_output .= sprintf( qq{

%s

%s

\n}, is_nonempty_print( $setup_ref->{page_title} ), is_nonempty_print( $setup_ref->{alt_links} ) ); my $sth = $setup_ref->{sth_ref}; if ( $sth && $sth->rows ) { my $table_ref = get_results_table( $dbh, $sth, $param_ref, $setup_ref, \@DRAFT_STATS_LOCAL ); $submit_output .= $table_ref->{table}; $return_meta = $table_ref->{return_meta}; my $bottom_output = $EMPTY_STR; $bottom_output .= qq{

*Compensation Picks
} . join( '
', @supplemental_picks ) . qq{\n

} if ( scalar @supplemental_picks ); $bottom_output .= <<"END_HTML" if ( ( $param_ref->{year_ID} == 1968 ) && ( $param_ref->{draft_type} =~ /^jun/ ) );

Montreal Expos, Seattle Pilots (Brewers), Royals, and Padres did not receive picks in the first three rounds of the draft.

END_HTML $bottom_output .= sprintf( qq{

%s

\n}, $setup_ref->{alt_links} ); my $majors_perc = $line_count ? sprintf " (%d%s)", 100 * $majors_count / $line_count, $PERCENT_SIGN : $EMPTY_STR; my $war_total = sprintf " %.1f", $war_sum; my $war_avg = $majors_count ? sprintf "%4.1f", $war_sum / $majors_count : '--'; $bottom_output .= <<"END_HTML";

$line_count matching player(s). $majors_count played in the majors$majors_perc. Total of $war_total WAR, or $war_avg per major leaguer.

DT Key

  • Blank - June Draft
  • 6sc - June Secondary
  • 6sa - June Secondary (normal, 1971 only)
  • 6sd - June Secondary (delayed, 1971 only)
  • 1rg - January Draft
  • 1sc - January Secondary Draft
  • 8lg - August Legion Draft

NOTES: We have not yet matched the $CURRENT_YEAR debuts to their draft into. We hope to do so soon.
Please note that searches by state are not complete and not guaranteed to be accurate.
Players are listed regardless of whether they signed or not.
School names may change from year to year making it difficult to find all player drafted from a school.
Please let us know if you find what you believe is an error.

The 1965 draft had a major league and AAA section (rounds 1-3), a AA section (4-7) and A-ball sections (8+). Each team had one pick in rounds 1-7 (MLB, AAA, and AA picks). Starting with the 8th round teams received a pick for each A-ball team in their system in the reverse order of the affiliate finishes. Franchises that shared affiliates, alternated the picks round-by-round. This means that a franchise will have multiple picks in a "round". For presentation reasons here, we have re-labeled the rounds to be the number of the selection made by this franchise. This means that the Twins' 10th round pick could come after the Mets' 12th round pick.

END_HTML $submit_output .= $bottom_output; } else { $submit_output .= '

No matching players.

'; } $sth->finish if ($sth); $page_description = $meta_header . _nempp($return_meta) . '...'; } my %model = (); my $page_title = ( $setup_ref->{page_title} ) ? ( $setup_ref->{page_title} ) : 'Draft Finder'; $model{header} = { page_title => $page_title, page_url => $SRlocal::Constants::SITE_URL . '/draft/', page_description => $page_description, you_are_here => generate_you_are_here( [ q{Baseball Amateur Draft}, ("$page_title") ] ), qi_section => "draft", }; # INNER NAV # #Expansion Drafts # # push( @{ $model{pagecontent} }, '

Draft Pick Search Options

' ); push( @{ $model{pagecontent} }, display_form( $srtemplates, $param_ref ) ); push( @{ $model{pagecontent} }, $submit_output ); my $output = $srtemplates->process( 'Pages/General.tt2', \%model ); print $q->header( -type => 'text/html', -charset => 'utf-8', -SR_SRC => 'brbuild' ); print $output; cache_set( 'draft:index', $memd_key, $output ); } $dbh->disconnect; } sub get_results_table { my ( $dbh, $sth, $param_ref, $setup_ref, $draft_stats_ref ) = @_; my @DRAFT_STATS_LOCAL = @{$draft_stats_ref}; my $output = $EMPTY_STR; my $return_meta = $EMPTY_STR; # Set the table's id and class. local $STATLINE_DEFAULTS{year_ID}{sort_default_asc} = $FALSE; local $STATLINE_DEFAULTS{team_ID}{mouseover_text} = qq{Click team name to get complete
draft for that year.}; local $STATLINE_DEFAULTS{pos}{link} = $TRUE; $STATLINE_DEFAULTS{pos}{mouseover_text} .= qq{
Click the position to get list of players from
that position drafted that year.}; local $STATLINE_DEFAULTS{from_type}{header} = 'Type'; $STATLINE_DEFAULTS{from_type}{mouseover_text} .= qq{Type of school drafted from
4Yr - Four Year college or university
JC - Junior College or Community College<
HS - High School or Prep School
Unk - not yet known or entered.}; local $STATLINE_DEFAULTS{G_bat}{mouseover_text} .= qq{Games played as a batter.}; local $STATLINE_DEFAULTS{G_pitch}{mouseover_text} .= qq{Games played as a pitcher.}; if ( is_nonempty( $param_ref->{key_school} ) ) { $output .= sprintf qq{

MLB Players from this school

\n}, $param_ref->{key_school}; } my %table_defn = ( table_id => 'draft_stats', title => 'Drafted Players', comment_by_default => $FALSE, table_cols_to_freeze => 1, section_heading_text => 'Links to minor league stats are incomplete and may not indicate a player signing', ); $output .= generate_statline_header_wrap( \%table_defn ); $output .= generate_statline_header( \@DRAFT_STATS_LOCAL ); my $last_label = $EMPTY_STR; # Get the statement handle from the reference. while ( my $tmp = $sth->fetchrow_hashref ) { #print '
'.Dumper($tmp).'
';exit; $line_count++; $majors_count++ if ( is_nonempty_print( $tmp->{player_link} ) =~ /[a-z]/ ); $war_sum += is_empty_zero( $tmp->{WAR} ); $tmp->{WAR_csk} = is_empty( $tmp->{WAR} ) ? -1_000 : $tmp->{WAR}; # Check for voided selections. $tmp->{player_append} = ' (SELECTION VOIDED) ' if ( $tmp->{is_void} ); $tmp->{draft_round} .= 's' if ( $tmp->{is_supplemental} ) && ( $tmp->{draft_round} !~ /s/ ) && ( $tmp->{compensation} !~ /nsigned/g ); # SEt up the link to the franchise round for this franchise. $tmp->{franch_round} = 'FrRnd'; $tmp->{franch_round_link} = sprintf( qq{/draft/?team_ID=%s&draft_round=%s&draft_type=%s&query_type=%s}, $tmp->{team_ID}, $tmp->{draft_round}, $tmp->{draft_type}, 'franch_round' ) if ( !is_nonempty_print( $setup_ref->{suppress_franch_round} ) ); # get the name of the draft. $tmp->{draft_abb} = $DRAFT_ABB{ $tmp->{draft_type} }; # Get a link to the other players in this round. $tmp->{draft_round_link} = sprintf( qq{/draft/?year_ID=%s&draft_round=%s&draft_type=%s&query_type=%s}, $tmp->{year_ID}, $tmp->{draft_round}, $tmp->{draft_type}, 'year_round' ) if ( !is_nonempty_print( $setup_ref->{suppress_year_round} ) ); $tmp->{overall_pick_link} = sprintf( qq{/draft/?overall_pick=%s&draft_type=%s&query_type=%s}, $tmp->{overall_pick}, $tmp->{draft_type}, 'overall_pick' ) if ( !is_nonempty_print( $setup_ref->{suppress_overall_pick} ) ); $tmp->{team_ID_link} = sprintf( qq{/draft/?team_ID=%s&year_ID=%s&draft_type=%s&query_type=%s}, $tmp->{team_ID}, $tmp->{year_ID}, $tmp->{draft_type}, 'franch_year' ) if ( !is_nonempty_print( $setup_ref->{suppress_franch_year} ) ); $tmp->{pos_link} = sprintf( qq{/draft/?pos=%s&year_ID=%s&draft_type=%s&query_type=%s}, $tmp->{pos}, $tmp->{year_ID}, $tmp->{draft_type}, 'pos_year' ) if ( !is_nonempty_print( $setup_ref->{suppress_pos_year} ) ); # Make this empty if there are no letters (for Luke # Hochevar and others) $tmp->{came_from_link} = sprintf( qq{/draft/?key_school=%s&exact=1&query_type=%s}, $tmp->{key_school}, 'key_school' ) if ( !_nemp( $setup_ref->{suppress_came_from} ) && $tmp->{key_school} ); # Handle the 39 cases where minor league teams drafted. my $nickname = gtrc( $dbh, 'majors_team_nicknames', { name => gtrc( $dbh, 'majors_team', { franch_ID => $tmp->{team_ID}, year_ID => $tmp->{year_ID} }, 'name' ) }, 'nickname' ) if ( $tmp->{team_ID} ); my $nickname_from = gtrc( $dbh, 'majors_team_nicknames', { name => gtrc( $dbh, 'majors_team', { franch_ID => $tmp->{from_ID}, year_ID => $tmp->{year_ID} }, 'name' ) }, 'nickname' ) if ( $tmp->{from_ID} ); $tmp->{team_ID} =~ s/ .minors./-min/; $tmp->{team_ID} = is_nonempty($nickname) ? $nickname : is_nonempty_print( $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $tmp->{team_ID} } ) =~ /[A-Z]/ ? $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $tmp->{team_ID} } : $tmp->{team_ID} eq 'MIM' ? 'Miami Miracle' : $tmp->{team_ID} eq 'BOI' ? 'Boise (minors)' : $tmp->{team_ID} eq 'BEN' ? 'Bend (minors)' : substr( $tmp->{team_ID}, 0, 9 ); # Handle the supplementals or compensation picks. We are # going to put a star before the player's name and then # print a list below. if ( $tmp->{is_supplemental} || ( is_nonempty_print( $tmp->{from_ID} ) =~ /[A-Z][A-Z][A-Z]/ ) ) { # Put an asterik before the player selected. $tmp->{player_prepend} = '*'; if ( $tmp->{compensation} =~ /^[a-z].*[0-9][0-9]$/ ) { # We have a player ID. if ( $tmp->{is_supplemental} ) { push( @supplemental_picks, sprintf qq{%s. %s (%s-%s) - Supplemental Pick for loss of Free Agent %s\n}, $tmp->{overall_pick}, $tmp->{player}, $tmp->{team_ID}, $tmp->{draft_round}, SRlocal::Constants::get_player_url_from_id( $tmp->{compensation} ), get_person_name( $dbh, $tmp->{compensation}, { no_mark => $TRUE } ) ); } else { $tmp->{team_ID_class} = 'wrap'; $tmp->{team_ID_append} = ' via ' . ( is_nonempty($nickname_from) ? $nickname_from : is_nonempty_print( $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $tmp->{from_ID} } ) =~ /[A-Z]/ ? $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $tmp->{from_ID} } : substr( $tmp->{from_ID}, 0, 9 ) ); push( @supplemental_picks, sprintf qq{%s (%s-%d) - Pick from %s as compensation for Free Agent %s\n}, $tmp->{player}, $tmp->{team_ID}, $tmp->{draft_round}, is_nonempty($nickname_from) ? $nickname_from : is_nonempty_print( $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $tmp->{from_ID} } ) =~ /[A-Z]/ ? $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $tmp->{from_ID} } : substr( $tmp->{from_ID}, 0, 9 ), SRlocal::Constants::get_player_url_from_id( $tmp->{compensation} ), get_person_name( $dbh, $tmp->{compensation}, { no_mark => $TRUE } ) ); } } else { push( @supplemental_picks, sprintf qq{%s (%s-%s) - %s\n}, $tmp->{player}, $tmp->{team_ID}, $tmp->{draft_round}, $tmp->{compensation} ); } } # update the from_type to be two characters. $tmp->{from_type} = is_empty( $tmp->{from_type} ) ? '' : $tmp->{from_type} eq 'secondary' ? 'HS' : $tmp->{from_type} eq 'juco' ? 'JC' : $tmp->{from_type} eq 'college' ? '4Yr' : $EMPTY_STR; $output .= generate_statline( \@DRAFT_STATS_LOCAL, $tmp ); if ( $last_label ne is_nonempty_print( $tmp->{ $setup_ref->{meta_label} } ) ) { $return_meta .= sprintf qq{%s:%s, }, is_nonempty_print( $tmp->{ $setup_ref->{meta_label} } ), $tmp->{player} if ( $line_count < 11 ); } else { $return_meta .= sprintf qq{%s, }, $tmp->{player} if ( $line_count < 11 ); } $last_label = $tmp->{ $setup_ref->{meta_label} }; } $output .= generate_statline_footer_wrap( \%table_defn ); return { table => $output, meta => $return_meta }; } ################################################################### # Usage : _setup_is_still_active_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_is_still_active_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; # Remove unneeeded columns. delete_stat_from_statline( 'team_ID', $stats_array_ref ); delete_stat_from_statline( 'franch_round', $stats_array_ref ); my $from_type_clause = _get_from_type_clause($param_ref); my $from_type_title = _get_from_type_title($param_ref); my $page_title = sprintf( qq{Still Active Picks from all %s MLB Amateur Drafts $from_type_title}, $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $param_ref->{team_ID} } =~ /[A-Z]/ ? $SRlocal::Constants::FRANCHISE_NICKNAME_OF{ $param_ref->{team_ID} } : $param_ref->{team_ID} ); my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND active='Y' AND d.franch_ID=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: is_still_active\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{team_ID} ) || croak( "failed exec: is_still_active\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $EMPTY_STR, }; } ################################################################### # Usage : _get_from_type_clause # Purpose : sets up a clause to select only certain types of schools. # Returns : a db where clause starting with AND # Parameters : param_ref # Throws : # See Also : # Comments : ################################################################### sub _get_from_type_clause { my ($param_ref) = @_; my @find_schools = (); my %school_types = ( hs => 'secondary', jc => 'juco', '4y' => 'college', unk => '' ); foreach my $type ( keys %school_types ) { push( @find_schools, $DOUBLE_QUOTE . $school_types{$type} . $DOUBLE_QUOTE ) if ( $param_ref->{ 'from_type_' . $type } ); } return $EMPTY_STR if ( ( scalar(@find_schools) == 0 ) || ( scalar(@find_schools) == scalar( keys %school_types ) ) ); return "AND IFNULL(school_type,'') IN (" . join( $COMMA, @find_schools ) . ")"; } ################################################################### # Usage : _get_from_type_clause # Purpose : sets up a clause to select only certain types of schools. # Returns : a db where clause starting with AND # Parameters : param_ref # Throws : # See Also : # Comments : ################################################################### sub _get_from_type_title { my ($param_ref) = @_; my @find_schools = (); my %school_types = ( hs => 'High Schools', jc => 'Junior Colleges', '4y' => '4-Yr Schools', unk => 'Uncategorized Schools' ); foreach my $type ( keys %school_types ) { push( @find_schools, $school_types{$type} ) if ( $param_ref->{ 'from_type_' . $type } ); } return $EMPTY_STR if ( ( scalar(@find_schools) == 0 ) || ( scalar(@find_schools) == scalar( keys %school_types ) ) ); return "From " . commify_series(@find_schools); } ################################################################### # Usage : _setup_overall_pick_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_overall_pick_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = "Other Picks: "; my @alt_links; push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_type}, $param_ref->{overall_pick} - 1, 'overall_pick', ordinate( $param_ref->{overall_pick} - 1 ) ) ) if ( $param_ref->{overall_pick} > 1 ); push( @alt_links, sprintf( "%s", ordinate( $param_ref->{overall_pick} ) ) ); push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_type}, $param_ref->{overall_pick} + 1, 'overall_pick', ordinate( $param_ref->{overall_pick} + 1 ) ) ) if ( $param_ref->{overall_pick} > 0 ); $alt_links .= join( ' / ', @alt_links ); # Remove unneeeded columns. delete_stat_from_statline( 'overall_pick', $stats_array_ref ); my $from_type_clause = _get_from_type_clause($param_ref); my $from_type_title = _get_from_type_title($param_ref); my $page_title = sprintf( qq{%s Picks Overall in the %s $from_type_title}, ordinate( $param_ref->{overall_pick} ), $DRAFT_TITLE{ $param_ref->{draft_type} } ); my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND d.overall_pick=? and d.draft_type=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{overall_pick}, $param_ref->{draft_type} ) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, suppress_overall_pick => $TRUE, }; } ################################################################### # Usage : _setup_franch_round_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_franch_round_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = "Other Rounds:"; my @alt_links; push( @alt_links, sprintf( qq{%s}, $param_ref->{team_ID}, $param_ref->{draft_round} - 1, $param_ref->{draft_type}, 'franch_round', ordinate( $param_ref->{draft_round} - 1 ) . ' round', ) ) if ( $param_ref->{draft_round} > 1 ); push( @alt_links, sprintf( "%s round", ordinate( $param_ref->{draft_round} ) ) ); push( @alt_links, sprintf( qq{%s}, $param_ref->{team_ID}, $param_ref->{draft_round} + 1, $param_ref->{draft_type}, 'franch_round', ordinate( $param_ref->{draft_round} + 1 ) . ' round' ) ) if ( $param_ref->{draft_round} > 0 ); $alt_links .= join( ' / ', @alt_links ); # Remove unneeeded columns. delete_stat_from_statline( 'team_ID', $stats_array_ref ); delete_stat_from_statline( 'franch_round', $stats_array_ref ); my $teamname = gtrc( $dbh, 'majors_team', { franch_ID => $param_ref->{team_ID}, year_ID => $param_ref->{year_ID} }, 'name' ); $teamname =~ s/Washington Nationals/Washington Nationals (& Montreal Expos 68-04)/; $teamname =~ s/Texas Rangers/Texas Rangers (& Washington Senators 65-71)/; my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title = sprintf( qq{%s %s Round Picks in the %s $from_type_title}, is_nonempty($teamname) ? $teamname : $SRlocal::Constants::FRANCHISE_FULL_NAME_OF{ $param_ref->{team_ID} } =~ /[A-Z]/ ? $SRlocal::Constants::FRANCHISE_FULL_NAME_OF{ $param_ref->{team_ID} } : $param_ref->{team_ID} eq 'MIM' ? 'Miami Miracle' : $param_ref->{team_ID} eq 'BOI' ? 'Boise (minors)' : $param_ref->{team_ID} eq 'BEN' ? 'Bend (minors)' : $param_ref->{team_ID}, ordinate( $param_ref->{draft_round} ), $DRAFT_TITLE{ $param_ref->{draft_type} } ); my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND d.franch_ID=? AND d.round=? AND d.draft_type=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{team_ID}, $param_ref->{draft_round}, $param_ref->{draft_type} ) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, suppress_franch_round => $TRUE, }; } ################################################################### # Usage : _setup_franch_year_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_franch_year_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = "Other Years: "; my @alt_links; my $start_year = $param_ref->{year_ID} - 3; my $end_year = $param_ref->{year_ID} + 3; foreach my $year ( $start_year .. $end_year ) { if ( $year == $param_ref->{year_ID} ) { push( @alt_links, sprintf( "%s", $year ) ); } else { push( @alt_links, sprintf( qq{%s}, $param_ref->{team_ID}, $year, $param_ref->{draft_type}, 'franch_year', $year ) ) if ( $year >= $FIRST_YEAR_DRAFT && $year <= $LAST_YEAR_DRAFT ); } } $alt_links .= join( ' / ', @alt_links ); # Remove unneeeded columns. delete_stat_from_statline( 'franch_round', $stats_array_ref ); my $teamname = gtrc( $dbh, 'majors_team', { franch_ID => $param_ref->{team_ID}, year_ID => $param_ref->{year_ID} }, 'name' ); $teamname = qq{Montreal Expos} if ( ( $param_ref->{team_ID} eq 'WSN' ) && ( $param_ref->{year_ID} < 2000 ) ); my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title = sprintf( qq{%s %s Picks in the %s $from_type_title}, $param_ref->{year_ID}, is_nonempty($teamname) ? $teamname : $SRlocal::Constants::FRANCHISE_FULL_NAME_OF{ $param_ref->{team_ID} } =~ /[A-Z]/ ? $SRlocal::Constants::FRANCHISE_FULL_NAME_OF{ $param_ref->{team_ID} } : $param_ref->{team_ID} eq 'MIM' ? 'Miami Miracle' : $param_ref->{team_ID} eq 'BOI' ? 'Boise (minors)' : $param_ref->{team_ID} eq 'BEN' ? 'Bend (minors)' : $param_ref->{team_ID}, $DRAFT_TITLE{ $param_ref->{draft_type} } ); my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND d.franch_ID=? AND d.year_ID=? AND d.draft_type=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{team_ID}, $param_ref->{year_ID}, $param_ref->{draft_type} ) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, suppress_franch_year => $TRUE, }; } ################################################################### # Usage : _setup_year_round_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_year_round_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = "Other Rounds:"; my @alt_links; push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_round}, $param_ref->{year_ID} - 1, $param_ref->{draft_type}, 'year_round', ( $param_ref->{year_ID} - 1 ) . ' ' . ordinate( $param_ref->{draft_round} ) ) ) if ( $param_ref->{year_ID} > $FIRST_YEAR_DRAFT ); push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_round} - 1, $param_ref->{year_ID}, $param_ref->{draft_type}, 'year_round', ( $param_ref->{year_ID} ) . ' ' . ordinate( $param_ref->{draft_round} - 1 ) ) ) if ( $param_ref->{draft_round} > 1 ); push( @alt_links, sprintf( "%s", ( $param_ref->{year_ID} ) . ' ' . ordinate( $param_ref->{draft_round} ) ) ); push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_round} + 1, $param_ref->{year_ID}, $param_ref->{draft_type}, 'year_round', ( $param_ref->{year_ID} ) . ' ' . ordinate( $param_ref->{draft_round} + 1 ) ) ) if ( $param_ref->{draft_round} > 0 ); push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_round}, $param_ref->{year_ID} + 1, $param_ref->{draft_type}, 'year_round', ( $param_ref->{year_ID} + 1 ) . ' ' . ordinate( $param_ref->{draft_round} ) ) ) if ( $param_ref->{year_ID} < $LAST_YEAR_DRAFT ); $alt_links .= join( ' / ', @alt_links ); my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title = sprintf( qq{%s Round of the %s %s $from_type_title}, ordinate( $param_ref->{draft_round} ), $param_ref->{year_ID}, $DRAFT_TITLE{ $param_ref->{draft_type} } ); my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND d.round=? AND d.year_ID=? AND d.draft_type=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{draft_round}, $param_ref->{year_ID}, $param_ref->{draft_type} ) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, suppress_year_round => $TRUE, }; } ################################################################### # Usage : _setup_year_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_year_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = "Other Rounds:"; my @alt_links; push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_round}, $param_ref->{year_ID} - 1, $param_ref->{draft_type}, 'year_round', ( $param_ref->{year_ID} - 1 ) . ' ' . ordinate( $param_ref->{draft_round} ) ) ) if ( $param_ref->{year_ID} > $FIRST_YEAR_DRAFT ); push( @alt_links, sprintf( "%s", ( $param_ref->{year_ID} ) . ' ' . ordinate( $param_ref->{draft_round} ) ) ); push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_round} + 1, $param_ref->{year_ID}, $param_ref->{draft_type}, 'year_round', ( $param_ref->{year_ID} ) . ' ' . ordinate( $param_ref->{draft_round} + 1 ) ) ) if ( $param_ref->{draft_round} > 0 ); push( @alt_links, sprintf( qq{%s}, $param_ref->{draft_round}, $param_ref->{year_ID} + 1, $param_ref->{draft_type}, 'year_round', ( $param_ref->{year_ID} + 1 ) . ' ' . ordinate( $param_ref->{draft_round} ) ) ) if ( $param_ref->{year_ID} < $LAST_YEAR_DRAFT ); $alt_links .= join( ' / ', @alt_links ); my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title = sprintf( qq{%s Round of the %s %s $from_type_title}, ordinate( $param_ref->{draft_round} ), $param_ref->{year_ID}, $DRAFT_TITLE{ $param_ref->{draft_type} } ); my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND d.year_ID=? AND d.draft_type=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{year_ID}, $param_ref->{draft_type} ) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, suppress_year_round => $TRUE, }; } ################################################################### # Usage : _setup_pos_year_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_pos_year_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = "Other Years:"; my @alt_links; push( @alt_links, sprintf( qq{%s}, $param_ref->{pos}, $param_ref->{year_ID} - 1, $param_ref->{draft_type}, 'pos_year', $param_ref->{year_ID} - 1 ) ) if ( $param_ref->{year_ID} > $FIRST_YEAR_DRAFT ); push( @alt_links, sprintf( "%s", $param_ref->{year_ID} ) ); push( @alt_links, sprintf( qq{%s}, $param_ref->{pos}, $param_ref->{year_ID} + 1, $param_ref->{draft_type}, 'pos_year', $param_ref->{year_ID} + 1 ) ) if ( $param_ref->{year_ID} < $LAST_YEAR_DRAFT ); $alt_links .= join( ' / ', @alt_links ); my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title = sprintf( qq{%s Picks in the %s, with a listed position of %s $from_type_title}, $param_ref->{year_ID}, $DRAFT_TITLE{ $param_ref->{draft_type} }, $param_ref->{pos} ); # Handle the Case of any-handed pitchers. my $where_pos = qq{d.Pos=?}; if ( $param_ref->{pos} eq 'P' ) { $where_pos = qq{d.Pos IN (?,'LHP','RHP')}; } # If outfielders are selected, include all OF positions. elsif ( $param_ref->{pos} eq 'OF' ) { $where_pos = qq{d.Pos IN (?,'LF','RF','CF')}; } else { # Otherwise it is completely defined and we can delete the pos. delete_stat_from_statline( 'pos', $stats_array_ref ); } my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND $where_pos AND d.year_ID=? AND d.draft_type=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{pos}, $param_ref->{year_ID}, $param_ref->{draft_type} ) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, suppress_pos_year => $param_ref->{pos} ne 'P', }; } ################################################################### # Usage : _setup_pos_round_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_pos_round_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = "Other Rounds:"; my @alt_links; push( @alt_links, sprintf( qq{%s}, $param_ref->{pos}, $param_ref->{draft_round} - 1, $param_ref->{draft_type}, 'pos_round', ordinate( $param_ref->{draft_round} - 1 ) . ' round', ) ) if ( $param_ref->{draft_round} > 1 ); push( @alt_links, sprintf( "%s round", ordinate( $param_ref->{draft_round} ) ) ); push( @alt_links, sprintf( qq{%s}, $param_ref->{pos}, $param_ref->{draft_round} + 1, $param_ref->{draft_type}, 'pos_round', ordinate( $param_ref->{draft_round} + 1 ) . ' round' ) ) if ( $param_ref->{draft_round} > 0 ); $alt_links .= join( ' / ', @alt_links ); my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title = sprintf( qq{%s Picks in the %s, with a listed position of %s $from_type_title}, ordinate( $param_ref->{draft_round} ) . $SPACE . 'Round Draft', $DRAFT_TITLE{ $param_ref->{draft_type} }, $param_ref->{pos} ); # Handle the Case of any-handed pitchers. my $where_pos = qq{d.Pos=?}; if ( $param_ref->{pos} eq 'P' ) { $where_pos = qq{d.Pos IN (?,'LHP','RHP')}; } elsif ( $param_ref->{pos} eq 'OF' ) { $where_pos = qq{d.Pos IN (?,'CF','LF','RF')}; } else { # Otherwise it is completely defined and we can delete the pos. delete_stat_from_statline( 'pos', $stats_array_ref ); } $DRAFT_STATS_ORDER = qq{year_ID desc, draft_type ASC, overall_pick ASC}; my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) AND $where_pos AND d.round=? AND d.draft_type=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute( $param_ref->{pos}, $param_ref->{draft_round}, $param_ref->{draft_type} ) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, #suppress_pos_year => $param_ref->{pos} ne 'P', }; } ################################################################### # Usage : _setup_came_from_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_came_from_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = $EMPTY_STR; my @alt_links; my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title; # Remove unneeeded columns. my $where_came_from_match; my $came_from_value; # Check to see if this is an exact match or not. if ( is_nonempty( $param_ref->{key_school} ) ) { $page_title = sprintf( qq{MLB Amateur Draft Picks who came from "%s" $from_type_title}, gtrc( $dbh, 'sabr_schools', { key_school => $param_ref->{key_school} }, 'name_display' ) ); delete_stat_from_statline( 'came_from', $stats_array_ref ); $where_came_from_match = qq{AND key_school=?}; $came_from_value = $param_ref->{key_school}; } else { # not an exact match, so we may have to do a full-text or a # regex search here. my @came_from_ft = (); my @came_from_regexp = (); $param_ref->{came_from} =~ s/[^-A-z\& ]//g; # split the string into names. my @came_from = split( /[ -]+/, $param_ref->{came_from} ); for ( my $i = 0; $i < scalar(@came_from); $i++ ) { # for full text we ignore of, the, and, a, an if ( ( $came_from[$i] ne 'of' ) && ( $came_from[$i] ne 'the' ) && ( $came_from[$i] ne 'and' ) && ( $came_from[$i] ne 'a' ) && ( $came_from[$i] ne 'an' ) && ( length( $came_from[$i] ) >= 4 ) ) { push( @came_from_ft, '+' . $came_from[$i] . '*' ); } # We assume anything less that 3 characters is a state. elsif (( $came_from[$i] ne 'of' ) && ( $came_from[$i] ne 'the' ) && ( $came_from[$i] ne 'and' ) && ( $came_from[$i] ne 'a' ) && ( $came_from[$i] ne 'an' ) && ( length( $came_from[$i] ) == 2 ) ) { push( @came_from_regexp, '((sabr_schools.state=' . $dbh->quote( $came_from[$i] ) . ') OR (sabr_schools.country=' . $dbh->quote( $came_from[$i] ) . ') )' ); } # We assume anything that is three characters is a pattern match. elsif (( $came_from[$i] ne 'of' ) && ( $came_from[$i] ne 'the' ) && ( $came_from[$i] ne 'and' ) && ( $came_from[$i] ne 'a' ) && ( $came_from[$i] ne 'an' ) && ( length( $came_from[$i] ) == 3 ) ) { push( @came_from_regexp, '(sabr_schools.name_display like "%' . $came_from[$i] . '%")' ); } } my $where_ft = $EMPTY_STR; my $where_regexp = $EMPTY_STR; if ( scalar @came_from_ft ) { $where_ft = "\n" . ' AND MATCH(sabr_schools.name_display) AGAINST("' . join( ' ', @came_from_ft ) . '" in BOOLEAN MODE) '; } if ( scalar @came_from_regexp ) { $where_regexp = "\n" . ' AND (' . join( ' AND ', @came_from_regexp ) . ' ) '; } $where_came_from_match = qq{$where_ft $where_regexp AND d.id_draft!=?}; $page_title = sprintf( qq{MLB Amateur Draft Picks who came from "%s" $from_type_title}, $param_ref->{came_from} ); $came_from_value = $param_ref->{came_from}; } my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) $from_type_clause $where_came_from_match ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = $dbh->prepare($query) || croak( "failed prep: overall_pick\n$query\n" . $dbh->errstr ); $sth->execute($came_from_value) || croak( "failed exec: overall_pick\n$query\n" . $dbh->errstr ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, suppress_came_from => $param_ref->{exact}, }; } ################################################################### # Usage : _setup_name_search # Purpose : sets up titles and queries for requested data. # Returns : a hash of values # Parameters : dbh, param_ref, stats_array_ref # Throws : # See Also : # Comments : ################################################################### sub _setup_name_search { my ( $dbh, $param_ref, $stats_array_ref ) = @_; my $alt_links = $EMPTY_STR; my @alt_links; my @name_regexp; my $from_type_title = _get_from_type_title($param_ref); my $from_type_clause = _get_from_type_clause($param_ref); my $page_title = sprintf( qq{MLB Amateur Draft Picks with the Name Matching: %s $from_type_title}, $param_ref->{name} ); my @name = split( / +/, $param_ref->{name} ); my @name_ft; my @name_exp; for ( my $i = 0; $i < scalar(@name); $i++ ) { push( @name_ft, '+' . $name[$i] . '*' ) if ( length( $name[$i] ) >= 4 ); push( @name_regexp, '((d.name like "' . $name[$i] . '%") OR (d.name like "% ' . $name[$i] . '%"))' ) if ( length( $name[$i] ) < 4 ); } my $name_ft = ''; my $name_regexp = ''; $name_ft = 'AND MATCH(d.name) AGAINST ("' . join( ' ', @name_ft ) . '" in BOOLEAN MODE)' if ( scalar @name_ft ); $name_regexp = 'AND (' . join( ' AND ', @name_regexp ) . ')' if ( scalar @name_regexp ); my $query = <<"END_SQL"; SELECT $DRAFT_STATS_SELECT FROM $DRAFT_STATS_FROM WHERE (a.age=0 or a.age is null) AND (b.age=0 or b.age is null) $name_ft $name_regexp AND d.id_draft !=? $from_type_clause ORDER BY $DRAFT_STATS_ORDER END_SQL $dbh->do('SET SQL_BIG_SELECTS=1'); my $sth = db_prepare_and_execute( $dbh, $query, $param_ref->{name} ); return { page_title => $page_title, sth_ref => $sth, alt_links => $alt_links, }; } ################################################################### # Usage : zzzz # Purpose : zzzz # Returns : zzzz # Parameters : zzzz # Throws : zzzz # See Also : zzzz # Comments : zzzz ################################################################### sub display_form { my ( $srtemplates, $param_ref ) = @_; my $output = $EMPTY_STR; my @draft_type_choices = [ { val => '', label => '=Type=', disabled => $TRUE, default => $TRUE }, { val => 'junreg', label => 'June', }, { val => '', label => '=Until 1986=', disabled => $TRUE, }, { val => 'junsec', label => 'June Sec.' }, { val => 'janreg', label => 'January' }, { val => 'jansec', label => 'Jan. Sec.' }, { val => '', label => '=Until 1966=', disabled => $TRUE, }, { val => 'augleg', label => 'Aug. Legion' }, { val => '', label => '=Just 1971=', disabled => $TRUE, }, { val => 'junseca', label => 'Jun Sec Norm' }, { val => 'junsecd', label => 'Jun Sec Del.' }, ]; my @pos_choices = [ { val => '', label => 'Position', disabled => $TRUE, default => $TRUE }, { val => 'C', label => 'C' }, { val => 'P', label => 'P' }, { val => 'SS', label => 'SS' }, { val => 'OF', label => 'OF' }, { val => '1B', label => '1B' }, { val => '3B', label => '3B' }, { val => '2B', label => '2B' }, { val => 'INF', label => 'INF' }, ]; my @teams = [ { val => "", label => "" }, { val => "ANA", label => "Angels" }, { val => "HOU", label => "Astros" }, { val => "OAK", label => "Athletics" }, { val => "TOR", label => "Blue Jays" }, { val => "ATL", label => "Braves" }, { val => "MIL", label => "Brewers" }, { val => "STL", label => "Cardinals" }, { val => "CHC", label => "Cubs" }, { val => "TBD", label => "Devil Rays" }, { val => "ARI", label => "Diamondbacks" }, { val => "LAD", label => "Dodgers" }, { val => "SFG", label => "Giants" }, { val => "CLE", label => "Indians" }, { val => "SEA", label => "Mariners" }, { val => "FLA", label => "Marlins" }, { val => "NYM", label => "Mets" }, { val => "WSN", label => "Nats/Expos" }, { val => "BAL", label => "Orioles" }, { val => "SDP", label => "Padres" }, { val => "PHI", label => "Phillies" }, { val => "PIT", label => "Pirates" }, { val => "TEX", label => "Rangers/Sens" }, { val => "BOS", label => "Red Sox" }, { val => "CIN", label => "Reds" }, { val => "COL", label => "Rockies" }, { val => "KCR", label => "Royals" }, { val => "DET", label => "Tigers" }, { val => "MIN", label => "Twins" }, { val => "CHW", label => "White Sox" }, { val => "NYY", label => "Yankees" }, { val => "", label => "=Minors=", disabled => $TRUE }, { val => "MIM", label => "Miami" }, { val => "BEN", label => "Bend" }, { val => "BOI", label => "Boise" }, ]; # add to each form element. my %from_types = ( '4y' => '4-Year College', hs => 'High School or Prep', jc => 'Junior or Community College', unk => 'Unknown', ); my @subfields_visible = (); my @subfields_hidden = (); foreach my $from_type ( keys %from_types ) { push( @subfields_visible, { type => 'boolean', name => 'from_type_' . $from_type, val => ( $param_ref->{ 'from_type_' . $from_type } ? 1 : 0 ), choices => [ { label => $from_types{$from_type}, val => 1, sr_preset => { 'preset-type' => 'change', 'preset-selector' => "input[name=from_type_" . $from_type . "]", 'preset-selector-value' => "match-this", } }, ], } ); push( @subfields_hidden, { type => 'hidden', name => 'from_type_' . $from_type, val => ( $param_ref->{ 'from_type_' . $from_type } ? 1 : 0 ), } ); } ######################################################################### my %formsettings1 = ( method => 'get', id => 'overall_pick', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), action => '/draft/', fields => [ { type => 'number', name => 'overall_pick', label => 'Pick #', placeholder => 1, max => 4, }, { type => 'dropdown', name => 'draft_type', choices => @draft_type_choices, }, { type => 'hidden', name => 'query_type', val => 'overall_pick', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, @subfields_hidden ] ); $formsettings1{formdata} = $param_ref if ( $param_ref->{query_type} eq 'overall_pick' ); ######################################################################### my %formsettings2 = ( id => 'year_round', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), method => 'get', action => '/draft/', fields => [ { label => 'Year', name => 'year_ID', type => 'dropdown', start => $LAST_YEAR_DRAFT, end => $FIRST_YEAR_DRAFT, step => -1, }, { linear => $TRUE, group => $TRUE, subfields => [ { type => 'number', label => 'Round #', name => 'draft_round', placeholder => 1, }, { type => 'dropdown', name => 'draft_type', choices => @draft_type_choices, }, ] }, { type => 'hidden', name => 'query_type', val => 'year_round', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, @subfields_hidden ] ); $formsettings2{formdata} = $param_ref if ( $param_ref->{query_type} eq 'year_round' ); ######################################################################### my %formsettings3 = ( id => 'franch_year', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), method => 'get', action => '/draft/', fields => [ { label => 'Franchise', type => 'dropdown', name => 'team_ID', choices => @teams, }, { linear => $TRUE, group => $TRUE, subfields => [ { label => 'Year', name => 'year_ID', type => 'dropdown', start => $LAST_YEAR_DRAFT, end => $FIRST_YEAR_DRAFT, step => -1, option_end => 'active', option_end_label => 'All Active Players', }, { type => 'dropdown', name => 'draft_type', choices => @draft_type_choices, }, ] }, { type => 'hidden', name => 'query_type', val => 'franch_year', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, @subfields_hidden ] ); $formsettings3{formdata} = $param_ref if ( $param_ref->{query_type} eq 'franch_year' ); ######################################################################### my %formsettings4 = ( id => 'franch_round', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), method => 'get', action => '/draft/', fields => [ { label => 'Franchise', type => 'dropdown', name => 'team_ID', choices => @teams, }, { linear => $TRUE, group => $TRUE, subfields => [ { type => 'number', label => 'Round #', name => 'draft_round', placeholder => 1, }, { type => 'dropdown', name => 'draft_type', choices => @draft_type_choices, }, ] }, { type => 'hidden', name => 'query_type', val => 'franch_round', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, ] ); $formsettings4{formdata} = $param_ref if ( $param_ref->{query_type} eq 'franch_round' ); ######################################################################### my %formsettings5 = ( id => 'pos_year', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), method => 'get', action => '/draft/', fields => [ { type => 'dropdown', name => 'pos', choices => @pos_choices, }, { linear => $TRUE, group => $TRUE, subfields => [ { label => 'Year', name => 'year_ID', type => 'dropdown', start => $LAST_YEAR_DRAFT, end => $FIRST_YEAR_DRAFT, step => -1, }, { type => 'dropdown', name => 'draft_type', choices => @draft_type_choices, }, ] }, { type => 'hidden', name => 'query_type', val => 'pos_year', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, @subfields_hidden ] ); $formsettings5{formdata} = $param_ref if ( $param_ref->{query_type} eq 'pos_year' ); ######################################################################### # set up the from_type for the hidden and for the actual hook # we set these up with presets, so that they set the hidden values we my %formsettings6 = ( id => 'from_type', #method => 'get', #action => '/draft/', fields => [ { label => 'Drafted From School Type (none set = return all)', group => $TRUE, linear => $TRUE, class => 'js', # we hide these if js is not working. subfields => [@subfields_visible], } ], ); ######################################################################### my %formsettings7 = ( id => 'pos_round', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), method => 'get', action => '/draft/', fields => [ { type => 'dropdown', name => 'pos', choices => @pos_choices, }, { linear => $TRUE, group => $TRUE, subfields => [ { type => 'number', label => 'Round #', name => 'draft_round', placeholder => 1, }, { type => 'dropdown', name => 'draft_type', choices => @draft_type_choices, }, ] }, { type => 'hidden', name => 'query_type', val => 'pos_round', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, @subfields_hidden ] ); $formsettings7{formdata} = $param_ref if ( $param_ref->{query_type} eq 'pos_round' ); ######################################################################### my %formsettings8 = ( id => 'pname', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), method => 'get', action => '/draft/', fields => [ { type => 'text', name => 'name', max => 30, }, { type => 'hidden', name => 'query_type', val => 'pname', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, @subfields_hidden ] ); $formsettings8{formdata} = $param_ref if ( $param_ref->{query_type} eq 'pname' ); ######################################################################### my %formsettings9 = ( id => 'came_from', class => ( $param_ref->{query_type} ? 'deserialize' : '' ), method => 'get', action => '/draft/', fields => [ { type => 'text', name => 'came_from', max => 70, }, { type => 'hidden', name => 'query_type', val => 'came_from', param => 'data-no-serial', }, { type => 'submit', val => 'Get Picks', }, @subfields_hidden ] ); $formsettings9{formdata} = $param_ref if ( $param_ref->{query_type} eq 'came_from' || $param_ref->{query_type} eq 'key_school' ); #print '
'.Dumper($param_ref);exit;

    #########################################################################
    #########################################################################
    my %flexindex = (
        class   => 'wrapable',
        content => [
            {   class => (
                    $param_ref->{query_type} eq 'overall_pick' ? 'active'
                    : ''
                ),
                header => 'Picks by Overall #',
                text   => $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings1 ),
            },
            {   class => (
                    $param_ref->{query_type} eq 'year_round' ? 'active'
                    : ''
                ),
                header => 'Picks by Year/Round',
                text   => $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings2 ),
            },
            {   class => (
                    $param_ref->{query_type} eq 'franch_year' ? 'active'
                    : ''
                ),
                header => 'Picks by Franchise/Year (& actives)',
                text   => $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings3 ),
            },
            {   class => (
                    $param_ref->{query_type} eq 'franch_round' ? 'active'
                    : ''
                ),
                header => 'Picks by Franchise/Round',
                text   => $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings4 ),
            },
            {   class => (
                    $param_ref->{query_type} eq 'pos_year' ? 'active'
                    : ''
                ),
                header => 'Picks by Position/Year',
                text   => $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings5 ),
            },

            #    $output .= $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings6 );
            {   class => (
                    $param_ref->{query_type} eq 'pos_round' ? 'active'
                    : ''
                ),
                header => 'Picks by Position/Round',
                text   => $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings7 ),
            },
            {   class => (
                    $param_ref->{query_type} eq 'pname' ? 'active'
                    : ''
                ),
                header => 'Player Name',
                text   => '

Make sure name is 3+ characters.

' . $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings8 ), }, { class => ( ( $param_ref->{query_type} eq 'came_from' || $param_ref->{query_type} eq 'key_school' ) ? 'active' : '' ), header => 'Came From', text => '

enter 2-letter code for states

' . $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings9 ), }, ], ); $output .= ''; $output .= $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings6 ); $output .= $srtemplates->process( 'Partials/ContentSection/FlexIndex.tt2', \%flexindex ); return $output; }