#! /usr/bin/perl

# score_browse - browse through test and quiz scores

use DBI;
use CGI;
use strict;

# default connection  parameters - all missing
my ($host_name, $user_name, $password) = (undef, undef, undef);
my ($db_name) = "samp_db";

# construct data source
my ($dsn) = "dbi:mysql:$db_name";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=/usr/local/apache/conf/samp_db.cnf";

# connect to server
my (%attr) = ( RaiseError => 1 );
my ($dbh) = DBI->connect ($dsn, $user_name, $password, \%attr);

my ($cgi) = new CGI;

# put out initial part of page
my ($title) = "Grade-Keeping Project -- Score Browser";
print $cgi->header ();
print $cgi->start_html (-title => $title);
print $cgi->h1 ($title);

# TEST-EVENT
# parameter that tells us which event to display scores for
my ($event_id) = $cgi->param ("event_id");

# if $event_id has a value, display the event list.
# otherwise display the scores for the given event.
if (!$event_id)
{
	display_events ()
}
else
{
	display_scores ($event_id);
}
# TEST-EVENT

print $cgi->end_html ();

$dbh->disconnect ();
exit (0);

# DISPLAY_CELL
# display a value in a table cell; put non-breaking
# space in "empty" cells so borders show up

sub display_cell
{
my ($tag, $value, $encode) = @_;

	$value = $cgi->escapeHTML ($value) if $encode;
	$value = " " unless $value;
	print "<$tag>$value</$tag>\n";
}
# DISPLAY_CELL

# DISPLAY_EVENTS
sub display_events
{
my ($sth, $url);

	print "Select an event by clicking on its number:<BR><BR>\n";

	# get list of events
	$sth = $dbh->prepare (qq{
		SELECT event_id, date, type
		FROM event
		ORDER BY event_id
	});
	$sth->execute ();

	# display table with a border
	print "<TABLE BORDER>\n";
	# use column names for table column headings
	print "<TR>\n";
	foreach my $col_name (@{$sth->{NAME}})
	{
		display_cell ("TH", $col_name, 1);
	}
	print "</TR>\n";

	# associate each event id with a link that will show the
	# scores for the event; return rows using a hash to make
	# it easy to refer to the event_id column value by name.
	while (my $hash_ref = $sth->fetchrow_hashref ())
	{
		print "<TR>\n";
		$url = $cgi->script_name ();
		$url .= sprintf ("?event_id=%s",
						$cgi->escape ($hash_ref->{event_id}));
		display_cell ("TD",
					$cgi->a ({-href => $url}, $hash_ref->{event_id}), 0);
		display_cell ("TD", $hash_ref->{date}, 1);
		display_cell ("TD", $hash_ref->{type}, 1);
		print "</TR>\n";
	}
	$sth->finish ();
	print "</TABLE>\n";
}
# DISPLAY_EVENTS

# DISPLAY_SCORES
sub display_scores
{
my ($event_id) = shift;
my ($sth);

	# a URL without any event_id parameter will
	# cause the event list to be displayed.
	print $cgi->a ({-href => $cgi->script_name ()}, "Show Event List");
	print "<BR><BR>\n";

	# select scores for the given event
	$sth = $dbh->prepare (qq{
		SELECT
			student.name, event.date, score.score, event.type
		FROM
			student, score, event
		WHERE
			student.student_id = score.student_id
			AND score.event_id = event.event_id
			AND event.event_id = ?
		ORDER BY
			event.date ASC, event.type ASC, score.score DESC
	});
	$sth->execute ($event_id);	# pass event ID as placeholder value

	print "<B>Scores for event $event_id</B><BR>\n";

	# display table with a border
	print "<TABLE BORDER>\n";
	# use column names for table column headings
	print "<TR>\n";
	foreach my $col_name (@{$sth->{NAME}})
	{
		display_cell ("TH", $col_name, 1);
	}
	print "</TR>\n";

	while (my @ary = $sth->fetchrow_array ())
	{
		print "<TR>\n";
		display_cell ("TD", shift (@ary), 1) while @ary;
		print "</TR>\n";
	}

	$sth->finish ();
	print "</TABLE>\n";
}
# DISPLAY_SCORES