Biometric Device Integration - Plugin Method in Zoho People | Zoho People 5.0 | Help | Admin Guide

Plugin Method

Integration Using Plugin Method

Another way to integrate the biometric device with Zoho People is by configuring a plugin through the Tomcat server.

Prerequisites 

We support Microsoft SQL Server, Microsoft Access and MySQL database and the query format as mentioned below.
Notes
It is important to have an IT person or your biometric vendor at your side to proceed with the integration.

Biometric ID Mapping

Once you have successfully configured and setup biometric devices for employee check-in and check-out, you can perform ID mapping to link the device's mapper ID to Zoho People's employee ID. This enables seamless biometric device-based secure check-in and check-out for all of your employees.

Map with Zoho People employee ID:
  1. To add user ID Mapping or to view existing mapping, navigate to Operations > Attendance > Biometric ID Mapping.
  2. Click on Add User ID Mapping to configure a new mapping.
  3. Click on the ellipses () icon for import, export, and download options. 
Watch the help video to learn how to configure Biometric ID Mapping in Zoho People:

Software Requirements 

Apache Tomcat 9 - https://tomcat.apache.org/download-90.cgi (Windows service installer)
Java - https://www.openlogic.com/openjdk-downloads (latest version and as per OS)

Warning
It is mandatory to have your biometric vendor/IT team at the time of integration.

Database Requirements

Database: We support Microsoft SQL Server, Microsoft Access and MySQL databases with the same query format as below.

"Select employeeID, eventTime, isCheckin, downloadDate from Attendance"

*employeeID - Should be the same as the employee ID in Zoho People
*eventTime - Attendance in-punch and out-punch (in date-time format)
*ischeckin - 1/0 (1 denotes check-in and 0 denotes check-out)
*downloadDate - Date and time at which the attendance data is pushed to the database (in date-time format)

Please find the sample database table screenshot attached below for reference.


Notes
  1. If the Downloaddate column is not available in the table, then we can’t assure that all the data from the SQL database will get pushed to Zoho People, and there might be a possible chance of data loss.
  2. If there are multiple shifts for your employees, all of them should be mapped in Zoho People.
  3. If the biometric ID is different from the Zoho People employee ID, then the biometric ID has to be mapped in Zoho People under Operations > Attendance > Biometric ID Mapping.
  4. The integration is fully dependent on the server system where the biometric data is stored. If it crashes or reboots due to improper shutdown of the system, it may corrupt the configuration page. In such a scenario, we will have to reconfigure the integration.

Terminologies

Terminology
Definition
Biometric Device 
It is an electronic machine that is made for identification and authentication through physiological and behavioural characteristics like face recognition, fingerprint reading, palm recognition, and others.
Tomcat Server
It is an open-source Java servlet container that implements many Java Enterprise Specs, such as the Websites API, JavaServer Pages and the Java Servlet.
MySQL (Database)
MySQL is an open-source relational database management system. The open-source GPL licence allows programmers to modify the MySQL software to fit their own specific environments. 
Microsoft Access
Microsoft Access is a database management system from Microsoft that combines the relational Access Database Engine with a graphical user interface and software development tools.
Microsoft SQL Server
Microsoft SQL Server is a relational database management system. It is a software product with the primary function of storing and retrieving data as requested by other software applications.

Procedure

Ensure Java and Tomcat applications are downloaded and installed. Check under Local disc C > Program files or Program files (86) > whether JAVA/Tomcat is installed. If not, install both the software.

Tomcat Setup

  1. Service Name: Tomcat9ZohoPeople
  2. Shutdown Port: 8989
  3. Connector Port/Tomcat Server Port: 8584 (check if this port no is not used in any other service)
  4. Kindly select the Open Logic Java file path: Under C drive > Program Files/ Program Files X86/ Open Logic/ jdk <version>
  5. Start-up Type: Start > Search Service > Tomcat9Zoho (right click) > Properties > Startup Type > Automatic

