update

liujie

2026-09-09 39f6fca28e3f78abe48f08e474ec011abb990b36

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

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

public interface DashboardMapper {
    @Select("SELECT COALESCE(SUM(COALESCE(payment_amount, amount)), 0) FROM tour_order " +
            "WHERE deleted = 0 AND status NOT IN ('PENDING_PAYMENT', 'CANCELLED', 'REFUNDED')")
    @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")
    BigDecimal selectTotalRevenue();

    @Select({
@@ -18,8 +19,9 @@
            " 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.status NOT IN ('PENDING_PAYMENT', 'CANCELLED', 'REFUNDED')",
            " THEN COALESCE(orders.payment_amount, orders.amount) ELSE 0 END), 0) AS revenue",
            " 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",
            "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"