#!/usr/bin/perl -w # AUTHORS # Sean Forman # Hans Van Slooten # $Date: 2017-04-25 16:48:16 -0400 (Tue, 25 Apr 2017) $ # $Author: hvs $ # $Rev: 51322 $ # $HeadURL: http://svn.sports-reference.com/svn/br_repos/br/trunk/friv/trade-partners.cgi $ # Copyright 2000-2017, SPORTS REFERENCE, LLC All rights reserved. use Carp; use CGI; use FindBin qw($Bin); use Modern::Perl '2010'; use lib "$Bin/../lib"; use SR::Defaults; use SR::Statline; use SR::Cache; use SRlocal::Constants; use SRlocal::Defaults; use SRlocal::DB; use SRlocal::Formatting; use SRlocal::Leagues; use SRlocal::Stats; use SRlocal::Templates; my %FRANCH_NAMES = ( 'ARI' => 'Arizona Diamondbacks', 'ATL' => 'Atlanta (BOS/MIL) Braves', 'BAL' => 'Baltimore Orioles/StL Browns', 'BOS' => 'Boston Red Sox', 'CHC' => 'Chicago Cubs', 'CHW' => 'Chicago White Sox', 'CIN' => 'Cincinnati Reds', 'CLE' => 'Cleveland Indians/Naps', 'COL' => 'Colorado Rockies', 'DET' => 'Detroit Tigers', 'FLA' => 'Miami/Florida Marlins', 'HOU' => 'Houston Astros/Colt .45\'s', 'ANA' => 'LA (Anah./Cal) Angels of Ana', 'LAD' => 'Los Angeles (BRO) Dodgers', 'KCR' => 'Kansas City Royals', 'MIL' => 'Milwaukee Brewers/Sea. Pilots', 'MIN' => 'Minnesota Twins/Senators(-1960)', 'NYM' => 'New York Mets', 'NYY' => 'New York Yankees', 'OAK' => 'Oakland (KC/PHI) Athletics', 'PHI' => 'Philadelphia Phillies', 'PIT' => 'Pittsburgh Pirates', 'STL' => 'St. Louis Cardinals', 'SDP' => 'San Diego Padres', 'SFG' => 'San Francisco (NY) Giants', 'SEA' => 'Seattle Mariners', 'TBD' => 'Tampa Bay Rays', 'TEX' => 'Texas Rangers/Senators (61-71)', 'TOR' => 'Toronto Blue Jays', 'WSN' => 'Washington Nats/Les Expos', ); chomp($0); MAIN: { # Connect to the database. our $dbh = get_site_db_connection($Bin); # see if we have a memd option here. our $memd = get_memcached_connection($Bin); my $memd_key = $ENV{'REQUEST_URI'}; my $page_data = SR::Cache::get_data_from_cache( { dbh => $dbh, memd => $memd, key => $memd_key } ); #my $page_data; my $q = new CGI; print $q->header; # if we got data, print it. if ( is_nonempty($page_data) && !defined $q->param('bust_cache') ) { print $page_data; $dbh->disconnect; exit; } our $site_params_ref = get_site_params($Bin); my $srtemplates = new SRlocal::Templates; $site_params_ref->{serve_as_cgi_script} = $TRUE; ##################################################################################### ##################################################################################### my $franch_ID_1 = $q->param('franch_ID_1'); my $franch_name_1 = ( defined $franch_ID_1 ) ? gtrc( $dbh, 'majors_franchises', { franch_ID => $franch_ID_1 }, 'franch_name' ) : undef; my $franch_ID_2 = $q->param('franch_ID_2'); my $franch_name_2 = ( defined $franch_ID_2 ) ? gtrc( $dbh, 'majors_franchises', { franch_ID => $franch_ID_2 }, 'franch_name' ) : undef; # Build the page start my $page_title = 'Find Franchise Trade History'; $page_title .= $SPACE . 'between ' . $franch_name_1 . ' & ' . $franch_name_2 if ( is_nonempty($franch_name_1) && is_nonempty($franch_name_1) ); my $yah = generate_you_are_here( [ 'Frivolities', qq{$page_title} ] ); my %model; $model{header} = { page_title => $page_title, page_url => $SRlocal::Constants::SITE_URL . '/friv/trade-partners.cgi', you_are_here => $yah, qi_section => 'friv', use_default_keywords => $FALSE, page_description => qq{Find baseball transactions between any two teams}, }; $model{inner_nav} = [ { index => $TRUE, label => "Frivolities Index", link => "/friv/" } ]; # print out a form for the franchises. my $query = 'SELECT franch_name, mf.franch_ID FROM majors_franchises mf INNER JOIN transactions_franch USING (franch_ID) WHERE active="Y" GROUP BY mf.franch_ID ORDER BY franch_name ASC'; my $sth = $dbh->prepare($query); $sth->execute; my @franch_list; my @franch_list_second = ( { val => 'ALL', label => qq{Most recent with each franchise (may take 10-20 sec)} } ); while ( my $tmp = $sth->fetchrow_hashref ) { push @franch_list, { val => $tmp->{franch_ID}, label => $FRANCH_NAMES{ $tmp->{franch_ID} } }; push @franch_list_second, { val => $tmp->{franch_ID}, label => $FRANCH_NAMES{ $tmp->{franch_ID} } }; } my $output = qq{

