feat:1.销售台账导出按照查询条件 2.导出订单和产品明细

liding

9 天以前 6305d0c86c23e9a583e8ca798645885d167f4dc5

feat:1.销售台账导出按照查询条件
2.导出订单和产品明细

 src/main/java/com/ruoyi/sales/controller/SalesLedgerController.java

@@ -161,6 +161,16 @@
    }

    /**
     * å¯¼å‡ºé”€å”®å°è´¦åˆ—表（包含产品明细，两个sheet页）
     */
    @Log(title = "销售台账", businessType = BusinessType.EXPORT)
    @PostMapping("/exportWithProducts")
    @ApiOperation("导出销售台账及产品明细（两个sheet页）")
    public void exportWithProducts(HttpServletResponse response, SalesLedgerDto salesLedgerDto) {
        salesLedgerService.exportWithProducts(response, salesLedgerDto);
    }

    /**
     * å¯¼å‡ºå¼€ç¥¨ç™»è®°åˆ—表
     */
    @Log(title = "导出开票登记列表", businessType = BusinessType.EXPORT)

 src/main/java/com/ruoyi/sales/dto/SalesLedgerExportDto.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,62 @@
package com.ruoyi.sales.dto;

import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

/**
 * é”€å”®å°è´¦å¯¼å‡ºDTO
 */
@Data
public class SalesLedgerExportDto {

    @Excel(name = "销售合同号")
    private String salesContractNo;

    @Excel(name = "客户合同号")
    private String customerContractNo;

    @Excel(name = "项目名称")
    private String projectName;

    @Excel(name = "客户名称")
    private String customerName;

    @Excel(name = "业务员")
    private String salesman;

    @Excel(name = "录入人")
    private String entryPersonName;

    @Excel(name = "录入日期", width = 30, dateFormat = "yyyy-MM-dd")
    private Date entryDate;

    @Excel(name = "签订日期", width = 30, dateFormat = "yyyy-MM-dd")
    private Date executionDate;

    @Excel(name = "交货日期", width = 30, dateFormat = "yyyy-MM-dd")
    private Date deliveryDate;

    @Excel(name = "合同金额(元)")
    private BigDecimal contractAmount;

    @Excel(name = "发货状态")
    private String deliveryStatusText;

    @Excel(name = "入库状态")
    private String stockStatusText;

    @Excel(name = "审核状态")
    private String reviewStatusText;

    @Excel(name = "订单状态")
    private String orderStatusText;

    @Excel(name = "备注")
    private String remarks;

    @Excel(name = "客户备注")
    private String customerRemarks;
}

 src/main/java/com/ruoyi/sales/dto/SalesLedgerProductExportDto.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,64 @@
package com.ruoyi.sales.dto;

import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import lombok.Data;

import java.math.BigDecimal;

/**
 * é”€å”®å°è´¦äº§å“å¯¼å‡ºDTO
 */
@Data
public class SalesLedgerProductExportDto {

    @Excel(name = "销售合同号")
    private String salesContractNo;

    @Excel(name = "产品大类")
    private String productCategory;

    @Excel(name = "规格型号")
    private String specificationModel;

    @Excel(name = "厚度(mm)")
    private BigDecimal thickness;

    @Excel(name = "楼层编号")
    private String floorCode;

    @Excel(name = "宽(mm)")
    private BigDecimal width;

    @Excel(name = "高(mm)")
    private BigDecimal height;

    @Excel(name = "数量")
    private BigDecimal quantity;

    @Excel(name = "结算单片面积(㎡)")
    private BigDecimal settlePieceArea;

    @Excel(name = "结算总面积(㎡)")
    private BigDecimal settleTotalArea;

    @Excel(name = "含税单价(元)")
    private BigDecimal taxInclusiveUnitPrice;

    @Excel(name = "税率(%)")
    private BigDecimal taxRate;

    @Excel(name = "含税总价(元)")
    private BigDecimal taxInclusiveTotalPrice;

    @Excel(name = "不含税总价(元)")
    private BigDecimal taxExclusiveTotalPrice;

    @Excel(name = "发票类型")
    private String invoiceType;

    @Excel(name = "加工要求")
    private String processRequirement;

    @Excel(name = "备注")
    private String remark;
}

 src/main/java/com/ruoyi/sales/service/ISalesLedgerService.java

