#!/usr/bin/perl
#  Copyright 2001-2021 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',
	   'Continue' => 'Continue',
	   
	   );


use DBI;
use CGI;
use Cwd;

my $self = 'staffabsRpt2.pl';


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);



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

print qq{<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};
    showReport();
}


#-------------
sub showReport {
#-------------

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    
    my $startyear = $arr{startyear};
    my $endyear = $startyear + 1;
    my $startdate = qq{$startyear-08-01};
    my $enddate = qq{$endyear-06-30};

    
    my %abs; # abs{userid}{reason} = daycount;
    my %late; # late{userid} = minutes late;
    my %name; # name{userid} = "lastname, firstname";
    my %fields;

    
    my $sth = $dbh->prepare("select * from staff_absent where 
			    to_days(adate) > to_days('$startdate') and 
			    to_days(adate) < to_days('$enddate')
			    order by adate desc");
    
    #$sth = $dbh->prepare("select * from staff_absent");
    
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	my $userid = $r{userid};
    
	my $tempname = qq{<b>$r{lastname}</b>, $r{firstname}};
	$name{$userid} = $tempname;
    
	if ( $r{late} ) {
	    #	print "Late:$r{late}<br>\n";
	    $r{late} =~ s/minute|minutes//g;
	    $late{ $userid } += $r{late};
	    next;
	} else { # normal reasons.
	    my $daypart;
	    if ( $r{daypart} eq 'AM' or $r{daypart} eq 'PM' ) {
		$daypart = 0.5;
	    } else {
		$daypart = 1;
	    }
	    $abs{$userid}{$r{reason}} += $daypart;
	    $fields{ $r{reason} } = 1;
	}

    }

    #foreach my $userid ( sort keys %abs ) {
    #    foreach my $reason ( sort keys %{ $abs{$userid}} ) {
    #	print qq{<div>User:$userid Reason:$reason VAL:$abs{$userid}{$reason}</div>\n};
    #    }
    #}


    my @fields = sort keys %fields;
    # print "Fields:@fields<br>\n";

    my %sort;
    foreach my $userid ( keys %name ) {
	$sort{"$name{$userid}$userid"} = $userid;
    }

    my $first = 1;
    foreach my $key ( sort keys %sort ) {
	my $userid = $sort{$key};
    
	if ( $first ) {
	    print qq{<table border="1" cellpadding="3" cellspacing="0">\n};
	    print qq{<tr><th>Name</th>};
	    foreach my $fld ( @fields ) {
		print qq{<th>$fld</th>};
	    }
	    print qq{<th>Late</th></tr>\n};
	    $first = 0;
	}

	print qq{<tr><td>$name{$userid}</td>};
	foreach my $fld ( @fields ) {
	    print qq{<td>$abs{$userid}{$fld}</td>};
	}
	if ( $late{$userid} ) {
	    print qq{<td>$late{$userid} min</td></tr>\n};
	} else {
	    print qq{<td></td></tr>\n};
	}
    }
    
    if ($first ) {
	print qq{<p>$lex{'No Records Found'}</p>\n};
    }
    print qq{</body></html>\n};

    exit;
    
} # end of showReport


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

    # Get dates in order to figure out the years.
    my  %startyear;
    my $sth = $dbh->prepare("select distinct year(adate),month(adate) from staff_absent 
			    order by adate");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    while ( my ($yr,$mo) = $sth->fetchrow ) {
	if ( $yr == 0 ) { next; }
	if ( $mo > 7 ) { $startyear{$yr} = 1; }
#	print "YR:$yr MO:$mo<br>\n";
    }

    # Check for NULL dates (0000-00-00);
    my $sth = $dbh->prepare("select * from staff_absent where adate = '0000-00-00'");
    $sth->execute;
    if ($DBI::errstr) { print $DBI::errstr; die $DBI::errstr; }
    my $first = 1;
    while ( my $ref = $sth->fetchrow_hashref ) {
	my %r = %$ref;
	if ( $first ) {
	    print qq{<table cellpadding="3" cellspacing="0" border="1" style="margin-bottom:1em;">\n};
	    print qq{<caption style="color:red;font-weight:bold;">$lex{Error} - Blank Date</caption>\n};
	    $first = 0;
	}
	print qq{<tr><td>$r{firstname} $r{lastname}</td><td>$r{reason}</td>};
	print qq{<td>$r{daypart} / $r{other}</td><td>$r{comment}</td></tr>\n};
    }
    
    if ( not $first ){ # close table
	print qq{</table>\n};
    }
    
    
    print qq{<form action="$self" method="post">\n};
    print qq{<input type="hidden" name="page" value="1">\n};

    
    print qq{<div>Select Year };
    print qq{<select name="startyear">\n};

    my $size = keys %startyear;
    if (  $size == 1 ) { # just display this year as default
	my ($startyear) = keys %startyear;
	my $endyear = $startyear + 1;
	print qq{<option value="$startyear">$startyear-$endyear</option>\n};
	print qq{</select></div>\n};
	
    } else { # more than 1 year to select
	print qq{<option value=""></option>\n};
	foreach my $startyear ( sort keys %startyear ) {
	    my $endyear = $startyear + 1;
	    print qq{<option value="$startyear">$startyear-$endyear</option>\n};
	}
	print qq{</select></div>\n};
    }

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

    exit;
}
