#! /usr/bin/perl
# Copyright 2001-2023 Leslie Richardson
# This file is part of Open Admin for Schools.
# Check the school course enrollments in OA VS MSS using mssCurrCourse table.
my $self = 'mssCheckCourses.pl';
use DBI;
use CGI;
use Time::JulianDay;
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
my $q = CGI->new;
my %arr = $q->Vars;
print $q->header( -charset, $charset );
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
my @tim = localtime(time);
my $year = $tim[5] + 1900;
$tim[4]++;
for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}}
my $currdate = "$year-$tim[4]-$tim[3]";
# my $currtime = "$tim[2]:$tim[1]:$tim[0]";
# HTML Header
my $title = 'Compare Course Enrollment/ Marks in MSS vs OA';
print qq{$doctype$title\n};
print qq{\n};
print qq{$chartype\n};
print qq{[ Main |\n};
print qq{Export ]\n};
print qq{
$title
};
checkCourses();
#---------------
sub checkCourses {
#---------------
#foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key} \n}; }
# passed term
# enddate, endterm
# Get the current grade 12 term based on the current date;
my $currterm; # set below
my $track = $g_MTrackTermType{12};
my $currjd = julian_day(split('-', $currdate));
foreach my $term ( sort keys %{ $g_MTrackTerm{$track} } ) {
my $startjd = julian_day( split('-', $g_MTrackTerm{$track}{$term}{'start'} ));
my $endjd = julian_day(split('-', $g_MTrackTerm{$track}{$term}{'end'} ));
if ( $currjd >= $startjd and $currjd <= $endjd ) { # we have the term.
$currterm = $term;
last;
}
};
if ( $currterm ) {
print qq{
Current Grade 12 Term - $currterm
\n};
}
# Get courses for this current term with enrollments.
my %courses;
# get Enrollment
my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
my $sth = $dbh->prepare("select * from subject where
grade = 10 or grade = 11 or grade = 12 and
endrptperiod >= ? and startrptperiod <= ?
order by grade, startrptperiod, endrptperiod, subjsec");
$sth->execute( $currterm, $currterm );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $first = 1;
while ( my $ref = $sth->fetchrow_hashref ) {
my %r = %$ref;
# if ( $first ) {
# print qq{
\n};
# print qq{
course
Subjsec (Code)
Grade
Terms
};
# print qq{
Enrol
\n};
# $first = 0;
# }
# check for enrollment.
my $trms = $r{endrptperiod} - $r{startrptperiod} + 1;
$sth1->execute( $r{subjsec} );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $count = $sth1->fetchrow;
if ( not $count ) { next; }
$courses{ $r{subjcode} }{ $r{subjsec} } = 1; # code first, subjsec second
# my $enrol;
# if ($trms > 0 ) {
# $enrol = $count / $trms;
# }
# print qq{
$r{description}
$r{subjsec} ($r{subjcode})
};
# print qq{
$r{grade}
$r{startrptperiod}-$r{endrptperiod}
};
# print qq{
$enrol
\n};
}
# Get Students enrolled in these current term courses.
my %crsStud;
my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ?");
foreach my $subjcode ( sort keys %courses ) {
foreach my $subjsec ( sort keys %{ $courses{$subjcode}} ) {
$sth->execute($subjsec); # Note: subjsec is really the value in the eval table
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth->fetchrow ) {
$crsStud{$subjcode}{$studnum} = 1; # list to track who is enrolled in a subject.
}
}
}
# find provnum, local num
my $sth1 = $dbh->prepare("select studnum, provnum from studentall where mssid = ?");
my $sth2 = $dbh->prepare("select id from eval where studnum = ? and subjcode = ?");
# Get Student Name
my $sth3 = $dbh->prepare("select lastname, firstname, grade from studentall
where studnum = ?");
# Get Course Name
my $sth4 = $dbh->prepare("select title from sasked_courses where code = ?");
# load each record from mss_currcourse
my $sth = $dbh->prepare("select mssid, coursecode from mss_currcourse order by coursecode");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $first = 1;
while ( my ($mssid,$coursecode) = $sth->fetchrow ) {
# Find the student provnum and studnum (local number)
$sth1->execute($mssid);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my ($studnum,$provnum) = $sth1->fetchrow;
if ( not $studnum ) {
print qq{
Student not found for MSSID: $mssid
\n};
next;
}
# Find his/her enrollment, loop over each course section if more than one.
my $enrolflag;
foreach my $subjsec ( sort keys %{ $courses{$coursecode} } ) {
$sth2->execute($studnum, $subjsec);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $eid = $sth2->fetchrow_hashref;
if ( $eid ) {
$enrolflag = 1;
last;
}
}
if ( not $enrolflag ) { # no course found in OA.
if ( $first ) {
print qq{
\n};
print qq{
};
print qq{Missing Course Enrollments in OA
\n};
print qq{
Course
Course Code
Student
\n};
$first = 0;
}
# Get Student Name
$sth3->execute($studnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname,$grade) = $sth3->fetchrow;
# Get Course Name
$sth4->execute($coursecode);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $title = $sth4->fetchrow;
print qq{
$title
$coursecode
};
print qq{
$firstname $lastname ($grade)
\n};
} else {
# They are enrolled. Delete from enrolled students hash. Those left are not in MSS.
delete $crsStud{$coursecode}{$studnum};
}
} # end of student loop.
if ( not $first ) {
print qq{
\n};
} else {
print qq{
No MSS Courses Found for Grades 10,11,12
\n};
print qq{\n};
exit;
}
# Print Students not in MSS but are in OA.
if ( %crsStud ) { # setup table.
print qq{
\n};
print qq{
};
print qq{Missing Course Enrollments in MSS
\n};
print qq{
Course
Course Code
Student
\n};
}
foreach my $subjcode ( sort keys %crsStud ) {
foreach my $studnum ( sort keys %{ $crsStud{$subjcode}} ) {
# Get Student Name
$sth3->execute($studnum);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname,$grade) = $sth3->fetchrow;
# Get Course Name
$sth4->execute($subjcode);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $title = $sth4->fetchrow;
print qq{