#! /usr/bin/perl # Copyright 2001-2019 Leslie Richardson # This file is part of Open Admin for Schools. # Query School Courses and compare to provincial enrollments. # Passed Values: none. my $self = 'qryschcourses.pl'; use DBI; use CGI; use XML::Writer; use XML::Writer::String; use Data::UUID; use HTTP::Request::Common qw(POST); use HTTP::Headers; use LWP::UserAgent; use XML::LibXML; my %lex = ( 'Main' => 'Main', 'Error' => 'Error', ); eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } eval require "slxmllibNew.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; my $q = new CGI; my %arr = $q->Vars; print $q->header( -charset, $charset); if ($arr{debug}){ $debug = 1; delete $arr{debug}; } print qq{$doctype\nSDS Query Course Enrollments\n}; print qq{\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ Main | Export ]\n}; print qq{

Query Sask Ed Course Enrollments

\n}; print qq{

Query Sask Ed's records of student course enrollments for your school

\n}; my @tim = localtime(time); my $year = $tim[5] + 1900; $tim[4]++; for (0..4){if (length($tim[$_]) == 1){ $tim[$_] = '0'.$tim[$_];}} $currdate = "$year-$tim[4]-$tim[3]"; $currtime = "$tim[2]:$tim[1]:$tim[0]"; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; showQuery(); } #------------ sub showQuery { #------------ my ($reqstart, $reqend) = split(':', $arr{terms}); # requested start and end terms. # Find all 10-12 subjects and add those with students enrolled into a hash. my $sth = $dbh->prepare("select distinct eval.subjcode from eval left outer join subject on eval.subjcode = subject.subjsec where subject.grade = 10 or subject.grade = 11 or subject.grade = 12"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} while ( my $subjsec = $sth->fetchrow ) { $enrolledsubjects{$subjsec} = 1; } # foreach my $subjsec ( sort keys %enrolledsubjects ) { # print qq{ $subjsec /\n}; # } # Create a new user agent my $ua = LWP::UserAgent->new(); $ua->agent("OpenAdmin"); $count=1; mkQueryString($count); # Generate $output string # DEBUG Data Errors if ($debug){ print qq{
\n}; print qq{

DEBUG - Request sent to Sask Ed

\n}; my $temp = $output->value; $temp =~ s//>/g; print qq{
",$temp,"

\n}; } # Create the https post request my $req = POST $url, [ XML=>$output->value ]; $req->content_type('application/xml;charset="utf-8"'); $req->authorization_basic($sds_userid, $sds_password); # Issue the request and receive a response my $res = $ua->request($req); # Check the status of the response if ($res->is_success) { # For Debugging Data Errors if ($debug){ print qq{

DEBUG - Sask Ed XML Response

\n}; print qq{
\n}; my $temp = $res->content; $temp =~ s//>/g; print qq{
$temp
\n}; } # Parse the response. my $parser = XML::LibXML->new(); eval {$doc = $parser->parse_string($res->content)}; if ($@){ print qq{Error: $@
\n}; print qq{
Sask Ed Error:\n",$res->content,"

\n}; print qq{\n}; die; } $doc->setEncoding('UTF-8'); $root = $doc->getDocumentElement; $root->setNamespace($xmlns,'sl',1); # find Message ID. $mastermsgid = $root->findvalue('//sl:SL_MsgId'); $status = $root->findvalue('//sl:SL_Status/sl:SL_StatusCode'); if ($status eq 'Errors' or $status eq 'Invalid'){ $statusmsg = $root->findvalue('//sl:SL_Status/sl:SL_StatusMsg'); $errormsg = $root->findvalue('//sl:SL_Error/sl:SL_ErrorMsg'); # Print out error and exit print qq{

Error: $statusmsg

