#! /usr/bin/perl # Copyright 2001-2020 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 = "rptstudenttimetable.pl"; # previously called 'rptschedule.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(); } 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; } print TEX "\\documentclass[$arr{fontsize},$papersize, landscape]{article} \\usepackage{array,colortbl,multicol,inputenc,graphicx,helvet} $a_latex_header \\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}; die $lex{'No Student(s) Found'}. "\n"; } # Now Do Student Timetables my %subtimes = (); # used for global subject storage; foreach my $studnum ( @students ) { 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; # 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; } $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 "l|" } 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 .. $#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 { #----------------- # 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}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{\n}; # print qq{\n}; # print qq{\n}; print qq{}; print qq{\n}; # Now do the term print qq{\n}; print qq{\n}; print qq{
$lex{'Sort by'}
$lex{Select}\n}; print qq{ $lex{'Blank=All'}
$lex{'Paper Size'}
$lex{'Font Size'}
$lex{Orientation}
Include Room Locaton?
$lex{'Term(s)'}}; print qq{\n}; print qq{$lex{'Separate with Spaces'}
}; print qq{
\n}; print qq{\n}; exit; }