#!/usr/bin/perl
# Copyright 2001-2024 Leslie Richardson
# This file is part of Open Admin for Schools.
# Load current courses and find teacher and student loading.
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}. ": $@ \n";
die $lex{Error}. ": $@\n";
}
my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
my $title = "Current Course Offerings - Teacher and Student";
print qq{$doctype\n$title\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{[ $lex{Main} |\n};
print qq{ Report Card ]\n};
print qq{
$title
\n};
# 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;
# 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 teachers of all courses from grade 10 to 12; used to check backings
my %courseteacher;
my $sth = $dbh->prepare("select subjsec, teacher from subject where grade = 10 or grade = 11 or grade = 12");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while ( my ($subjsec, $teacher ) = $sth->fetchrow ) {
$courseteacher{$subjsec} = $teacher;
}
# Check for enrollments and student grades same as in course master
# Do we have enrollments
my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
# Get students in course, in prep for grade check.
my $sth2 = $dbh->prepare("select distinct studnum from eval where subjcode = ?");
# Get grade of the student to see if same as course setting.
my $sth3 = $dbh->prepare("select grade from studentall where studnum = ?");
# Course Info - when no enrollments
my $sth4 = $dbh->prepare("select * from subject where subjsec = ?");
# Get all current courses
my $sth = $dbh->prepare("select * from subject");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my %crs; # course hash
my %altgrades; # alternate grades for a course; (ie. Law 30, grade 12 course offered to grade 10's)
# $altgrades{subjsec}{grades} = 1;
while ( $ref = $sth->fetchrow_hashref ) {
my %c = %$ref; # current record
my $subjsec = $c{subjsec}; # course code and section ie. 4017-1
if ( $subjsec =~ m/comment/ ) { next; } # skip any comment courses
if ( $c{grade} < 10 ) { next; } # skip lower grades
# Check for enrollments
$sth1->execute($subjsec);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $enrolcount = $sth1->fetchrow;
if ( not $enrolcount ) {
$sth4->execute($subjsec);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $cref = $sth4->fetchrow_hashref;
my %c = %$cref; # %c is the course fields
my $objcount = 0; # count objectives
for my $idx (1..20) {
my $fld = qq{q.$idx};
if ( $c{fld} ) { # something in the objective
$objcount++;
}
}
my ($code,$section) = split('-', $subjsec);
print qq{
No Course Enrollments for $saskedname{$code} ($subjsec)};
print qq{ Objectives:$objcount
\n};
next;
}
# Check for grade offerings.
$sth2->execute($subjsec);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while (my $studnum = $sth2->fetchrow ) { # get student in this course
# Get grade
$sth3->execute($studnum);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $grade = $sth3->fetchrow;
if ( $grade ne $c{grade} ) {
$altgrades{$subjsec}{$grade} = 1;
# print qq{
Alternate Grade for course $subjsec ($c{grade}) - $grade
\n};
}
}
# Add to course (crs) hash
$crs{ $c{startrptperiod} }{ $c{endrptperiod} }{ "$subjsec:$c{teacher}" } = 1;
} # end course loop loading up %crs hash.
# check alt grades.
if ( %altgrades ) {
foreach my $subjsec ( sort keys %altgrades ) {
foreach my $gr ( sort keys %{ $altgrades{$subjsec} } ) {
# print qq{
Alt Grades $subjsec - $gr
\n};
}
}
}
# Find largest ending term so we can loop from term 1.
my $sth = $dbh->prepare("select max(endrptperiod) from subject");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $maxendterm = $sth->fetchrow;
#print qq{
Max End Term is: $maxendterm
\n};
my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
my $sth2 = $dbh->prepare("select count(*) from schedat where subjsec = ? and term = ?");
my $sth3 = $dbh->prepare("select distinct e.studnum, s.grade from eval e, studentall s where
subjcode = ? and e.studnum = s.studnum");
foreach my $term (1..$maxendterm ) {
print qq{
\n};
print qq{
};
print qq{Term $term };
print qq{Bold Grade is Course Grade, Others are actual enrolled grades
\n};
print qq{
Grades
Course
Teacher
Enrolled
\n};
my $bref = createBacking($term);
%back = %$bref;
# %back{subjsec} = totalenrol (all backed courses in this term)
# TEST DATA
# print qq{
BACK Term $term \n};
# foreach my $subjsec ( sort keys %back ) {
# my ($code, $section) = split('-', $subjsec);
# print qq{CRS $saskedname{$code} ($subjsec) Enrolled:$back{$subjsec} \n};
# }
# print qq{
\n};
my (%load, %offr, @sort, %sort); # teacher load, course offerings
foreach my $sterm ( sort keys %crs ) {
foreach my $eterm ( sort keys %{ $crs{$sterm} } ) {
# now check if current term is within these parameters.
if ( $term >= $sterm and $term <= $eterm ) { # have a course here.
my $ref = $crs{$sterm}{$eterm};
my %val = %$ref;
foreach my $val ( sort keys %val ) {
my ($subjsec,$teacher) = split(':', $val);
my ($code, $section) = split('-', $subjsec);
$sort{"$saskedgrade{$code}$saskedname{$code}$subjsec"} = $val;
# Get course enrollment total
# $sth1->execute($subjsec);
# if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
# my $enrolcount = $sth1->fetchrow;
# print qq{
\n};
foreach my $teacher ( sort keys %load ) {
my $pcount; # period count
print qq{
$teachername{$teacher} ($teacher) \n};
my @crs = @{ $load{$teacher} };
foreach my $subjsec ( @crs ) {
my ($code, $section) = split('-', $subjsec);
# Get periods for this course
$sth2->execute($subjsec, $term);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $periods = $sth2->fetchrow;
print qq{$saskedname{$code} $periodsP\n};
if ( $back{$subjsec} ) {
print qq{Backed $back{$subjsec} Total Enrolled};
}
print qq{ \n};
$pcount += $periods;
}
# print qq{Total Periods: $pcount
\n};
#print qq{
@crs
\n};
print qq{\n};
}
print qq{
\n};
if ( $term % 2 == 0 ) {
print qq{\n};
}
} # end of terms loop
#--------------
sub createBacking { # create a backing hash %back{subjsec} = totalenrollment (all courses together)
#--------------
my $term = @_[0];
my %backed;
# get the courses this term
my $sth = $dbh->prepare("select distinct subjsec from schedat where term = ?");
# get the days, periods for courses this term
my $sth1 = $dbh->prepare("select day,period from schedat where subjsec = ? and term = ?");
# loop over the day, period, term
my $sth2 = $dbh->prepare("select subjsec from schedat where term = ? and day = ? and period = ?");
# get course enrollment
my $sth3 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
$sth->execute($term);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while ( my $subjsec = $sth->fetchrow ) { # loop over all courses this term
$sth1->execute($subjsec, $term);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while ( my ($day, $period) = $sth1->fetchrow ) { # loop over day, period
# Loop over all courses for this period (day, term) looking for common teacher (ie. backed)
my %tch; # tch{userid} = @subjsec
$sth2->execute($term, $day, $period);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
while ( my $subjsec = $sth2->fetchrow ) {
my $teacher = $courseteacher{$subjsec};
push @{$tch{$teacher}}, $subjsec;
}
foreach my $teacher ( keys %tch ) {
# check number of course entries for today
my $ccount = @{ $tch{$teacher}};
if ( $ccount > 1 ) { # backings
my $totalenrol;
foreach my $s ( @{ $tch{$teacher}} ) {
$sth3->execute($s);
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;}
my $enrol = $sth3->fetchrow;
$totalenrol += $enrol;
}
foreach my $s ( @{ $tch{$teacher}} ) { # populate for all courses backed.
$backed{$s} = $totalenrol;
}
# print qq{
CHK:Term $term SUB $subjsec CNT:$ccount Day:$day Period:$period
\n};
}
# find total enrollment for this backed period
my $totalenrol;
}
}
} # end of subjsec loop
my $bref = \%backed;
return $bref; # ref to the hash.
}