From c7d127ae867c00f01a7eb4977836565bcae6ee9f Mon Sep 17 00:00:00 2001
From: 陶杰 <1378534974@qq.com>
Date: 星期三, 11 十二月 2024 11:29:56 +0800
Subject: [PATCH] 1.财务报表-部分统计字段新增 2.合伙人报表-部分统计字段新增
---
src/main/java/com/mzl/flower/dto/response/report/OrderPartnerReportResultVO.java | 12 ++++
src/main/java/com/mzl/flower/dto/response/report/OrderReportResultVO.java | 23 +++++++
src/main/resources/mapper/report/OrderReportMapper.xml | 70 ++++++++++++-----------
src/main/java/com/mzl/flower/service/impl/report/OrderReportServiceImpl.java | 34 +++++++++--
4 files changed, 98 insertions(+), 41 deletions(-)
diff --git a/src/main/java/com/mzl/flower/dto/response/report/OrderPartnerReportResultVO.java b/src/main/java/com/mzl/flower/dto/response/report/OrderPartnerReportResultVO.java
index 0676964..c40cf04 100644
--- a/src/main/java/com/mzl/flower/dto/response/report/OrderPartnerReportResultVO.java
+++ b/src/main/java/com/mzl/flower/dto/response/report/OrderPartnerReportResultVO.java
@@ -55,9 +55,21 @@
@ApiModelProperty("总包干费--暂无")
private BigDecimal partnerTotalFeeAmount;
+ @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/dto/response/report/OrderReportResultVO.java b/src/main/java/com/mzl/flower/dto/response/report/OrderReportResultVO.java
index 7c82985..71273f4 100644
--- a/src/main/java/com/mzl/flower/dto/response/report/OrderReportResultVO.java
+++ b/src/main/java/com/mzl/flower/dto/response/report/OrderReportResultVO.java
@@ -40,11 +40,18 @@
@ApiModelProperty("合伙人区间加价--暂无")
private BigDecimal partnerSectionFeeAmount;
+
+ @ApiModelProperty("优惠合计")
+ private BigDecimal orderDiscountTotalFee;
+
@ApiModelProperty("会员折扣")
private BigDecimal orderPriceDiscountAmount;
@ApiModelProperty("优惠券")
private BigDecimal orderCouponAmountTotal;
+
+ @ApiModelProperty("质检总扣款")
+ private BigDecimal orderCheckTotalFee;
@ApiModelProperty("降级扣款")
private BigDecimal orderCheckFee;
@@ -55,6 +62,9 @@
@ApiModelProperty("补货扣款")
private BigDecimal orderReplaceFee;
+ @ApiModelProperty("售后总扣款")
+ private BigDecimal orderTotalFee;
+
@ApiModelProperty("售后扣合伙人款")
private BigDecimal orderFeePartner;
@@ -64,6 +74,19 @@
@ApiModelProperty("售后扣平台款")
private BigDecimal orderFeePlatform;
+ @ApiModelProperty("售后打包扣款")
+ private BigDecimal orderFeePlatformPack;
+
+ @ApiModelProperty("售后质检扣款")
+ private BigDecimal orderFeePlatformCheck;
+
+ @ApiModelProperty("售后物流扣款")
+ private BigDecimal orderFeePlatformTransport;
+
+ @ApiModelProperty("售后打包运费扣款")
+ private BigDecimal orderFeePackingTransport;
+
+
@ApiModelProperty("总包干费--暂无")
private BigDecimal partnerTotalFeeAmount;
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 5556fe2..0e270b3 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
@@ -103,9 +103,11 @@
return vo2;
}).collect(Collectors.toList());
- String[] rowsName = new String[]{"序号","下单日期", "销售额", "花农底价", "平台区间加价", "平台加价", "平台区域加价"
- , "合伙人加价", "合伙人区间加价", "会员折扣","优惠券","降级扣款","缺货扣款","补货扣款","售后扣合伙人款","售后扣花农款",
- "售后扣平台款","总包干费","总销售扎数","实际销售扎数","利润","结算状态",};
+ String[] rowsName = new String[]{"序号","下单日期", "销售额(原订单)","销售额(实付)", "花农底价", "平台区间加价", "平台加价", "平台区域加价"
+ , "合伙人加价", "合伙人区间加价","优惠合计", "会员折扣","优惠券",
+ "质检总扣款","降级扣款","缺货扣款","补货扣款",
+ "售后总扣款","售后供应商扣款","售后平台扣款","售后合伙人扣款","售后打包扣款","售后质检扣款","售后物流扣款","售后打包运费扣款",
+ "总包干费","总销售扎数","实际销售扎数","缺货扎数","降级扎数","补货扎数","利润","结算状态",};
List<Object[]> dataList = new ArrayList<>();
int sn = 1;
for (OrderReportResultVO o : list) {
@@ -113,24 +115,42 @@
int a = 0;
objs[a++] = sn; // 序号
objs[a++] = format(o.getOrderDate(), "yyyy-MM-dd"); // 下单日期
- objs[a++] = o.getOrderTotal(); // 销售额
+ objs[a++] = o.getOrderTotal(); // 销售额(原订单)
+ objs[a++] = o.getTotalAmount(); // 销售额(实付)
objs[a++] = o.getOrderSupplierPriceAmount(); // 花农底价
objs[a++] = o.getOrderMarkupOneAmount(); // 平台区间加价
objs[a++] = o.getOrderMarkupTwoAmount(); // 平台加价
objs[a++] = o.getPlatformAreaFeeAmount(); //平台区域加价
objs[a++] = o.getOrderMarkupPartnerAmount(); //合伙人加价
objs[a++] = o.getPartnerSectionFeeAmount(); //合伙人区间加价
+
+ objs[a++] = o.getOrderDiscountTotalFee();//优惠合计
objs[a++] = o.getOrderPriceDiscountAmount();//会员折扣
objs[a++] = o.getOrderCouponAmountTotal(); // 优惠券
+
+ objs[a++] = o.getOrderCheckTotalFee();//质检总扣款
objs[a++] = o.getOrderCheckFee();//降级扣款
objs[a++] = o.getOrderLackFeeSupplier(); //缺货扣款
objs[a++] = o.getOrderReplaceFee(); //补货扣款
- objs[a++] = o.getOrderFeePartner(); //售后扣合伙人款
- objs[a++] = o.getOrderFeeSupplier(); // 售后扣花农款
- objs[a++] = o.getOrderFeePlatform(); //售后扣平台款
+
+
+ objs[a++] = o.getOrderTotalFee(); //售后总扣款
+ objs[a++] = o.getOrderFeeSupplier(); //售后供应商扣款
+ objs[a++] = o.getOrderFeePlatform(); //售后平台扣款
+ objs[a++] = o.getOrderFeePartner(); //售后合伙人扣款
+ objs[a++] = o.getOrderFeePlatformPack(); //售后打包扣款
+ objs[a++] = o.getOrderFeePlatformCheck(); // 售后质检扣款
+ objs[a++] = o.getOrderFeePlatformTransport(); //售后物流扣款
+ objs[a++] = o.getOrderFeePackingTransport(); //售后打包运费扣款
+
+
objs[a++] = o.getPartnerTotalFeeAmount(); // 总包干费
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/resources/mapper/report/OrderReportMapper.xml b/src/main/resources/mapper/report/OrderReportMapper.xml
index bc6b171..f58f1ae 100644
--- a/src/main/resources/mapper/report/OrderReportMapper.xml
+++ b/src/main/resources/mapper/report/OrderReportMapper.xml
@@ -10,8 +10,10 @@
sum(order_markup_one_amount) as order_markup_one_amount,
sum(order_markup_two_amount) as order_markup_two_amount,
sum(order_markup_partner_amount) as order_markup_partner_amount,
+ sum(order_price_discount_amount)+sum(order_coupon_amount_total) as order_discount_total_fee,
sum(order_price_discount_amount) as order_price_discount_amount,
sum(order_coupon_amount_total) as order_coupon_amount_total,
+ sum(order_check_fee)+sum(order_lack_fee_supplier)+sum(order_replace_fee) as order_check_total_fee,
sum(order_check_fee) as order_check_fee,
sum(order_lack_fee_supplier) as order_lack_fee_supplier,
sum(order_replace_fee) as order_replace_fee,
@@ -39,10 +41,10 @@
where o.deleted=0
and o.status_backend not in ('PENDING','CANCEL','REFUND')
<if test="dto.startDate != null">
- and o.payment_time > #{dto.startDate}
+ and o.payment_time >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and o.payment_time <= #{dto.endDate}
+ and o.payment_time < #{dto.endDate}
</if>
<if test="dto.partnerId != null">
and o.create_by in (
@@ -88,10 +90,10 @@
where o.deleted=0
and o.status_backend not in ('PENDING','CANCEL','REFUND')
<if test="dto.startDate != null">
- and o.payment_time > #{dto.startDate}
+ and o.payment_time >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and o.payment_time <= #{dto.endDate}
+ and o.payment_time < #{dto.endDate}
</if>
<if test="dto.partnerId != null">
and o.create_by in (
@@ -122,10 +124,10 @@
where o.deleted=0
and o.status_backend not in ('PENDING','CANCEL','REFUND')
<if test="dto.startDate != null">
- and o.payment_time > #{dto.startDate}
+ and o.payment_time >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and o.payment_time <= #{dto.endDate}
+ and o.payment_time < #{dto.endDate}
</if>
<if test="dto.partnerId != null">
and o.create_by in (
@@ -151,10 +153,10 @@
where o.deleted=0
and o.status_backend not in ('PENDING','CANCEL','REFUND')
<if test="dto.startDate != null">
- and o.payment_time > #{dto.startDate}
+ and o.payment_time >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and o.payment_time <= #{dto.endDate}
+ and o.payment_time < #{dto.endDate}
</if>
<if test="dto.partnerId != null">
and o.create_by in (
@@ -234,7 +236,7 @@
select t2.payment_date,t2.id,t2.order_no,t2.create_by,t2.partner_id,p.name as partner_name,r2.* from (
SELECT t.*,
CASE
- WHEN t.payment_time > DATE(t.payment_time) + INTERVAL 17 HOUR THEN DATE(t.payment_time) + INTERVAL 1 DAY
+ WHEN t.payment_time >= DATE(t.payment_time) + INTERVAL 17 HOUR THEN DATE(t.payment_time) + INTERVAL 1 DAY
ELSE DATE(t.payment_time)
END AS payment_date,
t.payment_time as payment_time2
@@ -265,7 +267,7 @@
select t2.*,
IFNULL(t3.fee_supplier,0) as order_fee_supplier,
IFNULL(t3.fee_partner,0) as order_fee_partner,
- IFNULL(t3.fee_platform,0) + IFNULL(t3.fee_packing_transport,0) as order_fee_platform,
+ IFNULL(t3.fee_platform,0) as order_fee_platform,
IFNULL(t3.fee_platform_pack,0) as order_fee_platform_pack,
IFNULL(t3.fee_platform_check,0) as order_fee_platform_check,
IFNULL(t3.fee_platform_transport,0) as order_fee_platform_transport,
@@ -324,10 +326,10 @@
left join t_order_item_settlement ois on ois.order_item_id = oi.id
WHERE oi.deleted = 0
<if test="dto.startDate != null">
- and o.payment_time > #{dto.startDate}
+ and o.payment_time >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and o.payment_time <= #{dto.endDate}
+ and o.payment_time < #{dto.endDate}
</if>
)t1
@@ -368,7 +370,7 @@
from (
SELECT
CASE
- WHEN o.payment_time > DATE(o.payment_time) + INTERVAL 17 HOUR THEN DATE(o.payment_time) + INTERVAL 1 DAY
+ WHEN o.payment_time >= DATE(o.payment_time) + INTERVAL 17 HOUR THEN DATE(o.payment_time) + INTERVAL 1 DAY
ELSE DATE(o.payment_time)
END AS payment_date,
oi.order_id,
@@ -415,10 +417,10 @@
left join t_order_item_settlement ois on ois.order_item_id = oi.id
WHERE oi.deleted = 0
<if test="dto.startDate != null">
- and o.payment_time > #{dto.startDate}
+ and o.payment_time >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and o.payment_time <= #{dto.endDate}
+ and o.payment_time < #{dto.endDate}
</if>
)t1
where 1=1
@@ -497,7 +499,7 @@
and o.payment_time >= #{dto.startDate}
</if>
<if test="dto.endDate != null">
- and o.payment_time <= #{dto.endDate}
+ and o.payment_time < #{dto.endDate}
</if>
<if test="dto.supplierId != null">
and oi.supplier_id = #{dto.supplierId}
@@ -631,8 +633,8 @@
where o.deleted=0
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
</sql>
<!--上月成交：上月售卖的底价合计-->
<sql id="last_mon_supplier_amount">
@@ -643,8 +645,8 @@
where o.deleted=0
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
</sql>
<!--今日成交：今日售卖的底价合计 // todo 需要根据当前时间判断今日的开始时间和结束时间-->
<sql id="today_supplier_amount">
@@ -655,8 +657,8 @@
where o.deleted=0
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
- and o.payment_time > #{dto.startDateTime}
- and o.payment_time <= #{dto.endDateTime}
+ and o.payment_time >= #{dto.startDateTime}
+ and o.payment_time < #{dto.endDateTime}
</sql>
<!--缺货（本月）：本月质检缺货，并且web端审核通过的数量合计-->
<sql id="cur_mon_lack_num">
@@ -670,8 +672,8 @@
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
and oic.type = 'lack' and oic.audit_status='AGREED'
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
</sql>
<!--缺货（上月）：上月质检缺货，并且web端审核通过的数量合计-->
<sql id="last_mon_lack_num">
@@ -685,8 +687,8 @@
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
and oic.type = 'lack' and oic.audit_status='AGREED'
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
</sql>
<!--降级（本月）：本月质检降级，并且web端审核通过的数量合计-->
<sql id="cur_mon_reduce_num">
@@ -700,8 +702,8 @@
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
and oic.type = 'reduce' and oic.audit_status='AGREED'
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
</sql>
<!-- 降级（上月）：上月质检降级，并且web端审核通过的数量合计-->
@@ -716,8 +718,8 @@
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
and oic.type = 'reduce' and oic.audit_status='AGREED'
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
</sql>
<!-- 补货（本月）：本月质检补货，并且web端审核通过的数量合计-->
<sql id="cur_mon_replace_num">
@@ -731,8 +733,8 @@
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
and oic.type = 'replace' and oic.audit_status='AGREED'
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(LAST_DAY(CURRENT_DATE()), '%Y-%m-%d 17:00:00')
</sql>
<!-- 补货（上月）：上月质检补货，并且web端审核通过的数量合计-->
<sql id="last_mon_replace_num">
@@ -746,8 +748,8 @@
and o.status_backend not in ('PENDING','CANCEL','REFUND')
and oi.supplier_id = #{dto.supplierId}
and oic.type = 'replace' and oic.audit_status='AGREED'
- and o.payment_time > DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
- and o.payment_time <= DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
+ and o.payment_time >= DATE_FORMAT(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH)), '%Y-%m-%d 17:00:00')
+ and o.payment_time < DATE_FORMAT(DATE_SUB(DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01'), INTERVAL 1 DAY), '%Y-%m-%d 17:00:00')
</sql>
</mapper>
\ No newline at end of file
--
Gitblit v1.9.3