# How to get calculations on total time worked

Okay guys here is a tough one. I am just about through with the development of a job tracking system. The processing page that I have created is working great. It calculates reguler time, overtime and double time. It even can determin wether the date an employee worked is a weekend or a holiday and calculate the appropriate pay scale. The script also will charge a minimum of 4 hours if the work performed is less than 4 hours and the day is a holiday or weekend.

Now here is my dilema. Lets say that the following senario happens. An employee begins their day by logging into the job tracking system and starts two differnt job tickets for two different job sites that he will be working at for that day. After he enters these two different job tickets, he begins to add start time and stop time as well as job descriptions to each job ticket. Each job ticket may have one or more associated records with start times and stop times and descriptions. Now lets say the  employee works for 5 hours at one job site and then works another 5 hours on another job site on the same day. Thats a total of 10 hours (8 hours regular time and 2 hours overtime).

Now keep in mind that the way the job tickets are entered into the system CAN NOT be changed because these functions have been requested by the company for whom the program is being developed for.

The code that I have written processes the time by job and not by day for each employee so the 10 hours worked is actually calculated 5 hours on one job and 5 hours on another. As you can see the time is all billed at regular time and not  8 hours regular time and 2 hours overtime like it should.

Below is the code I have so far. I apologize for its length, but it is heavily commented

//*****************************************************
//First we retreive all of the records from the database that have been approved
//This is my sql statement and it probably has been set up wrong. I beleive that this
//is were the problem in my code exists
//*****************************************************

\$sqltime="SELECT SUM(track_job_lineitem_detail.total_time) AS time, SUM(track_job_lineitem_detail.mileage) AS mileage, track_job_lineitems.ID AS id, track_job_lineitems.costcenter As cc, track_job_lineitems.employee_number AS en, track_job_lineitem_detail.date_entered, track_job_lineitems.date_started, track_job_lineitems.date_completed, track_job_lineitems.job_id, track_job_lineitems.cc_masterplan AS mp, track_job_lineitems.cc_code AS code, track_job_lineitems.vantive_number AS vn, track_job_lineitems.approval_by AS ab, track_job_lineitem_detail.description AS d  FROM track_job_lineitem_detail INNER JOIN track_job_lineitems ON track_job_lineitems.job_id=track_job_lineitem_detail.job_id  WHERE track_job_lineitems.approval_code=1 GROUP BY job_id, date_entered ORDER BY job_id, date_entered";

