Timesheets calculation

[mondejars]

Hi there, first time posting on the forum, not a lot of php or SQL skills.

I have a timesheets list with the following elements:

User (User element).
Date (Date element).
Time in (time element, '{timesheets___time_in}', time type on db).
Time out (time element, '{timesheets___time_out}' time type on db).
Pause (dropdown, '{timesheets___pause}', time type).

Field types modification is locked in list settings.

I have been reading a lot about how to calculate durations on Fabrik and tried a lot of example codes (php, SQL), this code is returning the right values for me on a calc element:

$db = JFactory::getDbo();
$query = "SELECT SEC_TO_TIME(TIME_TO_SEC('{timesheets___time_out}')
- TIME_TO_SEC('{timesheets___time_in}')
- TIME_TO_SEC('{timesheets___pause}'))";
$db->setQuery($query);
$duration = $db->loadResult();
return $duration;

The only problem I am having is when there is an overnight shift, it returns a negative value. I understand there has to be a condition to add a day like "IF '{timesheets___time_out}' < '{timesheets___time_in}' ELSE ..." so I have tried using DATEADD, but as I said before, I am not really skilful with SQL therefore no success.

Could you guys please point me in the right direction to keep investigating how to do this?

Thank you very much in advance.

PS. Fabrik is amazing

 

[cheesegrits]

Not sure why you are doing a query there. You already have the times in the placeholders, no need to query the database.

However, there is a wrinkle when dealing with the time element data in a calc, because it can be in two different formats, depending on whether the calc is running on submit, or when rendering the element (in list or form/details view).

So you have to do it like this ...

// our eventual return value, default to 0
$totalSec = 0;

// get the time in, default to 0
$timeInSec = 0;
$timeIn = $data['timesheets___time_in_raw'];
// if it's an array (during submission), turn it into a "xx:xx" string
if (is_array($timeIn)) {
   $timeIn = implode(':', $timeIn);
}
// sanity check we have a value
if (!empty($timeIn)) {
   // bust xx:xx string into an array of hours and minutes
   $timeIn = explode(':', $timeIn);
  // calculate seconds from hours and minutes
   $timeInSec = ($timeIn[0] * 60 * 60) + ($timeIn[1] * 60);
}

// do the same for time out ...
$timeOutSec = 0;
$timeOut = $data['timesheets___time_out_raw'];
if (is_array($timeOut)) {
   $timeIn = implode(':', $timeOut);
}
if (!empty($timeOut)) {
   $timeOut = explode(':', $timeOut);
   $timeOutSec = ($timeOut[0] * 60 * 60) + ($timeOut[1] * 60);
}

// do the same for pause
$timePauseSec = 0;
$timePause = $data['timesheets___pause_raw'];
if (is_array($timePause)) {
   $timePause = implode(':', $timePause);
}
if (!empty($timePause)) {
   $timePause = explode(':', $timePause);
   $timePause = ($timePause[0] * 60 * 60) + ($timePause[1] * 60);
}

// if we got a time in and out ...
if (!empty($timeInSec) && !empty($timeOutSec)) {
  // if necessary, add a day to time out
  if ($timeOutSec <= $timeInSec) {
     $timeOutSec += 24 * 60 * 60 * 60;
  }
  // calculate the total
  $totalSec = $timeOutSec - $timeInSec - $timePauseSec; 
}

// w00t!  return
return $totalSec;

NOTE - the above assumes you are only using hours:minutes as your format. If you are using seconds, you'd have to add a $timeIn[2] (etc) to the seconds calculations.

-- hugh

 

[mondejars]

Hi Hugh, thank you for your quick and precise answer, I think I understand how this method works.
After implementing it I have realised that it is doing the calculation on the fly and not storing it in the db. Unfortunately I need this values stored because I need to to SUM the column total to get the user totals. Pause calculation is not working as well.

I am not using AJAX calculation, just save on submit as you suggested.

It is returning the value in seconds but I can sort this out using return gmdate("H:i", $totalSec); to fix the format.

My first post should have been a bit more complete information wise, I have attached a couple of pictures with my setup and list view.

Thanks again, learning slowly but surely.

 

[cheesegrits]

Calc's always store their values to the database on submission of the form. The "Only calc on save" option simply controls whether they then re-calc when displaying in list or form/details views.

This means that if you add your calc to the form AFTER you already have data in the form, then you either have to manually edit and save each existing record, to force the submission calc to run, or you need to run some query in MySQL by hand to set the existing values.

-- hugh

 

[mondejars]

Right, so I have emptied the table to start fresh with your clean code on it (no gmdate format correction) and cleared cache but unfortunately I can confirm that neither is calculating pause nor saving in db.
Could this be a collision with anything else on Joomla or a bug? I did a Github update like three days ago, I have attached my SystemInfo (in case it helps tracking the issue).

Thanks again and apologies for being a bit annoying...

 

[cheesegrits]

It might just be a bug in my code, which I just wrote off the top of my head. The only way I could really help with it any more is to log on to your site and put some debug statements in, and run some form submissions. Not something I'd usually do in Standard, more of a Pro thing, but as you are a new client ... I'll go the extra mile.

Is this on your accltech site?

-- hugh

 

[mondejars]

Yes it is on that site, I didn't go for a Pro version because at the moment this is just a small project and everything is getting there for what I need, just got stuck on that calculation. I am pretty sure this project will keep growing and Fabrik is the perfect tool, so hopefully we will be in a position to go Pro in the future.

Thanks a lot, you are a legend

 

[cheesegrits]

Should be working now. Couple of typos in my code, plus I thought 'pause' was a time element as well.

I added the date formatting.

Also, your "additional AJAX elements" wasn't comma separated, so AJAX calc'ing wasn't working. Is now.

-- hugh

 

[mondejars]

Working like a charm, this is what I call outstanding support.
Thanks a lot