CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 sum of total hours is breaking my head

Print topic Send  topic

Author Message
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
View profile  Send private message
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
View profile  Send private message
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
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

MS Access to Web

Convert MS Access to Web.
Join thousands of Web developers who build Web applications with minimal coding.

CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.