#! /usr/bin/perl # Copyright 2001-2011 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 = ('Main' => 'Main', 'No Student(s) Found' => 'No Student(s) Found', 'View/Download' => 'View/Download', 'Textbook' => 'Textbook', 'Records' => 'Records', 'Report' => 'Report', 'View Log File' => 'View Log File', 'Continue' => 'Continue', 'Homeroom' => 'Homeroom', 'Grade' => 'Grade', 'Blank=All' => 'Blank=All', 'Select' => 'Select', 'Students' => 'Students', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'Legal' => 'Legal', 'A4' => 'A4', 'Font Size' => 'Font Size', 'Error' => 'Error', 'Fees' => 'Fees', 'Sort by' => 'Sort by', 'In' => 'In', 'Out' => 'Out', 'All' => 'All', 'Not Found' => 'Not Found', 'Copies' => 'Copies', 'Student' => 'Student', 'Barcode' => 'Barcode', 'Id' => 'Id', 'Show' => 'Show', 'without' => 'without', 'Status' => 'Status', 'Sign Out' => 'Sign Out', ); my $self = 'tb_report.pl'; use DBI; use CGI; use Cwd; 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 $q = new CGI; print $q->header( -charset, $charset ); my %arr = $q->Vars; my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $wday++; $mon++; my $currdate = "$dow[$wday], $month[$mon] $mday, $year"; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); # Get current dir so know what CSS to display; if (getcwd() =~ /tcgi/){ # we are in tcgi $css = $tchcss; $homepage = $tchpage; } my $title = "$lex{Textbook} $lex{Report}"; print "$doctype\n$title\n"; print "\n"; print "$chartype\n\n"; print "[ $lex{Main} |\n"; print " $lex{Fees} ]\n"; print "

$title

\n"; if ( not $arr{page} ) { showStartPage(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; printReport(); } #---------------- sub showStartPage { #---------------- print "

\n"; print "\n"; # Start Table print "\n"; # Continue print "\n"; # Sort order print "\n"; # Textbook Records to print: All, In, Out print "\n"; # Student Records to print print "\n"; # Records without Copies print "\n"; # Paper Size print "\n"; # Font Size print "\n"; # Continue / Submit button print "\n"; print "

	"; print "
$lex{'Sort by'}	"; print "
$lex{Textbook} $lex{Records}	"; print "
$lex{Student} $lex{Records}	"; print " \n"; print "\n"; print "$lex{'Blank=All'}
$lex{Show} $lex{Records} $lex{without} $lex{Copies}	"; print "
". $lex{'Paper Size'}. "	"; print "
". $lex{'Font Size'}. "	\n"; print "

