#!/usr/bin/perl
#  Copyright 2001-2019 Leslie Richardson

#  This file is part of Open Admin for Schools.

#  Open Admin for Schools is free software; you can redistribute it 
#  and/or modify it under the terms of the GNU General Public License
#  as published by the Free Software Foundation; either version 2 of 
#  the License, or (at your option) any later version.

use DBI;
use CGI;
use Time::JulianDay;

my %lex = ('Graduation Requirements' => 'Graduation Requirements',
	   'Credits' => 'Credits',
	   'Check' => 'Check', 
	   'Main' => 'Main',
	   'Provincial Number' => 'Provincial Number',
	   'Duplicate' => 'Duplicate',
	   'Grade' => 'Grade',
	   'Homeroom' => 'Homeroom',
	   'Continue' => 'Continue',
	   'Check All' => 'Check All',
	   'Schools' => 'Schools',
	   'Select' => 'Select',
	   'Missing' => 'Missing',
	   'Section' => 'Section',
	   'Course' => 'Course',
	   'Start Term' => 'Start Term',
	   'End Term' => 'End Term',
	   'Teacher' => 'Teacher',

	   );


my $self = 'preplancourseview.pl';


# Read Config
eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


my $q = new CGI;
print $q->header( -charset, $charset ); 
my %arr = $q->Vars;

my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);


# load the sasked ed courses (they have a grade field).
my (%saskedgrade, %saskedname);
my $sth = $dbh->prepare("select * from sasked_courses");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while ( $ref = $sth->fetchrow_hashref ) {
    my %r = %$ref;
    $saskedgrade{$r{code}} = $r{grade};
    $saskedname{$r{code}} = $r{title};
}



my $title = "Preplan Course View";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{$chartype\n</head><body style="padding:1em;">\n};
print qq{[ <a href="$homepage">$lex{Main}</a> ]\n};

print qq{<h1>$title</h1>\n};



if ( not $arr{page} ) {
    showStartPage();

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    selectCourses();  # select courses and number of semesters, default ppc

}  elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    setCourses(); # set semester, teacher, grade offered, periods per cycle.
    
} elsif ( $arr{page} == 3 ) {
    delete $arr{page};
    planCourseSemester(); # update table with passed values.
    
} elsif ( $arr{page} == 4 ) {
    delete $arr{page};
    updatePeriods();
}



#-----------------
sub compressCourse {
#-----------------

    my $cref = shift;
    my %courses = %$cref;

    
    my (%back,%newkey );
    foreach my $subjsec ( keys %courses ) {
	if ( $courses{$subjsec}->{backedwith} ) {
	    my ($crsA, $crsB) = ($subjsec, $courses{$subjsec}->{backedwith} );
	    # check for any values in %back;
	    my $first = 1;
	    foreach my $key (keys %back) {
		# key format is: subjsec/subjsec/subjsec, etc.
		my @crs = split('/', $key);
		foreach my $cr ( @crs ) {
		    if ( $cr eq $crsA or $cr eq $crsB ) {
			# we have to delete the existing key and add a new larger key
			# put 2 backed courses together with this group.
			my %newkey = map { $_ => 1 } @crs;
			$newkey{$crsA} = 1;
			$newkey{$crsB} = 1;
			# all subjsec values now are keys
			my $newkey = join('/', keys %newkey);
			$first = 0;
			
			# out with old, in with new
			delete $back{$key};
			$back{$newkey} = 1;
			last;
		    }
		}
	    }
	    if ( $first ) { # no records found in %back;
		my $key = join('/', ($crsA,$crsB));
		$back{$key} = 1;
	    }
	}

    } # end of course loop


    # Display %back
#    print qq{Backings Hash\n};
#    foreach my $key ( sort keys %back) {
#	print qq{K:$key<br>\n};
#    }

    # Remove the single course values found in %back compound keys
    foreach my $key ( sort keys %back) {
	my @crs = split('/', $key);
	foreach my $subjsec ( @crs ) {
	    delete $courses{$subjsec};
	}
    }

    # Add compound keys into %courses
    foreach my $key ( sort keys %back) {
	$courses{$key} = 1;
    }



    
    return \%courses;

} # end of compressCourse (join backings)



#----------------
sub showStartPage {
#----------------


    # Form Start
#    print qq{<form action="$self" method="post">\n};
#    print qq{<input type="hidden" name="page" value="1">\n};
#    print qq{<input type="hidden" name="pass9" value="$pass9">\n};

    
    # Load all course data
    my %courses;
    my $sth = $dbh->prepare("select * from preplan_coursemaster");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	my $subjsec = $r{subjsec};
	$courses{$subjsec} = $ref;
    }
    my %refcourses = %courses; # %courses will be altered.


    
    my (%back,%newkey );
    foreach my $subjsec ( keys %courses ) {
	if ( $courses{$subjsec}->{backedwith} ) {
	    my ($crsA, $crsB) = ($subjsec, $courses{$subjsec}->{backedwith} );
	    # check for any values in %back;
	    my $first = 1;
	    foreach my $key (keys %back) {
		# key format is: subjsec/subjsec/subjsec, etc.
		my @crs = split('/', $key);
		foreach my $cr ( @crs ) {
		    if ( $cr eq $crsA or $cr eq $crsB ) {
			# we have to delete the existing key and add a new larger key
			# put 2 backed courses together with this group.
			my %newkey = map { $_ => 1 } @crs;
			$newkey{$crsA} = 1;
			$newkey{$crsB} = 1;
			# all subjsec values now are keys
			my $newkey = join('/', keys %newkey);
			$first = 0;
			
			# out with old, in with new
			delete $back{$key};
			$back{$newkey} = 1;
			last;
		    }
		}
	    }
	    if ( $first ) { # no records found in %back;
		my $key = join('/', ($crsA,$crsB));
		$back{$key} = 1;
	    }
	}
    }

    # Display %back
#    print qq{Backings Hash\n};
#    foreach my $key ( sort keys %back) {
#	print qq{K:$key<br>\n};
#    }

    # Remove the single course values found in %back compound keys
    foreach my $key ( sort keys %back) {
	my @crs = split('/', $key);
	foreach my $subjsec ( @crs ) {
	    delete $courses{$subjsec};
	}
    }

    # Add compound keys into %courses
    foreach my $key ( sort keys %back) {
	$courses{$key} = 1;
    }
    
    # Create a sorting order for the courses;
    my %sort;
    foreach my $key ( keys %courses ) {
	my @crs = split('/', $key);
	my ($code,$section) = split('-', $crs[0]);
	my $name = $saskedname{ $code };
	my $gradeoffered = $refcourses{$crs[0]}->{offeredto};
	$sort{"$gradeoffered$name$key"} = $key;
    }

    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;margin:0.4em;">Courses</caption>\n};
    print qq{<tr><th>Description</th><th>Code</th><th>Grade<br>Offered</th></tr>\n};

    my $count = 1;
    foreach my $key ( sort keys %sort ) {
	my $code = $sort{$key};  # $code may have multiple subjsec with / separator.
	print qq{<tr><td>$count. };
	$count++;
	if ( $code !~ m/\// ) { # normal
	    my ($scode,$section) = split('-', $code);
	    print qq{$saskedname{$scode}</td><td>$code</td><td>$saskedgrade{$scode}</td></tr>\n};
	} else { # multiple subjsec
	    my @crs = split('/', $code);
	    foreach my $subjsec ( @crs ) {
		my ($code,$section) = split('-', $subjsec);
		print qq{$saskedname{$code} ($subjsec)<br>\n};
	    }
	    my $gradeoffered = $refcourses{$crs[0]}->{offeredto};
	    print qq{</td><td>$code</td><td>$gradeoffered</td></tr>\n};
	    
	}
	# print qq{</td><td>$code</td></tr>\n};
    }
	    
    print qq{</table>\n};

    
    # print qq{<div><input type="submit" value="$lex{Continue}"></div>\n};
    # print qq{</form>\n};
    
    print qq{</body></html>\n};
    exit;

} # end of showStartPage




