#!/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',
	    'Add' => 'Add',
	    'Transactions' => 'Transactions',
	    'Date' => 'Date',
	    'Records' => 'Records',
	    'Edit' => 'Edit',
	    'Comments' => 'Comments',
	    'Category' => 'Category',
	    
	    );

my $self = 'addTransaction.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);
$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}: $@<br>\n";
	die "$lex{Error}: $@\n";
    }
}



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

# Date Entry Setup
print qq{<link rel="stylesheet" type="text/css" media="all" };
print qq{href="/js/calendar-blue.css" title="blue">\n};
print qq{<script type="text/javascript" src="/js/calendar.js"></script>\n};
print qq{<script type="text/javascript" src="/js/lang/calendar-en.js"></script>\n};
print qq{<script type="text/javascript" src="/js/calendar-setup.js"></script>\n};

print qq{$chartype\n</head><body>[ <a href="$homepage">$lex{Main}</a> ]\n};
print qq{<h1>$title</h1>\n};


if ( not $arr{page} ) {
    getDate();
    
} elsif ( $arr{page} == 1 ) {
    delete $arr{page};
    addTransactions();

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

} elsif ( $arr{page} == 3 ) {
    delete $arr{page};
    updateComments();
}


#-----------
sub getDate {  # Get date for transaction
#-----------

    # Load Date of last transaction.
    my $sth = $dbh->prepare("select max(date) from staff_payjrl");
    $sth->execute;
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    my $lastdate = $sth->fetchrow;
    my $lastjd = julian_day( split('-', $lastdate));
    my $lastdow = day_of_week($lastjd);
    my $fdate = formatDate($lastdate);

    
    print qq{<h3>Last Transaction Date - $dowstd[$lastdow], $fdate</h3>\n};

    
    my $currjd = julian_day( split('-', $currdate));
    my $currdow = day_of_week($currjd);
    if ( $currdow == 0 ) { 
	$currjd += 1;
	$currdow = day_of_week($currjd);
    } elsif ( $currjd == 6 ) { 
	$currjd += 2;
	$currdow = day_of_week($currjd);
    }
    
    my $dow = $currdow;
    my $jd = $currjd;
    
    # Start Table
    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
    print qq{<caption>Select Transaction Date</caption>\n};
    print qq{<tr><th>$lex{Date}</th></tr>\n};
        
    while ( $dow ) {
	my $date = join('-', inverse_julian_day($jd));
	print qq{<tr><td>};
	print qq{<form action="$self" method="post" style="display:inline;">\n};
	print qq{<input type="hidden" name="page" value="1">\n};
	print qq{<input type="hidden" name="date" value="$date">\n};
        print qq{<input type="submit" value="$dowstd[$dow], $date">\n};
	print qq{</form></td></tr>\n};
	$jd--;
	$dow--;
    }
    print qq{</table>\n};
    
	
    # Select A Date

    print qq{<div style="border:1px solid gray;width:40ch;margin:1em 0;padding:0.4em;">\n};
    print qq{<form action="$self" method="post" style="display:inline;">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="Select This Date">\n};
    print qq{<input type="text" name="date" id="date" size="12" value="">\n};
    print qq{<button type="reset" id="start_trigger">...</button>\n};
    print qq{</form></div>\n\n};


    print qq{<script type="text/javascript">
     Calendar.setup({
        inputField     :    "date", // id of the input field
        ifFormat       :    "%Y-%m-%d", // format of the input field
        button         :    "start_trigger", // trigger for the calendar (button ID)
        singleClick    :    false,        // double-click mode
        step           :    1             // show all years in drop-down boxes 
    }) };

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


    exit;

} # end of getDate


