| | |
|---|
| | | 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(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({ |
|---|
| | |
|---|
| | | " 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", |
|---|
| | | " 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" |
|---|
