#!/usr/bin/perl # Copyright 2001-2023 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 = ('Main' => 'Main', 'Error' => 'Error', 'Timetable Entry' => 'Timetable Entry', 'Timetable' => 'Timetable', 'Grade' => 'Grade', 'Term' => 'Term', 'Terms' => 'Terms', 'Day' => 'Day', 'Save' => 'Save', 'Period' => 'Period', 'Add Backings' => 'Add Backings', 'Records Stored' => 'Records Stored', 'Contact' => 'Contact', 'Separate with Spaces' => 'Separate with Spaces', 'Additional Courses' => 'Additional Courses', 'Add Timetable Entries' => 'Add Timetable Entries', 'Continue' => 'Continue', 'Teacher' => 'Teacher', 'Or' => 'Or', 'Either' => 'Either', 'No Terms Found' => 'No Terms Found', 'Number of Periods' => 'Number of Periods', 'Same Day/Period; More Than 1 Course' => 'Same Day/Period; More Than 1 Course', 'No Terms' => 'No Terms', 'Course' => 'Course', 'Exists' => 'Exists', 'Skipping' => 'Skipping', 'Per' => 'Per', 'Current' => 'Current', ); use DBI; use CGI; my $self = "ttAdd.pl"; my $maxbackings = 12; # maximum number of backed classes per cycle. eval require "../../etc/admin.conf"; 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); # Set a starting maxppd value. (Max Periods per Day) my $maxppd; foreach my $key ( keys %g_ppd ) { # from admin.conf if ( $g_ppd{ $key } > $maxppd ) { $maxppd = $g_ppd{ $key }; } } # print page header. my $title = "$lex{Add} $lex{'Timetable Entry'}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} |\n}; print qq{$lex{Timetable} ]\n}; print qq{

$title

\n}; # Starting Page if ( not $arr{page} ){ showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; setTimetable(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; updateRecords(); # Write Records } #--------------- sub setTimetable { #--------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Passed: grade, periods, additionalgrades, teacher, various terms: term1, term2, etc. if ( not $arr{periods} ) { print qq{

Error: No Periods Set

\n}; print qq{\n}; exit; } if ( not $arr{teacher} and not $arr{grade} ) { print qq{

Error: No Grade or Teacher Selected

\n}; print qq{\n}; exit; } # parse passed values for termX elements. Extract X. my @terms; foreach my $key ( keys %arr ) { if ( $key =~ m/term(\d*)/ ) { push @terms,$1; } } @terms = sort { $a <=> $b } @terms; if ( not @terms ) { print qq{

$lex{'No Terms'}

\n}; print qq{\n}; exit; } # Load All Courses from these terms/grade/teacher into hash. Used to set new timetable entries my (%courses,%sort); if ( $arr{grade} ) { my $sth = $dbh->prepare("select description, subjsec from subject where grade = ? and startrptperiod <= ? and endrptperiod >= ? order by description"); foreach my $term ( @terms ) { $sth->execute($arr{grade},$term,$term); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec ) = $sth->fetchrow ){ $sort{"$description$subjsec"} = $subjsec; $courses{$subjsec} = $description; } } } else { # teacher; my $sth = $dbh->prepare("select description, subjsec from subject where (teacher = ? or teacher2 = ?) and startrptperiod <= ? and endrptperiod >= ? order by description"); foreach my $term ( @terms ) { $sth->execute($arr{teacher}, $arr{teacher}, $term,$term); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec ) = $sth->fetchrow ){ $sort{"$description$subjsec"} = $subjsec; $courses{$subjsec} = $description; } } } my (%altcourses, %altsort); # hold alternate grade courses, if necessary for entry. if ( $arr{additionalgrades} and $arr{grade} ){ @addgrades = split(/\s/,$arr{additionalgrades}); my $sth = $dbh->prepare("select description, subjsec from subject where grade = ? and startrptperiod <= ? and endrptperiod >= ? order by description"); foreach my $grade ( @addgrades ) { foreach my $term ( @terms ) { $sth->execute($grade,$term,$term); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec ) = $sth->fetchrow ){ $altsort{"$description$subjsec"} = $subjsec; $altcourses{$subjsec} = $description; } } } } # we now have %courses,%sort populated for timetable entry. Also # possibly %altcourses,%alsort # Now Display # Print Teacher or Grade print qq{\n}; print qq{\n}; print qq{}; if ( $arr{grade} ) { # print grade print qq{\n}; } else { # teacher my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute( $arr{teacher} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth->fetchrow; print qq{}; print qq{\n}; } # Print Terms my $terms = join(',',@terms); print qq{\n} ; print qq{
$lex{Grade}$arr{grade}$lex{Teacher}$firstname $lastname$lex{Terms}$terms
\n}; if ( $arr{teacher} ) { # display his/her timetable currently. # Get his/her courses my @tcourses; $sth = $dbh->prepare("select subjsec from subject where teacher = ? or teacher2 = ?"); $sth->execute( $arr{teacher}, $arr{teacher} ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth->fetchrow ) { push @tcourses, $subjsec; } print qq{
}; print qq{$lex{Current} $lex{Timetable}
\n}; my $timetablefound; # check is there are any entries foreach my $trm ( @terms ) { my $response = printTimetable($arr{teacher},$trm, \@tcourses, $arr{periods} ); if ( $response ) { $foundtimetable = 1; }; } if ( not $foundtimetable ) { print qq{

