Need help in starting to structure code for a specific problem

[walermo]

I have put together a simple billing system for my contractors. They are assigned specific jobs at a specific site. Each job is unique with respect to my clients, but my contractors get paid an hourly rate with a minimum of 2 hours per site visit.

 

The problem I am facing is trying to come up with a way to calculate their per site visit total times, when each visit may include several jobs, in order to calculate their total hours.

 

Here is a sample of what I am trying to accomplish:

 

On a specific site visit, if a contractor works more than one job and the idle time between jobs <= 30 minutes, all jobs make up a single block of job time. If the idle time between jobs > 30 minutes, then the job(s) on either side of that idle time become independent blocks of job time.

#  Job#       Start     End       diff  thisjobtime  thisblocktime + difference + thisjobtime = thisblocktime
1  130516-03  10:00 am  11:00 am  0     1.00         0             + 0          + 1.00        = 1.00
2  130516-12  11:00 am  12:30 pm  0     1.50         1.00          + 0          + 1.50        = 2.50
3  130516-08  12:30 pm   2:00 pm  0     1.50         2.50          + 0          + 1.50        = 4.00
4  130516-10   2:00 pm   3:30 pm  0     1.50         4.00          + 0          + 1.50        = 5.50
5  130516-11   3:30 pm   5:30 pm  0     2.00         5.50          + 0          + 2.00        = 7.50

6  130517-01   7:30 am   8:00 am  0     0.50         0             + 0          + 0.50        = 0.50
7  130517-02   8:00 am  10:00 am  0     2.00         0.50          + 0          + 2.00        = 2.50

8  130517-09  12:30 pm   1:30 pm  > 30  1.00         0             + 0          + 1.00        = 1.00
9  130517-11   1:30 pm   3:30 pm  0     2.00         1.00          + 0          + 2.00        = 3.00

10 130520-02   7:30 am   8:30 am  0     1.00         0             + 0          + 1.00        = 1.00
11 130520-04   9:00 am  10:00 am  30    1.00         1.00          + 0.50       + 1.00        = 2.50
12 130520-07  10:00 am  11:00 am  0     1.00         2.50          + 0          + 1.00        = 3.50
13 130520-11  11:15 am   1:15 pm  15    2.00         3.50          + 0.25       + 2.00        = 5.75

14 130520-13   2:00 pm   3:15 pm  > 30  2.00         0             + 0          + 1.25        = 2.00

So my contractors record their time entries on my system. By default, when I query their job entry data, I do the following on a per row basis:

$rql = "SELECT
            jobs.*,
            contractors.rate AS contractor_rate,
            TIMEDIFF(jobs.job_end,jobs.job_start) AS job_time,
        FROM
            jobs
        LEFT JOIN
            contractors
        ON 
            jobs.contractor_id = contractors.id";

