it--solutions
Posts: 24
|
Posted: 12/09/2014, 12:49 PM |
|
I have a table of timesheets, each has a start_time and a finish_time in yyyy-mm-dd HH:nn:ss format in the database.
database table format
cID | userID | jobID | task | subject | location | description | start_time | end_time | color
Grid display fields
task | location | description | userID | start_time | end_time | "hours"
"hours" being a new label not linked to a database table field.
I have succeeded in creating some custom code attached to a new label "hours" which takes the timestamps, converts to epoch, subtracts and gives me a total for that entry of "hours" and it works well...
// get hours between 2 timestamps
$db = new clsDBdatabase();
$start_time = $calendar->start_time_txt->GetValue();
$end_time = $calendar->end_time_txt->GetValue();
$start = strtotime($start_time);
$end = strtotime($end_time);
$epochhours = $end - $start;
$hours = ($epochhours /60)/60;
$calendar->hours->SetValue($hours);
$db->next_record();
$db->close();
I have added another label to the bottom of the grid next to the page selection buttons called "total_hours"
What I am trying to get my head around is how can I total all "hours" labels to give "total_hours"
it normally could be done if hours was a database field but it cant be created and used in this instance as we have a JQuery GUI Calendar reading and updating the same table.
Any help would be most appreciated
|
|
|
eratech
Posts: 513
|
Posted: 12/09/2014, 2:43 PM |
|
Hi - I've built a similar timesheet program, but I prefer to do the hour calculation in SQL as it's much simpler and comes direct from the DB.
To make it easier on myself as I had both Detail (daily hours worked) and summary over several weeks, I created a DB view with the details I needed and I could also do a 'sum(hours)' on it to get a total.
You can also use the method that the Reporting side of CCS uses - Before Show (or maybe at the topof the page in the global scope) for the Grid do a Declare Variable of say, 'gtotal_hours', then in the Grid's Before Show Row, get the current record hours and add to gtotal_hours.
(actually, create a web report and see the code, if you have it available)
Hope that helps
(and BTW - from the code you outline above, you don't need to the $db connection/next_record /close - you are doing all the calcs using values from the existing recordset/controls. No DB needed)
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
it--solutions
Posts: 24
|
Posted: 01/19/2015, 3:21 AM |
|
after hours of trying different scriptings here is the result that works
$jobID = CCGetFromGet("jobID", 0);
$total_hours = CCDLookUP("sum(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time))/60/60", "calendar", "jobID = '$jobID'", $db);
$users_common_field_names->total_hours->SetValue($total_hours);
returns total hours from multiple entries saved as start & end timestamps in the mysql database
|
|
|
|