War File Installation

  1. Click on the below link to download the war file. 
    https://www.zoho.com/people/downloads/ZAttendance.war
  2. Navigate to Local disc C > Program file > Apache software foundation > Tomcat9 > Web apps > Paste the war file > refresh > File will be extracted.
  3. Routing to plugin page Check whether you're able to load the plugin page via the URL - http://localhost:8584/ZAttendance

Plugin Configuration Steps

  1. Click on the Configure button in the top right corner of the plugin page to open the configuration page and select the database type.
  2. Copy and paste the example connection URL given below the field and update the database name.

    Change the port number of the database instance accordingly.
    If Microsoft SQL: Navigate to Program file C > Windows > Syswow64 > sql_server_manager > SQL server network
    Configuration > Protocol SQLExpress > TCP/IP > IP Address > TCP port: 1433 port > Apply > Ok.
    (OR)
    Navigate to Start > Search > Computer management > SQL Server network management or
    Configuration (second option on left) > TCP/IP > IP Address > TCP port: 1433 port > Apply > Ok.

    If MySQL - Default port number is 3306.

    If Microsoft Access - Add the database path and change the forward slashes to backward slashes.

  3.  Add the database username and password. If MySQL, the default username is "root" and for Microsoft Access is "admin".
  4. Add the Delay Time and Sleep Time as 5 minutes (minimum suggested time).
  5. SQL query configurations.

    If all columns are as per the requirement:
    Static Table:
    select employeeId, isCheckin, eventTime, [downloadDate] FROM [ZAttendance].[dbo].[Attendance] where downloadDate>=$1 and downloadDate<$2

    Dynamic Table:
    select employeeId, isCheckin, eventTime, [downloadDate] FROM #Tablename_%M_%Y# where downloadDate>=$1 and downloadDate<$2

    When the download date column is not available: (Delay time should be 1-2 hours)
    select employeeId, isCheckin, eventTime FROM [ZAttendance].[dbo].[Attendance] where eventTime>=$1 and eventTime<$2

    When CheckIN/CheckOUT direction differentiation is not specified in the table
    Push each punch as both IN & OUT to ZOHO:
    select employeeId, '1' as isCheckin, eventTime, [downloadDate] FROM [ZAttendance].[dbo].[Attendance] where downloadDate>=$1 and downloadDate<$2
    UNION
    select employeeId, '0' as isCheckin, eventTime, [downloadDate] FROM [ZAttendance].[dbo].[Attendance] where downloadDate>=$1 and downloadDate<$2

    (OR)

    Alternate In and Out Punches:
    select employeeId ,eventTime, [downloadDate] FROM
    [ZAttendance].[dbo].[Attendance] where downloadDate>=$1 and downloadDate<$2

  6. Generate Auth tokens:
    Go to api-console.zoho.in (or com/eu - based on the DC). Create a "Self Client" client type, copy Client ID and Client Secret and paste it in the Plugin page.

    Generate a code under Generate Code tab,
    Scope: ZohoPeople.attendance.all
    Duration: 10 minutes
    Description: BI

    Generate Refresh Token: Paste the Client ID / Client Secret / CODE / > Select the DC >
    Generate > copy and paste it in the Refresh Token field.

    Make sure that the tokens were generated using an admin account where Zoho Role/Role is marked as "admin" under the profile.

  7. Update Time Zone: Select your location’s time zone. Ensure the time zone of the attendance data in the database and the admin account's time zone are the same. The timezone in the plugin page refers to the timezone of the data in the database. For example, GMT, GMT+4, GMT+5:30, etc. 

    The timezone of the admin account (from which the auth tokens are generated) should be the same as the timezone of the biometric users.

  8. Manual testing and pushing: For testing, sync any one employee's data. Change the downloadDate column's name to the EventTime column's name in the SQL query after the where condition and add the employee ID filter as mentioned below.

     select employeeId, isCheckin, eventTime , [DownloadDate] FROM [ZAttendance].[dbo].[Attendance] where eventTime>=$1 and eventTime<$2 and employeeId=‘1001’

    In Manual sync tab, click on 'Get record count' and fetch and upload to pull and push the data to Zoho People.

  9. Once testing is done, automate the sync by clicking Resume sync in the plugin page.

  10. In order to take a backup of the configuration, create a folder titled 'Backup' in Apache Software Foundation folder > Copy of the .config file (file type: .xml) from the tomcat folder > Navigate to the backup folder > Paste.

