#!/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 $self = 'tt_report1.pl'; my %lex = ('View' => 'View', 'Main' => 'Main', 'Timetable' => 'Timetable', 'No Record(s) Found' => 'No Record(s) 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', 'Enrol' => 'Enrol', ); 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 = qq{$lex{View} $lex{Timetable} - Student Course Enrollments}; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} \n}; unless (getcwd() =~ /tcgi/){ # unless we are in tcgi print qq{| $lex{Timetable} \n}; } print qq{]\n}; print qq{

$title

\n}; print qq{
\n}; my (%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 = ?"); # Loop through all courses in subject table. my $sth = $dbh->prepare("select subjsec, description, grade, startrptperiod, endrptperiod from subject"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $maxterm = 1; # max term value for looping through timetable entries. while ( my ( $subjsec, $desc, $grade, $startterm, $endterm ) = $sth->fetchrow ) { if ( $endterm > $maxterm ) { $maxterm = $endterm; } # store course info in hash. $course{$subjsec}{desc} = $desc; $course{$subjsec}{start} = $startterm; $course{$subjsec}{end} = $endterm; $course{$subjsec}{grade} = $grade; $grades{$grade} = 1; # track which grades we have info for. # Loop through Timetable data checking for number of entries. $sth1->execute($subjsec); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $tfirst = 1; while ( my ($term, $tcount) = $sth1->fetchrow ) { $timetable{$subjsec}{$term} = $tcount; $tfirst = 0; } if ( $tfirst ) { $nottbl{$subjsec} = 1; } # track if no timetable done. # Enrollment Count $sth2->execute($subjsec); if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; } my $ecount = $sth2->fetchrow; if (not $ecount ) { $noenrol{$grade}{$subjsec} = 1; # track if no student enrollments } else { $course{$subjsec}{enrol} = $ecount; } } # Loop Through Each Grade, and Display Courses and Timetable Values. my $count = 1; foreach my $grade ( sort {$a <=> $b} keys %grades ) { print qq{\n}; print qq{\n}; print qq{\n}; print qq{}; foreach my $trm ( 1..$maxterm ) { print qq{}; } print qq{\n}; # Find courses in this grade and alphabetize, skip any without enrollments. my %sort; # ie. grade courses foreach my $subjsec ( keys %course ) { if ( $noenrol{$grade}{$subjsec} ) { next; } # skip no enrollments. # print qq{Subjsec:$subjsec - $course{$subjsec}{grade}
\n}; if ( $course{$subjsec}{grade} eq $grade ) { my $desc = $course{$subjsec}{desc}; $sort{"$desc$subjsec"} = $subjsec; } } # Now use this %sort hash to go through this grades courses foreach my $key ( sort keys %sort ) { my $subjsec = $sort{$key}; print qq{}; foreach my $trm ( 1..$maxterm ) { print qq{}; } print qq{\n}; } print qq{
$lex{Grade} $grade
$lex{Course}$lex{Enrol}}; print qq{Term / Timetable Rec
$trm
$course{$subjsec}{desc} ($subjsec)}; print qq{$course{$subjsec}{enrol}$timetable{$subjsec}{$trm}
\n}; # Display No Enrollments for this grade if ( %{ $noenrol{$grade}} ) { my %sortem; foreach my $subjsec ( keys %{ $noenrol{$grade} } ) { my $desc = $course{$subjsec}{desc}; my $trm = "$course{$subjsec}{start}-$course{$subjsec}{end}"; $sortem{"$trm$desc$subjsec"} = $subjsec; } print qq{\n}; print qq{\n}; print qq{\n}; # foreach my $subjsec ( keys %{ $noenrol{$grade} } ) { foreach my $key ( sort keys %sortem ) { my $subjsec = $sortem{$key}; print qq{}; print qq{\n}; } print qq{
Courses without $lex{Enrollments}
$lex{Course}$lex{Term}
$course{$subjsec}{desc} ($subjsec)$course{$subjsec}{start}-$course{$subjsec}{end}
\n}; print qq{
\n}; $count++; } $count++; if ( $count % 3 == 0 ) { print qq{
\n}; } # print qq{
\n}; print qq{

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