\n}; print qq{

Error: $errormsg

\n}; print qq{\n}; exit; } elsif ($status eq 'Successful'){ # Done below instead. # parseStudentCourseEnrollments(); } else { # print warnings... ($status eq 'Warnings') print qq{There were warnings...}; } } else { # Transfer Error! my $err = $res->status_line; print qq{

Transfer Error: $err

}; } my ($course_ref,$coursedata_ref) = parseSCEbyClass(); # $courseid:startdate:enddate = mode:marksource mode is classroom or distance,marksource is school / blended. # TEST VALUES # my %courses = %$course_ref; # foreach my $subjsec ( sort keys %courses ) { print qq{ $subjsec / \n}; } # my %coursedata = %$coursedata_ref; # foreach my $key ( sort keys %coursedata ) { print qq{ KEY:$key Val:$coursedata{$key}
\n}; } my $track = $g_MTrackTermType{'12'}; # use grade 12's values my %termdates; # termdates{date} = term:start/end foreach my $term ( sort keys %{ $g_MTrackTerm{$track}} ) { my $startdate = $g_MTrackTerm{$track}{$term}{start}; $termdates{$startdate} = qq{$term:start}; my $enddate = $g_MTrackTerm{$track}{$term}{end}; $termdates{$enddate} = qq{$term:end}; } my %sort; # control sorting order of the course_ref object. my %coursedata; # Check if subjsec... normally should be... my $sth1 = $dbh->prepare("select * from subject where subjsec = ?"); foreach my $cl (keys %{$course_ref}) { my ($subj,$startdate,$enddate) = split ':',$cl; #Get Description, Grade, and Terms. (all fields) my $ref; $sth1->execute( $subj ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} $ref = $sth1->fetchrow_hashref; # Just in case a manually entered course.... if ( not $ref ) { # a manually entered course my ($tch,$code,$section, $sdate) = split('-', $subj); my $subj = qq{$code-$section}; $sth1->execute( $subj ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} $ref = $sth1->fetchrow_hashref; } my %r = %$ref; $coursedata{$subj} = $ref; # Check term values against Sask Ed info. my $stermval = $termdates{$startdate}; if ( not $stermval ) { print qq{

Error: No matching start date for SDS $startdate for $r{description} ($subj)

\n}; } my ($sterm, $dud) = split(':', $stermval); my $etermval = $termdates{$enddate}; if ( not $etermval ) { print qq{

Error: No matching end date for SDS $startdate for $r{description} ($subj)

\n}; } my ($eterm, $dud) = split(':', $etermval); # $sort{"$sterm$eterm$r{grade}$r{description}$subj"} = $cl; $sort{"$r{grade}$r{description}$subj"} = $cl; } print qq{
}; print qq{

A red value in the classid above the table shows that Sask Ed has stored a value that is actually just a subject code, not a real subject-section. The script has looked for a subject that matches this classid and used that instead.

\n}; print qq{

A red value in the total at the bottom of the table for a class indicates that there is difference of opinion between Sask Ed and the local enrollment records. This means that you should run the synchronization script for that subject-section to update enrollments for that class. Currently OA will not remove an entire missing class enrollment.

\n}; print qq{
\n}; print qq{

}; print qq{[ Courses that are NOT registered with Sask Ed ]

\n}; my $first = 1; my ($currgrade, $prevgrade, $currterm, $prevterm); my $coursecount = 0; # Course Main Loop foreach my $key (sort keys %sort ) { my $cl = $sort{$key}; my ($count, $subjsec, $description); my ($subj,$startdate,$enddate) = split ':',$cl; # print qq{SUB:$subj Start:$startdate End:$enddate
\n}; my $grade = ${coursedata}{$subj}{'grade'}; my $sterm = ${coursedata}{$subj}{'startrptperiod'}; my $eterm = ${coursedata}{$subj}{'endrptperiod'}; # Check if subjsec... normally should be... my $sth1 = $dbh->prepare("select id, description from subject where subjsec = ?"); $sth1->execute( $subj ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my ($id, $description) = $sth1->fetchrow; if ($id) { $subjsec = $subj; } else { # no id found, look at subject only for match. my $sth1 = $dbh->prepare("select id, description, subjsec from subject where subjcode = ?"); $sth1->execute( $subj ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my ($id, $description, $sub) = $sth1->fetchrow; if (not $id) { print qq{
}; print qq{No Matching local Course found for $subj. Error!
\n}; next; # CLASS; # jump to next class; } else { $subjsec = $sub; } } # remove from total count so ones left don't have kids enrolled. delete $enrolledsubjects{$subjsec}; # skip any terms not requested in start page if ( $eterm and ( $reqstart ne $sterm or $reqend ne $eterm && $eterm ) ) { #~~ print qq{SKIP REQ:$reqstart-$reqend START:$sterm END:$eterm SUB:$subj

