#! /usr/bin/perl # Copyright 2001-2024 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:$dbtype:database=$db;host=$remotehost"; $dbhr = DBI->connect($dsn,$remoteuser,$remotepassword); } my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); 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:$dbtype: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}; 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); 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{$firstname $lastname}; # 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; } } # Now get students sorted by name my %sortedmarks; # Sort students by name my $sth4 = $dbhr->prepare("select lastname, firstname,grade from studentall where studnum = ?"); foreach my $crs ( keys %marks ) { # Get all students in this course. my %sort = (); my @sort = (); foreach my $studnum ( keys %{ $marks{$crs}} ) { # Get name,grade $sth4->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn,$gr) = $sth4->fetchrow; $sort{"$ln$fn$studnum"} = $studnum; } foreach my $key ( sort keys %sort ) { my $sn = $sort{$key}; push @sort, $sn; } $sortedmarks{$crs} = \@sort; } # Test for arrays of student numbers in sortedmarks; =head foreach my $subjsec ( sort keys %sortedmarks ) { my $ref = $sortedmarks{$subjsec}; my @tmp = @$ref; print "Subjsec:$subjsec REF:$ref STUDS:@tmp

\n"; } =cut # 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; my ($currgrade, $prevgrade); 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; $prevgrade = $currgrade; $currgrade = $c{grade}; # Get Student Count $sth1->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $studcount = $sth1->fetchrow; if ( $currgrade ne $prevgrade ) { print qq{\n}; } print qq{}; print qq{\n}; } if ( not $first ) { print qq{

Course Name	Teacher	Course Section	Grade	Enrollment
}; print qq{Grade $currgrade
$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 = ?"); # Withdrawn? my $sth4 = $dbhr->prepare("select count(*) from studentwd where studnum = ?"); my %coursename; my $first = 1; my $count = 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 $exammix ) { $exammix = 'School'; } if ( $exammix eq 'Blended' ) { # make it more visible $exammix = qq{$exammix\n}; } if ( not $first ) { # print out course info print qq{\n}; print qq{\n}; print qq{}; print qq{\n}; } # my $ref = $sortedmarks{$subjsec}; # my @studs = @$ref; # foreach my $studnum ( @studs ) { # foreach my $subjsec ( sort keys %sortedmarks ) { my $ref = $sortedmarks{$subjsec}; my @tmp = @$ref; foreach my $studnum ( @tmp ) { my $wd; # set below my $mark = $marks{$subjsec}{$studnum}; # OA Mark if ( $first ) { print qq{

$coursename{$courseid} $subjsec/$exammix Mark - }; print qq{$teacher{$subjsec}		OA Mark	SDS Mark

\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; } # Check for Withdrawn $sth4->execute($studnum); # note courseid (8017), Not subjsec (8017-1) if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $wdcount = $sth4->fetchrow; if ( $wdcount ) { $wd = qq{WD}; } # 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 } # student loop $count++; if ( $count % 3 == 0 ) { print qq{

\n}; } } # 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
$wd $lastname, $firstname ($studnum)	$provnum	$mark	No SDS Mark
$wd $lastname, $firstname ($studnum)	No SDS Mark
$wd $lastname, $firstname ($studnum)	$provnum	$marks{$subjsec}{$studnum}	$r{finalmark}

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

$lex{Select} $lex{'School Year'}
$year ($db)
Current Year

}; # print qq{$lex{'Check Next Page'} (Grades)
Term