\n"; exit; } #-------------- sub printReport { #-------------- # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}
\n"; } # Title setup: lookup either way my %title = (); my %id2title = (); my $sth = $dbh->prepare("select id, title, author, pubdate from book_title"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($id, $title, $author, $pubdate ) = $sth->fetchrow ) { $title{"$title ($pubdate)"} = $id; $id2title{$id} = "$title ($pubdate)"; } # TeX Setup my $fontsize = $arr{fontsize}. 'pt'; # Set Paper Size, text width and height 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; } delete $arr{papersize}; # no longer needed. # Filter for Passed values that might crash LaTeX foreach my $key ( keys %arr ) { ( $arr{$key} ) = latex_filter( $arr{$key} ); } my $shortname = "tbreport$$"; my $filename = "$shortname.tex"; open(TEX,">$filename") || die "Can't open tex file"; print TEX "\\documentclass[$fontsize,$papersize ]{article} \\usepackage{array,fancyhdr,colortbl,inputenc} $a_latex_header \\pagestyle{empty} \\setlength{\\textwidth}{$textwidth} \\setlength{\\textheight}{$textheight} \\setlength{\\hoffset}{-36mm} \\setlength{\\voffset}{-30mm} \\addtolength{\\evensidemargin}{0mm} \\addtolength{\\oddsidemargin}{0mm} \\setlength{\\tabcolsep}{1mm} \\setlength{\\extrarowheight}{2mm} \\pagestyle{fancy} \\lhead{$schoolname -- $schoolyear} \\rfoot{ $currdate }\n"; print TEX "\\begin{document}\n"; print TEX "{\\Large\\bf $title}\n\n"; if ( $arr{sort} eq 'textbook' ) { # textbook report my $sth = $dbh->prepare("select id, barcode from book_copy where titleid = ? order by barcode"); foreach my $title ( sort keys %title ) { # print out by title. my $titleid = $title{$title}; # Get Copy Information $sth->execute( $titleid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $first = 1; while ( my ( $copyid, $barcode ) = $sth->fetchrow ) { # Check Copy Status my $sth1 = $dbh->prepare("select count(*) from book_signout where copyid = ? and returndate is NULL"); $sth1->execute($copyid); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $count = $sth1->fetchrow; my $copystatus; if ( $count ) { $copystatus = $lex{Out}; } else { $copystatus = $lex{In}; } if ( ( $arr{mode} eq 'out' and not $count) or ( $arr{mode} eq 'in' and $count ) ) { next; # skip this record } if ( $first ) { # print Header print TEX "\\begin{tabular}{|l|l|l|} \\hline\n"; print TEX "\\rowcolor[gray]{0.80}\\multicolumn{3}{|l|}{ $title} \\\\ \\hline\n"; print TEX "\\rowcolor[gray]{0.92}$lex{Barcode} &$lex{Id} &$lex{Status} \\\\ \\hline\n"; } print TEX "$barcode & $copyid & $copystatus \\\\ \\hline\n"; $first = 0; } if ( $first and $arr{showrecord} ) { # print records for title with no copies. print TEX "{\\it $title -- $lex{Copies} $lex{'Not Found'}}\\medskip\n\n"; } if ( not $first ) { # close tabular print TEX "\\end{tabular}\\medskip\n\n"; } } # end of titles. } else { # student report # sort by student my ($found, $sth); my $localgroup; if ( $group eq 'grade' ) { $localgroup = 'grade'; } else { $localgroup = 'homeroom'; } my $select; if ( $arr{groupvalue} ) { # check if group value found. my $sth1 = $dbh->prepare("select distinct $localgroup from student where $localgroup is not NULL and $localgroup != ''"); $sth1->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my $grp = $sth1->fetchrow ) { if ( $grp eq $arr{groupvalue} ) { $found = 1; last; } } if ( not $found ) { print "

$lex{Grade}/$lex{Homeroom} $lex{'Not Found'}

\n"; print "\n"; exit; } $sth = $dbh->prepare("select lastname, firstname, studnum from student where $localgroup = ? order by lastname, firstname"); $sth->execute( $arr{groupvalue} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } } else { # do all students $sth = $dbh->prepare("select lastname, firstname, studnum from student order by lastname, firstname"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } } # Get Signout Info $sth1 = $dbh->prepare("select copyid, signout, start_condition from book_signout where studnum = ? and returndate is NULL"); # Get Copy Info $sth2 = $dbh->prepare("select titleid, barcode from book_copy where id = ?"); # read all student data. while ( my ($lastname, $firstname, $studnum ) = $sth->fetchrow ) { # Get Signout information, if any. $sth1->execute( $studnum ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # Loop through signouts. my $first = 1; while ( my ( $copyid, $signout, $start_condition ) = $sth1->fetchrow ) { if ( $first ) { # print Header print TEX "\\begin{tabular}{|l|l|l|} \\hline\n"; print TEX "\\multicolumn{3}{|l|}{ $firstname $lastname ($studnum)} \\\\ \\hline\n"; } # Get Copy Info $sth2->execute( $copyid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ( $titleid, $barcode ) = $sth2->fetchrow; my $title = $id2title{ $titleid }; print TEX "$title & $barcode & $signout \\\\ \\hline\n"; $first = 0; } # end of this book if ( $first and $arr{showrecord} ) { # print records for title with no copies. print TEX "{\\it $firstname $lastname ($studnum) -- $lex{'Sign Out'} -- $lex{'Not Found'}}\\medskip\n\n"; } if ( not $first ) { # close tabular print TEX "\\end{tabular}\\medskip\n\n"; } } # end of this student } # end of student mode. # Finish Document print TEX "\\end{document}"; close TEX; # Solve download location issues with cgi vs tcgi.. # Get current dir so know what CSS to display; if (getcwd() =~ /tcgi/){ # we are in tcgi $downloaddir = $tchdownloaddir; $webdownloaddir = $tchwebdownloaddir; } system("$pdflatex $filename >pdflog$$.txt"); system("mv $shortname.pdf $downloaddir"); system("mv pdflog$$.txt $downloaddir"); system("rm -f $shortname.*"); print "

"; print "$lex{'View/Download'} $title

\n"; print "

[ $lex{Main} |\n "; print $lex{'View Log File'}. "\n ]

\n"; }