Lab 5
due Saturday June 4th, 2011 12:01 am

This lab is worth 10 extra credit points.

Note that since this is an extra credit lab, if it
doesn't work at all, it will be worth zero.  Please
test it in the location mentioned below, and make
sure it works.  A lab that just shows the example
cgi from class will not earn you any points.

You will write a simple cgi program.  It will display
a form that asks the user which display options they
want for sorting some data that you will read from a
database.

Your program should use the CGI module as discussed
in class.  All it needs to do is check param() to see
if there is anything defined (look at the example I
gave in class)- if there is, assume that the cgi 
params include the proper info for the display and
sorting options as outlined below.  If there is nothing
in the cgi params, then you need to display a simple
form to ask for the options as defined below.

Note that you will need to login to the machine called
cweb.cse.ohio-state.edu.  You will be working in a 
directory that is named according to your username in:
/var/www/cweb/459.51ae.  For example, my directory
for testing to make sure this works is:

/var/www/cweb/459.51ae/joseph.  Your scripts must
have permissions similar to 750 so that the webserver
on cweb can see them, and so that other students
cannot see them.  Note that the group id bit is
set on your directories- this will make all files that
you create in that directory be owned by apache- this
lets the webserver see them at 750 permission, while 
keeping other students from seeing them.  Please respect
this convention so that I know that you are all doing your
own work.

To access your lab through the web server on cweb, put
something like the following into your web browser (I'm
using my test program for this example):

http://cweb.cse.ohio-state.edu/459.51ae/joseph/lab5.cgi

(lab5.cgi is the name of your script, obviously, and
 you need to substitute your username for mine).

When you submit your lab, please leave a current copy
in this directory on cweb as well as submitting it,
so that I can grade them more easily.

You will use the Connect459 module that I have provided
to get a database handle to access the database.  You
can use this simple skeleton to get you started:

---------------------------- begin example ---------------------


#!/usr/bin/perl -w
use strict;
use lib "/opt/c459.51";
use Connect459;
use CGI qw/:standard/;
use Data::Dumper;

my $dbh = get_dbh;

my $sth = $dbh->prepare(qq/SELECT * FROM Player/);

$sth->execute;

my @players;

while (my $hr = $sth->fetchrow_hashref) {
  push @players, {%$hr};
}

$sth->finish;

print header, start_html('Lab 5 cgi connection test');
print p('This is a test to see if your permissions are correct and ' .
        'your connection to the database is working.' .
        'You should see a label asking for your favorite team, ' .
        'and a textfield, followed by a horizontal line, then, ' .
        'a raw dump of player data from the database.  Next should ' .
        'come another horizontal line, and a submit button.  If you ' .
        'hit the submit button, the same page will load again, but ' .
        'whatever you typed into the favorite team field will be ' .
        'printed out as well, after the submit button.');

print start_form,
      "What is your favorite team?", textfield('team'),
      hr,
      p(Dumper(@players)),
      hr,
      submit,
      end_form;

if (param()) {
  print h1("Your favorite team was " . param('team'));
}

print end_html;

--------------------------- end example ---------------------

Now for a definition of the data that you will be processing
from the database.

The data consists of three tables:

"Player" has fields called PLAYER_ID and NAME.  The PLAYER_ID
is a unique number that is used to tie with other tables.  The
NAME is the player's name.  You can assume that this NAME will
always be a string field that has two "words" in it, corresponding
to a first and last name, separated by a space (this information
will be useful for some of the extra credit).

"Fielding" has 4 fields:  PLAYER_ID which corresponds to the same
id in Player, PUT_OUTS which is an integer number of putouts that
this player had this year, ERRORS which is the number of errors
that this player committed this year, and POSITION, which is a 
string that tells what position this player played for his team.
To simplify things, players will only ever have one row in this
table, so they can be considered to always play the same position.

"Batting" has many fields:  PLAYER_ID is the same as the other
two tables, HITS is the number of total hits they had (including
singles, doubles, triples, and home runs), AT_BATS is the total
number of official at bats they had, DOUBLES, TRIPLES, and 
HOME_RUNS are the numbers of doubles, triples, and home runs
that this player hit this year, and WALKS is the number of
walks that they had.  For the non-baseball inclined, you can
calculate the number of singles that they had by subtracting
the number of doubles, triples, and home runs from the HITS
column.  You can assume for the purposes of this class that there
are no fielder's choice or sacrifices to worry about in 
calculating batting average.  There are also NOT different kinds
of walks (such as hit by pitch, intentional, etc.).  Walks also
do not count as an official at bat.