No Timetable Found

\n}; } print qq{
\n}; } else { # display timetable by grade. print qq{
}; print qq{$lex{Current} $lex{Timetable}
\n}; my $response = printGradeTimetable( $arr{grade}, \@terms, \%courses, $arr{periods} ); print qq{
\n}; } # Print Main Layout Table print qq{
}; print qq{$lex{'Add Timetable Entries'} - $lex{Terms} $terms
\n}; print qq{
\n}; print qq{
}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Set Days and Periods my $days = $g_DaysPerCycle; if ( $days eq 'w' or $days eq 'W' ) { $days = 5; } # weekly timetable. my $maxppd = $arr{periods}; # Print Header Row print qq{}; for my $d ( 1 .. $days ){ print qq{}; } print qq{\n}; # Print Each Row (period) then each Column (Day) in Term for my $ppd ( 1..$maxppd ) { print qq{}; for ( 0..$days ) { if ($_ == 0) { print qq{\n}; } else { print qq{\n}; } } print qq{\n}; } my $span = $days + 1; print qq{
$lex{Day} $d
$lex{Period} $ppd\n}; print qq{
\n}; # Now do backings... print qq{

$lex{'Add Backings'} ($lex{'Same Day/Period; More Than 1 Course'})

\n}; print qq{\n}; print qq{\n}; for ( 1..$maxbackings ){ print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; } print qq{
$lex{Course}$lex{Day}$lex{Period}
\n}; print qq{
\n}; exit; } # end of setTimetable #------------------ sub showStartPage { #------------------ # Load terms from school year configuration my %terms; foreach my $key ( sort keys %g_MTrackTerm ) { foreach my $t ( sort keys %{ $g_MTrackTerm{$key}} ) { $terms{$t} = 1; } } if ( not %terms ) { print qq{

$lex{'No Terms Found'}