Find all trades between two franchises:

}; my %formsettings = ( method => 'get', action => '/friv/trade-partners.cgi', fields => [ { type => 'dropdown', name => 'franch_ID_1', choices => \@franch_list, val => $franch_ID_1 }, { type => 'dropdown', name => 'franch_ID_2', choices => \@franch_list_second, val => $franch_ID_2 }, { type => 'submit', val => 'Find Trades', }, ], ); $output .= $srtemplates->process( 'Partials/Forms/Form.tt2', \%formsettings ); push @{ $model{pagecontent} }, $output; push @{ $model{pagecontent} }, _get_franchise_trades( $dbh, $srtemplates, \@franch_list, $franch_ID_1, $franch_name_1, $franch_ID_2, $franch_name_2 ); $page_data = $srtemplates->process( 'Pages/General.tt2', \%model ); print $page_data; # stuff the page into our cache. SR::Cache::put_data_in_cache( { dbh => $dbh, memd => $memd, key => $memd_key, data => $page_data, } ); } sub _get_franchise_trades { my ( $dbh, $srtemplates, $franch_list, $franch_ID_1, $franch_name_1, $franch_ID_2, $franch_name_2 ) = @_; my $output = ''; my $title = ''; if ( is_nonempty($franch_ID_1) && is_nonempty_print($franch_ID_2) eq 'ALL' ) { $title = qq{All $franch_name_1 Trades}; my $query = <<"END_SQL"; SELECT * FROM transactions WHERE tran_ID IN (select tran_ID from transactions_franch WHERE franch_ID=?) AND tran_ID IN (select tran_ID from transactions_franch WHERE franch_ID=?) GROUP BY tran_ID ORDER BY tran_date DESC LIMIT 1 END_SQL my $sth = $dbh->prepare($query); my $query_count = <<"END_SQL"; SELECT COUNT(distinct tran_ID) AS trans_number FROM transactions WHERE tran_ID IN (select tran_ID from transactions_franch WHERE franch_ID=?) AND tran_ID IN (select tran_ID from transactions_franch WHERE franch_ID=?) END_SQL my $sth_count = $dbh->prepare($query_count); foreach my $franchise (@$franch_list) { my $franch = $franchise->{val}; next if $franch eq $franch_ID_1; $sth->execute( $franch_ID_1, $franch ); $franch_name_2 = gtrc( $dbh, 'majors_franchises', { franch_ID => $franch }, 'franch_name' ); if ( $sth->rows ) { # get the count for the number of trans between teams. $sth_count->execute( $franch_ID_1, $franch ); my $tmp_count = $sth_count->fetchrow_hashref; my $trade_count = $tmp_count->{trans_number}; # print out the team, the # of trades and the last trade $output .= sprintf qq{

$franch_name_2 view %d trade%s

\n}, $franch_ID_1, $franch, $trade_count, $trade_count == 1 ? $EMPTY_STR : 's'; while ( my $tmp = $sth->fetchrow_hashref ) { my $trans = SRlocal::Transactions::get_general_transactions( $dbh, $tmp, $FALSE, { exclude_p => $TRUE } ); $output .= sprintf qq{

%s. %s

}, $trans->{date}, $trans->{text}; } $output .= qq{

}; } else { $output .= qq{

$franch_name_2 No Trades

\n}; } } $sth->finish; $sth_count->finish; } elsif ( is_nonempty($franch_ID_1) && is_nonempty($franch_ID_2) ) { $title = qq{All Trades Between $franch_name_1 & $franch_name_2}; my $query = <<"END_SQL"; SELECT * FROM transactions WHERE tran_ID IN (select tran_ID from transactions_franch WHERE franch_ID=?) AND tran_ID IN (select tran_ID from transactions_franch WHERE franch_ID=?) GROUP BY tran_ID ORDER BY tran_date DESC END_SQL my $sth = $dbh->prepare($query); $sth->execute( $franch_ID_1, $franch_ID_2 ); if ( $sth->rows ) { while ( my $tmp = $sth->fetchrow_hashref ) { my $trans = SRlocal::Transactions::get_general_transactions( $dbh, $tmp, $FALSE ); $output .= sprintf qq{

%s. %s

}, $trans->{date}, $trans->{text}; } } else { $output .= qq{

No trades between these two teams.

}; } $sth->finish; } return output_content_section( { section_content => $output, section_id => 'transactions', comment_by_default => $FALSE, srtemplates => $srtemplates, title => $title } ); return $output; }