Daily sales report for finance
Python · Django · intermediate · modification
Finance rebuilds a daily sales table from the order CSV export at every month-end; this gives them an endpoint instead. New `sales/reports.py` runs one aggregate query: paid orders in the month, grouped by `TruncDate("placed_at")`, which buckets by the active time zone (Berlin), as the `__year`/`__month` filter does. `orders` uses `Count("id", distinct=True)`, since the join to `lines` repeats an order once per line. The view is gated on `sales.view_order`, which the finance group already has, and validates `month` with a strict regex. Tried it on the dev database's seeded orders.
Django 5.1 on PostgreSQL 16, `USE_TZ = True`, `TIME_ZONE = "Europe/Berlin"`, and no middleware activates another time zone. Existing `sales/models.py`: `Order` has `customer` (FK to the user), `status` (`Order.Status`: `pending`, `paid`, `cancelled`) and `placed_at` (`DateTimeField`); `OrderLine` has `order` (FK, `related_name="lines"`), `product` (FK), `unit_price` (`DecimalField(max_digits=10, decimal_places=2)`, the net price charged for one unit, discounts already applied, at least 0.01) and `quantity` (`PositiveIntegerField`, at least 1). Checkout creates every order with at least one line, and lines never change after the order is placed. Shipping and tax are not part of sales revenue, and refunds are booked in finance's own ledger, not on orders. `sales/urls.py` is already included at `sales/` in the project's `urls.py`.
Requirements
- Add `GET /sales/reports/daily/?month=YYYY-MM` for the finance team: one row per calendar day of that month that has at least one paid order, in date order, with `day`, `orders` (the number of paid orders placed that day), `units` (the total `quantity` across those orders' lines) and `revenue`. Days without a paid order are left out.
- A day's `revenue` is the sum of the line totals of its paid orders, where a line's total is `unit_price × quantity`.
- Only orders with status `paid` count; `pending` and `cancelled` orders are ignored. Days and months are calendar days in the shop's time zone, Europe/Berlin.
- Only users with the `sales.view_order` permission may open the report; anyone else, signed out included, gets 403. The report covers the years 2000-2099: a missing or malformed `month` (anything but an ASCII year `2000`-`2099`, a dash and a two-digit month `01`-`12`) returns 400 with `{"error": "month must be YYYY-MM"}`.
Files touched
- sales/reports.py
- sales/urls.py
--- sales/reports.py
+import re
+
+from django.contrib.auth.decorators import permission_required
+from django.db.models import Count, Sum
+from django.db.models.functions import TruncDate
+from django.http import JsonResponse
+from django.views.decorators.http import require_GET
+
+from .models import Order
+
+MONTH_RE = re.compile(r"(20[0-9]{2})-(0[1-9]|1[0-2])")
+
+
+def daily_sales(year, month):
+ return (
+ Order.objects.filter(
+ status=Order.Status.PAID,
+ placed_at__year=year,
+ placed_at__month=month,
+ )
+ .annotate(day=TruncDate("placed_at"))
+ .values("day")
+ .annotate(
+ orders=Count("id", distinct=True),
+ units=Sum("lines__quantity"),
+ revenue=Sum("lines__unit_price"),
+ )
+ .order_by("day")
+ )