#!/usr/bin/perl
# Copyright 2001-2019 Leslie Richardson
# This file is part of Open Admin for Schools.
my %lex = ( 'Update' => 'Update',
'Continue' => 'Continue',
'Error' => 'Error',
'Main' => 'Main',
'Staff' => 'Staff',
'Leave' => 'Leave',
'Hours' => 'Hours',
'Position' => 'Position',
);
my $self = 'updateStaffLeave.pl';
use DBI;
use CGI;
use Time::JulianDay;
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);
# 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";
}
}
my $title = "$lex{Update} $lex{Staff} $lex{Leave}";
print qq{$doctype\n$title\n};
print qq{\n};
print qq{$chartype\n[ $lex{Main} ]\n};
print qq{
$title
\n};
if ( not $arr{page} ) {
showStartPage();
} elsif ( $arr{page} == 1 ) {
delete $arr{page};
updateLeave();
}
#---------------
sub updateLeave {
#---------------
# foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key} \n"; }
# Load Staff Name
my %staffname;
my $sth = $dbh->prepare("select lastname, firstname, userid from staff");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
while (my $ref = $sth->fetchrow_hashref ) {
my %r = %$ref;
$staffname{$r{userid}} = qq{$r{lastname}, $r{firstname}};
}
# Load Current Staff Leave Hours
my %hours;
my $sth = $dbh->prepare("select category, hours, userid from staff_leave");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
while ( my ($category,$hours, $userid) = $sth->fetchrow ) {
$hours{$userid}{$category} = $hours;
}
my $sth = $dbh->prepare("insert into staff_leave ( hours, userid, category )
values ( ?, ?, ? )");
my $sth1 = $dbh->prepare("update staff_leave set hours = ? where userid = ? and category = ?");
my $sth2 = $dbh->prepare("delete from staff_leave where userid = ? and category = ?");
my $sth3 = $dbh->prepare("select count(*) from staff_leave where userid = ? and category = ?");
foreach my $key ( sort keys %arr ) {
my ($userid, $category) = split(':', $key);
if ( not $arr{$key} ) {
# check if a record exists and delete;
$sth3->execute( $userid, $category);
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
my $count = $sth3->fetchrow;
if ( $count ) { # delete the record.
print qq{
Delete Leave Record for $staffname{$userid} - Category $category
\n};
$sth2->execute( $userid, $category);
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
}
next;
}
if ( $hours{$userid}{$category} == $arr{$key} ) {
# print qq{
\n};
next;
} # skip, same value
if ( $hours{$userid}{$category} ) { # record exists, update
$sth1->execute( $arr{$key}, $userid, $category);
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
print qq{
Update - $arr{$key} Hours, $userid,$category
\n};
} else { # add record.
$sth->execute( $arr{$key}, $userid, $category);
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
print qq{
Add - $arr{$key} Hours, $userid,$category
\n};
}
}
print qq{
Records Updated
\n};
print qq{[ $lex{Main} ]\n};
print qq{\n};
exit;
} # end of updateLeave;
#----------------
sub showStartPage {
#----------------
#foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key} \n"; }
# Load Staff
my (@staff, %staffname, %position);
my $sth1 = $dbh->prepare("select field_value from staff_multi
where userid = ? and field_name = 'position'");
my $sth = $dbh->prepare("select lastname, firstname, userid from staff
order by lastname, firstname");
$sth->execute;
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
while (my $ref = $sth->fetchrow_hashref ) {
my %r = %$ref;
push @staff, $r{userid};
$staffname{$r{userid}} = qq{$r{lastname}, $r{firstname}};
$sth1->execute( $r{userid} );
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
my $position = $sth1->fetchrow;
$position{$r{userid}} = $position
}
# Load Staff Leave Hours
my %hours;
my $sth = $dbh->prepare("select category, hours from staff_leave
where userid = ?");
foreach my $userid ( @staff ) {
$sth->execute( $userid );
if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
while ( my ($category,$hours) = $sth->fetchrow ) {
$hours{$userid}{$category} = $hours;
}
}
# Start Form
print qq{\n};
print qq{