Q/As and Common Issues Fixes

1. What are the types/methods of integrations available?
API and Plugin for integrating biometric attendance data. 
2. What are all the devices supported/suggested by Zoho People for the integration?
Any device that can use the Zoho People API/meets the prerequisite for plugin integration can be integrated with the Zoho People application.
3. Can we integrate the plugin through a cloud based server system?
Yes, provided that the system has Windows GUI.
4. What do the downloadDate column and eventTime column refer to?
The DownloadDate column refers to the date and time when the data was downloaded into the local attendance database, and the EventTime column refers to the date and time when the check-in/check-out punches were made in the biometric machine.
5. How to sync the data manually through the plugin page?
  1. Click on Stop Sync, then click on Configure.
  2. In the SQL query, after the where clause, change downloadDate to eventTime (as per the column name in the table), and click Save button.
  3. Manual sync has to be done for the date range for which data was not synced.
  4. Click on Configure. In the SQL query, revert back eventTime to downloadDate (as per the column name in the able) and automate the sync by changing the start time to the current time.
  5. Click Save button and click on Resume Sync. 
6. Configuration page is empty (or) and both 'Resume Sync' and 'Stop Sync' buttons are visible in the Plugin page and the Configure page is not opening when the button is clicked - Plugin page corruption/partial corruption issue.
  1. Click on Start Sync, then click Stop Sync.
  2. If only the Start Sync button is visible and the Configure page is empty when opened, the details need to be filled again. Skip to step 7.
  3. Else, if the page (once reloaded) is showing the Start Sync and the Stop Sync buttons once again, follow steps 4-11.
  4. Navigate to the search bar, search for ‘Services’ > Click on the relevant result and open Services > Right click on the tomcat service for Zoho People in ‘Running’ Status > Stop
  5. Delete the lastupdatedtime file which can be taken from C Drive>Program Files / Program Files (x86)>Apache Software Foundation>Tomcat
  6. Start the service once again on the Services page 
  7. If the backup folder is available in C Drive > Program Files/Program Files (x86) > Apache software foundation > Backup, copy the .config file from the backup folder and paste it into the Tomcat folder, replacing the existing .config file.
  8. Refresh the plugin page. Perform a manual sync for any past data that has not been synced to Zoho People.
  9. The sync can also be automated.
  10. If the backup folder is not available, the plugin page has to be configured again.
  11. If the complete reconfiguration is done, a backup can be taken of the .config file and the CX can use it if the issue is recurring.
7. "GC overhead limit exceeded" error in Tomcat Catalina Logs.
  1. The memory size of Tomcat for Java applications has to be increased.
  2. Go to C > Program Files/Program Files(X86) > Apache Software Foundation > Tomcat > bin folder > open tomcatw.exe > Java tab > Increase initial memory pool and maximum memory pool.
8. "Date range provided is more than 31 days" in Catalina Logs.
  1. Click on Stop Sync, then click on Configure.
  2. In the SQL query, after the where clause, change downloadDate to eventTime (as per the column name in the table), and click Save button.
  3. Manual sync has to be done for the date range for which data was not synced.
  4. Click on Configure. In the SQL query, revert back eventTime to downloadDate (as per the column name in the able) and automate the sync by changing the start time to the current time.
  5. Click Save button and click on Resume Sync. 
