Computing attendance hours

[newphpcoder]

:confused::(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:00
100603 2011-10-02 05:25:00 2011-10-01 13:55:00
100603 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:00
08:30:00
08:00:00



and the result is

the first is correct because the real schedule is 5:35 AM - 1:35 PM
the 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

[traq]

you're going to have to compare each timein/out to the employee's schedule, and deduct those hours that are outside the scheduled periods. This would probably be easier if you simply retrieve the timestamps first and then do processing in PHP:

compare timein to schedule
compare timeout to schedule
deduct difference
compare remaining time "on the clock"

however, I hope this isn't for an actual payroll. In most cases (in the US, anyway), if you allow* an employee to work early/late, you're legally obligated to pay them for their time.

*meaning, you don't make them clock back out and wait

[newphpcoder]

Good day!

I have new sample for further understanding of my problem.

The real schedule of employee DS-1001 is from 9:35 PM to 5:35 AM he is night shift and the employee DS-1002 is from 5:35 AM to 1:35 PM he is morning shift.

here is the sample .xml file
EMP_NO Time In Time Out
DS-1001 10/1/11 9:35 PM 10/2/11 5:35 AM // this is the exact timein and timeout so theres no problem
DS-1001 10/2/11 9:00 PM 10/3/11 6:00 AM // in this sample the employee timein early and also timeout late.
DS-1001 10/3/11 10:00 PM 10/4/11 5:00 AM // in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,
DS-1002 10/1/11 5:35 AM 10/1/11 1:35 PM// this is the exact timein and timeout so theres no problem
DS-1002 10/2/11 5:00 AM 10/2/11 2:00 PM// in this sample the employee timein early and also timeout late.
DS-1002 10/3/11 6:00 AM 10/3/11 1:00 PM// in this sample the employee late to timein and he timeout early, so it should be subtract in his attendance total hours,


and the data save in database is:
EMP_NO timein timeout total rendered
DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 00:00:00 00:00:00
DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 00:00:00 00:00:00
DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 00:00:00 00:00:00
DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 00:00:00 00:00:00
DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 00:00:00 00:00:00
DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 00:00:00 00:00:00

OT
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00

EMP_NO datatype is varchar
timein datatype is datetime
timeout datatype is datetime
total datatype is time
rendered datatype is time
OT datatype is time

the total field is the sum of the total hours of the employee
the rendered field is the exact 8 hours of employee or if the employee late like for example he is late or timeout early it should be subtracted and be output in rendered but normally it is 8 hours if his not late. In this field I don't have idea how can i do that.
the OT field is the total - rendered field.

I dont know how can I insert that in my database.



I used this code to get the total hours but it did not work, and no error displayed.

INSERT INTO employee (total) SELECT sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

I hope somebody can help me...

Thank you so much...

[newphpcoder]

tis is the vardump of my database:

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.1.41


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema db_upload
--

CREATE DATABASE IF NOT EXISTS db_upload;
USE db_upload;

--
-- Definition of table `db_upload`.`employee`
--

DROP TABLE IF EXISTS `db_upload`.`employee`;
CREATE TABLE  `db_upload`.`employee` (
  `EMP_NO` varchar(50) NOT NULL,
  `timein` datetime NOT NULL,
  `timeout` datetime NOT NULL,
  `total` time NOT NULL,
  `rendered` time NOT NULL,
  `OT` time NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `db_upload`.`employee`
--

/*!40000 ALTER TABLE `employee` DISABLE KEYS */;
INSERT INTO `db_upload`.`employee` (`EMP_NO`,`timein`,`timeout`,`total`,`rendered`,`OT`) VALUES 
 ('DS-1001','2011-10-01 21:35:00','2011-10-02 05:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-02 21:00:00','2011-10-03 06:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1001','2011-10-03 22:00:00','2011-10-04 05:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-01 05:35:00','2011-10-01 13:35:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-02 05:00:00','2011-10-02 14:00:00','00:00:00','00:00:00','00:00:00'),
 ('DS-1002','2011-10-03 06:00:00','2011-10-03 13:00:00','00:00:00','00:00:00','00:00:00');
/*!40000 ALTER TABLE `employee` ENABLE KEYS */;




/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

[traq]

do you have a specific question? or are you looking for someone to do this work for you?

* in order to handle cases like early time-in/time-out, you'll need to have a way to know what each employees' scheduled shift is. Perhaps there is another table in the DB with each employees' scheduled hours?

* How are you planning to administer this? You're posting in the PHP forum, but all the examples you've given use SQL (from MySQL Administrator). Do you want to use MySQL Admin? Do you want to make a web app to administer this? Do you want MySQL to handle it all automatically?

[newphpcoder]

do you have a specific question? or are you looking for someone to do this work for you?

* in order to handle cases like early time-in/time-out, you'll need to have a way to know what each employees' scheduled shift is. Perhaps there is another table in the DB with each employees' scheduled hours?

* How are you planning to administer this? You're posting in the PHP forum, but all the examples you've given use SQL (from MySQL Administrator). Do you want to use MySQL Admin? Do you want to make a web app to administer this? Do you want MySQL to handle it all automatically?

the employee schedule is 9:35 Pm - 5:35 AM, 5:35 AM-1:35 PM and 1:35 Pm - 9:35 PM only for operators. As of now I dont have tables for the scheduled hours. Is it needed ?I want that mysql will handled it automatically computed.

Sorry and Thank you

[newphpcoder]

I change my syntax from insert to update because the total field will only update because they are in same table of timein and timeout, so I used this syntax and i test it in mysql:

UPDATE employee
SET total= sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

When I used this syntax, the output was add in total field and it is correct...Now my problem is in the rendered, i dont know how can I display the rendered time the 8 hours or below 8 hours if he is late or early to timeout.Because now in total i only used update query and i run it in mysql.

In rendered it only gets the 8 hours from like for example 9:35 PM to 5:35 AM if he timein 9:00 PM he only get from 9:35PM - 5:35 AM or if he timeout 5:40 AM only the 9:35 PM - 5:35 AM he sum and insert in rendered field.

Thank you so much...

[newphpcoder]

I have 3 shifts 21:35:00 to 05:35:00 , 05:35:00 to 13:35:00 , and 13:35:00 to 21:35:00

I have this data in my database:
EMP_NO| timein| timeout |total| rendered
DS-1001 2011-10-01 21:35:00 2011-10-02 05:35:00 08:00:00 00:00:00
DS-1001 2011-10-02 21:00:00 2011-10-03 06:00:00 09:00:00 00:00:00
DS-1001 2011-10-03 22:00:00 2011-10-04 05:00:00 07:00:00 00:00:00
DS-1002 2011-10-01 05:35:00 2011-10-01 13:35:00 08:00:00 00:00:00
DS-1002 2011-10-02 05:00:00 2011-10-02 14:00:00 09:00:00 00:00:00
DS-1002 2011-10-03 06:00:00 2011-10-03 13:00:00 07:00:00 00:00:00

OT
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00
00:00:00

I compute my total buy this code:

UPDATE employee
SET total= sec_to_time(unix_timestamp(timeout) - unix_timestamp(timein));

I test the code suggesed by other programmer and I add syntax for 21:35:00 - 05:35:00 shift:

select sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
              else time_to_sec('05:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
            end+
	    case 
              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
              else time_to_sec('21:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
            end
           ) FROM employee;

and the output is
-16:00:00
-15:00:00
-18:00:00
08:00:00
08:00:00
07:00:00

I'm hoping that this output will insert in rendered field, I see only a problem in night shift 21:35:00 - 05:35:00
Thank you so much

[newphpcoder]

Is there any snytax/query except case statement if impossible to get the rendered from timein and timeout correctly?
The case statement that I post is produce correct data in oly one shift, the rest is wrong.

Thank you

[newphpcoder]

I tried this query:

UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 
           case 
              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0
              else time_to_sec('21:35:00') - time_to_sec(time(timein))
	      when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0
              else time_to_sec('05:35:00') - time_to_sec(time(timein))
	      when time_to_sec(time(timein)) < time_to_sec('13:35:00') then 0
              else time_to_sec('13:35:00') - time_to_sec(time(timein))
            end +
            case 
              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')
              when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('13:35:00')
              when time_to_sec(time(timeout)) > time_to_sec('21:35:00') then 0
              else time_to_sec(time(timeout)) - time_to_sec('21:35:00')
            end)
;

and i got this error:
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0

e' at line 9
(0 ms taken)