[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2007-10-08
5
Medium Priority
?
281 Views
Last Modified: 2013-12-13
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
0
Comment
Question by:debugitbob
  • 4
5 Comments
 
LVL 3

Author Comment

by:debugitbob
ID: 20035058
This is just a normal sql query.

 paraphrased

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

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

Accepted Solution

by:
chingmd earned 2000 total points
ID: 20035607
You are not taking into consideration the hours that you are closed.

Days looks right.

for hours:  
   take end time - start time
   subtract 57600 * taskavgd     \\57600 is the amount of time you are closed   5pm to 9am
 
  that will give you the actualy number of hours within m-f.  
  You will also need to take into consideration weekends.   But think you get the idea.



0
 
LVL 3

Author Comment

by:debugitbob
ID: 20036309
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."<br>";
            }

             if($taskavg % 86400 > 0)
               {
                    $restT = ($taskavg % 86400);
                  $taskavgd = ($taskavg - $restT) / 86400;
                  
                  echo "<br><br>if(taskavg % 86400 > 0) { <br>";
                  
                  echo "rest = (taskavg % 86400) | rest = (".$taskavg." % 86400) | rest = ".$restT."<br>";
                  echo "taskavgd = (taskavg - rest) | taskavgd = (".$taskavg." - ".$restT.") / 86400 | taskavgd = ".$taskavgd."<br>";
                  
                  $rest = $taskavg - ($taskavgd * 57600);
                  
                  
                  echo "<br><br>if( $rest % 3600 > 0){";                   
                  if( $rest % 3600 > 0 )
                {
                           $rest1 = ($rest % 3600);
                       $taskavgh = ($rest - $rest1) / 3600;
                        
                        echo "rest1 = (rest % 3600) | rest1 = (".$rest." % 3600) | rest1 =".$rest1."<br>";
                        echo "taskavgh = (rest - rest1) | taskavgh (".$rest." - ".$rest1.") | taskavgh = ".$taskavgh."<br>";
                       
                        echo "<br>if( $rest1 % 60 > 0 ){<br>";
                        if( $rest1 % 60 > 0 )
                     {
                              $rest2 = ($rest1 % 60);
                        $taskavgm = ($rest1 - $rest2) / 60;
                        $taskavgs = $rest2;
                              
                              echo "rest2 = (rest1 % 60) | rest2 = (".$rest1." % 60) | rest2 = ".$rest2."<br>";
                              echo "taskavgm = (rest1 - rest2) / 60 | taskavgm (".$rest1." - ".rest2.") /60 | ".$taskavgm."<br>";
                              echo "taskavgs = rest2 | taskavgs = ".$rest2."<br>";
                     }
                        else
                        {
                           echo "}<br>else{";
                              $taskavgm = $rest1 / 60;
                              echo "taskavgm = rest1 / 60 | taskavgm = (".$rest1." / 60) | taskavgm = ".$taskavgm."<br>}<br>}";
                        }
              }
                  else
                  {
                        echo "}<br>else{";
                       $taskavgh = $rest / 3600;
                        echo "taskavgh = rest / 3600 | taskavgh = (".$rest." / 3600) | taskavgh = ".$taskavgh."<br>}<br>}";
                  }
            }
                        
                  $taskavgd *= 24;
                  $taskavgh += $taskavgd;
0
 
LVL 3

Author Comment

by:debugitbob
ID: 20036446
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.
0
 
LVL 3

Author Comment

by:debugitbob
ID: 20036941
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;

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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 and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses
Course of the Month19 days, 21 hours left to enroll

872 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