9. Exception @getLastUpdatedTime{0} in Tomcat Catalina Logs.
  1. Open Services, Stop Apache Tomcat service, 
  2. Go to C > Program Files/Program Files(X86) > Apache Software Foundation > Tomcat folder in file manager.
  3. Delete the lastupdatedtime file.
  4. Open Services and start Apache Tomcat service.
  5. Manual sync has to be done for the date range for which data was not synced, and the sync has to be automated by changing the start time. 
  6. Click on Resume Sync.
10. "Tokens invalid" issue / "Access token not found" error in Plugin Page.
The issue can be resolved by regenerating and replacing authentication tokens.
  1. Stop the sync by clicking on the Stop Sync button on the Plugin page.
  2. Delete the token file from the Tomcat folder (C > Program Files/Program Files(X86) > Apache Software Foundation > Tomcat).
  3. Generate Auth tokens:
  4. Go to api-console.zoho.in (or com/eu - based on the DC). Create a Self Client client type, copy the Client ID and Client Secret, and paste it on the Plugin page.
  5. Generate a code under the Generate Code tab.
    Scope: ZohoPeople.attendance.all
    Duration: 10 minutes
    Description: BI
  6. Generate Refresh Token: Paste the Client ID / Client Secret / CODE > Select the DC > Generate > copy and paste it in the Refresh Token field.
  7. Make sure that the tokens were generated using an admin account where Zoho Role/Role is marked as "admin" under the profile.
  8. Once done, click Save. 
  9. Automate the sync by clicking on Resume Sync.
11. Direction column is not available for integration, or strict direction differentiation is not present in the database table.
Workarounds:
  1. Pushing each punch as both check-in and checkout - In this case, the first and last available punches will be taken into account to consider the total working hours. Every punch will be pushed as both in and out. However, the status would not be displayed as office in.
  2. Pushing punches alternatively - If there are an even number of punches, let's say 4, the first and last will be taken to calculate total hours. In case of an odd number of punches, let's say 3, only the first and second will be taken since the earliest available checkout punch would be the second punch.
12. "DownloadDate" column is not available for integration.
We can set the delay time in the plugin and sync based on a trial and run basis (minimum 4 hours).
13. "All record count is 0" in Tomcat Catalina Logs.
There is no data available in the database table from the last updated time to current time; we can ask the CX to check with the biometric vendor/IT team to download the same.
14. The Tomcat page is not loading if the version is greater than 10 while integrating.
  1. Go to Tomcat folders > bin folder.
  2. Add the below link within the <context> tag in the context file.
    <Loader jakartaConverter="TOMCAT" />
15. Can we use the API from the biometric vendors end for integration?
No, that is not supported as of now. Zoho People API can be used to fetch and upload data from vendors end.
16. Attendance data is skipped and not getting synced for employee IDs.
  1. Check for employee ID in Zoho People and in the database table; it should be the same (if not, biometric ID mapping has to be done).
  2. ID mapping can be done by navigating to Zoho People from Operations > Attendance > Biometric ID-Mapping > Add User/Bulk Import using the ellipsis icon (3 dots).
17. "java.sql.SQLException: No suitable driver found for" error in Tomcat Logs.
  1. The SQL Instance port number would not have been set properly.
  2. Double-check that the connection URL and the database type are correct.
18. "java.sql.SQLException: Cannot open database "databaseName" requested by the login. The login failed" error in Tomcat Catalina Logs.
  1. The SQL database username or password could be incorrect in the plugin page.
  2. The SQL username/password can be double-checked by reconnecting the SQL using the same credentials used in the plugin.
