From a42f3d5c9950511e935683621edd0325d52aba6c Mon Sep 17 00:00:00 2001
From: longz <longzhaoz@qq.com>
Date: 星期三, 05 八月 2026 18:08:04 +0800
Subject: [PATCH] fix: optimize charging order summary query
---
ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml | 50 +++++++++++++++-----------------------------------
1 files changed, 15 insertions(+), 35 deletions(-)
diff --git a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml b/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
index 9fbf656..a4c6648 100644
--- a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
+++ b/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
@@ -2645,41 +2645,21 @@
<select id="chargingOrderBaseSummary" resultType="map">
select
- sum(aa.time) as `time`,
- sum(aa.income) as income,
- sum(aa.electronicMoney) as electronicMoney,
- sum(aa.serviceMoney) as serviceMoney,
- sum(aa.total) as total,
- sum(CASE WHEN aa.recharge_payment_type = 1 and aa.thirdNetAmount > 0 THEN aa.thirdNetAmount ELSE 0 END) as wechatIncome,
- sum(CASE WHEN aa.recharge_payment_type in (2, 3) and aa.thirdNetAmount > 0 THEN aa.thirdNetAmount ELSE 0 END) as alipayIncome,
- sum(CASE WHEN aa.is_whitelist = 1 THEN IFNULL(aa.electrovalence, 0) + IFNULL(aa.serviceCharge, 0) ELSE 0 END) as whitelistChargingIncome,
- sum(CASE WHEN aa.is_whitelist = 1 THEN IFNULL(aa.whitelist_electric_refund_amount, 0) + IFNULL(aa.whitelist_service_refund_amount, 0) ELSE 0 END) as whitelistRefund
- from (
- select
- b.id,
- CASE WHEN count(a.id) > 0 THEN (UNIX_TIMESTAMP(MAX(b.end_time)) - UNIX_TIMESTAMP(MAX(b.start_time))) ELSE 0 END as time,
- sum(a.charging_capacity) as total,
- sum(a.period_electric_price) as electronicMoney,
- sum(CASE
- WHEN b.order_source = 1 THEN a.period_service_price
- ELSE a.period_service_price * 0.8
- END) as serviceMoney,
- sum(a.period_electric_price + CASE WHEN b.order_source = 1 THEN a.period_service_price ELSE a.period_service_price * 0.8 END) as income,
- MAX(b.recharge_payment_type) as recharge_payment_type,
- MAX(b.is_whitelist) as is_whitelist,
- MAX(b.electrovalence) as electrovalence,
- MAX(b.service_charge) as serviceCharge,
- MAX(b.whitelist_electric_refund_amount) as whitelist_electric_refund_amount,
- MAX(b.whitelist_service_refund_amount) as whitelist_service_refund_amount,
- MAX(IFNULL(b.third_pay_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.payment_amount, IFNULL(b.recharge_amount, 0)) ELSE 0 END) - IFNULL(b.third_refund_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.refund_amount, 0) ELSE 0 END)) as thirdNetAmount
- from `charging_pile_order`.t_charging_order b
- left join `charging_pile_order`.t_charging_order_accounting_strategy a on a.charging_order_id = b.id
- <if test="req.phone != null and req.phone != ''">
- inner join `charging_pile_account`.`t_app_user` app_user on app_user.id = b.app_user_id
- </if>
- <include refid="ChargingOrderSummaryWhere"/>
- group by b.id,b.end_time,b.start_time,b.order_source
- ) as aa
+ sum(COALESCE(summary.charging_duration, UNIX_TIMESTAMP(b.end_time) - UNIX_TIMESTAMP(b.start_time))) as `time`,
+ sum(COALESCE(summary.income, IFNULL(b.electrovalence, 0) + CASE WHEN b.order_source = 1 THEN IFNULL(b.service_charge, 0) ELSE IFNULL(b.service_charge, 0) * 0.8 END)) as income,
+ sum(COALESCE(summary.electric_price, IFNULL(b.electrovalence, 0))) as electronicMoney,
+ sum(COALESCE(summary.service_price, CASE WHEN b.order_source = 1 THEN IFNULL(b.service_charge, 0) ELSE IFNULL(b.service_charge, 0) * 0.8 END)) as serviceMoney,
+ sum(COALESCE(summary.charging_capacity, IFNULL(b.charging_capacity, 0))) as total,
+ sum(CASE WHEN b.recharge_payment_type = 1 AND IFNULL(b.third_pay_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.payment_amount, IFNULL(b.recharge_amount, 0)) ELSE 0 END) - IFNULL(b.third_refund_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.refund_amount, 0) ELSE 0 END) > 0 THEN IFNULL(b.third_pay_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.payment_amount, IFNULL(b.recharge_amount, 0)) ELSE 0 END) - IFNULL(b.third_refund_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.refund_amount, 0) ELSE 0 END) ELSE 0 END) as wechatIncome,
+ sum(CASE WHEN b.recharge_payment_type in (2, 3) AND IFNULL(b.third_pay_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.payment_amount, IFNULL(b.recharge_amount, 0)) ELSE 0 END) - IFNULL(b.third_refund_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.refund_amount, 0) ELSE 0 END) > 0 THEN IFNULL(b.third_pay_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.payment_amount, IFNULL(b.recharge_amount, 0)) ELSE 0 END) - IFNULL(b.third_refund_amount, CASE WHEN b.pay_method = 3 THEN IFNULL(b.refund_amount, 0) ELSE 0 END) ELSE 0 END) as alipayIncome,
+ sum(CASE WHEN b.is_whitelist = 1 THEN IFNULL(b.electrovalence, 0) + IFNULL(b.service_charge, 0) ELSE 0 END) as whitelistChargingIncome,
+ sum(CASE WHEN b.is_whitelist = 1 THEN IFNULL(b.whitelist_electric_refund_amount, 0) + IFNULL(b.whitelist_service_refund_amount, 0) ELSE 0 END) as whitelistRefund
+ from `charging_pile_order`.t_charging_order b
+ left join `charging_pile_order`.t_charging_order_summary_data summary on summary.charging_order_id = b.id
+ <if test="req.phone != null and req.phone != ''">
+ inner join `charging_pile_account`.`t_app_user` app_user on app_user.id = b.app_user_id
+ </if>
+ <include refid="ChargingOrderSummaryWhere"/>
</select>
<select id="chargingOrderNolimit" resultType="map">
--
Gitblit v1.7.1