\n}; print qq{\n}; exit; } # Get Teachers my %teachers; my $sth = $dbh->prepare("select distinct userid, lastname, firstname from staff order by lastname, firstname"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $userid, $lastname, $firstname ) = $sth->fetchrow ) { $teachers{"$lastname, $firstname ($userid)"} = $userid; } # Get Their Courses. my %courses; my $sth = $dbh->prepare("select distinct teacher, count(*) from subject group by teacher order by teacher"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $userid, $coursecount ) = $sth->fetchrow ) { $courses{$userid} = $coursecount; } # Get Grades my @grades; my $sth = $dbh->prepare("select distinct grade from student"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $gr = $sth->fetchrow ) { if ( $gr == 13 ) { next; } push @grades, $gr; } @grades = sort {$a <=> $b} @grades; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Grade print qq{\n}; # Additional Grades # print qq{\n}; # print qq{\n}; # print qq{\n}; # print qq{\n}; # Or Teacher print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Either}
$lex{'Timetable Entry'} $lex{Grade} }; print qq{
$lex{'Additional Courses'} - $lex{Grade} }; # print qq{$lex{'Separate with Spaces'}
}; # print qq{In cases where high school students
take courses }; # print qq{from a different grade level.
$lex{Or}
$lex{Teacher} }; print qq{
\n}; print qq{\n}; print qq{\n}; foreach my $trm ( sort {$a <=> $b} keys %terms ){ print qq{\n}; } # Number of Periods print qq{\n}; # Continue print qq{\n}; print qq{
$lex{'Timetable Entry'} $lex{Terms}
$lex{Term} $trm}; print qq{
$lex{'Number of Periods'} }; print qq{
\n\n}; exit; } #---------------- sub updateRecords { # Write the records to schedat table. #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # exit; my @terms = split(',',$arr{terms}); # same for all delete $arr{terms}; my $grade = $arr{grade}; delete $arr{grade}; my $sth = $dbh->prepare("insert into schedat (day, period, subjsec, term ) values (?,?,?,?)"); my $sth1 = $dbh->prepare("select count(*) from schedat where day = ? and period = ? and subjsec = ? and term = ?"); my $sth2 = $dbh->prepare("select description, startrptperiod, endrptperiod from subject where subjsec = ?"); foreach my $key ( keys %arr ) { my ($period,$day) = split(':',$key ); if ( $day and $arr{$key} and @terms ) { # filled entry # print qq{K: $key V: $arr{$key} Day: $day Period: $period
\n}; my $subjsec = $arr{$key}; # Get desc and terms. $sth2->execute( $subjsec ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ($desc,$startterm,$endterm) = $sth2->fetchrow; foreach my $trm ( @terms ) { # Check if this record already exists; $sth1->execute( $day, $period, $subjsec, $trm ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; if ( $count ) { # get description and skip print qq{
$lex{'Timetable Entry'} $lex{Exists}: $desc ($subjsec) }; print qq{$lex{Term}:$trm $lex{Day}:$day $lex{Period}:$period - }; print qq{Skipping!
\n}; next; # term } if ( $trm > $endterm or $trm < $starterm ) { print qq{
Error:$desc ($subjsec) Term $trm Outside of Start/End Term }; print qq{Skipping!
\n}; next; # term } # print qq{D:$day P:$period S:$subjsec G:$grade T:$trm
\n"; $sth->execute( $day, $period, $subjsec, $trm ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } } } elsif ( $arr{$key} and $key =~ m/b(\d)/){ # one of the backups... my $di = "bd$1"; my $pi = "bp$1"; if ( not $arr{$di} ) { print qq{
$lex{Error} - Missing Day}; print qq{value for $arr{$key}. Skipping!
\n}; } if ( not $arr{$pi} ) { print qq{
$lex{Error} - Missing Period}; print qq{value for $arr{$key}. Skipping!
\n}; } # print qq{Backup K:$key V:$arr{$key} T:$1 Day: $arr{$di} Period: $arr{$pi}
\n}; my $subjsec = $arr{$key}; # Get desc and terms. $sth2->execute( $subjsec ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my ($desc,$startterm,$endterm) = $sth2->fetchrow; if ( $arr{$di} and $arr{$pi} ) { # must have day and period, too. foreach my $trm ( @terms ){ # Check if this record already exists; $sth1->execute( $arr{$di}, $arr{$pi}, $subjsec, $trm ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; if ( $count ) { # get description and skip print qq{
$lex{'Timetable Entry'} $lex{Exists}: $desc ($subjsec) }; print qq{$lex{Term} $trm $lex{Day} $arr{$di} $lex{Period} $arr{$pi} }; print qq{- Skipping!
\n}; next; # term } if ( $trm > $endterm or $trm < $starterm ) { print qq{
Error:$desc ($subjsec) Term $trm Outside of Start/End Term }; print qq{Skipping!
\n}; next; # term } # print qq{BACKUP D:$arr{$di} P:$arr{$pi} S:$subjsec T:$trm
\n}; $sth->execute( $arr{$di}, $arr{$pi}, $subjsec, $trm ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } } } # end of "if day and period, too". } # if backup } # end of for loop if ( not $DBI::errstr ) { print qq{