Again for those who may not understand baseball, batting average
is (total hits) / (total at_bats) and is generally displayed as a
3 decimal place number.  For example, 3 hits and 10 at_bats is
a .300 batting average.  The "fielding percentage" of a player
is defined as (total number of putouts) / (total chances), where
total chances is PUT_OUTS + ERRORS from the Fielding table.

For those who do not have an extensive grasp of sql, the following
statements are ONE way of getting all the data that you need
out of the database:

SELECT * FROM Batting

SELECT * FROM Fielding

SELECT * FROM Player

You will obviously need to encase these sql statements into
the perl dbi calls that I mentioned in class.  I'll give one
full example (for the Player table) of fetching all this data
into a hashref:

# assuming a proper connect call to the database has already been
# done

my %players;

my $sth = $dbh->prepare(qq{SELECT * FROM Player});
$sth->execute;

while (defined(my $hr = $sth->fetchrow_hashref)) {
  $players{$hr->{PLAYER_ID}} = {%$hr}; 
}
$sth->finish;

If you want to use this method, you are perfectly welcome to
do so.  You would of course need to repeat similar calls three
times to get all of the data.  If you would like to combine
all the calls into one with joins (if you already know how
to do that) feel free to do so.  If you know some of the other
dbi methods like selectall_hashref, feel free to use that as
well- it would certainly be applicable in this case.


And now for the lab requirements:

(6 ec points for this section)
If you are not messing with the fielding table, all you need to
do is give the user two radio buttons and a submit button- the
radio buttons will tell your program if you want to see the data
sorted by batting average, or sorted by home runs.  In either
case, you should use a descending sort.  So, most home runs
first, or highest batting average first.  There is no need to
break ties for either of these fields.  These two modes are
obviously exclusive.  For the display, just give the player's
name first, and then the field in question (either batting
average or home runs), and obviously you must list them in 
the proper sorted order.

Don't forget that to make radio buttons, you can use this
Perl::CGI code:
(I've assumed a use CGI qw/:standard/; at the top of the program)

        print radio_group(-name=>'group_name',
                          -values=>['eenie','meenie','minie'],
                          -default=>'meenie',
                          -linebreak=>'true',
                          -labels=>\%labels);

The values (as you can see) are specified with an array reference.
If you do not specify the default, it will choose the first item in
the list as a default.  The fourth parameter (linebreak) makes the
options vertical instead of horizontal- this one is a matter of
preference- I don't care if you use it or not.  The last parameter
(labels) is a reference to a hash that contains values for what
you want to be displayed as the name of the label (if you don't
include it, it defaults to whatever the values were).  Again, you
don't have to use this one if you don't want to.


additional credit items:

(1 ec point here)
1.  Break ties in batting average or home runs based on the
    player's last name, and then if that is still the same,
    use their first name.  You will need to split the NAME
    field for this.

(1 ec point here)
2.  Add a third radio button option to the original two- this one
    will sort the players based on fielding percentage- again
    in descending order.  The display should just include the
    player's NAME followed by their fielding percentage.

(1 ec point here)
3.  Add a pulldown menu which asks the user whether they want
    extended statistics for the players.  There should only be
    two choices-  yes and no.  Choose "no" as the default.
    The extended statistics should include a nicely formatted
    output (sorted in whatever order the original mode indicated,
    so in order of home runs if that was the first option, or
    fielding percent, or batting average) of all of the columns
    that we know about the player from the database, as well as
    the computed columns of fielding %, batting average, and
    singles.

(1 ec point here)
4.  Add a set of checkboxes that allow certain positions to be
    filtered out of the output from the program.  The possible
    positions for this item will be:  

Left Field
Center Field
Right Field
Third Base
Shortstop
Second Base
First Base
Catcher
Pitcher

(as much of an American League fan as I am, we'll exclude designated
 hitter for this lab)

    The user should be able to check any number of the buttons,
    and any players with those positions will NOT be displayed
    once they hit the submit button.  Note that these should
    be a checkbox_group (as shown in class)- they are not 
    mutually exclusive, unlike the radio buttons for setting the
    program's main mode.