#! /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. use DBI; use CGI; my %lex = ('Student' => 'Student', 'Main' => 'Main', 'Timetables' => 'Timetables', 'Timetable' => 'Timetable', 'Cannot open tex file' => 'Cannot open tex file', 'View/Download' => 'View/Download', 'View Log File' => 'View Log File', 'Select' => 'Select', 'Lastname, Firstname' => 'Lastname, Firstname', 'Homeroom, Lastname, Firstname' => 'Homeroom, Lastname, Firstname', 'Grade, Lastname, Firstname' => 'Grade, Lastname, Firstname', 'Band, Lastname, Firstname' => 'Band, Lastname, Firstname', 'Sort by' => 'Sort by', 'Grade' => 'Grade', 'Homeroom' => 'Homeroom', 'Continue' => 'Continue', 'No Staff Found' => 'No Staff Found', 'Error' => 'Error', 'Blank=All' => 'Blank=All', 'Term' => 'Term', 'Period' => 'Period', 'Day' => 'Day', 'Separate with Spaces' => 'Separate with Spaces', 'Terms' => 'Terms', 'Missing' => 'Missing', 'Band' => 'Band', 'Student Number' => 'Student Number', ); my $self = "rptStudentTtb.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{$lex{Student} $lex{Timetable} Report 1}; 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(); # main options } elsif ($arr{page} == 1 ) { delete $arr{page}; selectStudents(); # students to print. } elsif ( $arr{page} == 2 ) { delete $arr{page}; showReport(); } #----------------- sub selectStudents { #----------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } if ( not $arr{term} ) { print qq{

No Terms

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

Terms:@terms

\n}; my $checked; if ( $arr{checknextpage} ) { $checked = qq{checked="checked"}; delete $arr{checknextpage}; } my ($grouptype, $groupval) = ($arr{grouptype}, $arr{groupval} ); delete $arr{grouptype}; delete $arr{groupval}; if ( not $groupval ) { print qq{

No Students Selected

\n}; print qq{\n}; exit; } # %arr values left are passed on # Set Sort Order my $sortorder; if ( $arr{sortorder} eq 'name' ) { $sortorder = "order by lastname, firstname"; } elsif ( $arr{sortorder} eq 'homeroom' ) { $sortorder = "order by homeroom, lastname, firstname"; } else { # must be grade $sortorder = "order by grade, lastname, firstname"; } # Start the form, pass values print qq{
\n}; print qq{\n}; foreach my $key ( keys %arr ) { print qq{\n}; } print qq{

\n}; # Passed: grouptype (ie. Grade,Homeroom) and groupval is the value. my $select; if ( $grouptype eq $lex{Grade} ) { # Find this grade; $select = "where grade = ?"; } else { $select = "where homeroom = ?"; } my $sth = $dbh->prepare("select lastname, firstname, grade, homeroom, studnum from student $select $sortorder"); my @groups = split(/\s+/, $groupval); my $first = 1; foreach my $grp ( @groups ) { $sth->execute( $grp ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; if ( $first ) { print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; $first = 0; } print qq{}; print qq{\n}; } } if ( $first ) { print qq{

No Students Found!

\n}; } else { print qq{
StudentGradeHomeroom
}; print qq{$r{lastname}, $r{firstname} ($r{studnum})$r{grade}$r{homeroom}
\n}; } print qq{

\n}; print qq{
\n}; print qq{\n}; exit; } #------------- sub showReport { #------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Outline: # Get terms and students in the grade/homeroom. # Get total courses for this group and those with no course enrollments. # Get courses by term if ( not $arr{term} ) { print qq{

No Terms

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

Terms:@terms

