#! /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 Timetables' => 'Student Timetables', 'Main' => 'Main', '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 Student(s) Found' => 'No Student(s) Found', 'Error' => 'Error', 'Blank=All' => 'Blank=All', 'Band' => 'Band', 'Term' => 'Term', 'Period' => 'Period', 'Day' => 'Day', 'Separate with Spaces' => 'Separate with Spaces', 'Term(s)' => 'Term(s)', 'Font Size' => 'Font Size', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'Legal' => 'Legal', 'A4' => 'A4', 'Student Number' => 'Student Number', ); my $grayshade = '0.80'; my $maxchars = 10; # maximum characters for subject description (if not small desc field) my $logowidth = '20mm'; my $logofile = 'schoollogo.jpg'; my $self = "rptStudentPDF.pl"; # previously called 'rptstudenttimetable.pl' eval require "../../etc/admin.conf"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } eval require "../../lib/liblatex.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $maxlines = 28; my $shortname = "schedrpt$$"; my $filename = "$shortname.tex"; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; my $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; # print page header my $title = $lex{'Student Timetables'}; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} | \n}; print qq{$lex{Timetable} ]\n}; if ( not $arr{page} ) { showStartPage(); } delete $arr{page}; #foreach my $key ( sort keys %arr ) { print qq{K:$key VAL:$arr{$key}
\n}; } # Must have Group and Term if ( not $arr{group} or not $arr{term} ) { print qq{

Missing Group Value or Term

\n}; print qq{\n}; exit; } my %staffnames; # global my $sortorder = "lastname, firstname"; if ( $arr{sortorder} eq 'grade' ) { $sortorder = "grade, lastname, firstname"; } elsif ( $arr{sortorder} eq 'band' ) { $sortorder = "band, lastname, firstname"; } elsif ( $arr{sortorder} eq 'homeroom' ) { $sortorder = "homeroom, lastname, firstname"; } delete $arr{sortorder}; my $includelocation; if ( $arr{includelocation} ) { $includelocation = 1; } delete $arr{includelocation}; my @terms = split /\s/, $arr{term}; delete $arr{term}; if ( not @terms ) { print qq{

No Terms Found