\$resulttime=mysql_query(\$sqltime);
for(\$x = 0; \$row=mysql_fetch_assoc(\$resulttime); \$x ++){

//************************************************
//All of the code below should not be messed with. The code below is
//working fine and doing all of the pay calcuations correctly
//************************************************

//Now we set up all of the variables for calculations of Regular Time
\$varid=\$row['id'];
\$varbilleddate=date('YmdHis');
\$varjobid=\$row['job_id'];
\$varen=\$row['en'];
\$varmp=\$row['mp'];
\$varcode=\$row['code'];
\$varcc=\$row['cc'];
\$varvn=\$row['vn'];
\$varab=\$row['ab'];
\$vard=\$row['d'];
\$workdate=\$row['date_entered'];

//Set up Variables for Cost Center to Display the name of the Cost Center
\$sqlf="SELECT cc_name FROM track_costcenter WHERE cc_id='\$varcc'";
\$resultf=mysql_query(\$sqlf);
\$rowf=mysql_fetch_assoc(\$resultf);
\$varccname=\$rowf['cc_name'];

//Update the job ticket to reflect that the job has been batched
\$sqla="UPDATE track_job_lineitems SET job_open='6' WHERE ID='\$varid'";
\$resulta=mysql_query(\$sqla);

//Update the database to reflect that the invoice has been generated
\$sqlb="UPDATE track_job_lineitems SET billed='1' WHERE ID='\$varid'";
\$resultb=mysql_query(\$sqlb);

//Update the database to show the time that the invoice was generated
\$sqlc="UPDATE track_job_lineitems SET date_billed='\$varbilleddate'";
\$resultc=mysql_query(\$sqlc);

//Calculate the total mileage
\$mile=\$row['mileage'];
\$milepay=(\$row['mileage'] * .75);
\$dateentered=\$row['date_entered'];

//Retreive all dates from the database that are weekends
\$sqld="SELECT * FROM track_weekend_dates WHERE weekend_date='\$dateentered'";
\$resultd=mysql_query(\$sqld);
\$weekendcount=mysql_num_rows(\$resultd);

//Retrieve all date from the database that are holidays
\$sqle="SELECT * FROM track_holiday_dates WHERE holiday_date='\$dateentered'";
\$resulte=mysql_query(\$sqle);
\$holidaycount=mysql_num_rows(\$resulte);

//Now we compare the date the time was worked to see if it is a
//holiday or a weekend, so we can charge the appropriate pay scale
if(\$weekendcount == 0 && \$holidaycount == 0){
\$regbill=51.00;
\$overbill=76.50;
\$doublebill=102.00;
}elseif(\$weekendcount == 1 && \$holidaycount == 0){
\$regbill=76.50;
\$overbill=114.75;
\$doublebill=153.00;
}elseif(\$weekendcount == 0 && \$holidaycount == 1){
\$regbill=102.00;
\$overbill=153.00;
\$doublebill=204.00;
}elseif(\$weekendcount == 1 && \$holidaycount == 1){
\$regbill=102.00;
\$overbill=153.00;
\$doublebill=204.00;
}

//If the date worked is a weekend or holiday
// we must make sure that a 4 hour minimum
// is  charged
\$reg=\$row['time'];
if(\$row['time'] <=14400 && \$weekendcount == 1 || \$holidaycount == 1){
\$reg=14400;
\$regpay=(\$row['time'] / 3600 * \$regbill);
\$regpayformat=number_format(\$regpay, 2, '.', '');
\$mile=\$row['mileage'];
\$milepay=(\$row['mileage'] * .75);
\$amount=(\$regpay + \$milepay);
\$sqlminimum="INSERT INTO track_invoices (invoice_num, invoice_date, job_id, workdate, employee_number, cc_masterplan, cc_code, costcenter, vantive_number, approved_by, description, reg_time, reg_pay, mileage, mileage_pay, total_amount) VALUES ('\$var', \$varbilleddate, '\$varjobid', '\$workdate', '\$varen', '\$varmp', '\$varcode', '\$varccname', '\$varvn', '\$varab', '\$vard', '\$reg', '\$regpay', '\$mile', '\$milepay', '\$amount')";
\$resultminimum=mysql_query(\$sqlminimum);
}elseif(\$weekendcount == 0 && holidaycount ==0){
//End setup 4 hour minimum time for Weekends and Holidays

//Calculate Regulare Time
\$reg=\$row['time'];
if(\$row['time'] <= 28800){
\$reg=\$row['time'];
\$regpay=(\$row['time'] / 3600 * \$regbill);
\$regpayformat=number_format(\$regpay, 2, '.', '');
\$mile=\$row['mileage'];
\$milepay=(\$row['mileage'] * .75);
\$amount=(\$regpay + \$milepay);
\$sql1="INSERT INTO track_invoices (invoice_num, invoice_date, job_id, workdate, employee_number, cc_masterplan, cc_code, costcenter, vantive_number, approved_by, description, reg_time, reg_pay, mileage, mileage_pay, total_amount) VALUES ('\$var', \$varbilleddate, '\$varjobid', '\$workdate', '\$varen', '\$varmp', '\$varcode', '\$varccname', '\$varvn', '\$varab', '\$vard', '\$reg', '\$regpay', '\$mile', '\$milepay', '\$amount')";
\$result1=mysql_query(\$sql1);
}

//Set up the variables and calulations for Regular Time and Overtime
}elseif((\$row['time'] > 28800) && (\$row['time'] <= 43200)){
\$reg=28800;
\$over=(\$row['time'] - 28800);
\$regpay=(\$reg / 3600 * \$regbill);
\$regpayformat=number_format(\$regpay, 2, '.', '');
\$overpay=(\$over / 3600 * \$overbill);
\$overpayformat=number_format(\$overpay, 2, '.', '');
\$mile=\$row['mileage'];
\$milepay=(\$row['mileage'] * .75);
\$amount=(\$regpay + \$overpay + \$milepay);
\$sql2="INSERT INTO track_invoices (invoice_num, invoice_date, job_id, workdate, employee_number, cc_masterplan, cc_code, costcenter, vantive_number, approved_by, description, reg_time, reg_pay, over_time, over_pay, mileage, mileage_pay, total_amount) VALUES ('\$var', \$varbilleddate, '\$varjobid', '\$workdate', '\$varen', '\$varmp', '\$varcode', '\$varccname', '\$varvn', '\$varab', '\$vard', '\$reg', '\$regpay', '\$over', '\$overpay', '\$mile', '\$milepay', '\$amount')";
\$result2=mysql_query(\$sql2);

//Set up the variables and calulations for Regular Time, Overtime, and Double Time
}elseif(\$row['time'] > 43200){
\$reg=28800;
\$over=14400;
\$double=(\$row['time'] - 43200);
\$regpay=(\$reg / 3600 * \$regbill);
\$regpayformat=number_format(\$regpay, 2, '.', '');
\$overpay=(\$over / 3600 * \$overbill);
\$overpayformat=number_format(\$overpay, 2, '.', '');
\$doublepay=(\$double / 3600 * \$doublebill);
\$doublepayformat=number_format(\$doublepay, 2, '.', '');
\$mile=\$row['mileage'];
\$milepay=(\$row['mileage'] * .75);
\$amount=(\$regpay + \$overpay + \$doublepay + \$milepay);
\$sql2="INSERT INTO track_invoices (invoice_num, invoice_date, job_id, workdate, employee_number, cc_masterplan, cc_code, costcenter, vantive_number, approved_by, description, reg_time, reg_pay, over_time, over_pay, double_time, double_pay, mileage, mileage_pay, total_amount) VALUES ('\$var', \$varbilleddate, '\$varjobid', '\$workdate', '\$varen', '\$varmp', '\$varcode', '\$varccname', '\$varvn', '\$varab', '\$vard', '\$reg', '\$regpay', '\$over', '\$overpay', '\$double', '\$doublepay', '\$mile', '\$milepay', '\$amount')";
\$result2=mysql_query(\$sql2);

}
}

