From b14befec1ef345d88a04da3ca1e10a006a7862e5 Mon Sep 17 00:00:00 2001
From: cloudroam <cloudroam>
Date: 星期一, 30 九月 2024 16:04:17 +0800
Subject: [PATCH] add:订单列表导出
---
src/main/java/com/mzl/flower/dto/response/payment/OrderListExportDTO.java | 55 +++++++++++++
src/main/java/com/mzl/flower/web/payment/OrderController.java | 7 +
src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java | 3
src/main/java/com/mzl/flower/service/payment/OrderService.java | 50 ++++++++++++
src/main/resources/mapper/payment/OrderMapper.xml | 80 ++++++++++++++++++++
5 files changed, 194 insertions(+), 1 deletions(-)
diff --git a/src/main/java/com/mzl/flower/dto/response/payment/OrderListExportDTO.java b/src/main/java/com/mzl/flower/dto/response/payment/OrderListExportDTO.java
new file mode 100644
index 0000000..480aedb
--- /dev/null
+++ b/src/main/java/com/mzl/flower/dto/response/payment/OrderListExportDTO.java
@@ -0,0 +1,55 @@
+package com.mzl.flower.dto.response.payment;
+
+import com.fasterxml.jackson.annotation.JsonFormat;
+import com.mzl.flower.base.AbstractTransDTO;
+import io.swagger.annotations.ApiModelProperty;
+import lombok.Data;
+import org.springframework.format.annotation.DateTimeFormat;
+
+import java.math.BigDecimal;
+import java.time.LocalDateTime;
+
+@Data
+public class OrderListExportDTO extends AbstractTransDTO {
+ @ApiModelProperty(value = "订单单号")
+ private String orderNo;
+
+ @ApiModelProperty(value = "下单人")
+ private String createName;
+
+ @ApiModelProperty(value = "收货人")
+ private String customer;
+
+ @ApiModelProperty(value = "收货人手机号码")
+ private String customerTel;
+
+ @ApiModelProperty(value = "收货地址")
+ private String customerAddress;
+
+ @ApiModelProperty(value = "订单金额")
+ private BigDecimal totalAmount;
+
+ @ApiModelProperty(value = "花农底价")
+ private BigDecimal supplierAmount;
+
+ @ApiModelProperty(value = "状态")
+ private String statusBackendStr;
+
+ @ApiModelProperty(value = "下单时间")
+ @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
+ @DateTimeFormat
+ private LocalDateTime createTime;
+
+ @ApiModelProperty(value = "合伙人名称")
+ private String partnerName;
+
+ @ApiModelProperty(value = "库位名称")
+ private String warehouseLocationCode;
+
+ @ApiModelProperty(value = "特殊需求")
+ private String specialNeedsStr;
+
+ @ApiModelProperty(value = "留言")
+ private String remarks;
+
+}
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 6dd5dc4..ec510dc 100644
--- a/src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java
+++ b/src/main/java/com/mzl/flower/mapper/payment/OrderMapper.java
@@ -52,4 +52,7 @@
int getFlowerCompleteNumWithinTimeRange(@Param("userId") String userId ,@Param("flowerId") Long flowerId,
@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);
+
+ List<OrderListExportDTO> selectOrderExportList(@Param("condition") OrderQueryDTO dto);
+
}
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 3ebd8d4..e4c422f 100644
--- a/src/main/java/com/mzl/flower/service/payment/OrderService.java
+++ b/src/main/java/com/mzl/flower/service/payment/OrderService.java
@@ -51,6 +51,7 @@
import com.mzl.flower.service.system.CodeService;
import com.mzl.flower.service.system.WeChatService;
import com.mzl.flower.service.transport.TransportService;
+import com.mzl.flower.utils.ExcelExportUtil;
import com.mzl.flower.utils.UUIDGenerator;
import com.wechat.pay.java.core.util.GsonUtil;
import io.micrometer.core.instrument.util.StringUtils;
@@ -63,8 +64,10 @@
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
+import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.RoundingMode;
+import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
@@ -1705,4 +1708,51 @@
orderMapper.updateById(order);
}
+ private List<OrderListExportDTO> getOrderExportListBase(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<OrderListExportDTO> ls = orderMapper.selectOrderExportList(dto);
+ return ls;
+ }
+
+ public void exportOrderDetail(HttpServletResponse response, OrderQueryDTO dto) {
+ List<OrderListExportDTO> ls = getOrderExportListBase( dto);
+ String[] rowsName = new String[]{"序号","订单号", "用户账号", "收货人", "收货人电话", "收获地址", "订单金额"
+ , "底价", "订单状态", "下单时间", "合伙人", "库位", "特殊需求", "备注"};
+ List<Object[]> dataList = new ArrayList<>();
+ int sn = 1;
+ for (OrderListExportDTO o : ls) {
+ Object[] objs = new Object[rowsName.length];
+ int a = 0;
+ objs[a++] = sn;
+ objs[a++] = o.getOrderNo();
+ objs[a++] = o.getCreateName();
+ objs[a++] = o.getCustomer();
+ objs[a++] = o.getCustomerTel();
+ objs[a++] = o.getCustomerAddress();
+ objs[a++] = o.getTotalAmount();
+ objs[a++] = o.getSupplierAmount();
+ objs[a++] = o.getStatusBackendStr();
+ objs[a++] = o.getCreateTime();
+ objs[a++] = o.getPartnerName();
+ objs[a++] = o.getWarehouseLocationCode();
+ objs[a++] = o.getSpecialNeedsStr();
+ objs[a++] = o.getRemarks();
+ dataList.add(objs);
+
+ sn++;
+ }
+ ExcelExportUtil excelExportUtil = new ExcelExportUtil("订单列表", rowsName, dataList, response);
+ try {
+ response.addHeader("filename", URLEncoder.encode("订单列表.xls", "UTF-8"));
+ response.addHeader("Access-Control-Expose-Headers", "filename");
+ excelExportUtil.export();
+ } catch (Exception e) {
+ log.error(e.getMessage(), e);
+ }
+
+ }
+
}
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 16ed168..0d9d2d6 100644
--- a/src/main/java/com/mzl/flower/web/payment/OrderController.java
+++ b/src/main/java/com/mzl/flower/web/payment/OrderController.java
@@ -22,6 +22,7 @@
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
+import javax.servlet.http.HttpServletResponse;
import java.util.List;
@RestController
@@ -51,7 +52,6 @@
public ResponseEntity<ReturnDataDTO<Page<OrderPlatformListDTO>>> selectOrderList(Page page, OrderQueryDTO dto){
return returnData(R.SUCCESS.getCode(), orderService.selectOrderPlatformList(page, dto));
}
-
@GetMapping("/list/view")
@ApiOperation(value = "订单详情")
@ApiImplicitParams({
@@ -188,4 +188,9 @@
return returnData(R.SUCCESS.getCode(),null);
}
+ @GetMapping("/list/export")
+ @ApiOperation(value = "订单导出")
+ public void exportOrderDetail(HttpServletResponse response, OrderQueryDTO queryOrderDTO) {
+ orderService.exportOrderDetail(response, queryOrderDTO);
+ }
}
diff --git a/src/main/resources/mapper/payment/OrderMapper.xml b/src/main/resources/mapper/payment/OrderMapper.xml
index 4d7d43a..17f9cde 100644
--- a/src/main/resources/mapper/payment/OrderMapper.xml
+++ b/src/main/resources/mapper/payment/OrderMapper.xml
@@ -275,4 +275,84 @@
AND oi.flower_id=#{flowerId}
AND o.payment_time BETWEEN #{startTime} AND #{endTime}
</select>
+
+ <select id="selectOrderExportList" resultType="com.mzl.flower.dto.response.payment.OrderListExportDTO">
+ SELECT q.order_no,q.create_time,q.customer,q.customer_tel,q.total_amount,q.status_backend,q.partner_name,q.warehouse_location_code,q.remarks,
+ u.nick_name createName ,
+ code.label as statusBackendStr,
+ code2.label as specialNeedsStr,
+ CONCAT(q.customer_province, q.customer_city,q.customer_region,q.customer_address) AS customerAddress,
+ (select Sum(COALESCE(oi.num, 0) * COALESCE(oi.supplier_price, 0))
+ from t_order_item oi where order_id = q.id) supplierAmount
+ FROM t_order q
+ left join t_user u on q.create_by = u.id
+ LEFT JOIN (SELECT ct.label, ct.value from t_code_value ct where type_code = 'ORDER_STATUS_BACKEND') code ON q.status_backend = code.value
+ LEFT JOIN (SELECT ct.label, ct.value from t_code_value ct where type_code = 'SPEC_REQ') code2 ON q.special_needs = code2.value
+ WHERE q.deleted = 0
+ <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.address != null and condition.address != ''">
+ AND CONCAT(q.customer_province, q.customer_city, q.customer_region, q.customer_address) LIKE concat('%',
+ #{condition.address},'%')
+ </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.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>
+ ORDER BY q.create_time desc, q.payment_time desc
+ </select>
</mapper>
\ No newline at end of file
--
Gitblit v1.9.3