#!/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.

my %lex = ('Reset' => 'Reset',
	   'Subjects' => 'Subjects',
	   'Main' => 'Main',
	   'Report Card' => 'Report Card',
	   'Subject' => 'Subject',
	   'Teacher' => 'Teacher',
	   'Term' => 'Term',
	   'Grade' => 'Grade',
	   'Sort by' => 'Sort by',
	   'No Teacher' => 'No Teacher',
	   'Subj-Sec' => 'Subj-Sec',
	   'Terms' => 'Terms',
	   'Edit' => 'Edit',
	   'Delete' => 'Delete',
	   'Clone' => 'Clone',
	   'SmDesc' => 'SmDesc',
	   'Enrol' => 'Enrol',
	   'Error' => 'Error',
	   'Field' => 'Field',
	   'Credit' => 'Credit',
	   'Difficulty' => 'Diff',
	   'Start Term' => 'Start<br>Term',
	   'End Term' => 'End<br>Term',
	   'Record(s) Updated' => 'Record(s) Updated',
	   'Contact' => 'Contact',
	   'Location' => 'Location',
	   'Course' => 'Course',
	   'Section' => 'Section',
	   'Master' => 'Master',
	   'Homeroom' => 'Homeroom',
	   'Seq' => 'Seq',
	   'Course Name' => 'Course Name',
	   'Sequence' => 'Sequence',
	   'Code' => 'Code',
	   'Enrollment' => 'Enrollment',
	   'students' => 'students',
	   'Primary Sort' => 'Primary Sort',
	   'Secondary Sort' => 'Secondary Sort',
	   'No Enrollments' => 'No Enrollments',
	   'Sort Courses by' => 'Sort Courses by',
	   
	   
	   );


use DBI;
use CGI;
use Number::Format qw(:all);


my $self = 'coursemstreset.pl';


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

my $dbtype = 'mysql';
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;

# Load Configuration Variables;
my $sth = $dbh->prepare("select id, datavalue from conf_system where filename = 'admin'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while (	my ($id, $datavalue) = $sth->fetchrow ) {
    eval $datavalue;
    if ( $@ ) {
	print "$lex{Error}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}


# Get Max terms defined in school year
my $maxterms;
foreach my $track ( keys %g_MTrackTerm ) {
    foreach my $trm ( keys %{ $g_MTrackTerm{$track}} ) {
	if ( $trm > $maxterms ) { $maxterms = $trm; }
    }
}


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



my $title = "$lex{Reset} $lex{Course} $lex{Master}";

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>\n};

print qq{[ <a href="$homepage">$lex{Main}</a> |\n};
print qq{<a href="$reppage">$lex{'Report Card'}</a> |\n};
print qq{School Year Max Terms: $maxterms\n};


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


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

} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    showCourses();
    
} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    updateRecords();
}



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


    print qq{<h3>$lex{'Sort Courses by'}</h3>\n};


    # Course Name, Grade
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{'Course Name'},$lex{Grade}">\n};
    print qq{<input type="hidden" name="sortorder" value="name:grade"></form>\n};
    print qq{<br>\n};

    # Grade, Name
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{Grade}, $lex{'Course Name'}">\n};
    print qq{<input type="hidden" name="sortorder" value="grade:name"></form>\n};


    # Grade, Sequence
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{Grade}, $lex{'Sequence'}">\n};
    print qq{<input type="hidden" name="sortorder" value="grade:seq"></form>\n};
    print qq{<br>\n};
    
    # Teacher, Name
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{Teacher}, $lex{'Course Name'}">\n};
    print qq{<input type="hidden" name="sortorder" value="teacher:name"></form>\n};
    print qq{<br>\n};

    # Terms, Grade
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{Terms}, $lex{Grade}">\n};
    print qq{<input type="hidden" name="sortorder" value="term:grade"></form>\n};

    
    # Terms, Name
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{Terms}, $lex{'Course Name'}">\n};
    print qq{<input type="hidden" name="sortorder" value="term:name"></form>\n};
    print qq{<br>\n};
    
    # Homeroom, Name
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{Homeroom}, $lex{'Course Name'}">\n};
    print qq{<input type="hidden" name="sortorder" value="homeroom:name"></form>\n};

    # Homeroom, Sequence
    print qq{<form action="$self" method="post" style="margin:0.4em;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value=" $lex{Homeroom}, $lex{Sequence}">\n};
    print qq{<input type="hidden" name="sortorder" value="homeroom:seq"></form>\n};

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

    exit;
}



