#! /usr/bin/env perl

use DBI;

$database[0] = $ARGV[0];
$database[1] = $ARGV[1];
if (!$database[0] || !$database[1]) {die 'error: two database names must be supplied on command line';}
$title = "Changes from $database[0] to $database[1]";

$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[0]:$hostname", $user, $password);

@wbsId = ();
%taskname = ();
for ($idb = 0; $idb < 2; $idb++) {
    $sql = "SELECT wbsId, name from $database[$idb].Task";
    $sth = DoQuery($sql);
    while (@row = $sth->fetchrow_array) {
	($wbsId, $name) = @row;
	#print "$idb, $wbsId, $name\n";
	push (@wbsId, $wbsId);
	$taskname{$wbsId} = $name;
    }
}

for ($i = 0; $i <= $#wbsId; $i++) {
    #print "$i, $wbsId[$i]\n";
}
@wbsId_sort = sort (@wbsId);
for ($i = 0; $i <= $#wbsId_sort; $i++) {
    #print "$i, $wbsId_sort[$i]\n";
}
@wbsId_merge = ();
$last_id = "0000000";
for ($i = 0; $i <= $#wbsId_sort; $i++) {
    $this_id = $wbsId_sort[$i];
    if ($this_id != $last_id) {
	push (@wbsId_merge, $this_id); 
    }
    $last_id = $this_id;
}
for ($i = 0; $i <= $#wbsId_merge; $i++) {
    #print "$i, $wbsId_merge[$i]\n";
}

for ($idb = 0; $idb < 2; $idb++) {

    $sql = "USE $database[$idb]";
    $sth = DoQuery($sql);

    $sql = "SELECT wbsId, units from Allocation, Resource, Task where taskId = Task.id AND resourceId = Resource.id AND Resource.name = \"Scientist\"";
    $sth = DoQuery($sql);
    while (@row = $sth->fetchrow_array) {
	($wbsId, $units) = @row;
	$scientist{$wbsId} = $units/$units_per_FTE;
    }
    
    $sql = "SELECT wbsId, units from Allocation, Resource, Task where taskId = Task.id AND resourceId = Resource.id AND Resource.name = \"Contributed\"";
    $sth = DoQuery($sql);
    while (@row = $sth->fetchrow_array) {
	($wbsId, $units) = @row;
	$contributed{$wbsId} = $units/$units_per_FTE;
    }
    
    $sql = "SELECT stdRate from Resource where name = \"Scientist\"";
    $sth = DoQuery($sql);
    ($rateScientistHourly)  = $sth->fetchrow_array;
    $rateScientist = $rateScientistHourly*$hours_per_week;
    
    $sql = "SELECT wbsId, work from Task";
    $sth = DoQuery($sql);
    while (@row = $sth->fetchrow_array) {
	($wbsId, $work) = @row;
	$work{$wbsId}[$idb] = $work/$seconds_per_week;
	$total_staff =  $scientist{$wbsId} + $contributed{$wbsId};
	$frac_scientist = $scientist{$wbsId}/$total_staff;
	$frac_scientist{$wbsId}[$idb] = $frac_scientist;
	$cost{$wbsId}[$idb] = $frac_scientist*$work{$wbsId}[$idb]*$rateScientist;
	#print "$idb, $wbsId, $work{$wbsId}[$idb], $cost{$wbsId}[$idb]\n";
    }
}

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>Change in Labor<br>(FTE-wks)</th><th>Change in Cost<br>(USD)</th><th>Comment</th></tr>\n";
$total_delta_work = 0;
$total_delta_cost = 0;
foreach $wbsId (@wbsId_merge) {
    $comment = "";
    if (! $work{$wbsId}[0]) {
	$work{$wbsId}[0] = 0;
	$cost{$wbsId}[0] = 0;
	$comment = "added";
    }
    if (! $work{$wbsId}[1]) {
	$work{$wbsId}[1] = 0;
	$cost{$wbsId}[1] = 0;
	$comment = "dropped";
    }
    $delta_work = $work{$wbsId}[1] - $work{$wbsId}[0];
    $total_delta_work += $delta_work;
    $delta_cost = $cost{$wbsId}[1] - $cost{$wbsId}[0];
    $total_delta_cost += $delta_cost;
    $dwr = round($delta_work);
    $dcr = round($delta_cost);
    $name = remove_wbs($taskname{$wbsId});
    if ($delta_work == 0 && $delta_cost == 0) {$comment = "no change"}
    if ($frac_scientist{$wbsId}[0] == 0 && $frac_scientist{$wbsId}[1] == 1.0/11.0){$comment = "10% scientist labor added";}
    if (! $comment) {$comment = "change of scope";}
    print "<tr><td>$wbsId</td><td>$name</td><td align=\"right\">$dwr</td><td align=\"right\">$dcr</td><td>$comment</td></tr>\n";
}
print "</table>\n";
print "<ul>\n";
$tdwr = round($total_delta_work);
print "<li>Total change in labor = $tdwr</li>\n";
$tdcr = round($total_delta_cost);
print "<li>Total change in cost = $tdcr</li>\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 round {
    my ($text) = @_;
    return sprintf("%.2f", $text);
}

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

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

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

#end of perl script
