#! /usr/bin/perl # Copyright 2001-2021 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. my %lex = ('Error' => 'Error', 'Main' => 'Main', 'Bus Route' => 'Bus Route', 'Students' => 'Students', 'Route' => 'Route', 'Name' => 'Name', 'Grade' => 'Grade', 'HRm' => 'HRm', 'Address' => 'Address', 'Ph' => 'Ph', 'Parent 1' => 'Parent 1', 'Parent 2' => 'Parent 2', 'View/Download' => 'View/Download', 'View Log File' => 'View Log File', 'Rm' => 'Rm', 'Report' => 'Report', 'Not Found' => 'Not Found', 'Family' => 'Family', 'Sort by' => 'Sort by', 'Continue' => 'Continue', ); use DBI; use CGI; # Basic Constants my $logfile = "pdflog$$.txt"; my $shortname = "busrpt$$"; my $filename = "$shortname.tex"; my $maxlines = 19; # Read config variables eval require "../etc/admin.conf"; if ( $@ ) { print $lex{Error}. ": $@
\n"; die $lex{Error}. ": $@\n"; } my $altNameField = $g_FamilyGroupNameField; # provides a family rather than a/b/c forms eval require "../lib/liblatex.pl"; if ( $@ ) { print $lex{Error}. " $@
\n"; die $lex{Error}. " $@\n"; } my $q = new CGI; my %arr = $q->Vars; print $q->header( -charset, $charset ); if ($arr{maxstudents}) { $maxlines = $arr{maxstudents}; } my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $mon++; $wday++; my $currdate = "$dow[$wday], $month[$mon] $mday, $year"; my $title = "$lex{'Bus Route'} $lex{Report}"; print qq{$doctype\n$title\n}; print qq{\n}; print qq{$chartype\n\n}; print qq{[ $lex{Main} ]\n}; print qq{

$title

\n}; if (not $arr{page} ) { showStartPage(); } else { delete $arr{page}; } # foreach my $key ( sort keys %arr ) { print qq{K:$key VAL:$arr{$key}
\n}; } # exit; # passed groupFld, sortorder my %bus; my $select = "where busroute is not NULL and busroute != ''"; $sth = $dbh->prepare("select distinct busroute from student $select order by busroute, lastname, firstname"); if ( $arr{sortorder} eq 'family' ) { # Assemble all families into bus driver hash # Get bus routes/drivers $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die; } while ( $busroute = $sth->fetchrow ) { my $ref = doFamily( $busroute ); # get a list of students ordered by family/name (fam:studnum) $bus{$busroute} = $ref; # array ref } } else { # end of family sorting; now normal sorting by name $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die; } while ( $busroute = $sth->fetchrow ) { my $ref = doName( $busroute ); # get a list of students ordered by name only $bus{$busroute} = $ref; # array ref } } # start of TeX setup open(TEX,'>', $filename) || die "Can't open tex file"; print TEX "\\documentclass[10pt,letterpaper]{article} \\pagestyle{empty} \\setlength{\\textwidth}{8in} \\setlength{\\textheight}{10.6in} \\setlength{\\hoffset}{-1.65in} \\setlength{\\voffset}{-1.4in} \\setlength{\\parindent}{0pt} \\setlength{\\tabcolsep}{3.5pt}\n"; print TEX "\\begin{document}\n"; # end of TEX setup. my $curroute = -1; my $oldroute; my $studcount; # Loop over all student records. my $first = 1; my $sth = $dbh->prepare("select * from student where studnum = ?"); foreach my $route ( sort keys %bus ) { $oldroute = $curroute; $curroute = $route; my $count = 1; print TEX "\\center{{\\bf $schoolname $lex{'Bus Report'}} - $currdate \\\\ \n"; print TEX "{\\bf $lex{Route}: $curroute } \\\\ \n"; print TEX "\\medskip\n\\begin{tabular}{|p{4.5cm}|p{4.0cm}|p{3.5cm}|p{3.5cm}|p{3.5cm}|}\n"; print TEX "\\hline\n {\\bf $lex{Name}} & {\\bf $lex{Grade}/$lex{HRm} }"; print TEX "& {\\bf $lex{Address}/$lex{Ph} } & {\\bf $lex{'Parent 1'} }"; print TEX "& {\\bf $lex{'Parent 2'}} \\\\ \n\\hline\n"; foreach my $val ( @{$bus{$route}} ) { # student/family loop my ( $fam,$studnum ) = split(':', $val); if ( not $studnum ) { # only studnum passed not fam:studnum ) $studnum = $fam; undef $fam; } # get student record $sth->execute($studnum); if ($DBI::errstr){ print $DBI::errstr; die; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; foreach my $val ( values %r ) { ( $val ) = latex_filter( $val ); } #remove any accidental spaces from the front or end of the route # $route =~ s/^\s+//; # $route =~ s/\s+$//; if ( $linecount > $maxlines ) { # end current page and start a new one. # We'll print a new page header here print TEX "Students: $studcount & & & & \\\\ \\hline"; print TEX "\\end{tabular}\\\\"; print TEX "\n\\newpage\n"; print TEX "\\center{{\\bf $schoolname $lex{'Bus Report'}} - $currdate \\\\ \n"; print TEX "{\\bf $lex{Route}: $curroute } \\\\ \n"; print TEX "\\medskip\n\\begin{tabular}{|p{4.5cm}|p{4.0cm}|p{3.5cm}|p{3.5cm}|p{3.5cm}|}\n"; print TEX "\\hline\n {\\bf $lex{Name}} & {\\bf $lex{Grade}/$lex{HRm} }"; print TEX "& {\\bf $lex{Address}/$lex{Ph} } & {\\bf $lex{'Parent 1'} }"; print TEX "& {\\bf $lex{'Parent 2'}} \\\\ \n\\hline"; $linecount = 0; $studcount = 0; } # End of New Page Header # Normal record printing. print TEX "{\\bf $r{lastname}, $r{firstname} $r{middlename} } & Grade: $r{grade} & "; print TEX qq{$r{address1} & $r{par1_firstname} $par1{lastname} &}; print TEX "$r{par2_firstname} $r{par2_lastname} \\\\ \n"; # Second Line print TEX "\\raggedright{$r{$altNameField}} &$lex{Rm}: $r{homeroom} & $lex{Ph}: $r{hphone1} "; print TEX "& \\\\ \\hline \n"; $linecount++; $studcount++; $count++; } # End of student loop # Now end the partial page print TEX "Students: $studcount & & & & \\\\ \\hline"; print TEX "\\end{tabular}\\\\\n"; print TEX "\\newpage\n\n"; $studcount = 0; $linecount = 0; } # end of bus driver route print TEX "\\end{document}"; close TEX; system("$pdflatex $filename > $logfile"); system("mv $shortname.pdf $downloaddir"); system("mv $logfile $downloaddir"); system("rm -f $shortname.*"); print qq{

