#! /usr/bin/env perl

use DBI;

$database = $ARGV[0];
if (!$database) {die 'error: database name must be supplied on command line';}
if ($ARGV[1] == 1) {$report_for_diff = 1;} else {$report_for_diff = 0;}

$seconds_per_week = 5*8*60*60;
$hours_per_week = 5*8;
$units_per_FTE = 100;

$user = "root";
$password = "EthanTak9";
$hostname = "localhost";

$dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password);

$sql = "SELECT id, parentId, wbsId, name, work from Task";
$sth = DoQuery($sql);
while (@row = $sth->fetchrow_array) {
    ($id, $parentId, $wbsId, $name, $work) = @row;
    if ($name =~ /\[/) {
	$task{$id} = $name;
	$work{$id} = $work/$seconds_per_week;
	$parentId{$id} = $parentId;
	$scientist{$id} = 0;
	$contributed{$id} = 0;
	$wbs_id{$id} = $wbsId;
    }
}

$sql = "SELECT taskId, units from Allocation, Resource where resourceId = id AND name = \"Scientist\"";
$sth = DoQuery($sql);
while (@row = $sth->fetchrow_array) {
    ($taskId, $units) = @row;
    $scientist{$taskId} = $units/$units_per_FTE;
}

$sql = "SELECT taskId, units from Allocation, Resource where resourceId = id AND name = \"Contributed\"";
$sth = DoQuery($sql);
while (@row = $sth->fetchrow_array) {
    ($taskId, $units) = @row;
    $contributed{$taskId} = $units/$units_per_FTE;
}

$sql = "SELECT stdRate from Resource where name = \"Scientist\"";
$sth = DoQuery($sql);
($rateScientistHourly)  = $sth->fetchrow_array;
$rateScientist = $rateScientistHourly*$hours_per_week;

$title = "Labor and Costs: $database";

print <<EOM;
<html>
<head>
<title>$title</title>
</head>
<body>
EOM

print "<h1>$title</h1>\n";
print "<table border>\n";
print "<tr><th>WBS</th><th>Task</th><th>Labor (FTE-wks)</th><th>Sci'ist (%)</th><th>Contrib (%)</th><th>Cost (USD)</th></tr>\n";
$sum_work = 0;
$sum_cost = 0;
$sum_work_scientist = 0;
$sum_work_contributed = 0;
foreach $id (sort wbsly keys %task) {
    $total_staff =  $scientist{$id} + $contributed{$id};
    $frac_scientist = $scientist{$id}/$total_staff;
    $frac_contributed = $contributed{$id}/$total_staff;
    $cost = $frac_scientist*$work{$id}*$rateScientist;
    #print "$task{$id} $work{$id} $scientist{$id} $contributed{$id} $total_staff $cost\n";
    $sum_work += $work{$id};
    $sum_work_scientist += $frac_scientist*$work{$id};
    $sum_work_contributed += $frac_contributed*$work{$id};
    $sum_cost += $cost;
    $fsr = round(100*$frac_scientist);
    $fcr = round(100*$frac_contributed);
    $cr = round($cost);
    $tnowbs = remove_wbs($task{$id});
    if ($report_for_diff) {
	print "$wbs_id{$id}\t$task{$id}\t$work{$id}\t$scientist{$id}\t$contributed{$id}\n";
    } else {
	print "<tr><td>$wbs_id{$id}</td><td>$tnowbs</td><td>$work{$id}</td><td>$fsr</td><td>$fcr</td><td align=\"right\">$cr</tr>\n";
    }
}
print "</table>\n";
print "<ul>\n";
print "<li>scientist rate = $rateScientistHourly\n";
print "<li>sum work scientist= $sum_work_scientist\n";
print "<li>sum work contributed = $sum_work_contributed\n";
print "<li>sum work total = $sum_work\n";
print "<li>sum cost = $sum_cost\n";
print "</ul>\n";

print "</body>\n";
print "</html>\n";

exit 0;


#################################################################

sub DoQuery {
    my ($sql) = @_;
    my $sth = $dbh->prepare($sql)
        or die "Can't prepare $sql: $dbh->errstr\n";
    my $rv = $sth->execute
        or die "Can't execute the query $sql\n error: $sth->errstr\n";
    return $sth;
}

#################################################################

sub wbsly {
    $wbs_id{$a} <=> $wbs_id{$b};
}

#################################################################

sub round {
    my ($text) = @_;
    return sprintf("%.2f", $text);
}

#################################################################

sub remove_wbs {
    my ($text) = @_;
    split(/ \[/,$text);
    return $_[0];
}

#################################################################

#end of perl script
