#!/usr/bin/perl
#  Copyright 2001-2019 Leslie Richardson

#  This file is part of Open Admin for Schools.


my %lex = ( 'Continue' => 'Continue',
	    'Error' => 'Error',
	    'Main' => 'Main',
	    'Staff' => 'Staff',
	    'Leave' => 'Leave',
	    'Hours' => 'Hours',
	    'Position' => 'Position',
	    'Add' => 'Add',
	    );

my $self = 'addStaffLeave.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 = "Add $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};
    selectStaff();

} elsif ( $arr{page} == 2 ) {
    delete $arr{page};
    addLeave();
}

#-----------
sub addLeave {
#-----------

    # foreach my $key ( sort keys %arr ) { print "K:$key  V:$arr{$key}<br>\n"; }

    my %leaves;
    foreach my $key ( keys %arr ) {
	my ( $idx,$leave) = split(':', $key );
	if ( $idx eq 'LV' ) {
	    $leaves{$leave} = $arr{$key};
	    delete $arr{$key};
	}
    }
    
 #   print "Leaves:", %leaves, "<br>\n";

 #   print qq{<br>\n};
 #   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
    }

    
    my $sth = $dbh->prepare("insert into staff_leave ( hours, userid, category )
      values ( ?, ?, ? )");
    my $sth2 = $dbh->prepare("select count(*) from staff_leave where userid = ? and category = ?");

    
    # Add Records
    foreach my $userid ( sort keys %arr ) {
	foreach my $leave ( keys %leaves ) {
	    my $hours = $leaves{$leave};

	    # check for existing record.
	    $sth2->execute( $userid, $leave );
	    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	    my $count = $sth2->fetchrow;
	    if ( $count ) {
		print qq{<div>Leave exists for $staffname{$userid} - $leave - Skipping</div>\n};
	    } else { # insert a record.
		$sth->execute( $hours, $userid, $leave);
		if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
		print qq{<div><b>Add</b> - $hours Hours, $staffname{$userid} - $leave</div>\n};
	    }
	} # end of leave loop
    } # end of userid loop

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

    exit;

} # end of addLeave;


#--------------
sub selectStaff  {
#--------------

    # foreach my $key ( sort keys %arr ) { print "K:$key V:$arr{$key}<br>\n"; }

    print qq{<table cellspacing="0" cellpadding="3" border="1" style="margin:1em;">\n};
    print qq{<tr><th>Category</th><th>Hours</th>\n};
    
    foreach my $key ( keys %arr ) {
	print qq{<tr><td>$key</td><td>$arr{$key}</td></tr>\n};
    }
    print qq{</table>\n};

    
    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    foreach my $key ( keys %arr ) {
	print qq{<input type="hidden" name="LV:$key" value="$arr{$key}">\n};
    }

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


    # Load Staff
    my (@staff, %staffname, %position);
    my $sth1 = $dbh->prepare("select field_value from staff_multi 
       where userid = ? and field_name = 'position'");
    my $sth2 = $dbh->prepare("select category from staff_leave where userid = ?"); 
    
    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
    }


    print qq{<table cellspacing="0" cellpadding="3" border="1">\n};
    print qq{<tr><th>Select</th><th>Name</th><th>Position</th><th>Current Set Leaves</tr>\n};
    
    foreach my $userid ( @staff ) {
	my @leaves;
	$sth2->execute( $userid );
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	while ( my $category = $sth2->fetchrow ) {
	    push @leaves, $category;
	}
	my $leave = join(',',@leaves);

	my $checked;
	if ( not $leave ) { $checked = qq{checked="checked"}; }
	
	print qq{<tr><td><input type="checkbox" name="$userid" value="1" $checked></td>\n};
	print qq{<td>$staffname{$userid}</td><td>$position{$userid}</td>\n};
	print qq{<td>$leave</td></tr>\n};
    }

    print qq{</table>\n};
    print qq{<input type="submit" value="$lex{Add} $lex{Leave}">\n};
    print qq{</form>\n};
    print qq{</body></html>\n};
    
    exit;

}
    

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

    print qq{<p style="width:60ch;border:1px solid gray;padding:0.5em;">\n};
    print qq{These default values may be applied to selected staff members };
    print qq{on the next page who have <b>no values set</b> in each of the categories of leave. };
    print qq{This is used to set values for <b>multiple users</b> without any leave values set.};
    print qq{</p>\n};

    
    # 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{Add} $lex{Leave}">\n};
    
    # Start Table
    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<tr><th>Leave</th><th>Hours</th></tr>\n};

    # Loop through leave headings.
    foreach my $leave ( @pay_LeaveAreas ) {
	print qq{<tr><td class="bra">$leave $lex{Hours}</td>};
	print qq{<td class="la"><input type="text" style="width:6ch;" name="$leave"></td></tr>\n};
    }
    print qq{</table>\n};

    print qq{<div style="margin:0.5em;"><input type="submit" value="$lex{Continue}"></div>\n};
    print qq{</form>\n};

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

    exit;

} # end of showStartPage();
