#!/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 = ('Main' => 'Main',
	   'Grade' => 'Grade',
	   'Continue' => 'Continue',
	   'Section' => 'Section',
	   'Course' => 'Course',
	   'Teacher' => 'Teacher',
	   'Error' => 'Error',

	   );

my $self = 'preplancoursemaster.pl';


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


# Load Grad Library (checkCredit*)
eval require "../../lib/libgrad.pl";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


my $passingaverage = $pp_Grade9PassingAverage; # from configuration


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);
$dbh->{mysql_enable_utf8} = 1;

# 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 $graderef = \%saskedgrade;


my $title = "Preplan Course Offerings for Upcoming Year";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};
print qq{<style>td.v { vertical-align:top;align:center; }\n};
print qq{.btn-grn {
    width:400px; height:60px; margin:10px; border-radius:15px;
    background-color: #042; color :#FFF; font-size:150%; font-weight:bold; }
</style>\n};

print qq{$chartype\n</head><body style="padding:1em;">\n};
print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{ <a href="$reppage">Report Card</a> ]\n};

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


# Find those passing in 9; check those passing courses in grade 10;

# Get all requirements for all students, split by grade
my %req; # requirements, $req{studnum}{grade} = @incompletes;

# Get all students
my $sth = $dbh->prepare("select studnum, lastname, firstname, provnum, grade from student");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}

while ( $ref = $sth->fetchrow_hashref ) {
    my %r = %$ref;
    my $studnum = $r{studnum};
    
    if ( $r{provnum} ) {

	my @result10 = checkCredit10( $r{provnum}, $graderef, $dbh, $r{studnum} );
	if ( @result10 ) {
	    push @{ $req{$studnum}{10}}, @result10;
	}
	
	my @result11 = checkCredit11( $r{provnum}, $graderef, $dbh );
	if ( @result11 ) { 
	    push @{ $req{$studnum}{11}}, @result11;
	}
	    
	my @result12 = checkCredit12( $r{provnum}, $graderef, $dbh );
	if ( @result12 ) {
	    push @{ $req{$studnum}{12}}, @result12;
	}

    } else {
	print qq{<div>Missing Provincial Number for $r{firstname} $r{lastname} ($r{studnum}), };
	print qq{Grade $r{grade}</div>\n};
    }

} # end student loop loading up %res hash.


# Display the structure to test
=head
foreach my $studnum ( sort keys %req ) {
    foreach my $gr ( sort keys %{ $req{$studnum}} ) {
	print @{ $req{$studnum}{$gr} }, "<br>\n";
#	my @inc = @{ $req{$studnum}{$gr} };
#	my $inc = join(', ',@inc );
#	print qq{<div>SN:$studnum - GR:$gr - INC:$inc</div>\n};
    }
}
=cut


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

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

}  elsif ( $arr{page} == 2 ) { # Page 3
    delete $arr{page};
    setCourses(); # set semester, teacher, grade offered, any backings.
    
} elsif ( $arr{page} == 3 ) { # Page 4
    delete $arr{page};
    setPeriods(); # choose periods for courses for each semester (due to all year courses mixes).
    
} elsif ( $arr{page} == 4 ) { # Page 5
    delete $arr{page};
    viewCourses(); # update periods, display final table values; provide links to reset.
}