\n}; next; } # Needs to be after the skip for courses not in this term group. $prevgrade = $currgrade; $currgrade = $grade; # Print divider if change in grade or terms if ( $currgrade != $prevgrade ) { # or $currterm ne $prevterm ) { print qq{

GRADE $currgrade



\n\n}; $coursecount = 1; } my (%localstudents, %totalstudents, %slstudents); my $localenrollment; # Find local students for this course. my $sth1 = $dbh->prepare("select distinct studnum, count(id) from eval where subjcode = ? group by studnum"); $sth1->execute($subjsec); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} # loop through all local students for this course. while ( my ($sn, $count) = $sth1->fetchrow ) { $localstudents{$sn} = $count; my ($lastname, $firstname, $studnum, $provnum) = split(':', getStudentInfo($sn,0) ); $totalstudents{"$lastname:$firstname:$studnum:$provnum"} = 1; } my ($mode,$marksource) = split ':',$coursedata_ref->{$cl}; my ($tmp, $termstart,$termend) = split ':',$cl; if ($tmp ne $subjsec) { $tmp = qq{}; } # Main Section for each grade level. print qq{
\n}; print qq{
}; print qq{$description ($subjsec)
\n}; print qq{ClassId $tmp  Mode $mode  }; if ($marksource eq 'Blended') { $marksource .= qq{ [ Dept Exams ]}; } print qq{Mark Source $marksource
}; print qq{Start $termstart End $termend\n}; print qq{Terms $sterm - $eterm Grade $grade\n}; print qq{\n}; print qq{}; print qq{}; print qq{\n}; foreach my $pn ( @{ $course_ref->{$cl} } ) { # Get student info for this provnum my ($lastname, $firstname, $studnum, $provnum) = split ':', getStudentInfo(0,$pn); $slstudents{$pn} = 1; $totalstudents{"$lastname:$firstname:$studnum:$pn"} = 1; } my $localcount; my $provcount; my $count = 1; # simple student counter # counts the number of students that have values in local recs. foreach my $rec (sort keys %totalstudents) { my ($lastname,$firstname, $studnum, $pn) = split(':',$rec); $lastname =~ s/WD$/WD<\/span>/; # print qq{TOTAL LN:$lastname FN:$firstname SN:$studnum PN:$pn
\n}; my $provenrol; if ($slstudents{$pn}) { $provenrol = 'Y'; $provcount++; } else { $provenrol = qq{N}; } my $localenrol; if ($localstudents{$studnum}) { $localenrol = 'Y'; $localcount++; } else { $localenrol = qq{N}; } print qq{
}; print qq{}; print qq{}; print qq{\n}; $count++; } my $newstyle; if ($provcount != $localcount) { $newstyle = 'style="color:red;background-color:#DDD;"'; } print qq{\n}; print qq{
Name (Studnum)Prov
Number
TrmsLocal
Enrol
SkLrn
Enrol
$count.$lastname, $firstname ($studnum)$pn$localstudents{$studnum}$localenrol$provenrol
SaskEd Count: $provcount }; print qq{Local Count: $localcount
\n\n}; $coursecount++; if ( $coursecount % 3 == 0 ) { print qq{
\n} } } # End of course Loop # Courses without registrations with Sask Ed. print qq{

