#! /usr/bin/perl
# Copyright 2001-2023 Leslie Richardson
# This file is part of Open Admin for Schools.
# Find students with 'collisions' where they are scheduled into more
# than one course in the same day and period.
# 1. Find all courses in the schedat table and their terms. (schedule)
# %course{term}{course} = 1;
# 2. Find all students in those courses. student{studnum}{term}{course};
# 3. Loop through each student, for each term, get their courses, and populate the timetable.
# %timetable{$period}{$day} = @course codes.
use DBI;
use CGI;
my %lex = ('Main' => 'Main',
'Timetable' => 'Timetable',
'Error' => 'Error',
);
my $self = "ttCollision.pl";
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. " $@ \n";
die $lex{Error}. " $@\n";
}
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;
# print page header
my $title = qq{Find Student Timetable Collisions};
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};
findCollisions();
#-----------------
sub findCollisions {
#-----------------
# foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key} \n"; }
my %studname;
my %course; # course{term}{subjsec}
my %coursename;
my %student; # studnum{studnum}{term}{course}
# Get the courses and term in schedule table (schedat)
my $sth = $dbh->prepare("select distinct subjsec, term from schedat");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my ($subjsec,$term) = $sth->fetchrow ) {
$course{$term}{$subjsec} = 1;
}
# Get the course names;
my $sth = $dbh->prepare("select title from sasked_courses where code = ?");
foreach my $t ( keys %course ) {
foreach my $subjsec ( keys %{ $course{$t}} ) {
my ($code,$section) = split('-', $subjsec);
$sth->execute( $code );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my $title = $sth->fetchrow;
$coursename{$subjsec} = $title;
}
}
# Get students in those courses, by term.
my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ? and term = ?");
foreach my $term ( sort keys %course ) {
foreach my $subjsec ( keys %{ $course{$term}} ) {
$sth->execute( $subjsec,$term );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my $studnum = $sth->fetchrow ) {
$student{$studnum}{$term}{$subjsec} = 1;
# print "T:$term S:$subjsec SN:$studnum\n";
}
}
}
# Get student name info, populate %sort
my %sort;
my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from studentall
where studnum = ?");
# Create %sort hash; populate %studname
foreach my $studnum ( keys %student ) {
$sth->execute( $studnum );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
my ($ln,$fn, $hr, $gr) = $sth->fetchrow;
$sort{"$gr$ln$fn$studnum"} = $studnum;
$studname{$studnum} = qq{$fn $ln (Gr $gr)};
}
# test sort.
# foreach my $key ( sort keys %sort ) {
# print "K:$key VAL:$sort{$key} \n";
# }
# Loop over all students, looking for collisions.
my $sth = $dbh->prepare("select period,day from schedat where term = ? and subjsec = ?");
my $sth1 = $dbh->prepare("select subjsec from schedat where term = ? and day = ? and period = ?");
foreach my $key (sort keys %sort ) {
my $studnum = $sort{$key};
my $first = 1;
foreach my $term ( sort keys %{ $student{$studnum}} ) {
# clear timetable hash. - only for 1 student for 1 term.
foreach (keys %timetable) {
delete $timetable{$_};
}
foreach my $subjsec ( keys %{ $student{$studnum}{$term}} ) {
# add this course to the timetable of this student.
$sth->execute( $term, $subjsec );
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my ( $period,$day ) = $sth->fetchrow ) {
push @{ $timetable{$period}{$day} }, $subjsec;
# print qq{P:$period D:$day CRS:$subjsec\n};
}
}
# Timetable now fully populated for this student, this term.
# Check for an array bigger than 1 for this course.
foreach my $period ( sort keys %timetable ) {
foreach my $day ( sort keys %{ $timetable{$period}} ) {
my $val = scalar @{ $timetable{$period}{$day}};
# print @{ $timetable{$period}{$day}}, qq{ CRS/\n};
# print qq{VAL:$val /T:$term /SN:$studnum\n};
if ( $val > 1 ) {
# start the table,
if ( $first ) {
print qq{