#!/usr/bin/perl # Copyright 2001-2019 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. my %lex = ('Reset' => 'Reset', 'Subjects' => 'Subjects', 'Main' => 'Main', 'Report Card' => 'Report Card', 'Subject' => 'Subject', 'Teacher' => 'Teacher', 'Term' => 'Term', 'Grade' => 'Grade', 'Sort by' => 'Sort by', 'No Teacher' => 'No Teacher', 'Subj-Sec' => 'Subj-Sec', 'Terms' => 'Terms', 'Edit' => 'Edit', 'Delete' => 'Delete', 'Clone' => 'Clone', 'SmDesc' => 'SmDesc', 'Enrol' => 'Enrol', 'Error' => 'Error', 'Field' => 'Field', 'Credit' => 'Credit', 'Difficulty' => 'Diff', 'Start Term' => 'Start
Term', 'End Term' => 'End
Term', 'Record(s) Updated' => 'Record(s) Updated', 'Contact' => 'Contact', 'Location' => 'Location', 'Course' => 'Course', 'Section' => 'Section', 'Master' => 'Master', 'Homeroom' => 'Homeroom', 'Seq' => 'Seq', 'Course Name' => 'Course Name', 'Sequence' => 'Sequence', 'Code' => 'Code', 'Enrollment' => 'Enrollment', 'students' => 'students', 'Primary Sort' => 'Primary Sort', 'Secondary Sort' => 'Secondary Sort', 'No Enrollments' => 'No Enrollments', 'Sort Courses by' => 'Sort Courses by', ); use DBI; use CGI; use Number::Format qw(:all); my $self = 'coursemstreset.pl'; eval require "../../etc/admin.conf.root"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $dbtype = 'mysql'; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; # Load Configuration Variables; my $sth = $dbh->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"; } } # Get Max terms defined in school year my $maxterms; foreach my $track ( keys %g_MTrackTerm ) { foreach my $trm ( keys %{ $g_MTrackTerm{$track}} ) { if ( $trm > $maxterms ) { $maxterms = $trm; } } } my $q = CGI->new; print $q->header( -charset, $charset ); my %arr = $q->Vars; my $title = "$lex{Reset} $lex{Course} $lex{Master}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{$lex{'Report Card'} |\n}; print qq{School Year Max Terms: $maxterms\n}; print qq{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; showCourses(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; updateRecords(); } #---------------- sub showStartPage { #---------------- print qq{

$lex{'Sort Courses by'}