Like I said above the code works fine but it looks at the total time by job and not by day
###### Who is Participating?

Commented:
If I understand your example correctly, you need to insure that the proper amount of overtime is charged to the proper client, right? (I had to deal with this in a similar system.)

In your example, you state that the Employee Z worked 5 hours for Client A, and 5 hours for Client B. What you need to do is check the start and stop time for each client. Let's say the 2 records look like this:

Client | Time In | Time Out | Employee
A | 8:00 AM | 1:00 PM | Z
B | 2:00 PM | 7:00 PM | Z

Assuming "normal pay" time is 8 AM to 5 PM, this would mean that 5 PM to 7 PM work done for Client B is overtime. A simple function may be the solution for you:

function check4overtime( \$timeIn, \$timeOut, \$date ) {

// zero value variable for normal hours
\$normalHours = 0;

// zero value variable for overtime hours
\$overtimeHours = 0;

// get the UNIX timestamp for 8 AM on the work day
\$eightAM = strtotime( \$date . " 8:00 AM" );

// get the UNIX timestamp for 5 PM on the work day
\$fivePM = strtotime( \$date . " 5:00 PM" );

// get UNIX timestamp for timeIn value
\$timeIn = strtotime( \$date . " " . \$timeIn );

// get UNIX timestamp for timeOut value
\$timeOut = strtotime( \$date . " " . \$timeOut );

// get total number of seconds between timeIn and timeOut
\$totalHours = intval( \$timeOut - \$timeIn );

// check to see if timeIn is before 8 AM
if( \$timeIn < \$eightAM ) {
// get number of seconds between timeIn and eightAM
\$overtimeHours = intval( \$eightAM - \$timeIn );
}

// check to see if timeOut is after 5 PM
if( \$timeOut > \$fivePM ) {
// get number of seconds between timeOut and fivePM
\$overtimeHours += intval( \$timeOut - \$fivePM );
}

// subtract overtimeHours from totalTime to get normalHours
\$normalHours = intval( \$totalHours - \$overtimeHours );

// convert normalHours value from seconds to hours
\$normalHours = round( \$normalHours / 3600, 2 );

// convert overtimeHours value from seconds to hours
\$overtimeHours = round( \$overtimeHours / 3600, 2 );

// convert totalHours value from seconds to hours
\$totalHours = round( \$totalHours / 3600, 2 );

print( "Total Hours: " . \$totalHours . "<br />" );
print( "Normal Hours: " . \$normalHours . "<br />" );
print( "Overtime Hours: " . \$overtimeHours . "<br />" );

return( 0 );
}

Here's an example of how to call the function:

check4overtime( "2:00 PM", "7:00 PM", "2004-09-28" );

Which outputs:

Total Hours: 5
Normal Hours: 3
Overtime Hours: 2
0

ConsultantCommented:
I'm not sure if I understand 100%, but from what I do understand:

You're doing pay calculation on every MySQL row that is being retrieved (i.e. every job/ticket record). Maybe instead of doing it within the record loop, just tally up the time, and once the loop is finished, THEN do the pay calculation?

- J
0

Author Commented:
I can not do the pay calculations outside of the loop because the invoice being generated needs to contain all of the job information for each employee. there are over 40 employees that need to be tracked. The company who commisioned the programs development has stated that tey want to see onyl one invoice for each day and that invoice must contain the job tracking for each employee.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.