Timesheet CSV export per pay period
Python · Django · intermediate · modification
Payroll currently copies hours out of the admin by hand twice a month. This adds a CSV download per pay period, with one row per employee: their employee number, total minutes and how many entries make it up. It filters entries by the period's dates, and the aggregation runs in the database with a single `values()` / `annotate()` query. Employee numbers are digits only, so nothing in the file can be read as a spreadsheet formula. Staff only. Tried it on a local period with a few entries spread over the fortnight; the totals matched the admin.
Django 5.1 HR app on PostgreSQL, `USE_TZ = True`, `TIME_ZONE = "Europe/Berlin"`, `LOGIN_URL` set. Existing models in `payroll/models.py`: `Employee` with `employee_number` (unique, always 6 digits, e.g. `000017`); `PayPeriod` with `start_date` and `end_date` (`DateField`s, a check constraint keeps `end_date >= start_date`; HR creates them, usually the 1st-15th and the 16th to the end of the month); `TimeEntry` with `employee` (FK to `Employee`, `related_name="time_entries"`), `clocked_at` (`DateTimeField`, the clock-in time, stored aware) and `minutes` (`PositiveIntegerField`). An entry is created at clock-out, so `minutes` is always set. `payroll/urls.py` is already included under `payroll/` in the project URLconf, and `views.period_list` exists. Payroll imports the file into its payroll software, which reads every column as text, so leading zeros in `employee_number` are kept. Shifts run around the clock, including overnight shifts that start late in the evening.
Requirements
- `GET /payroll/periods/<id>/timesheet.csv` downloads the timesheet of one pay period as CSV: a header row `employee_number,total_minutes,entries`, then one row per employee with at least one time entry in the period, ordered by `employee_number`. `total_minutes` is the sum of `minutes` over that employee's entries in the period, `entries` is how many there are.
- A pay period runs from its `start_date` to its `end_date`, both days included, as calendar days in the company's time zone (`Europe/Berlin`, the project's `TIME_ZONE`). An entry belongs to the period when its `clocked_at` (the clock-in time) falls on one of those days, at any time of day; a shift that runs past midnight counts on the day it started.
- Staff only: anonymous users are sent to the login page, signed-in users without `is_staff` get 403 whatever the period id, and for staff an unknown period id gets 404.
Files touched
- payroll/urls.py
- payroll/exports.py
--- payroll/urls.py
from django.urls import path
-from . import views
+from . import exports, views
app_name = "payroll"
urlpatterns = [
path("periods/", views.period_list, name="period-list"),
+ path("periods/<int:pk>/timesheet.csv", exports.timesheet_csv, name="timesheet-csv"),
]
--- payroll/exports.py
+import csv
+
+from django.contrib.auth.decorators import login_required
+from django.core.exceptions import PermissionDenied
+from django.db.models import Count, Sum
+from django.http import HttpResponse
+from django.shortcuts import get_object_or_404
+from django.views.decorators.http import require_GET
+
+from .models import PayPeriod, TimeEntry
+
+
+@require_GET
+@login_required
+def timesheet_csv(request, pk):
+ if not request.user.is_staff:
+ raise PermissionDenied