$report = $mysqli->query($rql);

    while($row = $report->fetch_assoc()) {
        $int_rate       = $row['contractor_rate'];
        $job_time       = decimalHours($row['job_time']);
        $job_bill_time  = decimalHours(2);
                            if($job_time > 2){ $job_bill_time = $job_time; }
        $job_pay        = $job_bill_time * $int_rate;

What this does is simply show that if the contractor worked less than two hours for a given job, he gest paid the 2-hour minimum.

 

But this does not help me calculate time worked during specific blocks of time. My attempts at writing code to calculate this when displaying the table got me nowhere, if only because it would be inefficient to perform this calculation on every query.

 

Next, I thought it would be perhaps simpler and certainly more efficient if the calculation is made when the contractors register their time. But that presented another problem: Every time the contractor records their time for a given job, I would have to look for previous job entries, calculate the difference between the previous job entry end time and the new job entry start time, and then record the time difference in a specific field (i.e.: job_time) if the new job entry is part of an existing time block or, because the difference between the previous job time is > 30 minutes, as a new time block (with the corresponding 2-hour minimum). This would work nicely if contractors recorded their time entries in chronological order (and would certainly be feasible with my current coding skills), but this is not always the case. So for every job entry, I would have to look for (and potentially modify) previous job entries to make sure that consecutive job times are assigned to a single time block (also stumped as to how exactly I can accomplish this).

 

This is my logic: Get all of a contractor's jobs for a given day. Compare the start times of a current job with the end time of a previous job (chronological, not job #) and get the difference. If the difference is <= 30 minutes, then the job block time = the sum of each of the job times and the difference. Record each the job times in a given time block as real time instead of the 2-hour minimum. If the difference > 30 minutes, the current job is in a time block different than the previous job's time block. Rinse and repeat. 

 

So this is where I am stuck. Any help pointing me in the right direction would be very much appreciated!

 

Thanks!

[Jessica]

That was a huge wall of text in which you didn't describe an actual problem. At the end you stated your logic. Then said you're stuck. On what?

[walermo]

Thanks Jessica! I'm stuck on turning my logic into code. That's it. Not  looking for someone to actually code it for me. Just looking for help in figuring out how to structure my logic into code. Thanks again!

[ignace]

The below code should set you off in the right direction

 

<?php

$sql = '
     SELECT jobs.id, jobs.trackno, jobs.job_start, jobs.job_end, contractors.rate
       FROM jobs
  LEFT JOIN contractors ON contractors.id = jobs.contractor_id
   ORDER BY jobs.id ASC
';

$stmt = $mysqli->query($sql);

$prev = null;

$diff = 0;
$totalWorkedHours = 0;

while ($job = $stmt->fetch_object('Job')) {
    if ($prev !== null && ($diff = $job->getJobStartDate()->diff($prev->getJobEndDate())->format('i')) > 30) {
        // process!
        // ...
        
        // reset!
        $totalWorkedHours = 0;
    }
    
    echo /* #               */ $job->getId(),
         /* Job#            */ $job->getTrackNo(),
         /* Start           */ $job->getJobStartDate()->format('H:i a'),
         /* End             */ $job->getJobEndDate()->format('H:i a'),
         /* diff            */ $diff,
         /* thisjobtime     */ ($workedHours = $job->getWorkedHours()),
         /* thisblocktime   */ $totalWorkedHours,
         /* + difference    */ ($diff / 60),
         /* + thisjobtime   */ $workedHours,
         /* = thisblocktime */ $totalWorkedHours + ($diff / 60) + $workedHours;
    
    $totalWorkedHours += $workedHours;
    $prev = $job;
}

class Job {
    const DATETIME = 'Y-m-d H:i:s';
    
    private $id;
    private $trackno;
    private $job_start;
    private $job_end;
    private $rate;
    
    public function __set($key, $val) {
        switch ($key) {
            case 'id':
                $this->id = $val;
                break;
            case 'trackno':
                $this->setTrackNo($val);
                break;
            case 'job_start':
                $this->setJobStartDate($val, self::DATETIME);
                break;
            case 'job_end':
                $this->setJobEndDate($val, self::DATETIME);
                break;
            case 'rate':
                $this->setJobRate($val);
                break;
            default:
                throw new DomainException;
        }
    }
      
    public function getId() {
        return $this->id;
    }
    
    public function setTrackNo($trackNo) {
        $this->trackno = $trackno;
        return $this;
    }
    
    public function getTrackNo() {
        return $this->trackno;
    }
    
    public function setJobStartDate($date, $format = null) {
        $this->job_start = ($date instanceof DateTime) ? $date : DateTime::createFromFormat($format, $date);
        return $this;
    }
    
    public function getJobStartDate() {
        return $this->job_start;
    }
    
    public function setJobEndDate($date, $format = null) {
        $this->job_end = ($date instanceof DateTime) ? $date : DateTime::createFromFormat($format, $date);
        return $this;
    }
    
    public function getJobEndDate() {
        return $this->job_end;
    }
    
    public function setJobRate($rate) {
        $this->rate = $rate;
        return $this;
    }
    
    public function getWorkedHours() {
        return $this->getJobEndDate()->diff($this->getJobStartDate())->format('s') / 3600;
    }
}

Edited June 2, 2013 by ignace

[walermo]

Thank you very much, Ignace! I will explore it and see how I can make it work with what I have now. I will certainly let you know how it goes.