#!/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 = ('View' => 'View',
'Staff' => 'Staff',
'Absences' => 'Absences',
'Main' => 'Main',
'Eoy' => 'Eoy',
'No Records Found' => 'No Records Found',
'Error' => 'Error',
'Edit' => 'Edit',
'Delete' => 'Delete',
'Continue' => 'Continue',
);
use DBI;
use CGI;
use Cwd;
my $self = 'staffabsRpt2.pl';
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. ": $@ \n";
die $lex{Error}. ": $@\n";
}
my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
# Print Page Header
my $title = qq{$lex{Staff} $lex{Absences} Report 2};
print qq{$doctype\n$title
\n};
print qq{[ $lex{Main} ]\n};
print qq{
$title
\n};
if ( not $arr{page} ){
showStartPage();
} elsif ( $arr{page} == 1 ) {
delete $arr{page};
showReport();
}
#-------------
sub showReport {
#-------------
# foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key} \n}; }
my $startyear = $arr{startyear};
my $endyear = $startyear + 1;
my $startdate = qq{$startyear-08-01};
my $enddate = qq{$endyear-06-30};
my %abs; # abs{userid}{reason} = daycount;
my %late; # late{userid} = minutes late;
my %name; # name{userid} = "lastname, firstname";
my %fields;
my $sth = $dbh->prepare("select * from staff_absent where
to_days(adate) > to_days('$startdate') and
to_days(adate) < to_days('$enddate')
order by adate desc");
#$sth = $dbh->prepare("select * from staff_absent");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my $ref = $sth->fetchrow_hashref ) {
my %r = %$ref;
my $userid = $r{userid};
my $tempname = qq{$r{lastname}, $r{firstname}};
$name{$userid} = $tempname;
if ( $r{late} ) {
# print "Late:$r{late} \n";
$r{late} =~ s/minute|minutes//g;
$late{ $userid } += $r{late};
next;
} else { # normal reasons.
my $daypart;
if ( $r{daypart} eq 'AM' or $r{daypart} eq 'PM' ) {
$daypart = 0.5;
} else {
$daypart = 1;
}
$abs{$userid}{$r{reason}} += $daypart;
$fields{ $r{reason} } = 1;
}
}
#foreach my $userid ( sort keys %abs ) {
# foreach my $reason ( sort keys %{ $abs{$userid}} ) {
# print qq{
\n};
# }
#}
my @fields = sort keys %fields;
# print "Fields:@fields \n";
my %sort;
foreach my $userid ( keys %name ) {
$sort{"$name{$userid}$userid"} = $userid;
}
my $first = 1;
foreach my $key ( sort keys %sort ) {
my $userid = $sort{$key};
if ( $first ) {
print qq{
\n};
print qq{
Name
};
foreach my $fld ( @fields ) {
print qq{
$fld
};
}
print qq{
Late
\n};
$first = 0;
}
print qq{
$name{$userid}
};
foreach my $fld ( @fields ) {
print qq{
$abs{$userid}{$fld}
};
}
if ( $late{$userid} ) {
print qq{
$late{$userid} min
\n};
} else {
print qq{
\n};
}
}
if ($first ) {
print qq{
$lex{'No Records Found'}
\n};
}
print qq{\n};
exit;
} # end of 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{