#! /usr/bin/perl # Copyright 2001-2018 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', 'View/Download' => 'View/Download', 'Staff' => 'Staff', 'View Log File' => 'View Log File', 'Continue' => 'Continue', 'Grade' => 'Grade', 'Rm' => 'Rm', 'Settings' => 'Settings', 'Paper Size' => 'Paper Size', 'Letter' => 'Letter', 'A4' => 'A4', 'Legal' => 'Legal', 'Error' => 'Error', 'Report' => 'Report', 'Select' => 'Select', 'Blank=All' => 'Blank=All', 'Count' => 'Count', 'Absence' => 'Absence', 'Page Break' => 'Page Break', 'between' => 'between', 'Font Size' => 'Font Size', 'Total Days' => 'Total Days', 'Printed' => 'Printed', 'Check All' => 'Check All', 'Late Count' => 'Late Count', 'Total' => 'Total', ); my $self = 'staffabsRpt1.pl'; use DBI; use CGI; # Configurable settings my $maxstaff = 26; # maximum staff per page. my $maxstaffpositions = 12; # max type of staff types in admin.conf; my $width = 8; # default width of columns, 8mm; my $entrylimit = 15; # Max entries allowed for rotated text. my $maxentrysize = 14; # Maximum number of characters for the entry my $namewidth = 50; # Width of the first 'Name' column in mm. my $extracolwidth = 10; # Width of the second column in mm. Done in # in case I need this for another field later... # No longer used: my $margins = 26; # leave 0.5in (13mm) for each side margin. 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; # Date set after pulling in month and dow array. my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $iddst) = localtime(time); $year = $year + 1900; $mon++; $wday++; my $currdate = "$s_dow[$wday], $s_month[$mon] $mday, $year"; my $dsn = "DBI:$dbtype:dbname=$dbase"; my $dbh = DBI->connect($dsn,$user,$password); $dbh->{mysql_enable_utf8} = 1; my $title = qq{$lex{Staff} $lex{Absence} $lex{Report} 1}; 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(); } elsif ( $arr{page} == 1 ) { delete $arr{page}; selectStaff(); } elsif ( $arr{page} == 2 ) { delete $arr{page}; showReport(); } #----------------- sub showStartPage { #----------------- # Get dates in order to figure out the years. my %startyear; my $sth = $dbh->prepare("select distinct year(adate),month(adate) from staff_absent order by adate"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($yr,$mo) = $sth->fetchrow ) { if ( $yr == 0 ) { next; } if ( $mo > 7 ) { $startyear{$yr} = 1; } # print "YR:$yr MO:$mo
\n"; } # Check for NULL dates (0000-00-00); my $sth = $dbh->prepare("select * from staff_absent where adate = '0000-00-00'"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $first = 1; while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; if ( $first ) { print qq{\n}; print qq{\n}; $first = 0; } print qq{}; print qq{\n}; } if ( not $first ){ # close table print qq{
$lex{Error} - Blank Date
$r{firstname} $r{lastname}$r{reason}$r{daypart} / $r{other}$r{comment}
\n}; } print qq{
\n}; print qq{\n}; print qq{
Select Year }; print qq{
\n}; } else { # more than 1 year to select print qq{\n}; foreach my $startyear ( sort keys %startyear ) { my $endyear = $startyear + 1; print qq{\n}; } print qq{\n}; } print qq{\n}; print qq{
\n}; exit; } #-------------- sub showReport { #-------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } my ( $papersize, $textwidth, $textheight ); if ( $arr{papersize} eq 'letter' ) { $papersize = 'letterpaper'; $textwidth = $g_letterpaper_textwidth; $textheight = $g_letterpaper_textheight; } elsif ( $arr{papersize} eq 'legal' ) { $papersize = 'legalpaper'; $textwidth = $g_legalpaper_textwidth; $textheight = $g_legalpaper_textheight; } elsif ( $arr{papersize} eq 'a4' ) { $papersize = 'a4paper'; $textwidth = $g_a4paper_textwidth; $textheight = $g_a4paper_textheight; } # Strip out extra passed values my $pagebreak = $arr{pagebreak}; delete $arr{pagebreak}; delete $arr{papersize}; my $fontsize = $arr{fontsize}; delete $arr{fontsize}; my $startyear = $arr{startyear}; delete $arr{startyear}; my $endyear = $startyear + 1; my $startdate = qq{$startyear-08-01}; my $enddate = qq{$endyear-06-30}; my $paperwidth; if ( $textwidth =~ m/(\d+)/ ) { $paperwidth = $1; } $fontsize = $fontsize . 'pt'; $shortname = "staffabsent$$"; $filename = "$shortname.tex"; open( TEX,">$filename" ) || die "Can't open tex file\n"; print TEX "\\documentclass[$fontsize,$papersize]{article} \\usepackage{array,newcent,rotating,colortbl,inputenc,fancyhdr} $a_latex_header \\pagestyle{empty} \\setlength{\\textwidth}{$textwidth} \\setlength{\\textheight}{$textheight} \\setlength{\\hoffset}{-36mm} \\setlength{\\voffset}{-24mm} \\addtolength{\\evensidemargin}{0mm} \\addtolength{\\oddsidemargin}{0mm} \\setlength{\\tabcolsep}{1mm} \\setlength{\\extrarowheight}{2mm} \\renewcommand{\\familydefault}{\\sfdefault}\n"; print TEX qq{\\pagestyle{fancy} \\lhead{\\footnotesize Staff Absence Report 1} \\rfoot{\\footnotesize $lex{Printed}: $currdate } \\cfoot{ } }; # Note above... a columncolor of 1.00 means white, smaller number is darker. print TEX "\\begin{document}\n"; # Prepare Queries my $sth = $dbh->prepare("select * from staff where userid = ?"); # my $sth1 = $dbh->prepare("select * from staff_absent where userid = ? order by adate desc"); my $sth1 = $dbh->prepare("select * from staff_absent where to_days(adate) > to_days('$startdate') and to_days(adate) < to_days('$enddate') and userid = ? order by adate desc"); my $tabularlayout = qq{\\begin{tabular}{|p{40 mm}|p{60mm}|p{15mm}|p{60mm}|}}; foreach my $key ( sort keys %arr ) { my $userid = $arr{$key}; my $daycount; my $totallate; my $latecount; # Get Staff Record $sth->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $ref = $sth->fetchrow_hashref; my %r = %$ref; # print TEX qq{\n\n{\\Large $r{firstname} $r{lastname}}\n\\medskip\n\n}; # Get Their Absences $sth1->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # Loop through all absences. my $first = 1; my $linecount; while ( my $aref = $sth1->fetchrow_hashref ) { my %a = %$aref; ($a{other}) = latex_filter( $a{other} ); if ( $a{reason} eq 'Late' ) { $latecount++; } if ( $a{late} ) { $a{late} =~ s/\D//g; # strip any non digit text $totallate += $a{late}; $a{reason} = $a{reason}. qq{ ($a{late} min)}; } if ( $a{daypart} eq 'All Day' ) { $daycount++; } else { $daycount += 0.5; } if ( $first ) { print TEX $tabularlayout; print TEX "\\multicolumn{4}{l}{\\Large\\bf $r{firstname} $r{lastname}}\n\n"; print TEX "\\\\ \\hline\n\n"; $first = 0; } print TEX qq{$a{adate} & $a{reason} & $a{daypart} & $a{other}\\\\ \\hline\n}; $linecount++; if ( $linecount % 6 == 0 ) { # put in place to split text over pages print TEX qq{\\end{tabular}\n\n}; print TEX $tabularlayout; print TEX qq{ \\hline\n} } } # end of absences loop if ( not $first ) { # end tabular # Total print TEX qq{\\rowcolor[gray]{0.9} $lex{'Total Days'} $daycount &}; if ( $latecount ) { print TEX qq{$lex{'Late Count'}:$latecount $lex{Total}:$totallate min &}; } else { print TEX qq{ & }; } print TEX qq{& \\\\ \\hline \n}; print TEX qq{\\end{tabular}\n}; print TEX qq{\\bigskip\n\n}; } if ( $pagebreak ) { print TEX qq{\\newpage\n}; } } # End of Loop for all staff print TEX "\\end{document}"; close TEX; system("$pdflatex $filename >pdflog$$.txt"); system("mv $shortname.pdf $downloaddir"); system("mv pdflog$$.txt $downloaddir"); system("rm -f $shortname.*"); print qq{

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

