We analyze employee attendance according to ACS

I, like most people reading this text, in order to sit in a chair, I need to successfully authenticate myself in a corporate access control system (hereinafter ACS). In each company, this is done differently: with the help of electronic cards, biometrics, passwords, or the old fashioned way - with the entry of your record as a security guard in a paper journal. It would seem that the attendance data already has storage and arrives there in one format, the logic of calculating time is as simple as a day, but few people use this data. In most cases, neither an ordinary employee, nor even his superiors can often answer a number of simple but practical questions: how many hours have I been at my workplace? Did an employee Denisov already come to the office today? Did citizen Frolova return from dinner? At what time the day before yesterday employee Smirnov left work. In the article, I will tell you how to get answers to these questions and try to convince that recording time spent in the office is useful for employees. If it became interesting - kindly ask for a cat.

Being a developer and participating in integration IT projects on customer premises, I often had to be in their offices, going through the access control systems available there. But life did help me with a rather impressive number of back offices of companies in various fields of activity, size and wealth, which gives me the right to be at least a little objective. Most often, data on office traffic are not even created (at the iron level, authentication is performed according to the 'friend / foe' principle without event logging), sometimes the raw data still arrives on the internal server and the logs can be accessed by the guard, and even less often available to the management in a readable form, ordinary employees usually do not provide access to such information. What about your office?

Having studied the capital market of access control systems, talking with friends from this area, and personally looking at the running software in the box, it was decided to create an application for time tracking of employees.

Of the domestic solutions in this area, it is worth mentioning the Sphinx software and their work time tracking module : Also Epicurus software :

What I wanted to implement:

* client-server cross-browser web-application that does not require separate installation on each user device;
* A simple and intuitive interface with adaptive layout, so that it was convenient to watch on a smartphone, tablet, PC;
* the ability to upload data to a .xls file (for the needs of accounting or a deeper third-party analysis);
* The application works both inside a closed local network and in an external network (collecting data from several disparate offices);
* scalability (in a config, geographically dispersed premises can be combined into one abstract corporate room);
* The ability to integrate with a corporate website.

Immediately make a reservation, this solution is suitable for access control systems with event logging, because it is such systems that write logs to the database (hereinafter referred to as the database) and have a network interface for working with their database.

The tasks that this web application does not solve:

1) improving the quality of identification / authentication of passing traffic. The application analyzes the data, but does not interfere with native authentication algorithms.
2) control of useful working time. It is incorrect to identify the time spent in the office as working hours. The application captures the value of time spent inside a certain room, but does not control what employees devote this time to: whether it is tea drinking, smoke breaks, reading a habr or the work itself;
3) non-compliance with the rules of access control, for example, when a successfully authenticated person passes someone else through the door / turnstile. The application cannot significantly affect the individual in this regard, although the algorithm for calculating the time has a logic that makes such actions unprofitable for the intruder (if he logged in twice, then the countdown will be taken from the moment of the second entry). Usually this problem is well solved by the ACS manufacturers themselves, the general name of the function is anti-passback .

Design and implementation

Having looked at the database structure of the most popular manufacturers of access control systems, it became clear that it is similar for everyone, the differences are only in the names of tables, columns and data format. The main I / O table necessarily has the following fields:

userID / * employee ID * /
checkTIME / * date and time of operation, difference in date recording formats between manufacturers * /
checkTYPE / * type of operation: success IN, success OUT and invalid authentication attempt (failed) * /
scanerID / * unique terminal serial number assigned by the manufacturer * /

These records will be used to calculate the time. The remaining database tables are more likely to have an auxiliary function.
The task of the application is to access the I / O database, get the necessary data from it, process it and display the result on the html page upon request. We integrate this data page into the corporate website and give access via a beautiful external link, like mycompany.com/time , without forgetting to implement password authorization to access the page.

Reflections on what useful information can be obtained from raw data and how to display the final result led me to three report pages (screenshots are clickable, only device frames are drawn directly from the demonstration server, inside the test database data).

1. Personal report for the day

In tabular form, you can see the statistics of inputs / outputs of a specific employee at the moment or for a specific date. The summary line shows the total office time and status (in / out).

2. A daily report throughout the office

In tabular form, you can see the current time spent in the office of each employee at the moment or on a specific date indicating the current status (in / out).

3. Monthly report for the entire office

In tabular form, you can see the total time spent in the office of each employee for a particular month. The final value can be expanded by clicking on it and get a decryption by day. Data for all employees for a specific month can be uploaded to a .xls file.

Why is this all or who needs a traffic analysis?

to employees:

- A convenient and accurate way to display your data about the time spent inside the office;
- the ability to monitor the presence / absence of employees in the office (including remotely!);
- simplifies own calculation of worked hours;

management

- real-time monitoring (including remotely) of office attendance, the history of employees for a specific date / month is also available;
- if employees do not have a flexible schedule, then the application can help eradicate absenteeism;
- if the wages of employees are tied to the actual time worked, then it is possible to save payroll by revealing hidden absenteeism (for example, you are late in the morning, delayed at dinner, left early in the evening, didn’t show up at all on some days, and your colleagues covered you up) ;

accounting and human resources

- constant and accurate automated accounting of the time spent in the office of each employee;
- excludes the human factor in the calculation;
- the ability to upload data on the hours worked by all employees to Excel spreadsheets (convenient when filling out the time sheet);
- quick reconciliation of the employee’s actual hours with the norm;

protection:

- you can quickly see how many people are still in the office in the evening / night / morning;
- in case of disputes and conflicts, there is always the opportunity to access the input / output data of each employee

What else could be implemented?

- differentiation of the rights of users to view the final data (manager / accounting / security / ordinary employee);
- sending e-mail notifications when certain conditions are triggered, for example, the head of the department came to the office -> a notification arrives at corporate mail -> documents for signature can be attributed; the employee was late (arrived later than the set time) -> a notification arrives at the post office head of the department.

I would be grateful for any additional ideas in the comments, because it was for this reason that I wrote an article - to get my portion of criticism and new ideas.

Total

I tested the hypothesis about the usefulness of the developed solution at other companies. I walked around four dozen offices that were interesting to me, demonstrated the operation of the application, in some places on the spot we tested it on 'combat' data, received good feedback, took into account the comments, received the first customers. My practice shows that most of the implementations are in offices with biometric ACS.

A good article on the basics of biometrics is here , and you can learn more about modern biometric identification methods here .