«
»

Import Fingerprint Data ’1_attlog.dat’ to OrangeHRM Table ‘hs_hr_attendance’ using PHP

February 9, 2010, 18:16

In previous article, Read Fingerprint Attendance Data ‘1_attlog.dat’ Using PHP and Transfer to MySQL, we already talk about importing the fingerprint time attendance data ’1_attlog.dat’ to MySQL table for further process. Now we will try to import the data to OrangeHRM. OrangeHRM is open source Human Resources software that run using PHP + MySQL platform. OrangeHRM consists several module that can help to manage human resource operation, such as leave management, time attendance management, salary management and employee data.

OrangeHRM screen shot

OrangeHRM screen shot

We still use fingreprint device X100 for this experiment.

X100 Fingerprint Device

X100 Fingerprint Device

We will focus on how to import the data from fingerprint time attendance data ’1_attlog.dat’ to OrangeHRM database that store the time attendance data. The table is called ‘hs_hr_attendance’. Here is the structure of ‘hs_hr_attendance’ table. By the way, we are using OrangeHRM version 2.5.0.2 by the time of this writing.

mysql> desc hs_hr_attendance;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| attendance_id  | int(11)       | NO   | PRI | NULL    |       |
| employee_id    | int(11)       | NO   |     | NULL    |       |
| punchin_time   | datetime      | YES  |     | NULL    |       |
| punchout_time  | datetime      | YES  |     | NULL    |       |
| in_note        | varchar(250)  | YES  |     | NULL    |       |
| out_note       | varchar(250)  | YES  |     | NULL    |       |
| timestamp_diff | int(11)       | NO   |     | NULL    |       |
| status         | enum('0','1') | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

Let we explain one-by-one of those fields.

- attendance_id, store the ID of time attendance log, it should be auto-increment, but we don’t know why it is not
- employee_id, store the employee ID data, it associated with table ‘hs_hr_employee’ on field ‘emp_number’
- punchin_time, store the punch-in time data
- punchout_time, store the punch-out time data
- in_note, store the reason of punch-in data
- out_note, store the reason of punch-out data
- timestamp_diff, we don’t know yet, but let’s assume it will store the difference between punchin_time and punchout_time (in minutes or seconds? we still don’t know yet)
- status, we don’t know yet

Let’s talk about the import strategy. Here is the step-by-step of our import mechanism.

1. User or administrator will download the time attendance data from fingerprint device using USB
2. The data will be automatically saved to USB with file name 1_attlog.dat
3. User or administrator have to upload the data using web interface
4. The system (I mean our script) will process the uploaded 1_attlog.dat

That’s the general procedure of processing data, and the key is point (4).

We will extend the point (4) like below.

1. We need an upload form to upload the 1_attlog.dat file
2. We need to store the uploaded 1_attlog.dat to specified folder on server
3. We need to read 1_attlog.dat and store it to temporary table (let’s called it to hs_hr_timeattendance_log)
4. We need to read the temporary table (hs_hr_timeattendance_log) and store it to OrangeHRM table hs_hr_attendance

For additonal purpose, we will create additional table to store the fingerprint serial number. We called the table hs_hr_fingerprint_device. The structure is like below.

