#! /usr/bin/perl
# Copyright 2001-2018 Leslie Richardson
# This file is part of Open Admin for Schools.
# Check the school marks from 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.
# Check for Missing Marks in current school year.
my $self = 'checkcurrmarks_local.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);
# HTML Header
my $title = 'Check for Missing SDS Marks';
print qq{$doctype\n$title\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{[ Main ]\n};
print qq{
$title
};
if ( not $arr{page} ) {
showStartPage();
} elsif ( $arr{page} == 1 ) {
delete $arr{page};
checkTerm();
}
#-------------
sub checkTerm {
#-------------
#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);
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 Sask Ed
every night. The results below compare the local OA marks against
what we have downloaded last night from Sask Ed (SDS).
If you have
updated Sask Ed with marks today, they will not be shown
below. They will be updated tonight. OR you can update them using the button below.\n};
print qq{
\n};
# get courses in passed term for 10-12 courses.
my %marks; # $marks{subjsec}{provnum} = mark.
my %teacher; # teacher{subjsec} = Name;
my $sth = $dbh->prepare("select subjsec, teacher from subject where ( grade = 10 or grade = 11 or grade = 12 )
and endrptperiod = ? order by grade, description");
my $sth1 = $dbh->prepare("select studnum, a1 from eval where subjcode = ? and term = ?");
my $sth2 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
my $sth3 = $dbh->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{
Course Name
Teacher
Course Section
};
print qq{
Grade
Enrollment
\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{
$c{description}
$teacher{$subjsec}
$subjsec
};
print qq{
$c{grade}
$studcount
\n};
}
if ( not $first ) {
print qq{
\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 = $dbh->prepare("select * from sasked_completedcourses where courseid = ? and provnum = ?");
my $sth1 = $dbh->prepare("select provnum, firstname, lastname, grade from studentall where studnum = ?");
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 = $dbh1->prepare("select title from sasked_courses where code = ?");
$sth2->execute($courseid);
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
$coursename{$courseid} = $sth2->fetchrow;
}
if ( not $first ) { # print out course info
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 - Grade $grade
\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{
$firstname $lastname ($studnum)
};
print qq{
$provnum
$mark
};
print qq{
No SDS Mark
\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{
$firstname $lastname ($studnum)
};
print qq{
No SDS Mark
\n};
} else {
my $markcolor = 'green';
if ( $r{finalmark} != $marks{$subjsec}{$studnum} ) { $markcolor = 'red'; }
my $datecolor = 'green';
if ( $r{courseenddate} ne $enddate ) { $datecolor = 'red'; }
print qq{
$firstname $lastname ($studnum)
};
print qq{
$provnum
}; #
$subjsec
};
# print qq{
$r{courseenddate}
};
# the OA Mark
print qq{
$marks{$subjsec}{$studnum}
};
# the SDS mark.
print qq{
$r{finalmark}
};
print qq{
\n};
}
} # end of id loop for student-course, multiple records
} # provnum loop
} # end of subjsec loop
print qq{
\n};
print qq{\n};
exit;
} # end of checkTerm
#----------------
sub showStartPage {
#----------------
# foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key} \n"; }
# Start the Form
print qq{};
print qq{