skutter
07-31-2008, 04:19 PM
As my first project I am writing a small bridging application that retrieves data from an existing timesheet database.
The form displays company, project, start time, end time, notes and I have used J de Silva's code to calculate the time difference between the start time and the end time.
This is then displayed as sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] )
I now need to take that value from each of the rows and add it together to give me a total time which has me stumped.
I am open to any assistance.
Just for note this is for internal use only to help me produce reports and and a little learning. I do believe in a baptism of fire when developing new skills.
Althoughthe code is spread across a few files my min file is here:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Client Support Record</title>
<link href="./css/style.css" rel="stylesheet" type="text/css"></link>
</head>
<body>
<br />
<?php
//turn on error reporting
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting (E_ALL);
//include login information and other creds.
include ('./include/credentials.php');
include ('./include/timediff.php');
include ('./include/table_names.php');
include ('./include/clientdets.php');
include ('./langsettings.php');
require_once('DB.php');
echo '<h1>' . $TEXT['rpt-head'] . '</h1><br /><br />';
//connect
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />".DB::errorMessage($connection));
}
// Assign the query
$table = "SELECT $CLIENT_TABLE .organisation, $PROJECT_TABLE.title, $TIMES_TABLE.start_time, $TIMES_TABLE.end_time, $TIMES_TABLE.log_message, $TASK_TABLE.name FROM $db_database.timesheet_client $CLIENT_TABLE , $db_database.timesheet_project $PROJECT_TABLE, $db_database.timesheet_task $TASK_TABLE, $db_database.$TIMES_TABLE $TIMES_TABLE WHERE ($CLIENT_TABLE .client_id=$PROJECT_TABLE.client_id) AND ($PROJECT_TABLE.proj_id=$TASK_TABLE.proj_id And $PROJECT_TABLE.proj_id=$TIMES_TABLE.proj_id) AND ($TASK_TABLE.task_id=$TIMES_TABLE.task_id) AND ($TIMES_TABLE.start_time>{ts '$starttime'} And $TIMES_TABLE.end_time<{ts '$endtime'}) AND ($CLIENT_TABLE .organisation LIKE '$company') ORDER BY $CLIENT_TABLE .organisation, $TIMES_TABLE.end_time";
//Excecute the query
$result = $connection->query($table);
if (DB::isError($result)){
die("Could not query the database:<br />$table ".DB::errorMessage($result));
}
//fetch and display the results
echo('<table border="1">');
echo '<tr><th>',$TEXT['rpt-attrib1'],'</th><th>',$TEXT['rpt-attrib2'],'</th><th>',$TEXT['rpt-attrib3'],'</th><th>',$TEXT['rpt-attrib4'],'</th><th>',$TEXT['rpt-attrib5'],'</th><th>',$TEXT['rpt-attrib6'],'</th></tr>';
while($result_row = $result->fetchRow()){
echo "<tr><td>";
echo $result_row[0] . '</td><td>';
echo $result_row[1] . '</td><td>';
echo $result_row[2] . '</td><td>';
echo $result_row[3] . '</td><td>';
echo stripslashes($result_row[4]) . '</td><td>'; //stripslashes to remove the odd charachters from the MySQL output
//get the difference in time between the start and finish times.
// a START time value from the query
$start = $result_row[2];
// an END time value from the query
$end = $result_row[3];
// what is the time difference between $end and $start?
if( $diff=@get_time_difference($start, $end) )
{
echo //output the difference in a readable format
sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] ) . '</td></tr>';
}
else //Provide an error feedback.
{
echo "Time cannot be calculated";
}
}
echo("</table><br />");
//start the total code
$time = 0;
// Left this in just a s a placemarker boy is it vexing me!
$time = sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] );
$total_time = 0;
$total_time = $time;
//New table for the monthly total .
echo('<table border="1">');
echo '<tr><th>',$TEXT['rpt-total'],'</th><th>';
echo $total_time . '</th></tr>';
echo('</table><br />');
//Close the connection after the results have been displayed.
$connection->disconnect();
$timestamp= time();
echo 'Page created,<br />' . date("d/m/y G.i:s" ,$timestamp);
?>
</body>
</html>
The form displays company, project, start time, end time, notes and I have used J de Silva's code to calculate the time difference between the start time and the end time.
This is then displayed as sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] )
I now need to take that value from each of the rows and add it together to give me a total time which has me stumped.
I am open to any assistance.
Just for note this is for internal use only to help me produce reports and and a little learning. I do believe in a baptism of fire when developing new skills.
Althoughthe code is spread across a few files my min file is here:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Client Support Record</title>
<link href="./css/style.css" rel="stylesheet" type="text/css"></link>
</head>
<body>
<br />
<?php
//turn on error reporting
ini_set('display_errors',1);
ini_set('display_startup_errors',1);
error_reporting (E_ALL);
//include login information and other creds.
include ('./include/credentials.php');
include ('./include/timediff.php');
include ('./include/table_names.php');
include ('./include/clientdets.php');
include ('./langsettings.php');
require_once('DB.php');
echo '<h1>' . $TEXT['rpt-head'] . '</h1><br /><br />';
//connect
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />".DB::errorMessage($connection));
}
// Assign the query
$table = "SELECT $CLIENT_TABLE .organisation, $PROJECT_TABLE.title, $TIMES_TABLE.start_time, $TIMES_TABLE.end_time, $TIMES_TABLE.log_message, $TASK_TABLE.name FROM $db_database.timesheet_client $CLIENT_TABLE , $db_database.timesheet_project $PROJECT_TABLE, $db_database.timesheet_task $TASK_TABLE, $db_database.$TIMES_TABLE $TIMES_TABLE WHERE ($CLIENT_TABLE .client_id=$PROJECT_TABLE.client_id) AND ($PROJECT_TABLE.proj_id=$TASK_TABLE.proj_id And $PROJECT_TABLE.proj_id=$TIMES_TABLE.proj_id) AND ($TASK_TABLE.task_id=$TIMES_TABLE.task_id) AND ($TIMES_TABLE.start_time>{ts '$starttime'} And $TIMES_TABLE.end_time<{ts '$endtime'}) AND ($CLIENT_TABLE .organisation LIKE '$company') ORDER BY $CLIENT_TABLE .organisation, $TIMES_TABLE.end_time";
//Excecute the query
$result = $connection->query($table);
if (DB::isError($result)){
die("Could not query the database:<br />$table ".DB::errorMessage($result));
}
//fetch and display the results
echo('<table border="1">');
echo '<tr><th>',$TEXT['rpt-attrib1'],'</th><th>',$TEXT['rpt-attrib2'],'</th><th>',$TEXT['rpt-attrib3'],'</th><th>',$TEXT['rpt-attrib4'],'</th><th>',$TEXT['rpt-attrib5'],'</th><th>',$TEXT['rpt-attrib6'],'</th></tr>';
while($result_row = $result->fetchRow()){
echo "<tr><td>";
echo $result_row[0] . '</td><td>';
echo $result_row[1] . '</td><td>';
echo $result_row[2] . '</td><td>';
echo $result_row[3] . '</td><td>';
echo stripslashes($result_row[4]) . '</td><td>'; //stripslashes to remove the odd charachters from the MySQL output
//get the difference in time between the start and finish times.
// a START time value from the query
$start = $result_row[2];
// an END time value from the query
$end = $result_row[3];
// what is the time difference between $end and $start?
if( $diff=@get_time_difference($start, $end) )
{
echo //output the difference in a readable format
sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] ) . '</td></tr>';
}
else //Provide an error feedback.
{
echo "Time cannot be calculated";
}
}
echo("</table><br />");
//start the total code
$time = 0;
// Left this in just a s a placemarker boy is it vexing me!
$time = sprintf( '%02d:%02d', $diff['hours'], $diff['minutes'] );
$total_time = 0;
$total_time = $time;
//New table for the monthly total .
echo('<table border="1">');
echo '<tr><th>',$TEXT['rpt-total'],'</th><th>';
echo $total_time . '</th></tr>';
echo('</table><br />');
//Close the connection after the results have been displayed.
$connection->disconnect();
$timestamp= time();
echo 'Page created,<br />' . date("d/m/y G.i:s" ,$timestamp);
?>
</body>
</html>