568-供应商财务统计优化：查询速度慢

cloudroam

2024-11-27 0d0a028920745bcdc5d9ddbf0ab3cbccfe167c63

568-供应商财务统计优化：查询速度慢

 src/main/java/com/mzl/flower/mapper/wallet/WalletBillRecordMapper.java

@@ -10,6 +10,7 @@
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
@@ -34,5 +35,8 @@
    @Select("select * from t_wallet_bill_record where withdraw_record_id  = #{withdrawRecordId}")
    WalletBillRecordDO getBillRecordByWithdrawRecordId(Long withdrawRecordId);

    @Select("SELECT COALESCE(SUM(change_amount), 0) AS change_amount FROM t_wallet_bill_record WHERE supplier_id = #{supplierId} and wallet_id = #{walletId}  AND remark = '历史订单统一提现'")
    BigDecimal getHistoryAmount(Long walletId, Long supplierId);
}



 src/main/java/com/mzl/flower/service/impl/wallet/WalletBillRecordServiceImpl.java

@@ -251,4 +251,9 @@
    public WalletBillRecordDO getBillRecordByWithdrawRecordId(Long withdrawRecordId) {
        return walletBillRecordMapper.getBillRecordByWithdrawRecordId(withdrawRecordId);
    }

    @Override
    public BigDecimal getHistoryAmount(Long walletId, Long supplierId) {
        return  walletBillRecordMapper.getHistoryAmount(walletId,supplierId);
    }
}

 src/main/java/com/mzl/flower/service/wallet/WalletBillRecordService.java

@@ -11,6 +11,7 @@
import com.baomidou.mybatisplus.extension.service.IService;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;

/**
 * <p>
@@ -36,4 +37,6 @@
    Page<WalletBillRecordVO> getPageByDesc(Page page, QueryWalletBillDTO dto);

    WalletBillRecordDO getBillRecordByWithdrawRecordId(Long withdrawRecordId);

    BigDecimal getHistoryAmount(Long walletId, Long supplierId);
}

 src/main/java/com/mzl/flower/web/v2/wallet/WalletController.java

@@ -9,6 +9,7 @@
import com.mzl.flower.dto.request.wallet.QueryWalletDTO;
import com.mzl.flower.dto.response.supplier.SupplierDTO;
import com.mzl.flower.service.supplier.SupplierService;
import com.mzl.flower.service.wallet.WalletBillRecordService;
import com.mzl.flower.service.wallet.WalletService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
@@ -36,6 +37,9 @@

    @Autowired
    private SupplierService supplierService;

    @Autowired
    private WalletBillRecordService walletBillRecordService;

    @PostMapping("")
    public ResponseEntity<ReturnDataDTO> create() {
@@ -71,12 +75,17 @@
        if(null!=walletDO){
            // 根据当前供应商获取待结算的钱
            BigDecimal waittingSettlementAmount = walletService.getWaittingSettlementAmount(walletDO);
            // 获取已经提现和已结算的钱
            BigDecimal historyAmount = walletBillRecordService.getHistoryAmount(walletDO.getId(), walletDO.getSupplierId());
            //已提现
            walletDO.setWithdrawnAmount(walletDO.getWithdrawnAmount().add(historyAmount));
            //已结算
            walletDO.setSettledAmount(walletDO.getSettledAmount().add(historyAmount));
            walletDO.setSettlingAmount(waittingSettlementAmount);
            // 总交易额度
            walletDO.setTotalTransactionAmount(walletService.getSupplierTotalTransactionAmount(walletDO));
            // 总扣款数量
            walletDO.setTotalDeduction(walletService.getSupplierDeductAmount(walletDO));

        }

        return returnData(R.SUCCESS.getCode(), walletDO);

 src/main/resources/mapper/wallet/WalletBillRecordMapper.xml

@@ -59,13 +59,22 @@
        order by wbr.create_time desc
    </select>
    <select id="queryPage" resultType="com.mzl.flower.dto.response.wallet.WalletBillRecordVO">
        select t.* ,s.name supplierName ,w.approve_time as approveTime ,u.nick_name AS approveName from t_wallet_bill_record t
        left join t_supplier_info s on t.supplier_id = s.id
        left join t_wallet_withdraw_record w on t.withdraw_record_id = w.id
        LEFT JOIN t_user u ON t.approve_by = u.id
        where t.deleted= 0
--         select t.* ,s.name supplierName ,w.approve_time as approveTime ,u.nick_name AS approveName from t_wallet_bill_record t
--         left join t_supplier_info s on t.supplier_id = s.id
--         left join t_wallet_withdraw_record w on t.withdraw_record_id = w.id
--         LEFT JOIN t_user u ON t.approve_by = u.id
--         where t.deleted= 0
        SELECT
        t.*,
        (SELECT s.name FROM t_supplier_info s WHERE s.id = t.supplier_id) AS supplierName,
        (SELECT w.approve_time FROM t_wallet_withdraw_record w WHERE w.id = t.withdraw_record_id) AS approveTime,
        (SELECT u.nick_name FROM t_user u WHERE u.id = t.approve_by) AS approveName
        FROM
        t_wallet_bill_record t
        WHERE
        t.deleted = 0
        <if test="dto.supplierName != null and dto.supplierName != ''">
            and s.name like concat('%', #{dto.supplierName}, '%')
            AND (SELECT s.name FROM t_supplier_info s WHERE s.id = t.supplier_id) LIKE CONCAT('%', #{dto.supplierName}, '%')
        </if>
        <if test="dto.supplierId != null and dto.supplierId != ''">
            and t.supplier_id = #{dto.supplierId}
@@ -83,10 +92,10 @@
            AND DATE_FORMAT(t.create_time, '%Y-%m-%d') &lt;= #{dto.endEndDate}
        </if>
        <if test="dto.approveStartDate!=null ">
            AND DATE_FORMAT(w.approve_time, '%Y-%m-%d') &gt;= #{dto.approveStartDate}
            AND DATE_FORMAT((SELECT w.approve_time FROM t_wallet_withdraw_record w WHERE w.id = t.withdraw_record_id), '%Y-%m-%d') &gt;= #{dto.approveStartDate}
        </if>
        <if test="dto.approveEndDate!=null ">
            AND DATE_FORMAT(w.approve_time, '%Y-%m-%d') &lt;= #{dto.approveEndDate}
            AND DATE_FORMAT((SELECT w.approve_time FROM t_wallet_withdraw_record w WHERE w.id = t.withdraw_record_id), '%Y-%m-%d')  &lt;= #{dto.approveEndDate}
        </if>
        order by t.create_time desc
    </select>