#!/usr/bin/perl # Copyright 2001-2022 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. # Description: Grade based subject enrollment. List all the students # for all the subjects in that grade. Deselect those that you don't # have in those subjects. my %lex = ('Main' => 'Main', 'Report Card' => 'Report Card', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Continue' => 'Continue', 'Show Withdrawn Students' => 'Show Withdrawn Students', 'Course Enrollments' => 'Course Enrollments', 'Add' => 'Add', 'Checked' => 'Checked', 'Blank=All' => 'Blank=All', 'Separate with Spaces' => 'Separate with Spaces', 'Starts with' => 'Starts with', 'Terms' => 'Terms', 'Start' => 'Start', 'End' => 'End', 'Student' => 'Student', 'Selection' => 'Selection', 'Grades' => 'Grades', 'Starts with' => 'Starts with', 'Courses' => 'Courses', 'Students' => 'Students', 'Record(s) Stored' => 'Record(s) Stored', 'Record Exists' => 'Record Exists', 'Contact' => 'Contact', 'Error' => 'Error', 'Checked' => 'Checked', 'Term' => 'Term', 'Not Found' => 'Not Found', 'Skip' => 'Skip', 'and' => 'and', 'Or' => 'Or', 'Name' => 'Name', 'Student Number' => 'Student Number', 'Next Page' => 'Next Page', 'Sort by' => 'Sort by', 'Mdl' => 'Mdl', 'Save' => 'Save', 'Course' => 'Course', ); my $self = 'enroladdall.pl'; use DBI; use CGI; 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"; } } # Load subjects to skip for moodle table. my $sth = $dbh->prepare("select id, datavalue from conf_system where dataname = ?"); foreach my $val ( 'r_SupressSubject','r_AdditionalComments' ) { $sth->execute( $val ); 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 $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; my $grade = $arr{grade}; my $checked = $arr{checked}; # Print Page Header my $title = "$lex{Add} $lex{'Course Enrollments'}"; 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{

$title

\n}; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectGroups(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; selectSubjStud(); } elsif ( $arr{page} == 3 ) { delete $arr{page}; writeRecords(); } #---------------- sub showStartPage { #---------------- my @homerooms; my $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; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Student} $lex{Selection}
\n}; print qq{
}; print qq{Select Students with one of the following methods
\n}; # Homeroom print qq{
  • $lex{Homeroom} }; print qq{
    • \n}; # Grade print qq{
  • $lex{Grades} }; print qq{\n}; print qq{$lex{'Separate with Spaces'}
    • }; # Student Number print qq{
  • Local Student Number }; print qq{
    • \n}; print qq{
\n}; print qq{
}; print qq{$lex{'Next Page'} $lex{Checked}? }; print qq{
$lex{'Show Withdrawn Students'}\n}; print qq{ }; print qq{
\n}; # Now Course Selection. print qq{\n}; print qq{\n}; # Search for Courses by Grade print qq{
$lex{Course} $lex{Selection}
}; print qq{
}; print qq{Select Courses with one of the following methods
\n}; print qq{
  • $lex{Grades} }; print qq{ }; print qq{Separate multiple grades with Spaces
    • \n}; # Search by Course Name (Description) print qq{
  • Course Name $lex{'Starts with'}}; print qq{
    • \n}; # Search for Courses by Term print qq{
  • Start-End $lex{Terms}\n}; print qq{ 3-4 6-8\n}; print qq{$lex{'Separate with Spaces'}
    • \n}; print qq{
\n}; # Section Filter print qq{
Filter by Section \n}; print qq{
\n}; print qq{Normally blank. Only show courses with these sections }; print qq{from grades above. Separate multiple sections with Spaces
\n}; # Next Page Checked for Courses. print qq{

