#!/usr/local/bin/perl

use CGI qw/:standard/;
use DBI;

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday) = gmtime(time);
$year += 1900;
$startDate = sprintf("%04d%02d01", $year, int($mon / 6) * 6 + 1);
$endDate = sprintf("%04d%02d31", $year + 1, int($mon / 6) * 6 + 1);

print header(-type=>'text/html');

#print "start $startDate end $endDate\n\n";

$SQL_User = 'perl';
$SQL_Pass = 'nhekjdlrre4p0(7ik';
$SQL_Source = 'DBI:mysql:database=fjong;';

$dbh = DBI->connect($SQL_Source, $SQL_User, $SQL_Pass);
my $sql = "SELECT DISTINCT DAYNAME(Date) AS Dayname ".
		     "FROM PracticeSession ".
		     "WHERE Date>='$startDate' AND Date<='$endDate' ".
		     "ORDER BY WEEKDAY(Date)";
$sth = $dbh->prepare($sql);
$sth->execute || die "Error executing SQL statement: $!";

print "<table border='1'><tr>";
print "<th align='center'>Week</th>";
$columns = 1;
while (defined($row=$sth->fetchrow_hashref)) {
    print "<th align='center'>".$$row{Dayname}."</th>";
    $column{$$row{Dayname}} = $columns++;
} 
print "</tr>\n";
$sth->finish;

$sth = $dbh->prepare("SELECT DAYNAME(Date) AS Dayname, ".
		     "WEEK(Date, 3) AS Week, ".
		     "Place, ".
		     "TIME_FORMAT(StartTime, '%H:%i') AS StartTime, ".
		     "TIME_FORMAT(EndTime, '%H:%i') AS EndTime, ".
		     "DATE_FORMAT(Date, '%e %M') AS FormattedDate ".
		     "FROM PracticeSession ".
		     "WHERE Date >='$startDate' AND Date<='$endDate' ".
		     "ORDER BY Date");
$sth->execute || die "Error executing SQL statement: $!";

$lastweek = -1;
while (defined($row = $sth->fetchrow_hashref)) {
    $week = $$row{Week};

    while ($week != $lastweek) {
	if ($lastweek == -1) {
	    $lastweek = $week;
	} elsif ($week > $lastweek) {
	    print "</tr>\n";
	    $lastweek++;
	} elsif ($week < $lastweek) {
	    print "</tr><tr><td>&nbsp;</td></tr>\n";
	    $lastweek = $week;
	}
	print "<tr>";
	print "<td align='center'>$lastweek&nbsp;<br>&nbsp;</td>";
	$currentPrintColumn = 1;
    }
    $dayname = $$row{Dayname};
    $entryColumn = $column{$dayname};
    while ($currentPrintColumn < $entryColumn) {
	print "<td></td>";
	$currentPrintColumn++;
    }
    print "<td>";
    print $$row{FormattedDate}." ";
    print $$row{StartTime}."-".$$row{EndTime}."<br>";
    print $$row{Place};
    print "</td>";
    $currentPrintColumn++;
} 
print "</tr>\n";
print "</table>\n";

$sth->finish;


$dbh->disconnect();