ReportService.java

package com.rbctcsworld.ecommerce.report;

import com.rbctcsworld.ecommerce.common.Csv;
import com.rbctcsworld.ecommerce.common.exception.BusinessRuleException;
import com.rbctcsworld.ecommerce.common.exception.NotFoundException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.Clock;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.YearMonth;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;

/**
 * Store reports for the admin. Dates are inclusive days (from 00:00 to the end of "to");
 * without dates a report covers the last 30 days up to today. At most 366 days per report.
 *
 * Money received = orders that were paid and not cancelled or returned (PAID, SHIPPED, DELIVERED,
 * RETURN_REQUESTED), counted on the day they were paid.
 */
@Service
@Transactional(readOnly = true)
public class ReportService {

    public static final List<String> NAMES = List.of("sales", "top-products", "orders-by-status", "returns",
            "coupons", "low-stock", "inventory", "stock-by-warehouse");
    public static final int MAX_DAYS = 366;
    static final String PAID = "'PAID', 'SHIPPED', 'DELIVERED', 'RETURN_REQUESTED'";
    static final List<String> STATUS_ORDER = List.of("PLACED", "PAID", "SHIPPED", "DELIVERED", "RETURN_REQUESTED",
            "RETURNED", "CANCELLED");

    private final JdbcTemplate jdbc;
    private final Clock clock;

    public ReportService(JdbcTemplate jdbc, Clock clock) {
        this.jdbc = jdbc;
        this.clock = clock;
    }

    /** Options a report may use; null = its default. */
    public record Options(LocalDate from, LocalDate to, String groupBy, Integer limit, Integer threshold) {
        public static Options none() {
            return new Options(null, null, null, null, null);
        }
    }

    public ReportTable run(String name, Options o) {
        String n = name == null ? "" : name.trim().toLowerCase(Locale.ROOT);
        return switch (n) {
            case "sales" -> sales(range(o), o.groupBy());
            case "top-products" -> topProducts(range(o), o.limit());
            case "orders-by-status" -> ordersByStatus(range(o));
            case "returns" -> returns(range(o));
            case "coupons" -> coupons(range(o));
            case "low-stock" -> lowStock(o.threshold());
            case "inventory" -> inventory();
            case "stock-by-warehouse" -> stockByWarehouse();
            default -> throw new NotFoundException("No such report: " + name + " (reports: " + NAMES + ")");
        };
    }

    /** The report as CSV: header, rows, and a "total" line when it has one. */
    public String csv(ReportTable t) {
        StringBuilder s = new StringBuilder(Csv.line(t.columns()));
        t.rows().forEach(r -> s.append(Csv.line(r)));
        if (t.totals() != null) s.append(Csv.line(t.totals()));
        return s.toString();
    }

    // ------------------------------------------------------------------ the date range

    record Range(LocalDate from, LocalDate to) {
        LocalDateTime start() { return from.atStartOfDay(); }
        LocalDateTime end() { return to.plusDays(1).atStartOfDay(); }
    }

    Range range(Options o) {
        LocalDate today = LocalDate.now(clock);
        LocalDate to = o.to() == null ? today : o.to();
        LocalDate from = o.from() == null ? to.minusDays(29) : o.from();
        if (from.isAfter(to)) throw new BusinessRuleException("from must be on or before to");
        if (ChronoUnit.DAYS.between(from, to) + 1 > MAX_DAYS) {
            throw new BusinessRuleException("a report covers at most " + MAX_DAYS + " days");
        }
        return new Range(from, to);
    }

    // ------------------------------------------------------------------ the reports