\n}; my @students; foreach my $key ( keys %arr ) { # student numbers. if ( $key =~ /\d/ ) { push @students, $key; } } my %studname; my %nocourse; # no course enrollments my %notimetable; # no timetable entries, though enrolled in courses. my %course; # course{subjsec} my %courseByTerm; # {term}{subjsec} my %coursename; # Get student names for these student numbers; %studname my (%sort, @sort); # these will be all students even those without course/ttb my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from studentall where studnum = ?"); # Create %sort hash; populate %studname foreach my $studnum ( @students ) { $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn, $hr, $gr) = $sth->fetchrow; if ( $arr{sortorder} eq 'name' ) { $sort{"$ln$fn$studnum"} = $studnum; } elsif ( $arr{sortorder} eq 'homeroom' ) { $sort{"$hr$ln$fn$studnum"} = $studnum; } else { # grade, lastname, firstname, studnum $sort{"$gr$ln$fn$studnum"} = $studnum; } $studname{$studnum} = qq{$fn $ln (Gr $gr/Hr $hr)}; } # test sort. #foreach my $key ( sort keys %sort ) { # print "K:$key VAL:$sort{$key}
\n"; #} # Get the course enrollments for each student, in order to get an overall list. my $sth = $dbh->prepare("select distinct subjcode from eval where studnum = ?"); foreach my $studnum ( @students ) { $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ecount = 0; while ( my $subjsec = $sth->fetchrow ) { $course{$subjsec} = 1; $ecount++; } if ( $ecount < 1 ) { $nocourse{$studnum} = 1 } } # we now have %course populated with all courses to check, and also have a %nocourse listing if ( %nocourse ) { print qq{
No Course Enrollments
\n}; foreach my $studnum ( sort keys %nocourse ) { print qq{$studname{$studnum} ($studnum)
\n}; } } # create a courseByTerm hash %term and %subjsec; loop over %course my $sth = $dbh->prepare("select description, startrptperiod, endrptperiod from subject where subjsec = ?"); foreach my $subjsec ( keys %course ) { $sth->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($desc,$startterm,$endterm) = $sth->fetchrow; $coursename{$subjsec} = $desc; foreach my $term ($startterm..$endterm ) { $courseByTerm{$term}{$subjsec} = $desc; } } foreach my $term ( @terms ) { # sort keys %courseByTerm ) { # these may be outside passed terms. foreach my $subjsec ( sort keys %{ $courseByTerm{$term}} ) { # print qq{T:$term SUB:$subjsec
\n}; } } print qq{
\n}; my $sth = $dbh->prepare("select distinct subjcode from eval where term = ? and studnum = ?"); my $sth1 = $dbh->prepare("select day,period from schedat where term = ? and subjsec = ?"); foreach my $studnum ( @students ) { if ( $nocourse{$studnum} ) { next; } foreach my $term ( @terms ) { my %ttb; # timetable for this term, this student # Loop over student courses for this term. $sth->execute( $term, $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth->fetchrow ) { # now find timetable entries for this course, this term. $sth1->execute( $term, $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ($day,$period) = $sth1->fetchrow ) { $ttb{$day}{$period}{$subjsec} = 1; } } # print qq{
SN:$studnum Term:$term
\n}; if ( not %ttb ) { $notimetable{$studnum} = 1; # print "No Timetable for term $term $studname{$studnum} ($studnum)
\n"; } else { foreach my $day ( keys %ttb ) { foreach my $period ( sort keys %{ $ttb{$day}} ) { foreach my $subjsec ( sort keys %{ $ttb{$day}{$period}} ) { # print qq{D:$day Per:$period Sub:$subjsec
\n}; } } } } } # end of term loop } # end of student loop # Display those without a timetable. if ( %notimetable ) { print qq{
No Timetable
\n}; foreach my $studnum ( sort keys %notimetable ) { print qq{$studname{$studnum} ($studnum)
\n}; } } if ( $nocourse{$studnum} or $notimetable{$studnum} ) { print qq{

\n}; } foreach my $key ( sort keys %sort ) { my $studnum = $sort{$key}; if ( $nocourse{$studnum} or $notimetable{$studnum} ) { next; } # get this student's Courses my %studentcourses; my $sth = $dbh->prepare("select distinct subjcode from eval where studnum = ?"); $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my $subjsec = $sth->fetchrow ) { $studentcourses{$subjsec} = 1; } # Setup for Get Subject Information my $sth2 = $dbh->prepare("select description, smdesc, location, teacher, grade from subject where subjsec = ?"); my $sth3 = $dbh->prepare("select count(distinct studnum) from eval where subjcode = ?"); print qq{

$studname{$studnum} ($studnum)

\n}; foreach my $term ( @terms ) { # Clean up global on later interations. foreach my $key ( keys %timetable ) { delete $timetable{$key}; } my %timetable; # timetable{period}{day}{subjsec} = 1 my $maxdays = 0; my $sth = $dbh->prepare("select * from subject where subjsec = ?"); my $sth1 = $dbh->prepare("select day, period from schedat where term = ? and subjsec = ? order by day, period"); my %periodmap; # stores the mapping values for period times; foreach my $subjsec ( keys %{ $courseByTerm{$term} } ) { if ( not $studentcourses{$subjsec} ) { next; } # doesn't take this. # Get Course Info $sth->execute( $subjsec ) ; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $cref = $sth->fetchrow_hashref; my %c = %$cref; # grade from course, or should be from student? $periodmap{ $g_PeriodMap{$c{grade}} } = 1; # the key has the map value. # load each timetable record for this course $sth1->execute( $term, $subjsec ) ; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } while ( my ( $day, $period ) = $sth1->fetchrow ) { if ( $day > $maxdays ) { $maxdays = $day; } push @{ $timetable{$period}{$day} }, $subjsec; # more than one course possible } } # Setup Period Times (into %periodTime{$period} = combined value. my @vals = keys %periodmap; my $mapvalue = pop @vals; my %periodTime; my %tempTime = %{ $g_PeriodTime{$mapvalue} }; foreach my $p ( keys %tempTime ) { my $start = $tempTime{$p}{s}; my $end = $tempTime{$p}{e}; my ($start,$end) = conv24to12($start,$end); my $val = qq{$start-$end}; $periodTime{$p} = $val; } # print qq{
MapValue:$mapvalue Periods:}, %periodTime, "
\n"; =head # Testing %timetable print qq{

