#!/usr/bin/perl # AUTHORS # Sean Forman # Hans Van Slooten # $Date: 2017-03-10 13:58:22 -0500 (Fri, 10 Mar 2017) $ # $Author: sean $ # $Rev: 49803 $ # $HeadURL: http://svn.sports-reference.com/svn/br_repos/br/trunk/register/affiliate.cgi $ # Copyright 2000-2017, SPORTS REFERENCE, LLC All rights reserved. ################################################################### # Usage : affiliate.cgi # Purpose : returns an affiliate summary. Gives all of a team's affiliates in a single page # Returns : a-z full webpage # Parameters : id (the sabr affiliateID), and/or a year. # Throws : none # See Also : # Comments : the id here is a franchise ID, or cc_codeClub that # appears in sabr_teams, so we need to handle each case. ################################################################### use Carp; use CGI; use Data::Dumper; use FindBin qw($Bin); use Modern::Perl '2010'; use lib "$Bin/../lib"; use SR::Defaults; use SR::Statline; use SR::Cache; use SRlocal::DB; use SRlocal::Defaults; use SRlocal::Constants; use SRlocal::Formatting; use SRlocal::Players; use SRlocal::Register; use SRlocal::Stats; use SRlocal::Register::Batting; use SRlocal::Register::Fetch; use SRlocal::Register::Fielding; use SRlocal::Register::Pitching; use SRlocal::Register::Managers; use SRlocal::Register::Model; use SRlocal::Templates; chomp($0); #### [] Starting: "$Bin/$0 " . join(' ',@ARGV) MAIN: { # Get the db handle and a reference to a hash of site params our $dbh = get_site_db_connection($Bin); our $site_params_ref = get_site_params($Bin); our $q = new CGI; our $srtemplates = SRlocal::Templates->new; # Define the site header values within a hash and send the hash to # the header script. # Set the page title. my $aff_id = is_nonempty_print( $q->param('id') ); my $year_id = is_nonempty_print( $q->param('year') ); my $clean_aff_id = $aff_id; $clean_aff_id =~ s/^cc_//g; my $key = _nempp($aff_id) . ':' . _nempp($year_id); my $page_data = cache_get( 'register:affiliates', $key ); # check to see if we got a result. if ( is_nonempty($page_data) ) { print "SR-SRC: memcached\n"; print "CONTENT-TYPE: text/html\n\n"; $page_data =~ s|CONTENT-TYPE: *text/html||i; print $page_data; $dbh->disconnect; exit; } else { print "SR-SRC: build\n"; } # we go through a couple of hoops here to get a franchise name # that is relevant to the request at hand. A particular year? # All-Time? not mlb? my $aff_name = get_table_row_column( $dbh, 'majors_team', { franch_ID => $aff_id, year_ID => $year_id }, 'name' ); $aff_name = get_table_row_column( $dbh, 'majors_franchises', { franch_ID => $aff_id }, 'franch_name' ) if ( is_empty($aff_name) ); $aff_name = get_table_row_column( $dbh, 'sabr_teams', { name_code => is_nonempty_print($clean_aff_id), year => ( $CURRENT_YEAR - 1 ) }, 'name_full' ) if ( is_empty($aff_name) ); my $page_title = qq{$year_id $aff_name Minor League Affiliates}; # Set up the "You Are Here" line. my @yah = ( qq{Register}, qq{Affiliates}, qq{$page_title} ); ## Build the URL for this page my $url = qq{/register/affiliate.cgi}; my @q; push @q, "id=$aff_id" if $aff_id; push @q, "year=$year_id" if $year_id; $url .= '?' . join( '&', @q ) if @q > 0; ## Ugh, hate to fetch one value.. my $team_id = gtrc( $dbh, 'majors_team', { franch_ID => $aff_id, year_ID => $year_id }, 'team_ID' ); my %model = ( header => { page_title => $page_title, you_are_here => generate_you_are_here( \@yah ), qi_section => 'register', page_url => $SRlocal::Constants::SITE_URL . $url, use_default_keywords => $FALSE, page_description => qq{Every Affiliates Statistics and the Stats for Every Player in the Organization Farm System}, }, affiliate_id => $aff_id, bio => { data => { year_id => $year_id, season => $year_id, franch_id => $aff_id, team_id => $team_id } }, ); my $template = 'Pages/General.tt2'; # validate the year_id passed in. if ( $year_id && $year_id !~ /^[0-9]{4}$/ ) { print $q->redirect( -uri => $SITE_URL . $SRlocal::Register::DIR_URL . '/', -status => '301 Moved Permanently' ); exit; } # year or team. if ( $year_id && $aff_id ) { $template = 'Pages/Team.tt2'; # print out all of the teams. _register_get_franch_year_affiliations( $dbh, $srtemplates, \%model, $aff_id, $aff_name, $year_id ); } # year, but no team elsif ( $year_id && !$aff_id ) { # print out all of the teams. _register_get_year_affiliations( $dbh, $srtemplates, \%model, $year_id ); } # team, but no year elsif ( !$year_id && $aff_id ) { # print out all of the teams. _register_get_franch_affiliations( $dbh, $srtemplates, \%model, $aff_id, $aff_name ); } # the main index else { $template = 'Pages/FrontPage.tt2'; # print out all of the teams. _register_get_affiliations( $dbh, $srtemplates, \%model ); } $model{inner_nav} = build_register_inner_nav( $dbh, 'affiliates' ); $page_data = $srtemplates->process( $template, \%model ); # stuff the page into our cache. $page_data =~ s|CONTENT-TYPE: *text/html||i; cache_set( 'register:affiliates', $key, $page_data ); print $q->header( -type => 'text/html', -charset => 'utf-8' ); print $page_data; $dbh->disconnect; } #### [] Finished: "$Bin/$0 " . join(' ',@ARGV) ################################################################### # Usage : _get_franch_year_minor_league_affiliations # Purpose : returns a summary of affiliates for a particular year for a franch # Returns : the interior of a page. # Parameters : dbh, fh, id, name, year # Throws : nothing # See Also : # Comments : ################################################################### sub _register_get_franch_year_affiliations { my ( $dbh, $srtemplates, $model, $franch_id, $name, $year_id ) = @_; ## printf $fh ( qq{

