#! /usr/bin/perl

# show_scores - show Grade-Keeping Project scores for a given date

use DBI;
use strict;

# parse connection parameters from command line if given

use Getopt::Long;
$Getopt::Long::ignorecase = 0; # options are case sensitive

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

# GetOptions doesn't seem to allow -uuser_name form, only -u user_name?
GetOptions(
	# =s means a string argument is required after the option
	"host|h=s"      => \$host_name
	,"user|u=s"      => \$user_name
	# :s means a string argument is optional after the option
	,"password|p:s"  => \$password
) or exit (1);

# solicit password if option specified without option value
if (defined ($password) && !$password)
{
	# turn off echoing but don't interfere with STDIN
	open (TTY, "/dev/tty") or die "Cannot open terminal\n";
	system ("stty -echo < /dev/tty");
	print STDERR "Enter password: ";
	chomp ($password = <TTY>);
	system ("stty echo < /dev/tty");
	close (TTY);
	print STDERR "\n";
}

# construct data source
my ($dsn) = "dbi:mysql:samp_db";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=$ENV{HOME}/.my.cnf";

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


# MAIN-BODY
# check for a command argument, perform rudimentary format check
@ARGV == 1 or die "No date given\n";
$ARGV[0] =~ /^\d+[^\d]\d+[^\d]\d+$/ or die "Malformed date: $ARGV[0]\n";

my ($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.date = ?
	ORDER BY
		event.date ASC, event.type ASC, score.score DESC
});
$sth->execute ($ARGV[0]);

# print heading, then scores
printf "%-12s  %10s  %5s  %4s\n", "Name", "Date", "Score", "Type";
while (my $hash_ref = $sth->fetchrow_hashref ())
{
	printf "%-12s  %10s  %5s  %4s\n",
				$hash_ref->{name},
				$hash_ref->{date},
				$hash_ref->{score},
				$hash_ref->{type};
}
$sth->finish ();
# MAIN-BODY

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