$lex{'Next Page'} $lex{Checked}?\n}; print qq{

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

\n}; print qq{
\n}; exit; } #----------------- sub selectGroups { # select groups of students and subjects to enrol. #----------------- # foreach my $key ( sort keys %arr) { print "K:$key V:$arr{$key}
\n"; } my %sortedstudents; my $table = 'student'; # overridden to studentall if show withdrawn selected. if ( $arr{showwithdrawn} ) { # change to both student tables. $table = 'studentall'; } my $studcheck = $arr{studcheck}; my $subjcheck = $arr{subjcheck}; my (@students, %students); if ( $arr{grades} ) { my $sth = $dbh->prepare("select lastname, firstname, studnum, grade from $table where grade = ? order by lastname, firstname"); my @grades = split(/\s+/, $arr{grades}); foreach my $grade ( @grades ) { $sth->execute( $grade ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $studnum, $grade ) = $sth->fetchrow ) { push @students, $studnum; $students{$studnum} = "$lastname, $firstname ($grade)"; } } } elsif ( $arr{homeroom} ) { my $sth = $dbh->prepare("select lastname, firstname, studnum, grade, homeroom from $table where homeroom = ? order by grade, lastname, firstname"); $sth->execute( $arr{homeroom} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $studnum, $grade, $homeroom ) = $sth->fetchrow ) { push @students, $studnum; $students{$studnum} = "$lastname, $firstname ($grade $homeroom)"; } } elsif ( $arr{studnum} ) { my $sth = $dbh->prepare("select lastname, firstname, studnum, grade, homeroom from $table where studnum = ?"); $sth->execute( $arr{studnum} ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } while ( my ( $lastname, $firstname, $studnum, $grade, $homeroom ) = $sth->fetchrow ) { push @students, $studnum; $students{$studnum} = "$lastname, $firstname ($grade $homeroom)"; } } else { print qq{

No Students Selected