#----------------
sub updatePeriods {
#----------------

    print qq{Update Periods<br>\n};
    
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my $dayspercycle = $arr{dayspercycle};
    delete $arr{dayspercycle};
    my $teachingperiods = $arr{teachingperiods}; # per day (teachppd)
    delete $arr{teachingperiods};
    
    my $sth = $dbh->prepare("update preplan_courseperiods set periods = ? where subjsec = ? and semester = ?");
    my $sth1 = $dbh->prepare("select count(*) from preplan_courseperiods where subjsec = ? and semester = ?");
    my $sth2 = $dbh->prepare("insert into preplan_courseperiods (periods, subjsec, semester) values(?,?,?)");
    
    foreach my $key ( sort keys %arr ) {

	my ($ident, $subjsec, $semester) = split(':', $key);
	my $periods = $arr{$key};

	# Check for existing record
	$sth1->execute($subjsec, $semester);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	my $count = $sth1->fetchrow;

	if ( $count ) { # update record if we have an existing one.
	    $sth->execute($arr{$key}, $subjsec, $semester);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
#	    print "<div>Update: $arr{$key}, $subjsec, $semester</div>\n";
	    
	} else { # insert record
	    $sth2->execute($arr{$key}, $subjsec, $semester);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
#	    print "<div>Insert: $arr{$key}, $subjsec, $semester</div>\n";
	}

    }

    

    # Display current layout for each grade, and give totals.
    # Get Number of Semesters
    my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $semestercount = $sth->fetchrow;
    print qq{<div>Semesters:$semestercount</div>\n};

    # Get PERIODS for each course, each semester.
    my $sth1 = $dbh->prepare("select periods from preplan_courseperiods where subjsec = ? and semester = ?");
    
    # Display Courses by Grade
    my $sth = $dbh->prepare("select c.* from preplan_coursemaster c, sasked_courses s
       where c.subjcode = s.code and offeredto = ? order by s.title, c.subjsec");

    # Form Start
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="4">\n};
    print qq{<input type="hidden" name="dayspercycle" value="$dayspercycle">\n};
    print qq{<input type="hidden" name="teachingperiods" value="$teachingperiods">\n};
    
    print qq{<input type="submit" value="Update Periods">\n};


    foreach my $grade (10..12) {

	my (%sort,%courses, %totals); # totals{semester};
	$sth->execute($grade);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    my $subjsec = $r{subjsec};
#	    $sort{"$saskedname{$subjsec}$subjsec"} = $subjsec;
	    push @{ $courses{$r{semester}} }, $subjsec; # could hold entire record, too?
	}

	# Get Rows for Table.
	my $rows;
	foreach my $semester ( 1.. $semestercount ) {
	    my $temp = scalar @{ $courses{$semester} };
	    if ( $temp > $rows ) { $rows = $temp; }
	}
	# print "Rows:$rows<br>\n";
	
	
	# Start Table
	if ( $rows ) {
	    print qq{<table cellpadding="3" border="1" cellspacing="0" };
	    print qq{style="border:1px solid gray;margin:1em;">\n};
	    print qq{<caption style="font-size:120%;font-weight:bold;">Grade $grade</caption>\n};
	    print qq{<tr>};
	    foreach my $semester (1..$semestercount) {
		print qq{<th class="bcn">Semester $semester</th><th>Code</th><th>Periods</th>};
	    }
	    print qq{</tr>\n};
	}

	# Loop through main rows until we run out of courses in arrays
	foreach my $row (1..$rows ) { # $rows calculated above.
	    print qq{<tr>};
	    foreach my $semester (1..$semestercount) {
		my $subjsec = shift @{ $courses{ $semester }};
		my ($code, $section) = split('-', $subjsec);
		
		print qq{<td>$saskedname{$code}</td><td>$subjsec</td>};
		
		# Get Periods for this course, this semester
		if ( $subjsec ) { # could be blank
		    $sth1->execute($subjsec, $semester);
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my $periods = $sth1->fetchrow;
		    if ( not $periods ) { $periods = $defppc; } # default periods per cycle; passed above.
		    print qq{<td><input type="text" style="width:3ch;" };
		    print qq{name="per:$subjsec:$semester" value="$periods"></td>\n};
		    $totals{$semester} += $periods;
		} else {  # empty cell
		    print qq{<td></td>\n}; 
		}
	    } # end of semester loop
	    print qq{</tr>\n};
	}

	
	# All Year Courses.
	foreach my $subjsec ( @{ $courses{'allyear'} } ) {
	    print qq{<tr style="background-color:#DDD;">};
	    my ($code, $section) = split('-', $subjsec);
	    foreach my $semester (1..$semestercount) {
		print qq{<td>$saskedname{$code}</td><td>$subjsec</td>};

		# Get Periods for this course, this semester
		if ( $subjsec ) { # could be blank
		    $sth1->execute($subjsec, $semester);
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my $periods = $sth1->fetchrow;
		    if ( not $periods ) { $periods = $defppc; } # default periods per cycle; passed above.
		    print qq{<td><input type="text" style="width:3ch;" };
		    print qq{name="per:$subjsec:$semester" value="$periods"></td>\n};
		    $totals{$semester}+= $periods;
		} else {  # empty cell; not possible?
		    print qq{<td></td>\n}; 
		}
	    }
	}
	    
	if ( $rows ) {
	    print qq{<tr style="background-color:#BBB">\n};
	    foreach my $semester (1..$semestercount) {
		print qq{<td class="bra" colspan="2" style="font-size:144%;">};
		print qq{Semester $semester Totals</td><td class="bcn">$totals{$semester}</td>};
	    }
	    print qq{</tr>\n</table>\n};
	}

    
    } # end of grades loop

    print qq{<input type="submit" value="Update Periods">\n};
    print qq{</form>\n};


    # Backings Updates
    # Get courses in this grade.

    # Start Table
    print qq{<table cellpadding="3" border="1" cellspacing="0" style="float:left;">};
    print qq{<caption style="font-weight:bold;font-size:120%;margin:0.4em;">};
    print qq{Backed Courses (offered at same time)</caption>\n};
    
    print qq{<tr><th>$lex{Course} A</th><th>$lex{Course} B</th></tr>\n};
    foreach my $idx (1..8) { # 8 rows for backing.
	print qq{<tr><td><select name="backA:$idx"><option></option>\n};
	
	foreach my $desc ( sort keys %sortc ) {
	    my $subjsec = $sortc{$desc};
	    print qq{<option value="$subjsec">$desc</option>};
	}

	print qq{</select></td><td><select name="backB:$idx"><option></option>\n};

	foreach my $desc ( sort keys %sortc ) {
	    my $subjsec = $sortc{$desc};
	    print qq{<option value="$subjsec">$desc</option>};
	}

	print qq{</select></td></tr>\n};

    }
	    
    print qq{</table>\n};
    print qq{<br clear="left">\n};

    
    # Submit Button
    print qq{<div style="text-align:left;padding:0.4em;"><input type="submit" value="$lex{Continue}"></div>\n};

    print qq{</form>\n};



    print qq{</body></html>\n};

    exit;

} # end of updatePeriods.