\n}; print qq{

Courses NOT registered with Sask Ed

\n}; print qq{\n}; print qq{}; print qq{\n}; my %sort; # control sorting order of the course_ref object. my $sth = $dbh->prepare("select * from subject where subjsec = ?"); foreach my $subjsec (keys %enrolledsubjects) { $sth->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my $ref = $sth->fetchrow_hashref; $coursedata{$subjsec} = $ref; my %r = %$ref; $sort{"$r{startrptperiod}$r{endrptperiod}$r{grade}$r{description}"} = $subjsec; } my $sth1 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?"); my ($currsterm, $prevsterm, $curreterm, $preveterm, $currgrade, $prevgrade ); # foreach my $subjsec (keys %enrolledsubjects) { foreach my $key (sort keys %sort ) { my $subjsec = $sort{$key}; # Now get enrollment. $sth1->execute( $subjsec); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my $enrollment = $sth1->fetchrow; my %r = %{ $coursedata{$subjsec}}; $prevsterm = $currsterm; $preveterm = $curreterm; $currsterm = $r{startrptperiod}; $curreterm = $r{endrptperiod}; $prevgrade = $currgrade; $currgrade = $r{grade}; if ( $currsterm ne $prevsterm or $curreterm ne $preveterm ) { # print divider; print qq{\n}; } if ( $currgrade ne $prevgrade ) { print qq{\n}; } print qq{}; print qq{}; print qq{\n}; } print qq{
CourseTeacherGradeTermsEnrollment
}; print qq{Terms $currsterm - $curreterm
}; print qq{Grade $currgrade
$r{description} ($subjsec)$r{teacher}$r{grade}$r{startrptperiod} - $r{endrptperiod}$enrollment
\n}; print qq{\n}; } # end of showQuery # Functions ============================= #-------------- sub mkQueryString { # for Course Enrollments #-------------- my $grade = shift; my $provnum = shift; my $idcount = shift; # Date and schoolnumber are globals. # Create Writer Instance $output = new XML::Writer::String; my $datamode = 0; if ($debug){ $datamode = 1;} # pretty print xml output $wr = new XML::Writer(OUTPUT => $output, DATA_MODE => $datamode, DATA_INDENT => '2'); # Set XML Header and write Root Element $wr->xmlDecl("UTF-8"); $wr->startTag('SL_Message','xmlns' =>$xmlns, 'xmlns:xsi' =>$xmlnsxsi, 'xsi:schemaLocation' => $xsischemaLocation); $wr->startTag('SL_Request'); mkSL_Header($currdate, $currtime, $schoolnumber,$idcount); $wr->startTag('SL_Query'); $wr->startTag('QueryBySchool', 'RefId' => "$schoolnumber", 'ObjectName' => 'StudentCourseEnrollments', 'ScopeCode' => 'Current' ); $wr->dataElement('SchoolId',$schoolnumber); my $sdate = $schoolstart; $sdate =~ s/-//g; my $edate = $schoolend; $edate =~ s/-//g; $wr->dataElement('FromDate', $sdate ); $wr->dataElement('ToDate', $edate ); # print qq{Start:$sdate End:$edate
\n}; $wr->endTag('QueryBySchool'); $wr->endTag('SL_Query'); $wr->endTag('SL_Request'); $wr->endTag('SL_Message'); $wr->end(); } #-------------------------------- sub parseStudentCourseEnrollments { # passed document root object. #-------------------------------- my @studinfo = $root->findnodes('//sl:StudentCourseEnrollments'); my $count = 1; foreach my $student (@studinfo){ my $provnum = $student->findvalue('sl:StudentIdentification/sl:DeptAssignedPersonId'); my $birthdate = $student->findvalue('sl:StudentIdentification/sl:BirthDate'); my $sth = $dbh->prepare("select lastname, firstname from student where provnum = '$provnum'"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} my ($lastname, $firstname) = $sth->fetchrow; print qq{

