#! /usr/bin/perl # Copyright 2001-2024 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', 'Continue' => 'Continue', ); 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}; if ( not $arr{page} ){ showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; findCollisions(); } #------------------ sub showStartPage { #------------------ # Get Terms and Grades to check for. my $grades; # grades with subject attendance foreach my $gr ( sort {$a <=> $b} keys %g_AttendanceEntryMethod ) { # print qq{GR:$gr Method:$g_AttendanceEntryMethod{$gr}
\n}; if ($g_AttendanceEntryMethod{$gr} eq 'subject' ) { $grades .= qq{$gr }; } } print qq{
\n}; print qq{\n}; print qq{\n}; # Grades print qq{}; print qq{\n}; # Terms print qq{\n}; print qq{\n}; # Continue print qq{\n}; print qq{
Grades to check \n}; print qq{Separate with Spaces
Terms to check }; print qq{Separate with Spaces
}; print qq{
\n\n}; exit; } #----------------- sub findCollisions { #----------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Check for missing values if ( not $arr{grades} or not $arr{terms} ) { print qq{

Error: Missing Grades or Terms

\n}; print qq{\n}; exit; } my @grades = split(/\s+/, $arr{grades} ); my @terms = split(/\s+/, $arr{terms} ); print qq{

Grades - @grades
Terms - @terms

\n}; # find courses in each grade and their terms in preparation my %coursegrade; # grade,term,subjsec; my $sth = $dbh->prepare("select description, subjsec,startrptperiod,endrptperiod from subject where grade = ?"); foreach my $grade ( @grades ) { $sth->execute($grade); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec, $startterm,$endterm ) = $sth->fetchrow ){ foreach my $trm ( $startterm..$endterm ) { $coursegrade{$grade}{$trm}{$subjsec} = $description; } } } # we now have course master information about all grades of interest. my %studname; my %course; # course{term}{subjsec} my %coursename; my %student; # studnum{studnum}{term}{course} # find courses in each grade and their terms in preparation my %coursegrade; # grade,term,subjsec; my $sth = $dbh->prepare("select description, subjsec,startrptperiod,endrptperiod from subject where grade = ?"); foreach my $grade ( @grades ) { $sth->execute($grade); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $description, $subjsec, $startterm,$endterm ) = $sth->fetchrow ){ foreach my $trm ( $startterm..$endterm ) { $coursegrade{$grade}{$trm}{$subjsec} = $description; } $coursename{$subjsec} = $description; } } # we now have course master information about all grades of interest. =head # Get the courses and term in schedule table (schedat) # my $sth = $dbh->prepare("select distinct subjsec, term from schedat"); my $sth = $dbh->prepare("select distinct subjsec,startrptperiod, endrptperiod from subject where grade > 6"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($subjsec,$sterm,$eterm) = $sth->fetchrow ) { foreach my $trm ($sterm..$eterm) { $course{$trm}{$subjsec} = 1; } } # test courses hash # foreach my $trm ( sort keys %course ) { # foreach my $subjsec ( sort keys %{$course{$trm}} ) { # print "
Term:Trm SUB:$subjsec
\n"; # } # } # Get the course names; my $sth = $dbh->prepare("select description from subject where subjsec = ?"); # was sasked_courses foreach my $t ( keys %course ) { foreach my $subjsec ( sort keys %{ $course{$t}} ) { my ($code,$section) = split('-', $subjsec); $sth->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $title = $sth->fetchrow; $coursename{$subjsec} = $title; if ( not $title ) { print "TITLE:$title Sub:$subjsec
\n"; } } } =cut # Get students in those courses, by term. my $sth = $dbh->prepare("select distinct studnum from eval where subjcode = ? and term = ?"); foreach my $grade ( sort keys %coursegrade ) { foreach my $term ( sort keys %{$coursegrade{$grade}} ) { foreach my $subjsec ( keys %{ $coursegrade{$grade}{$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 = ?"); my $sth2 = $dbh->prepare("select description from subject where subjsec = ?"); my $nocollisions = 1; 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{\n}; print qq{}; print qq{\n}; $first = 0; $nocollisions = 0; } print qq{}; print qq{}; print qq{\n}; } } } } # $end of this term; if ( not $first ) { print qq{
Name/GradeTermDayPeriodCourses
$studname{$studnum} ($studnum)$term$day$period}; foreach my $crs ( @{$timetable{$period}{$day}} ) { if ( not $coursename{$crs} ) { # get the title if course not scheduled. $sth2->execute($crs); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $title = $sth2->fetchrow; $coursename{$crs} = qq{$title UNSCH}; } print qq{$coursename{$crs} ($crs)
}; } print qq{
\n\n}; } } # end of this student; if ( $nocollisions ) { print qq{

No Collisions Found (Same Student, Same Period, 2 or more Courses)

\n}; } print qq{\n}; exit; } # end of findCollisions