    /** Money received per day or per month, every period listed (also the quiet ones). */
    ReportTable sales(Range r, String groupBy) {
        String g = groupBy == null || groupBy.isBlank() ? "day" : groupBy.trim().toLowerCase(Locale.ROOT);
        if (!g.equals("day") && !g.equals("month")) throw new BusinessRuleException("groupBy must be day or month");
        Map<String, BigDecimal[]> periods = new LinkedHashMap<>();       // orders, items, subtotal, discount, shipping, tax, total
        if (g.equals("day")) {
            for (LocalDate d = r.from(); !d.isAfter(r.to()); d = d.plusDays(1)) periods.put(d.toString(), zeros(7));
        } else {
            for (YearMonth m = YearMonth.from(r.from()); !m.isAfter(YearMonth.from(r.to())); m = m.plusMonths(1)) {
                periods.put(m.toString(), zeros(7));
            }
        }
        jdbc.query("select paid_at, total_items, subtotal, discount, shipping_fee, tax, total from orders "
                + "where paid_at >= ? and paid_at < ? and status in (" + PAID + ")", rs -> {
            LocalDate day = rs.getTimestamp("paid_at").toLocalDateTime().toLocalDate();
            BigDecimal[] p = periods.get(g.equals("day") ? day.toString() : YearMonth.from(day).toString());
            p[0] = p[0].add(BigDecimal.ONE);
            p[1] = p[1].add(BigDecimal.valueOf(rs.getInt("total_items")));
            p[2] = p[2].add(rs.getBigDecimal("subtotal"));
            p[3] = p[3].add(rs.getBigDecimal("discount"));
            p[4] = p[4].add(rs.getBigDecimal("shipping_fee"));
            p[5] = p[5].add(rs.getBigDecimal("tax"));
            p[6] = p[6].add(rs.getBigDecimal("total"));
        }, Timestamp.valueOf(r.start()), Timestamp.valueOf(r.end()));
        List<List<Object>> rows = new ArrayList<>();
        BigDecimal[] sum = zeros(7);
        periods.forEach((period, p) -> {
            rows.add(row(period, p));
            for (int i = 0; i < 7; i++) sum[i] = sum[i].add(p[i]);
        });
        return new ReportTable("sales", r.from(), r.to(),
                List.of(g, "orders", "items", "subtotal", "discount", "shipping", "tax", "total"), rows, row("total", sum));
    }

    /** Best sellers by units, with the money they brought in (orders paid in the range). */
    ReportTable topProducts(Range r, Integer limit) {
        int n = limit == null ? 10 : limit;
        if (n < 1 || n > 100) throw new BusinessRuleException("limit must be between 1 and 100");
        List<List<Object>> rows = jdbc.query("""
                select oi.product_id, oi.sku, max(oi.product_name) as name, sum(oi.quantity) as units,
                       count(distinct oi.order_id) as orders, sum(oi.line_total) as revenue
                from order_items oi join orders o on o.id = oi.order_id
                where o.paid_at >= ? and o.paid_at < ? and o.status in (%s)
                group by oi.product_id, oi.sku
                order by units desc, revenue desc, oi.product_id
                limit %d""".formatted(PAID, n),
                (rs, i) -> List.<Object>of(rs.getString("sku"), rs.getString("name"), rs.getLong("units"),
                        rs.getLong("orders"), money(rs.getBigDecimal("revenue"))),
                Timestamp.valueOf(r.start()), Timestamp.valueOf(r.end()));
        return new ReportTable("top-products", r.from(), r.to(), List.of("sku", "name", "units", "orders", "revenue"),
                rows, totals(rows, 5, 2, 4));
    }

    /** Orders placed in the range, by where they are now (in lifecycle order). */
    ReportTable ordersByStatus(Range r) {
        Map<String, List<Object>> byStatus = new LinkedHashMap<>();
        STATUS_ORDER.forEach(s -> byStatus.put(s, null));
        jdbc.query("select status, count(*) as n, coalesce(sum(total), 0) as amount from orders "
                + "where created_at >= ? and created_at < ? group by status", rs -> {
            byStatus.put(rs.getString("status"), List.of(rs.getString("status"), rs.getLong("n"), money(rs.getBigDecimal("amount"))));
        }, Timestamp.valueOf(r.start()), Timestamp.valueOf(r.end()));
        List<List<Object>> rows = new ArrayList<>();
        byStatus.forEach((s, row) -> rows.add(row == null ? List.of(s, 0L, new BigDecimal("0.00")) : row));
        return new ReportTable("orders-by-status", r.from(), r.to(), List.of("status", "orders", "amount"), rows,
                totals(rows, 3, 1, 2));
    }

    /** Return requests made in the range: why, what was decided, how much was refunded, how many restocked. */
    ReportTable returns(Range r) {
        List<List<Object>> rows = jdbc.query("""
                select reason, status, count(*) as n, coalesce(sum(refund_amount), 0) as refunded,
                       sum(case when restocked then 1 else 0 end) as restocked
                from return_requests where created_at >= ? and created_at < ?
                group by reason, status order by reason, status""",
                (rs, i) -> List.<Object>of(rs.getString("reason"), rs.getString("status"), rs.getLong("n"),
                        money(rs.getBigDecimal("refunded")), rs.getLong("restocked")),
                Timestamp.valueOf(r.start()), Timestamp.valueOf(r.end()));
        return new ReportTable("returns", r.from(), r.to(), List.of("reason", "status", "returns", "refunded", "restocked"),
                rows, totals(rows, 5, 2, 3, 4));
    }

