#!/usr/bin/perl
# Copyright 2001-2022 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',
'Records' => 'Records',
'Category' => 'Category',
'Hours' => 'Hours',
'Month' => 'Month',
);
use DBI;
use CGI;
use CGI::Session;
use Cwd;
my $self = 'viewStaffPayTch.pl';
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. ": $@ \n";
die $lex{Error}. ": $@\n";
}
# Set Date
my @tim = localtime(time);
my $cyear = @tim[5] + 1900;
my $cmonth = @tim[4] + 1;
my $cday = @tim[3];
my $currdate = "$cyear-$cmonth-$cday";
my $curryrmo = "$cyear-$cmonth";
my $q = new CGI;
my %arr = $q->Vars;
print $q->header( -charset, $charset );
my $dsn = "DBI:$dbtype:dbname=$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
# load staffpay vars: @pay_LeaveAreas
my $sth = $dbh->prepare("select id, datavalue from conf_system where sectionname = 'staffpay'");
$sth->execute;
if ( $DBI::errstr ) { print $DBI::errstr; die $DBI::errstr; }
while ( my ($id, $datavalue) = $sth->fetchrow ) {
eval $datavalue;
if ( $@ ) {
print "$lex{Error}: $@ \n";
die "$lex{Error}: $@\n";
}
}
# Get current dir so know what CSS to display;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
$css = $tchcss;
$homepage = $tchpage;
}
# Print Page Header
my $title = qq{$lex{View} $lex{Staff} $lex{Hours}};
print qq{$doctype\n$title\n};
print qq{\n};
print qq{\n};
print qq{[ $lex{Main} ]\n};
print qq{
$title
\n};
=head # USEFUL!
my $sth = $dbh->prepare("show columns from staff_absent");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while (my @cols = $sth->fetchrow ) {
if ( $cols[0] eq 'id' or $cols[0] eq 'comment' ) { next; }
push @fields, $cols[0];
}
=cut
if ( not $arr{page} ) {
showStartPage();
} elsif ( $arr{page} == 1 ) {
delete $arr{page};
showReport();
}
#if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
#-------------
sub showReport {
#-------------
# foreach my $key (sort keys %arr ) { print qq{K:$key V:$arr{$key} \n}; }
my ($year,$month) = split('-',$arr{month});
delete $arr{month};
# print "Year:$year Month:$month \n";
# remaining %arr values are userids.
# Name
my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
# Configured Leave Values
my $sth1 = $dbh->prepare("select category, hours from staff_leave where userid = ?");
# Used Leaves
my $sth2 = $dbh->prepare("select sum(hours) from staff_payjrl
where userid = ? and category = ?");
foreach my $userid ( sort keys %arr ) {
# Get Name
$sth->execute( $userid );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname) = $sth->fetchrow;
print qq{
$firstname $lastname
\n};
# Load starting hours for this staff member.
$sth1->execute( $userid );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my %leavehours;
while ( my ($category, $hours) = $sth1->fetchrow ) {
$leavehours{$category} = $hours;
}
# Summary Table
print qq{
\n};
print qq{
$lex{Category}
Starting
Used
Remaining
\n};
foreach my $category ( @pay_LeaveAreas ) {
print qq{
$category
$leavehours{$category}
};
$sth2->execute( $userid, $category );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $sum = $sth2->fetchrow;
my $balance = $leavehours{$category} - $sum;
print qq{
$sum
$balance
\n};
}
# Now do earned and used;
$sth2->execute( $userid, 'earned' );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $earned = $sth2->fetchrow;
$sth2->execute( $userid, 'used' );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my $used = $sth2->fetchrow;
my $balance = $earned - $used;
print qq{
\n};
my $sth;
if ( $arr{month} ) {
$sth = $dbh->prepare("select * from staff_payjrl where userid = ?
and year(date) = ? and month(date) = ? order by date");
$sth->execute( $userid, $year, $month );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
} else {
# All Records
$sth = $dbh->prepare("select * from staff_payjrl where userid = ? order by date");
$sth->execute( $userid );
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{
Date
Category
Hours
Comment
\n};
$first = 0;
}
print qq{
$r{date}
$r{category}
};
print qq{
$r{hours}
$r{comment}
\n};
}
print qq{
\n};
print qq{\n};
} # next userid;
if ( $first ) {
print qq{