Analyzing the attendance of employees according to the access control

I, like most of those reading this text, in order to sit in a work chair, I need to successfully authenticate myself in the corporate access control system (hereinafter referred to as ACS). In each company, this is done differently: with the help of electronic cards, biometrics, passwords, or in the old manner - with the security guard writing your notes in a paper journal. It would seem that the attendance data already has a repository and goes there in one format, the logic of counting time is simple, like 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 do I already have in the workplace? Did employee Denisov come to the office today? Has citizen Frolova returned from dinner? At what time the day before yesterday, Smirnov, an employee, left his job. In the article I will tell you how you can get answers to these questions and try to convince that the time spent in the office is useful for employees. If it became interesting - kindly ask for cat.

Being a developer and participating in integration IT projects on customers' areas, I often had to visit their offices, passing through the access control systems there. And life still shook me in a rather impressive number of back offices of companies of various fields of activity, size and wealth, which gives me the right to be at least a little objective. Most often, office attendance data is not even created (at the iron level, authentication is based on the 'own / someone else' principle without event logging), sometimes the raw data is still sent to the internal server and logs can be available to security, and even less often available to the management in a readable form, ordinary employees usually do not provide access to such information. And what about your office?

Having studied the capital's ACS market, having communicated with acquaintances from this sphere, and having personally looked at the software running in the box, it was decided to make my own application for recording employee time.
')
From domestic solutions in this area, it is worth mentioning Sphinx software and their time-tracking module:



Also software Epicurus :

What I wanted to implement:

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

Immediately make a reservation, this solution is suitable for access control systems with the event logging function, since It is these systems that write logs to the database (hereinafter the database) and have a network interface to work with their database.

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 the native authentication algorithms.
2) control of useful working time. It is wrong to identify the time spent in the office working time. The application captures the value of time spent inside a certain room, but does not control what employees devote this time: whether it is tea drinking, smoke breaks, reading habr or the work itself;
3) non-compliance with the rules of the access control mode, for example, when a successfully authenticated person passes someone else through the doors / turnstile. The application can not significantly influence the individual in this regard, although the algorithm for calculating the time contains logic that makes such actions unprofitable for the offender (if he entered the program twice, then the time 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

Looking at the structure of the database 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 the tables, columns and data format. The main input / output table must have the following fields:

userID / * employee ID ID * /
checkTIME / * date and time of operation, difference in date recording format between manufacturers * /
checkTYPE / * operation type: 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 rest of the database tables are rather auxiliary function.
The task of the application is to access the I / O database, retrieve the necessary data from it, process it and output the result to the html page upon request. We integrate this data page into a 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 (the screenshots are clickable, just from the demo server, only the device frames are drawn, inside the test database).

1. Personal report per day

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

2. Report per day throughout the office

In a table view, you can see the current time spent at the office of each employee at the moment or on a specific date with 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 total value can be expanded by clicking on it and get a transcript by day. Data for all employees for a particular month can be downloaded to the .xls file.

Why is it all or who needs attendance analysis?

employees:

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

leadership

- real-time monitoring (including remotely) of office attendance, also available history of employees for a specific date / month;
- if employees have a flexible schedule, the application can help eradicate absenteeism;
- if the staff’s salary is tied to the actual hours worked, then the payroll can be well saved by identifying hidden absenteeism (for example, it was late in the morning, was delayed at lunch, left earlier in the evening, didn’t come at one of the days, and your colleagues covered you) ;

accounting and personnel department

- constant and accurate automated accounting of the time spent in the office of each employee;
- eliminates the human factor in the calculation;
- the ability to upload data on the hours worked by all employees in Excel spreadsheets (conveniently when filling in the time sheet of the HRMS);
- 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 the case of controversial and conflict situations there is always the opportunity to refer to the data on the input / output of each employee

What else could be realized?

- delineation of user rights to view the summary data (head / 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 the corporate mail -> documents for signature can be referred to; the employee was late (he arrived later than the set time) -> a notice arrives at the corporate post office to the 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 share of criticism and new ideas.

Total

Hypothesis about the usefulness of the developed solution tested on other companies. He walked around four dozen offices of interest to me, demonstrated the operation of the application, tried it on the 'combat' data here and there, received good feedback, took into account the comments, got the first clients. My practice shows that most deployments occur in offices with biometric access control systems.

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