$lex{Timetable} $lex{'Records Stored'}

}; } else { print qq{

$lex{Error} $DBI::errstr

\n}; } print qq{

[ $lex{Timetable} ]

\n}; print qq{\n}; exit; } #----------------- sub printTimetable { # print timetable for one teacher for 1 term. #----------------- my ($teacher, $term, $subjref, $periods) = @_; my @subjects = @$subjref; # Get Teacher Name my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute( $teacher ); my ($lastname, $firstname) = $sth->fetchrow; my @subject; my ($tblcols, $tblrows); # not globals anymore foreach my $subjsec ( @subjects ) { my $sth = $dbh->prepare("select day, period from schedat where term = $term and subjsec = ? order by period, day"); $sth->execute($subjsec); while ( my ($day, $period) = $sth->fetchrow ) { # setup values for $tblrows and $tblcols if ( $day > $tblcols ) { $tblcols = $day; } if ( $period > $tblrows ) { $tblrows = $period; } if ($subject[$period][$day] and $subject[$period][$day] ne $subjsec){ # if a value exists and is not identical to subjsec then add to it. $subject[$period][$day] .= " $subjsec"; } else { # just slap it in... $subject[$period][$day] = $subjsec; } } } # Done filling 2D array. if ( not @subject ) { return; } # no timetable my $sth = $dbh->prepare("select description from subject where subjsec = ?"); # print heading print qq{\n}; print qq{\n}; print qq{}; for (1..$tblcols){ print qq{}; } print qq{\n}; # Main body of table. for $i (1..$periods){ # was $tblrows print qq{}; for (1..$tblcols){ if ($subject[$i][$_]){ # if we have a subjsec value; my $firstflag = 1; @subjval = split(/\s+/,$subject[$i][$_]); print qq{}; } else { print qq{}; } } print qq{\n}; } print qq{
$firstname $lastname - }; print qq{$lex{Term} $term
$lex{Day} $_
$lex{Per} $i}; foreach my $sv (@subjval){ if (not $firstflag){ print qq{
};} else { $firstflag = 0;} $sth->execute($sv); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $desc = $sth->fetchrow; print qq{$desc ($sv)}; } print qq{		$lex{'No Value'}
\n}; return 1; # timetable found } #---------------------- sub printGradeTimetable { # print timetable for one grade for passed terms. #---------------------- my ($grade, $termref, $courseref,$periods ) = @_; my @terms = @$termref; my %courses = %$courseref; # Now for display purposes when displaying current records by # grade, we need to find courses that students may be enrolled in, # outside of their current grade; The %courses hash already has # current grade courses in these terms. my %othercourses; my $sth1 = $dbh->prepare("select description, grade from subject where subjsec = ?"); my $sth = $dbh->prepare("select distinct e.subjcode from eval e, student s where s.grade = ? and e.studnum = s.studnum"); foreach my $term ( @terms ) { $sth->execute($arr{grade}); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth->fetchrow ){ # Check the grade; $sth1->execute($subjsec); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($desc, $testgr) = $sth1->fetchrow; if ( $testgr != $arr{grade} ) { $othercourses{$subjsec} = $desc; } } } my ($tblcols, $tblrows); my $sth = $dbh->prepare("select day, period, subjsec from schedat where term = ? order by period, day"); my $first = 1; foreach my $term ( @terms ) { my @crs; # reset # Get Current timetable entries for these courses, terms. $sth->execute($term); while ( my ($day, $period,$subjsec) = $sth->fetchrow ) { if ( not $courses{$subjsec} and not $othercourses{$subjsec} ) { next; } # skip since not a course we need. $first = 0; # we have a course # setup values for $tblrows and $tblcols if ( $day > $tblcols ) { $tblcols = $day; } if ( $period > $tblrows ) { $tblrows = $period; } push @{ $crs[$period][$day] }, $subjsec; } # Done filling 2D array. if ( $first ) { return; } # no timetable my $sth = $dbh->prepare("select description, grade from subject where subjsec = ?"); # print heading print qq{\n}; print qq{\n}; print qq{}; for (1..$tblcols){ print qq{}; } print qq{\n}; # Main body of table. for $i (1..$periods){ # was tblrows print qq{}; for (1..$tblcols){ if ($crs[$i][$_]){ # if we have a subjsec value; my $firstflag = 1; my @subjval = @{ $crs[$i][$_] }; print qq{}; } else { print qq{}; } } print qq{\n}; } print qq{
Grade $grade - }; print qq{$lex{Term} $term
$lex{Day} $_
$lex{Per} $i}; foreach my $sv (@subjval){ if ( not $firstflag){ print qq{
};} else { $firstflag = 0;} $sth->execute($sv); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my ($desc,$gr) = $sth->fetchrow; print qq{$desc - Gr$gr/$sv}; } print qq{		$lex{'No Value'}
\n}; } # end of term loop return 1; # timetable found } # end of printGradeTimetable