Attendance Device Integration

An attendance management tool is a positive strategy to magnify productivity in any organization. Zoho People offers attendance device integrations that aid in accurate attendance management.

You can integrate your attendance device with Zoho People by making use of APIs. In order to sync your device's attendance entries with your employee's attendance records in Zoho People, you need to have a common format between your device and your Zoho People account.

1. Introduction
2. Types of integration
3. Integration using API
4. Plugin integration

• Details and requirements required for plugin integration
• Prerequisites
• Configuration steps

5. Common issues and fixes

Introduction

Zoho people offers integration between the biometric device and Zoho People and thus helps manage the attendance data.

Types of integration

Biometric devices can be integrated with Zoho People in two ways:

• API
• Plugin integration

Integration using API method

Using this API, the data can directly be pushed to Zoho People from the biometric device. Configuration of the same has to be done by the biometric vendor.

Please note that the Attendance Bulk Import API has threshold limitations of 10 requests/minute and a Lock period of 5 minutes.

Plugin integration

Another way of integrating the biometric device with Zoho People is through the configuration of a plugin using Tomcat server. The following are the prerequisites required to do the integration.

Integration can be done with any of these databases: MS SQL Server, MS Access and MY SQL. The data from the biometric should be automatically downloaded into the database. Query has to be generated in the following format to fetch data from the database. 

Date-Time format sample for EventTime and DownloadTime: yyyy-MM-dd HH:mm:ss (2020-12-29 15:30:06)

Software installation

The following software have to be installed if not already.

1. Java

• Go to https://java.com/en/download/manual.jsp
• Click next on every pop-up and then click on Install

2. Apache Tomcat

• Go to https://tomcat.apache.org/download-90.cgi
• Click on 32-bit/64-bit Windows Service Installer Under Core
• Change port numbers(HTTP/1.1 Connector port and Server shut down) during installation to avoid conflicts with any other applications
• Once Tomcat is installed, go to Start > View Local Services/Services > select Apache Tomcat 8.5 > Right Click > Properties > change Startup type to Automatic

If Apache Tomcat is already installed:

Go to Program Files/Program Files(x86) > Apache Software Foundation > Tomcat 8.5 > Conf folder > open server.xml file > note the connector port of the protocol HTTP/1.1

Mappings to be done before the integration:

UserID Mapping

The Employee ID in Zoho People and in the database must be the same. In case of mismatch between the ID, userID mapping has to be done.

• Go to Attendance > Settings > User ID mapping
• Select the Employee and then enter the Mapper ID (ID as in the database)

Import can also be done by clicking on the ellipsis icon and in the format as specified.

Shift Mapping

In case of multiple shifts, employees must be mapped to their corresponding shifts under Attendance > Shift Schedule > Employee Shift mapping.

War File Deployment

Once Tomcat is installed, copy the  ZAttendance.war file and paste it in the tomcat > webapps folder and a folder in the same name will be extracted.

Steps to configure the Plugin:

1. Go to the browser of the server system which has the database
2. Type localhost:tomcatportnumber/ZAttendance and press Enter
3. Click on Configure on the right top of the plugin page
4. Fill in the configuration details:

• Select the database type
• Connection Url:

If MS Access -> copy the sample path mentioned below the Connection URL and replace the path with the actual path of the database and replace '\' with '//' and at the end enter the access file name followed by its extension.

If MS SQL -> Open SQL Configuration Manager (C > Windows > SystemWOW64 > SQL configuration manager)

Select > SQL Server Network Configuration > Protocols >TCP/IP - Enable, then click on TCP/IP, pop-up will be opened, Click on IP address, scroll down at the end change the port number if it is 0, else copy the port number and paste it in connection url. At the end of the url after the port number, the database name has to be entered.

MS SQL - Default port number -1433

My SQL- Default port number - 3306

If localhost is called from some other system using ip:portno, then in the config.properties folder under Tomcat server update the IP address of the current system.

5. Username and password:

MSAcess - default username - Admin, no password

MSSQL- user name and password should be there. Also should not use windows authentication, need SQL authenticated login.

MY SQL - default username - root, no password

6. Delay Time:

If downloadDate column is present in the database - minimum of 2 mins can be set. Delay time refers to the time at which the timer should be running in the plugin page and it is the time till the sync happens from the Last Updated Time in the plugin page.

7. Query

Enter the corresponding query to fetch the details from the database. Sample queries will be available below the query editor. Query can be modified as required to fetch the data in the required format.

Sample Basic Queries

In the case of Dynamic table format, table names have to be given in this format: #DeviceLogs_%M_%Y# where the %M will be replaced by the current month and %Y with the current year automatically.

Scenario 1:

SQL database - Has IsCheckin column with String value:

select [Id] as employeeId, case [Checkin] when 'IN' then 1 when 'OUT' then 0 end as isCheckin ,[CheckedTime] as eventTime ,[DownloadDate] FROM [ZAttendance].[dbo].[Att-checkin] where DownloadDate>=$1 and DownloadDate<$2

Scenario 2:

No IsCheckin Column:

