From 78082b601d0eda9b820fbeba9a01fbfeb5309692 Mon Sep 17 00:00:00 2001
From: 陶杰 <1378534974@qq.com>
Date: 星期五, 08 十一月 2024 14:27:51 +0800
Subject: [PATCH] 1.供应商-钱包：统计修改
---
src/main/java/com/mzl/flower/service/impl/wallet/WalletServiceImpl.java | 6 +
src/main/java/com/mzl/flower/service/impl/wallet/WalletReduceServiceImpl.java | 5 +
src/main/resources/mapper/wallet/WalletMapper.xml | 44 ++++++++++++++
src/main/java/com/mzl/flower/mapper/wallet/WalletReduceMapper.java | 2
src/main/java/com/mzl/flower/dto/response/wallet/WalletReduceVO.java | 3 +
src/main/java/com/mzl/flower/service/wallet/WalletReduceService.java | 7 ++
src/main/resources/mapper/wallet/WalletReduceMapper.xml | 86 ++++++++++++++++++++++------
7 files changed, 130 insertions(+), 23 deletions(-)
diff --git a/src/main/java/com/mzl/flower/dto/response/wallet/WalletReduceVO.java b/src/main/java/com/mzl/flower/dto/response/wallet/WalletReduceVO.java
index e6e59d9..859f008 100644
--- a/src/main/java/com/mzl/flower/dto/response/wallet/WalletReduceVO.java
+++ b/src/main/java/com/mzl/flower/dto/response/wallet/WalletReduceVO.java
@@ -25,5 +25,8 @@
@ApiModelProperty(value = "供应商ID")
private Long supplierId;
+ @ApiModelProperty(value = "支付时间")
+ private String paymentTime;
+
}
diff --git a/src/main/java/com/mzl/flower/mapper/wallet/WalletReduceMapper.java b/src/main/java/com/mzl/flower/mapper/wallet/WalletReduceMapper.java
index 0148a84..f44e21a 100644
--- a/src/main/java/com/mzl/flower/mapper/wallet/WalletReduceMapper.java
+++ b/src/main/java/com/mzl/flower/mapper/wallet/WalletReduceMapper.java
@@ -30,4 +30,6 @@
BigDecimal getCheckReduceAmount(@Param("dto") QueryWalletAmountDTO queryWalletAmountDTO);
BigDecimal getSaleReduceAmount(@Param("dto")QueryWalletAmountDTO queryWalletAmountDTO);
+
+ BigDecimal getStationFeeAmount(@Param("dto") QueryWalletAmountDTO queryWalletAmountDTO);
}
diff --git a/src/main/java/com/mzl/flower/service/impl/wallet/WalletReduceServiceImpl.java b/src/main/java/com/mzl/flower/service/impl/wallet/WalletReduceServiceImpl.java
index eefd7a7..7165124 100644
--- a/src/main/java/com/mzl/flower/service/impl/wallet/WalletReduceServiceImpl.java
+++ b/src/main/java/com/mzl/flower/service/impl/wallet/WalletReduceServiceImpl.java
@@ -42,4 +42,9 @@
public BigDecimal getSaleReduceAmount(QueryWalletAmountDTO queryWalletAmountDTO) {
return walletReduceMapper.getSaleReduceAmount(queryWalletAmountDTO);
}
+
+ @Override
+ public BigDecimal getStationFeeAmount(QueryWalletAmountDTO queryWalletAmountDTO) {
+ return walletReduceMapper.getStationFeeAmount(queryWalletAmountDTO);
+ }
}
diff --git a/src/main/java/com/mzl/flower/service/impl/wallet/WalletServiceImpl.java b/src/main/java/com/mzl/flower/service/impl/wallet/WalletServiceImpl.java
index 1f3d758..161cc3e 100644
--- a/src/main/java/com/mzl/flower/service/impl/wallet/WalletServiceImpl.java
+++ b/src/main/java/com/mzl/flower/service/impl/wallet/WalletServiceImpl.java
@@ -171,8 +171,12 @@
BigDecimal saleDeduceAmount = walletReduceService.getSaleReduceAmount(queryWalletAmountDTO);
saleDeduceAmount = saleDeduceAmount != null ? saleDeduceAmount : BigDecimal.ZERO;
+ // 集货站运费
+ BigDecimal stationFeeAmount = walletReduceService.getStationFeeAmount(queryWalletAmountDTO);
+ stationFeeAmount = stationFeeAmount != null ? stationFeeAmount : BigDecimal.ZERO;
+
// 总扣款
- BigDecimal deduceAmount = checkDeduceAmount.add(saleDeduceAmount);
+ BigDecimal deduceAmount = checkDeduceAmount.add(saleDeduceAmount).add(stationFeeAmount);
return deduceAmount;
}
diff --git a/src/main/java/com/mzl/flower/service/wallet/WalletReduceService.java b/src/main/java/com/mzl/flower/service/wallet/WalletReduceService.java
index a1c9b8f..e26494d 100644
--- a/src/main/java/com/mzl/flower/service/wallet/WalletReduceService.java
+++ b/src/main/java/com/mzl/flower/service/wallet/WalletReduceService.java
@@ -35,4 +35,11 @@
* @return
*/
BigDecimal getSaleReduceAmount(QueryWalletAmountDTO queryWalletAmountDTO);
+
+ /**
+ * 获取集货站扣款数量
+ * @param queryWalletAmountDTO
+ * @return
+ */
+ BigDecimal getStationFeeAmount(QueryWalletAmountDTO queryWalletAmountDTO);
}
diff --git a/src/main/resources/mapper/wallet/WalletMapper.xml b/src/main/resources/mapper/wallet/WalletMapper.xml
index 51d5ce4..9131387 100644
--- a/src/main/resources/mapper/wallet/WalletMapper.xml
+++ b/src/main/resources/mapper/wallet/WalletMapper.xml
@@ -23,7 +23,9 @@
</resultMap>
<select id="getWaittingSettlementAmount" resultType="java.math.BigDecimal">
- <include refid="supplier_settlement_amount"></include>
+ <!-- <include refid="supplier_settlement_amount"></include>-->
+ <include refid="supplier_settlement_amount_timing"></include>
+
</select>
<!-- 总交易额-->
@@ -32,7 +34,9 @@
from t_order_item oi
left join t_order o
on oi.order_id = o.id
- where oi.supplier_id = #{dto.supplierId}
+ where o.deleted=0
+ and o.status_backend not in ('PENDING','CANCEL','REFUND')
+ and oi.supplier_id = #{dto.supplierId}
and o.payment_time > #{dto.startTime}
</select>
@@ -47,4 +51,40 @@
AND USER_ID=#{dto.userId}
</sql>
+
+ <sql id="supplier_settlement_amount_timing">
+ select
+ sum(t2.final_price) as settlement_amount
+ from(
+ select
+ t.order_item_id,
+ t.supplier_id,
+ t.supplier_price,
+ t.num-t.reduce_num-t.replace_num-t.lack_num as final_num,
+ t.supplier_price*(t.num-t.reduce_num-t.replace_num-t.lack_num )-t.fee_supplier-t.station_fee as final_price,
+ t.fee_supplier,
+ t.station_fee
+ from (
+ SELECT
+ o.order_no,
+ oi.id as order_item_id,
+ oi.supplier_id,
+ o.payment_time,
+ oi.supplier_price,
+ oi.num as num,
+ IFNULL( (select oic.num from t_order_item_check oic where oic.deleted=0 and oic.order_item_id = oi.id and oic.type = 'reduce' and oic.audit_status='AGREED'),0) as reduce_num,
+ IFNULL( (select oic.num from t_order_item_check oic where oic.deleted=0 and oic.order_item_id = oi.id and oic.type = 'replace' and oic.audit_status='AGREED'),0) as replace_num,
+ IFNULL( (select oic.num from t_order_item_check oic where oic.deleted=0 and oic.order_item_id = oi.id and oic.type = 'lack' and oic.audit_status='AGREED'),0) as lack_num,
+ IFNULL( (select ois.fee_supplier from t_order_item_sales ois where ois.deleted=0 and ois.order_item_id = oi.id and ois.status='AGREED'),0) as fee_supplier,
+ IFNULL( (select ois.station_fee from t_order_item_settlement ois where ois.deleted=0 and ois.order_item_id = oi.id ),0) as station_fee
+ FROM t_order_item oi
+ LEFT JOIN t_order o ON oi.order_id = o.id
+ WHERE o.deleted=0
+ and o.status_backend not in ('PENDING','CANCEL','REFUND','COMPLETED')
+ and oi.supplier_id = #{dto.supplierId}
+ ) t
+ ) t2
+
+ </sql>
+
</mapper>
diff --git a/src/main/resources/mapper/wallet/WalletReduceMapper.xml b/src/main/resources/mapper/wallet/WalletReduceMapper.xml
index 3501285..fd03188 100644
--- a/src/main/resources/mapper/wallet/WalletReduceMapper.xml
+++ b/src/main/resources/mapper/wallet/WalletReduceMapper.xml
@@ -6,57 +6,78 @@
<select id="getPage" resultType="com.mzl.flower.dto.response.wallet.WalletReduceVO">
select *
from (
- SELECT si.id as id,si.fee_supplier as fee ,si.audit_time as audit_time,'sale' as type,'售后扣款' as type_name,o.order_no as order_no,oi.supplier_id as supplier_id
+ SELECT si.id as id
+ ,si.fee_supplier as fee
+ ,si.audit_time as audit_time
+ ,o.payment_time as payment_time
+ ,'sale' as type,'售后扣款' as type_name
+ ,o.order_no as order_no
+ ,oi.supplier_id as supplier_id
FROM t_order_item_sales si
join t_order_item oi on oi.id = si.order_item_id
left join t_order o on oi.order_id=o.id
- where si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
+ where o.deleted=0
+ and o.status_backend not in ('PENDING','CANCEL','REFUND')
+ and si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
and o.payment_time >= #{dto.walletOnlineTime}
<if test="dto.supplierId != null">
and oi.supplier_id = #{dto.supplierId}
</if>
+<!-- <if test="dto.startDate != null">-->
+<!-- and DATE_FORMAT(si.audit_time, '%Y-%m-%d') >= #{dto.startDate}-->
+<!-- </if>-->
+<!-- <if test="dto.endDate != null">-->
+<!-- and DATE_FORMAT(si.audit_time, '%Y-%m-%d') <= #{dto.endDate}-->
+<!-- </if>-->
<if test="dto.startDate != null">
- and DATE_FORMAT(si.audit_time, '%Y-%m-%d') >= #{dto.startDate}
+ and DATE_FORMAT(o.payment_time, '%Y-%m-%d') >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and DATE_FORMAT(si.audit_time, '%Y-%m-%d') <= #{dto.endDate}
+ and DATE_FORMAT(o.payment_time, '%Y-%m-%d') <= #{dto.endDate}
</if>
union
- select oic.id as id ,oic.deduct_amount as fee,oic.audit_time as audit_time,oic.type as type,check_type.label as type_name,o.order_no as order_no,oi.supplier_id as supplier_id
+ select oic.id as id ,
+ oic.num*oi.supplier_price AS fee,
+ oic.audit_time AS audit_time,
+ o.payment_time as payment_time,
+ oic.type as type,
+ check_type.label as type_name,
+ o.order_no as order_no,
+ oi.supplier_id as supplier_id
from t_order_item_check oic
left join t_order_item oi
on oic.order_item_id=oi.id
left join t_order o
on oi.order_id=o.id
left join t_code_value check_type
- on check_type.value=oic.type and check_type.type_code='CHECK_TYPE'
- where oic.deleted=0 and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
+ on check_type.value=oic.type and check_type.type_code='CHECK_TYPE'
+ where
+ o.deleted=0
+ and o.status_backend not in ('PENDING','CANCEL','REFUND')
+ and oic.deleted=0 and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
and o.payment_time >= #{dto.walletOnlineTime}
<if test="dto.supplierId != null">
and oi.supplier_id = #{dto.supplierId}
</if>
+<!-- <if test="dto.startDate != null">-->
+<!-- and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') >= #{dto.startDate}-->
+<!-- </if>-->
+<!-- <if test="dto.endDate != null">-->
+<!-- and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') <= #{dto.endDate}-->
+<!-- </if>-->
<if test="dto.startDate != null">
- and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') >= #{dto.startDate}
+ and DATE_FORMAT(o.payment_time, '%Y-%m-%d') >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and DATE_FORMAT(oic.audit_time, '%Y-%m-%d') <= #{dto.endDate}
+ and DATE_FORMAT(o.payment_time, '%Y-%m-%d') <= #{dto.endDate}
</if>
) t
order by audit_time desc
</select>
<select id="getCheckReduceAmount" resultType="java.math.BigDecimal">
- SELECT sum(si.fee_supplier)
- FROM t_order_item_sales si
- join t_order_item oi on oi.id = si.order_item_id
- left join t_order o on oi.order_id=o.id
- where si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
- and oi.supplier_id = #{dto.supplierId}
- and o.payment_time >= #{dto.startTime}
- </select>
- <select id="getSaleReduceAmount" resultType="java.math.BigDecimal">
- select sum(oic.deduct_amount)
+ select sum(oic.num*oi.supplier_price)
from t_order_item_check oic
left join t_order_item oi
on oic.order_item_id=oi.id
@@ -64,7 +85,32 @@
on oi.order_id=o.id
left join t_code_value check_type
on check_type.value=oic.type and check_type.type_code='CHECK_TYPE'
- where oic.deleted=0 and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
+ where o.deleted=0
+ and o.status_backend not in ('PENDING','CANCEL','REFUND')
+ and oic.deleted=0 and oic.audit_status='AGREED' and oi.deleted=0 and o.deleted=0
+ and oi.supplier_id = #{dto.supplierId}
+ and o.payment_time >= #{dto.startTime}
+ </select>
+ <select id="getSaleReduceAmount" resultType="java.math.BigDecimal">
+
+ SELECT sum(si.fee_supplier)
+ FROM t_order_item_sales si
+ join t_order_item oi on oi.id = si.order_item_id
+ left join t_order o on oi.order_id=o.id
+ where o.deleted=0
+ and o.status_backend not in ('PENDING','CANCEL','REFUND')
+ and si.deleted=0 and si.status='AGREED' and oi.deleted=0 and o.deleted=0
+ and oi.supplier_id = #{dto.supplierId}
+ and o.payment_time >= #{dto.startTime}
+ </select>
+ <select id="getStationFeeAmount" resultType="java.math.BigDecimal">
+ SELECT sum(ois.station_fee)
+ FROM t_order_item_settlement ois
+ join t_order_item oi on oi.id = ois.order_item_id
+ left join t_order o on oi.order_id=o.id
+ where o.deleted=0
+ and o.status_backend not in ('PENDING','CANCEL','REFUND')
+ and ois.deleted=0 and oi.deleted=0
and oi.supplier_id = #{dto.supplierId}
and o.payment_time >= #{dto.startTime}
</select>
--
Gitblit v1.9.3