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

#  This file is part of Open Admin for Schools.



my %lex = ('User Id' => 'User Id',
	   'Password' => 'Password',
	   'Duration' => 'Duration',
	   'Login' => 'Login',
	   'Log In' => 'Log In',
	   'Log Out' => 'Log Out',
	   'min' => 'min',
	   'No Userid Found' => 'No Userid Found',
	   'Incorrect Password' => 'Incorrect Password',
	   'Logged In' => 'Logged In',
	   'Error' => 'Error',
	   'Continue' => 'Continue',
	   'User' => 'User',
	   'Teacher Administration' => 'Teacher Administration',
	   'Announcements' => 'Announcements',
	   'Hover=Full View' => 'Hover on Title to see Full Text',
	   'View All' => 'View All',
	   'Logged Out' => 'Logged Out',
	   'Main' => 'Main',
	   'No Announcements' => 'No Announcements',

	   );

my $self = 'rptWorkSchool.pl';

use DBI;
use CGI;
use Number::Format qw(:all);
use Time::JulianDay;

my $q = CGI->new;
my %arr = $q->Vars;
print $q->header;

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

# get the workFirstNatin hash
eval require "../etc/first_nation.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}

# get global user/password.
eval require "$globdir/global.conf";
if ( $@ ) {
    print $lex{Error}. ": $@<br>\n";
    die $lex{Error}. ": $@\n";
}


my $title = qq{First Nation Contract Report};


# Global user access to consultant DB.
my $dbase = 'consultant';
my $dsn = "DBI:mysql:dbname=$dbase";
my $dbh = DBI->connect($dsn,$guser,$gpassword);



print qq{<!DOCTYPE html>\n};
print qq{<html><head><title>$title</title>\n};
print qq{<meta charset="utf-8">\n};
print qq{<link rel="stylesheet" type="text/css" href="$css">\n};
print qq{</head><body>\n};

print qq{<a href="top"></a>\n}; # anchor for the top of page
print qq{<h1>$title</h1>\n};


if ( not %workFirstNation ) {
    print qq{<h3>Not Configured - workFirstNation not set.</h3>\n};
    print qq{</body></html>\n};
    exit;
}


print qq{<div>Jump To };
foreach my $school ( sort keys %workFirstNation ) {
    print qq{[ <a href="#$school">$school</a> ]};
}
print qq{</div>\n};

showReport();


# Get the First Nation
#    my %fnation = reverse %schoolmap;


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

    # foreach my $key ( sort keys %arr ) { print qq{K:$key V:$arr{$key}<br>\n}; }
    # Passed the First Nation as text.
    # my $summaryonly = $arr{summaryonly};
    # delete $arr{summaryonly};

    # $schoolmap is hash: school => First Nation.

    

    # Get Users from Apache consultant password file.
    use Apache::Htpasswd; # to read the consultant password file.
    my $basepath = '/etc/apache2/auth_users';
    my $file = 'consultant';
    my $ph = new Apache::Htpasswd("$basepath/$file");

    my %names;
    my @users = $ph->fetchUsers();
    foreach my $userid ( sort @users ) {
	my $name = $ph->fetchInfo($userid);
	$names{$userid} = $name;
#	print qq{$userid - $name<br>\n};
    }


    

    foreach my $school ( sort keys %workFirstNation ) {

	my $firstnation = $workFirstNation{$school};
	
	# we can now get work records for this school, in all categories.
	my %work;
	my $sth = $dbh->prepare("select * from worklog where school = ?");
	$sth->execute( $school );
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; };
	while ( my $ref = $sth->fetchrow_hashref ) {
	    my %r = %$ref;
	    $work{ $school }{ $r{category} } += $r{hours};
	}

	print qq{<h3 id="$school">}, ucfirst($school), qq{</h3>\n};
   
    
	# Display Contract Values
	my $sth1 = $dbh->prepare("select hours from worklog where school = ? and category = ?");
	my $sth = $dbh->prepare("select * from contract where firstnation = ?");

	# Table Layout
	print qq{<table cellpadding="3" border="1" cellspacing="0" style="margin-bottom:1em;">\n};
	print qq{<tr><th>Category</th><th>Contract<br>Days</th><th>Days Used</th>};
	print qq{<th>Days<br>Remaining</th></tr>\n}; # <th>Details</th></tr>\n};


	# Load the contract record.
	$sth->execute( $firstnation );  # which is the first nation.
	if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; };
	my $ref = $sth->fetchrow_hashref;
	my %contract = %r = %$ref;
	my @category;
	foreach my $cat ( sort keys %contract ) {
	    if ( $cat eq 'firstnation' or $cat eq 'id' ) { next; }
	    push @category, $cat;
#	    print qq{CAT $cat<br>\n};
	}
