yys 销售台账导入接口

maven

9 天以前 5fa5d9beee2594461871ae6bd6e24ddefc12b8ff

yys 销售台账导入接口

 src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java

@@ -526,6 +526,176 @@
    }



    /**

     * æ‰©å±•ï¼šè¯»å–Excel中多个指定Sheet的数据

     * @param sheetNameList è¦è¯»å–çš„Sheet名称列表（null则读取所有Sheet）

     * @param is è¾“入流

     * @param titleNum æ ‡é¢˜å ç”¨è¡Œæ•°

     * @return Map<Sheet名称, å¯¹åº”Sheet的数据列表>

     */

    public Map<String, List<T>> importExcelMultiSheet(List<String> sheetNameList, InputStream is, int titleNum) {

        Map<String, List<T>> resultMap = new HashMap<>();

        try {

            this.type = Type.IMPORT;

            this.wb = WorkbookFactory.create(is);



            // 1. ç¡®å®šè¦è¯»å–çš„Sheet列表

            List<Sheet> sheetsToRead = new ArrayList<>();

            if (sheetNameList != null && !sheetNameList.isEmpty()) {

                // è¯»å–指定名称的Sheet

                for (String sheetName : sheetNameList) {

                    Sheet sheet = wb.getSheet(sheetName);

                    if (sheet != null) {

                        sheetsToRead.add(sheet);

                    } else {

                        log.warn("指定的Sheet名称不存在：{}", sheetName);

                    }

                }

            } else {

                // è¯»å–所有Sheet

                int sheetCount = wb.getNumberOfSheets();

                for (int i = 0; i < sheetCount; i++) {

                    sheetsToRead.add(wb.getSheetAt(i));

                }

            }



            // 2. éåŽ†æ¯ä¸ªSheet，复用原有导入逻辑

            for (Sheet sheet : sheetsToRead) {

                String sheetName = wb.getSheetName(wb.getSheetIndex(sheet));

                // å¤ç”¨åŽŸæœ‰æ ¸å¿ƒå¯¼å…¥é€»è¾‘（关键：将原有方法拆分为可复用的内部方法）

                List<T> sheetData = importExcelBySheet(sheet, titleNum);

                resultMap.put(sheetName, sheetData);

            }

        } catch (Exception e) {

            log.error("导入多Sheet Excel异常{}", e.getMessage());

            throw new UtilException(e.getMessage());

        } finally {

            IOUtils.closeQuietly(is);

        }

        return resultMap;

    }



    /**

     * å†…部复用方法：根据指定Sheet对象读取数据（提取原有importExcel的核心逻辑）

     */

    private List<T> importExcelBySheet(Sheet sheet, int titleNum) throws Exception {

        List<T> list = new ArrayList<T>();

        if (sheet == null) {

            return list;

        }



        boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);

        Map<String, List<PictureData>> pictures = null;

        if (isXSSFWorkbook) {

            pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);

        } else {

            pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);

        }



        int rows = sheet.getLastRowNum();

        if (rows > 0) {

            Map<String, Integer> cellMap = new HashMap<String, Integer>();

            Row heard = sheet.getRow(titleNum);

            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {

                Cell cell = heard.getCell(i);

                if (StringUtils.isNotNull(cell)) {

                    String value = this.getCellValue(heard, i).toString();

                    cellMap.put(value, i);

                } else {

                    cellMap.put(null, i);

                }

            }



            List<Object[]> fields = this.getFields();

            Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();

            for (Object[] objects : fields) {

                Excel attr = (Excel) objects[1];

                Integer column = cellMap.get(attr.name());

                if (column != null) {

                    fieldsMap.put(column, objects);

                }

            }



            for (int i = titleNum + 1; i <= rows; i++) {

                Row row = sheet.getRow(i);

                if (isRowEmpty(row)) {

                    continue;

                }

                T entity = null;

                for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {

                    Object val = this.getCellValue(row, entry.getKey());

                    entity = (entity == null ? clazz.newInstance() : entity);

                    Field field = (Field) entry.getValue()[0];

                    Excel attr = (Excel) entry.getValue()[1];

                    Class<?> fieldType = field.getType();



                    // ä»¥ä¸‹æ˜¯åŽŸæœ‰æ•°æ®ç±»åž‹è½¬æ¢ã€å­—典解析等逻辑（完全复用）

                    if (String.class == fieldType) {

                        String s = Convert.toStr(val);

                        if (s.matches("^\\d+\\.0$")) {

                            val = StringUtils.substringBefore(s, ".0");

                        } else {

                            String dateFormat = field.getAnnotation(Excel.class).dateFormat();

                            if (StringUtils.isNotEmpty(dateFormat)) {

                                val = parseDateToStr(dateFormat, val);

                            } else {

                                val = Convert.toStr(val);

                            }

                        }

                    } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {

                        val = Convert.toInt(val);

                    } else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {

                        val = Convert.toLong(val);

                    } else if (Double.TYPE == fieldType || Double.class == fieldType) {

                        val = Convert.toDouble(val);

                    } else if (Float.TYPE == fieldType || Float.class == fieldType) {

                        val = Convert.toFloat(val);

                    } else if (BigDecimal.class == fieldType) {

                        val = Convert.toBigDecimal(val);

                    } else if (Date.class == fieldType) {

                        if (val instanceof String) {

                            val = DateUtils.parseDate(val);

                        } else if (val instanceof Double) {

                            val = DateUtil.getJavaDate((Double) val);

                        }

                    } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {

                        val = Convert.toBool(val, false);

                    }



                    if (StringUtils.isNotNull(fieldType)) {

                        String propertyName = field.getName();

                        if (StringUtils.isNotEmpty(attr.targetAttr())) {

                            propertyName = field.getName() + "." + attr.targetAttr();

                        }

                        if (StringUtils.isNotEmpty(attr.readConverterExp())) {

                            val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());

                        } else if (StringUtils.isNotEmpty(attr.dictType())) {

                            if (!sysDictMap.containsKey(attr.dictType() + val)) {

                                String dictValue = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());

                                sysDictMap.put(attr.dictType() + val, dictValue);

                            }

                            val = sysDictMap.get(attr.dictType() + val);

                        } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {

                            val = dataFormatHandlerAdapter(val, attr, null);

                        } else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures)) {

                            StringBuilder propertyString = new StringBuilder();

                            List<PictureData> images = pictures.get(row.getRowNum() + "_" + entry.getKey());

                            for (PictureData picture : images) {

                                byte[] data = picture.getData();

                                String fileName = FileUtils.writeImportBytes(data);

                                propertyString.append(fileName).append(SEPARATOR);

                            }

                            val = StringUtils.stripEnd(propertyString.toString(), SEPARATOR);

                        }

                        ReflectUtils.invokeSetter(entity, propertyName, val);

                    }

                }

                list.add(entity);

            }

        }

        return list;

    }



    /**

     * å¯¹list数据源将其里面的数据导入到excel表单

     * 

     * @param list å¯¼å‡ºæ•°æ®é›†åˆ


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

@@ -21,10 +21,14 @@
import com.ruoyi.sales.pojo.SalesLedger;
import com.ruoyi.sales.service.ICommonFileService;
import com.ruoyi.sales.service.ISalesLedgerService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
@@ -43,6 +47,7 @@
@RestController
@RequestMapping("/sales/ledger")
@AllArgsConstructor
@Api(tags = "销售台账")
public class SalesLedgerController extends BaseController {

    private ISalesLedgerService salesLedgerService;
@@ -59,6 +64,18 @@
    private ReceiptPaymentMapper receiptPaymentMapper;

    /**
     * å¯¼å…¥é”€å”®å°è´¦
     */
    @Log(title = "导入销售台账", businessType = BusinessType.INSERT)
    @PostMapping("/import")
    @ApiOperation("导入销售台账")
    public AjaxResult importData(@RequestParam("file")
                                 @ApiParam(value = "Excel文件", required = true)
                                 MultipartFile file) {
        return salesLedgerService.importData(file);
    }

    /**
     * æŸ¥è¯¢é”€å”®å°è´¦åˆ—表
     */
    @GetMapping("/list")

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

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