\n}; print qq{\n}; exit; } # All students now in @students and %students; # print "Students", @students, "
\n"; # Now do Course Selection. my ( @subjects_name, %subjects_name); # Search by description if any... if ( $arr{subjname} ) { # Find all subjects with this name. my $sth = $dbh->prepare("select description, subjsec from subject where description like ? order by description"); $desc = $arr{subjname}. '%'; $sth->execute( $desc ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec ) = $sth->fetchrow ) { my ($code, $section) = split('-', $subjsec); if ( $arr{section} and $arr{section} ne $section ) { next; } $description =~ s/://g; # strip any colons push @subjects_name, $subjsec; $subjects_name{$subjsec} = $description; } } # Search by grades, if any. my ( @subjects_grade, %subjects_grade); if ( $arr{grade} ) { my @grades = split /\s+/, $arr{grade}; # load sections, if any. my @sections; if ( $arr{section} ) { @sections = split /\s+/, $arr{section}; } # print "Grades:@grades Sections:@sections
\n"; my %sort; foreach my $gr (sort @grades ) { # Find all courses in this grade... my $sth; if ( $arr{section} ) { foreach my $section ( @sections ) { $sth = $dbh->prepare("select description, subjsec from subject where grade = ? and section = ? order by description"); $sth->execute( $gr, $section ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec ) = $sth->fetchrow ) { $description =~ s/://g; # strip any colons $sort{"$description$subjsec"} = $subjsec; $subjects_grade{$subjsec} = $description; } } } else { # no section selector $sth = $dbh->prepare("select description, subjsec from subject where grade = ? order by description"); $sth->execute( $gr ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec ) = $sth->fetchrow ) { $description =~ s/://g; # strip any colons $sort{"$description$subjsec"} = $subjsec; $subjects_grade{$subjsec} = $description; } } # populate @subjects_grade array for ordering of courses foreach my $key ( sort keys %sort ) { push @subjects_grade, $sort{$key}; } # print "Subjects Grade: @subjsects_grade
", %sort, "
\n"; } # end of grade loop } # end of if grade # Find all subjects for these terms my ( @subjects_term, %subjects_term); if ( $arr{term} ) { my $sth = $dbh->prepare("select description, subjsec from subject where startrptperiod = ? and endrptperiod = ? order by description"); if ( $arr{section} ) { $sth = $dbh->prepare("select description, subjsec from subject where startrptperiod = ? and endrptperiod = ? and section = '$arr{section}' order by description"); } my @termgroups = split(/\s+/, $arr{term}); foreach my $val ( @termgroups ) { my ( $start, $end ) = split '-', $val; $sth->execute( $start, $end ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec ) = $sth->fetchrow ) { $description =~ s/://g; # strip any colons push @subjects_term, $subjsec; $subjects_term{$subjsec} = $description; } } } # Now should have @subjects and %subjects populated. # print "Subjects:", @subjects_term, "
\n"; # Print Form start print qq{
\n}; print qq{\n}; # Get Checkbox state (for next page for select particular subject-student enrol). print qq{

}; print qq{$lex{'Next Page'} $lex{Checked}?\n}; print qq{

\n}; # Print Student Information. print qq{\n}; print qq{\n}; my $class = 'blue'; foreach my $studnum ( @students ){ #loop through students # Manage Class value. if ( not $arr{sortbyname} ) { my ($dud, $grp) = split(/\(/, $students{$studnum}); chop $grp; # remove trailing parens. $oldgrp = $currgrp; $currgrp = $grp; if ( $currgrp ne $oldgrp ) { # switch colors; if ( $class eq 'blue' ) { $class = 'gray'; } else { $class = 'blue'; } } } print qq{\n}; } print qq{\n}; print qq{
}; print qq{
}; print qq{\n}; print qq{ $students{$studnum}
}; print qq{
\n}; # Print Course Information, now print qq{\n}; print qq{\n}; $sth1 = $dbh->prepare("select lastname,firstname from staff where userid = ?"); $sth = $dbh->prepare("select startrptperiod, endrptperiod, teacher from subject where subjsec = ?"); if ( @subjects_name ) { print qq{\n}; foreach my $subjsec ( @subjects_name ){ # loop through subjects by Name $sth->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $startterm, $endterm, $teacher ) = $sth->fetchrow; $sth1->execute( $teacher ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth1->fetchrow; print qq{\n}; } } if ( @subjects_grade ) { print qq{\n}; foreach my $subjsec ( @subjects_grade ){ # loop through subjects by Name $sth->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $startterm, $endterm, $teacher ) = $sth->fetchrow; $sth1->execute( $teacher ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth1->fetchrow; print qq{\n}; } } if ( @subjects_term ) { print qq{\n}; foreach my $subjsec ( @subjects_term ){ # loop through subjects by Name $sth->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $startterm, $endterm, $teacher ) = $sth->fetchrow; $sth1->execute( $teacher ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth1->fetchrow; print qq{\n}; } } print qq{\n}; print qq{
}; print qq{
$lex{Name} $arr{subjname}
}; print qq{\n}; print qq{ $subjects_name{$subjsec} ($subjsec) $firstname $lastname ($teacher) - $lex{Terms} $startterm\-$endterm
$lex{Grades} $arr{grade}
}; print qq{\n}; print qq{ $subjects_grade{$subjsec} ($subjsec) $firstname $lastname ($teacher) $lex{Terms} }; print qq{ $startterm\-$endterm
$lex{Terms} $arr{term}
}; print qq{\n}; print qq{ $subjects_term{$subjsec} ($subjsec) $firstname $lastname ($teacher) $lex{Terms} }; print qq{ $startterm\-$endterm
}; print qq{
\n}; exit; } #----------------- sub selectSubjStud { #----------------- # foreach my $key ( sort keys %arr) { print "K:$key V:$arr{$key}
\n"; } # select Student-Subject enrollments - one student in one subject; my $checked = $arr{checked}; delete $arr{checked}; foreach my $key ( sort keys %arr) { my ($dud, $sigil, $value ) = split /:/, $key; if ( $sigil eq 'ST' ) { push @students, $value; } elsif ( $sigil eq 'SU' ) { push @subjects, $value; } else { # problems! print $lex{Error}. " $key
\n"; } } # Find subject descriptions / student name my $sth = $dbh->prepare("select description from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); print qq{
\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; foreach my $subjsec ( @subjects ) { # Get Subject Description $sth->execute( $subjsec ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $description = $sth->fetchrow; # Print Table Heading print qq{\n}; print qq{\n}; foreach my $studnum ( @students ) { # Get Student Name $sth1->execute( $studnum ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth1->fetchrow; # print a current table row print qq{\n}; } print qq{
$description ($subjsec)
}; print qq{\n}; print qq{$lastname, $firstname
\n}; } print qq{
\n}; print qq{
\n}; print qq{
\n}; exit; } # end of selectSubjects #--------------- sub writeRecords { #--------------- #foreach my $key (keys %arr) { print "K:$key V:$arr{$key}
\n"; } print qq{\n\n}; print qq{}; print qq{\n}; my $sth = $dbh->prepare("select description, startrptperiod, endrptperiod from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select lastname, firstname from studentall where studnum = ?"); foreach my $key ( sort keys %arr ) { # $key contains studnum:subjsec my ( $subjsec, $studnum ) = split(/:/,$key); # Read the subject information $sth->execute( $subjsec ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ( $description, $startterm, $endterm ) = $sth->fetchrow; # Get Student Name $sth1->execute( $studnum ); if ( $DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth1->fetchrow; if ( not $lastname ) { $lastname = qq{$lex{'Not Found'}}; } # Check for the existence of the same record(s), # one for each term for each student in each subject. my ( $term, %terms ); $sth2 = $dbh->prepare("select term from eval where subjcode = ? and studnum = ? order by term"); $sth2->execute( $subjsec, $studnum ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;} # Put existing term records in the 'terms' hash; check for dups. while ( my $term = $sth2->fetchrow ) { if ( $terms{$term} ) { print "$lex{'Record Exists'} $lex{Term}: $term ". print " $description ($subjsec) - $lastname, $firstname ($studnum).
\n"; # miss steak? next; } else { # add to hash. $terms{$term} = 1; } } # print line for this record. print ""; print "\n\n}; # end of printed row } # Next Student/Subject combination. if ( not $DBI::errstr ) { print qq{
$lex{Students}$lex{Courses}$lex{Terms}
$lastname, $firstname ($studnum)$description ($subjsec) "; # Loop through and check where term is missing... for ( my $i = $startterm; $i <=$endterm; $i++ ) { if ( not $terms{$i} ){ # fill in holes! $sth3 = $dbh->prepare("insert into eval ( subjcode, studnum, term ) values ( ?, ?, ? )"); $sth3->execute( $subjsec, $studnum, $i ); # $i is the term if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } print " $i "; } else { print '('. $lex{Skip}. " $i) "; } } # end of Terms Loop # Check and Insert into external table for Moodle (ext_moodle table) my $sth4 = $dbh->prepare("select count(*) from ext_moodle where studnum = ? and subjsec = ?"); $sth4->execute( $studnum, $subjsec ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $count = $sth4->fetchrow; # skip if member of %r_SupressSubject or AdditionalComments my ($tsubjsec, $dud) = split('-', $subjsec ); my $skipflag; if ( $count or $r_SupressSubject{$tsubjsec} or $r_SupressSubject{$subjsec} or $r_AdditionalComments{$tsubjsec} or $r_AdditionalComments{$subjsec} ) { $skipflag = 1; } if ( not $skipflag ) { my ($subjcode, $section) = split('-',$subjsec); my $sth4 = $dbh->prepare("insert into ext_moodle (studnum, subjsec, subjcode, role) values ( ?, ?, ?, 'student' )"); $sth4->execute( $studnum, $subjsec, $subjcode ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } print qq{ $lex{Mdl} }; # short form for Moodle. } else { print qq{ ($lex{Skip} $lex{Mdl})}; # short form for Moodle. } print qq{
\n}; print qq{

$lex{'Record(s) Stored'}

\n}; } else { print qq{

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

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

\n}; exit; } # End of writeRecords