#---------------------
sub planCourseSemester {
#---------------------

    print qq{<div>Plan Course Semesters</div>\n};
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # $id:offered, semester,teacher; then backing courses, then 3 values:
    
    
    my $dayspercycle = $arr{dayspercycle};
    delete $arr{dayspercycle};
    my $teachingperiods = $arr{teachingperiods}; # per day (teachppd)
    delete $arr{teachingperiods};
    my $defppc = $arr{defppc};
    delete $arr{defppc};

    
    # Extract 'BackA' and 'BackB' values.
    my %back; # {$idx}{'backA' or 'backB'
    foreach my $key ( sort keys %arr ) {
	my ($val, $idx) = split(':', $key);
	if ( $val =~ m/back./ ) { # match!
	    if ( $arr{$key} ) { # we have a value
		$back{$idx}{$val} = $arr{$key};
	    }
	    delete $arr{$key};
	}
    }

    my $sth = $dbh->prepare("update preplan_coursemaster set backedwith = ? where subjsec = ?");
    foreach my $idx ( sort keys %back ) {
	if ( $back{$idx}{'backA'} and  $back{$idx}{'backB'} ) { # we have the 2 courses
	    my $crsA = $back{$idx}{'backA'};
	    my ($codeA,$section) = split('-',$crsA);
	    my $crsB = $back{$idx}{'backB'};
	    my ($codeB,$section) = split('-',$crsB);

	    $sth->execute($crsA, $crsB);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    $sth->execute($crsB, $crsA);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    
#	    print qq{<div>Backed Courses: $saskedname{$codeA} ($crsA) / $saskedname{$codeB} ($crsB)</div>\n};
	}
    }

	
    # Now do the course Information.
    my (%courses, %periods);
    foreach my $key ( sort keys %arr ) {
	my ($id, $field) = split(':', $key);
	if ( $id =~ m/\d/ ) {
	    if ( $arr{$key} ) { # if we have a value
		if ( $field eq 'periods') {
		    $periods{$id} = $arr{$key};
		} else {
		    $courses{$id}{$field} = $arr{$key};
		}
	    }
	    delete $arr{$key};
	}
    }

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # %arr should now be empty.


    # Write Updated Values to the preplan course master.
    foreach my $id ( sort keys %courses ) {
#	print qq{<div>ID:$id - };
	foreach my $field ( sort keys %{ $courses{$id}} ) {
	    my $sth = $dbh->prepare("update preplan_coursemaster set $field = ? where id = ?");
	    $sth->execute($courses{$id}{$field}, $id);
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
#	    print qq{ $field $courses{$id}{$field}/ };
	}
    }
    print qq{<div>Course Master Updated with semester, teacher, and grade offered to</div>\n};


    
    # Get Number of Semesters
    my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    my $semestercount = $sth->fetchrow;
#    print qq{<div>Semesters:$semestercount</div>\n};

    # Get PERIODS for each course, each semester.
    my $sth1 = $dbh->prepare("select periods from preplan_courseperiods where subjsec = ? and semester = ?");
    
    # Display Courses by Grade
    my $sth = $dbh->prepare("select c.* from preplan_coursemaster c, sasked_courses s
       where c.subjcode = s.code and offeredto = ? order by s.title, c.subjsec");

    # Form Start
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="4">\n};
    print qq{<input type="hidden" name="dayspercycle" value="$dayspercycle">\n};
    print qq{<input type="hidden" name="teachingperiods" value="$teachingperiods">\n};
    
    print qq{<input type="submit" value="Update Periods">\n};


    foreach my $grade (10..12) {

	my (%sort,%courses);
	$sth->execute($grade);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    my $subjsec = $r{subjsec};
#	    $sort{"$saskedname{$subjsec}$subjsec"} = $subjsec;
	    push @{ $courses{$r{semester}} }, $subjsec; # could hold entire record, too?
	}

	# Get Rows for Table.
	my $rows;
	foreach my $semester ( 1.. $semestercount ) {
	    my $temp = scalar @{ $courses{$semester} };
	    if ( $temp > $rows ) { $rows = $temp; }
	}
	# print "Rows:$rows<br>\n";
	
	
	# Start Table
	if ( $rows ) {
	    print qq{<table cellpadding="3" border="1" cellspacing="0" };
	    print qq{style="border:1px solid gray;margin:1em;">\n};
	    print qq{<caption style="font-size:120%;font-weight:bold;">Grade $grade</caption>\n};
	    print qq{<tr>};
	    foreach my $semester (1..$semestercount) {
		print qq{<th class="bcn">Semester $semester</th><th>Code</th><th>Periods</th>};
	    }
	    print qq{</tr>\n};
	}

	# Loop through main rows until we run out of courses in arrays
	foreach my $row (1..$rows ) { # $rows calculated above.
	    print qq{<tr>};
	    foreach my $semester (1..$semestercount) {
		my $subjsec = shift @{ $courses{ $semester }};
		my ($code, $section) = split('-', $subjsec);
		
		print qq{<td>$saskedname{$code}</td><td>$subjsec</td>};
		
		# Get Periods for this course, this semester
		if ( $subjsec ) { # could be blank
		    $sth1->execute($subjsec, $semester);
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my $periods = $sth1->fetchrow;
		    if ( not $periods ) { $periods = $defppc; } # default periods per cycle; passed above.
		    print qq{<td><input type="text" style="width:3ch;" };
		    print qq{name="per:$subjsec:$semester" value="$periods"></td>\n};
		} else {  # empty cell
		    print qq{<td></td>\n}; 
		}
	    } # end of semester loop
	    print qq{</tr>\n};
	}

	
	# All Year Courses.
	foreach my $subjsec ( @{ $courses{'allyear'} } ) {
	    print qq{<tr style="background-color:#DDD;">};
	    my ($code, $section) = split('-', $subjsec);
	    foreach my $semester (1..$semestercount) {
		print qq{<td>$saskedname{$code}</td><td>$subjsec</td>};

		# Get Periods for this course, this semester
		if ( $subjsec ) { # could be blank
		    $sth1->execute($subjsec, $semester);
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my $periods = $sth1->fetchrow;
		    if ( not $periods ) { $periods = $defppc; } # default periods per cycle; passed above.
		    print qq{<td><input type="text" style="width:3ch;" };
		    print qq{name="per:$subjsec:$semester" value="$periods"></td>\n};
		} else {  # empty cell; not possible?
		    print qq{<td></td>\n}; 
		}
	    }
	}
	    
	if ( $rows ) {
	    print qq{</table>\n};
	}

    
    } # end of grades loop

    print qq{<input type="submit" value="Update Periods">\n};
    print qq{</form>\n};
    print qq{</body></html>\n};

    exit;
}