\n}; # Course Name, Grade print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; # Grade, Name print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; # Grade, Sequence print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; # Teacher, Name print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; # Terms, Grade print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; # Terms, Name print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; # Homeroom, Name print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; # Homeroom, Sequence print qq{
\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{\n}; exit; } #---------------- sub showCourses { #---------------- # foreach $key ( sort keys(%arr)) { print "K:$key V:$arr{$key}
\n"} # Note: only sortorder passed from start page. my ($prisort, $secsort) = split(':', $arr{sortorder}); delete $arr{sortorder}; print qq{
$lex{'Primary Sort'}: $prisort }; print qq{$lex{'Secondary Sort'}: $secsort
\n}; # Grades, Homerooms my (@grades, @homerooms); my $sth = $dbh->prepare("select distinct grade from student where grade != '' and grade is not NULL"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $grade = $sth->fetchrow ) { push @grades, $grade; } @grades = sort {$a <=> $b} @grades; $sth = $dbh->prepare("select distinct homeroom from student where homeroom is not NULL and homeroom != ''"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $hr = $sth->fetchrow ) { push @homerooms, $hr; } @homerooms = sort {$a <=> $b} @homerooms; # Load staff into hash my (%teachers, %teachername); my $sth = $dbh->prepare("select lastname, firstname, st.userid from staff st, staff_multi sm where st.userid = sm.userid and sm.field_name = 'position' and sm.field_value = 'Classroom Teacher'"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $userid) = $sth->fetchrow ) { $teachers{"$lastname$firstname$userid"} = $userid; $teachername{$userid} = "$lastname, $firstname"; } # Load Locations my @locations; $sth = $dbh->prepare("select distinct location from subject where location is not NULL and location != ''"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $loc = $sth->fetchrow ) { push @locations, $loc; } @locations = sort @locations; # Start Form print qq{
\n}; print qq{\n}; # Submit Button print qq{
\n}; # Start Table print qq{}; # Queries setup my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?"); my $sth2 = $dbh->prepare("select lastname, firstname from staff where userid = ?"); my $sth3 = $dbh->prepare("select distinct term from eval where subjcode = ? and term != '' and term is not NULL"); # Sort and Display my %sort; my $sth; if ( $prisort eq 'name' ) { # secondary is blank. only order by name my $first = 1; $sth = $dbh->prepare("select * from subject order by description, grade, startrptperiod"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $id = $r{id}; my $inforef = checkCourse($ref); if ( $first ) { # Table Heading print qq{}; print qq{}; print qq{}; print qq{\n}; $first = 0; } print qq{}; print qq{}; print qq{\n}; # Sequence print qq{\n}; # Teacher print qq{\n}; # Terms print qq{\n}; print qq{\n}; # Location if ( @locations ) { print qq{\n}; } else { print qq{\n}; } # Course Enrollments print qq{\n}; } # end of Course Loop # Grades } elsif ( $prisort eq 'grade' ) { my $sort = 'sequence'; if ( $secsort eq 'name' ) { $sort = 'description'; } $sth = $dbh->prepare("select * from subject where grade = ? order by $sort"); foreach my $grade ( @grades ) { $sth->execute( $grade ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $first = 1; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $id = $r{id}; my $inforef = checkCourse($ref); # Table Heading if ( $first ) { # in this grade; print qq{\n}; print qq{}; print qq{}; print qq{}; print qq{\n}; $first = 0; } print qq{}; print qq{\n}; # Sequence print qq{\n}; # Teacher print qq{\n}; # Terms print qq{\n}; print qq{\n}; # Location if ( @locations ) { print qq{\n}; } else { print qq{\n}; } # Course Enrollments print qq{\n}; } # end of this grades records; } # end of grades loop # Teachers } elsif ( $prisort eq 'teacher' ) { my $sort = 'grade, description'; # only sort teachers by description, so far # if ( $secsort eq 'name' ) { $sort = 'description'; } $sth = $dbh->prepare("select * from subject where teacher = ? order by $sort"); foreach my $key ( sort keys %teachers ) { my $userid = $teachers{$key}; my $tname = $teachername{$userid}; $sth->execute( $userid ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $first = 1; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; my $id = $r{id}; my $inforef = checkCourse($ref); # Table Heading if ( $first ) { # in this grade; print qq{\n}; print qq{}; print qq{}; print qq{}; print qq{\n}; $first = 0; } print qq{}; print qq{\n}; # Sequence print qq{\n}; # Teacher print qq{\n}; # Terms print qq{\n}; print qq{\n}; # Location if ( @locations ) { print qq{\n}; } else { print qq{\n}; } # Course Enrollments print qq{\n}; } # end of this teachers records; } # end of teacher loop } elsif ( $prisort eq 'term' ) { $sth = $dbh->prepare("select distinct startrptperiod, endrptperiod from subject order by startrptperiod, endrptperiod"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $sort = 'grade, description'; if ( $secsort eq 'name' ) { $sort = 'description, grade'; } my $sth1 = $dbh->prepare("select * from subject where startrptperiod = ? and endrptperiod = ? order by $sort"); while ( my ($startterm, $endterm ) = $sth->fetchrow ) { $sth1->execute( $startterm, $endterm ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $first = 1; while ( my $ref = $sth1->fetchrow_hashref ) { my %r = %$ref; my $id = $r{id}; my $inforef = checkCourse($ref); # Table Heading if ( $first ) { # in this grade; print qq{\n}; print qq{}; print qq{}; print qq{}; print qq{\n}; $first = 0; } print qq{}; print qq{\n}; # Sequence print qq{\n}; # Teacher print qq{\n}; # Terms print qq{\n}; print qq{\n}; # Location if ( @locations ) { print qq{\n}; } else { print qq{\n}; } # Course Enrollments print qq{\n}; } # end of this terms records; } # end of term loop } elsif ( $prisort eq 'homeroom' ) { my %foundcourses; # keep track of them all so only show once. my $sort = 'sequence'; if ( $secsort eq 'name' ) { $sort = 'description'; } $sth = $dbh->prepare("select * from subject where subjsec = ?"); foreach my $homeroom ( @homerooms ) { # find the students in that room, and their courses. my $sth1 = $dbh->prepare("select description, grade, sequence from subject where subjsec = ?"); my %courses; $sth2 = $dbh->prepare("select e.subjcode from eval e, studentall s where s.homeroom = ? and e.studnum = s.studnum "); $sth2->execute( $homeroom ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth2->fetchrow ) { if ( not $coursesfound{$subjsec} ) { # add it $coursesfound{$subjsec} = 1; } else { # if already done, skip it next; } # Get course desc, grade; $sth1->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ($desc, $grade, $sequence ) = $sth1->fetchrow; if ( $secsort eq 'name' ) { $courses{"$desc$grade"} = $subjsec; } else { # by sequence $courses{"$grade$sequence"} = $subjsec; } } if ( not %courses ) { print qq{
No Courses found for homeroom $homeroom
\n}; next; } # Get Courses in this homeroom my $first = 1; foreach my $key ( sort keys %courses ) { my $subjsec = $courses{$key}; # Get the record $sth->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; my $id = $r{id}; my $inforef = checkCourse($ref); # Table Heading if ( $first ) { # in this grade; print qq{\n}; print qq{}; print qq{}; print qq{}; print qq{\n}; $first = 0; } print qq{}; print qq{\n}; # Sequence print qq{\n}; # Teacher print qq{\n}; # Terms print qq{\n}; print qq{\n}; # Location if ( @locations ) { print qq{\n}; } else { print qq{\n}; } # Course Enrollments print qq{\n}; } # end of this homeroom; } # end of homeroom loop } print qq{
$lex{Course}$lex{Code}$lex{Section}$lex{Grade}$lex{Seq}$lex{Teacher}$lex{'Start Term'}$lex{'End Term'}$lex{Location}$lex{Enrollment}
$r{description}$r{subjcode}$r{section}$r{grade}$inforef->{enrollments} $inforef->{error}
$lex{Grade} $grade
$lex{Course}$lex{Code}$lex{Section}$lex{Grade}$lex{Seq}$lex{Teacher}$lex{'Start Term'}$lex{'End Term'}$lex{Location}$lex{Enrolment}
$r{description}$r{subjcode}$r{section}$r{grade}$inforef->{enrollments} $inforef->{error}
$lex{Teacher} $tname
$lex{Course}$lex{Code}$lex{Section}$lex{Grade}$lex{Seq}$lex{Teacher}$lex{'Start Term'}$lex{'End Term'}$lex{Location}$lex{Enrolment}
$r{description}$r{subjcode}$r{section}$r{grade}$inforef->{enrollments} $inforef->{error}
$lex{Terms} $startterm - $endterm
$lex{Course}$lex{Code}$lex{Section}$lex{Grade}$lex{Seq}$lex{Teacher}$lex{'Start Term'}$lex{'End Term'}$lex{Location}$lex{Enrolment}
$r{description}$r{subjcode}$r{section}$r{grade}$inforef->{enrollments} $inforef->{error}
$lex{Homeroom} $homeroom
$lex{Course}$lex{Code}$lex{Section}$lex{Grade}$lex{Seq}$lex{Teacher}$lex{'Start Term'}$lex{'End Term'}$lex{Location}$lex{Enrolment}
$r{description}$r{subjcode}$r{section}$r{grade}$inforef->{enrollments} $inforef->{error}
\n}; # Submit Button print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } #--------------- sub updateRecords { #--------------- #foreach $key ( sort keys(%arr)) { print "K:$key V:$arr{$key}
\n"} foreach my $key ( sort keys %arr ) { my ($id, $fld ) = split(/:/, $key); # Check for start/end term errors: if ( $fld eq 'sterm' ) { # if (( $arr{$key} > $maxterms ) or ( $arr{$key} < 0 )) { print qq{

