Best sellers of the month for the admin dashboard
Java · Spring Boot · beginner · modification
Adds the best-sellers card to the admin dashboard's monthly report. A new `BestSellerController` takes the month, turns it into a UTC `[from, to)` range and asks `OrderLineRepository` for the top ten. The repository gains one JPQL query that joins order lines to their order and product, keeps paid orders inside the range, groups by product and builds `BestSellerRow` records with the units and revenue per product, so the aggregation and the top-ten cut both happen in the database. `BestSellerRow` is a plain record the dashboard renders as a table.
Shop backend on Spring Boot 3.3 (Java 21), Spring Data JPA (Hibernate 6) on PostgreSQL. Existing and unchanged: `SecurityConfig` restricts `/api/admin/**` to `ROLE_ADMIN`; the entities `Product` (`id`, `name`), `CustomerOrder` (`id`, `status` as an `OrderStatus` enum `PENDING`/`PAID`/`CANCELLED`, `placedAt` a non-null `Instant`) and `OrderLine` (`order` and `product`, both required many-to-one, `quantity` an `int` from 1 to 999, `lineTotal` a non-null non-negative `numeric(12,2)` set at checkout as quantity times unit price after line discounts). Products are never deleted. The shop sells in one currency (EUR) and refunds are reported elsewhere. A `List`-returning repository query with a `Pageable` applies that page's limit in the SQL and issues no count query. A month has at most a few hundred thousand order lines.
Requirements
- `GET /api/admin/reports/best-sellers?month=yyyy-MM` returns the month's 10 best-selling products ranked by revenue, highest revenue first. A product's revenue is the sum of `lineTotal` over its order lines in that month. Each element is a `BestSellerRow` (productId, name, unitsSold, revenue), where `unitsSold` is the sum of `quantity` over the same lines; the response is a JSON array. Products that tie on revenue may come back in any order relative to each other.
- Count only order lines of orders with status `PAID` whose `placedAt` falls inside the calendar month in UTC (from the first day 00:00 UTC, inclusive, to the first day of the next month 00:00 UTC, exclusive). A month with fewer than 10 such products returns all of them; a month with none returns an empty array.
- `month` is required and must be a month from 2015-01 (the shop's launch) to 2099-12; a missing, malformed or out-of-range value gets 400.
Files touched
- src/main/java/com/acme/shop/orders/OrderLineRepository.java
- src/main/java/com/acme/shop/reports/BestSellerRow.java
- src/main/java/com/acme/shop/reports/BestSellerController.java
--- src/main/java/com/acme/shop/orders/OrderLineRepository.java
package com.acme.shop.orders;
+import com.acme.shop.reports.BestSellerRow;
+import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.data.repository.query.Param;
+import java.time.Instant;
import java.util.List;
List<OrderLine> findByOrderId(Long orderId);
+
+ @Query("""
+ select new com.acme.shop.reports.BestSellerRow(
+ p.id, p.name, sum(l.quantity), sum(l.lineTotal))
+ from OrderLine l join l.order o join l.product p
+ where o.status = com.acme.shop.orders.OrderStatus.PAID
+ and o.placedAt >= :from and o.placedAt < :to
+ group by p.id, p.name
+ order by sum(l.quantity) desc
+ """)
+ List<BestSellerRow> bestSellers(@Param("from") Instant from, @Param("to") Instant to, Pageable page);
}
--- src/main/java/com/acme/shop/reports/BestSellerRow.java
+package com.acme.shop.reports;
+
+import java.math.BigDecimal;