@@ -3,9 +3,9 @@
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.ruoyi.approve.pojo.ApproveProcess;
import com.ruoyi.basic.pojo.Customer;
import com.ruoyi.common.enums.SaleEnum;
import com.ruoyi.approve.pojo.ApproveProcess;
import com.ruoyi.sales.dto.*;
import com.ruoyi.sales.pojo.SalesLedger;
import com.ruoyi.sales.pojo.SalesLedgerProcessRoute;
@@ -13,6 +13,7 @@
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Nullable;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.math.BigDecimal;
import java.util.List;
@@ -118,4 +119,11 @@
     * @param printType æ‰“印类型：label=标签打印，document=单据打印
     */
    void incrementPrintCount(Long id, String printType);

    /**
     * å¯¼å‡ºé”€å”®å°è´¦åŠäº§å“æ˜Žç»†ï¼ˆä¸¤ä¸ªsheet页）
     * @param response HttpServletResponse
     * @param salesLedgerDto æŸ¥è¯¢æ¡ä»¶
     */
    void exportWithProducts(HttpServletResponse response, SalesLedgerDto salesLedgerDto);
}

 src/main/java/com/ruoyi/sales/service/impl/SalesLedgerServiceImpl.java

@@ -78,11 +78,13 @@
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
@@ -3694,4 +3696,235 @@
            );
        }
    }

    @Override
    public void exportWithProducts(HttpServletResponse response, SalesLedgerDto salesLedgerDto) {
        try {
            // 1. æŸ¥è¯¢é”€å”®å°è´¦åˆ—表（导出使用升序排序）
            Page<SalesLedger> page = new Page<>(-1, -1);
            // ä½¿ç”¨ Wrappers æž„建升序查询
            LambdaQueryWrapper<SalesLedger> queryWrapper = Wrappers.<SalesLedger>lambdaQuery()
                .orderByAsc(SalesLedger::getEntryDate)
                .orderByAsc(SalesLedger::getId);

            // æ·»åŠ æŸ¥è¯¢æ¡ä»¶
            if (salesLedgerDto.getCustomerName() != null && !salesLedgerDto.getCustomerName().isEmpty()) {
                queryWrapper.like(SalesLedger::getCustomerName, salesLedgerDto.getCustomerName());
            }
            if (salesLedgerDto.getSalesContractNo() != null && !salesLedgerDto.getSalesContractNo().isEmpty()) {
                queryWrapper.like(SalesLedger::getSalesContractNo, salesLedgerDto.getSalesContractNo());
            }
            if (salesLedgerDto.getProjectName() != null && !salesLedgerDto.getProjectName().isEmpty()) {
                queryWrapper.like(SalesLedger::getProjectName, salesLedgerDto.getProjectName());
            }
            if (salesLedgerDto.getEntryDateStart() != null && !salesLedgerDto.getEntryDateStart().isEmpty()) {
                queryWrapper.ge(SalesLedger::getEntryDate, salesLedgerDto.getEntryDateStart());
            }
            if (salesLedgerDto.getEntryDateEnd() != null && !salesLedgerDto.getEntryDateEnd().isEmpty()) {
                queryWrapper.le(SalesLedger::getEntryDate, salesLedgerDto.getEntryDateEnd());
            }
            if (salesLedgerDto.getDeliveryStatus() != null) {
                queryWrapper.eq(SalesLedger::getDeliveryStatus, salesLedgerDto.getDeliveryStatus());
            }
            if (salesLedgerDto.getStockStatus() != null) {
                queryWrapper.eq(SalesLedger::getStockStatus, salesLedgerDto.getStockStatus());
            }
            if (salesLedgerDto.getReviewStatus() != null) {
                queryWrapper.eq(SalesLedger::getReviewStatus, salesLedgerDto.getReviewStatus());
            }
            if (salesLedgerDto.getOrderStatus() != null) {
                queryWrapper.eq(SalesLedger::getOrderStatus, salesLedgerDto.getOrderStatus());
            }
            if (salesLedgerDto.getReviewStatusList() != null && !salesLedgerDto.getReviewStatusList().isEmpty()) {
                queryWrapper.and(w -> w.in(SalesLedger::getReviewStatus, salesLedgerDto.getReviewStatusList())
                    .or().isNull(SalesLedger::getReviewStatus));
            }

            List<SalesLedger> ledgerList = salesLedgerMapper.selectList(page, queryWrapper);

            // 2. æ”¶é›†æ•°æ®
            List<SalesLedgerExportDto> ledgerExportList = new ArrayList<>();
            List<SalesLedgerProductExportDto> productExportList = new ArrayList<>();

            for (SalesLedger ledger : ledgerList) {
                // è½¬æ¢å°è´¦æ•°æ®
                SalesLedgerExportDto ledgerDto = new SalesLedgerExportDto();
                ledgerDto.setSalesContractNo(ledger.getSalesContractNo());
                ledgerDto.setCustomerContractNo(ledger.getCustomerContractNo());
                ledgerDto.setProjectName(ledger.getProjectName());
                ledgerDto.setCustomerName(ledger.getCustomerName());
                ledgerDto.setSalesman(ledger.getSalesman());
                ledgerDto.setEntryPersonName(ledger.getEntryPersonName());
                ledgerDto.setEntryDate(ledger.getEntryDate());
                ledgerDto.setExecutionDate(ledger.getExecutionDate() != null ?
                    java.sql.Date.valueOf(ledger.getExecutionDate()) : null);
                ledgerDto.setDeliveryDate(ledger.getDeliveryDate() != null ?
                    java.sql.Date.valueOf(ledger.getDeliveryDate()) : null);
                ledgerDto.setContractAmount(ledger.getContractAmount());
                ledgerDto.setRemarks(ledger.getRemarks());
                ledgerDto.setCustomerRemarks(ledger.getCustomerRemarks());
                ledgerDto.setDeliveryStatusText(getDeliveryStatusText(ledger.getDeliveryStatus()));
                ledgerDto.setStockStatusText(getStockStatusText(ledger.getStockStatus()));
                ledgerDto.setReviewStatusText(getReviewStatusText(ledger.getReviewStatus()));
                ledgerDto.setOrderStatusText(getOrderStatusText(ledger.getOrderStatus()));
                ledgerExportList.add(ledgerDto);

                // æŸ¥è¯¢è¯¥å°è´¦çš„产品列表
                List<SalesLedgerProduct> products = salesLedgerProductMapper.selectList(
                    Wrappers.<SalesLedgerProduct>lambdaQuery()
                        .eq(SalesLedgerProduct::getSalesLedgerId, ledger.getId())
                        .eq(SalesLedgerProduct::getType, 1)
                );

                for (SalesLedgerProduct product : products) {
                    SalesLedgerProductExportDto productDto = new SalesLedgerProductExportDto();
                    productDto.setSalesContractNo(ledger.getSalesContractNo());
                    productDto.setProductCategory(product.getProductCategory());
                    productDto.setSpecificationModel(product.getSpecificationModel());
                    productDto.setThickness(product.getThickness());
                    productDto.setFloorCode(product.getFloorCode());
                    productDto.setWidth(product.getWidth());
                    productDto.setHeight(product.getHeight());
                    productDto.setQuantity(product.getQuantity());
                    productDto.setSettlePieceArea(product.getSettlePieceArea());
                    productDto.setSettleTotalArea(product.getSettleTotalArea());
                    productDto.setTaxInclusiveUnitPrice(product.getTaxInclusiveUnitPrice());
                    productDto.setTaxRate(product.getTaxRate());
                    productDto.setTaxInclusiveTotalPrice(product.getTaxInclusiveTotalPrice());
                    productDto.setTaxExclusiveTotalPrice(product.getTaxExclusiveTotalPrice());
                    productDto.setInvoiceType(product.getInvoiceType());
                    productDto.setProcessRequirement(product.getProcessRequirement());
                    productDto.setRemark(product.getRemark());
                    productExportList.add(productDto);
                }
            }

            // 3. ä½¿ç”¨ExcelUtil导出（内部会创建Workbook并写入响应）
            // å…ˆåˆ›å»ºä¸´æ—¶æ–‡ä»¶ï¼Œå†åˆå¹¶ä¸¤ä¸ªsheet
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("销售台账.xlsx", "utf-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);

            org.apache.poi.xssf.usermodel.XSSFWorkbook workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook();

            // Sheet1: é”€å”®å°è´¦ - æ‰‹åŠ¨å¡«å……
            fillSheetWithData(workbook, "销售台账", SalesLedgerExportDto.class, ledgerExportList);

            // Sheet2: äº§å“æ˜Žç»† - æ‰‹åŠ¨å¡«å……
            fillSheetWithData(workbook, "产品明细", SalesLedgerProductExportDto.class, productExportList);

            workbook.write(response.getOutputStream());
            workbook.close();

        } catch (Exception e) {
            log.error("导出销售台账失败", e);
            throw new ServiceException("导出失败：" + e.getMessage());
        }
    }

    /**
     * æ‰‹åŠ¨å¡«å……Sheet数据
     */
    private <T> void fillSheetWithData(org.apache.poi.xssf.usermodel.XSSFWorkbook workbook, String sheetName, Class<T> clazz, List<T> dataList) throws Exception {
        org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet(sheetName);

        // èŽ·å–字段上的@Excel注解
        java.lang.reflect.Field[] fields = clazz.getDeclaredFields();
        List<java.lang.reflect.Field> excelFields = new ArrayList<>();
        for (java.lang.reflect.Field field : fields) {
            com.ruoyi.framework.aspectj.lang.annotation.Excel excel = field.getAnnotation(com.ruoyi.framework.aspectj.lang.annotation.Excel.class);
            if (excel != null) {
                excelFields.add(field);
            }
        }

        // åˆ›å»ºè¡¨å¤´
        org.apache.poi.ss.usermodel.Row headerRow = sheet.createRow(0);
        org.apache.poi.ss.usermodel.CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(org.apache.poi.ss.usermodel.IndexedColors.GREY_50_PERCENT.getIndex());
        headerStyle.setFillPattern(org.apache.poi.ss.usermodel.FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);
        org.apache.poi.ss.usermodel.Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        for (int i = 0; i < excelFields.size(); i++) {
            java.lang.reflect.Field field = excelFields.get(i);
            field.setAccessible(true);
            com.ruoyi.framework.aspectj.lang.annotation.Excel excel = field.getAnnotation(com.ruoyi.framework.aspectj.lang.annotation.Excel.class);
            org.apache.poi.ss.usermodel.Cell cell = headerRow.createCell(i);
            cell.setCellValue(excel.name());
            cell.setCellStyle(headerStyle);
            sheet.setColumnWidth(i, 20 * 256);
        }

        // åˆ›å»ºæ•°æ®è¡Œ
        org.apache.poi.ss.usermodel.CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER);

        for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
            T data = dataList.get(rowIndex);
            org.apache.poi.ss.usermodel.Row row = sheet.createRow(rowIndex + 1);
            for (int colIndex = 0; colIndex < excelFields.size(); colIndex++) {
                java.lang.reflect.Field field = excelFields.get(colIndex);
                field.setAccessible(true);
                Object value = field.get(data);
                org.apache.poi.ss.usermodel.Cell cell = row.createCell(colIndex);
                if (value == null) {
                    cell.setCellValue("");
                } else if (value instanceof Number) {
                    cell.setCellValue(((Number) value).doubleValue());
                } else if (value instanceof Date) {
                    cell.setCellValue(new java.text.SimpleDateFormat("yyyy-MM-dd").format((Date) value));
                } else {
                    cell.setCellValue(value.toString());
                }
                cell.setCellStyle(dataStyle);
            }
        }
    }

    private String getDeliveryStatusText(Integer status) {
        if (status == null) return "未知";
        switch (status) {
            case 1: return "未发货";
            case 2: return "审批中";
            case 3: return "审批不通过";
            case 4: return "审批通过";
            case 5: return "已发货";
            case 6: return "部分发货";
            default: return "未知";
        }
    }

    private String getStockStatusText(Integer status) {
        if (status == null) return "未知";
        switch (status) {
            case 0: return "未入库";
            case 1: return "部分入库";
            case 2: return "已入库";
            case 3: return "审批中";
            default: return "未知";
        }
    }

    private String getReviewStatusText(Integer status) {
        if (status == null) return "待审核";
        switch (status) {
            case 0: return "待审核";
            case 1: return "已审核";
            case 2: return "已反审";
            default: return "待审核";
        }
    }

    private String getOrderStatusText(Integer status) {
        if (status == null || status == 0) return "进行中";
        switch (status) {
            case 0: return "进行中";
            case 1: return "已完成";
            default: return "进行中";
        }
    }
}

 src/main/resources/mapper/sales/SalesLedgerProductMapper.xml

@@ -24,7 +24,7 @@
                AND T1.type = #{salesLedgerProduct.type}
            </if>
        </where>
        ORDER BY T1.register_date DESC
        ORDER BY T1.register_date ASC,T1.id ASC
    </select>

    <select id="selectSalesLedgerProductByMainId" resultType="com.ruoyi.sales.pojo.SalesLedgerProduct">

 src/main/resources/mapper/sales/ShippingInfoMapper.xml

@@ -1,4 +1,5 @@
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.sales.mapper.ShippingInfoMapper">

@@ -57,7 +58,8 @@
            sl.sales_contract_no,
            sl.customer_name
        FROM shipping_info s
                 LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
        LEFT JOIN sales_ledger sl ON s.sales_ledger_id = sl.id
        ORDER BY s.create_time ASC
    </select>

    <select id="getReturnManagementDtoById" resultType="com.ruoyi.sales.dto.SalesLedgerProductDto">