Computing time and attendance

nivashni1234

Hello Everyone.

Please assist me to understand how I would save this information in a database.
I am using the information exported from a Fingerprint reader that is used for Time and Attendance.The csv export has many field but the information I am concerned with is the DATE,TIME,EMPLOYEE NAME,EMPLOYEE ID ,RESULT (success or fail)

The sample information is shown below.We have an employee that will clock in when he arrives for work and clock out for lunch,return and then again clock out
for tea.He will clock in again after tea and finally clock out when he goes home.
Off course there could be other reasons will clock in and out.

I realise I will have to store these raw logs in the database,but I need ideas on how to store information once computed.What I need to do is calculate how many hours an employee worked for the day,then have some method to extract how many hours were worked for a period of 7 days or even 14 days.

Should I have seperate tables for the computed information ? How can I use PHP to compute
the hours worked per day based on the several clocks in and out per employee ?

Any advice will be appreciated.

2014-11-26 07:16:09 James Lolly ED001 Success
2014-11-26 07:17:06 James Lolly ED001 Success
2014-11-26 07:22:18 James Lolly ED001 Success
2014-11-26 07:32:59 Kelly ZA001 Success
2014-11-26 07:34:13 Kelly ZA001 Success
2014-11-26 07:35:56 Suri SP001 Success
2014-11-26 07:50:30 Milo MM001 Success
2014-11-26 07:53:40 Annie ZD001 Success
2014-11-26 07:59:58 Chume TH002 Success
2014-11-26 08:00:33 Chume TH002 Success
2014-11-26 08:01:13 Jack Anston NS001 Success
2014-11-26 08:03:10 Peter Zack SZP001 Success
2014-11-26 08:04:39 Peter Zack SZP001 Success
2014-11-26 08:05:26 Suri SP001 Success
2014-11-26 08:06:52 James Lolly ED001 Success
2014-11-26 08:22:31 Jack Anston NS001 Success
2014-11-26 08:22:49 Chume TH002 Success
2014-11-26 08:23:40 James Lolly ED001 Success
2014-11-26 08:38:21 Ari Ari TK001 Success
2014-11-26 08:39:25 Ari Ari TK001 Success

Thanks
Kimmy

pbismad

Ideally, you should end up with two database tables.

The first table is a user table that holds the EMPLOYEE NAME (separate columns for first name and last name), EMPLOYEE ID, and has an auto-increment integer column that assigns a user_id. You may in fact already have a table like this if the users have the ability to log in to a computer system for controlling access to information.

After you validate the raw data (for each employee - does it have an even number of in/out records during the period of a normal work day, do the length of the in and out periods make sense, if there are no records for an employee on any particular work day, why, and what do you do if the result = fail) and reconcile the discrepancies by manually entering/editing data, you need to store the data in a table that has columns for - an id (auto-increment integer column), the user_id (this will be the value from the user table), start datetime and end datetime for each 'in/work' period. This will let you write a sql query that calculates the time worked by any user(s), just by summing up the time difference between the start and end datetime values on each day and for any date range.

Assuming you are using a mysql database, you would use the TIMEDIFF() function to calculate the time difference between the start and end times in a row, TIME_TO_SEC() to convert that to seconds, then SUM() the seconds value by grouping (GROUP BY) the data by the user_id and the DATE() part of the start datetime, then use SEC_TO_TIME() to convert the summed seconds back to a HH:MM:SS time value.

lilysmith22

pbismad;11055115 wrote:
Ideally, you should end up with two database tables.

The first table is a user table that holds the EMPLOYEE NAME (separate columns for first name and last name), EMPLOYEE ID, and has an auto-increment integer column that assigns a user_id. You may in fact already have a table like this if the users have the ability to log in to a computer system for controlling access to information.

After you validate the raw data (for each employee - does it have an even number of in/out records during the period of a normal work day, do the length of the in and out periods make sense, if there are no records for an employee on any particular work day, why, and what do you do if the result = fail) and reconcile the discrepancies by manually entering/editing data, you need to store the data in a table that has columns for - an id (auto-increment integer column), the user_id (this will be the value from the user table), start datetime and end datetime for each 'in/work' period. This will let you write a sql query that calculates the time worked by any user(s), just by summing up the time difference between the start and end datetime values on each day and for any date range.

Assuming you are using a mysql database, you would use the TIMEDIFF() function to calculate the time difference between the start and end times in a row, TIME_TO_SEC() to convert that to seconds, then SUM() the seconds value by grouping (GROUP BY) the data by the user_id and the DATE() part of the start datetime, then use SEC_TO_TIME() to convert the summed seconds back to a HH:MM:SS time value.

Well this was a correct and easiest way for his question. Thanks.