#!/usr/bin/perl # Copyright 2001-2025 Leslie Richardson # This file is part of Open Admin for Schools. # Set the daybook timetable for homeroom teachers. # Note: we should likely have a setting for which timetable to use for this: highschool or homeroom. 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 Subjects' => 'Additional Subjects', '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 Subject' => 'Same Day/Period; More Than 1 Subject', 'No Terms' => 'No Terms', 'Course' => 'Course', 'Exists' => 'Exists', 'Skipping' => 'Skipping', 'Per' => 'Per', 'Current' => 'Current', 'User Id' => 'User Id', 'Password' => 'Password', 'Not Found' => 'Not Found', 'Cookie Duration' => 'Cookie Duration', 'Please Log In' => 'Please Log In', 'Set' => 'Set', 'Select' => 'Select', 'Add' => 'Add', 'Delete' => 'Delete', ); use DBI; use CGI; use CGI::Session; my $self = "timetableset.pl"; my $maxbackings = 6; # maximum number of backed classes per cycle. eval require "../../etc/admin.conf.root"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $q = CGI->new; my %arr = $q->Vars; my $dbtype = 'mysql'; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); my $sth = $dbh->prepare("select datavalue from conf_system where dataname = ?"); foreach my $var ( qw( doctype chartype charset tchcss tchpage g_MTrackTerm g_AttendanceEntryMethod g_DaysPerCycle)) { $sth->execute( $var ); my $datavalue = $sth->fetchrow; eval $datavalue; if ( $@ ) { print "$lex{Error}: $@
\n"; die "$lex{Error}: $@\n"; } } print $q->header( -charset, $charset ); my $userid = $ENV{'REMOTE_USER'}; # print page header. my $title = "$lex{Set} Homeroom $lex{Timetable}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ]\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: periods, various terms: term1, term2, etc. # parse passed values for termX elements. Extract X. my @terms; foreach my $key ( keys %arr ) { if ( $key =~ m/term(\d*)/ ) { push @terms,$1; } } my @terms = sort { $a <=> $b } @terms; if ( not @terms ) { print qq{

$lex{'No Terms'}

\n}; print qq{\n}; exit; } my $periods = $arr{periods}; if ( not $periods ) { print qq{

No Periods

\n}; print qq{\n}; exit; } # Load Courses $sth = $dbh->prepare("select description, smdesc, subjsec from subject where teacher = ?"); my (%course,%sort); $sth->execute($userid); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description,$smdesc, $subjsec ) = $sth->fetchrow ){ $sort{"$description$subjsec"} = $subjsec; $course{$subjsec} = $description; } my @course; # need a sorted list of courses for display foreach my $key ( sort keys %sort ) { push @course, $sort{$key}; } # Load Activities my (%activity,%asort, @activity); $sth = $dbh->prepare("select * from dbkactivity where userid = ?"); $sth->execute($userid); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ){ my %r = %$ref; $asort{"$r{title}$r{id}"} = $r{id}; $activity{$r{id}} = $ref; } foreach my $key ( sort keys %asort ) { push @activity, $asort{$key}; } # Print Teacher print qq{\n}; print qq{\n}; print qq{}; my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth->fetchrow; print qq{\n}; # Print Terms my $terms = join(',',@terms); print qq{\n} ; print qq{
$lex{Teacher}$firstname $lastname$lex{Terms}$terms
\n}; print qq{
}; print qq{$lex{Current} $lex{Timetable}
\n}; foreach my $trm ( @terms ) { printTimetable($userid,$trm ); } print qq{
\n}; my %current; # current{day}{period} = ( list of courses, activities (ACTIV-id) $sth = $dbh->prepare("select * from dbktimetable where userid = ?"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; push @{ $current{ $r{day} }{ $r{period} } }, $ref # course subjsec or a:id } # Print Main Layout Table print qq{
}; print qq{Add/Delete Timetable Entries
\n}; print qq{
}; 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. # 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 $per ( 1..$periods ) { print qq{}; for my $day ( 0..$days ) { if ($day == 0) { print qq{\n}; } else { print qq{\n}; } } print qq{\n}; } my $span = $days + 1; print qq{
$lex{Day} $d
$lex{Period} $per}; # display current courses/activities on this period. foreach my $ref ( @{ $current{$day}{$per} } ) { my %r = %$ref; my ($type,$id) = split('-', $r{courseact}); if ( $type eq 'ACTIV' ) { print qq{Activity $activity{ $id }->{title} Term $r{term}
\n}; print qq{$lex{Delete}
\n}; } else { # we have a course print qq{$course{$r{courseact}} ($r{courseact}) Term $r{term}\n}; print qq{$lex{Delete}
\n}; } } print qq{$lex{Add}
\n}; =head # Now do backings... print qq{

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