    /** Every coupon with what it did in the range (cancelled orders do not count). */
    ReportTable coupons(Range r) {
        List<List<Object>> rows = jdbc.query("""
                select c.code, c.type, c.discount_value, c.active, count(o.id) as uses,
                       coalesce(sum(o.discount), 0) as discount_given, coalesce(sum(o.total), 0) as order_total
                from coupons c left join orders o on o.coupon_code = c.code
                     and o.created_at >= ? and o.created_at < ? and o.status <> 'CANCELLED'
                group by c.code, c.type, c.discount_value, c.active
                order by uses desc, c.code""",
                (rs, i) -> List.<Object>of(rs.getString("code"), rs.getString("type"), money(rs.getBigDecimal("discount_value")),
                        rs.getBoolean("active") ? "ACTIVE" : "DISABLED", rs.getLong("uses"),
                        money(rs.getBigDecimal("discount_given")), money(rs.getBigDecimal("order_total"))),
                Timestamp.valueOf(r.start()), Timestamp.valueOf(r.end()));
        return new ReportTable("coupons", r.from(), r.to(),
                List.of("code", "type", "value", "state", "uses", "discount", "orderTotal"), rows, totals(rows, 7, 4, 5, 6));
    }

    /** Products on sale with this many or fewer in stock (default 5), emptiest first. A snapshot: no dates. */
    ReportTable lowStock(Integer threshold) {
        int t = threshold == null ? 5 : threshold;
        if (t < 0 || t > 1000) throw new BusinessRuleException("threshold must be between 0 and 1000");
        List<List<Object>> rows = jdbc.query("""
                select sku, name, category, stock, price from products
                where active = true and stock <= ? order by stock, id""",
                (rs, i) -> Arrays.<Object>asList(rs.getString("sku"), rs.getString("name"), rs.getString("category"),
                        rs.getInt("stock"), money(rs.getBigDecimal("price"))), t);
        return new ReportTable("low-stock", null, null, List.of("sku", "name", "category", "stock", "price"), rows, null);
    }

    /** What the stock on the shelves is worth, per category (products on sale). A snapshot: no dates. */
    ReportTable inventory() {
        List<List<Object>> rows = jdbc.query("""
                select coalesce(category, '(none)') as cat, count(*) as product_count, sum(stock) as unit_count,
                       sum(price * stock) as stock_value
                from products where active = true
                group by coalesce(category, '(none)') order by stock_value desc, cat""",
                (rs, i) -> List.<Object>of(rs.getString("cat"), rs.getLong("product_count"), rs.getLong("unit_count"),
                        money(rs.getBigDecimal("stock_value"))));
        return new ReportTable("inventory", null, null, List.of("category", "products", "units", "value"), rows,
                totals(rows, 4, 1, 2, 3));
    }

    /** Where the stock is: units and their value per warehouse (products on sale). A snapshot: no dates. */
    ReportTable stockByWarehouse() {
        List<List<Object>> rows = jdbc.query("""
                select w.code, w.city, w.state, w.active, coalesce(s.product_count, 0) as product_count,
                       coalesce(s.unit_count, 0) as unit_count, coalesce(s.stock_value, 0) as stock_value
                from warehouses w left join (
                    select ws.warehouse_id, count(case when ws.quantity > 0 then 1 end) as product_count,
                           sum(ws.quantity) as unit_count, sum(ws.quantity * p.price) as stock_value
                    from warehouse_stock ws join products p on p.id = ws.product_id
                    where p.active = true group by ws.warehouse_id) s on s.warehouse_id = w.id
                order by w.priority, w.id""",
                (rs, i) -> List.<Object>of(rs.getString("code"), rs.getString("city"), rs.getString("state"),
                        rs.getBoolean("active") ? "ACTIVE" : "DISABLED", rs.getLong("product_count"),
                        rs.getLong("unit_count"), money(rs.getBigDecimal("stock_value"))));
        return new ReportTable("stock-by-warehouse", null, null,
                List.of("warehouse", "city", "usState", "state", "products", "units", "value"), rows, totals(rows, 7, 5, 6));
    }

    // ------------------------------------------------------------------ helpers

    /** Money always with 2 decimals (databases differ in the scale of SUM and COALESCE). */
    static BigDecimal money(BigDecimal v) {
        return (v == null ? BigDecimal.ZERO : v).setScale(2, java.math.RoundingMode.HALF_UP);
    }

    private static BigDecimal[] zeros(int n) {
        BigDecimal[] a = new BigDecimal[n];
        Arrays.fill(a, BigDecimal.ZERO);
        return a;
    }