import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import com.ruoyi.sales.pojo.CommonFile;
import com.ruoyi.sales.pojo.SalesLedgerProduct;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.Date;
import java.util.List;

/**
 * @author :yys
 * @date : 2026/1/19 9:50
 */
@Data
public class SalesLedgerImportDto extends SalesLedgerProductImportDto{

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

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

    @Excel(name = "业务员")
    private String salesman;
    @Excel(name = "客户名称")
    private String customerName;
    @Excel(name = "录入人")
    private String entryPerson;
    @Excel(name = "备注")
    private String remarks;

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

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


}

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

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

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import lombok.Data;

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

/**
 * @author :yys
 * @date : 2026/1/19 9:59
 */
@Data
public class SalesLedgerProductImportDto {

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


    /**
     * äº§å“å¤§ç±»
     */
    @Excel(name = "产品大类")
    private String productCategory;

    /**
     * è§„格型号
     */
    @Excel(name = "规格型号")
    private String specificationModel;

    /**
     * å•ä½
     */
    @Excel(name = "单位")
    private String unit;

    /**
     * æ•°é‡
     */
    @Excel(name = "数量")
    private BigDecimal quantity;

    /**
     * ç¨ŽçŽ‡
     */
    @Excel(name = "税率")
    private BigDecimal taxRate;