#	print "Contract:", %contract, "<br>\n";

	
	my ($total, $totalused,$totalleft);

    
	foreach my $category ( @category ) {
	    my $hoursused;
#	    print qq{S:$school C:$category WORK VAL:$work{$school}{$category}<br>\n};
#	    print qq{S:$school C:$category DRIVE VAL:$schoolhr{$school}{$category}<br>\n};
	    $hoursused += $work{$school}{$category};
	    $hoursused += $schoolhr{$school}{$category};


	    my $daysused = round($hoursused / 8, 1);
	    $totalused += $daysused;
	    my $daysleft = round( $r{$category} - $daysused, 1);
	    $totalleft += $daysleft;
	
	    print qq{<tr><td class="bla">$category</td><td class="cn">$r{$category}</td>};
	    print qq{<td>$daysused</td><td>$daysleft</td>\n};
	    print qq{</tr>\n};
	
	    $total += $r{$category};
	}


	# Totals
	print qq{<tr style="background-color:#DDD;"><td class="bra">Totals</td>};
	print qq{<td class="cn">$total</td>};
	print qq{<td>$totalused</td><td>$totalleft</td></tr>\n};
	print qq{</table>\n};


#	if ( not $summaryonly ) { # show full detail.

	# Detail Tables for each Category
	my $sth = $dbh->prepare("select * from worklog where school = ? and category = ?
				order by date, userid, id");

	
	foreach my $category ( @category ) {

	    my $cathours;
	    my $first = 1;
	    # See if we have any contract values and any work values.
	    $sth->execute( $school, $category);
	    if ($DBI::errstr){ print $DBI::errstr; die "$DBI::errstr\n"; };
	    while ( my $ref = $sth->fetchrow_hashref ) {
		my %r = %$ref;
		my $userid = $r{userid};
		
		if ( $first ) { # setup table
		    # Table Layout
		    print qq{<table cellpadding="3" border="1" cellspacing="0" };
		    print qq{style="margin-bottom:1em;">\n};
		    print qq{<tr><th>Name</th><th>Date</th><th>School</th><th>Hours</th>};
		    print qq{<th title="Work Type">WorkType</th>};
		    print qq{<caption style="font-weight:bold;font-size:120%;text-align:left;">};
		    print qq{$school - $category</caption>\n};
		    
		    $first = 0;
		}

		# Record lines (2)
		print qq{<tr><td><b>$names{$userid}</b> ($userid)</td>\n};
		print qq{<td>$r{date}</td><td>$r{school}</td><td>$r{hours}</td>\n};
		print qq{<td>$r{worktype}</td>};
		print qq{<tr><td colspan="7">$r{description}</td></tr>\n};
		
		$totalhours += $r{hours};
		$cathours += $r{hours};
	    }
	    if ( not $first ) {
		my $days = $cathours / 8;
		$days = round($days, 1);
		print qq{<tr><td colspan="5" style="background-color:#DDD;" class="bla">};
		print qq{Total Category Hours $cathours  ($days Days)</td></tr>\n};
		print qq{</table>\n};
	    }
	    
	} # end of category loop, details.
    
    } # end of school loop

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

    exit;

} # end of showReport();