#---------------
sub setCourses {
#---------------

    print qq{<div>Set Courses</div>\n};
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    # Extract sections from %arr
    my %section;
    foreach my $key ( sort keys %arr ) {
	my ($sec, $code ) = split(':', $key);
	if ( $sec eq 'sec' ) { # we have a section
	    $section{$code} = $arr{$key};
	    delete $arr{$key};
	}
    }

    my $addelectives10 = $arr{addelectives10};
    delete $arr{addelectives10};
    my $addelectives11 = $arr{addelectives11};
    delete $arr{addelectives11};
    my $addelectives12 = $arr{addelectives12};
    delete $arr{addelectives12};

    my $next10 = $arr{next10};
    delete $arr{next10};
    my $next11 = $arr{next11};
    delete $arr{next11};
    my $next12 = $arr{next12};
    delete $arr{next12};

    my $semesters = $arr{semesters};
    delete $arr{semesters};

    my $defppc = $arr{periodspercycle}; # default PPC
    delete $arr{periodspercycle};
    
    my $deleterecords = $arr{deleterecords};
    delete $arr{deleterecords};
    
    
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # Only course codes left in %arr now.

    # Load staff into hash; not restricted to classroom teachers.
    my (%teachers, %teachername);
    my $sth = $dbh->prepare("select lastname, firstname, userid from staff 
     order by lastname, firstname"); 
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my ( $lastname, $firstname, $userid) = $sth->fetchrow ) {
	$teachers{"$lastname, $firstname ($userid)"} = $userid;
	$teachername{$userid} = "$lastname,$firstname";
    }


    if ( $deleterecords ) {
    
	# Delete any existing records in preplan_coursemaster table.
	my $sth = $dbh->prepare("delete from preplan_coursemaster");
	$sth->execute;
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

	# insert the new records.
	my $sth = $dbh->prepare("insert into preplan_coursemaster 
         (subjcode, section, subjsec, description, grade) 
         values(?,?,?,?,?)");

	foreach my $coursecode ( keys %arr ) {
	    foreach my $section (1.. $section{$coursecode} ) {
		my $subjsec = $coursecode. '-'. $section;
		$sth->execute($coursecode, $section, $subjsec, $saskedname{$coursecode}, $saskedgrade{$coursecode});
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	    }
	}
	# table now populated.
	print qq{<h3>Courses Added to PrePlan Course Master</h3>\n};
    }

    
    # Put in teacher and terms if available
    # Get current teacher, terms.
    my $sth1 = $dbh->prepare("select teacher from subject where subjcode = ?");
    my $sth2 = $dbh->prepare("update preplan_coursemaster set teacher = ? where id = ?");

    my $sth = $dbh->prepare("select id, subjcode from preplan_coursemaster");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    
    while ( my ($id, $subjcode ) = $sth->fetchrow ) {

	# Get current values
	$sth1->execute( $subjcode );
	if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	my $teacher = $sth1->fetchrow;

	if ( $teacher ) {
	    # update coursemaster
	    $sth2->execute( $teacher, $id );
	    if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
	}
    }


    # Then display for reset.
    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="3">\n};
    print qq{<input type="hidden" name="defppc" value="$defppc">\n};
    

    # Get PPD, Days per cycle,
    print qq{<table cellpadding="3" border="1" cellspacing="0" style="border:1px solid gray;margin:1em;">\n};
    print qq{<tr><td class="bra">Teaching Periods Per Day</td>};
    print qq{<td><input type="text" name="teachingperiods" style="width:4ch;" value="6"></td></tr>\n};
    
    print qq{<tr><td class="bra">Days per Cycle</td>};
    print qq{<td><input type="text" name="dayspercycle" style="width:4ch;" value="6"></td></tr>\n};

    # already passed from previous page (defppc);
    # print qq{<tr><td class="bra">Default Periods per Cycle Per Course</td>};
    # print qq{<td><input type="text" name="periodspercycle" style="width:4ch;" value="6"></td></tr>\n};
    print qq{</table>\n\n};
    
    
    # Submit Button
    print qq{<div style="text-align:left;padding:0.4em;"><input type="submit" value="$lex{Continue}"></div>\n};

    # Queries setup
    my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?");

    # Get Subject Info
    my $sth = $dbh->prepare("select * from preplan_coursemaster where grade = ?
       order by grade, description, section ");

    foreach my $grade ( 10..12 ) {

	print qq{<div style="font-weight:bold;font-size:144%;margin:1em;">Grade $grade</div>\n};
	
	# Start Table
	print qq{<table cellpadding="3" border="1" cellspacing="0" style="float:left;margin:0.5em 1.5em;">};

	# Table Heading
	print qq{<tr><th>$lex{Course} ($lex{Section})</th><th>$lex{Grade}</th><th>$lex{Teacher}</th>};
	print qq{<th>Semester</th><th>Grade<br>Offered</th></tr>\n};

	

	$sth->execute( $grade );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my (%courses, %sortc);
	my $count = 1;
    
	while (my $ref = $sth->fetchrow_hashref ) {

	    %r = %$ref;
	    my $id = $r{id};
	
	    $courses{$r{subjsec}} = $r{description};
	    $sortc{"$r{description}-$r{subjsec}"} = $r{subjsec};
	
	    print qq{<tr><td class="la"><b>$r{description}</b> ($r{subjsec})</td><td class="cn">$r{grade}</td>\n};

	    # Teacher
	    print qq{<td><select name="$id:teacher">};
	    print qq{<option value="$r{teacher}">$teachername{$r{teacher}}</option>\n};
	    foreach my $key ( sort keys %teachers ) {
		print qq{<option value="$teachers{$key}">$key</option>\n};
	    }
	    print qq{\n<option></option></select></td>\n};

	    # Semester
	    my $defsem = ($count % $semesters) + 1;
	    $count++;
	    print qq{<td class="cn"><select name="$id:semester"><option>$defsem</option>\n};
	    foreach my $sem (1..$semesters) { print qq{<option>$sem</option>\n}; }
	    print qq{<option value="allyear">All Year</option></select></td>\n};

	    # Grade Offering
	    print qq{<td class="cn"><select name="$id:offeredto"><option>$r{grade}</option>\n};
	    foreach my $g (10..12) {
		if ( $grade == $g ) { next; }
		print qq{<option>$g</option>\n};
	    }
	    print qq{</select></td></tr>\n};

	}
	
	print qq{</table>\n};


	# Backings List
	# Start Table
	print qq{<table cellpadding="3" border="1" cellspacing="0" style="float:left;margin:1.5em;">};
	print qq{<caption style="font-weight:bold;font-size:120%;margin:0.4em;">};
	print qq{Grade $grade Backed Courses (offered at same time)</caption>\n};
    
	print qq{<tr><th>$lex{Course} A</th><th>$lex{Course} B</th></tr>\n};
	foreach my $idx (1..8) {
	    print qq{<tr><td><select name="backA:$idx"><option></option>\n};
	
	    foreach my $desc ( sort keys %sortc ) {
		my $subjsec = $sortc{$desc};
		print qq{<option value="$subjsec">$desc</option>};
	    }

	    print qq{</select></td><td><select name="backB:$idx"><option></option>\n};

	    foreach my $desc ( sort keys %sortc ) {
		my $subjsec = $sortc{$desc};
		print qq{<option value="$subjsec">$desc</option>};
	    }
	    
	    print qq{</select></td></tr>\n};

	}
	print qq{</table>\n};
	print qq{<br clear="left">\n};
	print qq{<hr style="width:70%;margin-left:0;">\n};
	
    } # next grade;

    
    # Submit Button
    print qq{<div style="text-align:left;padding:0.4em;"><input type="submit" value="$lex{Continue}"></div>\n};

    print qq{</form>\n};
    print qq{</body></html>\n};


    exit;
    
} # end of setCourses






#-------------
sub selectCourses {
#-------------

    print qq{<div>Set Courses</div>\n};
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    # Grade 10 pass count (1-5). 
    my @req10 = ( 'ELA A10', 'ELA B10', 'Science 10', '10 Math', '10 Social' );
    my %required10 = map { $_ => 1 } @req10;
    my ($promote10, $fail10);
    
    my $sth = $dbh->prepare("select studnum from student where grade = 10");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $studnum = $sth->fetchrow ) {
	my $passcount = 0;
	my @completed = @{ $req{$studnum}{10} };
	foreach my $req ( @completed ) {
	    if ( $required10{$req} ) { $passcount++; }
	}
	if ( $passcount >= 4) { 
	    $promote10++;
	} else { $fail10++; }
	
    }

    my $next10 = $arr{pass9} + $fail10;

    # Start Table
    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Next Year Student Numbers</caption>\n};
    print qq{<tr><th>Current<br>Grade</th><th>Passed</th><th>Incomplete</th><th>Next Year<br>Students</th></tr>\n};
    
    print qq{<tr><td class="cn">9</td><td class="cn">$arr{pass9}</td><td colspan="4"></td></tr>\n};
    print qq{<tr><td class="cn">10</td><td class="cn">$promote10</td><td class="cn">$fail10</td>};
    print qq{<td class="bcn">$next10</td></tr>\n};


    #-----------------------------------------------------------------
    # Grade 11 Numbers.
    #-----------------------------------------------------------------
    # Grade 11 pass count (1-4).
    my @req11 = ( 'ELA 20', '11 Science', '11 Math', '11 Social' );
    my %required11 = map { $_ => 1 } @req11;

    my ($promote11, $fail11);
    
    my $sth = $dbh->prepare("select studnum from student where grade = 11");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $studnum = $sth->fetchrow ) {
	my $passcount = 0;
	my @completed = @{ $req{$studnum}{11} };
	foreach my $req ( @completed ) {
	    if ( $required11{$req} ) { $passcount++; }
	}
#	print qq{SN:$studnum - $passcount<br>\n};
	if ( $passcount >= 3) { 
	    $promote11++;
	} else { $fail11++; }
	
    }

    my $next11 = $promote10 + $fail11;
#    print qq{<div>Grade 11 students to promote: $promote11</div>\n};
#    print qq{<div>Next Year Grade 11 - Gr 10's ($promote10) + };
#    print qq{Gr 11's without enough reqs($fail11) = $next11</div>\n};

    print qq{<tr><td class="cn">11</td><td class="cn">$promote11</td>};
    print qq{<td class="cn">$fail11</td><td class="bcn">$next11</td></tr>\n};

    #-----------------------------------------------------------------
    # Grade 12 Numbers.
    #-----------------------------------------------------------------
    # Grade 12 pass count (1-3).
    my @req12 = ( 'ELA A30', 'ELA B30', '12 Social' );
    my %required12 = map { $_ => 1 } @req12;

    my ($promote12, $fail12);
    
    my $sth = $dbh->prepare("select studnum from student where grade = 12");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $studnum = $sth->fetchrow ) {
	my $passcount = 0;
	my @completed = @{ $req{$studnum}{12} };
	foreach my $req ( @completed ) {
	    if ( $required12{$req} ) { $passcount++; }
	}
#	print qq{SN:$studnum - $passcount<br>\n};
	if ( $passcount >= 2) { 
	    $promote12++;
	} else { $fail12++; }
	
    }

    my $next12 = $promote11 + $fail12;
