#!/usr/bin/perl
# Copyright 2001-2024 Leslie Richardson / Michael Peters
# This file is part of Open Admin for Schools.
# Display the Timetables for Grades and Terms; select from timetable. Display locations
my $self = 'tt_report4.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',
'Grades' => 'Grades',
'Terms' => 'Terms',
'Not Found' => 'Not Found',
'Description' => 'Description',
'Sort by' => 'Sort by',
'Select' => 'Select',
'Continue' => 'Continue',
'Location' => 'Location',
);
$novalue = qq{
};
#$novalue = qq{
$lex{'No Value'}
}; # value for an empty cell in the 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);
# print page header.
my $title = "$lex{Timetable} $lex{Report} 4 - Course Locations";
print qq{$doctype\n$title\n};
print qq{\n};
print qq{$chartype\n\n};
print qq{[ $lex{Main} };
unless (getcwd() =~ /tcgi/){ # unless we are in tcgi
print qq{| $lex{Timetable} };
}
print qq{]\n};
print qq{
$title
\n};
if (not $arr{page} ) {
showStartPage();
} elsif ($arr{page} == 1 ) {
delete $arr{page};
showReport();
}
#-------------
sub showReport {
#-------------
# foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key} \n}; }
print qq{\n};
# Get the terms.
my %terms;
foreach my $key ( keys %arr ) {
my ($type,$val) = split(':', $key);
if ( $type eq 'term' ) {
$terms{$val} = 1;
}
}
if ( not %terms ) { # All Terms
my $sth = $dbh->prepare("select distinct term from schedat
where term is not NULL and term != '' order by term");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my $trm = $sth->fetchrow ) {
$terms{$trm} = 1;
}
}
my @grades = split(/\s+/, $arr{grades} );
if ( not @grades ) { # all grades.
my $sth = $dbh->prepare("select distinct grade from student where grade is not NULL and grade != ''");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my $grd = $sth->fetchrow ) {
push @grades, $grd;
}
# print qq{
$lex{Grades} $lex{'Not Found'}
\n};
# print qq{\n};
# exit;
}
@grades = sort {$a <=> $b} @grades;
# print "Grades:@grades Terms:", %terms, " \n";
# Get all of the locations / rooms
my (@loctot, %loctot); # total locations in the school
my $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 ) {
$loctot{$loc} = 1;
}
@loctot = sort keys %loctot;
# Get all courses in grades;
my %courses;
my $sth = $dbh->prepare("select * from subject where grade = ?");
my $sth1 = $dbh->prepare("select count(*) from eval where subjcode = ?");
foreach my $grade ( @grades ) {
$sth->execute($grade);
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
while ( my $ref = $sth->fetchrow_hashref ) {
$courses{$ref->{subjsec}} = $ref;
# Get course enrollment record count
$sth1->execute($ref->{subjcode});
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
my $count = $sth1->fetchrow;
$ref->{count} = $count;
}
}
my $first = 1;
foreach my $term ( sort keys %terms ) {
if ( not $first ) {
print qq{\n};
} else { $first = 0; }
printTimetable( $term, \%courses, $arr{sort}, $arr{grades});
}
# print qq{\n};
print qq{