Timetable Hash

\n}; foreach my $period (keys %timetable ) { foreach my $day ( keys %{ $timetable{$period} } ) { print qq{Day:$day Period:$period Courses:}, @{ $timetable{$period}{$day} }, qq{
\n}; } } =cut # if ( not %timetable ) { next; } # term # Start Table. print qq{\n}; # Print Top Row here. print qq{\n}; my $dayvalue; for my $day ( 1 .. $maxdays ) { if ( $g_DaysPerCycle == 5 or $g_DaysPerCycle eq 'W' or $g_DaysPerCycle eq 'w' ) { # weekly $dayvalue = $dow[$day + 1]; } else { $dayvalue = $day; } print qq{}; } print qq{\n}; my @periods = sort { $a <=> $b } keys %timetable; my $maxperiods = $periods[-1]; foreach my $period ( 1 .. $maxperiods ) { print qq{}; foreach my $day ( 1 .. $maxdays ) { print qq{}; } # end of day loop. print qq{\n}; } # end of periods loop print qq{
$lex{Term} $term / $lex{Day}$dayvalue
$lex{Period} $period
$periodTime{$period}		}; while ( my $subjsec = pop @{ $timetable{$period}{$day} } ) { # Load subject information. $sth2->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth2->fetchrow_hashref; %r = %$ref; if ( not $r{smdesc} ) { $r{smdesc} = substr( $r{description}, $maxchars ); # defined at top. } # Load course enrollment $sth3->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } $studcount = $sth3->fetchrow; # $arr{desctype} is either 'description' or 'smdesc' field my $coursedesc = $r{"$arr{desctype}"}; if ( length $coursedesc > $arr{descsize} ) { # truncate. $coursedesc = substr( $coursedesc, 0, $arr{descsize} ); } my $cellvalue = qq{$coursedesc
}; if ( $arr{includeSubjsec} ) { $cellvalue .= qq{ $subjsec }; } if ( $arr{includeStudcount} and $studcount ) { $cellvalue .= qq{ ($studcount)}; } if ( $arr{includeLocation} and $r{location} ) { $cellvalue .= qq{
$r{location}}; } print qq{$cellvalue
}; } print qq{
\n}; } # end of Term Loop if ( $arr{includePageBreak} ) { print qq{

\n}; } ## new page for next teacher. } # next userid return @skipped; } # end of prTimetable #----------------- sub showStartPage { #----------------- my (@homerooms, @grades ); # Get Homerooms my $sth = $dbh->prepare("select distinct homeroom from student where homeroom is not NULL and homeroom != ''"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $hr = $sth->fetchrow ) { push @homerooms, $hr; } @homerooms = sort {$a <=> $b} @homerooms; # Get Grades $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 $gr = $sth->fetchrow ) { push @grades, $gr; } @grades = sort {$a <=> $b} @grades; # print sortorder and selection input form. # print qq{

$lex{'Student Timetables'}

\n}; print qq{\n}; print qq{}; print qq{\n}; print qq{}; print qq{\n}; print qq{
Current Grades}; foreach my $gr ( @grades ) { print "$gr "; } print qq{
Current Homerooms}; foreach my $hr ( @homerooms ) { print qq{$hr }; } print qq{
\n}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # Now do the term(s) print qq{\n}; my $checked = qq{checked="checked"}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{'Sort by'}
$lex{Select}\n}; print qq{
$lex{Terms}}; print qq{\n}; print qq{$lex{'Separate with Spaces'}
}; print qq{}; print qq{ Include Course Code-Section
}; print qq{ Include Room # / Location
}; print qq{}; print qq{ Check Next Page
}; print qq{}; print qq{ Add Page Break after each Student
}; print qq{ Course Description Size
}; print qq{\n}; print qq{ Max Description Characters (for Full Description)
}; print qq{
\n}; print qq{\n}; exit; } #------------- sub conv24to12 { #------------- my @times; while ( my $time = shift ) { # if ( not $time ) { return } $time =~ s/\s+//g; # strip any spaces. my ($hr, $min) = split(':', $time); my $ampm = 'AM'; if ( $hr == 12 ) { $ampm = 'PM'; } if ( $hr == 0 ) { $hr = 12; }; if ( $hr > 12 ) { $hr -= 12; $ampm = 'PM'; } my $t = qq{$hr:$min$ampm}; push @times, $t; } return @times; }