%s %s Minor League Affiliates

\n}, $year_id, ## $name ); $model->{name} = $name; $model->{header_middle} = 'Minor League'; $model->{header_end} = 'Affiliates'; my $prevnext = register_fetch_aff_prevnext( $dbh, $franch_id, $year_id ); if ($prevnext) { $prevnext->{srtemplates} = $srtemplates; $model->{prevnext} = output_prevnext_nav($prevnext); } $model->{bio}{rankings} = register_fetch_aff_rankings( $dbh, $franch_id, $year_id ); ## Display the top prospect rankings from various sources _register_get_aff_top_100_prospects( $dbh, $srtemplates, $model, $franch_id, $name, $year_id ); ## Check to see if we have any teams. my $team_count = register_get_aff_team_pitching_from_id( $dbh, $srtemplates, $model, $franch_id, $year_id ); # there are no pitching stats for this franchise. if ( !$team_count ) { # get a formatted list of franchises. $team_count = _register_get_year_affiliations( $dbh, $srtemplates, $model, $year_id, $franch_id ); # no teams found at all, print a not to the user. if ( !$team_count ) { push @{ $model->{pagecontent} }, <<"...";

This franchise had no minor league affiliates this season (or play has not yet started). See complete minor league affiliate history.

... } return; } register_get_aff_team_batting_from_id( $dbh, $srtemplates, $model, $franch_id, $year_id ); register_get_aff_team_fielding_from_id( $dbh, $srtemplates, $model, $franch_id, $year_id ); register_get_aff_players_batting_from_id( $dbh, $srtemplates, $model, $franch_id, $year_id ); register_get_aff_players_pitching_from_id( $dbh, $srtemplates, $model, $franch_id, $year_id ); return $TRUE; } ################################################################### # Usage : # Purpose : returns a summary of affiliates for a year by franch # Returns : the interior of a page. # Parameters : dbh, fh, id # Throws : nothing # See Also : # Comments : ################################################################### sub _register_get_aff_top_100_prospects { my ( $dbh, $srtemplates, $model, $franch_id, $franch_name, $year_id ) = @_; my $got_prospects = $FALSE; my %flexgrid = ( grid_id => 'top_prospects', title => $franch_name . ' Top Prospects', comment_by_default => $FALSE, # full => $TRUE, groups => [], ); my %group = ( grids => [] ); push @{ $flexgrid{groups} }, \%group; foreach my $site ( 'ba', 'mlb', 'bp' ) { # check to see if we have any applicable of this type. This # is to handle the years we have BA, but not MLB.com. next if ( !gtrc( $dbh, 'sabr_' . $site . '_prospects', { aff => $franch_id }, sprintf( 'sum(year=%d) + sum(year=%d)', $year_id, $year_id + 1 ) ) ); # First we get the affiliates with franchise ID's my $query = <<"END_SQL"; select sabr_$site\_prospects.*, name_common as player, concat('/register/player.fcgi?id=', sabr_$site\_prospects.milbID) as player_link from sabr_$site\_prospects join sabr_people using (milbID) where sabr_$site\_prospects.year=? and aff=? order by rank asc END_SQL my $sth = $dbh->prepare($query) || croak qq{Error Running query\n$query\n}; my $grid_title = sprintf( qq{%s}, $site eq 'ba' ? 'Baseball America' : $site eq 'mlb' ? 'MLB.com' : 'BaseballProspectus.com' ); my %grid = ( tabular => $TRUE, title => $grid_title, no_hide => $TRUE, rows => [] ); my $got_prospects_table = $FALSE; foreach my $y ( $year_id, $year_id + 1 ) { $sth->execute( $y, $franch_id ) || croak qq{Error Running query exec\n$query\n} . $dbh->errstr; if ( $sth->rows ) { $got_prospects = $TRUE; $got_prospects_table = $TRUE; ## Display a title row for before and after season prospect lists my $row_title = $y == $year_id ? 'Before ' . $year_id . ' Season' : 'After ' . $year_id . ' Season'; my %title_row = ( subhead => $TRUE, cols => [ { text => $row_title, colspan => 3 } ] ); push @{ $grid{rows} }, \%title_row; ## Display the prospects while ( my $tmp = $sth->fetchrow_hashref ) { my %row = ( cols => [] ); push @{ $row{cols} }, { text => $tmp->{rank} }; push @{ $row{cols} }, { text => $tmp->{player} }; push @{ $row{cols} }, { text => $tmp->{pos} }; push @{ $grid{rows} }, \%row; } } } if ($got_prospects_table) { my $footer = <<"END_HTML"; Rankings are by Baseball America,
the best source for minor league baseball news. END_HTML $footer = <<"END_HTML" if ( $site eq 'mlb' ); Rankings are by Jonathan Mayo of MLB.com. END_HTML $footer = <<"END_HTML" if ( $site eq 'bp' ); Rankings are by BaseballProspectus.com. END_HTML $grid{footnote} = $footer; push @{ $group{grids} }, \%grid; } } if ($got_prospects) { push @{ $model->{pagecontent} }, $srtemplates->process( 'Partials/ContentSection/FlexGrid.tt2', \%flexgrid ); } } ################################################################### # Usage : _register_get_year_affiliations # Purpose : returns a summary of affiliates for a year by franch # Returns : the interior of a page. # Parameters : dbh, fh, id # Throws : nothing # See Also : # Comments : ################################################################### sub _register_get_year_affiliations { my ( $dbh, $srtemplates, $model, $year_id, $franch_id ) = @_; my $where = $EMPTY_STR; my @params; if ( is_nonempty($franch_id) ) { $where = 't.franchID=? and '; push @params, $franch_id; } # First we get the affiliates with franchise ID's my $query = <<"END_SQL"; select t.name_full, t.key_team, l.classification, l.name_code, l.year, t.name_short, mt.franch_ID, mt.name as franch_name from register_teams t join register_affiliates a on t.key_team = a.key_affiliate join register_leagues l on l.key_league = t.key_league join majors_team mt on mt.franch_ID = a.franchID and mt.year_ID=l.year where $where l.year=? order by mt.name, t.name_full asc END_SQL push @params, $year_id; my $count = 0; my $sth = db_prepare_and_execute( $dbh, $query, @params ); if ( $sth->rows ) { my $affiliations = {}; my %franchs; my %franch_names; my @rows; my $prev_franch = ''; my %used_stats; while ( my $tmp = $sth->fetchrow_hashref ) { $count++; if ( $prev_franch ne $tmp->{franch_ID} ) { if ( _nemp($prev_franch) ) { $affiliations->{franch_name_append} = qq{ ($affiliations->{franch_name_append} teams)}; push @rows, $affiliations; } $affiliations = {}; $affiliations = { franch_name_append => 0, franch_name => $tmp->{franch_name}, franch_name_link => qq{/register/affiliate.cgi?id=$tmp->{franch_ID}&year=$year_id} }; } $tmp->{name_short} =~ s/ / /g; $tmp->{name_full} =~ s/ / /g; my $class; if ( ( $tmp->{classification} =~ /^A{1,3}$/ ) || ( $tmp->{classification} =~ /^Rk/ ) || ( $tmp->{classification} eq 'A+' ) || ( $tmp->{classification} eq 'A-' ) || ( $tmp->{classification} =~ /^FRK$/i ) ) { $class = $tmp->{classification}; } else { $class = 'other'; } $affiliations->{franch_name_append}++; my $class_name = $SRlocal::Constants::MILB_CLASSIFICATIONS{$class}{name}; $used_stats{$class_name} = $SRlocal::Constants::MILB_CLASSIFICATIONS{$class}{order}; if ( exists $affiliations->{$class_name} ) { $affiliations->{$class_name} .= ', '; } else { $affiliations->{$class_name} = ''; } $affiliations->{$class_name} .= qq{ } . qq{$tmp->{name_short}}; $prev_franch = $tmp->{franch_ID}; } ## Add the last franchise $affiliations->{franch_name_append} = qq{ ($affiliations->{franch_name_append} teams)}; push @rows, $affiliations; ## The table settings my $table_title = $year_id . ' Season Minor League Affiliates'; my %table_defn = ( table_id => 'affiliates', title => $table_title, caption => $table_title, suppress_header => $TRUE, table_cols_to_freeze => 1, comment_by_default => $FALSE, hide_long => $TRUE, ); ## Start the table my $table = generate_statline_header_wrap( \%table_defn ); ## Not Assists here. $STATLINE_DEFAULTS{A}{mouseover_text} = ''; $STATLINE_DEFAULTS{A}{justify} = 'left'; ## Hack for year justification $STATLINE_DEFAULTS{franch_name}{header} = 'Franchise'; $STATLINE_DEFAULTS{franch_name}{justify} = 'left'; ## First Headers my @classes = sort { $used_stats{$b} <=> $used_stats{$a} } keys %used_stats; ## Left justify classifications $STATLINE_DEFAULTS{$_}{justify} = 'left' foreach @classes; my @stats = ( 'franch_name', @classes ); $table .= generate_statline_header( \@stats ); foreach my $row (@rows) { ## Statline $table .= generate_statline( \@stats, $row ); } ## End Table $table .= generate_statline_footer_wrap( \%table_defn ); push @{ $model->{pagecontent} }, $table; push @{ $model->{pagecontent} }, register_data_note( srtemplates => $srtemplates ); } return $count; } ################################################################### # Usage : _register_get_franch_affiliations # Purpose : returns a summary of a team or franchises yby affiliates # Returns : the interior of a page. # Parameters : dbh, fh, id # Throws : nothing # See Also : # Comments : ################################################################### sub _register_get_franch_affiliations { my ( $dbh, $srtemplates, $model, $id, $name ) = @_; my $clean_id = $id; $clean_id =~ s/^cc_//g; # First we get the affiliates with franchise ID's my $query; # this case we are doing the non-mlb franchise option. if ( $id =~ /^cc_/ ) { $query = <<"..."; select t.name_full, t.key_team, l.classification, NULL as W, NULL as L, l.name_code, l.year, t.name_short from register_teams t join register_affiliates as a on t.key_team = a.key_affiliate join register_teams as parent on parent.key_team = a.key_parent join register_leagues as l on l.key_league = t.key_league where parent.name_code=? order by l.year desc ... } else { $query = <<"..."; select t.name_full, t.key_team, l.classification, sum(W) as W, sum(L) as L, l.name_code, l.year, t.name_short from register_teams as t join register_affiliates as a on t.key_team = a.key_affiliate join register_leagues as l using (key_league) join majors_franchises as f on f.franch_ID = a.franchID left join register_seasons as s using (key_league) left join register_standings as st on s.id_season = st.id_season and t.key_team = st.key_team where a.franchID=? and ifnull(s.season_type, 'Regular')='Regular' group by t.key_team order by l.year desc ... } my $sth = db_prepare_and_execute( $dbh, $query, $clean_id ); if ( $sth->rows ) { # This hash is 2-d {classification}{year} and stores links to # teams of that year/class combo. Multiple teams are concatenated # together. my @rows; my %used_stats; my $affiliations = {}; my $prev_year = 0; while ( my $tmp = $sth->fetchrow_hashref ) { if ( $prev_year != $tmp->{year} ) { if ( $prev_year > 0 ) { $affiliations->{year_append} = qq{ ($affiliations->{year_append} Teams)}; push @rows, $affiliations; } $prev_year = $tmp->{year}; $affiliations = { year_append => 0, year => $tmp->{year}, year_link => qq{/register/affiliate.cgi?id=$id&year=$tmp->{year}} }; } $tmp->{name_short} =~ s/ / /g; my $class; if ( ( $tmp->{classification} =~ /^A{1,3}$/ ) || ( $tmp->{classification} =~ /^Rk/ ) || ( $tmp->{classification} eq 'A+' ) || ( $tmp->{classification} eq 'A-' ) || ( $tmp->{classification} =~ /^FRK$/i ) ) { $class = $tmp->{classification}; } else { $class = 'other'; } my $class_name = $SRlocal::Constants::MILB_CLASSIFICATIONS{$class}{name}; $used_stats{$class_name} = $SRlocal::Constants::MILB_CLASSIFICATIONS{$class}{order}; $affiliations->{year_append}++; my $record = _nemp( $tmp->{W} ) ? qq{ ($tmp->{W}-$tmp->{L})} : ''; if ( exists $affiliations->{$class_name} ) { $affiliations->{$class_name} .= ', '; } else { $affiliations->{$class_name} = ''; } $affiliations->{$class_name} .= qq{$tmp->{name_short}}; } ## Add the last affiliations if ( $prev_year > 0 ) { $affiliations->{year_append} = qq{ ($affiliations->{year_append} Teams)}; push @rows, $affiliations; } ## The table settings my $table_title = $name . ' Minor League Affiliates'; my %table_defn = ( table_id => 'affiliates', title => $table_title, caption => $table_title, suppress_header => $TRUE, table_cols_to_freeze => 1, comment_by_default => $FALSE, hide_long => $TRUE, ); ## Start the table my $table = generate_statline_header_wrap( \%table_defn ); ## Not Assists here. $STATLINE_DEFAULTS{A}{mouseover_text} = ''; $STATLINE_DEFAULTS{A}{justify} = 'left'; ## Hack for year justification $STATLINE_DEFAULTS{year}{justify} = 'left'; ## First Headers my @classes = sort { $used_stats{$b} <=> $used_stats{$a} } keys %used_stats; ## Left justify the classifications $STATLINE_DEFAULTS{$_}{justify} = 'left' foreach @classes; my @stats = ( 'year', @classes ); $table .= generate_statline_header( \@stats ); foreach my $row (@rows) { ## Statline $table .= generate_statline( \@stats, $row ); } ## End Table $table .= generate_statline_footer_wrap( \%table_defn ); push @{ $model->{pagecontent} }, $table; push @{ $model->{pagecontent} }, register_data_note( srtemplates => $srtemplates ); } } ################################################################### # Usage : _register_get_affiliations # Purpose : returns a summary of all affiliations # Returns : the interior of a page. # Parameters : dbh # Throws : nothing # See Also : # Comments : ################################################################### sub _register_get_affiliations { my ( $dbh, $srtemplates, $model ) = @_; # Run through all of the teams that are listed as affiliates. my $sth = register_fetch_teams_with_affiliates($dbh); $model->{inpage_title} = 'The Register: Minor League Affiliates'; my $teams_html = ''; while ( my $tmp = $sth->fetchrow_hashref ) { $teams_html .= sprintf qq{%s (%d-%d, %d team%s)\n
}, $tmp->{franch_ID}, $tmp->{franch_name}, $tmp->{first_year}, $tmp->{last_year}, $tmp->{teams}, $tmp->{teams} == 1 ? $EMPTY_STR : 's'; } ## Add the MLB team links to the page push @{ $model->{sections} }, { id => 'mlb_teams', header => 'MLB Teams with Affiliates', content => $teams_html }; ## Get all the years with affiliates my $years = register_fetch_affiliate_years($dbh); my $html = ''; foreach my $year (@$years) { $html .= sprintf( qq{

