#!/usr/bin/perl
# Copyright 2001-2024 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.
# Display the Timetables for Grades and Terms
# Note: We get courses by grade based on enrollments in courses for
# grade students rather than using the grade value in the course
# record. (ie. subject table)
my $self = 'tt_report2.pl';
my %lex = ('View' => 'View',
'Main' => 'Main',
'Timetable' => 'Timetable',
'No Records Found' => 'No Records Found',
'Grade' => 'Grade',
'Term' => 'Term',
'Delete Entries' => 'Delete Entries',
'No Value' => 'No Value',
'Period' => 'Period',
'Day' => 'Day',
'Term' => 'Term',
'Error' => 'Error',
'Course' => 'Course',
'Missing' => 'Missing',
'Enrollments' => 'Enrollments',
'Report' => 'Report',
);
use DBI;
use CGI;
use Cwd;
# Set prepath for config file: (/tcgi or cgi/schedule)
my $prepath = '../..';
if (getcwd() =~ /tcgi/){ # we are in tcgi
$prepath = '..';
}
eval require "$prepath/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;
# Get current dir so know what CSS to display;
if (getcwd() =~ /tcgi/){ # we are in tcgi
$css = $tchcss;
$homepage = $tchpage;
}
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
my ($select, $term);
if ($arr{term} =~ m/\d/) { # must be a digit
$term = $arr{term};
my $trm = $dbh->quote($arr{term});
$select = "where term = $trm";
}
# print page header.
my $title = "$lex{Timetable} $lex{Report} 2";
print qq{$doctype\n$title
$chartype\n\n};
print qq{[ $lex{Main} };
unless (getcwd() =~ /tcgi/){ # unless we are in tcgi
print qq{| $lex{Timetable} };
}
print qq{]\n};
print qq{\n};
my (%studcourse, %course, %timetable, %noenrol, %nottbl, %grades);
my $sth1 = $dbh->prepare("select distinct term, count(*) from schedat where subjsec = ?");
my $sth2 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?");
my $sth3 = $dbh->prepare("select description, grade, startrptperiod, endrptperiod from subject where subjsec = ?");
# Find courses of this student;
my $sth4 = $dbh->prepare("select distinct subjcode from eval where studnum = ?");
# Get grade, studnum from all current students.
my $sth = $dbh->prepare("select distinct grade, studnum from student");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
# NOTE: Getting grade from student, not value in course, since they
# may not be in same grade as course normally taught in.
while ( my ($grade, $studnum ) = $sth->fetchrow ) {
# Get his/her courses
$sth4->execute($studnum);
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
while ( my $subjsec = $sth4->fetchrow ) {
$studcourse{$grade}{$subjsec} = 1;
}
}
#print qq{Student Course}, %studcourse, " \n};
# Now Loop through all grades, then all subjects.
foreach my $grade ( sort {$a <=> $b} keys %studcourse ) {
print qq{
$lex{Grade} $grade
\n};
my (%gcourse, %courseorder, $maxterm);
foreach my $subjsec ( keys %{ $studcourse{$grade}} ) {
# Get Course Info
$sth3->execute($subjsec);
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my ( $desc, $grade, $startterm, $endterm ) = $sth3->fetchrow;
if ( $endterm > $maxterm ) { $maxterm = $endterm; }
# Put into data struct
$gcourse{$subjsec}{desc} = $desc;
$gcourse{$subjsec}{start} = $startterm;
$gcourse{$subjsec}{end} = $endterm;
$gcourse{$subjsec}{grade} = $grade;
$courseorder{"$desc$subjsec"} = $subjsec; # order of courses for display;
};
my @sorted = map { $courseorder{$_} } sort {$a <=> $b} keys %courseorder;
foreach my $term (1..$maxterm) {
printTimetable($grade, $term, \%gcourse, \@sorted);
}
print qq{\n};
} # end of grade loop
print qq{\n};
#-----------------
sub printTimetable { # print timetable for 1 grade for 1 term.
#-----------------
my ($grade, $term, $courseref, $sortref) = @_;
my %course = %$courseref;
my @sortedcourse = @$sortref;
my (%tb, $rows, $cols );
# print qq{[ $lex{'Delete Entries'} ]\n};
my $sth = $dbh->prepare("select day,period from schedat where term = ? and subjsec = ? order by period, day");
foreach my $subjsec ( @sortedcourse ) {
# Get Timetable values
$sth->execute( $term, $subjsec );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
while ( my ($day,$period) = $sth->fetchrow ) {
# print qq{S:$subjsec D:$day P:$period \n};
if ( $period > $rows ) { $rows = $period; }
if ( $day > $cols ) { $cols = $day; }
push @{ $tb{$period}{$day} }, $subjsec;
}
}
$sth = $dbh->prepare("select description from subject where subjsec = ?");
if ( %tb ) {
print qq{
\n};
print qq{
$lex{Term} $term
\n};
# heading
print qq{
};
for my $day ( 1 .. $cols ){ print qq{
$lex{Day} $day
}; }
print qq{
\n};
# Main body of table.
for my $i ( 1 .. $rows ) {
print qq{
$lex{Period} $i
};
for ( 1 .. $cols ) {
if ( $tb{$i}{$_} ){ # if we have a course code
my $first = 1;
@subjval = @{ $tb{$i}{$_} };
print qq{
};
foreach my $sv ( @subjval ) {
if (not $first){ print qq{ }; } else { $first = 0;}
$sth->execute($sv);
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr;}
my $desc = $sth->fetchrow;
print qq{$desc ($sv)};
}
print qq{