    /**
     * å«ç¨Žå•ä»·
     */
    @Excel(name = "含税单价")
    private BigDecimal taxInclusiveUnitPrice;

    /**
     * å«ç¨Žæ€»ä»·
     */
    @Excel(name = "含税总价")
    private BigDecimal taxInclusiveTotalPrice;

    /**
     * å‘票类型
     */
    @Excel(name = "发票类型")
    private String invoiceType;

    /**
     * æ˜¯å¦è´¨æ£€
     */
    @Excel(name = "是否质检", readConverterExp = "0=否,1=是")
    private Boolean isChecked;



}

 src/main/java/com/ruoyi/sales/pojo/SalesLedgerProduct.java

@@ -129,16 +129,16 @@
    private BigDecimal futureTicketsAmount=BigDecimal.ZERO;

    @ApiModelProperty(value = "开票数")
    private BigDecimal invoiceNum;
    private BigDecimal invoiceNum = BigDecimal.ZERO;

    @ApiModelProperty(value = "未开票数")
    private BigDecimal noInvoiceNum;
    private BigDecimal noInvoiceNum = BigDecimal.ZERO;

    @ApiModelProperty(value = "开票金额")
    private BigDecimal invoiceAmount;
    private BigDecimal invoiceAmount = BigDecimal.ZERO;

    @ApiModelProperty(value = "未开票金额")
    private BigDecimal noInvoiceAmount;
    private BigDecimal noInvoiceAmount = BigDecimal.ZERO;

    @ApiModelProperty(value = "本次开票数")
    @TableField(exist = false)

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

@@ -3,9 +3,11 @@
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.framework.web.domain.AjaxResult;
import com.ruoyi.sales.dto.MonthlyAmountDto;
import com.ruoyi.sales.dto.SalesLedgerDto;
import com.ruoyi.sales.pojo.SalesLedger;
import org.springframework.web.multipart.MultipartFile;

import java.math.BigDecimal;
import java.util.List;
@@ -35,4 +37,6 @@
    List<MonthlyAmountDto> getAmountHalfYear(Integer type);

    IPage<SalesLedger> selectSalesLedgerListPage(Page page, SalesLedgerDto salesLedgerDto);

    AjaxResult importData(MultipartFile file);
}

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