1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS `hs_hr_fingerprint_device` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hardware_serial_number` varchar(100) DEFAULT NULL,
  `location` varchar(100) DEFAULT NULL,
  `hardware_model` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is the purpose of each field.

- id, store the id, primary key and auto-increment value
- hardware_serial_number, store the serial number of the fingerprint device
- location, store the location of the device
- hardware_model, store the fingerprint hardware model

Here is the sample of the data.

mysql> SELECT * FROM hs_hr_fingerprint_device;
+----+------------------------+----------------+----------------+
| id | hardware_serial_number | location       | hardware_model |
+----+------------------------+----------------+----------------+
|  1 | 1533039480175          | JAKARTA OFFICE | X100           |
+----+------------------------+----------------+----------------+
1 row in set (0.00 sec)

The hardware serial number is stored in file 1_attlog.dat on the first line. Here is the sample.

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
   33	2010-01-14 12:17:30	1	0	0	0
   12	2010-01-14 12:19:24	1	0	0	0

Now we will create the temporary table for store the data from 1_attlog.dat, called hs_hr_timeattendance_log.

1
2
3
4
5
6
7

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

1. We need an upload form to upload the 1_attlog.dat file
OK, now we create the form to upload the 1_attlog.dat file. It just simple HTML form like below.

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

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Upload Data</title>
</head>
<body>
<p>Use this form to upload time attendance data (1_attlog.dat)</p>
<form action="p_upload_time_attendance.php" method="post" enctype="multipart/form-data">
  <p>Filename:
    <label>
      <input type="file" name="file_upload" id="file_upload" />
    </label>
    <label>
      <input name="submit" type="submit" id="submit" value="Upload" />
    </label>
  </p>
</form>
<p>&copy; Sony AK Knowledge Center</p>
</body>
</html>

Save is as upload_time_attendance.php.

Upload form for 1_attlog.dat

Upload form for 1_attlog.dat

2. We need to store the uploaded 1_attlog.dat to specified folder on server
We want to store the uploaded 1_attlog.dat at ../attdata folder under your OrangeHRM application folder, so you need to create ‘attdata’ folder inside your OrangeHRM installation folder.

3. We need to read 1_attlog.dat and store it to temporary table (let’s called it to hs_hr_timeattendance_log)
Now we will create the script that handle the file upload, read all data from 1_attlog.dat on the server and then transfer it to hs_hr_timeattendance_log table.

Important notes
OrangeHRM has it’s own employee ID and the fingerprint attendance system usually has it’s own ID, so
we have to associate this together. How? Simple, you can use the ‘custom1′ field on table ‘hs_hr_employee’ to store the user ID from the fingerprint device. User ID from the fingerprint device is the left most field on the ’1_attlog.dat’ file.

For example, there is employee name SONY ARIANTO KURNIAWAN, in OrangeHRM his employee number is B001 but in the fingerprint device the ID is 5, so you can put 5 on custom1 field on his employee table data.

4. We need to read the temporary table (hs_hr_timeattendance_log) and store it to OrangeHRM table hs_hr_attendance
Now we will mix it. Here is the full source code that will cover point (3) and (4) above.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124

<?php
  // script name: p_upload_time_attendance.php
 
  $folderName = "attdata";
 
  // connect to database
  $dbConn = mysql_connect('localhost', 'root', '') or DIE("Connection to database failed, perhaps the service is down !!");
  mysql_select_db('hr_mysql') or DIE("Database name not available !!");
 
  function processLogFileData() {
    global $folderName;
    global $dbConn;
 
    // read the attendance data log file (text format)
    $attendanceData = file_get_contents(getcwd() . "/" . $folderName . "/" . "1_attlog.dat");
    $arrAttendance = explode("\r\n", $attendanceData);
    $hardwareSerialNumber = substr($arrAttendance[0], 3); // get the hardware serial number string from the first element of the array
    $arrAttendance = array_slice($arrAttendance, 1, -2);
 
    // get the serial number id
    $sqlString = "SELECT id FROM hs_hr_fingerprint_device WHERE hardware_serial_number = '" . $hardwareSerialNumber . "'";
    $resultFingerprint = mysql_query($sqlString, $dbConn);
    if (mysql_num_rows($resultFingerprint) > 0) {
      $hardwareId = mysql_result($resultFingerprint, 0, "id");
    } else {
      $hardwareId = 0;
    }
 
    // clean the data table first
    echo "cleaning the temporary table hs_hr_timeattendance_log...<br/>";
    mysql_query("DELETE FROM hs_hr_timeattendance_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];
      echo "processing user hardware id: " . $hardwareUserId . "<br/>";
      mysql_query("INSERT INTO hs_hr_timeattendance_log (hardware_user_id, time_log, hardware_id) VALUES ('" . $hardwareUserId . "', '" . $timeLog . "', " . $hardwareId . ")", $dbConn);
    }
 
    echo "import to hs_hr_timeattendance_log done!<br/>";
 
    // looping on the hs_hr_timeattendance_log (group by on date)
    $sqlString = "SELECT SUBSTR(time_log FROM 1 FOR 10) AS time_log_date, hardware_id FROM hs_hr_timeattendance_log GROUP BY substr(time_log FROM 1 FOR 10)";
    $resultTimeLogDate = mysql_query($sqlString, $dbConn);
 
    for ($i=0;$i<mysql_num_rows($resultTimeLogDate);$i++) {
      $punchDate = mysql_result($resultTimeLogDate, $i, "time_log_date");
      echo "processing date " . $punchDate . "<br/>";
 
      // select all employee
      $sqlString = "SELECT * FROM hs_hr_employee WHERE custom1 IS NOT NULL";
      $resultEmployee = mysql_query($sqlString, $dbConn);
 
      for ($j=0;$j<mysql_num_rows($resultEmployee);$j++) {
        $hardwareIdOnEmployee = mysql_result($resultEmployee, $j, "custom1");
        $employeeIdOrange = mysql_result($resultEmployee, $j, "emp_number");
        $employeeFullName = mysql_result($resultEmployee, $j, "emp_firstname") . " " . mysql_result($resultEmployee, $j, "emp_lastname");;
 
        echo "processing hardware user id: " . $hardwareIdOnEmployee . " " . $employeeFullName . "<br/>";
 
        // select first data on punch-in
        $sqlString = "SELECT * FROM hs_hr_timeattendance_log WHERE SUBSTR(time_log FROM 1 FOR 10) = '" . $punchDate . "' AND hardware_user_id = '" . $hardwareIdOnEmployee . "' ORDER BY time_log ASC LIMIT 1";
        $resultPunchStart = mysql_query($sqlString, $dbConn);
        if (mysql_num_rows($resultPunchStart) > 0) {
          $punchStart = mysql_result($resultPunchStart, 0, "time_log");
        } else {
          $punchStart = "";
        }
 
        // select last data on punch-in
        $sqlString = "SELECT * FROM hs_hr_timeattendance_log WHERE SUBSTR(time_log FROM 1 FOR 10) = '" . $punchDate . "' AND hardware_user_id = '" . $hardwareIdOnEmployee . "' ORDER BY time_log DESC LIMIT 1";
        $resultPunchEnd = mysql_query($sqlString, $dbConn);
        if (mysql_num_rows($resultPunchEnd) > 0) {
          $punchEnd = mysql_result($resultPunchEnd, 0, "time_log");
        } else {
          $punchEnd = "";
        }
 
        // validation
        if ($punchStart != "" && $punchEnd != "") {
          if ($punchStart == $punchEnd) {
            $punchEnd = "";
          }
 
          if ($punchStart != $punchEnd) {
            $sqlString = "SELECT last_id FROM hs_hr_unique_id WHERE table_name = 'hs_hr_attendance'";
            $resultMaxAttendance = mysql_query($sqlString, $dbConn);
            $maxAttendanceId = mysql_result($resultMaxAttendance, 0, "last_id") + 1;
 
            // start save last ID to hs_hr_unique_id
            $sqlString = "UPDATE hs_hr_unique_id SET last_id = " . $maxAttendanceId . " WHERE table_name = 'hs_hr_attendance'";
            mysql_query($sqlString, $dbConn);
 
            // start save it to hs_hr_attendance
            if ($punchEnd != "") {
              $sqlString = "INSERT INTO hs_hr_attendance " .
                         "(attendance_id, employee_id, punchin_time, punchout_time, in_note, out_note, timestamp_diff, status) VALUES " .
                         "(" . $maxAttendanceId . ", " . $employeeIdOrange . ", '" . $punchStart . "', '" . $punchEnd . "', '', '', 0, '1')";
            } else {
              $sqlString = "INSERT INTO hs_hr_attendance " .
                         "(attendance_id, employee_id, punchin_time, punchout_time, in_note, out_note, timestamp_diff, status) VALUES " .
                         "(" . $maxAttendanceId . ", " . $employeeIdOrange . ", '" . $punchStart . "', NULL, '', '', 0, '1')";
            }
            mysql_query($sqlString, $dbConn);
          }
        }
      }
    }
  }
 
  // check the file upload error
  if ($_FILES["file_upload"]["error"] > 0) {
    // there is error on file upload process
    echo "Error: " . $_FILES["file_upload"]["error"] . "<br/>";
  } else {
    // move the uploaded 1_attlog.dat to ../attdata
    if (move_uploaded_file($_FILES["file_upload"]["tmp_name"], getcwd() . "/" . $folderName . "/" . $_FILES["file_upload"]["name"])) {
      processLogFileData();
      echo "import to hs_hr_attendance done!<br/>";
    }
  }
?>

Let us explain about the code above. Actually we just do insert data to ‘hs_hr_attendance’ table on OrangeHRM and also update the ‘hs_hr_unique_id’ table. We treat fingerprint device as a dumb terminal, so actually you can punch your fingerprint as many as you like, but we only record the first punch and the last punch on particular date. We assume the first punch is the punch-in time and the last punch is the punch-out time.

If everything goes right then you can see the import result from the time attendance report from OrangeHRM, both from admin page or from employee page. The example is like below.

OrangeHRM attendance report example

OrangeHRM attendance report example

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

Bookmark and Share
Tags: , , , , , , , , , , ,
Category: MySQL, OrangeHRM, PHP  |  Comment (RSS)  |  Trackback

Related posts:

  1. Read Fingerprint Attendance Data ’1_attlog.dat’ Using PHP and Transfer to MySQL
  2. Add Province Options in OrangeHRM for your Country
  3. Create Table Structure for Storing Multi Level Category Data
  4. Get Feeds Data (RSS) using PHP and DOMDocument Class
  5. Create Simple Data Paging using PHP and MySQL

11 Comments

  1. Titus Permadi says:
    February 9, 2010 at 20:52

    Dear Mr. Sony,

    Thank you so much for this tool.

    Titus Permadi

  2. OrangeHRM Tools by Sony-AK | Titus Permadi's Weblog says:
    February 16, 2010 at 00:20

    [...] 1. Import data dari Finger Print ke OrangeHRM –> lihat disini [...]

  3. didit permana says:
    April 3, 2010 at 12:54

    dari Info linux ke OrangeHRM akhirnya kesini juga, gak sabar coba feature OrangeHRM

  4. djoko says:
    May 8, 2010 at 11:40

    mo tanya mas Sony, kalo PHP bisa akses langsung ke mesin sidik jari ga ya ?

  5. Sony AK says:
    May 10, 2010 at 12:17

    @djoko halo, PHP gak bisa akses langsung, akses langsung di sini maksudnya adalah akses ke mini web server yang ada di mesin fingerprint nya.

  6. Chris says:
    May 12, 2010 at 14:48

    Salam kenal Pak Sony.

    Apakah rules ini compatible dengan semua fingerprint??
    Mungkin kita bisa chat pak? YM saya: chrizz_stuff@yahoo.co.id.

    Thanks.

  7. Sony AK says:
    May 13, 2010 at 12:22

    @chris Hi Pak Chris, harus dilihat dulu format data attendance yang dikeluarkan oleh mesin fingerprint nya seperti apa dulu, makanya di artikel saya sebutkan model hardware nya.

  8. Teguh says:
    May 15, 2010 at 17:08

    Articel yang sangat menarik mas sony, kalo kita pake mesin absen lain bagaimana, maksud saya bukan finger print, kalo handkey bagaimana?

    Terima kasih mas

  9. trie says:
    June 17, 2010 at 23:38

    Malam Mas Sony,

    Pak untuk fingerprint yg compatible diaplikasi php underweb itu apa ya Mas..?
    bisa chatt dengan sy Mas..? triyo_13@yahoo.com

    salam

  10. Tahmid says:
    June 19, 2010 at 21:19

    Hi I am using Real-And.com product ZD120T Which work fine in my local network 192.168.0.224

    and my HRM 2.6 on 192.168.0.117

    how can I connect or import my employee enroll log to orangehrm?

    my device not supported USB

  11. Firman Hadi says:
    July 9, 2010 at 11:48

    Selamat siang, Pak Sony
    utk mesin sidik jari X100 apakah bisa diatur utk waktu izin keluar pas jam kerja. misal saya praktekkan di sekolah. apakah bisa disetting apabila ada siswa izin keluar dan harus melakukan absensi dengan mesin.

    mohon pencerahannya

    terima kasih

Leave a Reply