Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to get calculations on total time worked

Posted on 2004-09-24
5
203 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:stanforrest
5 Comments
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 12146812
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 Comment

by:stanforrest
ID: 12146856
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
 
LVL 1

Accepted Solution

by:
KodeKrash earned 500 total points
ID: 12167691
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
            // and add it overtimeHours
            $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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question