#--------------
sub showTeacher {  # display all teacher values for page 3 and 4.
#--------------

    # 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";
    }


    # Get Number of Semesters in preplan CM table
    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;

    
    # Get PERIODS for each course, each semester.
    my $sth1 = $dbh->prepare("select periods from preplan_courseperiods 
      where subjsec = ? and semester = ?");
    
    # Get TEACHERS in the preplan coursemaster table
    my %sort;
    my $sth = $dbh->prepare("select distinct teacher from preplan_coursemaster where teacher is not NULL");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $teacher = $sth->fetchrow ) {
	# print qq{<div>Teacher:$teacher</div>\n};
	$sort{"$teachername{$teacher}$teacher"} = $teacher;
    }


    # Get BACKINGS for courses. - not done yet!
    my %backed;
    my $sth = $dbh->prepare("select subjsec, backedwith from preplan_coursemaster"); 
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my ($subjsec, $backedwith) = $sth->fetchrow ) {
	if ( $backedwith ) {
	    if ( $backed{$subjsec} and $backed{$subjsec} ne $backedwith ) { # a problem. triple back?

	    } else {
		$backed{$subjsec} = $backedwith;
	    }
	    $backed{$backedwith} = $subjsec;
	}
    }

   
    # Display Courses by this teacher.
    my $sth = $dbh->prepare("select * from preplan_coursemaster where teacher = ?");

    # Loop through each teacher
    foreach my $key ( sort keys %sort ) {
	my $teacher = $sort{$key};

#	print qq{<div>Teacher:$teacher</div>\n};
	
	my ( %courses, %totals, %data); # totals{semester};

	# Get Courses by this teacher.
	$sth->execute($teacher);
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    my $subjsec = $r{subjsec};
	    my $semester = $r{semester};

	    $data{$subjsec} = $ref;
	    push @{ $courses{ $semester } }, $subjsec;
	}

	# 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="float:left;border:1px solid gray;margin:1em;">\n};
	    print qq{<caption style="font-size:120%;font-weight:bold;margin:0.3em;text-align:left;">};
	    print qq{$teachername{$teacher}</caption>\n};
	    
	    print qq{<tr>};
	    foreach my $semester (1..$semestercount) {
		print qq{<th class="bcn">Semester $semester</th><th>Code</th>};
		print qq{<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);
		
		my $backed;
		if ( $data{$subjsec}->{backedwith} ) {
		    $backed = qq{<span style="color:red;font-weight:bold;font-size:150%;">*</span>};
		}

		my $userid = $data{$subjsec}->{teacher};
		my $teacher = $teachername{$userid};
		# my $grade = $data{$subjsec}->{offeredto};
		
		print qq{<td>$saskedname{$code}$backed</td><td>$subjsec</td>};
		# print qq{<td>$teacher</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;
		    print qq{<td class="cn">$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);

	    my $userid = $data{$subjsec}->{teacher};
	    my $teacher = $teachername{$userid};
	    
	    foreach my $semester (1..$semestercount) {
		print qq{<td>$saskedname{$code}</td><td>$subjsec</td>};
		# print qq{<td>$teacher</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;
		    print qq{<td class="cn">$periods</td>};
		    $totals{$semester}+= $periods;
		} else {
		    print qq{<td></td>};
		}
	    }
	    print qq{</tr>\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 teacher loop

    exit;



}


#----------------
sub viewCourses { # update Periods; display all course values.
#----------------

    print qq{<h3>Page 5 - View Courses</h3>\n};
    
    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }


    # Update the Periods
    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, $courses, $semester) = split(':', $key);
	my $periods = $arr{$key};

	my @courses = split('/', $courses ); # could be compound

	foreach my $subjsec ( @courses ) {
	    # 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; }
	    
	    } 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";
	    }
	}

    }
    # Course Periods all updated at this point.

    
    print qq{<div style="font-weight:bold;margin:1em;color:green;">};
    print qq{Periods Updated in Preplan Course Master</div>\n};

    
    # 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";
    }

    

    # Display Course 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");


    foreach my $grade (10..12) {

	my (%sort,%courses, %totals, %data); # totals{semester};

	# Get 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};
	    $data{$subjsec} = $ref;
	    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="float:left;border:1px solid gray;margin:1em;">\n};
	    print qq{<caption style="font-size:120%;font-weight:bold;margin:0.4em;">};
	    print qq{Grade $grade</caption>\n};
	    
	    print qq{<tr>};
	    foreach my $semester (1..$semestercount) {
		print qq{<th class="bcn">Semester $semester</th><th>Code</th>};
		print qq{<th>Teacher</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);
		
		my $backed;
#		#if ( $data{$subjsec}->{backedwith} ) {
#		    $backed = qq{<span style="color:red;">*</span>};
#		}

		my $userid = $data{$subjsec}->{teacher};
		my $teacher = $teachername{$userid};
		# my $grade = $data{$subjsec}->{offeredto};
		
		print qq{<td>$saskedname{$code}$backed</td><td>$subjsec</td>};
		print qq{<td>$teacher</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;
		    print qq{<td class="cn">$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);

	    my $userid = $data{$subjsec}->{teacher};
	    my $teacher = $teachername{$userid};
	    
	    foreach my $semester (1..$semestercount) {
		print qq{<td>$saskedname{$code}</td><td>$subjsec</td>};
		print qq{<td>$teacher</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;
		    print qq{<td>$periods</td>};
		    $totals{$semester}+= $periods;
		} else {
		    print qq{<td></td>};
		}
	    }
	    print qq{</tr>\n};
	}


	if ( $rows ) {
	    print qq{<tr style="background-color:#BBB">\n};
	    foreach my $semester (1..$semestercount) {
		print qq{<td class="bra" colspan="3" style="font-size:144%;">};
		print qq{Semester $semester Totals</td><td class="bcn">$totals{$semester}</td>};
	    }
	    print qq{</tr>\n</table>\n};
	}

	
	# Now put in backings table
	# Start Table
	my $first = 1;
	
	foreach my $subjsec ( sort keys %data ) {
	    if ( $data{$subjsec}->{backedwith} ) {

		if ( $first ) {
		    print qq{<table cellpadding="3" border="1" cellspacing="0" };
		    print qq{style="float:left;margin:1em;">\n};
		    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};
		    $first = 0;
		}
		
		my ($code, $section) = split('-',$subjsec);
		my $backedwith = $data{$subjsec}->{backedwith};
		my ($bcode, $bsection) = split('-',$backedwith);
		print qq{<tr><td>$saskedname{$code} ($subjsec)</td>};
		print qq{<td>$saskedname{$bcode} ($backedwith)</td></tr>\n};
	    }
	}
	
	if ( not $first ) {
	    print qq{</table>\n};
	}

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

    
    } # end of grades loop


    print qq{<form action="$self" method="post">\n};
    print qq{<input type="submit" value="Start Over - Choose Courses">\n};
    print qq{</form>\n};


    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="submit" value="Update Teachers,Semesters,Backings,Periods">\n};
    print qq{</form>\n};
    
    print qq{</body></html>\n};

    exit;

} # end of viewCourses; (and update periods)