select [empid] as employeeId, '1' as isCheckin, [eventtime] as eventTime, [downloaddate] FROM [ZAttendance].[dbo].[Att_noCheck] where DownloadDate>=$1 and DownloadDate<$2 UNION select [empid] as employeeId, '0' as isCheckin, [eventtime] as eventTime, [downloaddate] FROM [ZAttendance].[dbo].[Att_noCheck] where DownloadDate>=$1 and DownloadDate<$2

8. Authentication

• Go to https://api-console.zoho.com/ from the Zoho People admin account browser
• Click Add Client > Self Client > Copy > Create > Copy Client ID and Client secret in the plugin page
• Click on Generate Code > Scope: ZohoPeople.Attendance.ALL > Time duration: 5 minutes > Give scope description > Click create > copy the code
• In the plugin configuration, paste the client ID and client secret and then click on 'Generate' next to Refresh token and then paste, client ID, client secret, code and select the DC. Click on Generate token to generate the Refresh token and paste the same Refresh token.

9. Timezone: Admin and all employees should be in the same time zone.
10. Sleep time:If downloadDate column is present in the database - minimum of 5 mins can be set. Sleep time refers to the periodicity at which the sync in the plugin configuration will happen.
11. If proxy is configured on the system then enter the proxy details.
12. Bookmark the plugin page for future reference.
13. After entering the details, save the configuration. Once the integration is tested, click on 'Resume Sync' button to start the automatic sync.

Testing the integration:

Manual pushing of the data has to be done by selecting the date range and then by clicking on fetch and upload for the data to be pushed to Attendance. Status code 200 refers to the successful sending of the data. In order to check the detailed response, open Tomcat > Logs > Current date Catalina file > see the response.

Configuration change:

If people domain is other than .com (.in or .eu), update the domain in config.properties in tomcat folder for both targetURL and acesstokenURL.

Common Issues and fixes

• Sync not happening - Need to check for the running of Tomcat under Services.
• Configuration page is empty and both 'Resume Sync' and 'Stop Sync' button available in the plugin page: mostly occurs in case of forced shutdown, need to enter the configuration details again.
• Java socket connection error - occurs in case of MS SQL database, check if SQL is running, the port number and if TCP/IP port is enabled in SQL configuration.
• Java heap space out of memory issue: mostly occurs in case of MS Access database. Go to Tomcat folders > bin folder > open tomcatw.exe > Java tab > Increase initial memory pool and maximum memory pool.
• Getting status 200 and entries not updated in People. Check logs for the current day under the logs folder in Tomcat, if the response is {"message":"To add entry in Attendance, log time for any of your jobs"}, then go to Timesheet > Setting > General Setting > disable Push Timer entries to the Attendance option. Either of the Attendance punches or the Timer entries can be pushed to Attendance.
• Skipped employee info: If the employee id is found inside SkippedEmployeeInfo in logs, the employee ID in Zoho and in the database are not the same and User ID mapping is not done.
• The time difference in Attendance for all employees - The time zone of the admin and all the employees should be the same and the time zone specified in the plugin configuration.
• Error 'Date range provided is more than 31 days' in logs, the data that is being pushed in a particular sync from the plugin has date range of more than 31 days as mentioned in the error. Only data of date range within a month is to be downloaded into the database.
• Login failed the exception in logs, username and password of the MS SQL database is incorrect in the plugin configuration.
• When the data is not synced for a few employees, check for the attendance data availability in the database. Only if the data is present in the database, it can be fetched and pushed to Zoho People Attendance.
• In the configuration page, error 'Error occurred, data count more than 20000 records', check as to why more than 20000 records were downloaded into the database at a particular time. In order to resolve the same, stop the sync by clicking the 'Stop Sync' button in the plugin page. Push the data manually by using manual sync for the dates for which it is needed by selecting the dates and with eventTime in the where condition instead of downloadDate. This will push the data as per the eventTime. Then change the query back to pushing the data based on downloadDate. Click on the 'Resume Sync' button.
• If Java is updated, Tomcat service would stop running which in turn would stop the sync. Refer this link.
• Unknown Host Exception - Unknown host exception - system not connected to the internet. people.zoho.com domain could have been blocked as well. host name: people.zoho.com and the port number: 8443 have to be whitelisted.
• When the sync happens, the current timer time in the plugin is to be updated in the Last Updated Time, if not, open the record file in the Tomcat folders. If found to be empty, insert {} and then click on Save.
• If javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException error found in logs, Authentication has failed, when trying to connect from tomcat server to java client. Refer this link. Follow the steps at 161 in stack overflow.

Steps to follow

1. Download sslhandshake certificate from https website in same browser(load https site ->on the top left corner there is lock icon->click on that ->go to details->copy to file->save it in some specific folder.
2. Go to cmd by running it as administrator , 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,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
• Type "yes"

Certificate will get added into cacerts. Once all above said is done, restart Tomcat.

For any queries related to biometric integration, please send an email to support@zohopeople.com

Here's a downloadable guide to biometric integration in Zoho People.