#!/usr/bin/perl # Copyright 2001-2020 Leslie Richardson # This file is part of Open Admin for Schools. # Open Admin for Schools is free software; you can redistribute it # and/or modify it under the terms of the GNU General Public License # as published by the Free Software Foundation; either version 2 of # the License, or (at your option) any later version. use DBI; use CGI; use Time::JulianDay; my %lex = ('Graduation Requirements' => 'Graduation Requirements', 'Credits' => 'Credits', 'Check' => 'Check', 'Main' => 'Main', 'Provincial Number' => 'Provincial Number', 'Duplicate' => 'Duplicate', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Continue' => 'Continue', 'Check All' => 'Check All', 'Schools' => 'Schools', 'Select' => 'Select', 'Missing' => 'Missing', ); my $self = 'checkgrad_local.pl'; # Read Config eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } # Read checkCredit library. eval require "../../lib/libgrad.pl"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; my (%saskedgrade, %saskedname); my $title = "$lex{Check} $lex{Credits}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ]\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; checkSchool(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; viewStudentCredits(); } #-------------- sub checkSchool { #-------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my %totals; my $studcount; if ( not $arr{grade} ) { print qq{

$lex{Missing} $lex{Grade}

\n}; print qq{\n}; exit; } my $grade = $arr{grade}; delete $arr{grade}; # load the sasked ed courses with grade; my $sth = $dbh->prepare("select * from sasked_courses where grade = 10 or grade = 11 or grade = 12"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} while ( $ref = $sth->fetchrow_hashref ) { my %r = %$ref; $saskedgrade{$r{code}} = $r{grade}; $saskedname{$r{code}} = $r{title}; } my $graderef = \%saskedgrade; print qq{

[ View Requirement Totals ]

\n}; # Load 'Program' hash my $sth = $dbh->prepare("select defaultvalue from meta where fieldid = 'program'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my $progdata = $sth->fetchrow; my %program = split(/\s+/, $progdata); # Get students in that grade. my $sth = $dbh->prepare("select studnum, lastname, firstname, provnum, program from student where grade = ? order by lastname, firstname"); $sth->execute( $grade ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my $first = 1; while ( $ref = $sth->fetchrow_hashref ) { my %r = %$ref; if ( not $r{program} ) { $r{program} = 10; } # normal program my $programtext = $program{ $r{program} }; $programtext =~ s/_/ /g; $studcount++; if ( $first ) { print qq{\n}; print qq{}; print qq{\n}; $first = 0; } print qq{}; if ( $r{program} == 9 ) { my @result = checkCreditAdult12( $r{provnum}, $graderef, $dbh ); print q{}; # nothing in grade 10 or grade 11 rows. print qq{\n}; # Print completed Credits my $cref = getCredits( $r{provnum}, $dbh ); my %credits = %$cref; my %gcred; foreach my $cid ( keys %credits ) { $gcred{ $credits{$cid} }++; } print q{\n}; } elsif ( $r{provnum} ) { my @result = checkCredit10( $r{provnum}, $graderef, $dbh ); print q{\n}; # Print completed Credits my $cref = getCredits( $r{provnum}, $dbh ); my %credits = %$cref; my %gcred; foreach my $cid ( keys %credits ) { $gcred{ $credits{$cid} }++; } print q{\n}; } else { print qq{\n}; } } # end student loop # Print Totals Row print qq{ }; foreach my $grade ( 10..12 ) { print q{\n}; } print qq{\n}; print qq{
StudentMissing
Gr 10 Req		Missing
Gr 11 Req		Missing
Gr 12 Req		Grade
Credits
$r{lastname}, $r{firstname} - $r{provnum}
\n}; print qq{ $programtext
\n}; if ( $r{provnum} ) { print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; } print qq{		}; if ( not @result ) { print qq{Complete}; } else { #print incompletes foreach my $res ( @result ) { $totals{12}{$res}++; print qq{$res
\n}; } } print qq{		}; my $csum = '0'; foreach my $grade ( sort keys %gcred ) { print qq{Gr $grade: $gcred{$grade}
\n}; $csum += $gcred{$grade}; } print qq{Total $csum
}; if ( not @result ) { print qq{Complete}; } else { #print incompletes foreach my $res ( @result ) { $totals{10}{$res}++; print qq{$res
\n}; } } print q{		}; my @result11 = checkCredit11( $r{provnum}, $graderef, $dbh ); if ( not @result11 ) { print qq{Complete}; } else { #print incompletes foreach my $res ( @result11 ) { $totals{11}{$res}++; print qq{$res
\n}; } } print q{		}; my @result12 = checkCredit12( $r{provnum}, $graderef, $dbh ); if ( not @result12 ) { print qq{Complete}; } else { #print incompletes foreach my $res ( @result12 ) { $totals{12}{$res}++; print qq{$res
\n}; } } print qq{		}; my $csum = '0'; foreach my $grade ( sort keys %gcred ) { print qq{Gr $grade: $gcred{$grade}
\n}; $csum += $gcred{$grade}; } print qq{Total $csum
Missing Provincial Number
}; print qq{Requirement
Totals
Students:$studcount		}; foreach my $key ( sort keys %{ $totals{$grade} } ) { print qq{$key - $totals{$grade}{$key}
\n}; } print qq{
\n}; print qq{\n}; exit; } # end of checkSchool #------------- sub getCredits { #------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ($provnum, $dbh) = @_; # passed provincial number, dbh # Load their completed courses my $sth = $dbh->prepare("select courseid from sasked_completedcourses where provnum = $provnum and creditsearned = '1'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my %completed; while ( my $courseid = $sth->fetchrow ) { $completed{$courseid} = $saskedgrade{$courseid}; } return \%completed; } # end of getCredits #---------------- sub showStartPage { #---------------- my $checked; if ( $arr{checked} ) { $checked = qq{checked="checked"}; } print qq{

