Attendance Reporting & CSV Export
Relevant source files
The following files were used as context for generating this wiki page:
The Attendance Reporting and Export module is a critical administrative feature of the Smart Absen system. It provides administrators with a comprehensive interface to view, analyze, and export employee attendance data. The system aggregates data from the attendance and employees tables, presenting it through a web interface and allowing for data extraction in various formats, including CSV, JSON, and Excel.
This module is primarily handled by the Flask backend (app.py) and rendered through the Jinja2 template admin_reports.html. It offers pre-defined reports (daily, weekly, monthly) and a custom report generator, making it a flexible tool for HR and management tasks.
Sources: app.py, templates/admin_reports.html, README.md:10-13
System Architecture
The reporting system follows a standard three-tier architecture. The frontend, built with HTML and JavaScript, makes requests to the Flask backend. The backend contains the business logic to query the MySQL database, process the data, and either render it in a template or format it for export.
This diagram illustrates the high-level data flow for generating a report.
Reporting Endpoints
The backend exposes several RESTful endpoints to generate and export reports. These are protected and require an admin session.
| Method | Endpoint | Description |
|---|---|---|
GET | /admin/reports | Renders the main reporting page. |
GET | /admin/reports/daily | Generates a JSON object for the daily attendance report. |
GET | /admin/reports/weekly | Generates a JSON object for the weekly attendance report. |
GET | /admin/reports/monthly | Generates a JSON object for the monthly attendance report. |
GET | /admin/reports/performance | Generates a JSON object for the employee performance report. |
GET | /admin/reports/overtime | Generates a JSON object for the overtime report. |
GET | /admin/reports/export | Exports report data in a specified format (CSV, JSON, Excel). |
Sources: app.py:2004-2287, static/openapi.yaml:220-252
Report Generation Flow
The following sequence diagram shows the interaction when an admin requests a daily report from the UI.
This flow is initiated by the generateDailyReport() JavaScript function in the frontend.
Sources: templates/admin_reports.html:606-621, app.py:2010-2046
Report Generation Logic
The core logic resides in app.py, where each report type has a dedicated function that queries the database and formats the result.
Daily Report (/admin/reports/daily)
This function fetches all employees and joins them with today's attendance records. It calculates statistics like total present, absent, and late employees.
# Source: app.py:2014-2022
# Query to get daily attendance data
query = """
SELECT e.name, e.bagian, a.jam_masuk, a.jam_pulang, a.status,
TIMESTAMPDIFF(HOUR, a.jam_masuk, a.jam_pulang) as total_jam
FROM employees e
LEFT JOIN attendance a ON e.id = a.employee_id AND DATE(a.tanggal) = %s
ORDER BY e.name
"""
result = db.execute_query(query, (today,))
Sources: app.py:2010-2046
Performance Report (/admin/reports/performance)
This report analyzes employee performance over the last 30 days. It calculates a skor_kinerja (performance score) based on attendance percentage, on-time percentage, and average work hours.
The performance score is calculated with the following logic:
- Attendance Score (40%):
(total_hadir / 30) * 40 - Punctuality Score (40%):
(tepat_waktu / total_hadir) * 40 - Work Hours Score (20%):
min(rata_jam_kerja / 8, 1) * 20
Sources: app.py:2167-2216
Overtime Report (/admin/reports/overtime)
This function identifies workdays where an employee's total_jam exceeds the NORMAL_WORK_HOURS (defined as 8). It provides a summary per employee and a detailed daily breakdown.
-- Source: app.py:2235-2252
-- Query to calculate overtime hours
SELECT e.name, e.bagian,
DATE(a.tanggal) as tanggal,
a.jam_masuk, a.jam_pulang,
TIMESTAMPDIFF(HOUR, a.jam_masuk, a.jam_pulang) as total_jam,
CASE
WHEN TIMESTAMPDIFF(HOUR, a.jam_masuk, a.jam_pulang) > 8
THEN TIMESTAMPDIFF(HOUR, a.jam_masuk, a.jam_pulang) - 8
ELSE 0
END as jam_lembur
FROM employees e
JOIN attendance a ON e.id = a.employee_id
WHERE DATE(a.tanggal) BETWEEN %s AND %s
Sources: app.py:2219-2285
Frontend Interface
The user interface for reporting is defined in templates/admin_reports.html. It provides a rich, interactive experience for admins.
Key UI Components
| Component | Description | Source File |
|---|---|---|
| Quick Stats | Displays high-level statistics like total employees and today's attendance rate. | templates/admin_reports.html:360-385 |
| Report Type Cards | Clickable cards for generating pre-defined reports (Daily, Weekly, Monthly, etc.). | templates/admin_reports.html:390-445 |
| Custom Report Generator | A form to generate reports for a custom date range, specific employees, and export format. | templates/admin_reports.html:450-496 |
| Quick Actions | Buttons for common tasks like exporting today's attendance or viewing charts. | templates/admin_reports.html:501-530 |
| Visual Analysis | A section with tabs for displaying charts related to attendance, performance, and trends. | templates/admin_reports.html:535-585 |
JavaScript Interactivity
The frontend uses vanilla JavaScript to handle user interactions and communicate with the backend API.
generate...Report()functions: These functions (e.g.,generateDailyReport) are triggered when a user clicks a report card. They usefetchto call the corresponding API endpoint and then use a display function (e.g.,displayReportData) to show the results in a modal.exportReport(): This function constructs the URL for the/admin/reports/exportendpoint with the correct parameters (type,format,start_date,end_date) and triggers a file download.display...Data()functions: These functions dynamically create HTML content (tables, stats) from the JSON data returned by the API and inject it into a Bootstrap modal for the user to view.
Sources: templates/admin_reports.html:591-1025
Data Export Functionality
The system supports exporting report data to CSV, JSON, and Excel formats through a single, versatile endpoint: /admin/reports/export.
Export Process Flow
The export process is determined by the type and format query parameters sent to the endpoint.
This diagram shows how the backend handles an export request, from parsing parameters to returning a downloadable file.
Sources: app.py:2288-2415
CSV Generation
For CSV exports, the application uses Python's built-in csv module with an io.StringIO buffer to create the file in memory before sending it as a response.
# Source: app.py:2385-2395
if format_type == 'csv':
import csv
from io import StringIO
output = StringIO()
writer = csv.DictWriter(output, fieldnames=result[0].keys() if result else [])
writer.writeheader()
writer.writerows(result)
response = make_response(output.getvalue())
response.headers['Content-Type'] = 'text/csv; charset=utf-8'
response.headers['Content-Disposition'] = f'attachment; filename="{filename}.csv"'
return response
Sources: app.py:2385-2395
Excel Generation
For Excel (.xlsx) exports, the system uses the pandas and openpyxl libraries. It converts the query result into a DataFrame, writes it to an in-memory BytesIO buffer, and then auto-adjusts column widths for better readability before sending the file.
Sources: app.py:2408-2439
Conclusion
The Attendance Reporting & CSV Export module is a robust and feature-rich component of the Smart Absen system. It effectively separates concerns between the backend data processing and the interactive frontend. By providing multiple report types and export formats, it empowers administrators to efficiently monitor and manage employee attendance. The use of dedicated API endpoints and dynamic JavaScript makes the user experience responsive and intuitive.