$firstname $lastname ($provnum - $birthdate

\n}; print qq{\n}; print qq{}; print qq{\n}; my @classes = $student->findnodes('sl:StudentSchoolClasses/sl:StudentTermClasses'); foreach my $class (@classes) { my $schoolyear = $class->findvalue('sl:TermInfo/@SchoolYear'); my $termstart = $class->findvalue('sl:TermInfo/sl:StartDate'); my $termend = $class->findvalue('sl:TermInfo/sl:EndDate'); my @sections = $class->findnodes('sl:StudentClass'); foreach my $section (@sections) { my $classid = $section->findvalue('sl:ClassId'); my $mode = $section->find('sl:ModeOfInstruction/@Code'); my $marksource = $section->findvalue('sl:MarkSource/@Code'); print qq{\n}; print qq{\n}; } } print qq{
SchoolyearTermstartTermendClassIdModeMark Src
$schoolyear$termstart$termend$classid$mode$marksource
\n}; } } # End of Sub #------------------ sub parseSCEbyClass { # passed document root object. #------------------ # Parse SchoolCourseEnrollments, and return a reference to a courses # hash containing refs to a list of provincial numbers. my @studinfo = $root->findnodes('//sl:StudentCourseEnrollments'); my $count = 1; my (%courses, %coursedata); foreach my $student (@studinfo){ my $provnum = $student->findvalue('sl:StudentIdentification/sl:DeptAssignedPersonId'); # my $birthdate = $student->findvalue('sl:StudentIdentification/sl:BirthDate'); my @classes = $student->findnodes('sl:StudentSchoolClasses/sl:StudentTermClasses'); foreach my $class (@classes) { #my $schoolyear = $class->findvalue('sl:TermInfo/@SchoolYear'); my $termstart = $class->findvalue('sl:TermInfo/sl:StartDate'); my $termend = $class->findvalue('sl:TermInfo/sl:EndDate'); my @sections = $class->findnodes('sl:StudentClass'); foreach my $section (@sections) { my $classid = $section->findvalue('sl:ClassId'); if ( $classid =~ m/^[^0-9-]/ ) { my ($tch,$code,$section, $sdate) = split('-', $classid); $classid = qq{$code-$section}; # print "Non digits - $classid
\n"; } my $mode = $section->find('sl:ModeOfInstruction/@Code'); my $marksource = $section->findvalue('sl:MarkSource/@Code'); $coursedata{"$classid:$termstart:$termend"} = "$mode:$marksource"; if (not defined $courses{"$classid:$termstart:$termend"}) { $courses{"$classid:$termstart:$termend"} = [ $provnum ]; } else { push @{$courses{"$classid:$termstart:$termend"}},$provnum; } } } # End of StudentSchoolClasses } # End of student loop return \%courses, \%coursedata; } # End of parseSCEbyCourse #----------------- sub getStudentInfo { #----------------- my $sn = shift; # passed student number (studnum) my $pn = shift; # passed provincial number (provnum) my $sth; my ($lastname, $firstname, $studnum, $provnum); if ($pn) { $sth = $dbh->prepare("select lastname, firstname, studnum, provnum from student where provnum = ?"); $sth->execute($pn); } elsif ($sn) { $sth = $dbh->prepare("select lastname, firstname, studnum, provnum from student where studnum = ?"); $sth->execute($sn); } else { return; # nothing passed. } if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } ($lastname, $firstname,$studnum,$provnum) = $sth->fetchrow; if ( not $lastname ) { # look them up in withdrawn student table. if ( $pn ) { $sth = $dbh->prepare("select lastname, firstname,studnum,provnum from studentwd where provnum = ?"); $sth->execute($pn); } elsif ($sn) { $sth = $dbh->prepare("select lastname, firstname,studnum,provnum from studentwd where studnum = ?"); $sth->execute($sn); } if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } ($lastname, $firstname,$studnum, $provnum) = $sth->fetchrow; $lastname = qq{$lastname WD}; if ( not $lastname ) { $lastname = qq{Not Found}; } } return "$lastname:$firstname:$studnum:$provnum"; } # end of getStudentInfo #---------------- sub showStartPage { #---------------- print qq{
\n}; print qq{Select a start term / end term combination
\n}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Show the Start/End terms for choosing subjsec's. my $sth1 = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject where grade = 10 or grade = 11 or grade = 12 group by startrptperiod, endrptperiod order by startrptperiod, endrptperiod"); $sth1->execute; if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr;} while ( my ( $startterm, $endterm ) = $sth1->fetchrow ) { print qq{}; print qq{\n}; } print qq{\n}; print qq{
Start TermEnd TermSelect
$startterm$endterm
\n}; print qq{ Chk \n}; print qq{ Debug \n}; print qq{
\n}; exit; }