update

liujie

2026-09-11 4f6f15b818704079ff2e4e4719b798f48d68a335

 backend/src/main/java/com/tourguide/modules/dashboard/mapper/DashboardMapper.java

@@ -8,9 +8,10 @@
import java.util.List;

public interface DashboardMapper {
    @Select("SELECT COALESCE(SUM(GREATEST(COALESCE(payment_amount, amount, 0) - " +
            "COALESCE(refunded_amount, CASE WHEN status = 'REFUNDED' THEN refund_amount ELSE 0 END, 0), 0)), 0) " +
            "FROM tour_order WHERE deleted = 0 AND payment_time IS NOT NULL")
    @Select("SELECT COALESCE(SUM(COALESCE(payment_amount, amount, 0)), 0) " +
            "FROM tour_order WHERE deleted = 0 AND payment_time IS NOT NULL " +
            "AND (refund_status IS NULL OR refund_status IN ('NONE', 'REFUND_FAILED')) " +
            "AND status <> 'REFUNDED'")
    BigDecimal selectTotalRevenue();

    @Select({
@@ -19,9 +20,10 @@
            " UNION ALL SELECT day_value + INTERVAL 1 DAY FROM days WHERE day_value < CURDATE()",
            ")",
            "SELECT DATE_FORMAT(days.day_value, '%m-%d') AS date_label,",
            " COALESCE(SUM(CASE WHEN orders.payment_time IS NOT NULL THEN GREATEST(",
            " COALESCE(orders.payment_amount, orders.amount, 0) - COALESCE(orders.refunded_amount,",
            " CASE WHEN orders.status = 'REFUNDED' THEN orders.refund_amount ELSE 0 END, 0), 0) ELSE 0 END), 0) AS revenue",
            " COALESCE(SUM(CASE WHEN orders.payment_time IS NOT NULL",
            " AND (orders.refund_status IS NULL OR orders.refund_status IN ('NONE', 'REFUND_FAILED'))",
            " AND orders.status <> 'REFUNDED' THEN COALESCE(orders.payment_amount, orders.amount, 0)",
            " ELSE 0 END), 0) AS revenue",
            "FROM days LEFT JOIN tour_order orders",
            " ON orders.deleted = 0 AND DATE(COALESCE(orders.payment_time, orders.placed_at, orders.created_at)) = days.day_value",
            "GROUP BY days.day_value ORDER BY days.day_value"