# mod_perl
#
#
#
# courselist.pl: generates course lists
#
# 20000113 matt@gold.ac.uk
#  spliced
# 19990913 matt@gold.ac.uk
#  finished and forced user authentication
# 19990813 matt@gold.ac.uk
#  discovered that you could do "SELECT DISTINCT" ;-)
# 19990805 matt@gold.ac.uk
#  (I haven't fully mastered the subleties of mod_perl yet)

######################### "formalities"

my ($result, @row, @r);
my %in = mcsdb::getvars();
my $conn = mcsdb::mcsconn($in{user},$in{pass});
exit 0 if $conn == -1;

######################### Now the script proper

unless (defined($in{course})) {     # no inputs => print the pick lists

    print <<EOFT;
<html><head><title>Course Lists</title></head><body bgcolor="#abcdef" text="012345">
<form method=post action="courselist.pl">
<h1>Course Lists</h1>
<p>Use this page to generate a course list from the database.</p>
<input name=user type=hidden value="$in{user}">
<input name=pass type=hidden value="$in{pass}"></p></small>
<p><b>Course:</b><br><select name=course size="8">
EOFT

    $result = $conn->exec("SELECT DISTINCT courses.course,name FROM courses,rv_courselists WHERE courses.course=rv_courselists.course ORDER BY course");
#    die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus;

    while (@row = $result->fetchrow) {
	s/\s+$// for @row; # strip tailing spaces
	print "<option value=\"$row[0]\">", join(": ", @row), "</option>\n";
    }

print <<EOFT;
</select><br><b>Year:</b> <select name=ayear>
EOFT

    $result = $conn->exec("SELECT DISTINCT ayear FROM rv_courselists ORDER BY ayear DESC");
#    die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus;

    while (my @row = $result->fetchrow) {
	s/\s+$// for @row; # strip trailing spaces
	print "<option value=\"$row[0]\">$row[0]</option>\n";
    }
    print '</select><br><input type="radio" name="sort" value="surname,forename"> Sort by student\'s name<br><input type="radio" name="sort" value="mode DESC,surname,forename"> Sort by mode (reversed), then name<br><input type="radio" name="sort" value="cattempt,surname,forename" checked> Sort by attempt, then name</b></p><input type=submit></form>';

} else {

    #### ascertain the course name from the course code

    $result = $conn->exec("SELECT name FROM courses WHERE course = '$in{course}' ");
#    die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus;
    @r=$result->fetchrow or $r[0]="";

    print <<EOTT;
<html><head><title>${in{course}}: ${r[0]} ${in{ayear}}</title></head>
<body><table border><tr><th colspan=6><big>${in{course}}: $r[0] $in{ayear}</big>
EOTT

    #### collect the "credits"!

    $result = $conn->exec(
        "SELECT cstaffallocs.role, forename, surname, email, cstaffallocs.semester
	 FROM cstaffallocs, rv_staff
	 WHERE
             cstaffallocs.course = '$in{course}' AND
             cstaffallocs.ayear = '$in{ayear}' AND
             cstaffallocs.staff = rv_staff.staff
         ORDER BY cstaffallocs.semester, cstaffallocs.role, surname, forename");
#    die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus;

    print "<br>$r[0]", ($r[4] eq "") ? "" : " (semester $r[4])" ,": $r[1] $r[2] &lt;<a href=\"mailto:$r[3]\">$r[3]</a>&gt;"
        while (@r=$result->fetchrow);

    print "</th></tr><tr><th>Student</th><th><small>Programme</small></th><th><small>mode</small></th><th><small>attempt#</small></th><th><small>Tutor</small></i></th><th>signature........................</th></tr>";

    #### now execute the big query, an SQL join across 4 tables via a view

    $result = $conn->exec(
	"SELECT title, forename, surname, ucascode, mode, cattempt, ptutor
         FROM rv_courselists
         WHERE
             course = '$in{course}' AND
             ayear = '$in{ayear}' AND
	     student = rv_students.student AND
	     rv_students.regstatus = 'R'
         ORDER BY $in{sort}");

#    die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus;
    my $hits=0; my ($col, $notcol) = ('"#fedcba"', '"#abcdef"'); # stripes!

    while (@row = $result->fetchrow) {
	s/\s+$// for @row; # remove trailing spaces

	## format the row!
	@row=($row[0]." ".$row[1]." ".$row[2],"<small>".$row[3]."</small>",$row[4],$row[5],"<small>".$row[6]."</small>");

	print "<tr><td bgcolor=$col>",
	    join "</td><td bgcolor=$col>" => @row, "&nbsp;</td></tr>";
	$hits++; ($col, $notcol) = ($notcol, $col);
    }

    print "<tr><td colspan=6>Total: <b>$hits</b>; correct as of <b>",
	scalar localtime, "</b></td></tr></table>";

}

print "</body></html>";

######################### EOF