#--------------
sub setPeriods {
#--------------

    # print qq{<div>Set Periods</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:

    print qq{\n<table width="100%">\n<tr><td style="vertical-align:top;">\n\n};
    #====================================

    
    print qq{<h3>Page 4 - Set Course Periods</h3>\n};
    
    my $dayspercycle = $pp_DaysPerCycle;
    my $teachingperiods = $pp_PeriodsPerDay;
    my $defppc = $pp_DefaultPeriodsPerCycle;


    print qq{<table cellpadding="3" border="1" cellspacing="0" };
    print qq{style="border:1px solid gray;margin:1em;">\n};
    print qq{<caption>From Configuration System</caption>\n};
    
    print qq{<tr><td class="bra">Teaching Periods Per Day</td>};
    print qq{<td>$pp_PeriodsPerDay</td></tr>\n};
    
    print qq{<tr><td class="bra">Days per Cycle</td>};
    print qq{<td>$pp_DaysPerCycle</td></tr>\n};

    print qq{<tr><td class="bra">Default Periods per Cycle<br>(for a course)</td>};
    print qq{<td>$pp_DefaultPeriodsPerCycle</td></tr>\n};

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


    
#    delete $arr{defppc};
#    delete $arr{dayspercycle};
#    delete $arr{teachingperiods};


    
    #-----------------------------------------------------------
    # This first section of the subroutine updates values passed from
    # previous page: teacher, semester, and offeredto, and backings.
    # ----------------------------------------------------------
    
    # Extract 'BackAxx' and 'BackBxx' 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};
	}
    }

    # Update the Backing Field (backedwith)
    my %backed; # add both pairs into the hash  A->B, B->A
    
    my $sth = $dbh->prepare("update preplan_coursemaster set backedwith = ? where subjsec = ?");
    my $sth1 = $dbh->prepare("select backedwith from preplan_coursemaster where subjsec = ?");
    
    foreach my $idx ( sort keys %back ) {
	foreach my $gr ( 10..12 ) {
	    my $crsA = $back{$idx}{"backA$gr"};
	    my $crsB = $back{$idx}{"backB$gr"};
	    
	    if ( $crsA and $crsB ) { # we have the 2 courses
		# set B into A record
		
		my ($codeA,$section) = split('-',$crsA);
		my ($codeB,$section) = split('-',$crsB);

		$backed{$crsA} = $crsB;
		$backed{$crsB} = $crsA;
		
		# Check for an existing backed subjsec value in 'backedwith' field.
#		$sth1->execute($crsB);
#		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
#		my $count = $sth1->fetchrow;
#		if ( not $count ) {
#		    $sth->execute($crsA, $crsB);
#		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
#		}

#		$sth1->execute($crsA);
#		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
#		my $count = $sth1->fetchrow;
#		if ( not $count ) {
		    $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 style="font-weight:bold;margin:1em;color:green;">};
    print qq{Preplan Course Master Updated - Semester, Teacher, Grade Offered, and Backings</div>\n};

    # Passed Updates now done: teacher, semester, offeredto, backings.
    #-----------------------------------------------------------------------------------

    
    #----------------------------------------------
    # Now get the periods for the courses - page 4
    #----------------------------------------------
    
    # Get Number of Semesters; not using configuration since we need to use set values.
    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{<div style="font-size:120%;border:1px solid gray;margin:1em;width:28ch;padding:0.4em;">};
    print qq{Backed Courses are in bold type</div>\n};
    
    print qq{<input type="submit" value="Update Periods">\n};


    foreach my $grade (10..12) {

	my (%sort,%courses, %totals, %data );
	# Get Courses for this grade, and compress backings.
	$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};
	    $temp{$subjsec} = $ref;
	    $data{$subjsec} = $ref;
	}
	my $cref = compressCourse( \%temp );
	my %compressed = %$cref; # only keys of use; values will be damaged for compound keys
	
#	foreach my $key ( sort keys %compressed ) {
#	    print qq{K:$key  V:$compressed{$key}<br>\n};
#	}

	foreach my $key ( sort keys %compressed ) { # may have compound keys with /
	    my @vals = split('/', $key); # use $val[0], first subjsec
	    my $subjsec = $vals[0];
	    %r = %{ $data{$subjsec}};
	    push @{ $courses{$r{semester}} }, $key; # could be compound
	}

	# 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 }};
		# check for compound key...
		my @vals = split('/', $subjsec); # use $val[0], first subjsec
		if ( scalar @vals > 1 ) { print qq{<td class="bla">}; } else { print qq{<td>}; }
		foreach my $val ( @vals ) {
		    my ($code, $section) = split('-', $val);
		    # my $backed;
		    # if ( $data{$subjsec}->{backedwith} ) {
		    #    $backed = qq{<span style="color:red;font-weight:bold;">*</span>};
		    # }
		    print qq{$saskedname{$code}<br>\n};
		}
		print qq{</td><td>$subjsec</td>}; # subjsec (or compound subjsec/subjsec)

		# Get Periods for this course, this semester; if compound use first only
		if ( $subjsec ) { # could be blank
		    my $tempsubjsec;
		    if ( $subjsec =~ m/\// ) { # compound subjsec
			my @vals = split('/', $subjsec); # use $val[0], first subjsec
			$tempsubjsec = $vals[0];
		    } else { $tempsubjsec = $subjsec; }
		    $sth1->execute($tempsubjsec, $semester);
		    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		    my $periods = $sth1->fetchrow;
		    # if ( not $periods ) { $periods = $defppc; } # default periods per cycle; 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);

	    my $backed;
	    if ( $data{$subjsec}->{backedwith} ) {
		$backed = qq{<span style="color:red;font-weight:bold;">*</span>};
	    }
	    
	    foreach my $semester (1..$semestercount) {
		print qq{<td>$saskedname{$code}$backed</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; 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};

    #=========================
    print qq{\n</td><td style="vertical-align:top;border:3px solid black;">\n}; # next column

    print qq{<div style="position:absolute;right:3em;;">};
    print qq{[ <span style="color:red;font-weight:bold;font-size:100%;">* = Backed</span> ]</div>\n};
    
    print qq{<div style="font-weight:bold;font-size:144%;padding:1em 0 0 1em;">};
    print qq{Current Teacher Load</div>\n};


    showTeacherSummary();
    showTeacher(); # show current loading.
    
    print qq{</td></tr>\n};
    print qq{</table>\n};


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

    exit;
} # end of setPeriods - Page 4



#---------------
sub setCourses {  # Page 3
#---------------

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

    print qq{\n<table width="100%">\n<tr><td>\n\n};
    #====================================
    
    
    print qq{<h3>Page 3 - Set Course Values: Teacher, Semester, Grade Offered, Backings</h3>\n};

    
    print qq{<div style="width:80ch;margin-bottom:1em;">The <b>Grade
    Offered</b> is where students take a course designed for a
    different grade. (ie. Art 30 offered to Grade 10's). <b>Course
    Backings</b> are 2 (or more) courses that are taught at the same
    time (typically to split a large student group based on interests
    or ability). They may actually be taught in the same classroom by
    the same teacher or they may be in separate rooms with separate
    teachers. These backed courses <b>must</b> be taught in the same
    semester and have the same number of periods per cycle.</div>\n};


    print qq{<p style="width:80ch;">For courses <b>longer than a
    semester</b>, but <b>not all year</b>, select 'All Year', and then only set
    the periods for the semesters that the course is offered, on page
    4.</p>\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};

 
    # Get Semesters
    my $semesters;
    if ( $pp_SemesterCount ) {
	print qq{<h3>Semester Count - $pp_SemesterCount (from configuration)</h3>\n};
	$semesters = $pp_SemesterCount;
	
    } else { # get from table.
	my $sth = $dbh->prepare("select max(semester) from preplan_coursemaster where semester != 'allyear'");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	$semesters = $sth->fetchrow;
	print qq{<h3>Semester Count - $semesters (from preplan table)</h3>\n};
    }

    
    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};
	
    } # Delete current table.

    
    # Put in a teacher of that course if blank in PPCM
    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, teacher from preplan_coursemaster");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    
    while ( my ($id, $subjcode, $teacher) = $sth->fetchrow ) {

	if ( not $teacher ) { # we will add from subject table, if present.
	    # Get current a teacher from 'subject' table for this course (NOTE: not unique))
	    $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; }
	    }
	}
    }

    # Check for records in preplan_coursemaster;
    my $sth = $dbh->prepare("select count(*) from preplan_coursemaster");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    my $count = $sth->fetchrow;
    if ( not $count ) {
	print qq{<hr style="width:60ch;margin-left:0;">\n};
	print qq{<h3>No Pre-plan Course Master records found. Please start on Page 1</h3>\n};
	print qq{<form action="$self" method="post">\n};
	print qq{<input type="submit" value="Go to Start - Page 1"></form>\n};
	print qq{</body></html>\n};
	exit;
    }
    

    print qq{<h3>Course Teachers Added, where none set</h3>\n};


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


    
    # Not Needed anymore
    # Show Days per cycle, PPD