$lex{Select} $lex{Grade}

\n}; # Form Start print qq{
\n}; print qq{\n}; # Start Table print qq{\n}; # Grade print qq{\n}; print qq{\n}; print qq{
$lex{Grade}
\n}; print qq{
\n}; exit; } #--------------------- sub viewStudentCredits { #--------------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } if ( not $arr{provnum} ) { print qq{

Student Not found

\n}; print qq{\n}; exit; } # Get Course Info from Sask Ed courses. my $sth1 = $dbh->prepare("select title, grade, ctype from sasked_courses where code = ?"); # Load 'Program' hash my $sth = $dbh->prepare("select defaultvalue from meta where fieldid = 'program'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my $progdata = $sth->fetchrow; my %program = split(/\s+/, $progdata); # Student Name, Grade, Program my $sth = $dbh->prepare("select lastname, firstname, studnum, grade, program from studentall where provnum = ?"); $sth->execute( $arr{provnum} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($lastname, $firstname, $studnum, $grade, $programcode ) = $sth->fetchrow; my $assumeflag; if ( not $programcode ) { $programcode = 10; $assumeflag = '(Assumed)'; } my $programtext = $program{$programcode}; $programtext =~ s/_/ /g; print qq{

$firstname $lastname Grade:$grade

\n}; print qq{
Program $programtext $assumeflag
\n}; # Load their completed courses my $sth = $dbh->prepare("select * from sasked_completedcourses where provnum = ? order by schoolyear, courseenddate"); $sth->execute( $arr{provnum} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} print qq{\n}; print qq{}; print qq{\n}; my $totalcredits; my ($curryear, $prevyear); my $first = 1; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; $prevyear = $curryear; $curryear = $r{schoolyear}; if ( $prevyear ne $curryear and not $first ) { print qq{\n}; } $first = 0; # Get Course Title, grade, type $sth1->execute( $r{courseid} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr;} my ($title, $grade, $ctype) = $sth1->fetchrow; my $creditcolor = 'green'; if ( not $r{creditsearned} ) { $creditcolor = 'red'; } print qq{}; print qq{}; print qq{\n}; $totalcredits += $r{creditsearned}; } print qq{}; print qq{\n}; print qq{
TitleCodeGradeTypeSchool
Year		MarkCredits
Earned
}; print qq{Current Credits$totalcredits
$title$r{courseid}$grade$ctype$r{schoolyear}$r{finalmark}$r{creditsearned}
Total Credits$totalcredits
\n}; print qq{\n}; exit; }