SiT! Bugs - SiT!
View Issue Details
0001825SiT!SLAspublic2012-12-20 22:442014-02-13 10:25
SLionB 
paulh 
immediatemajoralways
assignedopen 
LinuxLinux2.6+later
3.67 LTS 
 
0001825: calculate working time is buggy, does not support holidays
I has broken my head with this ugly function at the heart of SiT!
Based on it are SLAs, so if it is buggy its a huge problem.
put start_time, end_time and start creating incidents.
Clock is counting even if it is not working hours.
It also support holidays.
I hope it will help a lot of people.
Implement my replacement function;

function calculate_working_time($t1, $t2,$holidays)
{
    DEFINE ('ONEMINUTE', 60);

    // ESTABLISH THE MINUTES PER DAY FROM START AND END TIMES
    global $CONFIG;
    $start_ts = $CONFIG['start_working_day'];
    $end_ts = $CONFIG['end_working_day'];
    $working_days = $CONFIG['working_days'];

    // FUTURE UPDATE MUST HAVE READABLE DATES AND NOT SECONDS FROM MIDNIGHT
    //$start_time = '09:00';
    //$end_time = '17:00';
    //$start_ts = strtotime($start_time);
    //$end_ts = strtotime($end_time);

    $minutes_per_day = (int)( ($end_ts - $start_ts) / 60 );

    // CONVERT HOLIDAYS TO ISO DATES
    foreach ($holidays as $x => $holiday)
    {
        $holidays[$x] = date('Y-m-d', strtotime($holiday));
    }

    // CHECK FOR VALID DATES
    if (!$start = $t1) die('Invalid START Date');
    if (!$end = $t2) die('Invalid END Date');
    $start_p = date('Y-m-d H:i:s', $start);
    $end_p = date('Y-m-d H:i:s', $end);
 
    // MAKE AN ARRAY OF WORK MINUTES
    $workminutes = array();

    // ITERATE OVER THE DAYS
    $start = $start - ONEMINUTE;
    while ($start < $end)
    {
        $start = $start + ONEMINUTE;
        // ELIMINATE HOURS BEFORE BUSINESS HOURS
        $daytime = date('H:i', $start);
        if(($daytime < date('H:i',$start_ts))) continue;
        // ELIMINATE HOURS PAST BUSINESS HOURS
        $daytime = date('H:i', $start);
        if(($daytime > date('H:i',$end_ts))) continue;
        // ELIMINATE WEEKENDS - SAT AND SUN
        $weekday = date('w', $start);
        if (!in_array($weekday,$working_days))continue;
        // ELIMINATE HOLIDAYS
        $iso_date = date('Y-m-d', $start);
        if (in_array($iso_date, $holidays)) continue;
        $workminutes[] = $iso_date;
        // END ITERATOR
    }

    // HOW MANY WORK MINUTES?
    $number_of_workminutes = number_format(count($workminutes)-1);
    return $number_of_workminutes;
}
No tags attached.
has duplicate 0001920closed  Calculation of working time does not take holidays into account 
zip calculate_working_time.zip (1,951) 2013-02-17 19:41
http://bugs.sitracker.org/file_download.php?file_id=218&type=bug
Issue History
2012-12-20 22:44SLionBNew Issue
2013-02-03 15:30paulhNote Added: 0004673
2013-02-03 15:30paulhAssigned To => paulh
2013-02-03 15:30paulhStatusnew => assigned
2013-02-07 11:13SLionBNote Added: 0004695
2013-02-17 19:41SLionBFile Added: calculate_working_time.zip
2013-02-17 19:46SLionBNote Added: 0004708
2014-02-13 10:25ivanRelationship addedhas duplicate 0001920

Notes
(0004673)
paulh   
2013-02-03 15:30   
Thanks for the report and patch, unfortunately this is a long standing issue we've had I've tried to fix it before (see the commented out code in the function) unfortunately the counting approach is somewhat slow and on larger systems where calls last several months and there are several hundred open it takes long time to render incidents.php, I'll look at your code and look to fix this in an imminent release - I'm currently thinking we may need to cache this on the updates table per record to improve performance.
(0004695)
SLionB   
2013-02-07 11:13   
I had tried both the original function as the commented out one and is not accurate.
Instead this one is accurate.

at the end I forgot the number_format function used for debugging.

// HOW MANY WORK MINUTES?
$number_of_workminutes =count($workminutes)-1;
return $number_of_workminutes;

I dont have performance issues at the moment as I dont have so many open incidents for very long period of times.
Caching on the updates table will not function as this one which counts every active minute.
(0004708)
SLionB   
2013-02-17 19:46   
Inspired by the Microsoft's Excel networkdays function, I created a calculate_working_time that is based on a php networkdays function.
It calculates automatically the minutes of network days in between two dates and then adds the working minutes for the first and last day.
As there are no loops anymore, this is the best performance you could achieve even with many incidents open, with durations for months and years.