%d

\n}, $year, $year ); } ## Add the affiliates by year links push @{ $model->{sections} }, { id => 'nonmlb_teams', class => 'condensed', header => 'Affiliates by Year', content => $html }; ################# Handle other teams and affiliate agreements # First we get the affiliates with franchise ID's my $query = <<"END_SQL"; SELECT tm.name_full, tm.name_code, MIN(lg.year) AS first_year, MAX(lg.year) AS last_year, COUNT(DISTINCT tm2.key_team) AS teams FROM register_teams tm INNER JOIN register_affiliates aff ON tm.key_team = aff.key_parent INNER JOIN register_teams tm2 ON aff.key_affiliate = tm2.key_team INNER JOIN register_leagues lg ON tm2.key_league = lg.key_league WHERE tm2.franchID IS NULL AND tm.key_team != tm2.key_team AND tm.franchID IS NULL GROUP BY tm.name_code HAVING teams > 0 ORDER BY tm.name_full END_SQL $sth = db_prepare_and_execute( $dbh, $query ); $teams_html = ''; while ( my $tmp = $sth->fetchrow_hashref ) { $teams_html .= sprintf qq{%s (%d-%d, %d team%s)\n
}, $tmp->{name_code}, $tmp->{name_full}, $tmp->{first_year}, $tmp->{last_year}, $tmp->{teams}, $tmp->{teams} == 1 ? $EMPTY_STR : 's'; } ## Add the non-MLB team links to the page push @{ $model->{sections} }, { id => 'nonmlb_teams', header => 'Other non-MLB Teams with Affiliates', content => $teams_html }; ## Add the data source notes push @{ $model->{sections} }, { id => 'register_data', header => 'About the Register Data', content => SRlocal::Register::register_data_note( srtemplates => $srtemplates, section => $TRUE ), }; }