#! /usr/bin/perl # Copyright 2001-2023 Leslie Richardson # This file is part of Open Admin for Schools. # Check the school marks from current or previous years and then compare the # marks with student values stored in the sasked completed marks table # (which is assumed to be up to date) # Still using the completed marks table, and mss export imported into there. my %lex = ('Main' => 'Main', 'Continue' => 'Continue', 'Error' => 'Error', 'Select' => 'Select', 'School Year' => 'School Year', ); my $self = 'checkMarksMss.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 ); # Possibly Load Remote database, remote values in configuration system my $dbhr; if ( $arr{db} and $arr{db} ne 'curr' ) { # from start page my $db = $arr{db}; my $dsn = "DBI:mysql:database=$db;host=$remotehost"; $dbhr = DBI->connect($dsn,$remoteuser,$remotepassword); } my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; if ( not defined $dbhr ) { $dbhr = $dbh; } # we are using the current database for marks. # HTML Header my $title = 'Check for Missing MSS Marks - Current/Previous Years'; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ Main | Report Card ]\n}; print qq{

$title

}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; checkTerm(); } #---------------- sub showStartPage { #---------------- # Get remote databases my $remotedbase = 'information_schema'; my $dsnr = "DBI:mysql:database=$remotedbase;host=$remotehost"; my $dbhr = DBI->connect($dsnr,$remoteuser,$remotepassword); my $sth = $dbhr->prepare("select distinct table_schema from TABLES order by table_schema"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my %remotedb; while ( my $db = $sth->fetchrow ) { if ( $db eq 'mysql' or $db eq 'information_schema' ) { next; } # print qq{DB:$db
\n}; if ( $db =~ m/$dbase/ ) { # print qq{Match! $dbase - $db
\n}; $remotedb{$db} = 1; } } print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; foreach my $db ( sort keys %remotedb ) { my $temp = $db; $temp =~ s/$dbase//; # strip database name; my $year = $temp - 1; # previous year; $year = qq{$year-$temp}; # now in 2018-2019 format print qq{\n}; } # Current School Year print qq{\n}; print qq{\n}; # Check next Page # print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Select} $lex{'School Year'}
$year ($db)
Current Year

}; # print qq{$lex{'Check Next Page'} (Grades)
Term
\n}; print qq{\n}; exit; } # end of New Start Page #------------- sub checkTerm { # Check marks in current term agains MSS/Aspen #------------- #foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # require "$globdir/global.conf" or die "Cannot open global.conf!\n"; # open database connection to central # my $db = 'central'; # my $dsn1 = "DBI:$dbtype:dbname=$db"; # my $dbh1 = DBI->connect($dsn1,$guser,$gpassword); # $dbh->{mysql_enable_utf8} = 1; if ( not $arr{db} ) { print qq{

No Database Selected!

\n}; print qq{\n}; exit; } # Load Main admin configuration ('admin') from remote/local/database my $sth = $dbhr->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}: $@
\n"; die "$lex{Error}: $@\n"; } } my $schoolstartjd = julian_day( split('-', $schoolstart)); my $track = $g_MTrackTermType{12}; # track of grade 12; my $enddate = $g_MTrackTerm{$track}{$arr{endterm}}{end}; print qq{

Term End Date:$enddate

\n}; print qq{
We download the completed course marks from Aspen (MSS) every night.
The results below compare the local OA marks against what we have downloaded last night from MSS.

If you have updated MSS/SDS with marks today, they will not be shown below. They will be updated tonight.

\n}; # get courses in the passed term for 10-12 courses. my %marks; # $marks{subjsec}{provnum} = mark. my %teacher; # teacher{subjsec} = Name; my $sth = $dbhr->prepare("select subjsec, teacher from subject where ( grade = 10 or grade = 11 or grade = 12 ) and endrptperiod = ? order by grade, description"); my $sth1 = $dbhr->prepare("select studnum, a1 from eval where subjcode = ? and term = ?"); my $sth2 = $dbhr->prepare("select count(distinct studnum) from eval where subjcode = ?"); my $sth3 = $dbhr->prepare("select lastname, firstname from staff where userid = ?"); my @courses; $sth->execute( $arr{endterm} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($subjsec, $userid) = $sth->fetchrow ) { # Get Teacher Name $sth3->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname) = $sth3->fetchrow; if ( not $lastname ) { $lastname = qq{Teacher Not Found ($userid)}; } $teacher{$subjsec} = qq{$lastname, $firstname}; # Get Student Count $sth2->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $studcount = $sth2->fetchrow; # print "Course:$subjsec Count:$studcount
\n"; if ( not $studcount ) { next; } # skip this subject, no enrollments. push @courses, $subjsec; # Get students; studnum and mark for this course. $sth1->execute( $subjsec, $arr{endterm} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($studnum, $mark) = $sth1->fetchrow ) { # print "SN:$studnum Mark:$mark
\n"; #$mark =~ s/\D+//g; # strip any non numeric values # if ( not $mark ) { next; } # skip any blank marks # No, we want to see all enrolled. $marks{$subjsec}{$studnum} = $mark; } } # Prep for loop building hash of subjects and marks my $sth = $dbh->prepare("select * from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?"); print qq{

