add:订单列表导出

cloudroam

2024-09-30 b14befec1ef345d88a04da3ca1e10a006a7862e5

add:订单列表导出

 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;

}

 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);

}

 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);
        }

    }

}

 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);
    }
}

 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 &gt;= #{condition.startDate}
        </if>
        <if test="condition.endDate != null">
            AND q.payment_time &lt;= #{condition.endDate}
        </if>
        <if test="condition.createStartDate != null">
            AND q.create_time &gt;= #{condition.createStartDate}
        </if>
        <if test="condition.createEndDate != null">
            AND q.create_time &lt;= #{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>