#! /usr/bin/perl # Copyright 2001-2022 Leslie Richardson # This file is part of Open Admin for Schools. 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; my $self = 'rptbusroute2.pl'; # Basic Constants my $logfile = "pdflog$$.txt"; my $shortname = "busrpt2$$"; my $filename = "$shortname.tex"; my $maxlines = 45; # 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'} 2 - Alphabetical by Family"; 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, maxstudents # Create a hash with family name, and then student number array. my %family; # family{altNameField}[studnum]; my $sth1 = $dbh->prepare("select studnum from student where $altNameField = ? order by lastname, firstname"); my $sth = $dbh->prepare("select distinct $altNameField from student where $altNameField is not null and $altNameField != ''"); $sth->execute; if ($DBI::errstr){ print $DBI::errstr; die; } while ( my $famname = $sth->fetchrow ) { $sth1->execute($famname); if ($DBI::errstr){ print $DBI::errstr; die; } while ( my $studnum = $sth1->fetchrow ) { push @{ $family{$famname} }, $studnum; } } # Test #foreach my $famname ( sort keys %family ) { # my @studnum = @{ $family{$famname}}; # print qq{Family:$famname / @studnum
\n}; #} # 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. print TEX "\\center{{\\bf $title } - $currdate \\\\ \n"; # Heading print TEX "\\medskip\n\\begin{tabular}{|p{55mm}|p{60mm}|p{35mm}|p{25mm}|}\n"; print TEX "\\hline\n {\\rule[-1mm]{0pt}{14pt}\\bf Family Name} & {\\bf Children at that House Hr/Gr}"; print TEX "& {\\bf Bus Driver } & {\\bf House \\#}\\\\\n"; print TEX "\\hline\\end{tabular}\\\\ \n"; my $currname = -1; my $prevname; my $studcount; # Loop over family, then student records. my $first = 1; my $sth = $dbh->prepare("select lastname, firstname, grade, homeroom, busroute, $arr{groupFld} from student where studnum = ?"); foreach my $famname ( sort keys %family ) { $prevname = $currname; $currname = $famname; my $count = 1; print TEX "\\begin{tabular}{|p{55mm}|p{60mm}|p{35mm}|p{25mm}|}\n"; my $first = 1; foreach my $studnum ( @{ $family{$famname}} ) { # family/studnum loop # get student record $sth->execute($studnum); if ($DBI::errstr){ print $DBI::errstr; die; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; # LaTeX filter foreach my $val ( values %r ) { ( $val ) = latex_filter( $val ); } # Normal record printing. if ( $first ) { # print the family name, etc; full line. ($newfamname ) = latex_filter( $famname ); print TEX "\\raggedright $newfamname &"; print TEX "$r{firstname} $r{lastname} $r{homeroom}/$r{grade}"; print TEX "& $r{busroute} & $r{field8} \\\\ \n"; $first = 0; } else { # line with only student name print TEX "&$r{firstname} $r{lastname} $r{homeroom}/$r{grade} & & \\\\ \n"; } $linecount++; $studcount++; $count++; } # End of student loop if ( $studcount > $maxlines ) { # end current page and start a new one. # We'll print a new page header here print TEX "\\hline\\end{tabular}\\\\"; # New Page print TEX "\n\\newpage\n"; print TEX "\\center{{\\bf $schoolname $lex{'Bus Report'} 2} - $currdate \\\\ \n"; # Heading print TEX "\\medskip\\begin{tabular}{|p{55mm}|p{60mm}|p{35mm}|p{25mm}|}\n"; print TEX "\\hline\n {\\rule[-1mm]{0pt}{14pt}\\bf Family Name} "; print TEX "& {\\bf Children at that House Hr/Gr}"; print TEX "& {\\bf Bus Driver } & {\\bf House \\# } \\\\ \n\\hline\n"; print TEX "\\end{tabular}\\\\ \n"; $studcount = 0; } else { # end of this family print TEX "\\hline\\end{tabular}\\\\ \n"; } } # end of family names loop # print TEX "\\end{tabular}\\\\ \n"; 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; } # print qq{\n}; print qq{
\n}; print qq{
}; # start form. print qq{
\n}; print qq{\n}; print qq{Family Link Field
\n}; print qq{Maximum Students per Page
\n}; print qq{
\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}; print qq{\n}; exit; }