Current OA Subjects ending in term $arr{endterm} ($enddate)

\n}; my $first = 1; foreach my $subjsec ( @courses ) { if ( $first ) { print qq{\n}; print qq{}; print qq{\n}; $first = 0; } # Get Course Info $sth->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } $cref = $sth->fetchrow_hashref; %c = %$cref; # Get Student Count $sth1->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $studcount = $sth1->fetchrow; print qq{}; print qq{\n}; } if ( not $first ) { print qq{
Course NameTeacherCourse
Section
GradeEnrollment
$c{description}$teacher{$subjsec}$subjsec$c{grade}$studcount

\n}; } else { print qq{

No Courses Found

\n}; } # Test =head foreach my $subjsec ( sort keys %marks ) { my ($courseid, $section) = split('-', $subjsec); foreach my $studnum ( sort keys %{ $marks{$subjsec} } ) { print "Sub:$subjsec SN:$studnum Mark:$marks{$subjsec}{$studnum}
\n"; } } =cut # Now check, using this %marks hash for all matching marks in the completed courses. my $sth = $dbhr->prepare("select * from sasked_completedcourses where courseid = ? and provnum = ?"); my $sth1 = $dbhr->prepare("select provnum, firstname, lastname, grade from studentall where studnum = ?"); my $sth3 = $dbhr->prepare("select exammix from subject where subjsec = ?"); my %coursename; my $first = 1; foreach my $subjsec ( @courses ) { #sort keys %marks ) { my ($courseid, $section) = split('-', $subjsec); # Load course name if not in hash if ( not $coursename{$courseid} ) { my $sth2 = $dbhr->prepare("select title from sasked_courses where code = ?"); $sth2->execute($courseid); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } $coursename{$courseid} = $sth2->fetchrow; } # Load course exammix to see if blended dept/school mark $sth3->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $exammix = $sth3->fetchrow; if ( not $first ) { # print out course info print qq{$coursename{$courseid} ($subjsec / $exammix) - }; print qq{$teacher{$subjsec}}; print qq{OA
MarkSDS
Mark\n}; } foreach my $studnum ( sort keys %{ $marks{$subjsec} } ) { my $mark = $marks{$subjsec}{$studnum}; # OA Mark if ( $first ) { print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; $first = 0; } # Get their provincial number. If missing show error and skip $sth1->execute($studnum); # note courseid (8017), Not subjsec (8017-1) if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($provnum, $firstname, $lastname, $grade) = $sth1->fetchrow; if ( not $provnum ) { print qq{

Missing Provincial Number for $firstname $lastname - }; print qq{Grade $grade ($studnum)

\n}; next; } # Load the SDS Course Mark, this student, this course. $sth->execute( $courseid, $provnum ); # note courseid (8017), Not subjsec (8017-1) if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchall_hashref(id); my %data = %$ref; # use Data::Dumper; # print Dumper $ref; if ( not %data ) { # no record print qq{}; print qq{}; print qq{\n}; next; } foreach my $id ( keys %data ) { # print "ID:$id - V:$data{$id} - $data{$id}{courseid}
\n"; my %r = %{ $data{$id}}; my $endjd = julian_day( split('-', $r{courseenddate} )); if ( $endjd < $schoolstartjd ) { next; } # skip if ( not $r{id} ) { # no record print qq{}; print qq{}; print qq{\n}; } else { my $markcolor = 'green'; if ( $r{finalmark} != $marks{$subjsec}{$studnum} ) { $markcolor = 'red'; } if ( $markcolor eq 'red' and $exammix eq 'Blended' ) { $markcolor = 'DarkOrange'; } my $datecolor = 'green'; if ( $r{courseenddate} ne $enddate ) { $datecolor = 'red'; } print qq{}; print qq{}; # the OA Mark print qq{}; # the SDS mark. print qq{}; print qq{\n}; } } # end of id loop for student-course, multiple records } # provnum loop } # end of subjsec loop print qq{
Red SDS Mark = }; print qq{Mismatch with OA Mark }; print qq{Red Date = }; print qq{Different Course End Date
$coursename{$courseid} ($subjsec / $exammix) - }; print qq{$teacher{$subjsec}OA
Mark
SDS
Mark
$firstname $lastname ($studnum)$provnum$markNo SDS Mark
$firstname $lastname ($studnum)No SDS Mark
$firstname $lastname ($studnum)$provnum$marks{$subjsec}{$studnum}$r{finalmark}

\n}; print qq{\n}; exit; } # end of checkTerm