\n}; print qq{
\n}; print qq{[ $lex{'View/Download'} $title ]
\n}; print qq{
[ $lex{'View Log File'} | }; print qq{$lex{Main} ]
\n}; print qq{\n}; #---------------- sub showStartPage { #---------------- # Display Current Busroute numbers; my $sth = $dbh->prepare("select distinct busroute, count(busroute) from student group by busroute order by busroute"); $sth->execute; if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my $first = 1; while ( my ( $busroute, $count ) = $sth->fetchrow ) { if ( $first ) { print qq{\n}; print qq{\n}; $first = 0; } print qq{\n}; } if ( not $first ) { print qq{
Bus RoutesCount
$busroute$count
\n}; } else { print qq{

No Bus Routes found

\n}; print qq{\n}; exit; } # Create meta hash for fieldnames from meta. my $sth = $dbh->prepare("select fieldid, fieldname from meta where tableid = ?"); $sth->execute( 'student' ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my %meta; while ( my ( $fieldid, $fieldname, $required ) = $sth->fetchrow ) { $meta{$fieldid} = $fieldname; } # start form. print qq{
\n}; print qq{\n}; print qq{\n}; # Group: grade or homeroom; # print qq{\n}; # print qq{\n}; # Sorting print qq{
\n}; print qq{
}; print qq{$lex{'Sort by'}\n}; print qq{
\n}; print qq{If sorting by family, Family Link Field
\n
\n}; # Also allow for an edit of the family values print qq{
}; print qq{Edit Family Fields that join Students into Families/Provide unified Family Name \n}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{ (new tab)
\n}; # Reset Bus Routes print qq{
}; print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{ (new tab)
\n}; print qq{
\n}; # Edit only the list value # print qq{\n}; # print qq{\n}; # print qq{\n}; # print qq{\n}; # print qq{\n}; print qq{\n}; exit; } #--------- sub doName { # find students on a bus route, sort by name #--------- # passed a bus driver value # find all students within and return array of students my $busroute = shift; my @studnums; my $sth = $dbh->prepare("select studnum from student where busroute = ? order by lastname, firstname"); $sth->execute( $busroute ); if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; } while ( my $studnum = $sth->fetchrow ) { push@studnums, $studnum; } return \@studnums; } #------------ sub doFamily { # find students on a route sort by family, then name. #------------ # passed a bus driver value # find all families within and return array of students, plus any errors my $busroute = shift; my @studnums; my $groupFld = $arr{groupFld}; # find all families within this route, check for errors (same family, different route) # Find all family groups within this busdriver my $sth = $dbh->prepare("select distinct $groupFld from student where $groupFld != '' and $groupFld is not null and busroute = ? order by $groupFld"); my $sth1 = $dbh->prepare("select studnum, lastname, firstname from student where $groupFld = ? and busroute = ? order by lastname, firstname"); # print qq{
Family Busroute $busroute
\n}; my $count = 1; $sth->execute( $busroute ); if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; } while ( my $family = $sth->fetchrow ) { $sth1->execute( $family, $busroute ); if ($DBI::errstr) {print $DBI::errstr; die $DBI::errstr; } while ( my ($studnum,$lastname,$firstname) = $sth1->fetchrow ) { # print qq{
$count. FAM:$family STUD:$studnum - $lastname,$firstname
\n}; $count++; push @studnums, qq{$family:$studnum}; } } # Check for errors, students outside busroute in same family return \@studnums; }
$lex{'Select by'}\n}; # print qq{ $lex{'Blank=All'}