Native SQL reports#

@Component
@CrudMapping("/v1/reports/user-sales")
public class UserSalesReportHandler implements NativeQueryHandler {

    @Autowired private AuthProvider auth;

    @Override
    public int aclCheck(FilterCriteria criteria, HandlerContext ctx) {
        return userProvider.hasRole("REPORT_VIEW") ? 0 : -1;
    }

    @Override
    public void preProcess(FilterCriteria criteria, HandlerContext ctx) {
        if (criteria.getSimpleCriteria().get("since") == null) {
            criteria.addCriteria("since", Instant.now().minus(30, ChronoUnit.DAYS).toString());
        }
    }

    @Override
    public NativeQuery getQuery(FilterCriteria criteria, HandlerContext ctx) {
        String sql = """
            SELECT u.id, u.name, SUM(o.amount) AS total
            FROM users u JOIN orders o ON o.user_id = u.id
            WHERE o.created_at >= ?
            GROUP BY u.id, u.name
        """;
        NativeQuery q = new NativeQuery(sql, criteria.getSimpleCriteria().get("since"));
        q.setCountQuery("""
            SELECT COUNT(DISTINCT u.id)
            FROM users u JOIN orders o ON o.user_id = u.id
            WHERE o.created_at >= ?
        """);
        q.setFetchSize(500);
        return q;
    }

    @Override
    public Tuple onQueryResult(Tuple tuple, Action action) {
        BigDecimal total = (BigDecimal) tuple.get("total");
        if (total != null) tuple.set("total", total.setScale(2, RoundingMode.HALF_UP));
        return tuple;
    }
}