#    print qq{<table cellpadding="3" border="1" cellspacing="0" };
#    print qq{style="border:1px solid gray;margin:1em;">\n};
#    print qq{<caption>From Configuration System</caption>\n};
    
#    print qq{<tr><td class="bra">Teaching Periods Per Day</td>};
#    print qq{<td>$pp_PeriodsPerDay</td></tr>\n};
#    
#    print qq{<tr><td class="bra">Days per Cycle</td>};
#    print qq{<td>$pp_DaysPerCycle</td></tr>\n};

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

    
    
    # Submit Button
    print qq{<div style="text-align:left;padding:0.4em;">};
    print qq{<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 semester, description ");

    foreach my $grade ( 10..12 ) {

	# Start Table
	print qq{<table cellpadding="3" border="1" cellspacing="0" };
	print qq{style="float:left;margin:0.5em 1.5em;">};
	print qq{<caption style="font-weight:bold;font-size:120%;margin:0.4em;">};
	print qq{Grade $grade</caption>\n};
	
	# 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, %data);
	my $count = 1;

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

	    %r = %$ref;
	    my $id = $r{id};
	    $data{$r{subjsec}} = $ref;
	    
	    $courses{$r{subjsec}} = $r{description};
	    $sortc{"$r{description}-$r{subjsec}"} = $r{subjsec};
	
	    print qq{<tr><td class="la">$count. <b>$r{description}</b> ($r{subjsec})</td>};
	    print qq{<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">};
#	    if ( $r{semester} ) {
		print qq{<option>$r{semester}</option>\n};
#	    } else {
#		print qq{<option>$defsem</option>\n};
#	    }
	    foreach my $sem (1..$semesters) {
		if ( $sem eq $r{semester} ) { next; }
		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">};
	    if ( $r{offeredto} ) {
		print qq{<option>$r{offeredto}</option>\n};
	    } else {
		print qq{<option>$r{grade}</option>\n};
	    }
	    foreach my $g (10..12) {
		if ( $r{offeredto} == $g ) { next; }
		print qq{<option>$g</option>\n};
	    }
	    print qq{</select></td></tr>\n};

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


	# Backings
	# Start Table
	print qq{<table cellpadding="3" border="1" cellspacing="0" };
	print qq{style="float:left;margin:0.5em 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)<br>\n};
	print qq{<span style="font-size:90%">Courses must have same semester and #periods.};
	print qq{</span></caption>\n};

	print qq{<tr><th>$lex{Course} A</th><th>$lex{Course} B</th></tr>\n};

	# Look for existing backings.
	my $count = 1; # count any existing values
	foreach my $desc ( sort keys %sortc ) {
	    my $subjsec = $sortc{$desc};
	    if ( $data{$subjsec}->{backedwith} ) {
		my $back = $data{$subjsec}->{backedwith};
		my ($bcode,$bsection) = split('-',$back);
		my ($code,$section) = split('-', $subjsec);
		print qq{<tr><td><select name="backA$grade:$count">};
		print qq{<option value="$subjsec">$saskedname{$code} ($subjsec)</option>\n};
		foreach my $desc ( sort keys %sortc ) {
		    my $subjcode = $sortc{$desc};
		    print qq{<option value="$subjcode">$desc</option>};
		}
		print qq{<option></option></select></td>\n};

		print qq{<td><select name="backB$grade:$count">};
		print qq{<option value="$back">$saskedname{$bcode} ($back)</option>\n};
		foreach my $desc ( sort keys %sortc ) {
		    my $subjcode = $sortc{$desc};
		    print qq{<option value="$subjcode">$desc</option>};
		}
		print qq{<option></option></select></td></tr>\n\n};
		$count++;
	    }
	}
	
	foreach my $idx ($count..10) {

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

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

	}
	print qq{</table>\n};
	# End of backings tables.
	
	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;">};
    print qq{<input type="submit" value="$lex{Continue}"></div>\n};

    print qq{</form>\n};

    #=========================
    print qq{\n</td><td style="vertical-align:top;border:3px solid black;">\n}; # next column

    print qq{<div style="position:absolute;right:3em;;">};
    print qq{[ <span style="color:red;font-weight:bold;font-size:100%;">* = Backed</span> ]</div>\n};

    print qq{<div style="font-weight:bold;font-size:144%;padding:1em 0 0 1em;">};
    print qq{Current Teacher Load</div>\n};

    showTeacherSummary();
    showTeacher(); # show current loading.
    
    print qq{</td></tr>\n};
    print qq{</table>\n};
    
    print qq{</body></html>\n};

    exit;
    
} # end of setCourses - Page 3




#------------------------------
sub findCourseRequirementCounts {
#------------------------------

    print qq{<h3>Page 1 - View Numbers of Students for Required Courses</h3>\n};

    print qq{<p style="width:80ch;"><b>What's a section?</b> A section
    is a group of students taking a particular course with a
    teacher. If you have too many students taking grade 10 science to
    fit in a room or to be effectively taught, you have another
    teacher teach the same course in another location/time. Each
    section will have a different unique code made up of the Sasked
    course code (4 digit) and then a section number (starting from 1
    typically) with the 2 separated with a dash. (4017-1). Two
    sections of ELA A10 would be 4017-1, and 4017-2).</p>\n};


    print qq{<div style="width:80ch;">These numbers of students will
    help you plan for an appropriate number of sections for each
    course.  The next page, page 2, will allow you to select the
    courses to be offered next year, and how many sections of each
    course. Change the numbers in the <b>select</b> columns, if
    necessary to help in your decision making, so you can see those
    numbers on the next page.</div>\n};

    
    # find grade 9 pass/fail.
    my $res = findGrade9Pass();
    my ($pass9, $fail9) = split(':', $res);
    print qq{<div style="margin:1.5em;font-weight:bold;font-size:120%;">};
    print qq{Grade 9's with Average >= $passingaverage is $pass9</div>\n};
    # These students will all need the required courses below.
    print qq{<hr style="width:20%;margin-left:0;"><br>\n};
    

    # 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};
    
    
    print qq{<div style="font-weight:bold;margin:0.4em;">};
    print qq{Change any selected values as necessary for course requirement student counts</div>\n};
    print qq{<div><input type="submit" value="$lex{Continue}"></div>\n};

    
    # Grade 10
    my %incomplete; # students in upper grades 10-12 with incomplete requirements 
                    #(or failing current course).
    # incomplete{requirement}{grade of student missing this requirement};
    # Go through 10, 11, 12 for any missing grade 10 requirements. (skipping electives)
    my @req10 = ( 'ELA A10', 'ELA B10', 'Science 10', '10 Math', '10 Social' );
    my %required10 = map { $_ => 1 } @req10;

    
    # Klunky but what the heck...
    my %adultprogram;
    my $sth = $dbh->prepare("select studnum, grade, program from student 
      where grade = 10 or grade = 11 or grade = 12");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my ($studnum, $grade, $program) = $sth->fetchrow ) {

	if ( $program eq '09' ) { 
	    $adultprogram{$studnum} = 1;
	    next;
	} # set for adult program
	
	if ( my @inc = @{ $req{$studnum}{10} } ) {
	    foreach my $val ( @inc ) {
		if ( $required10{ $val } ) {
		    $incomplete{$val}{$grade}++;
		}
	    }
	}
    }

    # Display the grade 10 requirements
    my %reqtotal;
    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;">};
    print qq{Grade 10 Required Courses</caption>\n};
    print qq{<tr><th>Requirement</th><th>Grade 9</th><th>Grade 10</th><th>Grade 11</th>};
    print qq{<th>Grade 12</th><th>Total</th><th>Select</th></tr>\n};

    foreach my $req ( @req10 ) {
	my $reqtotal = $pass9 + $incomplete{$req}{10} + $incomplete{$req}{11} + $incomplete{$req}{12};
	print qq{<tr><td>$req</td><td>$pass9</td><td>$incomplete{$req}{10}</td>};
	print qq{<td>$incomplete{$req}{11}</td><td>$incomplete{$req}{12}</td><td>$reqtotal</td>};
	print qq{<td><input type="text" style="width:3em;" name="$req" value="$reqtotal"></td></tr>\n};
    }

    print qq{<tr><td colspan="8">};
    print qq{Does not include students taking the course and currently passing</td></tr>\n};
    print qq{</table>\n};


    

    # now calculate the grade 11 requirements for next year;
    
    my %incomplete; 
    # incomplete{requirement}{grade of student missing this requirement};
    # Go through 10, 11, 12 for any missing grade 10 requirements. (skipping electives)
    my @req11 = ( 'ELA 20', '11 Science', '11 Math', '11 Social' );
    my %required11 = map { $_ => 1 } @req11;

    
    my $sth = $dbh->prepare("select studnum, grade from student 
      where grade = 10 or grade = 11 or grade = 12");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my ($studnum, $grade) = $sth->fetchrow ) {

	if ( $adultprogram{$studnum} ) { next; } # skip adult program
	
	if ( my @inc = @{ $req{$studnum}{11} } ) {
	    foreach my $val ( @inc ) {
		if ( $required11{ $val } ) {
		    $incomplete{$val}{$grade}++;
		}
	    }
	}
    }

    # Display the grade 11 requirements
    my %reqtotal;
    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;">};
    print qq{Grade 11 Required Courses</caption>\n};
    print qq{<tr><th>Requirement</th><th>Grade 10</th><th>Grade 11</th>};
    print qq{<th>Grade 12</th><th>Total</th><th>Select</th></tr>\n};

    foreach my $req ( @req11 ) {
	my $reqtotal = $incomplete{$req}{10} + $incomplete{$req}{11} + $incomplete{$req}{12};
	print qq{<tr><td>$req</td><td>$incomplete{$req}{10}</td><td>$incomplete{$req}{11}</td>};
	print qq{<td>$incomplete{$req}{12}</td><td>$reqtotal</td>};
	print qq{<td><input type="text" style="width:3em;" name="$req" value="$reqtotal"></td></tr>\n};
    }
    print qq{<tr><td colspan="8">};
    print qq{Does not include students taking the course and currently passing</td></tr>\n};
    print qq{</table>\n};

    print qq{<br clear="left">\n};
    # end of grade 11

    
    # grade 12 requirements for next year;
    my %incomplete; 
    # incomplete{requirement}{grade of student missing this requirement};
    # Go through 10, 11, 12 for any missing grade 10 requirements. (skipping electives)
    my @req12 = ( 'ELA A30', 'ELA B30', '12 Social' );
    my %required12 = map { $_ => 1 } @req12;
    
    my $sth = $dbh->prepare("select studnum, grade from student where grade = 11 or grade = 12");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
    while ( my ($studnum, $grade) = $sth->fetchrow ) {
	
	if ( $adultprogram{$studnum} ) { next; } # skip adult program

	if ( my @inc = @{ $req{$studnum}{12} } ) {
	    foreach my $val ( @inc ) {
		if ( $required12{ $val } ) {
		    $incomplete{$val}{$grade}++;
		}
	    }
	}
    }

    # Display the grade 12 requirements
    my %reqtotal;
    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;">};
    print qq{Grade 12 Required Courses</caption>\n};
    print qq{<tr><th>Requirement</th><th>Grade 11</th><th>Grade 12</th><th>Total</th>};
    print qq{<th>Select</th></tr>\n};

    foreach my $req ( @req12 ) {
	my $reqtotal = $incomplete{$req}{11} + $incomplete{$req}{12};
	print qq{<tr><td>$req</td><td>$incomplete{$req}{11}</td>};
	print qq{<td>$incomplete{$req}{12}</td><td>$reqtotal</td>};
	print qq{<td><input type="text" style="width:3em;" name="$req" value="$reqtotal"></td></tr>\n};
    }
    print qq{<tr><td colspan="8">};
    print qq{Does not include students taking the course and currently passing</td></tr>\n};
    print qq{<tr><td colspan="8" class="bla">Adult Program Students not counted above - };
    print scalar keys %adultprogram;
    print qq{</td></tr>\n};
    
    print qq{</table>\n};
    # Grade 12 End


    
    # Phys Ed. Requirement
    my $sth = $dbh->prepare("select studnum, provnum, lastname, firstname, grade from student 
      where grade = ?");

    my $sth1 = $dbh->prepare("select courseid from sasked_completedcourses 
      where provnum = ? and creditsearned = '1'");

    my %incomplete;
    foreach my $grade ( '10','11','12' ) {
	$sth->execute($grade);
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	while ( my ( $studnum, $provnum, $lastname, $firstname, $grade ) = $sth->fetchrow ) {
	    
	    # Load their completed courses
	    $sth1->execute( $provnum );
	    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
	    my %completed;
	    while ( my $courseid = $sth1->fetchrow ) {
		$completed{$courseid} = 1;
	    }

	    # Wellness 10 (4600), Phys Ed 20(6603), Phys Ed 30(8603)
	    if ( not $completed{4600} and not $completed{6603} and not $completed{8603} ) {
		$incomplete{$grade}++;
	    }
	}
    }

    my %reqtotal;
    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;">};
    print qq{Phys Ed Requirement 10-12</caption>\n};
    print qq{<tr><th>Grade 10</th><th>Grade 11</th><th>Grade 12</th><th>Total</th>};
    print qq{<th>Select</th></tr>\n};

    print qq{<tr>};
    foreach my $grade ( '10','11','12' ) {
	$reqtotal += $incomplete{$grade};
	print qq{<td>$incomplete{$grade}</td>};
    }
    print qq{<td>$reqtotal</td>};
    print qq{<td><input type="text" style="width:3em;" name="physed" value="$reqtotal"></td></tr>\n};

    print qq{<tr><td colspan="8">Students without any Phys Ed course };
    print qq{( Wellness 10 (4600), Phys Ed 20(6603), Phys Ed 30(8603))</td></tr>\n};
    print qq{</table>\n};

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

} # end of findCourseRequirementCounts



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

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    print qq{<h3>Page 2 - Select Courses and Sections</h3>\n};

    
    print qq{<div style="width:80ch;margin-bottom:1em;">Use the pull down menus to set
    the number of sections that you want to offer. Select the courses
    that you wish to offer with the checkboxes. Required courses are
    automatically selected.</div>\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 Course Selection Area
    # ---------------------------------
#    print qq{<hr style="width:40%;margin-left:0;">\n};
    print qq{<h2 style="margin-top:1.5em;">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 Grade 10 Courses</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>};
    print qq{<th title="Sections">Sections</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">Sections</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>};
    print qq{</tr>\n};
    
    print qq{</table>\n};

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

    

    # Grade 11 Course Selection Area
    # ---------------------------------
#    print qq{<hr style="width:40%;margin-left:0;">\n};
    print qq{<h2 style="margin-top:1.5em;">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 11 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 Grade 11 Courses</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>};
    print qq{<th title="Sections">Sections</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">Sections</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">};
    print qq{View Course Codes in New Tab</a> ]</td></tr>\n};
    print qq{</table>\n};

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


    
    # Grade 12 Course Selection Area
    # ---------------------------------
#    print qq{<hr style="width:40%;margin-left:0;">\n};
    print qq{<h2 style="margin-top:1.5em;">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 12 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 Grade 12 Courses</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>};
    print qq{<th title="Sections">Sections</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">Sections</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">};
    print qq{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{<p style="width:90ch;"><b>What's A Semester?</b> A
    <b>Semester</b> is the normal length of a course. In a typical 4
    term, 2 semester school, there are 2 terms (2 report cards) per
    semester (September-January, February-June).</p>\n};

    print qq{<p style="width:90ch;">A <b>Term</b> is a period of time
    with a report card at the end. In other schools with a trimester
    system there could be 6 terms and 2 terms per semester. This
    system could also have just 3 terms (ie. 3 report cards) as is
    typical of K-9 schools.</p>\n};


    # Update Table?
    print qq{<div style="font-weight:bold;font-size:140%;margin:1em;color:red;">};
    print qq{Update PrePlan Course Master? };
    print qq{<input type="checkbox" name="deleterecords" value="1"><br>\n};
    print qq{(delete existing courses and insert these new courses instead)};
    print qq{</div>\n};
    
    print qq{<div><input class="btn-grn" type="submit" value="Continue"></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 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";
	
} # end of findGrade9Pass


#-----------------
sub compressCourse {  # full data in; non-destructive; only keys back out.
#-----------------

    my $cref = shift;  # passed a reference to %courses;
    
    my %crs = %$cref;
    my %courses = %crs; # create a new hash.
    
    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;
    }

    # Clean up rest of hash values (set to 1)
    foreach my $key ( sort keys %courses) {
	$courses{$key} = 1;
    }
    
    
    return \%courses;  # Note: the values of the hash are useless.

} # end of compressCourse (join backings)


#---------------------
sub showTeacherSummary {  # summary table of teacher loading.
#---------------------

    # 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";
    }

    
    # Load all course data
    my (%courses, %data);
    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};
	$data{$subjsec} = $ref;
    }


    # Compress Course List
    my $ref = compressCourse( \%data ); # %data not affected.
    %courses = %$ref;

    
    # Get Periods, Semester
    my $sth1 = $dbh->prepare("select semester, periods from preplan_courseperiods where subjsec = ?");
    
    my %periods;
    foreach my $subjsec ( keys %courses ) {

	my @temp; 
	if ( $subjsec =~ m/\// ) { # compound entry (backing)
	    @temp = split('/', $subjsec);
	} else {
	    push @temp, $subjsec; # only a single subjsec;
	}

	my %teacher; # track if more than one entry for the same teacher in compound course list
	foreach my $subjsec ( @temp ) {
	    my %r = %{ $data{$subjsec} };
	    if ( $teacher{ $r{teacher} } ) { next; } # only one period update per teacher.
	    
	    $sth1->execute( $r{subjsec} );
	    while ( my ($semester, $periods ) = $sth1->fetchrow ) {
		$periods{ $r{teacher} }{ $r{semester} } += $periods;
	    }
	    $teacher{ $r{teacher} } = 1; # tracking for how many periods added.
	}
    } # end of adding periods to %periods hash
    

    # 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;


    # Get TEACHERS in the preplan coursemaster table
    my %sort;
    my $sth2 = $dbh->prepare("select distinct teacher from preplan_coursemaster where teacher is not NULL");
    $sth2->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $teacher = $sth2->fetchrow ) {
	# print qq{<div>Teacher:$teacher</div>\n};
	$sort{"$teachername{$teacher}$teacher"} = $teacher;
    }

    my $first = 1;
    # Loop through all teachers.
    foreach my $key ( sort keys %sort ) {
	my $teacher = $sort{$key};
	
	# Start Table
	if ( $first ) {
	    print qq{<table cellpadding="3" border="1" cellspacing="0" };
	    print qq{style="border:1px solid gray;margin:1em;">\n};
	    
	    print qq{<tr><th>Teacher</th>};
	    foreach my $semester (1..$semestercount) {
		print qq{<th class="bcn">Semester $semester</th>};
	    }
	    print qq{</tr>\n};
	    $first = 0;
	}

	
	print qq{<tr><td>$teachername{$teacher}</td>\n};
	
	foreach my $semester (1..$semestercount) {
	    print qq{<td>$periods{$teacher}{$semester}</td>};
	} # end of semester loop
	print qq{</tr>\n};

    } # end of teacher loop

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

}