#----------------
sub showCourses {
#----------------

    # foreach $key ( sort keys(%arr)) { print "K:$key V:$arr{$key}<br>\n"}
    # Note: only sortorder passed from start page.

    my ($prisort, $secsort) = split(':', $arr{sortorder});
    delete $arr{sortorder};

    
    print qq{<div style="margin:1em;"><b>$lex{'Primary Sort'}</b>: $prisort  };
    print qq{<b>$lex{'Secondary Sort'}</b>: $secsort</div>\n};

    
    # Grades, Homerooms
    my (@grades, @homerooms);
    my $sth = $dbh->prepare("select distinct grade from student where grade != '' and grade is not NULL");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $grade = $sth->fetchrow ) {
	push @grades, $grade;
    }
    @grades = sort {$a <=> $b} @grades;

    $sth = $dbh->prepare("select distinct homeroom from student where homeroom is not NULL and homeroom != ''");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $hr = $sth->fetchrow ) {
	push @homerooms, $hr;
    }
    @homerooms = sort {$a <=> $b} @homerooms;
    
    
    # Load staff into hash
    my (%teachers, %teachername);
    my $sth = $dbh->prepare("select lastname, firstname, st.userid from staff st, staff_multi sm 
     where st.userid = sm.userid and sm.field_name = 'position' and sm.field_value = 'Classroom Teacher'"); 
    $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 Locations
    my @locations;
    $sth = $dbh->prepare("select distinct location from subject where location is not NULL and location != ''");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
    while ( my $loc = $sth->fetchrow ) {
	push @locations, $loc;
    }
    @locations = sort @locations;

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

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

    # Start Table
    print qq{<table cellpadding="3" border="1" cellspacing="0">};


    # Queries setup
    my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
    my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    my $sth3 = $dbh->prepare("select distinct term from eval where subjcode = ? and 
       term != '' and term is not NULL");
    
   
    # Sort and Display
    my %sort;
    my $sth;
    if ( $prisort eq 'name' ) {
	# secondary is blank. only order by name

	my $first = 1;
	$sth = $dbh->prepare("select * from subject order by description, grade, startrptperiod");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    my $id = $r{id};
	    my $inforef = checkCourse($ref);

	    if ( $first ) {
		# Table Heading
		print qq{<tr><th>$lex{Course}</th><th>$lex{Code}</th><th>$lex{Section}</th>};
		print qq{<th>$lex{Grade}</th><th>$lex{Seq}</th><th>$lex{Teacher}</th>};
		print qq{<th>$lex{'Start Term'}</th><th>$lex{'End Term'}</th><th>$lex{Location}</th>};
		print qq{<th>$lex{Enrollment}</th></tr>\n};
		$first = 0;
	    }

	    print qq{<tr><td class="bla">$r{description}</td><td class="cn">$r{subjcode}</td>};
	    print qq{<td class="cn">$r{section}</td>};
	    print qq{<td class="cn">$r{grade}</td>\n};
	
	    # Sequence
	    print qq{<td><input type="text" name="$id:sequence" value="$r{sequence}" size="4"></td>\n};

	    # Teacher
	    print qq{<td><select name="$id:teacher"><option value="$r{teacher}">};
	    if ( not $teachername{$r{teacher}} ) { $teachername{$r{teacher}} = $r{teacher}; }
	    print qq{$teachername{$r{teacher}}</option>\n};
	    
	    foreach my $key ( sort keys %teachers ) {
		my $userid = $teachers{$key};
		print qq{<option value="$userid">$teachername{$userid}</option>};
	    }
	    print qq{\n<option></option></select></td>\n};
	
	    # Terms
	    print qq{<td class="cn"><select name="$id:sterm"><option>$r{startrptperiod}</option>\n};
	    foreach my $trm ( 1..$maxterms ) {
		if ( $trm == $r{startrptperiod} ) { next; }
		print qq{<option>$trm</option>};
	    }
	    print qq{</select></td>\n};
	    
	    print qq{<td class="cn"><select name="$id:eterm"><option>$r{endrptperiod}</option>\n};
	    foreach my $trm ( 1..$maxterms ) {
		if ( $trm == $r{endrptperiod} ) { next; }
		print qq{<option>$trm</option>};
	    }
	    print qq{</select></td>\n};

	    
	    # Location
	    if ( @locations ) {
		print qq{<td><select name="$id:location"><option>$r{location}</option>};
		foreach my $loc ( @locations ) {
		    print qq{<option>$loc</option>\n};
		}
		print qq{<option></option></select></td>\n};
	    
	    } else {
		print qq{<td><input type="text" name="$id:location" style="width:100px;" };
		print qq{value="$r{location}"></td>\n};
	    }
	    
	    # Course Enrollments
	    print qq{<td>$inforef->{enrollments} $inforef->{error}</td></tr>\n};
	
	} # end of Course Loop



    # Grades
    } elsif ( $prisort eq 'grade' ) {

	my $sort = 'sequence';
	if ( $secsort eq 'name' ) { $sort = 'description'; }
 
	$sth = $dbh->prepare("select * from subject where grade = ? order by $sort");
	    
	foreach my $grade ( @grades ) {
	    
	    $sth->execute( $grade );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $first = 1;
	    
	    while ( my $ref = $sth->fetchrow_hashref ) {
		my %r = %$ref;
		my $id = $r{id};
		my $inforef = checkCourse($ref);

		# Table Heading
		if ( $first ) {  # in this grade;
		    print qq{<tr><td style="font-size:120%;font-weight:bold;text-align:left;padding:0.4em;" };
		    print qq{colspan="10">$lex{Grade} $grade</td></tr>\n};
		    print qq{<tr><th>$lex{Course}</th><th>$lex{Code}</th><th>$lex{Section}</th>};
		    print qq{<th>$lex{Grade}</th><th>$lex{Seq}</th><th>$lex{Teacher}</th>};
		    print qq{<th>$lex{'Start Term'}</th><th>$lex{'End Term'}</th><th>$lex{Location}</th>};
		    print qq{<th>$lex{Enrolment}</th></tr>\n};
		    $first = 0;
		}

		print qq{<tr><td class="bla">$r{description}</td><td class="cn">$r{subjcode}</td>};
		print qq{<td class="cn">$r{section}</td><td class="cn">$r{grade}</td>\n};
	
		# Sequence
		print qq{<td><input type="text" name="$id:sequence" value="$r{sequence}" size="4"></td>\n};

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

		
		# Terms
		print qq{<td class="cn"><select name="$id:sterm"><option>$r{startrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{startrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};
	    
		print qq{<td class="cn"><select name="$id:eterm"><option>$r{endrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{endrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};


		# Location
		if ( @locations ) {
		    print qq{<td><select name="$id:location"><option>$r{location}</option>};
		    foreach my $loc ( @locations ) {
			print qq{<option>$loc</option>\n};
		    }
		    print qq{<option></option></select></td>\n};
	    
		} else {
		    print qq{<td><input type="text" name="$id:location" size="12" value="$r{location}"></td>\n};
		}
	    
		# Course Enrollments
		print qq{<td>$inforef->{enrollments} $inforef->{error}</td></tr>\n};

	    } # end of this grades records;
	    
	} # end of grades loop
	

    
    # Teachers
    } elsif ( $prisort eq 'teacher' ) {

	my $sort = 'grade, description';  # only sort teachers by description, so far
	# if ( $secsort eq 'name' ) { $sort = 'description'; }
 
	$sth = $dbh->prepare("select * from subject where teacher = ? order by $sort");

	foreach my $key ( sort keys %teachers ) {

	    my $userid = $teachers{$key};
	    my $tname = $teachername{$userid};
	    
	    $sth->execute( $userid );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $first = 1;
	    
	    while ( my $ref = $sth->fetchrow_hashref ) {
		my %r = %$ref;
		my $id = $r{id};
		my $inforef = checkCourse($ref);

		# Table Heading
		if ( $first ) {  # in this grade;
		    print qq{<tr><td style="font-size:120%;font-weight:bold;text-align:left;padding:0.4em;" };
		    print qq{colspan="10">$lex{Teacher} $tname</td></tr>\n};
		    print qq{<tr><th>$lex{Course}</th><th>$lex{Code}</th><th>$lex{Section}</th>};
		    print qq{<th>$lex{Grade}</th><th>$lex{Seq}</th><th>$lex{Teacher}</th>};
		    print qq{<th>$lex{'Start Term'}</th><th>$lex{'End Term'}</th><th>$lex{Location}</th>};
		    print qq{<th>$lex{Enrolment}</th></tr>\n};
		    $first = 0;
		}

		print qq{<tr><td class="bla">$r{description}</td><td class="cn">$r{subjcode}</td>};
		print qq{<td class="cn">$r{section}</td><td class="cn">$r{grade}</td>\n};
	
		# Sequence
		print qq{<td><input type="text" name="$id:sequence" value="$r{sequence}" size="4"></td>\n};

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

	
		# Terms
		print qq{<td class="cn"><select name="$id:sterm"><option>$r{startrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{startrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};
	    
		print qq{<td class="cn"><select name="$id:eterm"><option>$r{endrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{endrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};

		
		# Location
		if ( @locations ) {
		    print qq{<td><select name="$id:location"><option>$r{location}</option>};
		    foreach my $loc ( @locations ) {
			print qq{<option>$loc</option>\n};
		    }
		    print qq{<option></option></select></td>\n};
	    
		} else {
		    print qq{<td><input type="text" name="$id:location" size="12" value="$r{location}"></td>\n};
		}
	    
		# Course Enrollments
		print qq{<td>$inforef->{enrollments} $inforef->{error}</td></tr>\n};

	    } # end of this teachers records;
	    
	} # end of teacher loop


    } elsif ( $prisort eq 'term' ) {

	$sth = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject 
          order by startrptperiod, endrptperiod");
	$sth->execute;
	if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }

	
	my $sort = 'grade, description';
	if ( $secsort eq 'name' ) { $sort = 'description, grade'; }

	my $sth1 = $dbh->prepare("select * from subject where startrptperiod = ? and endrptperiod = ? 
           order by $sort");

	while ( my ($startterm, $endterm ) = $sth->fetchrow ) {

	    $sth1->execute( $startterm, $endterm );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    my $first = 1;
	    
	    while ( my $ref = $sth1->fetchrow_hashref ) {
		my %r = %$ref;
		my $id = $r{id};
		my $inforef = checkCourse($ref);

		# Table Heading
		if ( $first ) {  # in this grade;
		    print qq{<tr><td style="font-size:120%;font-weight:bold;text-align:left;padding:0.4em;" };
		    print qq{colspan="10">$lex{Terms} $startterm - $endterm</td></tr>\n};
		    print qq{<tr><th>$lex{Course}</th><th>$lex{Code}</th><th>$lex{Section}</th>};
		    print qq{<th>$lex{Grade}</th><th>$lex{Seq}</th><th>$lex{Teacher}</th>};
		    print qq{<th>$lex{'Start Term'}</th><th>$lex{'End Term'}</th><th>$lex{Location}</th>};
		    print qq{<th>$lex{Enrolment}</th></tr>\n};
		    $first = 0;
		}

		print qq{<tr><td class="bla">$r{description}</td><td class="cn">$r{subjcode}</td>};
		print qq{<td class="cn">$r{section}</td><td class="cn">$r{grade}</td>\n};
	
		# Sequence
		print qq{<td><input type="text" name="$id:sequence" value="$r{sequence}" size="4"></td>\n};

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

		
		# Terms
		print qq{<td class="cn"><select name="$id:sterm"><option>$r{startrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{startrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};
	    
		print qq{<td class="cn"><select name="$id:eterm"><option>$r{endrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{endrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};


		# Location
		if ( @locations ) {
		    print qq{<td><select name="$id:location"><option>$r{location}</option>};
		    foreach my $loc ( @locations ) {
			print qq{<option>$loc</option>\n};
		    }
		    print qq{<option></option></select></td>\n};
	    
		} else {
		    print qq{<td><input type="text" name="$id:location" size="12" value="$r{location}"></td>\n};
		}
	    
		# Course Enrollments
		print qq{<td>$inforef->{enrollments} $inforef->{error}</td></tr>\n};

	    } # end of this terms records;
	    
	} # end of term loop
	

    } elsif ( $prisort eq 'homeroom' ) {

	my %foundcourses; # keep track of them all so only show once.
	
	my $sort = 'sequence';
	if ( $secsort eq 'name' ) { $sort = 'description'; }
 
	$sth = $dbh->prepare("select * from subject where subjsec = ?");
	    
	foreach my $homeroom ( @homerooms ) {

	    # find the students in that room, and their courses.
	    my $sth1 = $dbh->prepare("select description, grade, sequence from subject where subjsec = ?");
	    
	    my %courses;
	    $sth2 = $dbh->prepare("select e.subjcode from eval e, studentall s 
               where s.homeroom = ? and e.studnum = s.studnum ");
	    $sth2->execute( $homeroom );
	    if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
	    while ( my $subjsec = $sth2->fetchrow ) {

		if ( not $coursesfound{$subjsec} ) { # add it
		    $coursesfound{$subjsec} = 1;
		} else { # if already done, skip it
		    next;
		}

		# Get course desc, grade;
		$sth1->execute( $subjsec );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my ($desc, $grade, $sequence ) = $sth1->fetchrow;

		if ( $secsort eq 'name' ) {
		    $courses{"$desc$grade"} = $subjsec;
		} else { # by sequence
		    $courses{"$grade$sequence"} = $subjsec;
		}
	    }

	    if ( not %courses ) {
		print qq{<div>No Courses found for homeroom $homeroom</div>\n};
		next;
	    }

	    # Get Courses in this homeroom	    
	    my $first = 1;
	    foreach my $key ( sort keys %courses ) {
		my $subjsec = $courses{$key};

		# Get the record
		$sth->execute( $subjsec );
		if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
		my $ref = $sth->fetchrow_hashref;
		
		my %r = %$ref;
		my $id = $r{id};
		my $inforef = checkCourse($ref);

		# Table Heading
		if ( $first ) {  # in this grade;
		    print qq{<tr><td style="font-size:120%;font-weight:bold;text-align:left;padding:0.4em;"};
		    print qq{colspan="10">$lex{Homeroom} $homeroom</td></tr>\n};
		    print qq{<tr><th>$lex{Course}</th><th>$lex{Code}</th><th>$lex{Section}</th>};
		    print qq{<th>$lex{Grade}</th><th>$lex{Seq}</th><th>$lex{Teacher}</th>};
		    print qq{<th>$lex{'Start Term'}</th><th>$lex{'End Term'}</th>};
		    print qq{<th>$lex{Location}</th><th>$lex{Enrolment}</th></tr>\n};
		    $first = 0;
		}

		print qq{<tr><td class="bla">$r{description}</td><td class="cn">$r{subjcode}</td>};
		print qq{<td class="cn">$r{section}</td><td class="cn">$r{grade}</td>\n};
	
		# Sequence
		print qq{<td><input type="text" name="$id:sequence" value="$r{sequence}" size="4"></td>\n};

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

		
		# Terms
		print qq{<td class="cn"><select name="$id:sterm"><option>$r{startrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{startrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};
	    
		print qq{<td class="cn"><select name="$id:eterm"><option>$r{endrptperiod}</option>\n};
		foreach my $trm ( 1..$maxterms ) {
		    if ( $trm == $r{endrptperiod} ) { next; }
		    print qq{<option>$trm</option>};
		}
		print qq{</select></td>\n};


		# Location
		if ( @locations ) {
		    print qq{<td><select name="$id:location"><option>$r{location}</option>};
		    foreach my $loc ( @locations ) {
			print qq{<option>$loc</option>\n};
		    }
		    print qq{<option></option></select></td>\n};
	    
		} else {
		    print qq{<td><input type="text" name="$id:location" size="12" value="$r{location}"></td>\n};
		}
	    
		# Course Enrollments
		print qq{<td>$inforef->{enrollments} $inforef->{error}</td></tr>\n};

	    } # end of this homeroom;
	    
	} # end of homeroom loop

    } 

    print qq{</table>\n};

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

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

    exit;

}


#---------------
sub updateRecords {
#---------------

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

    foreach my $key ( sort keys %arr ) {

	my ($id, $fld ) = split(/:/, $key);

	# Check for start/end term errors:
	if ( $fld eq 'sterm' ) { #
	    if (( $arr{$key} > $maxterms ) or ( $arr{$key} < 0 )) {
		print qq{<h3>Error: $fld - $arr{$key}</h3>\n};
		print qq{</body></html>\n};
		exit;
	    }

	} elsif ( $fld eq 'eterm' ) { #
	    my $stermkey = $id. ':sterm';
	    my $stermval = $arr{$stermkey};
	    if (( $arr{$key} > $maxterms ) or ( $arr{$key} < 0 ) or ( $arr{$key} < $stermval )) {
		print "<h3>Error: $fld - $arr{$key}</h3>\n";
		print "</body></html>\n";
		exit;
	    }
	}

	if ( $fld eq 'eterm' ) { $fld = 'endrptperiod'; }
	if ( $fld eq 'sterm' ) { $fld = 'startrptperiod'; }


	my $sth = $dbh->prepare("update subject set $fld = ? where id = ?");
	$sth->execute( $arr{$key}, $id );
	if ($DBI::errstr ) { print "$DBI::errstr;<br>\n"; }

    }

    if ( not $DBI::errstr ) {
	print qq{<h3>$lex{'Record(s) Updated'}</h3></p>\n};

    } else {
	print qq{<h3>$lex{Error}: $DBI::errstr<br>\n};
	print qq{$lex{Contact} $adminname (<a href="mailto:$adminemail">$adminemail</a> )</h3>\n};
    }

    print qq{<p>[ <a href="$reppage">$lex{'Report Card'}</a> |\n};
    print qq{<a href="$self">$title</a> ]</p>\n};
    print qq{</body></html>\n};

    exit;

}


#--------------
sub checkCourse {
#--------------
    # Passed a ref to the course information, check and return course enrollments, and error msg

    my $ref = shift;

    my %r = %$ref;
    my $subjsec = $r{subjsec};

    my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
    my $sth3 = $dbh->prepare("select distinct term from eval where subjcode = ? 
      and term != '' and term is not NULL");

    my %info;
    
    # Get count of students enrolled in this course, and look for errors in course enrollments
    $sth1->execute( $subjsec );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my $count = $sth1->fetchrow;

    my $terms = ($r{endrptperiod} - $r{startrptperiod}) + 1;
    if ($terms < 1) { $terms = 1; }
    my $enrollments = round( $count / $terms, 2);

    $info{enrollments} = "$enrollments $lex{students}";
#    if ($count % $terms != 0 ) { # error
#	$enrollments = qq{$enrollments: <span style="color:red;font-weight:bold;">Error</span>};
#    }

	
    # Check that start term -> end terms match those in course enrollments.
    my (%crsenrol,%crsmst, %master);
    $sth3->execute( $subjsec );
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while (my $trm = $sth3->fetchrow ) {
	$crsenrol{$trm} = 1;
    }
    foreach my $trm ($r{startrptperiod}..$r{endrptperiod}) {
	$crsmst{$trm} = 1;
    }

    $info{enrol} = join(',', sort keys %crsenrol);
    $info{mst} = join(',', sort keys %crsmst);
    
    %master = (%crsenrol,%crsmst); # join them
    foreach my $t ( keys %master ) {
	if ( $crsenrol{$t} and $crsmst{$t} ) { 
	    delete $crsenrol{$t};
	    delete $crsmst{$t};
	}
    }
    # both should now be empty;

    my $errormsg;
    if ( %crsenrol ) {
	$errormsg = " Extra Course Enrollments - Term ";
	my $terms = join(',', sort keys %crsenrol );
	$errormsg .= $terms;
    }
    if ( %crsmst ) {
	$errormsg .= " Missing Course Enrollments - Term ";
	my $terms = join(',', sort keys %crsmst );
	$errormsg .= $terms;
    }

    if ( $enrollments and $errormsg ) {
	$errormsg = qq{<span style="color:red;font-weight:bold;">$errormsg</span>};
	
    } elsif ( not $enrollments ) {
	$errormsg = qq{<span style="color:blue;font-weight:bold;">$lex{'No Enrollments'}</span>};
    }

    $info{error} = $errormsg;

    return \%info;

}