\n}; print qq{\n}; exit; } my $select; if ( $arr{group} ) { if ( $arr{select} eq $lex{Grade} ) { # Find this grade; my $grp = $dbh->quote( $arr{group} ); $select = "where grade = $grp"; } elsif ( $arr{select} eq $lex{Homeroom} ) { my $grp = $dbh->quote( $arr{group} ); $select = "where homeroom = $grp"; } elsif ( $arr{select} eq $lex{Band} ) { my $grp = $dbh->quote( $arr{group} ); $select = "where band = $grp"; } elsif ( $arr{select} eq $lex{'Student Number'} ) { my $val = $dbh->quote( $arr{group} ); $select = "where studnum = $val"; } } # foreach my $key (sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # print qq{Sortorder: $sortorder Select: $select
\n}; # Now create an array, @students, to store student numbers of students to print my $sth = $dbh->prepare("select studnum from student $select order by $sortorder"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push @students, $studnum; } if ( @students ) { # We have students to print; setup LaTeX file. open(TEX,">$filename") || die $lex{'Cannot open tex file'}; my ( $papersize, $textwidth, $textheight ); if ( $arr{papersize} eq $lex{Letter} ) { $papersize = 'letterpaper'; $textwidth = $g_letterpaper_textwidth; $textheight = $g_letterpaper_textheight; } elsif ( $arr{papersize} eq $lex{Legal} ) { $papersize = 'legalpaper'; $textwidth = $g_legalpaper_textwidth; $textheight = $g_legalpaper_textheight; } elsif ( $arr{papersize} eq $lex{A4} ) { $papersize = 'a4paper'; $textwidth = $g_a4paper_textwidth; $textheight = $g_a4paper_textheight; } my $fontsize = $arr{fontsize}. 'pt'; print TEX "\\documentclass[$fontsize,$papersize, landscape]{article} \\usepackage{array,colortbl,multicol,graphicx,noto} \\pagestyle{empty} \\setlength{\\textwidth}{$textwidth} \\setlength{\\textheight}{$textheight} \\setlength{\\evensidemargin}{0mm} \\setlength{\\oddsidemargin}{0mm} \\setlength{\\hoffset}{-10mm} \\setlength{\\voffset}{-25mm} \\setlength{\\extrarowheight}{2pt} \\setlength{\\parindent}{0pt}\n"; print TEX "\\begin{document}\n"; } else { print qq{

$lex{'No Student(s) Found'}

\n}; print qq{\n}; exit; } 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 my $sth = $dbh->prepare("select lastname, firstname, homeroom, grade from studentall where studnum = ?"); foreach my $studnum ( @students ) { $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($ln,$fn, $hr, $gr) = $sth->fetchrow; $studname{$studnum} = qq{$fn $ln (Gr $gr/Hr $hr)}; } 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 my $noCount = 1; if ( %nocourse ) { print qq{
No Course Enrollments
\n}; foreach my $studnum ( sort keys %nocourse ) { print qq{$noCount. $studname{$studnum} ($studnum)
\n}; $noCount++; } } # 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. my $noCount = 1; if ( %notimetable ) { print qq{
No Timetable
\n}; foreach my $studnum ( sort keys %notimetable ) { print qq{$noCount. $studname{$studnum} ($studnum)
\n}; $noCount++; } } if ( $nocourse{$studnum} or $notimetable{$studnum} ) { print qq{

\n}; } # Now Do Student Timetables my %subtimes; # used for global subject storage; # max periods even if last one is blank. my $sth = $dbh->prepare("select max(period) from schedat"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $maxperiods = $sth->fetchrow; foreach my $studnum ( @students ) { if ( $nocourse{$studnum} or $notimetable{$studnum} ) { next; } prTimetable( $studnum, $term ); # do 1 term and 1 student.l } # End of Student Loop print TEX "\\end{document}"; close TEX; system("$pdflatex $filename >pdflog$$.txt"); system("mv $shortname.pdf $downloaddir"); system("mv pdflog$$.txt $downloaddir"); system("rm $shortname.*"); print qq{

