fix: optimize charging order summary query

longz

2026-08-05 a42f3d5c9950511e935683621edd0325d52aba6c

fix: optimize charging order summary query

 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) &gt; 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) &gt; 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">