#! /usr/bin/perl
# Copyright 2001-2025 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);
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{
$lex{Error} - Blank Date
\n};
$first = 0;
}
print qq{
$r{firstname} $r{lastname}
$r{reason}
};
print qq{
$r{daypart} / $r{other}
$r{comment}
\n};
}
if ( not $first ){ # close table
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 $sth2 = $dbh->prepare("select * from staffwd 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;
if ( not $r{lastname} ) { # try withdrawn.
$sth2->execute( $userid );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $ref = $sth2->fetchrow_hashref;
%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{
\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{