From 0d0a028920745bcdc5d9ddbf0ab3cbccfe167c63 Mon Sep 17 00:00:00 2001
From: cloudroam <cloudroam>
Date: 星期三, 27 十一月 2024 14:24:36 +0800
Subject: [PATCH] 568-供应商财务统计优化：查询速度慢
---
src/main/java/com/mzl/flower/service/wallet/WalletBillRecordService.java | 3 +++
src/main/java/com/mzl/flower/mapper/wallet/WalletBillRecordMapper.java | 4 ++++
src/main/java/com/mzl/flower/web/v2/wallet/WalletController.java | 11 ++++++++++-
src/main/resources/mapper/wallet/WalletBillRecordMapper.xml | 25 +++++++++++++++++--------
src/main/java/com/mzl/flower/service/impl/wallet/WalletBillRecordServiceImpl.java | 5 +++++
5 files changed, 39 insertions(+), 9 deletions(-)
diff --git a/src/main/java/com/mzl/flower/mapper/wallet/WalletBillRecordMapper.java b/src/main/java/com/mzl/flower/mapper/wallet/WalletBillRecordMapper.java
index b599c4b..f93dbac 100644
--- a/src/main/java/com/mzl/flower/mapper/wallet/WalletBillRecordMapper.java
+++ b/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);
}
+
diff --git a/src/main/java/com/mzl/flower/service/impl/wallet/WalletBillRecordServiceImpl.java b/src/main/java/com/mzl/flower/service/impl/wallet/WalletBillRecordServiceImpl.java
index 1154701..f3a17c7 100644
--- a/src/main/java/com/mzl/flower/service/impl/wallet/WalletBillRecordServiceImpl.java
+++ b/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);
+ }
}
diff --git a/src/main/java/com/mzl/flower/service/wallet/WalletBillRecordService.java b/src/main/java/com/mzl/flower/service/wallet/WalletBillRecordService.java
index cfc9afc..ba142d8 100644
--- a/src/main/java/com/mzl/flower/service/wallet/WalletBillRecordService.java
+++ b/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);
}
diff --git a/src/main/java/com/mzl/flower/web/v2/wallet/WalletController.java b/src/main/java/com/mzl/flower/web/v2/wallet/WalletController.java
index 208b7b0..5f9406b 100644
--- a/src/main/java/com/mzl/flower/web/v2/wallet/WalletController.java
+++ b/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);
diff --git a/src/main/resources/mapper/wallet/WalletBillRecordMapper.xml b/src/main/resources/mapper/wallet/WalletBillRecordMapper.xml
index 37f7e00..088d280 100644
--- a/src/main/resources/mapper/wallet/WalletBillRecordMapper.xml
+++ b/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') <= #{dto.endEndDate}
</if>
<if test="dto.approveStartDate!=null ">
- AND DATE_FORMAT(w.approve_time, '%Y-%m-%d') >= #{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') >= #{dto.approveStartDate}
</if>
<if test="dto.approveEndDate!=null ">
- AND DATE_FORMAT(w.approve_time, '%Y-%m-%d') <= #{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') <= #{dto.approveEndDate}
</if>
order by t.create_time desc
</select>
--
Gitblit v1.9.3