19. Can we reduce the delay time in the Plugin page?
Yes, we can reduce, but we have to ensure that data is downloaded to the database in real time and we cannot assure that all punches will be synced (if there is any delay from biometric to database, that punch might get missed).
20. The "Resume Sync" button and "Configure" button are not working.
  1. Click on Start Sync, then click Stop Sync.
  2. If only the Start Sync button is visible and the Configure page is empty when opened, the details need to be filled again. Skip to step 7.
  3. Else, if the page (once reloaded) is showing the Start Sync and the Stop Sync buttons once again, follow steps 4-11.
  4. Navigate to the search bar, search for ‘Services’ > Click on the relevant result and open Services > Right click on the tomcat service for Zoho People in ‘Running’ Status > Stop
  5. Delete the lastupdatedtime file which can be taken from C Drive>Program Files / Program Files (x86)>Apache Software Foundation>Tomcat
  6. Start the service once again on the Services page 
  7. If the backup folder is available in C Drive > Program Files/Program Files (x86) > Apache software foundation > Backup, copy the .config file from the backup folder and paste it into the Tomcat folder, replacing the existing .config file.
  8. Refresh the plugin page. Perform a manual sync for any past data that has not been synced to Zoho People.
  9. The sync can also be automated.
  10. If the backup folder is not available, the plugin page has to be configured again.
  11. If the complete reconfiguration is done, a backup can be taken of the .config file and the CX can use it if the issue is recurring.
21. Sync is not happening and the Plugin page is not loading.
  1. Check if the correct port number is used.
  2. In services, check if the Tomcat service is running.
  3. If stopped, start the Tomcat service.
22. Can we have an SQL table in one system and a Plugin in another system?
Yes, but both systems need to be connected to the same network.
23. The data is available in the table, but the record count is fetched as '0', and there are no exceptions in the Catalina file.
  1. Check if the correct port has been mentioned in the connection URL in the plugin page.
  2. The port number can be checked through the SQL configuration manager.
  3. The name of the SQL server should match in both the SQL workspace and the configuration manager file.
  4. If the correct protocol for the server in use is not available, it is to be made available by the biometric vendor.
24. Plugin page not opening - "Address Already in Use" / "JVM_Bind Exception" error in Tomcat Catalina Logs.
  1. The server port or the connector port may already be in use.
  2. Both have to be modified in the server.xml file in the conf folder in the Tomcat folder.
  3. Modification of the connector port and the shutdown port, and restarting the Tomcat would resolve the issue.
25. "Connect / Java Socket Connection Error - Connection Refused: Connect" error in Tomcat Catalina Logs.
Caused by: java.net.ConnectException: Connection refused: Connect

In the SQL Server configuration manager, ensure that the TCP/IP is enabled in the protocols and the correct port is mentioned.
26. "Communications Link Failure" error in Tomcat Catalina Logs.
Authentication has failed when trying to connect from the Tomcat server to the Java client.

Steps to follow:
  1. Download the sslhandshake certificate from the HTTPS website in the same browser (Load the HTTPS site > Lock icon (top left corner) > Click on that > Go to details > Copy to file > Save it in a specific folder.
  2. Go to cmd by running it as administrator and type the following commands:
    cd Program Files/Java/jre1.8.0_211/bin
    keytool -list -keystore ..\lib\security\cacerts\" ->The above cmd asks for pwd; the default pwd is "changeit".
    keytool -import -alias ca (alias name) -file "C:\Users\Administrator\Desktop\ssl.cer" (path to downloaded SSL certificate) -keystore ..\lib\security\cacerts -storepass changeit
  3. Type "yes".
  4. The certificate will get added into cacerts.
  5. Once all the above is done, restart Tomcat.
27. "Communications Link Failure" / "SSLHandshakeException: No Appropriate Protocol" error in Tomcat Catalina Logs.
Add this at the end of the connection.

URL - ?enabledTLSProtocols=TLSv1.2 
28. "JSONObject: Access Token Not Found" error after Clicking 'Fetch & Upload'.
If Zoho People is in DC:
  1. Navigate to the Tomcat folder > config.properties > Open in Notepad.
  2. For the three API URLs that are available, do change the DC from https://people.zoho.com/........... to https://people.zoho.in/.........
  3. Similarly for the API URLS for https://accounts.zoho.com/......... to https://accounts.zoho.in/............