Sony AK Knowledge Center

During our experiment using fingerprint device X100 we need to read the time attendance log data. The device has option to download the time attendance data to USB flash disk.

X100 Fingerprint Device

The file that contains the time attendance data is 1_attlog.dat. It’s actually a text file and you can open it using standard text editor. There is a one line header on the first line and two footer lines on the end of the file, but between that header and footer is the actual attendance log in the form of tab delimited file. The sample data is like below.

SN=1533039480175
    1	2010-01-14 10:38:50	1	0	0	0
    1	2010-01-14 11:34:18	1	0	0	0
   20	2010-01-14 12:15:07	1	0	0	0
   26	2010-01-19 17:44:06	1	1	0	0
   19	2010-01-19 17:47:15	1	1	0	0
   29	2010-01-19 20:02:48	1	1	0	0
    2	2010-01-19 20:38:25	1	1	0	0
   24	2010-01-20 08:47:18	1	1	0	0
   20	2010-01-20 08:51:28	1	0	0	0
   19	2010-01-20 08:57:31	1	0	0	0
   27	2010-01-20 09:06:37	1	0	0	0
CHECKSUM=-1264567042

Let we explain the format. There are several fields, 6 fields actually. Our needs only the first and second field.

1. First field is the employee ID stored on the fingerprint device.

2. Second field is the punch time. Every punch on the fingerprint will be stored and become one new line of data.

The rest fields is actually the check-in or check-out status. We don’t need it this time.

Now we have to create table on MySQL to store the attendance data. We just want to store the employee ID from the fingerprint device and punch datetime. We called it es_temp_time_attendance_log table.

1
2
3
4
5
6

CREATE TABLE IF NOT EXISTS `es_temp_time_attendance_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hardware_user_id` int(11) DEFAULT NULL,
  `time_log` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=351 ;

Here is the code to read the 1_attlog.dat and stored it to the MySQL table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<?php
  // connect to database
  $dbConn = mysql_connect('localhost', 'root', '') or DIE("Connection to database failed, perhaps the service is down !!");
  mysql_select_db('your_database_name') or DIE("Database name not available !!");
 
  // read the attendance data log file (text format)
  $attendanceData = file_get_contents(getcwd() . "/" . "1_attlog.dat");
  $arrAttendance = explode("\r\n", $attendanceData);
  $arrAttendance = array_slice($arrAttendance, 1, -2);
 
  // clean the data table first
  mysql_query("DELETE FROM es_temp_time_attendance_log", $dbConn);
 
  // loop the attendance data and save it to database
  foreach ($arrAttendance as $attendance) {
    $arrAttendanceLine = explode("\t", trim($attendance));
    $hardwareUserId = $arrAttendanceLine[0];
    $timeLog = $arrAttendanceLine[1];
    mysql_query("INSERT INTO es_temp_time_attendance_log (hardware_user_id, time_log) VALUES (" . $hardwareUserId . ", '" . $timeLog . "')", $dbConn);
  }
?>

You can adjust the database connection above to your machine configuration. From now we have the data transfered to MySQL database and next we can use it for many purpose, for example to integrate with OrangeHRM application or for payroll calculation.

Update February 10, 2010
We already create article about fingerprint time attendance data integration with OrangeHRM time attendance table, you can read here Import Fingerprint Data ‘1_attlog.dat’ to OrangeHRM Table ‘hs_hr_attendance’ using PHP.

If you have any difficulties please send us comment below or drop e-mail to info@sony-ak.com.