#!/usr/bin/perl
#  Copyright 2001-2019 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',
	   'Report' => 'Report',
	   'Start Date' => 'Start Date',
	   'End Date' => 'End Date',
	   'Worksheet' => 'Worksheet',
	   
	   );


use DBI;
use CGI;
use Cwd;
use Time::JulianDay;

my $self = 'rptStaffPay.pl';


eval require "../../etc/admin.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


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);
$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";
    }
}


# Get current dir so know what CSS to display;
if ( getcwd() =~ /tcgi/ ){ # we are in tcgi
    $css = $tchcss;
    $homepage = $tchpage;
}


# Print Page Header
my $title = qq{$lex{Worksheet} $lex{Report}};
print qq{$doctype\n<html><head><title>$title</title>\n};
print qq{<link rel="stylesheet" href="$css" type="text/css">\n};

if ( not $arr{page} ) { # load javascript library
    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>\n};

print qq{<body>[ <a href="$homepage">$lex{Main}</a> ]\n};
print qq{<h1>$title</h1>\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 {
#-------------

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

    # load any stat holiday;
    my %stathol;
    my $sth = $dbh->prepare("select date, desc1 from dates where type = 'StatHol'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($date, $desc1) = $sth->fetchrow ) {
	$stathol{$date} = $desc1;
    }

    
    my $startdate = $arr{startdate};
    delete $arr{startdate};
    my $enddate = $arr{enddate};
    delete $arr{enddate};
    my $workhours = $arr{workhours};
    delete $arr{workhours};

    
    my $startjd = julian_day(split('-', $startdate));
    my $endjd = julian_day(split('-', $enddate));
    # remaining values are userids.

    # Sort by Name
    my (%sort, %name);
    my $sth = $dbh->prepare("select lastname, firstname from staff where userid = ?");
    
    foreach my $userid ( keys %arr ) {
    
	# Get Name
	$sth->execute( $userid );
	if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
	my ($lastname, $firstname) = $sth->fetchrow;
	$sort{"$lastname$firstname$userid"} = $userid;
	$name{$userid} = qq{$firstname $lastname};
    }


    # Prepare to load records.
    my $sth1 = $dbh->prepare("select category, hours from staff_leave where userid = ?");
    my $sth2 = $dbh->prepare("select * from staff_payjrl where userid = ? and date = ?");
    
    # Loop through userids.
    foreach my $key ( sort keys %sort ) {
	
	my $userid = $sort{$key};
	my $name = $name{$userid};

	my $totalhours;
	
	print qq{<h3>$name</h3>\n};

	# Start Table
	print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
	print qq{<tr><th>Day</th><th>Date</th>};
	print qq{<th>Type</th><th>Amount</th><th>Note</th></tr>\n};

	# loop through each of the dates;
	foreach my $jd ( $startjd .. $endjd ) {
	    my $dow = day_of_week($jd);
	    if ( $dow == 0 or $dow == 6 ) { next; } # skip weekends.
	    my $date = join('-', inverse_julian_day($jd));
	    print qq{<tr><td>$dowstd[$dow]</td><td>$date</td>\n};

	    # Stat Holiday?
	    if ( exists $stathol{$date} ) { # it's a holiday
		print qq{<td>Holiday</td><td>Statutory</td><td>$workhours</td>};
		print qq{<td>$stathol{$date}</td></tr>\n};
		$totalhours += $workhours;
		
	    } else { # normal working day

		my $absenthours; # for this date
		my $wopayhours; # absent without pay hours
		my %data;
		
		# Get any records.
		$sth2->execute( $userid, $date );
		if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
		while ( my $ref = $sth2->fetchrow_hashref ) {
		    if ( $ref->{category} eq 'earned' ) { next; }
		    if ( $ref->{category} eq 'wopay' ) {
			$wopayhours += $ref->{hours};
		    } else { # normal hours not deducted.
			$absenthours += $ref->{hours};
		    }
		    $data{$ref->{id}} = $ref;
		}
		
		# Calculate balance for today
		my $balance = $workhours - $wopayhours;
		$totalhours += $balance;

		my $workdisplay;
		if ( $absenthours > $workhours ) { 
		    $balance = qq{<span style="color:red;">Error</span>}; 
		} else { # ok.
		    $workdisplay = $balance - $absenthours;
		}


		# Now printout.
#		if ( %data ) {
		    my @id = ( sort keys %data );
		    # Category
		    print qq{<td style="vertical-align:top;">};
		    foreach my $id ( @id ) {
			my $cat = ucfirst $data{$id}->{category};
			if ( $cat eq 'Wopay') { $cat = 'No Pay'; }
			print qq{$cat<br>\n};
		    }
		    print qq{Work<br>\n};
		    print qq{<b>Total</b></td>\n};
		    
		    # Hours.
		    print qq{<td style="vertical-align:top;">};
		    foreach my $id ( @id ) {
			print qq{$data{$id}->{hours}<br>\n};
		    }
		    print qq{$workdisplay<br>\n};
		    print qq{<b>$balance</b></td>\n};
		    
		    # Comment
		    print qq{<td style="vertical-align:top;">};
		    foreach my $id ( @id ) {
			print qq{$data{$id}->{comment}<br>\n};
		    }
		    print qq{</td></tr>\n};
#		}
		
		# print qq{<td>Work</td><td>$workdisplay</td><td></td></tr>\n};
		
	    }
	}
	# print total hours.
	print qq{<tr style="background-color:#DDD;"><td colspan="3" class="bra">Total Hours</td>};
	print qq{<td class="bla" colspan="2">$totalhours</td></tr>\n};
	print qq{</table>\n};
	print qq{<div style="page-break-after:always;"></div>\n};
	
    }
    

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

    exit;
    
} # end of showReport


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


    # Start Form
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="submit" value="Check All">\n};
    print qq{<input type="hidden" name="checked" value="1">\n};
    print qq{</form><hr style="width:30%;margin-left:0;">\n};

    
    my $checked;
    if ( $arr{checked} ) {
	$checked = qq{checked = "checked"};
    }
    delete $arr{checked};

    
    my $currjd = julian_day( split('-', $currdate));
    my $currdow = day_of_week($currjd);

    my $mondayjd = $currjd - $currdow + 1;
    my $prevmondayjd = $mondayjd - 7;
    my $prevmonday = join('-', inverse_julian_day($prevmondayjd));
    my $nextfridayjd = $prevmondayjd + 11;
    my $nextfriday = join('-', inverse_julian_day($nextfridayjd));
    
    
    # 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{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};
    print qq{<input type="submit" value="Continue">\n};

    # Dates
    print qq{<table style="width:45ch;border:1px solid gray;padding:0.3em;margin:0.5em 0em;" };
    print qq{padding="3" spacing="0" border="0">};
    
    print qq{<tr><td class="bra">$lex{'Start Date'}</td> \n};
    print qq{<td><input type="text" name="startdate" id="sdate" style="width:10ch;" };
    print qq{value="$prevmonday">};
    print qq{<button type="reset" id="start_trigger">...</button> Previous Monday</td></tr>\n};

    print qq{<tr><td class="bra">$lex{'End Date'}</td>\n};
    print qq{<td><input type="text" name="enddate" id="edate" style="width:10ch;" value="$nextfriday">};
    print qq{<button type="reset" id="end_trigger">...</button> Following Friday</td></tr>\n};

    print qq{<tr><td class="bra">Daily Work Hours</td>\n};
    print qq{<td><input type="text" name="workhours" style="width:6ch;" value="7.25"></td></tr>\n};
    
    
    
    # Start Table
    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
    print qq{<tr><th>$lex{Staff}</th></tr>\n};


    while ( my ($userid, $lastname, $firstname) = $sth->fetchrow ) {

	print qq{<tr><td><input type="checkbox" name="$userid" value="1" $checked>};
	print qq{<b>$lastname</b>, $firstname ($userid)</td></tr>\n};

    }

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

    print qq{<script type="text/javascript">
     Calendar.setup({
        inputField     :  "sdate", // 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 
    })

    Calendar.setup({
        inputField     :  "edate", // id of the input field
        ifFormat       :  "%Y-%m-%d", // format of the input field
        button         :  "end_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;

}