#    print qq{<div>Grade 12 students to graduate: $promote12 (if sufficient credits)</div>\n};
#    print qq{<div>Next Year Grade 12 - Gr 11's ($promote11) + };
#    print qq{Gr 12's without enough reqs($fail12) = $next12</div>\n};

    print qq{<tr><td class="cn">12</td><td class="cn">$promote12</td>};
    print qq{<td class="cn">$fail12</td><td class="bcn">$next12</td></tr>\n};

    # The values of $next10, $next11, and $next12 are the numbers for next year.
    my $nextyeartotal = $next10 + $next11 + $next12;
    my $curryeartotal = $promote10 + $fail10 + $promote11 + $fail11 + $promote12 + $fail12;
    print qq{<tr style="background-color:#DDD;"><td colspan="3">Next Year Grade 10-12 Students</td>};
    print qq{<td class="bcn">$nextyeartotal</td></tr>\n};
    print qq{<tr><td colspan="3">Current Year Grade 10-12 Students</td><td class="cn">$curryeartotal</td></tr>\n};

    print qq{</table>\n};
    print qq{<div>Incomplete students have more than 1 required area of study missing; Electives ignored</div>\n};

    # Form Start
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    
    # print qq{<input type="hidden" name="pass9" value="$pass9">\n};
    print qq{<input type="hidden" name="next10" value="$next10">\n};
    print qq{<input type="hidden" name="next11" value="$next11">\n};
    print qq{<input type="hidden" name="next12" value="$next12">\n};
    

    # hidden values for required areas of study 10-12
    print qq{<input type="hidden" name="4017" value="1">\n};
    print qq{<input type="hidden" name="4018" value="1">\n};
    print qq{<input type="hidden" name="4214" value="1">\n};
    print qq{<input type="hidden" name="6017" value="1">\n};
    print qq{<input type="hidden" name="8017" value="1">\n};
    print qq{<input type="hidden" name="8018" value="1">\n};
    

    
    # Grade 10 Area
    # ---------------------------------