    /** Label + the counts (orders, items) as whole numbers + the money columns with 2 decimals. */
    private static List<Object> row(String label, BigDecimal[] p) {
        List<Object> row = new ArrayList<>();
        row.add(label);
        row.add(p[0].longValue());
        row.add(p[1].longValue());
        for (int i = 2; i < p.length; i++) row.add(p[i].setScale(2, java.math.RoundingMode.HALF_UP));
        return row;
    }

    /**
     * The totals line: "total" in the first column, the sum of the given columns, null elsewhere.
     * width = number of columns; sums of whole numbers stay whole, money keeps 2 decimals.
     */
    static List<Object> totals(List<List<Object>> rows, int width, int... summed) {
        List<Object> t = new ArrayList<>(java.util.Collections.nCopies(width, null));
        t.set(0, "total");
        for (int c : summed) {
            boolean money = rows.stream().anyMatch(r -> r.get(c) instanceof BigDecimal);
            BigDecimal sum = BigDecimal.ZERO;
            for (List<Object> r : rows) sum = sum.add(new BigDecimal(r.get(c).toString()));
            t.set(c, money ? sum.setScale(2, java.math.RoundingMode.HALF_UP) : (Object) sum.longValue());
        }
        return t;
    }
}




















































Active mutators

CONDITIONALS_BOUNDARY
EMPTY_RETURNS
FALSE_RETURNS
INCREMENTS
INVERT_NEGS
MATH
NULL_RETURNS
PRIMITIVE_RETURNS
REMOVE_CONDITIONALS_EQUAL_ELSE
REMOVE_CONDITIONALS_ORDER_ELSE
TRUE_RETURNS
VOID_METHOD_CALLS

Tests examined

com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:couponsWithTheirUseExcludingCancelledOrders()] (12 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:everyReportNameRuns()] (13 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:salesPerDayCountOnlyMoneyReceivedOnThePaymentDayAndListQuietDays()] (3 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:salesPerMonthAndTheDefaultRangeIsTheLast30Days()] (13 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:theRangeIsCheckedAndOneDayIsAllowed()] (22 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:inventoryValuePerCategory()] (4 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:csvHasTheHeaderTheRowsAndTheTotals()] (4 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:lowStockIsASnapshotOfProductsOnSale()] (4 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:ordersByStatusInLifecycleOrderWithEmptyStatusesListed()] (8 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:stockByWarehouseShowsWhereTheUnitsAndTheirValueAre()] (20 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:returnsByReasonAndDecision()] (12 ms)
com.rbctcsworld.ecommerce.report.ReportServiceTest.[engine:junit-jupiter]/[class:com.rbctcsworld.ecommerce.report.ReportServiceTest]/[method:topProductsByUnitsThenMoney()] (17 ms)

Report generated by PIT 1.30.0 support

1		package com.rbctcsworld.ecommerce.report;
2
3		import com.rbctcsworld.ecommerce.common.Csv;
4		import com.rbctcsworld.ecommerce.common.exception.BusinessRuleException;
5		import com.rbctcsworld.ecommerce.common.exception.NotFoundException;
6		import org.springframework.jdbc.core.JdbcTemplate;
7		import org.springframework.stereotype.Service;
8		import org.springframework.transaction.annotation.Transactional;
9
10		import java.math.BigDecimal;
11		import java.sql.Timestamp;
12		import java.time.Clock;
13		import java.time.LocalDate;
14		import java.time.LocalDateTime;
15		import java.time.YearMonth;
16		import java.time.temporal.ChronoUnit;
17		import java.util.ArrayList;
18		import java.util.Arrays;
19		import java.util.LinkedHashMap;
20		import java.util.List;
21		import java.util.Locale;
22		import java.util.Map;
23
24		/**
25		* Store reports for the admin. Dates are inclusive days (from 00:00 to the end of "to");
26		* without dates a report covers the last 30 days up to today. At most 366 days per report.
27		*
28		* Money received = orders that were paid and not cancelled or returned (PAID, SHIPPED, DELIVERED,
29		* RETURN_REQUESTED), counted on the day they were paid.
30		*/
31		@Service
32		@Transactional(readOnly = true)
33		public class ReportService {
34
35		public static final List<String> NAMES = List.of("sales", "top-products", "orders-by-status", "returns",
36		"coupons", "low-stock", "inventory", "stock-by-warehouse");
37		public static final int MAX_DAYS = 366;
38		static final String PAID = "'PAID', 'SHIPPED', 'DELIVERED', 'RETURN_REQUESTED'";
39		static final List<String> STATUS_ORDER = List.of("PLACED", "PAID", "SHIPPED", "DELIVERED", "RETURN_REQUESTED",
40		"RETURNED", "CANCELLED");

ReportService.java

Mutations

Active mutators

Tests examined