\n}; print qq{$lex{'View/Download'} $lex{'Student Timetables'}

\n}; print qq{[ $lex{Timetable} |\n}; print qq{$lex{'View Log File'}\n}; print qq{ ]\n\n}; #-------------- sub prTimetable { #-------------- my $studnum = shift; # Get more student info my $sth = $dbh->prepare("select lastname, firstname, grade, homeroom, band, provnum from studentall where studnum = ?"); $sth->execute( $studnum ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname, $grade, $homeroom, $band, $provnum ) = $sth->fetchrow; ($band) = latex_filter($band); # Setup for Get Subject Information my $sth2 = $dbh->prepare("select description, smdesc, location, teacher from subject where subjsec = ?"); # Setup for Get Teacher Info my $sth3 = $dbh->prepare("select lastname, firstname, sal from staff where userid = ?"); # Now print the Timetable in TeX, heading first. print TEX "\\includegraphics[width=$logowidth]{../../etc/$logofile}\\quad\n"; print TEX "\\parbox[b]{150mm}{ {\\Large $schoolname}\n\n\n\n"; print TEX "{\\LARGE\\bf $firstname $lastname} ($provnum)\n\n"; print TEX "{\\bf $lex{Grade}} $grade\\\\\n"; if ( $homeroom ) { print TEX "{\\bf $lex{Homeroom}} $homeroom\\\\\n"; } if ( $band ) { print TEX "{\\bf $lex{Band}} $band\\\\\n"; } print TEX " }\n\n"; # end of parbox. print TEX "\\vspace{8mm}\n"; foreach my $term ( @terms ) { # Get their enrollment this term; could be repeated for multiple terms. $sth = $dbh->prepare("select distinct subjcode from eval where studnum = ? and term = ?"); $sth->execute( $studnum, $term ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my @timetable; my $maxdays = 0; my $sth1 = $dbh->prepare("select day, period from schedat where term = $term and subjsec = ? order by day, period"); while ( my $subjsec = $sth->fetchrow ) { if ( not $subtimes{$subjsec} ) { # don't have subject's timetable values # load each timetable record for this subject. $sth1->execute( $subjsec ) ; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } $subtimes{ $subjsec } = [ ]; # create while ( my ( $day, $period ) = $sth1->fetchrow ) { push @{ $subtimes{$subjsec} }, "$day:$period"; } } # Populate 2D day:period array; use global values for day and period. my @dayperiod = @{ $subtimes{$subjsec} }; foreach my $rec ( @dayperiod ) { my ( $day, $period ) = split ':', $rec; if ( $day > $maxdays ) { $maxdays = $day; } if ( $period > $maxperiods ) { $maxperiods = $period; } $timetable[$period][$day] = $subjsec; # since we iterate over periods first. } } if ( not $maxdays ) { next; } #$maxdays = 1; # override for Eagleview... print TEX "\\begin{tabular}{|c|"; for ( 1 .. $maxdays ) { print TEX "c|" } print TEX "}\\hline\n\n"; # Print Top Row here. print TEX "\\rowcolor[gray]{0.92}$lex{Term} $term / ". $lex{Day}; for my $day ( 1 .. $maxdays ) { print TEX " & $day "; } print TEX "\\\\ \\hline\n"; for my $period ( 1 .. $maxperiods ) { # $#timetable ) { print TEX $lex{Period}. " $period"; my %dayname; # day -> name for my $day ( 1 .. $maxdays ) { my $subjsec = $timetable[$period][$day]; # If no subject, skip to next if ( not $subjsec ) { print TEX "& "; next; } # Load subject information. $sth2->execute( $subjsec ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } # Get $description, $smdesc, $location, $teacher (userid) my $ref = $sth2->fetchrow_hashref; foreach my $key ( keys %$ref ) { ($ref->{$key}) = latex_filter($ref->{$key}); } my ( $description, $smdesc, $location, $teacher ) = ( $ref->{description}, $ref->{smdesc}, $ref->{location}, $ref->{teacher} ); if ( not $smdesc ) { $smdesc = substr( $description, $maxchars ); # defined at top. } my $teachername; if ( not $staffnames{$teacher} ) { # get teacher name. $sth3->execute( $teacher ); # userid if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ($lastname, $firstname, $sal) = $sth3->fetchrow; my $finitial = substr($firstname, 0, 1); $teachername = "$finitial.$lastname"; $staffnames{$teacher} = $teachername; } else { $teachername = $staffnames{$teacher}; } $dayname{$day} = $teachername; print TEX " & $smdesc ($subjsec)"; if ( $location and $includelocation ) { print TEX " $location"; } } print TEX "\\\\ \n"; # Now do the second line for same period, all days print TEX ' '; # below periods value for my $day ( 1 .. $maxdays ) { print TEX " & $dayname{$day}"; } print TEX "\\\\ \\hline\n"; } print TEX "\\end{tabular}\\hfill\n"; } # end of Term Loop print TEX "\n\\newpage\n"; # new line for next student. } # 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}; # Get default papersize my $papersize; if ( $defaultpapersize ) { $papersize = $defaultpapersize; $papersize =~ s/paper//; # strip off the 'paper' ending; $papersize = ucfirst( $papersize ); } # print sortorder and selection input form. print qq{

$lex{'Student Timetables'}

\n}; print qq{
\n}; print qq{\n}; # Sort print qq{\n}; print qq{\n}; print qq{\n}; # Group Type print qq{\n}; print qq{\n}; # Term(s) print qq{\n}; # Paper print qq{\n}; print qq{\n}; # Font print qq{\n}; print qq{\n}; # print qq{\n}; # print qq{\n}; print qq{}; print qq{\n}; print qq{\n}; print qq{
$lex{'Sort by'}
$lex{Select}\n}; # Group print qq{
$lex{'Term(s)'}}; print qq{\n}; print qq{$lex{'Separate with Spaces'}
$lex{'Paper Size'}
$lex{'Font Size'}
$lex{Orientation}
Include Room Locaton?
}; print qq{
\n}; print qq{\n}; exit; }