Error: $fld - $arr{$key}

\n}; print qq{\n}; exit; } } elsif ( $fld eq 'eterm' ) { # my $stermkey = $id. ':sterm'; my $stermval = $arr{$stermkey}; if (( $arr{$key} > $maxterms ) or ( $arr{$key} < 0 ) or ( $arr{$key} < $stermval )) { print "

Error: $fld - $arr{$key}

\n"; print "\n"; exit; } } if ( $fld eq 'eterm' ) { $fld = 'endrptperiod'; } if ( $fld eq 'sterm' ) { $fld = 'startrptperiod'; } my $sth = $dbh->prepare("update subject set $fld = ? where id = ?"); $sth->execute( $arr{$key}, $id ); if ($DBI::errstr ) { print "$DBI::errstr;
\n"; } } if ( not $DBI::errstr ) { print qq{

$lex{'Record(s) Updated'}

\n}; } else { print qq{

$lex{Error}: $DBI::errstr
\n}; print qq{$lex{Contact} $adminname ($adminemail )

\n}; } print qq{

[ $lex{'Report Card'} |\n}; print qq{$title ]

\n}; print qq{\n}; exit; } #-------------- sub checkCourse { #-------------- # Passed a ref to the course information, check and return course enrollments, and error msg my $ref = shift; my %r = %$ref; my $subjsec = $r{subjsec}; my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?"); my $sth3 = $dbh->prepare("select distinct term from eval where subjcode = ? and term != '' and term is not NULL"); my %info; # Get count of students enrolled in this course, and look for errors in course enrollments $sth1->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; my $terms = ($r{endrptperiod} - $r{startrptperiod}) + 1; if ($terms < 1) { $terms = 1; } my $enrollments = round( $count / $terms, 2); $info{enrollments} = "$enrollments $lex{students}"; # if ($count % $terms != 0 ) { # error # $enrollments = qq{$enrollments: Error}; # } # Check that start term -> end terms match those in course enrollments. my (%crsenrol,%crsmst, %master); $sth3->execute( $subjsec ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while (my $trm = $sth3->fetchrow ) { $crsenrol{$trm} = 1; } foreach my $trm ($r{startrptperiod}..$r{endrptperiod}) { $crsmst{$trm} = 1; } $info{enrol} = join(',', sort keys %crsenrol); $info{mst} = join(',', sort keys %crsmst); %master = (%crsenrol,%crsmst); # join them foreach my $t ( keys %master ) { if ( $crsenrol{$t} and $crsmst{$t} ) { delete $crsenrol{$t}; delete $crsmst{$t}; } } # both should now be empty; my $errormsg; if ( %crsenrol ) { $errormsg = " Extra Course Enrollments - Term "; my $terms = join(',', sort keys %crsenrol ); $errormsg .= $terms; } if ( %crsmst ) { $errormsg .= " Missing Course Enrollments - Term "; my $terms = join(',', sort keys %crsmst ); $errormsg .= $terms; } if ( $enrollments and $errormsg ) { $errormsg = qq{$errormsg}; } elsif ( not $enrollments ) { $errormsg = qq{$lex{'No Enrollments'}}; } $info{error} = $errormsg; return \%info; }