#------------------
sub addTransactions {
#------------------

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

    # passed: entry date;
    my $date = $arr{date};
    my $jd = julian_day( split('-', $date));
    my $dow = day_of_week($jd);
    my $fdate = formatDate($date);
    
    print qq{<h3>Transaction Date - $dowstd[$dow], $fdate</h3>\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
    }

   
    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="2">\n};
    print qq{<input type="hidden" name="date" value="$date">\n};
    print qq{<input type="submit" value="$lex{Add}/$lex{Edit} $lex{Records}">\n};

    # Start Table, but no heading.
    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};    

    my $sth = $dbh->prepare("select * from staff_payjrl where userid = ? and date = ?");

    
    # Enter/Display Values
    my $count;
    foreach my $userid ( @staff ) {


	# Table Heading
	if ($count % 12 == 0 ) {
	    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{<th>Earned<br>Hours</th>};
	    print qq{<th>Used<br>Hours</th>};
	    print qq{<th>Other<br>Hours</th>};
	    print qq{<th>Without Pay<br>Hours</th>};
	    print qq{<th>Late<br>Hours</th></tr>\n};
	}

	

	# Load any existing records for this date, userid;
	my %data;
	$sth->execute($userid, $date);;
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	while ( my $ref = $sth->fetchrow_hashref ) {
	    $data{$ref->{category}} = $ref;
	}

	my $ref = calcLeaveBalance( $userid );
	my %bal = %$ref;
	
	print qq{<tr><td>$staffname{$userid}</td>}; # <td>$position{$userid}</td>};
	foreach my $leave ( @pay_LeaveAreas ) {
	    print qq{<td class="la"><input type="text" style="width:6ch;" };
	    print qq{name="$userid:$leave" value="$data{$leave}->{hours}"> <i>$bal{$leave}</i></td>};
	}

	print qq{<td><input type="text" name="$userid:earned" style="width:6ch;" };
	print qq{value="$data{earned}->{hours}"> $bal{earned}</td>\n};
	print qq{<td><input type="text" name="$userid:used" style="width:6ch;" };
	print qq{value="$data{used}->{hours}"></td>\n};
	print qq{<td><input type="text" name="$userid:other" style="width:6ch;" };
	print qq{value="$data{other}->{hours}"></td>\n};
	print qq{<td><input type="text" name="$userid:wopay" style="width:6ch;" };
	print qq{value="$data{wopay}->{hours}"></td>\n};
	print qq{<td><input type="text" name="$userid:late" style="width:6ch;" };
	print qq{value="$data{late}->{hours}"></td>\n};

	print qq{</tr>\n};
	$count++;
    }

    print qq{</table>\n};
    print qq{<input type="submit" value="$lex{Add}/$lex{Edit} $lex{Records}">\n};
    print qq{</form>\n};

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

    exit;

} # end of addTransactions();



#-------------
sub addComments {  # add/update records first and get id of records.
#-------------

    use Data::UUID;

    # Load Staff
    my %staffname;
    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;
	$staffname{$r{userid}} = qq{<b>$r{lastname}</b>, $r{firstname}};
    }

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


    my $date = $arr{date};
    delete $arr{date};

    my $ug = Data::UUID->new;

    
    # Check for records first (only hours and comment left)
    
    
    # Insert the records first.
    my $sth = $dbh->prepare("insert into staff_payjrl ( userid, date, category, hours, comment )
      values ( ?, ?, ?, ?, ? )");

    # Get Record from UUID value after adding.
    my $sth1 = $dbh->prepare("select id from staff_payjrl where comment = ?");
    # Reset Comment to NULL, if adding and checking for UUID.
    my $sth2 = $dbh->prepare("update staff_payjrl set comment = NULL where id = ?");

    # Check for existing record.
    my $sth3 = $dbh->prepare("select id from staff_payjrl 
       where userid = ? and date = ? and category = ?");
    # Update Hours for existing record.
    my $sth4 = $dbh->prepare("update staff_payjrl set hours = ? where id = ?");

    
    my %data;  # holds keys of inserted records or existing records.
        
    foreach my $key ( sort keys %arr ) {
	if ( $arr{$key} ) { # if we have hour value.

	    my ($userid, $category) = split(':', $key);
	    
	    # Already exists?
	    $sth3->execute($userid, $date, $category);
	    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	    my $testid = $sth3->fetchrow;
	    
	    if ( $testid ) { # then we just have to update the hours; leave comment change for next.
		$sth4->execute($arr{$key}, $testid);
		if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
		$data{$key} = $testid;
		
	    } else { # insert a record

		my $uuid = $ug->create_str();
		#print "STRING:$uuid<br>\n";

		$sth->execute($userid, $date, $category, $arr{$key}, $uuid );
		if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}

		# Get the id of the record.
		$sth1->execute( $uuid );
		if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
		my $id = $sth1->fetchrow;
		# print "ID:$id  UUID:$uuid<br>\n";

		$data{$key} = $id;

		# remove the uuid in comment
		$sth2->execute( $id );
		if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	    }
	} # if we have an hour value.
    }


     
    # Check for any removals
    # Delete from staff payjrl where record removed.
    my $sth1 = $dbh->prepare("delete from staff_payjrl where id = ?");
    my $sth2 = $dbh->prepare("select * from staff_payjrl where id = ?");
    my %deltest; # holds all ids of records for this date.
    my $sth = $dbh->prepare("select id from staff_payjrl where date = ?");    
    $sth->execute($date);
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    while ( my $id = $sth->fetchrow ) {
	$deltest{$id} = 1;
    }

    #remove matching records, so what's left in %deltest should be deleted
    foreach my $key ( keys %data ) {
	my $id = $data{$key};
	if ( $deltest{$id} ) { delete $deltest{$id}; }
    }

    
    foreach my $id ( keys %deltest ) {
	# Load the record.
	$sth2->execute($id);
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	my $ref = $sth2->fetchrow_hashref;
	my %r = %$ref;
	
	# delete this record.
	$sth1->execute($id);
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	
	print qq{Record Deleted - $r{userid} - $r{date} - $r{comment} - $r{hours}<br>\n};
    }
    

    # Now do comment entry.
    my %sort;
    foreach my $key ( keys %data ) {
	my ($userid, $category) = split(':', $key);
	my $name = $staffname{$userid};
	$sort{"$name$category$userid"} = $key;
    }

    if ( not %sort ) {
	print qq{<h3>No Records</h3>\n};
	print qq{</body></html>\n};
	exit;
    }
    

    my $sth = $dbh->prepare("select comment from staff_payjrl where id = ?");
    
    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="3">\n};
    print qq{<input type="submit" value="$lex{Add}/$lex{Edit} $lex{Comments}">\n};
    
    my $first = 1;
    foreach my $key ( sort keys %sort ) {
	my $val = $sort{$key};
	my $id = $data{$val};
	my ($userid,$category) = split(':', $val);

	# Get comment if any.
	$sth->execute( $id );
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	my $comment = $sth->fetchrow;
    
#	print "UID:$userid Cat:$category ID:$id<br>\n";

	if ( $first ) {
	    print qq{<table cellpadding="4" cellspacing="0" border="1">\n};
	    print qq{<tr><th>$lex{Staff}</th><th>$lex{Category}</th><th>$lex{Hours}</th>\n};
	    print qq{<th>Comment</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td>$staffname{$userid}</td><td>$category</td><td>$arr{$val}</td>\n};
	print qq{<td><input type="text" style="width:50ch;" name="$id:comment" value="$comment">};
	print qq{</td></tr>\n};
	
    }

    if ( not $first ) {
	print qq{</table>\n};
    } 

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

    exit;

} # end of addComments




