From e96fa2622043188f3e7eeee5ddc4d203c9fa5494 Mon Sep 17 00:00:00 2001
From: 陶杰 <1378534974@qq.com>
Date: 星期五, 13 十二月 2024 09:03:59 +0800
Subject: [PATCH] 1.花农报表调整 2.订单列表：异常订单 3.钱包增加体现状态查询
---
src/main/java/com/mzl/flower/dto/response/report/OrderSupplierReportResultVO.java | 14 ++
src/main/java/com/mzl/flower/web/payment/OrderController.java | 6 +
src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java | 2
src/main/java/com/mzl/flower/service/payment/OrderService.java | 30 +++++
src/main/resources/mapper/report/OrderReportMapper.xml | 20 ++
src/main/java/com/mzl/flower/dto/request/payment/OrderQueryDTO.java | 3
src/main/java/com/mzl/flower/dto/request/wallet/QueryWalletWithdrawDTO.java | 5
src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java | 9 +
src/main/resources/mapper/wallet/WalletWithdrawRecordMapper.xml | 4
src/main/resources/mapper/payment/OrderMapper.xml | 208 ++++++++++++++++++++++++++++++++++
10 files changed, 296 insertions(+), 5 deletions(-)
diff --git a/src/main/java/com/mzl/flower/dto/request/payment/OrderQueryDTO.java b/src/main/java/com/mzl/flower/dto/request/payment/OrderQueryDTO.java
index 5d8126c..ee3f4a3 100644
--- a/src/main/java/com/mzl/flower/dto/request/payment/OrderQueryDTO.java
+++ b/src/main/java/com/mzl/flower/dto/request/payment/OrderQueryDTO.java
@@ -91,4 +91,7 @@
@ApiModelProperty("服务区域-区")
private String region;
+
+ @ApiModelProperty("异常订单状态")
+ private String abnormalOrderStatus;
}
diff --git a/src/main/java/com/mzl/flower/dto/request/wallet/QueryWalletWithdrawDTO.java b/src/main/java/com/mzl/flower/dto/request/wallet/QueryWalletWithdrawDTO.java
index c4e4a0e..d3c6ee7 100644
--- a/src/main/java/com/mzl/flower/dto/request/wallet/QueryWalletWithdrawDTO.java
+++ b/src/main/java/com/mzl/flower/dto/request/wallet/QueryWalletWithdrawDTO.java
@@ -36,7 +36,10 @@
@DateTimeFormat(pattern="yyyy-MM-dd")
private LocalDate approveEndDate;
- @ApiModelProperty(value = "提现状态")
+ @ApiModelProperty(value = "审核状态")
private String approveState;
+ @ApiModelProperty(value = "提现状态（提现中、提现成功、提现失败）")
+ private String withdrawState;
+
}
diff --git a/src/main/java/com/mzl/flower/dto/response/report/OrderSupplierReportResultVO.java b/src/main/java/com/mzl/flower/dto/response/report/OrderSupplierReportResultVO.java
index 2414d2a..f48a48b 100644
--- a/src/main/java/com/mzl/flower/dto/response/report/OrderSupplierReportResultVO.java
+++ b/src/main/java/com/mzl/flower/dto/response/report/OrderSupplierReportResultVO.java
@@ -35,9 +35,23 @@
@ApiModelProperty("售后扣花农款")
private BigDecimal salesFeeSupplier;
+
+ @ApiModelProperty("总销售扎数")
+ private Integer orderNum;
+
@ApiModelProperty("实际销售扎数")
private Integer realSaleNum;
+ @ApiModelProperty("缺货扎数")
+ private BigDecimal orderLackNum;
+
+ @ApiModelProperty("降级扎数")
+ private BigDecimal orderReduceNum;
+
+ @ApiModelProperty("补货扎数")
+ private BigDecimal orderReplaceNum;
+
+
@ApiModelProperty("结算费用")
private BigDecimal profitFeeAmount;
diff --git a/src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java b/src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java
index 461ee22..debf57f 100644
--- a/src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java
+++ b/src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java
@@ -77,4 +77,6 @@
List<Order> selectAllDateList();
void updatePaymentCreateDate(@Param("order") Order order);
+
+ List<OrderStatusCountDTO> getAbnormalOrderStatusCount(@Param("condition") OrderQueryDTO dto);
}
diff --git a/src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java b/src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java
index 3ef65aa..a43f482 100644
--- a/src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java
+++ b/src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java
@@ -360,7 +360,8 @@
List<OrderSupplierReportResultVO> list = orderReportMapper.getOrderSupplierReport(supplierReportCalendarBO);
String[] rowsName = new String[]{"序号","日期", "供应商ID", "供应商","花农底价"
- , "降级扣款", "缺货扣款（缺货+补货）","售后扣花农款","实际销售扎数","结算费用","订单状态"};
+ , "降级扣款", "缺货扣款（缺货+补货）","售后扣花农款",
+ "总销售扎数","实际销售扎数","缺货扎数","降级扎数","补货扎数","结算费用","订单状态"};
List<Object[]> dataList = new ArrayList<>();
int sn = 1;
for (OrderSupplierReportResultVO o : list) {
@@ -375,7 +376,13 @@
objs[a++] = o.getOrderCheckFee(); //降级扣款
objs[a++] = o.getOrderLackFeeSupplier();//缺货扣款
objs[a++] = o.getSalesFeeSupplier(); // 售后扣花农款
+
+ objs[a++] = o.getOrderNum(); // 总销售扎数
objs[a++] = o.getRealSaleNum(); // 实际销售扎数
+ objs[a++] = o.getOrderLackNum(); // 缺货扎数
+ objs[a++] = o.getOrderReduceNum(); // 降级扎数
+ objs[a++] = o.getOrderReplaceNum(); // 补货扎数
+
objs[a++] = o.getProfitFeeAmount(); // 结算费用
objs[a++] = o.getSettleStatus(); // 订单状态
dataList.add(objs);
diff --git a/src/main/java/com/mzl/flower/service/payment/OrderService.java b/src/main/java/com/mzl/flower/service/payment/OrderService.java
index 69a4c85..2f92e46 100644
--- a/src/main/java/com/mzl/flower/service/payment/OrderService.java
+++ b/src/main/java/com/mzl/flower/service/payment/OrderService.java
@@ -1818,4 +1818,34 @@
orderMapper.updatePaymentCreateDate(order);
});
}
+
+ public List<OrderStatusCountDTO> getAbnormalOrderStatusCount(OrderQueryDTO dto) {
+ dto.setStartDate(parseLocalDateTime(dto.getStartDateStr(), true));
+ dto.setEndDate(parseLocalDateTime(dto.getEndDateStr(), false));
+
+ dto.setCreateStartDate(parseLocalDateTime(dto.getCreateStartDateStr(), 17, 0, 0, -1));
+ dto.setCreateEndDate(parseLocalDateTime(dto.getCreateEndDateStr(), 17, 0, 0, 0));
+
+ List<CodeValueDTO> ls = codeService.searchValue("ABNORMAL_ORDER_STATUS");
+ List<OrderStatusCountDTO> ll = orderMapper.getAbnormalOrderStatusCount(dto);
+ Map<String, Integer> llMap = new HashMap<>();
+ if (ll != null && ll.size() > 0) {
+ for (OrderStatusCountDTO c : ll) {
+ llMap.put(c.getValue(), c.getOrderCount());
+ }
+ }
+
+ List<OrderStatusCountDTO> rr = new ArrayList<>();
+ for (CodeValueDTO c : ls) {
+ OrderStatusCountDTO r = new OrderStatusCountDTO();
+ r.setValue(c.getValue());
+ r.setLabel(c.getLabel());
+ Integer count = llMap.get(r.getValue());
+ r.setOrderCount(count == null ? 0 : count);
+
+ rr.add(r);
+ }
+
+ return rr;
+ }
}
diff --git a/src/main/java/com/mzl/flower/web/payment/OrderController.java b/src/main/java/com/mzl/flower/web/payment/OrderController.java
index 476248d..b82944f 100644
--- a/src/main/java/com/mzl/flower/web/payment/OrderController.java
+++ b/src/main/java/com/mzl/flower/web/payment/OrderController.java
@@ -48,6 +48,12 @@
return returnData(R.SUCCESS.getCode(), orderService.getOrderStatusCount(dto));
}
+ @GetMapping("/abnormal/status/count")
+ @ApiOperation(value = "获取异常订单状态数量")
+ public ResponseEntity<ReturnDataDTO<List<OrderStatusCountDTO>>> getAbnormalOrderStatusCount(OrderQueryDTO dto){
+ return returnData(R.SUCCESS.getCode(), orderService.getAbnormalOrderStatusCount(dto));
+ }
+
@GetMapping("/list")
@ApiOperation(value = "查询订单列表")
public ResponseEntity<ReturnDataDTO<Page<OrderPlatformListDTO>>> selectOrderList(Page page, OrderQueryDTO dto){
diff --git a/src/main/resources/mapper/payment/OrderMapper.xml b/src/main/resources/mapper/payment/OrderMapper.xml
index 66cd365..fc86d17 100644
--- a/src/main/resources/mapper/payment/OrderMapper.xml
+++ b/src/main/resources/mapper/payment/OrderMapper.xml
@@ -193,6 +193,54 @@
AND u.nick_name LIKE concat('%', #{condition.createName},'%')
</if>
+ <if test="condition.abnormalOrderStatus != null and condition.abnormalOrderStatus != '' and condition.abnormalOrderStatus == 'UN_PROCESSED'">
+ AND q.id in (
+ select oi.order_id
+ from t_order_item oi
+ left join t_order o
+ on oi.order_id=o.id
+ where oi.status in ('abnormal','reduce','lack','back')
+ and o.transfer_id is null
+ and o.status in ('COLLECTION','SEND')
+ )
+-- AND exists(
+-- select 1 from t_order_item oi
+-- where oi.order_id = q.id
+-- and oi.status in ('abnormal','reduce','lack','back')
+-- )
+-- and q.transfer_id is null
+-- and q.status in ('COLLECTION', 'SEND')
+ </if>
+
+ <if test="condition.abnormalOrderStatus != null and condition.abnormalOrderStatus != '' and condition.abnormalOrderStatus == 'PROCESSED'">
+ AND q.id in (
+ select order_id from t_order_item oi where oi.status in ('abnormal','reduce','lack','back')
+ )
+ and q.id not in(
+ select oi.order_id
+ from t_order_item oi
+ left join t_order o
+ on oi.order_id=o.id
+ where oi.status in ('abnormal','reduce','lack','back')
+ and o.transfer_id is null
+ and o.status in ('COLLECTION','SEND')
+ )
+-- AND exists(
+-- select 1 from t_order_item oi
+-- where oi.order_id = q.id
+-- and oi.status in ('abnormal','reduce','lack','back')
+-- )
+-- AND not exists(
+-- select 1 from t_order_item oi
+-- where oi.order_id = q.id
+-- and oi.status in ('abnormal','reduce','lack','back')
+-- and q.transfer_id is null
+-- and q.status in ('COLLECTION', 'SEND')
+-- )
+
+
+ </if>
+
ORDER BY q.create_time desc, q.payment_time desc
</select>
@@ -429,6 +477,166 @@
FROM t_order t
order by t.create_time desc
</select>
+ <select id="getAbnormalOrderStatusCount"
+ resultType="com.mzl.flower.dto.response.payment.OrderStatusCountDTO">
+ SELECT 'UN_PROCESSED' as value, count(1) orderCount
+ FROM t_order q
+ left join t_user u on q.create_by = u.id
+ WHERE q.deleted = 0
+ <include refid="getAbnormalOrderStatusCount_condition"></include>
+
+ AND q.id in (
+ select oi.order_id
+ from t_order_item oi
+ left join t_order o
+ on oi.order_id=o.id
+ where oi.status in ('abnormal','reduce','lack','back')
+ and o.transfer_id is null
+ and o.status in ('COLLECTION','SEND')
+ )
+
+ union
+
+ SELECT 'PROCESSED' as value, count(1) orderCount
+ FROM t_order q
+ left join t_user u on q.create_by = u.id
+ WHERE q.deleted = 0
+ <include refid="getAbnormalOrderStatusCount_condition"></include>
+ AND q.id in (
+ select order_id from t_order_item oi where oi.status in ('abnormal','reduce','lack','back')
+ )
+ and q.id not in(
+ select oi.order_id
+ from t_order_item oi
+ left join t_order o
+ on oi.order_id=o.id
+ where oi.status in ('abnormal','reduce','lack','back')
+ and o.transfer_id is null
+ and o.status in ('COLLECTION','SEND')
+ )
+
+ </select>
+
+ <select id="getAbnormalOrderStatusCount2"
+ resultType="com.mzl.flower.dto.response.payment.OrderStatusCountDTO">
+ SELECT 'UN_PROCESSED' as value, count(1) orderCount
+ FROM t_order q
+ left join t_user u on q.create_by = u.id
+ WHERE q.deleted = 0
+ <include refid="getAbnormalOrderStatusCount_condition"></include>
+
+ AND exists(
+ select 1 from t_order_item oi
+ where oi.order_id = q.id
+ and oi.status in ('abnormal','reduce','lack','back')
+ )
+ and q.transfer_id is null
+ and q.status in ('COLLECTION', 'SEND')
+
+ union
+
+ SELECT 'PROCESSED' as value, count(1) orderCount
+ FROM t_order q
+ left join t_user u on q.create_by = u.id
+ WHERE q.deleted = 0
+ <include refid="getAbnormalOrderStatusCount_condition"></include>
+ AND exists(
+ select 1 from t_order_item oi
+ where oi.order_id = q.id
+ and oi.status in ('abnormal','reduce','lack','back')
+ )
+ AND not exists(
+ select 1 from t_order_item oi
+ where oi.order_id = q.id
+ and oi.status in ('abnormal','reduce','lack','back')
+ and q.transfer_id is null
+ and q.status in ('COLLECTION', 'SEND')
+ )
+
+ </select>
+
+ <sql id="getAbnormalOrderStatusCount_condition">
+ <if test="condition.orderNo != null and condition.orderNo != ''">
+ AND q.order_no LIKE concat('%', #{condition.orderNo},'%')
+ </if>
+ <if test="condition.customerAddress != null and condition.customerAddress != ''">
+ AND q.customer_address LIKE concat('%', #{condition.customerAddress},'%')
+ </if>
+ <if test="condition.customer != null and condition.customer != ''">
+ AND (q.customer LIKE concat('%', #{condition.customer},'%')
+ or q.customer_tel LIKE concat('%', #{condition.customer},'%')
+ )
+ </if>
+ <if test="condition.createBy != null and condition.createBy != ''">
+ AND q.create_by = #{condition.createBy}
+ </if>
+ <if test="condition.status != null and condition.status != ''">
+ AND q.status = #{condition.status}
+ </if>
+ <if test="condition.statusBackend != null and condition.statusBackend != ''">
+ AND q.status_backend = #{condition.statusBackend}
+ </if>
+ <!-- <if test="condition.startDate != null">-->
+ <!-- AND q.payment_time >= #{condition.startDate}-->
+ <!-- </if>-->
+ <!-- <if test="condition.endDate != null">-->
+ <!-- AND q.payment_time <= #{condition.endDate}-->
+ <!-- </if>-->
+ <!-- <if test="condition.createStartDate != null">-->
+ <!-- AND q.create_time >= #{condition.createStartDate}-->
+ <!-- </if>-->
+ <!-- <if test="condition.createEndDate != null">-->
+ <!-- AND q.create_time <= #{condition.createEndDate}-->
+ <!-- </if>-->
+ <if test="condition.createStartDate != null">
+ AND q.payment_time >= #{condition.createStartDate}
+ </if>
+ <if test="condition.createEndDate != null">
+ AND q.payment_time < #{condition.createEndDate}
+ </if>
+ <if test="condition.partnerId != null">
+ AND q.partner_id = #{condition.partnerId}
+ </if>
+ <if test="condition.partnerName != null and condition.partnerName != ''">
+ AND q.partner_name LIKE concat('%', #{condition.partnerName},'%')
+ </if>
+<!-- <if test="condition.levelDown != null and condition.levelDown != ''">-->
+<!-- AND exists(-->
+<!-- select 1 from t_order_item oi-->
+<!-- where oi.order_id = q.id-->
+<!-- and (oi.status = 'reduce' or oi.status = 'abnormal')-->
+<!-- )-->
+<!-- and q.transfer_id is null-->
+<!-- and q.status in ('COLLECTION', 'SEND')-->
+<!-- </if>-->
+ <if test="condition.billId != null and condition.billId != ''">
+ AND q.bill_id = #{condition.billId}
+ </if>
+ <if test="condition.flowerName != null and condition.flowerName != ''">
+ AND exists(
+ select 1 from t_order_item oi
+ where oi.order_id = q.id
+ and oi.flower_name LIKE concat('%', #{condition.flowerName},'%')
+ )
+ </if>
+
+ <if test="condition.warehouseLocationCode != null and condition.warehouseLocationCode != ''">
+ AND q.warehouse_location_code LIKE concat('%', #{condition.warehouseLocationCode},'%')
+ </if>
+ <if test="condition.createName != null and condition.createName != ''">
+ AND u.nick_name LIKE concat('%', #{condition.createName},'%')
+ </if>
+ <if test="condition.province != null and condition.province != ''">
+ AND q.customer_province LIKE concat('%', #{condition.province},'%')
+ </if>
+ <if test="condition.city != null and condition.city != ''">
+ AND q.customer_city LIKE concat('%', #{condition.city},'%')
+ </if>
+ <if test="condition.region != null and condition.region != ''">
+ AND q.customer_region LIKE concat('%', #{condition.region},'%')
+ </if>
+ </sql>
+
<update id="updatePaymentCreateDate">
update t_order set payment_date_sta=#{order.paymentDate}, create_date_sta=#{order.createDate} where id=#{order.id}
diff --git a/src/main/resources/mapper/report/OrderReportMapper.xml b/src/main/resources/mapper/report/OrderReportMapper.xml
index b9b2d5c..affc3b6 100644
--- a/src/main/resources/mapper/report/OrderReportMapper.xml
+++ b/src/main/resources/mapper/report/OrderReportMapper.xml
@@ -267,6 +267,7 @@
<sql id="baseSql">
select t2.*,
IFNULL(t2.order_check_fee+t2.order_lack_fee_supplier+t2.order_replace_fee,0) as order_check_total_fee,
+ IFNULL(t2.order_price_discount_amount+t2.order_coupon_amount_total,0) as order_discount_total_fee,
IFNULL(t3.fee_supplier,0) as order_fee_supplier,
IFNULL(t3.fee_partner,0) as order_fee_partner,
IFNULL(t3.fee_platform,0) as order_fee_platform,
@@ -480,7 +481,9 @@
sum( t1.replace_fee ) AS order_replace_fee,
sum( t1.lack_fee_supplier ) AS order_lack_fee_supplier,
sum( t1.num ) AS order_num,
- sum( t1.lack_num ) AS order_lack_num
+ sum( t1.lack_num ) AS order_lack_num,
+ sum( t1.reduce_num ) AS order_reduce_num,
+ sum( t1.replace_num ) AS order_replace_num
FROM
(
SELECT
@@ -496,8 +499,11 @@
IFNULL( oi.supplier_price, 0 ) * IFNULL( oi.num, 0 ) AS supplier_price_amount,
IFNULL( ois.check_fee, 0 ) AS check_fee,
IFNULL( ois.replace_fee, 0 ) AS replace_fee,
- IFNULL( ois.lack_fee_supplier, 0 ) AS lack_fee_supplier,
- IFNULL( (select oic.num from t_order_item_check oic where oic.order_item_id = oi.id and oic.type = 'lack' and oic.audit_status='AGREED'),0) as lack_num
+ IFNULL( ois.lack_fee_supplier, 0 ) AS lack_fee_supplier
+
+ ,IFNULL( (select oic.num from t_order_item_check oic where 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.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.order_item_id = oi.id and oic.type = 'lack' and oic.audit_status='AGREED'),0) as lack_num
FROM
t_order o
LEFT JOIN t_order_item oi ON oi.order_id = o.id
@@ -531,7 +537,11 @@
sum(order_supplier_price_amount) order_supplier_price_amount,
sum(order_check_fee) order_check_fee,
sum(order_replace_fee) + sum(order_lack_fee_supplier) order_lack_fee_supplier,
+ sum(order_num) as order_num,
sum(order_num)-sum(order_lack_num) as real_sale_num,
+ sum(order_lack_num) as order_lack_num,
+ sum(order_reduce_num ) AS order_reduce_num,
+ sum(order_replace_num ) AS order_replace_num,
sum(sales_fee_supplier) sales_fee_supplier,
sum(order_supplier_price_amount) - sum(order_check_fee) - sum(order_replace_fee) - sum(order_lack_fee_supplier) - sum(sales_fee_supplier) profitFeeAmount,
case when COUNT(CASE WHEN status_backend ='COMPLETED' THEN NULL ELSE 1 END) = 0 Then '已完成' Else '进行中' end as settleStatus
@@ -558,7 +568,11 @@
sum(order_supplier_price_amount) order_supplier_price_amount,
sum(order_check_fee) order_check_fee,
sum(order_replace_fee) + sum(order_lack_fee_supplier) order_lack_fee_supplier,
+ sum(order_num) as order_num,
sum(order_num)-sum(order_lack_num) as real_sale_num,
+ sum(order_lack_num) as order_lack_num,
+ sum(order_reduce_num ) AS order_reduce_num,
+ sum(order_replace_num ) AS order_replace_num,
sum(sales_fee_supplier) sales_fee_supplier,
sum(order_supplier_price_amount) - sum(order_check_fee) - sum(order_replace_fee) - sum(order_lack_fee_supplier) - sum(sales_fee_supplier) profitFeeAmount,
case when COUNT(CASE WHEN status_backend ='COMPLETED' THEN NULL ELSE 1 END) = 0 Then '已完成' Else '进行中' end as settleStatus
diff --git a/src/main/resources/mapper/wallet/WalletWithdrawRecordMapper.xml b/src/main/resources/mapper/wallet/WalletWithdrawRecordMapper.xml
index 1adafd6..fa6c5d8 100644
--- a/src/main/resources/mapper/wallet/WalletWithdrawRecordMapper.xml
+++ b/src/main/resources/mapper/wallet/WalletWithdrawRecordMapper.xml
@@ -34,6 +34,10 @@
<if test="dto.approveState != null and dto.approveState != ''">
and t.approve_state = #{dto.approveState}
</if>
+ <if test="dto.withdrawState != null and dto.withdrawState != ''">
+ and t.withdraw_state = #{dto.withdrawState}
+ </if>
+
<if test="dto.createStartDate!=null ">
AND DATE_FORMAT(t.create_time, '%Y-%m-%d') >= #{dto.createStartDate}
</if>
--
Gitblit v1.9.3