#!/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}. ": $@<br>\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}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}



my $title = "$lex{Update} $lex{Staff} $lex{Leave}";
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};


print qq{$chartype\n</head><body>[ <a href="$homepage">$lex{Main}</a> ]\n};
print qq{<h1>$title</h1>\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}<br>\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{<b>$r{lastname}</b>, $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{<div>Delete Leave Record for $staffname{$userid} - Category $category</div>\n};
		$sth2->execute( $userid, $category);
		if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	    }
	    next;
	}
		
	
	if ( $hours{$userid}{$category} == $arr{$key} ) {
#	    print qq{<div>SKIP: $hours{$userid}{$category} equals $arr{$key}</div>\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{<div><b>Update</b> - $arr{$key} Hours, $userid,$category</div>\n};
	} else { # add record.
	    $sth->execute( $arr{$key}, $userid, $category);
	    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	    print qq{<div><b>Add</b> - $arr{$key} Hours, $userid,$category</div>\n};
	}
    }

    print qq{<h3>Records Updated</h3>\n};
    print qq{[ <a href="$homepage">$lex{Main}</a> ]\n};
    print qq{</body></html>\n};

    exit;

} # end of updateLeave;



#----------------
sub showStartPage {
#----------------

    #foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\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{<b>$r{lastname}</b>, $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{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="$lex{Update} $lex{Leave}">\n};
    
    # Start Table
    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<tr><th>$lex{Staff}</th><th>$lex{Position}</th>};

    # Loop through leave headings.
    foreach my $leave ( @pay_LeaveAreas ) {
	print qq{<th>$leave<br>$lex{Hours}</th>};
    }
    print qq{</tr>\n};

    # Enter/Display Values
    foreach my $userid ( @staff ) {
	print qq{<tr><td>$staffname{$userid}</td><td>$position{$userid}</td>};
	foreach my $leave ( @pay_LeaveAreas ) {
	    print qq{<td><input type="text" style="width:6ch;text-align:center;" };
	    print qq{name="$userid:$leave" };
	    print qq{value="$hours{$userid}{$leave}"></td>}; # $lex{Hours}</td>};
	}
	print qq{</tr>\n};
    }

    print qq{</table>\n};

    print qq{<input type="submit" value="$lex{Update} $lex{Leave}">\n};
    print qq{</form>\n};

    print qq{</body></html>\n};

    exit;

} # end of showStartPage();