#----------------
sub updateComments {
#----------------

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

    my $sth = $dbh->prepare("update staff_payjrl set comment = ? where id = ?");

    foreach my $key ( sort keys %arr ) {
	if ( not $arr{$key} ) { next; } # skip no values
	my ($id, $dud) = split(':', $key);
	my $comment = $arr{$key};

	$sth->execute( $comment, $id );
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    }

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

    exit;

} # end of updateComments




#-------------------
sub calcLeaveBalance {
#-------------------

    my $userid = shift; # passed userid.

    my %data;

    # Get Starting Leave Balances.
    my $first = 1;
    my $sth = $dbh->prepare("select category,hours from staff_leave where userid = ?");
    $sth->execute( $userid );
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    while ( my ($category,$hours) = $sth->fetchrow ) {
	$data{$category} = $hours;
	$first = 0;
    }
    if ( $first ) { # no transactions.
	return 0;
    }
    
    # Get Journal Transactions.
    $sth = $dbh->prepare("select hours from staff_payjrl where userid = ? and category = ?");
    foreach my $category ( @pay_LeaveAreas ) {
	$sth->execute( $userid, $category );
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
	while ( my  $hours = $sth->fetchrow ) {
	    $data{$category} -= $hours;
#	    print qq{<div>$userid - $category - $hours - Balance:$data{$category}</div>\n};
	}
    }
    # Get Earned/Used Balance.
    my ($earned, $used);
    $sth->execute( $userid, 'earned' );
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    while ( my $hours = $sth->fetchrow ) {
	$earned += $hours;
#	print qq{<div>$userid - Earned - $hours - Balance:$earned</div>\n};
    }
    # used
    $sth->execute( $userid, 'used' );
    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n";}
    while ( my $hours = $sth->fetchrow ) {
	$used += $hours;
#	print qq{<div>$userid - Used - $hours - Balance:$used</div>\n};
    }

    # Add to %data hash
    $data{earned} = $earned - $used;

    return \%data;

}
    
	
#-------------
sub formatDate {
#-------------

    my ( $year, $mon, $day ) = split '-', shift;
    return "$year-$s_month[$mon]-$day";
}