@@ -12,24 +12,33 @@
import com.ruoyi.account.pojo.AccountIncome;
import com.ruoyi.account.service.AccountIncomeService;
import com.ruoyi.basic.mapper.CustomerMapper;
import com.ruoyi.basic.mapper.ProductMapper;
import com.ruoyi.basic.mapper.ProductModelMapper;
import com.ruoyi.basic.pojo.Customer;
import com.ruoyi.basic.pojo.Product;
import com.ruoyi.basic.pojo.ProductModel;
import com.ruoyi.common.enums.FileNameType;
import com.ruoyi.common.exception.base.BaseException;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.framework.security.LoginUser;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.other.mapper.TempFileMapper;
import com.ruoyi.other.pojo.TempFile;
import com.ruoyi.production.dto.ProductStructureDto;
import com.ruoyi.production.mapper.*;
import com.ruoyi.production.pojo.*;
import com.ruoyi.project.system.domain.SysDept;
import com.ruoyi.project.system.domain.SysUser;
import com.ruoyi.project.system.mapper.SysDeptMapper;
import com.ruoyi.project.system.mapper.SysUserMapper;
import com.ruoyi.quality.mapper.QualityInspectMapper;
import com.ruoyi.quality.pojo.QualityInspect;
import com.ruoyi.sales.dto.MonthlyAmountDto;
import com.ruoyi.sales.dto.SalesLedgerDto;
import com.ruoyi.sales.dto.SalesLedgerImportDto;
import com.ruoyi.sales.dto.SalesLedgerProductImportDto;
import com.ruoyi.sales.mapper.*;
import com.ruoyi.sales.pojo.*;
import com.ruoyi.sales.service.ISalesLedgerProductService;
@@ -44,10 +53,13 @@
import org.springframework.data.redis.core.script.DefaultRedisScript;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
@@ -130,6 +142,9 @@
    private final RedisTemplate<String, String> redisTemplate;
    @Autowired
    private ProductModelMapper productModelMapper;

    @Autowired
    private ProductMapper productMapper;
    @Autowired
    private ProductStructureMapper productStructureMapper;