#    print qq{<hr style="width:40%;margin-left:0;">\n};
    print qq{<h2>Grade 10 - $next10 Students</h2>\n};
    print qq{<hr style="width:40%;margin-left:0;">\n};

    my @req10code = qw(4017 4018 4214 4424 4423 4307 4306 4309);
    my %req10code = map {$_ => 1} @req10code;
    
    # Current 10 Courses.    
    my (%elective10, %course10, %sort); # elective 10 will have all courses to start and then have req stripped below
    my $sth1 =$dbh->prepare("select title, grade, ctype from sasked_courses where code = ?");
    my $sth = $dbh->prepare("select distinct subjcode from subject where grade = 10");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $subjcode = $sth->fetchrow ) {

	$sth1->execute($subjcode);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my ($title, $grade, $ctype) = $sth1->fetchrow;
	$elective10{$subjcode} = 1;
	$sort{"$saskedname{$subjcode}$subjcode"} = $subjcode;
	
	if ( $grade != 10 ) { print qq{<div>Error for course $subjcode, $title - not grade 10!</div>\n};}

	$course10{$subjcode} = $title;
	$ctype10{$subjcode} = $ctype;
    }


    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin-bottom:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Current Courses 10</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Type</th><th>Category</th></tr>\n};

    my $count = 1;
    foreach my $key ( sort keys %sort ) {
	$subjcode = $sort{$key};
	
	my $category = 'Elective';
	if ( $req10code{$subjcode} ) { $category = qq{<span style="color:green;font-weight:bold;">Required</span>}; }
	
	print qq{<tr><td>$count. $course10{$subjcode}</td><td>$subjcode</td>};
	print qq{<td>$ctype10{$subjcode}</td><td>$category</td></tr>\n};
	$count++;
    }
    print qq{</table>\n};

    
    # Required Areas of Study.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Required Areas of Study 10</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Stud<br>Count</th><th title="Sections">Sect</th></tr>\n};
    
    print qq{<tr><td class="la">1. English Language Arts A10</td><td class="cn">4017</td>};
    print qq{<td class="bcn">$arr{'ELA A10'}</td>};
    print qq{<td><select name="sec:4017">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
    
    print qq{<tr><td class="la">2. English Language Arts B10</td><td class="cn">4018</td>};
    print qq{<td class="bcn">$arr{'ELA B10'}</td>\n};
    print qq{<td><select name="sec:4018">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
    
    print qq{<tr><td class="la">3. Science 10</td><td class="cn">4214</td>};
    print qq{<td class="bcn">$arr{'Science 10'}</td>\n};
    print qq{<td><select name="sec:4214">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    
    print qq{<tr style="background-color:#DDD"><td class="bla">4. Math 10</td>};
    print qq{<td class="cn">#Stud</td><td class="bcn">$arr{'10 Math'}</td><td></td></tr>\n};
    
    print qq{<tr><td class="la">Math: Foundations and Pre-calculus 10</td><td class="cn">4424</td>};
    print qq{<td><input type="checkbox" name="4424" value="1"></td>\n};
    print qq{<td><select name="sec:4424">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="la">Math: Workplace and Apprenticeship 10</td><td class="cn">4423</td>};
    print qq{<td><input type="checkbox" name="4423" value="1"></td>\n};
    print qq{<td><select name="sec:4423">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
    
    
    print qq{<tr style="background-color:#DDD"><td class="bla">5. Social 10</td>};
    print qq{<td class="cn">#Stud</td><td class="bcn">$arr{'10 Social'}</td><td></td></tr>\n};
    
    print qq{<tr><td class="la">Social Studies 10</td><td class="cn">4307</td>\n};
    print qq{<td><input type="checkbox" name="4307" value="1"></td>\n};
    print qq{<td><select name="sec:4307">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
    
    print qq{<tr><td class="la">History 10</td><td class="cn">4306</td>\n};
    print qq{<td><input type="checkbox" name="4306" value="1"></td>\n};
    print qq{<td><select name="sec:4306">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
    
    print qq{<tr><td class="la">Native Studies</td><td class="cn">4309</td>\n};
    print qq{<td><input type="checkbox" name="4309" value="1"></td>\n};
    print qq{<td><select name="sec:4309">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    print qq{</table>\n};
    

    # strip %elective10 and then display electives.
    foreach my $code ( keys %elective10 ) {
	if ( $req10code{$code} ) {
	    delete $elective10{$code};
	}
    }

    my %sort;
    foreach my $code ( keys %elective10 ) {
	$sort{"$saskedname{$code}$code"} = $code;
    }

    # Electives Offered.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Grade 10 Electives</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Select</th><th title="Sections">Sect</th></tr>\n};

    foreach my $key ( sort keys %sort ) {
	my $subjcode = $sort{$key};
	
	print qq{<tr><td>$saskedname{$subjcode}</td><td>$subjcode</td>};
	print qq{<td><input type="checkbox" name="$subjcode" value="1"></td>\n};
	print qq{<td><select name="sec:$subjcode">};
	foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
	print qq{</select></td></tr>\n};
    }

    print qq{<tr><td colspan="5">Additional Elective Courses };
    print qq{<input type="text" name="addelectives10" style="width:22ch;"></td></tr>\n};
    print qq{<tr><td colspan="5" class="ra">Separate Course Codes with spaces \n};
    print qq{[ <a href="coursecodeview.pl" target="_blank">View Course Codes in New Tab</a> ]</td></tr>\n};
    
    print qq{</table>\n};

    print qq{<br clear="left">\n};
    

    # Grade 11 Area
    # ---------------------------------
#    print qq{<hr style="width:40%;margin-left:0;">\n};
    print qq{<h2>Grade 11 - $next11 Students</h2>\n};
    print qq{<hr style="width:40%;margin-left:0;">\n};

    my @req11code = qw(6017 6245 6246 6247 6702 6212 6213  6423 6425 6426);  # ELA 20, a Science, a Math
    my %req11code = map {$_ => 1} @req11code;

#    foreach my $code ( sort keys %req11code ) {
#	print "$saskedname{$code} - $code<br>\n";
#    }

    # Current 11 Courses.    
    my (%elective11, %course11, %sort); # elective 10 will have all courses to start and then have req stripped below
    my $sth1 =$dbh->prepare("select title, grade, ctype from sasked_courses where code = ?");
    my $sth = $dbh->prepare("select distinct subjcode from subject where grade = 11");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $subjcode = $sth->fetchrow ) {

	$sth1->execute($subjcode);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my ($title, $grade, $ctype) = $sth1->fetchrow;
	$elective11{$subjcode} = 1;
	$sort{"$saskedname{$subjcode}$subjcode"} = $subjcode;
	
	if ( $grade != 11 ) { print qq{<div>Error for course $subjcode, $title - not grade 11!</div>\n};}

	$course11{$subjcode} = $title;
	$ctype11{$subjcode} = $ctype;
    }


    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin-bottom:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Current Courses 11</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Type</th><th>Category</th></tr>\n};

    my $count = 1;
    foreach my $key ( sort keys %sort ) {
	$subjcode = $sort{$key};
	
	my $category = 'Elective';
	if ( $req11code{$subjcode} ) { $category = qq{<span style="color:green;font-weight:bold;">Required</span>}; }
	
	print qq{<tr><td>$count. $course11{$subjcode}</td><td>$subjcode</td>};
	print qq{<td>$ctype11{$subjcode}</td><td>$category</td></tr>\n};
	$count++;
    }
    print qq{</table>\n};

    
    # Required Areas of Study.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Required Areas of Study 11</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Stud<br>Count</th><th title="Sections">Sect</th></tr>\n};
    
    print qq{<tr><td class="la">1. English Language Arts 20</td><td class="cn">6017</td>};
    print qq{<td class="bcn">$arr{'ELA 20'}</td>\n};
    print qq{<td><select name="sec:6017">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
    
    
    print qq{<tr style="background-color:#DDD"><td class="bla">2. Math 11</td>};
    print qq{<td class="cn">#Stud</td><td class="bcn">$arr{'11 Math'}</td><td></td></tr>\n};
    
    print qq{<tr><td class="la">Math: Workplace and Apprenticeship 20</td><td class="cn">6423</td>};
    print qq{<td><input type="checkbox" name="6423" value="1"></td>\n};
    print qq{<td><select name="sec:6423">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};


    print qq{<tr><td class="la">Math: Foundations 20</td><td class="cn">6425</td>};
    print qq{<td><input type="checkbox" name="6425" value="1"></td>\n};
    print qq{<td><select name="sec:6425">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};


    print qq{<tr><td class="la">Math: Pre-calculus 20</td><td class="cn">6426</td>};
    print qq{<td><input type="checkbox" name="6426" value="1"></td>\n};
    print qq{<td><select name="sec:6426">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};


    
    print qq{<tr style="background-color:#DDD"><td class="bla">3. Science 11</td>};
    print qq{<td class="cn">#Stud</td><td class="bcn">$arr{'11 Science'}</td><td></td></tr>\n};

    print qq{<tr><td class="la">Chemistry 20</td><td class="cn">6212</td>};
    print qq{<td><input type="checkbox" name="6212" value="1"></td>\n};
    print qq{<td><select name="sec:6212">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="la">Physics 20</td><td class="cn">6213</td>};
    print qq{<td><input type="checkbox" name="6213" value="1"></td>\n};
    print qq{<td><select name="sec:6213">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="la">Health Science 20</td><td class="cn">6245</td>};
    print qq{<td><input type="checkbox" name="6245" value="1"></td>\n};
    print qq{<td><select name="sec:6245">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="la">Environmental Science 20</td><td class="cn">6246</td>};
    print qq{<td><input type="checkbox" name="6246" value="1"></td>\n};
    print qq{<td><select name="sec:6246">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    print qq{<tr><td class="la">Physical Science 20</td><td class="cn">6247</td>};
    print qq{<td><input type="checkbox" name="6247" value="1"></td>\n};
    print qq{<td><select name="sec:6427">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
    
    print qq{<tr><td class="la">Computer Science 20</td><td class="cn">6702</td>};
    print qq{<td><input type="checkbox" name="6702" value="1"></td>\n};
    print qq{<td><select name="sec:6702">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

    print qq{<tr><td colspan="5">Required Area Single Courses are automatically selected</td></tr>\n};
    print qq{</table>\n};
        
    # strip %elective11 and then display electives.
    foreach my $code ( keys %elective11 ) {
	if ( $req11code{$code} ) {
	    delete $elective11{$code};
	}
    }

    my %sort;
    foreach my $code ( keys %elective11 ) {
	$sort{"$saskedname{$code}$code"} = $code;
    }

    # Electives Offered.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Grade 11 Electives</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Select</th><th title="Sections">Sect</th></tr>\n};

    foreach my $key ( sort keys %sort ) {
	my $subjcode = $sort{$key};
	
	print qq{<tr><td>$saskedname{$subjcode}</td><td>$subjcode</td>};
	print qq{<td><input type="checkbox" name="$subjcode" value="1"></td>\n};
	print qq{<td><select name="sec:$subjcode">};
	foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
	print qq{</select></td></tr>\n};

    }

    print qq{<tr><td colspan="5">Additional Elective Courses };
    print qq{<input type="text" name="addelectives11" style="width:22ch;"></td></tr>\n};
    print qq{<tr><td colspan="5" class="ra">Separate Course Codes with spaces \n};
    print qq{[ <a href="coursecodeview.pl" target="_blank">View Course Codes in New Tab</a> ]</td></tr>\n};
    print qq{</table>\n};

    print qq{<br clear="left">\n};


    
    # Grade 12 Area
    # ---------------------------------
#    print qq{<hr style="width:40%;margin-left:0;">\n};
    print qq{<h2>Grade 12 - $next12 Students</h2>\n};
    print qq{<hr style="width:40%;margin-left:0;">\n};

    my @req12code = qw(8017 8018 8306 8321 8307);
    my %req12code = map {$_ => 1} @req12code;

#    foreach my $code ( sort keys %req12code ) {
#	print "$saskedname{$code} - $code<br>\n";
#    }


    
    # Current 12 Courses.    
    my (%elective12, %course12, %sort); # elective 10 will have all courses to start and then have req stripped below
    my $sth1 =$dbh->prepare("select title, grade, ctype from sasked_courses where code = ?");
    my $sth = $dbh->prepare("select distinct subjcode from subject where grade = 12");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my $subjcode = $sth->fetchrow ) {

	$sth1->execute($subjcode);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my ($title, $grade, $ctype) = $sth1->fetchrow;
	$elective12{$subjcode} = 1;
	$sort{"$saskedname{$subjcode}$subjcode"} = $subjcode;
	
	if ( $grade != 12 ) { print qq{<div>Error for course $subjcode, $title - not grade 12!</div>\n};}

	$course12{$subjcode} = $title;
	$ctype12{$subjcode} = $ctype;
    }


    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin-bottom:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Current Courses 12</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Type</th><th>Category</th></tr>\n};

    my $count = 1;
    foreach my $key ( sort keys %sort ) {
	$subjcode = $sort{$key};
	
	my $category = 'Elective';
	if ( $req12code{$subjcode} ) { $category = qq{<span style="color:green;font-weight:bold;">Required</span>}; }
	
	print qq{<tr><td>$count. $course12{$subjcode}</td><td>$subjcode</td>};
	print qq{<td>$ctype12{$subjcode}</td><td>$category</td></tr>\n};
	$count++;
    }
    print qq{</table>\n};

    
    # Required Areas of Study.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Required Areas of Study 12</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Stud<br>Count</th><th title="Sections">Sect</th></tr>\n};
    
    print qq{<tr><td class="la">1. English Language Arts A 30</td><td class="cn">8017</td>};
    print qq{<td class="bcn">$arr{'ELA A30'}</td>\n};
    print qq{<td><select name="sec:8017">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};
	    
    print qq{<tr><td class="la">2. English Language Arts B 30</td><td class="cn">8018</td>};
    print qq{<td class="bcn">$arr{'ELA B30'}</td>\n};
    print qq{<td><select name="sec:8018">};
    foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
    print qq{</select></td></tr>\n};

        
    print qq{<tr style="background-color:#DDD"><td class="bla">3. Social 12</td>};
    print qq{<td class="cn">#Stud</td><td class="bcn">$arr{'12 Social'}</td><td></td></tr>\n};

    foreach my $code ('8306','8307','8321') {
	print qq{<tr><td class="la">$saskedname{$code}</td><td class="cn">$code</td>};
	print qq{<td><input type="checkbox" name="$code" value="1"></td>\n};
	print qq{<td><select name="sec:$code">};
	foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
	print qq{</select></td></tr>\n};

    }

    print qq{<tr><td colspan="5">Required Area Single Courses are automatically selected</td></tr>\n};
    print qq{</table>\n};

    
    # strip %elective12 and then display electives.
    foreach my $code ( keys %elective12 ) {
	if ( $req12code{$code} ) {
	    delete $elective12{$code};
	}
    }

    my %sort;
    foreach my $code ( keys %elective12 ) {
	$sort{"$saskedname{$code}$code"} = $code;
    }

    # Electives Offered.
    print qq{<table cellpadding="3" cellspacing="0" border="1" style="float:left;margin:1em;">\n};
    print qq{<caption style="font-weight:bold;font-size:120%;">Grade 12 Electives</caption>\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Select</th><th title="Sections">Sect</th></tr>\n};

    foreach my $key ( sort keys %sort ) {
	my $subjcode = $sort{$key};
	
	print qq{<tr><td>$saskedname{$subjcode}</td><td>$subjcode</td>};
	print qq{<td><input type="checkbox" name="$subjcode" value="1"></td>\n};
	print qq{<td><select name="sec:$subjcode">};
	foreach my $idx (1..4) { print qq{<option>$idx</option>}; }
	print qq{</select></td></tr>\n};

    }

    print qq{<tr><td colspan="5">Additional Elective Courses };
    print qq{<input type="text" name="addelectives12" style="width:22ch;"></td></tr>\n};
    print qq{<tr><td colspan="5" class="ra">Separate Course Codes with spaces \n};
    print qq{[ <a href="coursecodeview.pl" target="_blank">View Course Codes in New Tab</a> ]</td></tr>\n};
    print qq{</table>\n};
    # end of 12 area

    print qq{<br clear="left">\n};

    print qq{<hr style="width:40%;margin-left:0;">\n};
    print qq{<div style="font-weight:bold;font-size:130%;margin:1em;">Number of Semesters };
    print qq{<input type="text" name="semesters" style="width:4ch;" value="2"></div>\n};

    # Default Periods per Course 
    print qq{<div style="font-weight:bold;font-size:130%;margin:1em;">};
    print qq{Default Periods Per Cycle ( ie. per 6 day cycle / week) };
    print qq{<input type="text" name="periodspercycle" style="width:4ch;" value="6"></div>\n};

    # Update Table?
    print qq{<div style="font-weight:bold;font-size:130%;margin:1em;">};
    print qq{Delete Records from Existing PrePlan Course Master? };
    print qq{<input type="checkbox" name="deleterecords" value="1"></div>\n};


    print qq{<div><input class="btn-grn" type="submit" value="Update Courses -> PrePlan Course Master"></div>\n};

    print qq{</form>\n};
    print qq{</body></html>\n};
    
    exit;

   
} # end of selectCourses




#-------------
sub getCredits { 
#-------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my ($provnum, $dbh) = @_; # passed provincial number, dbh

    # Load their completed courses
    my $sth = $dbh->prepare("select courseid from sasked_completedcourses where 
       provnum = $provnum and creditsearned = '1'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    my %completed;
    while ( my $courseid = $sth->fetchrow ) {
	$completed{$courseid} = $saskedgrade{$courseid};
    }

    return \%completed;

} # end of getCredits



#----------------
sub checkCredit10 { # check grade 10 completion list; return any errors (missing requirements)
#----------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my ($provnum, $dbh, $studnum) = @_; # passed provincial number, dbh, localnumber.

    # Load their completed courses
    my $sth = $dbh->prepare("select courseid from sasked_completedcourses 
      where provnum = $provnum and creditsearned = '1'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    
    my %completed;
    while ( my $courseid = $sth->fetchrow ) {
	$completed{$courseid} = 1;
    }

   
    # Load courses in progress, and passing.
    # my %inprogress;
    my $sth = $dbh->prepare("select subjcode, a1 from eval where studnum = ?");
    $sth->execute( $studnum );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my ($subjcode, $a1) = $sth->fetchrow ) {
	$subjcode =~ s/-.*//g;
	if ( $a1 >= 50 ) {  # assume they will complete; # and not $completed{$subjcode} ) {
	    $completed{$subjcode} = 1;
	}
    }

        
    
    my @incomplete; # return text based codes

    my @ignore = qw(4017 4018 4214 4424 4423 4307 4306 4309); # courses required to skip in finding electives.
    my %ignore = map { $_ => 1 } @ignore;

    # Test.
    # foreach my $key ( sort keys %ignore ) {
    #  print qq{K:$key V:$ignore{$key}<br>\n};
    # }

    
    # Go through the completion requirements;

    # 1. ELA A10 (4017)
    if ( not $completed{4017} ) {
	push @incomplete, 'ELA A10';
    }

    # 2. ELA B10 (4018)
    if ( not $completed{4018} ) {
	push @incomplete, 'ELA B10';
    }

    # 3. Science 10 (4214)
    if ( not $completed{4214} ) {
	push @incomplete, 'Science 10';
    }


    # 4. A Math (4424 (Foundations), or 4423 (Workplace))
    if ( not $completed{4424} and not $completed{4423} ) { # haven't done either math
	push @incomplete, '10 Math';
    }

    # 5. A Social Science SS10 (4307), Hist10 (4306), Native Stud 10 (4309)
    if ( not $completed{4307} and not $completed{4306} and not $completed{4309} ) { # haven't done any social
	push @incomplete, '10 Social';
    }

    # 6. 3 more electives at 10 level or higher.
    # Loop over completed courses
    my @electives;
    foreach my $courseid ( keys %completed ) { 
	my $grade = $saskedgrade{$courseid};

	if ( $ignore{$courseid} ) { next; } # these don't count, done above.
	if ( $grade < 10 ) { next; } # lower grade level

	push @electives, $courseid;
    }

    if ( @electives < 3 ) { # error
	push @incomplete, '10 Electives';
    }

    return @incomplete;

} # end of checkCredit10



#----------------
sub checkCredit11 { # check grade 11 completion list; return any errors (missing requirements)
#----------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my ($provnum,$dbh) = @_;

    # Load their completed courses
    my $sth = $dbh->prepare("select courseid from sasked_completedcourses where 
      provnum = $provnum and creditsearned = '1'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    my %completed;
#    print qq{<b>Credit 11:$provnum</b><br>\n};
    while ( my $courseid = $sth->fetchrow ) {
#	print "$saskedname{$courseid} ($courseid)/ ";
	$completed{$courseid} = 1;
    }


    my @incomplete; # return text based codes
    my %ignore; # codes to ignore (since credit already given)


    # Go through the completion requirements;

    # 1. ELA 20 (6017)
    if ( not $completed{6017} ) {
	push @incomplete, 'ELA 20';
    } else {
	$ignore{6017} = 1;
    }

    # 2. Sciences ( Enviro 6246, Physical 6247, Health 6245, Computer
    # 6702 ) (#physics 20 (6213) and chem 20 (6212) no longer allowed
    
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid ( qw(6245 6246 6247 6702 6212 6213)) {  
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, 
	         # since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Science';
    }


    # 3. A Math ( 6423 Workplace, 6425 Foundations, 6426 Pre-Calculus )
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid (qw(6423 6425 6426)) {
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, 
	       # since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Math';
    }


    # 4. A Social Science (big list)
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid ( qw(6306 6309 6307 2140 2150 6340 8306 8321 8307 8339 3140 3150 8340 )) {
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0;
	    $ignore{$cid} = 1; # add this course to the ignore list, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Social';
    }


    # 5. 3 more electives at 11 level or higher.
    # Loop over completed courses
    my @electives;
    foreach my $courseid ( keys %completed ) { 
	my $grade = $saskedgrade{$courseid};
	# print "Course:$courseid Grade:$grade<br>\n";

	if ( $ignore{$courseid} ) { next; } # these don't count, done above.
	if ( $grade < 11 ) { next; } # lower grade level
	push @electives, $courseid;
    }

    if ( @electives < 3 ) { # error
	push @incomplete, '11 Electives';
    }

    return @incomplete;


} # end of checkCredit11


#----------------
sub checkCredit12 { # check grade 12 completion list; return any errors (missing requirements)
#----------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    my ($provnum,$dbh) = @_;

    # Load their completed courses
    my $sth = $dbh->prepare("select courseid from sasked_completedcourses where provnum = $provnum and creditsearned = '1'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    my %completed;
#    print qq{<b>Credit 11:$provnum</b><br>\n};
    while ( my $courseid = $sth->fetchrow ) {
#	print "$saskedname{$courseid} ($courseid)/ ";
	$completed{$courseid} = 1;
    }


    my @incomplete; # return text based codes
    my %ignore; # codes to ignore (since credit already given)


    # Go through the completion requirements;

    # 1. ELA A30
    if ( not $completed{8017} ) {
	push @incomplete, 'ELA A30';
    } else {
	$ignore{8017};
    }


    # 2. ELA B30
    if ( not $completed{8018} ) {
	push @incomplete, 'ELA B30';
    } else {
	$ignore{8018};
    }


=head
    # 3. Sciences ( Enviro 6246, Physical 6247, Health 6245, Computer 6702 ) (#physics 20 (6213) and Chem 20 (6212) no longer allowed
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid qw(6245 6246 6247 6702 6212 6213) {  
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Science';
    }


    # 3. A Math ( 6423 Workplace, 6425 Foundations, 6426 Pre-Calculus )
    my $incompleteflag = 1; # assume incomplete unless we find a completed course;
    foreach my $cid qw(6423 6425 6426) {
	if ( $completed{$cid} ) { 
	    $incompleteflag = 0; 
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }
    if ( $incompleteflag ) {
	push @incomplete, '11 Math';
    }
=cut


    # 4. A Social Science
    my ($cdnflag, $g10flag, $g1112flag); # flags to show if done both gr 10 credit and 11/12 credit for these other SS courses.
    foreach my $cid ( qw(8306 8321 8307)) {
	if ( $completed{$cid} ) { 
	    $cdnflag = 1;
	    $ignore{$cid} = 1; # add this course to the ignore list, since we get a credit for this, can't be elective.
	}
    }

    # Now check the rest
    foreach my $cid ( qw(4306 4307 4309 6306 6307 6309 2140 2150 6340 8339 3140 3150 8340 )) {
	if ( $completed{$cid} ) { 
	    if ( $saskedgrade{$cid} == 10 ) { $g10flag = 1; }
	    if ( $saskedgrade{$cid} == 11 or $saskedgrade{$cid} == 12 ) { $g1112flag = 1; }
	    $ignore{$cid} = 1;
	}
    }

    if ( not $cdnflag or not $g10flag or not $g1112flag ) {
	push @incomplete, '12 Social';
    }


    # 5. Electives: 6 more electives at 11 level or higher.
    # Loop over completed courses
    my @electives;
    foreach my $courseid ( keys %completed ) {
	my $grade = $saskedgrade{$courseid};

	if ( $ignore{$courseid} ) { next; } # these don't count, done above.
	if ( $grade < 11 ) { next; } # lower grade level
	push @electives, $courseid;
    }

    if ( @electives < 6 ) { # error
	push @incomplete, '12 Electives';
    }

    return @incomplete;


} # end of checkCredit12




#-----------------
sub findGrade9Pass {
#-----------------

    use Number::Format qw(:all);

    my $sth1 = $dbh->prepare("select distinct subjcode from eval where studnum = ?");
    my $sth2 = $dbh->prepare("select term, a1 from eval where studnum = ? and subjcode = ? order by term desc");
    
    # Get students in grade 9, then check their marks.
    my $sth = $dbh->prepare("select studnum from student where grade = 9");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    my ($fail, $pass);
    my @marks;
    while ( my $studnum = $sth->fetchrow ) {

	# Get Courses.
	$sth1->execute( $studnum );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
COURSE:	while ( my $subjsec = $sth1->fetchrow ) {

	    # Get Marks.
	    $sth2->execute( $studnum, $subjsec );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	    while ( my ($term,$mark) = $sth2->fetchrow ) {
		if ( not $mark ) { 
#		    print "No Mark - STUD:$studnum CRSE:$subjsec Term $term<br>\n";
		    next;
		}
		push @marks, $mark;
		next COURSE;
	    }
	}

	# Average for this student
	my ($sum,$count,$avg);
	foreach my $val ( @marks ) {
	    $sum += $val;
	    $count++;
	}
	
	if ( $count ) {
	    $avg = round($sum / $count, 1);
	}

	if ( $avg >= $passingaverage ) {
	    $pass++;
	} else {
	    $fail++;
	}

#	print qq{<div>SN:$studnum AVG:$avg</div>\n};
	
    } # end of student loop

 
#    print qq{<div>Pass:$pass Fail:$fail</div>\n};

    return "$pass:$fail";
	
}






#---------------------
sub viewStudentCredits {
#---------------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }

    if ( not $arr{provnum} ) { print "<h3>Student Not found</h3>\n"; print "</body></html>\n"; exit; }


    
    # Get Course Info from Sask Ed courses.
    my $sth1 = $dbh->prepare("select title, grade from sasked_courses where code = ?");


    # Load 'Program' hash
    my $sth = $dbh->prepare("select defaultvalue from meta where fieldid = 'program'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $progdata = $sth->fetchrow;
    my %program = split(/\s+/, $progdata);


    
    # Student Name, Grade, Program
    my $sth = $dbh->prepare("select lastname, firstname, studnum, grade, program from studentall where provnum = ?");
    $sth->execute( $arr{provnum} );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my ($lastname, $firstname, $studnum, $grade, $programcode ) = $sth->fetchrow;
    my $assumeflag;
    if ( not $programcode ) { $programcode = 10; $assumeflag = '(Assumed)'; }
    my $programtext = $program{$programcode};
    $programtext =~ s/_/ /g;
    
    print qq{<h3>$firstname $lastname  Grade:$grade</h3>\n};
    print qq{<div><b>Program</b> $programtext $assumeflag</div>\n};


    # Load their completed courses
    my $sth = $dbh->prepare("select * from sasked_completedcourses where provnum = ? 
      order by schoolyear, courseenddate");
    $sth->execute( $arr{provnum} );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

    print qq{<table cellpadding="3" cellspacing="0" border="1">\n};
    print qq{<tr><th>Title</th><th>Code</th><th>Grade</th><th>School<br>Year</th><th>Mark</th>};
    print qq{<th>Credits<br>Earned</th></tr>\n};

    my $totalcredits;
    my ($curryear, $prevyear);
    my $first = 1;

    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;

	$prevyear = $curryear;
	$curryear = $r{schoolyear};
	if ( $prevyear ne $curryear and not $first ) {
	    print qq{<tr style="background-color:#EEE;"><td colspan="5" class="bra">};
	    print qq{Current Credits</td><td  class="cn">$totalcredits</td></tr>\n};
	}
	$first = 0;
	
	# Get Course Title if not stored
	my ($title, $grade);
	$sth1->execute( $r{courseid} );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	my ($title, $grade) = $sth1->fetchrow;

	my $creditcolor = 'green';
	if ( not $r{creditsearned} ) {
	    $creditcolor = 'red';
	}

	print qq{<tr><td>$title</td><td>$r{courseid}</td><td>$grade</td><td>$r{schoolyear}</td>};
	print qq{<td>$r{finalmark}</td>};
	print qq{<td style="color:$creditcolor;" class="cn">$r{creditsearned}</td></tr>\n};
	$totalcredits += $r{creditsearned};

    }

    print qq{<tr style="background-color:#EEE;"><td colspan="5" class="bra">Total Credits</td>};
    print qq{<td class="cn">$totalcredits</td></tr>\n};
    print qq{</table>\n};

=head

    my @result = checkCredit10( $r{provnum}, $dbh );
    print q{<td>};
    if ( not @result ) { 
	print qq{<b>Complete</b>}; 
    } else { #print incompletes
	foreach my $res ( @result ) { 
	    print qq{$res<br>\n}; 
	}
    }
    print q{</td><td>};

    my @result11 = checkCredit11( $r{provnum}, $dbh );
    if ( not @result11 ) { 
	print qq{<b>Complete</b>}; 
    } else { #print incompletes
	foreach my $res ( @result11 ) { 
	    print qq{$res<br>\n}; 
	}
    }
    print qq{</td></tr>\n};
=cut

    print qq{</body></html>\n};
    exit;

}
