php - MYSQL - Timeclock Count Hours In/Out for each day of current week

582

Below is my table for my databasetimeclock that records each record of an employee clocking in/out. I am wanting to create a function that returns hours for each day Monday-Sunday of current week.

id  user    timeinout           type    function    
1   1   2015-08-05 20:20:32     in         1
2   1   2015-08-05 20:20:56     out        1
3   1   2015-08-05 20:29:25     in         1
4   1   2015-08-05 20:32:04     out        2
5   1   2015-08-05 20:36:28     in         1
6   1   2015-08-05 20:40:00     out        1
7   1   2015-08-06 09:05:39     in         1

I know this function is way wrong, but here is where I am. I have never done this before so I am at a real loss and all examples I have tried have failed.

function gettimeclockHours($user) {
  include $_SESSION['db'];
  try {
      $sql = "SELECT HOUR(timeinout) as hour, COUNT(*) as num_rows FROM timeclock WHERE user = :user GROUP BY HOUR(timeinout)";
      $s = $pdo->prepare($sql);
      $s->bindValue(':user', $user);
      $s->execute();
  }
  catch (PDOException $e)
  {
      $error = 'Error.' . $e->getMessage();
      moduleError($error);
      exit();
  }
  $result = $s->fetch();
  return $result;
}

The Output example I am looking for is below.

Day of Week     Total Time
Monday          1.2 Hours
Tuesday     
Wednesday   
Thursday    
Friday  
Saturday    
Sunday  

Thanks for your help in advance.

321

Answer

Solution:

split dates by weekday (Mon, Tue etc.) actually simple question. You can use DAYNAME() function.

More interesting task is to calculate time when user inside. It could be sum(outs_time)-sum(ins_time), but in case, when user come at 23:45 and leave at 00:15 next day it won't work. in this case we need to add additional out at 24:00:00

I would go for following solution: 1. transform time to seconds from beginning of the day (TIME_TO_SEC() 2. check what the last action is "in" and make a flag in subquery

select if(max(if(type = 'in', timeinout, 0)) > max(if(type = 'out', timeinout, 0)), 1,0) // 1 - last type is in, 0 - last type is out
from timeclock t1 
  1. final query with adding 24 hours if last type = "in"

    select DAYNAME(timeinout) as weekday_name,
    (
      sum(if(type = "out",TIME_TO_SEC(timeinout), 0)) - 
      sum(if(type = "in",TIME_TO_SEC(timeinout), 0)) + 24*60*60*
      (select if(max(if(type = 'in', timeinout, 0)) > max(if(type = 'out', timeinout, 0)), 1,0) // 1 - last type is in, 0 - last type is out
        from timeclock t1 
        where t1.user = t2.user and date(t1.timeinout) = date(t2.timeinout))
    )/60/60 hours
    FROM timeclock t2
    GROUP BY user, date(timeinout)
    

People are also looking for solutions to the problem: php - how to display unicode characters using jquery ajax?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.