\n}; print qq{

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

\n}; print qq{\n}; exit; } # end of showReport #--------------- sub selectStaff { #--------------- # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # passed the startyear value. Thus get dates between startyear-08-01 and endyear-06-30. if ( not $arr{startyear} ) { print qq{

Missing School Year

\n}; print qq{\n}; exit; } my $startyear = $arr{startyear}; my $endyear = $startyear + 1; my $startdate = qq{$startyear-08-01}; my $enddate = qq{$endyear-06-30}; # print "Start:$startdate End:$enddate
\n"; # Get default papersize my $papersize = $defaultpapersize; $papersize =~ s/paper//; # strip off the 'paper' ending; $papersize = ucfirst( $papersize ); # Checked option form print qq{
\n}; print qq{\n}; print qq{\n}; print qq{\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; print qq{\n}; my $checked; if ( $arr{checked} ) { $checked = qq{checked="checked"}; } # Outermost Table print qq{\n}; print qq{
\n}; # First Cell as Table print qq{\n}; print qq{\n}; print qq{\n}; # Find the staff $sth = $dbh->prepare("select * from staff order by lastname,firstname"); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } # Get Absence Count my $sth1 = $dbh->prepare("select count(*) from staff_absent where to_days(adate) > to_days('$startdate') and to_days(adate) < to_days('$enddate') and userid = ?"); while ( my $ref = $sth->fetchrow_hashref ) { my %r = %$ref; $sth1->execute( $r{userid} ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $abscount = $sth1->fetchrow; print qq{}; print qq{\n}; } print qq{
$lex{Select} $lex{Staff}$lex{Absence}
$lex{Count}
}; if ( $abscount ) { print qq{}; } print qq{$r{lastname}, $r{firstname}$abscount
\n}; # Top Right now print qq{
\n}; # Now Set Width and Column Headings, print qq{\n}; print qq{\n}; print qq{\n}; # Paper Size print qq{\n}; print qq{\n}; # Font Size print qq{\n}; print qq{\n}; print qq{\n}; print qq{
$lex{Settings}
}; print qq{$lex{'Page Break'} $lex{between} $lex{Staff}
$lex{'Paper Size'}
$lex{'Font Size'}
}; print qq{
\n}; print qq{
\n}; # end of outer table. print qq{
\n}; exit; } #----------- sub prHeader { #----------- # Done at the start of a new page. print TEX "\\begin{tabular}{G|p{$extracolwidth mm}|"; for (1..$colcount){ print TEX "p{$wi mm}|"; } print TEX "}\n"; print TEX "\\raggedright\\bf $schoolname ". $title; for (1..$colcount + 1){ print TEX "& ";} print TEX "\\\\ \n"; print TEX "\\small\\raggedright $currdate\n\\bigskip "; for (1..$colcount + 1){ print TEX "& ";} print TEX "\\\\ \n"; if ($arr{group} eq $lex{Grade} ){ print TEX "\\Large\\raggedright ". $lex{Grade}. " $curroom & "; } else { #print TEX "\\bf\\raggedright $teachername "; #print TEX $lex{Rm}. " $curroom & "; print TEX ' & '; } my $remainder = $colcount - ( $repeatcount * $entrysize ); if ( $repeatcount ) { for (1..$repeatcount){ # times to loop to print rotated text. foreach my $txt ( @entry ) { print TEX "&\\hfil\\rule{6pt}{0pt}\\begin{rotate}{90}$txt"; print TEX "\\end{rotate}\\hfil"; } } } for (1..$remainder){ print TEX "& ";} print TEX "\\\\ \\hline\n"; } # End of prHeader #----------- sub prRecord { # print normal record (ie. line) #----------- my $userid = shift; my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?"); $sth->execute( $userid ); if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; } my ( $lastname, $firstname ) = $sth->fetchrow; if ($alternatingcolor == 0){ print TEX "\\rowcolor[gray]{0.93}"; $alternatingcolor = 1; } else { $alternatingcolor = 0;} print TEX "\\raggedright {\\bf $lastname}, $firstname"; print TEX " & "; for (1..$colcount){ print TEX "& ";} print TEX "\\\\\\hline\n"; }