BolPoint.wordpress.com

February 27, 2011

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

Filed under: Coretan,Fredom,Linux,Windows — bolpoint @ 4:11 am

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 shotOrangeHRM screen shot

We still use fingreprint device X100 for this experiment.

X100 Fingerprint DeviceX100 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.datUpload 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 exampleOrangeHRM attendance report example

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

 

Sumber : http://www.sony-ak.com/2010/02/import-fingerprint-data-1_attlog-dat-to-orangehrm-table-hs_hr_attendance-using-php/

2 Comments »

  1. Hello, I have followed what is given here and used the code exactlly how it is, but i get this error after I hit upload,

    Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 11 in C:\Program Files\OrangeHRM\2.7\htdocs\orangehrm-2.7\p_upload_time_attendance.php on line 90

    Please Help.

    Comment by Naveen S — July 8, 2012 @ 3:30 am | Reply

  2. hiii…how to do automatic upload file in server

    Comment by dharam — January 5, 2016 @ 9:27 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: