};
# 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{$lex{Course} $lex{Code} $lex{Section} };
print qq{$lex{Grade} Sm Desc $lex{Seq} $lex{Teacher} };
print qq{$lex{'Start Term'} $lex{'End Term'} $lex{Location} };
print qq{$lex{Enrollment} \n};
$first = 0;
}
print qq{$r{description} $r{subjcode} };
# Section / Hidden Subjcode
print qq{ \n};
print qq{ \n};
# Grade
print qq{$r{grade} \n};
# Small Description
print qq{ \n};
# Sequence
print qq{ \n};
# Teacher
print qq{};
if ( not $teachername{$r{teacher}} ) { $teachername{$r{teacher}} = $r{teacher}; }
print qq{$teachername{$r{teacher}} \n};
foreach my $key ( sort keys %teachers ) {
my $userid = $teachers{$key};
print qq{$teachername{$userid} };
}
print qq{\n \n};
# Terms
print qq{$r{startrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{startrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
print qq{$r{endrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{endrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
# Location
if ( @locations ) {
print qq{$r{location} };
foreach my $loc ( @locations ) {
print qq{$loc \n};
}
print qq{ \n};
} else {
print qq{ \n};
}
# Course Enrollments
print qq{$inforef->{enrollments} $inforef->{error} \n};
} # end of Course Loop
# Grades
} elsif ( $prisort eq 'grade' ) {
my $sort = 'subjcode';
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{$lex{Grade} $grade \n};
print qq{$lex{Course} $lex{Code} $lex{Section} };
print qq{$lex{Grade} Sm Desc $lex{Seq} };
print qq{$lex{Teacher} };
print qq{$lex{'Start Term'} $lex{'End Term'} };
print qq{$lex{Location} };
print qq{$lex{Enrolment} \n};
$first = 0;
}
print qq{$r{description} $r{subjcode} };
# Section / Hidden Subjcode
print qq{ \n};
print qq{ \n};
# Grade
print qq{$r{grade} \n};
# Small Description
print qq{ \n};
# Sequence
print qq{ \n};
# Teacher
print qq{};
print qq{$teachername{$r{teacher}} \n};
foreach my $key ( sort keys %teachers ) {
my $userid = $teachers{$key};
print qq{$teachername{$userid} };
}
print qq{\n \n};
# Terms
print qq{$r{startrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{startrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
print qq{$r{endrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{endrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
# Location
if ( @locations ) {
print qq{$r{location} };
foreach my $loc ( @locations ) {
print qq{$loc \n};
}
print qq{ \n};
} else {
print qq{ \n};
}
# Course Enrollments
print qq{$inforef->{enrollments} $inforef->{error} \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{$lex{Teacher} $tname \n};
print qq{$lex{Course} $lex{Code} $lex{Section} };
print qq{$lex{Grade} Sm Desc $lex{Seq} };
print qq{$lex{Teacher} };
print qq{$lex{'Start Term'} $lex{'End Term'} };
print qq{$lex{Location} };
print qq{$lex{Enrolment} \n};
$first = 0;
}
print qq{$r{description} $r{subjcode} };
# Section / Hidden Subjcode
print qq{ };
print qq{ \n};
# Grade
print qq{$r{grade} \n};
# Small Description
print qq{ \n};
# Sequence
print qq{ \n};
# Teacher
print qq{};
print qq{$teachername{$r{teacher}} \n};
foreach my $key ( sort keys %teachers ) {
print qq{$teachername{$teachers{$key}} };
}
print qq{\n \n};
# Terms
print qq{$r{startrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{startrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
print qq{$r{endrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{endrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
# Location
if ( @locations ) {
print qq{$r{location} };
foreach my $loc ( @locations ) {
print qq{$loc \n};
}
print qq{ \n};
} else {
print qq{ \n};
}
# Course Enrollments
print qq{$inforef->{enrollments} $inforef->{error} \n};
} # end of this teachers records;
} # end of teacher loop
# Terms
} 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{$lex{Terms} $startterm - $endterm \n};
print qq{$lex{Course} $lex{Code} $lex{Section} };
print qq{$lex{Grade} Sm Desc $lex{Seq} };
print qq{$lex{Teacher} };
print qq{$lex{'Start Term'} $lex{'End Term'} };
print qq{$lex{Location} };
print qq{$lex{Enrolment} \n};
$first = 0;
}
print qq{$r{description} $r{subjcode} };
# Section / Hidden subjcode
print qq{ \n};
print qq{ \n};
# Grade
print qq{$r{grade} \n};
# Small Description
print qq{ \n};
# Sequence
print qq{ \n};
# Teacher
print qq{};
print qq{$teachername{$r{teacher}} \n};
foreach my $key ( sort keys %teachers ) {
print qq{$teachername{$teachers{$key}} };
}
print qq{\n \n};
# Terms
print qq{$r{startrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{startrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
print qq{$r{endrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{endrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
# Location
if ( @locations ) {
print qq{$r{location} };
foreach my $loc ( @locations ) {
print qq{$loc \n};
}
print qq{ \n};
} else {
print qq{ \n};
}
# Course Enrollments
print qq{$inforef->{enrollments} $inforef->{error} \n};
} # end of this terms records;
} # end of term loop
# Homeroom
} elsif ( $prisort eq 'homeroom' ) {
my %foundcourses; # keep track of them all so only show once.
my $sort = 'subjcode';
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 distinct 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 ) {
=head
# Check that all student homerooms are unique for this course.....
my $sth4 = $dbh->prepare("select distinct studnum from eval where subjcode = ?");
$sth4->execute( $subjsec );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth4->fetchrow ) {
my $sth5 = $dbh->prepare("select firstname, lastname, homeroom from studentall
where studnum = ?");
$sth5->execute( $studnum );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my ($firstname, $lastname, $homeroom) = $sth5->fetchrow;
print qq{$subjsec - $firstname $lastname $homeroom
\n};
}
=cut
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;
# pad sequence to 4 digits.
my $newseq = sprintf "%04d", $sequence;
if ( $secsort eq 'name' ) {
$courses{"$desc$grade"} = $subjsec;
} else { # by sequence
$courses{"$grade$newseq$subjsec"} = $subjsec;
}
}
if ( not %courses ) {
print qq{No Courses found for homeroom $homeroom
\n};
next;
}
# Display 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{$lex{Homeroom} $homeroom \n};
print qq{$lex{Course} $lex{Code} $lex{Section} };
print qq{$lex{Grade} $lex{Seq} $lex{Teacher} };
print qq{$lex{'Start Term'} $lex{'End Term'} };
print qq{$lex{Location} $lex{Enrolment} \n};
$first = 0;
}
print qq{$r{description} $r{subjcode} };
# Section / Hidden subjcode
print qq{ \n};
print qq{ \n};
# Grade
print qq{$r{grade} \n};
# Sequence
print qq{ \n};
# Teacher
print qq{};
print qq{$teachername{$r{teacher}} \n};
foreach my $key ( sort keys %teachers ) {
my $userid = $teachers{$key};
print qq{$teachername{$userid} };
}
print qq{\n \n};
# Terms
print qq{$r{startrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{startrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
print qq{$r{endrptperiod} \n};
foreach my $trm ( 1..$maxterms ) {
if ( $trm == $r{endrptperiod} ) { next; }
print qq{$trm };
}
print qq{ \n};
# Location
if ( @locations ) {
print qq{$r{location} };
foreach my $loc ( @locations ) {
print qq{$loc \n};
}
print qq{ \n};
} else {
print qq{ \n};
}
# Course Enrollments
print qq{$inforef->{enrollments} $inforef->{error} \n};
} # end of this homeroom;
} # end of homeroom loop
}
print qq{
\n};
# Submit Button
print qq{