#!/usr/local/bin/perl

use strict;
use warnings;
use CGI qw/:standard :html3 *table/;
use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
use DBI;
use Date::Calc qw( Add_Delta_DHMS Date_to_Days );

my $query = new CGI;
my $dbhost = "localhost";
my $dbuser = "username";
my $dbpass = "password";
my $mydbh = DBI->connect("DBI:mysql:database=tpsreports;host=$dbhost", $dbuser, $dbpass, {'RaiseError' => 1});
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time); $year += 1900; $mon++;
my $newStyle='
    <!-- 
    body	{ background-color: black; color:lightgray; }
    a:link      { color:gray; }
    a:hover	{ color:#FFC; background:#353 fixed; }
    tr.rfc	{ background-color: black; vertical-align: top; text-align: center }
    /* tr:hover > td, tr:hover > td > a			{ background-color: #f98620; color: black; } */
    /* tr:hover > td, tr:hover > td > a			{ background-color: black; color: white; } */
    /* tr:hover > td.ready, tr:hover > td.ready > a	{ background-color: black; color: cyan; } */
    td		{ font-size: 10pt; font-family: sans-serif; color: gray; }
    td.done     { font-size: 10pt; font-family: sans-serif; background-color: black; color: gray }
    td.notready { font-size: 10pt; font-family: sans-serif; background-color: black; color: red }
    td.ready    { font-size: 10pt; font-family: sans-serif; background-color: black; color: cyan }
    td.submitter	{ text-align: right; }
    -->';
my ($cookie, $user_id_cookie);
if ($query->param('user_id') =~ /(\d+)/) {
	$cookie = $query->cookie(-name=>'tps_user_id', -value=>"$1");
	$user_id_cookie = $1;
} elsif ($query->cookie('tps_user_id') =~ /(\d+)/) {
	$user_id_cookie = $1;
}
print header(-cookie=>$cookie);
print start_html(-title=>'Don\'t forget the cover sheet..', -style=>{-code=>$newStyle});
print '<table border="0" rules="rows" bgcolor="black" color="lightgray" cellspacing="2" cellpadding="3" width="75%">';
	## The first row is for project listings
	my @tharray = ("Project List", "Add New Project");
	foreach my $th (@tharray) { print "<th>$th</th>"; }
	print "\n";
	print "<tr align='center' valign='top'><td>\n";
		my $projectsquery = 'SELECT * FROM projects ORDER BY projectname';
		my $projectsth = $mydbh->prepare($projectsquery);
		$projectsth->execute();
		my $projectsuserquery = 'SELECT * FROM users ORDER BY username';
		my $projectusersth = $mydbh->prepare($projectsuserquery);
		$projectusersth->execute();
		print "<form name='startproject' method='post' action='tps.pl'>\n";
		print "<table>\n";
		print "<tr><td>Your name:</td><td><select name='user_id'>\n";
		while (my $ref = $projectusersth->fetchrow_hashref()) {
			print "\t<option value='" . $ref->{'user_id'} . "'";
			if ($user_id_cookie eq $ref->{'user_id'}) { print " SELECTED"; } else { print $query->cookie('tps_user_id'); }
			print ">" . $ref->{'username'} . "</option>\n";
		}
		print "</select></td></tr>\n";
		print "<tr><td>Project name:</td><td><select name='project_id'>\n";
		while (my $ref = $projectsth->fetchrow_hashref()) {
			print "\t<option value='" . $ref->{'project_id'} . "'";
			if ($user_id_cookie eq $ref->{'user_id'}) { print " SELECTED"; } else { print $query->cookie('tps_user_id'); }
			print ">" . $ref->{'projectname'} . "</option>\n";
		}
		print "</select></td></tr>\n";
		print "<tr><td></td><td><input name='action' type='hidden' value='start'><input name='submit' value='start' type='submit'></td></tr>\n";
		print "</table>\n";
		print "</form>\n";
	print "</td>\n";
	print "<td>\n";
		print "<form name='newproject' method='post' action='tps.pl'>\n";
		print "<table>\n";
		print "<tr><td>Project Name:</td><td><input name='newproject' type='textbox' size='30'></td></tr>\n";
		print "<tr><td></td><td><input name='action' type='hidden' value='addproject'><input name='submit' value='Add Project' type='submit'></td></tr>\n";
		print "</table>\n";
		print "</form>\n";
	print "</td></tr>\n";
	print "<th>Update project times</th><th>Add New User</th>\n";
	print "<tr align='center' valign='top'><td>\n";
		my $userquery = 'SELECT * FROM users ORDER BY username';
		my $usersth = $mydbh->prepare($userquery);
		$usersth->execute();
		print "<form name='selectuser' method='post' action='tps.pl'>\n";
		print "<table>\n";
		print "<tr><td>Your Name:</td><td><select name='user_id'>\n";
		while (my $ref = $usersth->fetchrow_hashref()) {
			print "\t<option value='" . $ref->{'user_id'} . "'";
			if ($user_id_cookie eq $ref->{'user_id'}) { print " SELECTED"; } else { print $query->cookie('tps_user_id'); }
			print ">" . $ref->{'username'} . "</option>\n";
		}
		print "</select></td></tr>\n";
		print "<tr><td></td><td><input name='action' type='hidden' value='selectuser'><input name='submit' value='Select User' type='submit'></td></tr>\n";
		print "</table>\n";
		print "</form>\n";
	print "</td>\n";
	print "<td>\n";
		print "<form name='newuser' method='post' action='tps.pl'>\n";
		print "<table>\n";
		print "<tr><td>Your name:</td><td><input name='username' type='textbox' size='30'></td></tr>\n";
		print "<tr><td>Playbook ID:</td><td><input name='playbook_id' type='textbox' size='30'></td></tr>\n";
		print "<tr><td>Manager:</td><td><select name='manager_id'>\n";
		my $managerquery = 'SELECT * FROM users ORDER BY username';
		my $managersth = $mydbh->prepare($managerquery);
		$managersth->execute();
		while (my $ref = $managersth->fetchrow_hashref()) {
			print "\t<option value='" . $ref->{'user_id'} . "'>" . $ref->{'username'} . "</option>\n";
		}
		print "</select></td></tr>\n";
		print "<tr><td></td><td><input name='action' type='hidden' value='adduser'><input name='submit' value='Add User' type='submit'></td></tr>\n";
		print "</table>\n";
		print "</form>\n";
	print "</td></tr>\n";
	if ($query->param('action') =~ /selectuser/) { selectuser(); }
	if ($query->param('action') =~ /edittimes/) { edittimes(); }
	if ($query->param('action') =~ /updatetimes/) { updatetimes(); }

	print "<th>Generate Reports</th>\n";
	print "<tr valign='top'><table>\n";
	print "<tr><td>By Project:</td></tr>\n";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=projecttotal'>Total Time Spent On Each Project</a></td></tr>";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=projectweek'>Total Time Spent On Each Project This Week</a></td></tr>";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=projectday'>Total Time Spent On Each Project Today</a></td></tr>";
	print "<tr><td>By Systems Administrator:</td></tr>\n";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=satotal'>Total Time Spent By Each SA This Month</a></td></tr>";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=saweek'>Total Time Spent By Each SA This Week</a></td></tr>";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=saday'>Total Time Spent By Each SA Today</a></td></tr>";
	print "<tr><td>Detailed Breakdown:</td></tr>\n";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=brokentotal'>SA per Project Breakdown This Month</a></td></tr>";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=brokenweek'>SA per Project Breakdown This Week</a></td></tr>";
	print "<tr><td></td><td><a href='/cgi-bin/tps.pl?action=report&type=brokenday'>SA per Project Today</a></td></tr>";
	if ($query->param('action') =~ /report/) { generatereport(); }
	print "</table></tr>";



## Action subs below here.
## These take POSTed and GETed data and dothings with it.

if ($query->param('action') =~ /adduser/) {
	my $username = $query->param('username');
	my $playbook_id = $query->param('playbook_id');
	my $manager_id = $query->param('manager_id');
	my $broken;
	foreach my $check (qw/username playbook_id manager_id/) {
		if ($query->param($check) !~ /[\d\w]/) { print "<h3>ERROR: Field $check was blank! Button pressed: ".$query->param('action')."</h3>\n"; $broken = "yes"; }
	}
	my $adduserquery = $mydbh->do("INSERT INTO users (username, playbook_id, manager_id) VALUES ('$username', '$playbook_id', '$manager_id')") unless defined $broken;
}
if ($query->param('action') =~ /start/) {
	my $user_id = $query->param('user_id');
	my $project_id = $query->param('project_id');
	my $broken;
	foreach my $check (qw/user_id project_id/) {
		if ($query->param($check) !~ /[\d\w]/) { print "<h3>ERROR: Field $check was blank! Button pressed: ".$query->param('action')."</h3>\n"; $broken = "yes"; }
	}
	my $adduserquery = $mydbh->do("INSERT INTO timespent (user_id, project_id, starttime) VALUES ('$user_id', '$project_id', NOW())") unless defined $broken;
	selectuser();
}
sub selectuser {
	my $user_id = $query->param('user_id');
	my $timequery = "SELECT * FROM timespent,projects WHERE timespent.user_id = '$user_id' AND timespent.project_id = projects.project_id";
	my $timesth = $mydbh->prepare($timequery);
	$timesth->execute();
	print "<form name='updatetime' action='tps.pl' method='post'>\n";
	print "<tr><td colspan='2'><table>\n";
	my @tharray = ("Project Name", "Start Time", "End Time");
	foreach my $th (@tharray) { print "<th>$th</th>"; }
	print "\n";
	while (my $ref = $timesth->fetchrow_hashref()) {
		my $timespent_id = $ref->{'timespent_id'};
		my $projectname = $ref->{'projectname'};
		my $starttime = $ref->{'starttime'};
		my $endtime = $ref->{'endtime'};
		print "<tr><td><a href='/cgi-bin/tps.pl?action=edittimes&timespent_id=$timespent_id&user_id=$user_id'>$projectname</a></td><td>$starttime</td><td>$endtime</td></tr>\n";
	}
	print "</table></td></tr>\n";
	print "</form>\n";
}
sub edittimes {
	my $timespent_id = $query->url_param('timespent_id');
	my $user_id = $query->url_param('user_id');
	my $editquery = "SELECT * FROM timespent,projects WHERE timespent.timespent_id = '$timespent_id' AND timespent.project_id = projects.project_id";
	my $editsth = $mydbh->prepare($editquery);
	$editsth->execute();
	print "<form name='updatetime' action='tps.pl' method='post'>\n";
	print "<tr><td colspan='2'><table>\n";
	print "<th>Project Name</th><th colspan='5'>Start Time</th><th colspan='5'>End Time</th>\n";
	while (my $ref = $editsth->fetchrow_hashref()) {
		my $starttime = $ref->{'starttime'};
		my $endtime = $ref->{'endtime'};
		my $projectname = $ref->{'projectname'};
		my ($s_year, $s_month, $s_day, $s_hour, $s_min, $s_sec) = split(/[\s\-\:]/, $starttime);
		my ($e_year, $e_month, $e_day, $e_hour, $e_min, $e_sec) = split(/[\s\-\:]/, $endtime);
		print "<tr><td></td>";
		## Loop through printing the td headings twice, once for the start and once for the end times
		for (my $i=0; $i < 2; $i++) {
			foreach my $timespan ('Year', 'Month', 'Day', 'Hour', 'Minute') {
				print "<td>$timespan</td>";
			}
		}
		print "</tr>\n";
		print "<tr><td>$projectname</td>";
		print "<td align='left'><select name='s_year'>";
		foreach my $s_year_opt ($s_year, '2005', '2006', '2007') {
			print "<option value='$s_year_opt'>$s_year_opt</option>\n";
		}
		print "</select></td><td>-<select name='s_month'>";
		foreach my $s_month_opt ($s_month, (1..12)) {
			print "<option value='$s_month_opt'>$s_month_opt</option>\n";
		}
		print "</select></td><td>-<select name='s_day'>";
		foreach my $s_day_opt ($s_day, (1..31)) {
			print "<option value='$s_day_opt'>$s_day_opt</option>\n";
		}
		print "</select></td><td> <select name='s_hour'>";
		foreach my $s_hour_opt ($s_hour, (0..23)) {
			print "<option value='$s_hour_opt'>$s_hour_opt</option>\n";
		}
		print "</select></td><td>:<select name='s_min'>";
		foreach my $s_min_opt ($s_min, 0, 10, 20, 30, 40, 50) {
			print "<option value='$s_min_opt'>$s_min_opt</option>\n";
		}
		print "</td>";
		print "<td align='right'><select name='e_year'>";
		foreach my $e_year_opt ($e_year, '2005', '2006', '2007') {
			print "<option value='$e_year_opt'>$e_year_opt</option>\n";
		}
		print "</select></td><td>-<select name='e_month'>";
		foreach my $e_month_opt ($e_month, (1..12)) {
			print "<option value='$e_month_opt'>$e_month_opt</option>\n";
		}
		print "</select></td><td>-<select name='e_day'>";
		foreach my $e_day_opt ($e_day, (1..31)) {
			print "<option value='$e_day_opt'>$e_day_opt</option>\n";
		}
		print "</select></td><td> <select name='e_hour'>";
		foreach my $e_hour_opt ($e_hour, (0..23)) {
			print "<option value='$e_hour_opt'>$e_hour_opt</option>\n";
		}
		print "</select></td><td>:<select name='e_min'>";
		foreach my $e_min_opt ($e_min, 0, 10, 20, 30, 40, 50) {
			print "<option value='$e_min_opt'>$e_min_opt</option>\n";
		}
		print "</select><input name='timespent_id' type='hidden' value='$timespent_id'><input name='action' type='hidden' value='updatetimes'><input name='user_id' type='hidden' value='$user_id'></td></tr>\n";
		print "<tr><td colspan='11' align='right'>Or minutes worked: <input name='minsworked' type='text'></td></tr>\n";
		print "<tr><td colspan='11' align='right'><input name='submit' value='Update Times' type='submit'></td>\n";
	}
	print "</table></td></tr>\n";
}
sub updatetimes {
	my $timespent_id = $query->param('timespent_id');
	my $s_year = $query->param('s_year');
	my $s_month = $query->param('s_month');
	my $s_day = $query->param('s_day');
	my $s_hour = $query->param('s_hour');
	my $s_min = $query->param('s_min');
	my $s_sec = 0;
	my $e_year = $query->param('e_year');
	my $e_month = $query->param('e_month');
	my $e_day = $query->param('e_day');
	my $e_hour = $query->param('e_hour');
	my $e_min = $query->param('e_min');
	my $e_sec = 0;
	my $broken;
	my $starttime = "$s_year-$s_month-$s_day $s_hour:$s_min";
	my $endtime;
	if ($query->param('minsworked') =~ /^\d+$/) {
		($e_year,$e_month,$e_day,$e_hour,$e_min,$e_sec) = Add_Delta_DHMS($s_year,$s_month,$s_day,$s_hour,$s_min,$s_sec,0,0,$query->param('minsworked'),0);
		$endtime = "$e_year-$e_month-$e_day $e_hour:$e_min";
	} else {
		$endtime = "$e_year-$e_month-$e_day $e_hour:$e_min";
	}
	my @startdate = ($s_year,$s_month,$s_day,$s_hour,$s_min,0);
	my @enddate = ($e_year,$e_month,$e_day,$e_hour,$e_min,0);
	my @startd1 = ( Date_to_Days(@startdate[0..2]), ($startdate[3]*60+$startdate[4])*60+$startdate[5] );
	my @endd1 = ( Date_to_Days(@enddate[0..2]), ($enddate[3]*60+$enddate[4])*60+$enddate[5] );
	my @datediff = ( $endd1[0]-$startd1[0], $endd1[1]-$startd1[1] );
	if ($datediff[0] < 0 and $datediff[1] > 0) {
		print "<h3>ERROR: Start Time was after End Time!</h3>\n"; $broken = "yes";
	}
	foreach my $check (qw/timespent_id s_year s_month s_day s_hour s_min e_year e_month e_day e_hour e_min/) {
		if ($check !~ /[\d\w]/) { print "<h3>ERROR: Field $check was blank! Button pressed: ".$query->param('action')."</h3>\n"; $broken = "yes"; }
	}
	my $adduserquery = $mydbh->do("UPDATE timespent SET starttime = '$starttime', endtime = '$endtime' WHERE timespent_id = '$timespent_id'") unless defined $broken;
	selectuser();
}
sub generatereport {
	my ($sqlquery, $field);
	if ($query->param('type') =~ /projecttotal/) {
		$sqlquery = "SELECT projectname, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,projects WHERE DATE_SUB(CURDATE(), INTERVAL 999 DAY) <= starttime AND timespent.project_id = projects.project_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /projectweek/) {
		$sqlquery = "SELECT projectname, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,projects WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= starttime AND timespent.project_id = projects.project_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /projectday/) {
		$sqlquery = "SELECT projectname, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,projects WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= starttime AND timespent.project_id = projects.project_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /satotal/) {
		$sqlquery = "SELECT username, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,users WHERE DATE_SUB(CURDATE(), INTERVAL 999 DAY) <= starttime AND timespent.user_id = users.user_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /saweek/) {
		$sqlquery = "SELECT username, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,users WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= starttime AND timespent.user_id = users.user_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /saday/) {
		$sqlquery = "SELECT username, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,users WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= starttime AND timespent.user_id = users.user_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /brokentotal/) {
		$sqlquery = "SELECT username,projectname, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,users,projects WHERE DATE_SUB(CURDATE(), INTERVAL 999 DAY) <= starttime AND timespent.user_id = users.user_id AND timespent.project_id = projects.project_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /brokenweek/) {
		$sqlquery = "SELECT username,projectname, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,users,projects WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= starttime AND timespent.user_id = users.user_id AND timespent.project_id = projects.project_id AND endtime IS NOT NULL";
	} elsif ($query->param('type') =~ /brokenday/) {
		$sqlquery = "SELECT username,projectname, (TO_DAYS(endtime)*24*3600+TIME_TO_SEC(endtime))-((TO_DAYS(starttime)*24*3600+TIME_TO_SEC(starttime))) AS diff FROM timespent,users,projects WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= starttime AND timespent.user_id = users.user_id AND timespent.project_id = projects.project_id AND endtime IS NOT NULL";
	}
	## This is a complicated (looking) query. In fact, all it is doing, is getting the endtime and starttime
	## columns from the timespent table, and subtracting one from the other. When we get this on a DB with
	## MySQL v 4.1.1 or higher, we can replace it with:
	## SELECT projectname, TIMEDIFF(endtime,starttime) AS diff FROM timespent,projects WHERE timespent.project_id = projects.project_id AND endtime IS NOT NULL
	## Until then, we have to do this messed up math, to convert the date to seconds, and subract one from
	## the other, then SEC_TO_TIME() it back into hours. Doh.
	my $rptsth = $mydbh->prepare($sqlquery);
	$rptsth->execute();
	my %totaltime;
	print "<tr><td></td><td><table>\n";
	if ($query->param('type') =~ /project(total|week|day)/) {
		print "<th align='left'>Project Name</th><th align='left'>Hours Worked</th>\n";
		while (my $ref = $rptsth->fetchrow_hashref()) {
			my $projectname = $ref->{'projectname'};
			my $diff = $ref->{'diff'};
			$totaltime{$projectname} += $diff;
		}
		foreach my $timekeys (keys %totaltime) {
			$totaltime{$timekeys} = sprintf("%.2f", $totaltime{$timekeys} / 3600);
			print "<tr><td>$timekeys</td><td>$totaltime{$timekeys}</td></tr>\n";
		}
	} elsif ($query->param('type') =~ /sa(total|week|day)/) {
		print "<th align='left'>Systems Administrator</th><th align='left'>Hours Worked</th>\n";
		while (my $ref = $rptsth->fetchrow_hashref()) {
			my $username = $ref->{'username'};
			my $diff = $ref->{'diff'};
			$totaltime{$username} += $diff;
		}
		foreach my $timekeys (keys %totaltime) {
			$totaltime{$timekeys} = sprintf("%.2f", $totaltime{$timekeys} / 3600);
			print "<tr><td>$timekeys</td><td>$totaltime{$timekeys}</td></tr>\n";
		}
	} elsif ($query->param('type') =~ /broken(total|week|day)/) {
		print "<th align='left'>Systems Administrator</th><th align='left'>Project Name</th><th align='left'>Hours Worked</th>\n";
		while (my $ref = $rptsth->fetchrow_hashref()) {
			my $brokenadmin = $ref->{'username'};
			my $brokenproject = $ref->{'projectname'};
			my $diff = $ref->{'diff'};
			$totaltime{$brokenadmin}{$brokenproject} += $diff;
		}
		foreach my $namekeys (keys %totaltime) {
			foreach my $projectkeys (keys %{ $totaltime{$namekeys} }) {
				$totaltime{$namekeys}{$projectkeys} = sprintf("%.2f", $totaltime{$namekeys}{$projectkeys} / 3600);
				print "<tr><td>$namekeys</td><td>$projectkeys</td><td>$totaltime{$namekeys}{$projectkeys}</td></tr>\n";
			}
		}
	}
	print "</table></tr></td>\n";
}

print '</table>';
print end_html;