Compute Attendance Hours

[newphpcoder]

I upload attendance .xml using php and it saves to database. I tried this data to upload:100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late. It should be only 8 hours.100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout there’s no exemption because he late on his work. So it should has deduction or minus in his total hours. and it saves it database:100603 2011-10-01 05:35:00 2011-10-01 13:35:00100603 2011-10-02 05:25:00 2011-10-01 13:55:00100603 2011-10-02 05:40:00 2011-10-01 13:40:00 I want to accomplish is to get the total hours of the employee based on the employee no. And even the employee get timein early before his time or late to timeout the hours computed only 8 hours. Honestly, I don’t have idea how can be possible it is. and I tried this code for computing the hours per day:

select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;

and the result of this code is: totalhours:08:00:0008:30:0008:00:00 and the result is the first is correct because the real schedule is 5:35 AM - 1:35 PMthe second is wrong it should be 8 hours only even he timein early and timeout late.the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee. I hope someone can help me. Thank youI upload attendance .xml using php and it saves to database. I tried this data to upload:100603 10/1/11 5:35 AM 10/1/11 1:35 PM // this is the exact schedule of employee and its 8 hours per day he time in exactly and timeout exactly also, so no problem in computing because it is 8 hours.100603 10/2/11 5:25 AM 10/2/11 1:55 PM //in this example data the employee time in early in his schedule and time out late. It should be only 8 hours.100603 10/3/11 5:40 AM 10/3/11 1:40 PM // in this example data the employee time in is late, so even he also late to timeout there’s no exemption because he late on his work. So it should has deduction or minus in his total hours. and it saves it database:100603 2011-10-01 05:35:00 2011-10-01 13:35:00100603 2011-10-02 05:25:00 2011-10-01 13:55:00100603 2011-10-02 05:40:00 2011-10-01 13:40:00 I want to accomplish is to get the total hours of the employee based on the employee no. And even the employee get timein early before his time or late to timeout the hours computed only 8 hours. Honestly, I don’t have idea how can be possible it is. and I tried this code for computing the hours per day:

select  sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein)) AS totalhours from employee;

and the result of this code is: totalhours:08:00:0008:30:0008:00:00 and the result is the first is correct because the real schedule is 5:35 AM - 1:35 PMthe second is wrong it should be 8 hours only even he timein early and timeout late.the third is also wrong because the employee is late to timein, even he also timeout late., it should be deduct or subtract in hours the late of employee. I hope someone can help me. Thank you

[justsomeguy]

You either need to write a stored procedure if you want to do everything in SQL, or get all of the data and do the logic in PHP. A basic query is going to get pretty complex if you're trying to do everything there. Get all of the records in PHP and then do your checks and comparisons to figure out how many hours, when they started, etc. It may be easiest if you add the time in to your query and then you can check the time in and subtract time from the elapsed hours based on when they clocked in.