#!/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 = 'rptStaffPaySummary.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 $currfdate = $s_month[$cmonth]. "$cday, $cyear"; 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"; } } # Print Page Header my $title = qq{$lex{Staff} Leave Remaining $lex{Hours}}; print qq{$doctype\n$title\n}; print qq{\n}; print qq{\n}; print qq{[ $lex{Main} ]\n}; print qq{
\n}; print qq{\n}; print qq{
\n}; print qq{

$title $currfdate

\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 { # do all months for all staff #------------- # foreach my $key (sort keys %arr ) { print qq{K:$key V:$arr{$key}
\n}; } # Start Form print qq{
\n}; # Load all staff members my (@userids,%names); 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; } while ( my ($userid, $lastname, $firstname) = $sth->fetchrow ) { push @userids, $userid; $names{$userid} = qq{$lastname, $firstname}; } # 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 = ?"); my $sth3 = $dbh->prepare("select * from staff_payjrl where userid = ? and category = 'other'"); my $first = 1; my $count = 1; foreach my $userid ( @userids ) { if ( $first ) { # start table header. print qq{\n}; print qq{\n}; foreach my $category ( @pay_LeaveAreas ) { print qq{}; } print qq{}; print qq{\n}; $first = 0; } # Name if ( $count % 2 == 0 ) { print qq{}; } else { print qq{}; } print qq{}; # 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; } foreach my $category ( @pay_LeaveAreas ) { # typically 4 categories predefined. $sth2->execute( $userid, $category ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $sum = $sth2->fetchrow; my $balance = $leavehours{$category} - $sum; if ( $balance < 0 and $arr{showcalc} ) { # any value of showcalc works (neg/all) print qq{}; } elsif ( $arr{showcalc} eq 'all' ) { print qq{}; } else { # no calcs print qq{}; } } # Now do earned / 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; if ( $arr{showcalc} eq 'all' ) { print qq{}; } else { # no calcs print qq{\n}; } # Now do without pay $sth2->execute( $userid, 'wopay' ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my $wopaysum = $sth2->fetchrow; print qq{}; # Other Category - load entire record $sth3->execute( $userid ); if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } my ($ocount, $otext); while ( my $ref = $sth3->fetchrow_hashref ) { my %r = %$ref; $ocount++; $otext = $otext. qq{$r{comment}\n}; } if ( $ocount ) { print qq{}; } else { print qq{}; } print qq{\n}; $count++; } # next userid; if ( $first ) { print qq{

$lex{'No Records Found'}

\n}; print qq{\n}; exit; } else { print qq{
Staff Name$categoryEarnedLWOPOther
$names{$userid}$balance
}; print qq{$leavehours{$category} - $sum		$balance
}; print qq{$leavehours{$category} - $sum		$balance$balance
}; print qq{$earned - $used		$balance$wopaysum$ocount View
\n}; } print qq{

[ $lex{Main} ]\n}; print qq{\n}; exit; } # end of showReport #---------------- sub showStartPage { #---------------- print qq{\n}; print qq{\n}; print qq{

\n}; my $checked; if ( $arr{checked} ) { $checked = qq{Checked="Checked"}; delete $arr{checked}; } # Setup Year-Months. my @months; my %months; my $sth = $dbh->prepare("select distinct year(date), month(date) from staff_payjrl where date is not NULL and date != '0000-00-00' order by year(date), month(date) "); $sth->execute; if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; } while ( my ($yr,$mo) = $sth->fetchrow ) { # print qq{Y:$yr M:$mo
\n}; my $yrmo = "$yr-$mo"; push @months, $yrmo; $months{$yrmo} = "$s_month[$mo]-$yr"; } # rptmonth4 has a starting year / 10 month setup. # 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{
\n}; print qq{\n}; print qq{
\n}; print qq{ Blank Month = All Months
\n}; my $first = 1; while ( my ($userid, $lastname, $firstname) = $sth->fetchrow ) { if ( $first ) { print qq{\n}; print qq{\n}; $first = 0; } print qq{\n}; } print qq{
$lex{Staff}
}; print qq{$lastname, $firstname ($userid)
\n}; # Get Month print qq{\n}; print qq{\n}; # print qq{\n}; } print qq{
$lex{Month}
$months{$mo}
\n}; print qq{
\n}; print qq{
\n}; print qq{
\n}; print qq{\n}; exit; }