\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}
All Save Buttons work the same!\n}; =cut 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 Current Teacher Name (from userid) my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname ) = $sth->fetchrow; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Load current periods values, if any. # load staff record for this user and get periods field, then eval it. my $sth = $dbh->prepare("select dbkperiods from staff where userid = ?"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $periods = $sth->fetchrow; # print "PERIODS:$periods
\n"; eval $periods; if ( $@ ) { print $lex{Error}. " for Teacher Periods: $@
\n"; exit; } my $periodcount = keys %periods; print qq{}; print qq{\n}; print qq{\n}; print qq{
$lex{Select} $lex{Terms}
(for your timetable)		}; foreach my $trm ( sort {$a <=> $b} keys %terms ){ print qq{$lex{Term} $trm
\n}; } print qq{
$lex{'Number of Periods'} }; print qq{(from your set periods)
\n}; # Display Current Timetables print qq{
}; print qq{$lex{Current} $lex{Timetable}
\n}; foreach my $trm ( sort {$a <=> $b} keys %terms ){ printTimetable($userid,$trm ); } print qq{
\n}; print qq{
\n}; print qq{\n}; exit; } #---------------- sub updateRecords { # Write the records to dbktimetable table. #---------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Get Terms my @terms = split(',',$arr{terms}); # same for all delete $arr{terms}; # Delete timetable entries (Activity or Course/Term) my $sth = $dbh->prepare("delete from dbktimetable where id = ?"); foreach my $key ( keys %arr ) { my ( $type, $id ) = split(':', $key ); if ($type eq 'DEL' ) { $sth->execute( $id ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } delete $arr{$key}; } } # print qq{
After Terms and Deletes
\n}; # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Setup to Add timetable entries. my $sth = $dbh->prepare("insert into dbktimetable (userid, term, day, period, courseact ) values (?,?,?,?,?)"); my $sth1 = $dbh->prepare("select count(*) from dbktimetable where day = ? and period = ? and courseact = ? and term = ?"); my $sth2 = $dbh->prepare("select description from subject where subjsec = ?"); foreach my $key ( keys %arr ) { my ($day, $period) = split(':',$key ); if ( $day and $arr{$key} and @terms ) { # filled entry # print qq{K: $key V: $arr{$key} Day: $day Period: $period
\n}; my $courseact = $arr{$key}; foreach my $trm ( @terms ) { # Check if this record already exists; $sth1->execute( $day, $period, $courseact, $trm ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; if ( $count ) { # get description and skip $sth2->execute( $courseact ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $desc = $sth2->fetchrow; 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 } # print qq{D:$day P:$period S:$courseact T:$trm
\n}; $sth->execute( $userid, $trm, $day, $period, $courseact ); 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} - }; print qq{Missing Day value for $arr{$key}. Skipping!
\n}; } if ( not $arr{$pi} ) { print qq{
$lex{Error} - Missing }; print qq{Period 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}; 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 $sth2->execute( $subjsec ); if ( $DBI::errstr ){ print $DBI::errstr; die $DBI::errstr; } my $desc = $sth2->fetchrow; 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 } # 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{Contact} $adminname $adminemail

\n}; } print qq{

[ $lex{Timetable} ]

\n}; print qq{\n}; exit; } #----------------- sub printTimetable { # print timetable for one teacher for 1 term. #----------------- my ($teacher, $term ) = @_; # Get Teacher Name my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute( $teacher ); my ($lastname, $firstname) = $sth->fetchrow; # Get Subjects and Activities from dbktimetable. my $sth = $dbh->prepare("select day, period, courseact from dbktimetable where term = ? and userid = ? order by period, day"); $sth->execute( $term, $teacher ); my %courseact; # courseact{period}{day}{courseact} = 1; while ( my ($day, $period, $courseact) = $sth->fetchrow ) { # print "Day:$day Period:$period CRS:$courseact
\n"; $courseact{$period}{$day}{$courseact} = 1; } if ( not %courseact ) { # no timetable print qq{

No Timetable for Term $term
\n}; return; } my $sth = $dbh->prepare("select description from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select title from dbkactivity where id = ?"); # Find max days and periods for table. my ($maxperiod, $maxday); foreach my $period ( sort keys %courseact ) { foreach my $day ( sort keys %{ $courseact{$period} } ) { if ( $period > $maxperiod ) { $maxperiod = $period; } if ( $day > $maxday ) { $maxday = $day; } } } # print heading print qq{\n}; print qq{\n}; print qq{}; foreach my $day (1..$maxday){ print qq{}; } print qq{\n}; # Main body of table. for $period (1..$maxperiod){ print qq{}; foreach my $day (1..$maxday ) { if ( %{ $courseact{$period}{$day}} ) { print qq{}; } else { print qq{}; } } print qq{\n}; } print qq{
$firstname $lastname - }; print qq{$lex{Term} $term
$lex{Day} $day
$lex{Per} $period}; # Loop through each course/activity and print title/desc foreach my $crsact ( keys %{ $courseact{$period}{$day} } ) { # print "CRSACT:$crsact Day:$day Period:$period
\n"; my $desc; # description (course) or title (activity) my ($type, $id) = split('-', $crsact ); if ( $type eq 'ACTIV' ) { # an activity $sth1->execute( $id ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } $desc = $sth1->fetchrow; $desc = "ACT $desc"; } else { # a course (with course code) $sth->execute( $crsact ); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } $desc = $sth->fetchrow; $desc .= ($crsact); } print qq{$desc
\n}; } print qq{		$lex{'No Value'}
\n}; return; }