@@ -325,6 +340,99 @@
        return salesLedgerMapper.selectSalesLedgerListPage(page, salesLedgerDto);
    }

    @Autowired
    private SysUserMapper sysUserMapper;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult importData(MultipartFile file) {
        LoginUser loginUser = SecurityUtils.getLoginUser();
        try {
            InputStream inputStream = file.getInputStream();
            ExcelUtil<SalesLedgerImportDto> salesLedgerImportDtoExcelUtil = new ExcelUtil<>(SalesLedgerImportDto.class);
            Map<String, List<SalesLedgerImportDto>> stringListMap = salesLedgerImportDtoExcelUtil.importExcelMultiSheet(Arrays.asList("销售台账数据","销售产品数据"), inputStream, 0);
            if(CollectionUtils.isEmpty(stringListMap)) return AjaxResult.error("销售表格为空！");
            // ä¸šåŠ¡å±‚合并
            List<SalesLedgerImportDto> salesLedgerImportDtoList = stringListMap.get("销售台账数据");
            if(CollectionUtils.isEmpty(salesLedgerImportDtoList)) return AjaxResult.error("销售台账数据为空！");
            List<SalesLedgerImportDto> salesLedgerProductImportDtoList = stringListMap.get("销售产品数据");
            if(CollectionUtils.isEmpty(salesLedgerProductImportDtoList)) return AjaxResult.error("销售产品数据为空！");
            // å®¢æˆ·æ•°æ®
            List<Customer> customers = customerMapper.selectList(new LambdaQueryWrapper<Customer>().in(Customer::getCustomerName,
                    salesLedgerImportDtoList.stream().map(SalesLedgerImportDto::getCustomerName).toArray(String[]::new)));
            // è§„格型号数据
            List<ProductModel> productModels = productModelMapper.selectList(new LambdaQueryWrapper<ProductModel>().in(ProductModel::getModel,
                    salesLedgerImportDtoList.stream().map(SalesLedgerImportDto::getSpecificationModel).toArray(String[]::new)));
            // äº§å“å¤§ç±»æ•°æ®
            List<Product> productList = productMapper.selectList(new LambdaQueryWrapper<Product>().in(Product::getProductName,
                    salesLedgerImportDtoList.stream().map(SalesLedgerImportDto::getProductCategory).toArray(String[]::new)));
            // å½•å…¥äººæ•°æ®
            List<SysUser> sysUsers = sysUserMapper.selectList(new LambdaQueryWrapper<SysUser>().in(SysUser::getNickName,
                    salesLedgerImportDtoList.stream().map(SalesLedgerImportDto::getEntryPerson).toArray(String[]::new)));
            for (SalesLedgerImportDto salesLedgerImportDto : salesLedgerImportDtoList) {
                SalesLedger salesLedger = new SalesLedger();
                BeanUtils.copyProperties(salesLedgerImportDto, salesLedger);
                // é€šè¿‡å®¢æˆ·åç§°æŸ¥è¯¢å®¢æˆ·ID，客户合同号
                salesLedger.setCustomerId(customers.stream()
                        .filter(customer -> customer.getCustomerName().equals(salesLedger.getCustomerName()))
                        .findFirst()
                        .map(Customer::getId)
                        .orElse(null));
                salesLedger.setCustomerContractNo(customers.stream()
                        .filter(customer -> customer.getCustomerName().equals(salesLedger.getCustomerName()))
                        .findFirst()
                        .map(Customer::getTaxpayerIdentificationNumber)
                        .orElse(null));
                Long aLong = sysUsers.stream()
                        .filter(sysUser -> sysUser.getNickName().equals(salesLedger.getEntryPerson()))
                        .findFirst()
                        .map(SysUser::getUserId)
                        .orElse(null);
                if(aLong == null) throw new RuntimeException("录入人:"+salesLedger.getEntryPerson()+",无对应用户！");
                salesLedger.setEntryPerson(aLong.toString());
                salesLedgerMapper.insert(salesLedger);
                // é”€å”®äº§å“æ•°æ®ç»‘定，通过销售单号获取对应销售产品数据
                List<SalesLedgerProductImportDto> salesLedgerProductImportDtos = salesLedgerProductImportDtoList.stream()
                        .filter(salesLedgerProductImportDto -> salesLedgerProductImportDto.getSalesContractNo().equals(salesLedger.getSalesContractNo()))
                        .collect(Collectors.toList());
                if(CollectionUtils.isEmpty(salesLedgerProductImportDtos)) throw new RuntimeException("销售单号:"+salesLedgerImportDto.getSalesContractNo()+",无对应产品数据！");
                for (SalesLedgerProductImportDto salesLedgerProductImportDto : salesLedgerProductImportDtos) {
                    SalesLedgerProduct salesLedgerProduct = new SalesLedgerProduct();
                    BeanUtils.copyProperties(salesLedgerProductImportDto, salesLedgerProduct);
                    salesLedgerProduct.setSalesLedgerId(salesLedger.getId());
                    salesLedgerProduct.setType(1);
                    // è®¡ç®—不含税总价
                    salesLedgerProduct.setTaxExclusiveTotalPrice(salesLedgerProduct.getTaxInclusiveTotalPrice().divide(new BigDecimal(1).add(salesLedgerProduct.getTaxRate().divide(new BigDecimal(100))), 2, RoundingMode.HALF_UP));
                    salesLedgerProduct.setNoInvoiceNum(salesLedgerProduct.getQuantity());
                    salesLedgerProduct.setNoInvoiceAmount(salesLedgerProduct.getTaxExclusiveTotalPrice());

                    salesLedgerProduct.setProductId(productList.stream()
                            .filter(product -> product.getProductName().equals(salesLedgerProduct.getProductCategory()))
                            .findFirst()
                            .map(Product::getId)
                            .orElse(null));
                    salesLedgerProduct.setProductModelId(productModels.stream()
                            .filter(productModel -> productModel.getModel().equals(salesLedgerProduct.getSpecificationModel()))
                            .findFirst()
                            .map(ProductModel::getId)
                            .orElse(null));
                    salesLedgerProduct.setRegister(loginUser.getNickName());
                    salesLedgerProduct.setRegisterDate(LocalDateTime.now());
                    salesLedgerProduct.setApproveStatus(0);
                    salesLedgerProduct.setPendingInvoiceTotal(salesLedgerProductImportDto.getTaxInclusiveTotalPrice());
                    salesLedgerProductMapper.insert(salesLedgerProduct);
                }


            }

            return AjaxResult.success("导入成功");
        }catch (Exception e) {
            e.printStackTrace();
        }
        return AjaxResult.success("导入失败");
    }

    // å†…部类用于存储聚合结果
    private static class GroupedCustomer {
        private final Long customerId;