# declare mod usage
use DBI;
use Echo;
print Echo::carp;    # content-type header

# predefined vars
@courses = ('COSC 314', 'COSC 321', 'COSC 444', 'COSC 423', 'MATH 319'
+);
$htdocs = Echo::path("htdocs");
$current = 0;
$| = 1;

# open body template
$file = "$htdocs/html/grades/body.txt";
open(READ, $file) or die "can't open $file: $!";
  $body = join("", <READ>);
close(READ);

# connect to mysql db
dbh = DBI->connect("DBI:mysql:host=localhost;database=DATABASE", "USER
+", "PASS", {PrintError => 0, RaiseError => 1});

# cycle through courses
foreach $course (@courses) {

  # keep track of current class
  $current++;

  # execute query
  $cursor = $dbh->prepare("SELECT * FROM grades WHERE className='$cour
+se'");
  $cursor->execute();

  # reset vars
  $earned = $possible = 0;
  $assignments = "";

  # loop through result
  while (@result = $cursor->fetchrow_array()) {

    # open assignment template
    $file = "$htdocs/html/grades/assignment.txt";
    open(READ, $file) or die "can't open $file: $!";
      $assignment = join("", <READ>);
    close(READ);

    # substitutions
    $assignment =~ s/VAR_ASSIGNMENTTITLE/$result[1]/;
    $assignment =~ s/VAR_POINTSEARNED/$result[2]/;
    $assignment =~ s/VAR_POINTSPOSSIBLE/$result[3]/;
    $assignments .= $assignment . "\n";

    # add up points
    $earned += $result[2];
    $possible += $result[3];
  }

  # add padding to last TR
  $assignments =~ s/(.*)<tr>/$1<tr class=last>/s;

  # calculate average and grade
  if ($possible ) { $average = sprintf("%2.2f", ($earned / $possible) 
+* 100); } else { $average = "?"; }
  if ($average >= 95) { $grade = "A"; }
    elsif ($average >= 90) { $grade = "A-"; }
    elsif ($average >= 87) { $grade = "B+"; }
    elsif ($average >= 84) { $grade = "B"; }
    elsif ($average >= 80) { $grade = "B-"; }
    elsif ($average >= 77) { $grade = "C+"; }
    elsif ($average >= 74) { $grade = "C"; }
    elsif ($average >= 70) { $grade = "C-"; }
    elsif ($average >= 67) { $grade = "D+"; }
    elsif ($average >= 64) { $grade = "D"; }
    elsif ($average >= 60) { $grade = "D-"; }
    elsif ($average eq "?") { $grade = "?"; }
    elsif ($average < 60) { $grade = "E"; }

  # fix points
  if ($possible == 0) { $earned = $possible = "?"; }
  
  # open class template
  $file = "$htdocs/html/grades/class.txt";
  open(READ, $file) or die "can't open $file: $!";
    $class = join("", <READ>);
  close(READ);

  # substitutions
  $class =~ s/VAR_CLASSNAME/$course/;
  $class =~ s/VAR_POINTSEARNED/$earned\/$possible/;
  $class =~ s/VAR_AVERAGE/$average/;
  $class =~ s/VAR_GRADE/$grade/;
  $class =~ s/\t\tVAR_ASSIGNMENT/$assignments/;
  $body =~ s/\t      VAR_CLASS$current/$class/;
}

# clean up
$cursor->finish();
$dbh->disconnect();

# substitutions
$body =~ s/VAR_SEMESTER/Fall 2004/g;

# print
print $body;
[download]