asked on

php-mysql calculating avg time only for m-f 9-5

Hello all,

I need a bit of help. I am working on a project that is keeping track of the time spent on tasks in my office. I am using php and mysql. I am able to calculate the total time difference and display it correctly. The issue is that we only work 9-5 M-F so if a task starts at 9am Monday and ends 5pm Tuesday then the app will say 48 hours instead of the 10 hours they actually worked.

$tasktime = end time - star time for each task added togeather.
$tasktikrow = the total number of tasks in the db.

$taskavg = intval(($tasktime / $tasktikrow), 0);
 if($taskavg % 86400 <= 0) //there are 86,400 seconds in a day
 {$taskavgd = $taskavg / 86400;}

 if($taskavg % 86400 > 0)
 {
 $rest = ($taskavg % 86400);
 $taskavgd = ($taskavg - $rest) / 86400;

 if( $rest % 3600 > 0 )
 {
 $rest1 = ($rest % 3600);
 $taskavgh = ($rest - $rest1) / 3600;

 if( $rest1 % 60 > 0 )
 {
 $rest2 = ($rest1 % 60);
 $taskavgm = ($rest1 - $rest2) / 60;
 $taskavgs = $rest2;
 }
 else
 $taskavgm = $rest1 / 60;
 }
 else
 $taskavgh = $rest / 3600;}

 $taskavgd *= 24;
 $taskavgh += $taskavgd;

so this is getting my total hours, min and sec as an average for the times spent but the numbers are through the roof. if I can only calculate the 8hrs a day and the 5 days a week my numbers will be completely accurate. Thanks for any help

debugitbob

ASKER

This is just a normal sql query.

paraphrased

$t = SELECT endtime, startime FROM tasks WHERE task=done

while(rows)
{
$tasktime += $tt["end"] - $t["start"];
}

ASKER CERTIFIED SOLUTION

chingmd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

debugitbob

ASKER

That helps but I still need to factor in the weekends. subtracting 57600 for each day that is counted will help for Monday through Friday but if I have a task that starts on Thursday or Friday and does not end until Monday or Tuesday I still cant figure that out.

Ex1. Task starts on 10/1/07 at 7:26 am and ends on 10/3/07 at 15:48 pm (3:48). so 9 to 5 on monday was 8hrs, 9-5 on tues was 9 hrs 26min 9 to 3:48 on wed was 6hr 48min. so thats 24hr and 22 min of work. That is working now.

so here is the new code. I made the input number static to test as a control. I also made each action print so if you want to copy and paste it will run.

202920 is the difference between the start time and end time (1191426480 - 1191223560)

$taskavg = 202920; //intval(($tasktime / $tasktikrow), 0);
 echo "taskavg = ".$taskavg;

 if($taskavg % 86400 <= 0) //there are 86,400 seconds in a day
 {
 $taskavgd = $taskavg / 86400;
 echo "taskavgd <= 0: ".$taskavgd." ";
 }

 if($taskavg % 86400 > 0)
 {
 $restT = ($taskavg % 86400);
 $taskavgd = ($taskavg - $restT) / 86400;

 echo " if(taskavg % 86400 > 0) { ";

 echo "rest = (taskavg % 86400) | rest = (".$taskavg." % 86400) | rest = ".$restT." ";
 echo "taskavgd = (taskavg - rest) | taskavgd = (".$taskavg." - ".$restT.") / 86400 | taskavgd = ".$taskavgd." ";

 $rest = $taskavg - ($taskavgd * 57600);


 echo " if( $rest % 3600 > 0){";
 if( $rest % 3600 > 0 )
 {
 $rest1 = ($rest % 3600);
 $taskavgh = ($rest - $rest1) / 3600;

 echo "rest1 = (rest % 3600) | rest1 = (".$rest." % 3600) | rest1 =".$rest1." ";
 echo "taskavgh = (rest - rest1) | taskavgh (".$rest." - ".$rest1.") | taskavgh = ".$taskavgh." ";

 echo " if( $rest1 % 60 > 0 ){ ";
 if( $rest1 % 60 > 0 )
 {
 $rest2 = ($rest1 % 60);
 $taskavgm = ($rest1 - $rest2) / 60;
 $taskavgs = $rest2;

 echo "rest2 = (rest1 % 60) | rest2 = (".$rest1." % 60) | rest2 = ".$rest2." ";
 echo "taskavgm = (rest1 - rest2) / 60 | taskavgm (".$rest1." - ".rest2.") /60 | ".$taskavgm." ";
 echo "taskavgs = rest2 | taskavgs = ".$rest2." ";
 }
 else
 {
 echo "} else{";
 $taskavgm = $rest1 / 60;
 echo "taskavgm = rest1 / 60 | taskavgm = (".$rest1." / 60) | taskavgm = ".$taskavgm." } }";
 }
 }
 else
 {
 echo "} else{";
 $taskavgh = $rest / 3600;
 echo "taskavgh = rest / 3600 | taskavgh = (".$rest." / 3600) | taskavgh = ".$taskavgh." } }";
 }
 }

 $taskavgd *= 24;
 $taskavgh += $taskavgd;

debugitbob

ASKER

so basically I just need to check the start time and end time and if a sat and Sunday fall between the dates the subtract 172800 from the total # of sec. then run the rest of the code. and that should do it. chingmd, I'll make sure you get credit for helping I'm just seeing if any other answers come in.

debugitbob

ASKER

I found some code that worked plus what you gave me fixed the issue

this segment was meant to count the number of business days, so I added a piece that counts weekends when it's not a business day and Tada, it worked.

 $tasktikrow +=1;
 $tasktime += ($min["edit_date"] - $min["create_date"]);

 $start_date = strtotime($min["create_date"]);
 $end_date = strtotime($min["edit_date"]);

 $business_days = 0;
 $weekend = 0;

 if ( $start_date < $end_date )
 {
 $date1 = $start_date;
 $date2 = $end_date;
 }
 else
 {
 $date1 = $end_date;
 $date2 = $start_date;
 }

 while ($date1 <= $date2)
 {
 $thedate = getdate( $date1 );

 // Skip Saturday and Sunday.
 if (($thedate["wday"] != '0') and ($thedate["wday"] != '6'))
 {
 $business_days++;
 }
 else
 $weekend++;

 $date1 += 86400; // Add 1 day.
 }
 $wend = ($weekend * 86400);

 $tasktime = $tasktime - $wend;