#!/usr/bin/perl
# Copyright 2001-2019 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',
'Report' => 'Report',
'Start Date' => 'Start Date',
'End Date' => 'End Date',
'Worksheet' => 'Worksheet',
);
use DBI;
use CGI;
use Cwd;
use Time::JulianDay;
my $self = 'rptStaffPay.pl';
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. ": $@ \n";
die $lex{Error}. ": $@\n";
}
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);
$dbh->{mysql_enable_utf8} = 1;
# Set Date
my @tim = localtime(time);
my $year = @tim[5] + 1900;
my $month = @tim[4] + 1;
my $day = @tim[3];
my $currdate = "$year-$month-$day";
my $currdate1 = "$month[$month] $day, $year";
# 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{Worksheet} $lex{Report}};
print qq{$doctype\n$title\n};
print qq{\n};
if ( not $arr{page} ) { # load javascript library
print qq{\n};
print qq{\n};
print qq{\n};
print qq{\n};
}
print qq{$chartype\n\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}; }
# load any stat holiday;
my %stathol;
my $sth = $dbh->prepare("select date, desc1 from dates where type = 'StatHol'");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my ($date, $desc1) = $sth->fetchrow ) {
$stathol{$date} = $desc1;
}
my $startdate = $arr{startdate};
delete $arr{startdate};
my $enddate = $arr{enddate};
delete $arr{enddate};
my $workhours = $arr{workhours};
delete $arr{workhours};
my $startjd = julian_day(split('-', $startdate));
my $endjd = julian_day(split('-', $enddate));
# remaining values are userids.
# Sort by Name
my (%sort, %name);
my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
foreach my $userid ( keys %arr ) {
# Get Name
$sth->execute( $userid );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
my ($lastname, $firstname) = $sth->fetchrow;
$sort{"$lastname$firstname$userid"} = $userid;
$name{$userid} = qq{$firstname $lastname};
}
# Prepare to load records.
my $sth1 = $dbh->prepare("select category, hours from staff_leave where userid = ?");
my $sth2 = $dbh->prepare("select * from staff_payjrl where userid = ? and date = ?");
# Loop through userids.
foreach my $key ( sort keys %sort ) {
my $userid = $sort{$key};
my $name = $name{$userid};
my $totalhours;
print qq{
$name
\n};
# Start Table
print qq{
\n};
print qq{
Day
Date
};
print qq{
Type
Amount
Note
\n};
# loop through each of the dates;
foreach my $jd ( $startjd .. $endjd ) {
my $dow = day_of_week($jd);
if ( $dow == 0 or $dow == 6 ) { next; } # skip weekends.
my $date = join('-', inverse_julian_day($jd));
print qq{
$dowstd[$dow]
$date
\n};
# Stat Holiday?
if ( exists $stathol{$date} ) { # it's a holiday
print qq{
Holiday
Statutory
$workhours
};
print qq{
$stathol{$date}
\n};
$totalhours += $workhours;
} else { # normal working day
my $absenthours; # for this date
my $wopayhours; # absent without pay hours
my %data;
# Get any records.
$sth2->execute( $userid, $date );
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
while ( my $ref = $sth2->fetchrow_hashref ) {
if ( $ref->{category} eq 'earned' ) { next; }
if ( $ref->{category} eq 'wopay' ) {
$wopayhours += $ref->{hours};
} else { # normal hours not deducted.
$absenthours += $ref->{hours};
}
$data{$ref->{id}} = $ref;
}
# Calculate balance for today
my $balance = $workhours - $wopayhours;
$totalhours += $balance;
my $workdisplay;
if ( $absenthours > $workhours ) {
$balance = qq{Error};
} else { # ok.
$workdisplay = $balance - $absenthours;
}
# Now printout.
# if ( %data ) {
my @id = ( sort keys %data );
# Category
print qq{
[ $lex{Main} ]\n};
print qq{\n};
exit;
} # end of showReport
#----------------
sub showStartPage {
#----------------
# Start Form
print qq{
\n};
my $checked;
if ( $arr{checked} ) {
$checked = qq{checked = "checked"};
}
delete $arr{checked};
my $currjd = julian_day( split('-', $currdate));
my $currdow = day_of_week($currjd);
my $mondayjd = $currjd - $currdow + 1;
my $prevmondayjd = $mondayjd - 7;
my $prevmonday = join('-', inverse_julian_day($prevmondayjd));
my $nextfridayjd = $prevmondayjd + 11;
my $nextfriday = join('-', inverse_julian_day($nextfridayjd));
# Load starting hours for this staff member.
my $sth = $dbh->prepare("select distinct l.userid, s.lastname,s.firstname from staff_leave l, staff s
where l.userid = s.userid order by s.lastname, s.firstname");
$sth->execute;
if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
# Start Form
print qq{