feat(sales): 添加销售台账批量导入功能

liyong

2026-04-24 c1bbff15e4f64767d471de763613a54c9cb8d4b4

feat(sales): 添加销售台账批量导入功能

- 新增销售台账导入接口支持Excel文件上传
- 实现销售台账数据验证和去重处理逻辑
- 添加客户信息匹配和产品模型自动创建功能
- 集成库存管理避免重复数据处理
- 完善导入结果统计和错误信息反馈机制
- 优化数据库查询性能提升导入效率

 src/main/java/com/ruoyi/basic/mapper/CustomerPrivatePoolMapper.java

@@ -26,4 +26,5 @@

    List<CustomerPrivatePoolDto> selectInfos();

    List<CustomerPrivatePoolDto> selectByCusterNames(@Param("collect") List<String> collect);
}

 src/main/java/com/ruoyi/basic/service/impl/ProductModelServiceImpl.java

@@ -184,6 +184,13 @@
        }else {
            //找到父节点
            Product productParent = productMapper.selectOne(new QueryWrapper<Product>().lambda().eq(Product::getProductName, "成品").last("limit 1"));
            if (ObjectUtils.isEmpty(productParent)) {
                Product product = new Product();
                product.setProductName("成品");
                product.setDeptId(SecurityUtils.getDeptId()[0]);
                productMapper.insert(product);
                productParent.setId(product.getId());
            }
            //新增产品大类
            Product product = new Product();
            product.setProductName(productModelDto.getProductName());

 src/main/java/com/ruoyi/production/service/impl/ProductionProductMainServiceImpl.java

@@ -2,7 +2,6 @@

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.extra.spring.SpringUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -16,7 +15,6 @@
import com.ruoyi.basic.pojo.ProductModel;
import com.ruoyi.common.enums.AuditEnum;
import com.ruoyi.common.enums.StockInQualifiedRecordTypeEnum;
import com.ruoyi.common.enums.StockOutQualifiedRecordTypeEnum;
import com.ruoyi.common.enums.StockInUnQualifiedRecordTypeEnum;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.SecurityUtils;
@@ -35,18 +33,18 @@
import com.ruoyi.quality.service.IQualityInspectService;
import lombok.AllArgsConstructor;
import org.springframework.aop.framework.AopContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.ruoyi.production.mapper.ProductionProductMainMapper;

import javax.validation.constraints.NotNull;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

@Service
@@ -183,7 +181,7 @@
            productionProductInput.setQuantity(productStructureDto.getUnitQuantity().multiply(dto.getQuantity()));
            productionProductInput.setProductMainId(productionProductMain.getId());
            productionProductInputMapper.insert(productionProductInput);
            stockUtils.substractStock(productStructureDto.getProductModelId(), productionProductInput.getQuantity(), StockOutQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_OUT.getCode(), productionProductMain.getId());
//            stockUtils.substractStock(productStructureDto.getProductModelId(), productionProductInput.getQuantity(), StockOutQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_OUT.getCode(), productionProductMain.getId());

        }
        /*新增报工产出表*/
@@ -465,7 +463,7 @@
        //删除不需要质检的合格入库
        stockUtils.deleteStockInRecord(productionProductMain.getId(), StockInQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_IN.getCode());
        //删除投入对应的出库记录
        stockUtils.deleteStockOutRecord(productionProductMain.getId(), StockOutQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_OUT.getCode());
//        stockUtils.deleteStockOutRecord(productionProductMain.getId(), StockOutQualifiedRecordTypeEnum.PRODUCTION_REPORT_STOCK_OUT.getCode());
        // åˆ é™¤ä¸»è¡¨
        productionProductMainMapper.deleteById(productionProductMain.getId());
        return true;

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

@@ -185,7 +185,14 @@
        salesLedgerService.exportProcessContract(id);
    }

    /**
    @GetMapping("/importSalsesLedger")
    @ApiOperation("阳光导入销售台账新")
    public AjaxResult importSalsesLedger( MultipartFile file) {
        return salesLedgerService.importSalsesLedger(file);
    }


    /**w
     * æ–°å¢žä¿®æ”¹é”€å”®å°è´¦
     */
    @Log(title = "销售台账", businessType = BusinessType.INSERT)

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

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

import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

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

@Data
public class SalesLedgerSimpleImportDto {


    @Excel(name = "订货日期")
    @DateTimeFormat(pattern = "yy.MM.dd")
    @JsonFormat(pattern = "yy.MM.dd", timezone = "GMT+8")
    private Date orderDate;

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

    @Excel(name = "产品名称")
    private String productName;

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

    @Excel(name = "单价")
    private BigDecimal unitPrice;

    @Excel(name = "金额")
    private BigDecimal amount;

    @Excel(name = "订单编号")
    private String orderNo;
}

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

@@ -3,7 +3,6 @@
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.aftersalesservice.pojo.AfterSalesService;
import com.ruoyi.common.enums.SaleEnum;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.sales.dto.LossProductModelDto;
@@ -58,4 +57,6 @@
     * @param id
     */
    void exportProcessContract(@NotNull Long id);

    AjaxResult importSalsesLedger(MultipartFile file);
}

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

@@ -531,6 +531,141 @@

        exportProcessContractToWord(exportProcessContract);
    }
    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult importSalsesLedger(MultipartFile file) {
        LoginUser loginUser = SecurityUtils.getLoginUser();
        int successCount = 0;
        Date lastDate = null;
        BigDecimal lastUnitPrice = null;
        int skipCount = 0;
        List<String> errorMessages = new ArrayList<>();
        CustomerPrivatePoolDto lastCustomer = null;

        try {
            InputStream inputStream = file.getInputStream();
            ExcelUtil<SalesLedgerSimpleImportDto> excelUtil = new ExcelUtil<>(SalesLedgerSimpleImportDto.class);
            List<SalesLedgerSimpleImportDto> importDataList = excelUtil.importExcel(inputStream);

            if (CollectionUtils.isEmpty(importDataList)) {
                return AjaxResult.error("导入数据为空！");
            }

            List<String> customerNames = importDataList.stream()
                    .map(SalesLedgerSimpleImportDto::getCustomerName)
                    .filter(StrUtil::isNotBlank)
                    .distinct()
                    .collect(Collectors.toList());

            List<CustomerPrivatePoolDto> customers = customerPrivatePoolMapper.selectByCusterNames(customerNames);

            for (int i = 0; i < importDataList.size(); i++) {
                SalesLedgerSimpleImportDto importDto = importDataList.get(i);
                int rowNum = i + 2;

                if (StrUtil.isBlank(importDto.getOrderNo())) {
                    errorMessages.add(String.format("第%d行：订单编号为空", rowNum));
                    skipCount++;
                    continue;
                }

                if (importDto.getQuantity() == null ) {
                    errorMessages.add(String.format("第%d行：数量不能为空", rowNum));
                    skipCount++;
                    continue;
                }

                long count = salesLedgerMapper.selectCount(new LambdaQueryWrapper<SalesLedger>()
                        .eq(SalesLedger::getSalesContractNo, importDto.getOrderNo()));
                if (count > 0) {
                    errorMessages.add(String.format("第%d行：订单编号[%s]已存在", rowNum, importDto.getOrderNo()));
                    skipCount++;
                    continue;
                }

                CustomerPrivatePoolDto matchedCustomer = customers.stream()
                        .filter(c -> c.getCustomerName().equals(importDto.getCustomerName()))
                        .findFirst()
                        .orElse(lastCustomer);

                if (matchedCustomer == null) {
                    errorMessages.add(String.format("第%d行：客户[%s]不存在且无历史客户", rowNum, importDto.getCustomerName()));
                    skipCount++;
                    continue;
                }

                lastCustomer = matchedCustomer;

                try {
                    SalesLedger salesLedger = new SalesLedger();
                    salesLedger.setSalesContractNo(importDto.getOrderNo());
                    salesLedger.setCustomerName(matchedCustomer.getCustomerName());
                    salesLedger.setCustomerId(matchedCustomer.getCustomerId());
                    salesLedger.setCustomerContractNo(matchedCustomer.getTaxpayerIdentificationNumber());
                    Date date = importDto.getOrderDate();
                    if (ObjectUtils.isEmpty(date)){
                        date = lastDate;
                    }
                    lastDate = date;
                    salesLedger.setEntryDate(date);
                    salesLedger.setContractAmount(importDto.getAmount());
                    salesLedger.setEntryPerson(loginUser.getUserId().toString());
                    salesLedger.setTenantId(matchedCustomer.getTenantId());
                    salesLedgerMapper.insert(salesLedger);

                    SalesLedgerProduct product = new SalesLedgerProduct();
                    product.setSalesLedgerId(salesLedger.getId());
                    product.setType(1);
                    product.setQuantity(importDto.getQuantity());
                    BigDecimal unitPrice = importDto.getUnitPrice();
                    if (ObjectUtils.isEmpty(unitPrice)) {
                        unitPrice = lastUnitPrice;
                    }
                    lastUnitPrice = unitPrice;
                    product.setTaxInclusiveUnitPrice(unitPrice);
                    product.setTaxInclusiveTotalPrice(unitPrice.multiply(importDto.getQuantity()));
                    product.setTaxExclusiveTotalPrice(unitPrice.multiply(importDto.getQuantity()));
                    product.setNoInvoiceNum(importDto.getQuantity());
                    product.setNoInvoiceAmount(importDto.getAmount());
                    product.setPendingInvoiceTotal(importDto.getAmount());
                    product.setUnit("/");
                    product.setProductCategory(importDto.getProductName());
                    product.setSpecificationModel(importDto.getProductName());
                    product.setRegister(loginUser.getNickName());
                    product.setRegisterDate(LocalDateTime.now());
                    product.setApproveStatus(0);

                    if (ObjectUtils.isEmpty(product.getProductModelId())) {
                        ProductModelAnticlockwiseDto productModelAnticlockwiseDto = new ProductModelAnticlockwiseDto();
                        productModelAnticlockwiseDto.setModel(product.getSpecificationModel());
                        productModelAnticlockwiseDto.setProductName(product.getProductCategory());
                        productModelAnticlockwiseDto.setUnit("/");
                        productModelAnticlockwiseDto.setSubUnit("/");
                        Long productModelId = productModelService.productModelAnticlockwise(productModelAnticlockwiseDto);
                        product.setProductModelId(productModelId);
                    }

                    salesLedgerProductMapper.insert(product);
                    salesLedgerProductServiceImpl.addProductionData(product);
                    successCount++;

                } catch (Exception e) {
                    log.error("第{}行订单[{}]导入失败：{}", rowNum, importDto.getOrderNo(), e.getMessage(), e);
                    errorMessages.add(String.format("第%d行订单[%s]：导入失败-%s", rowNum, importDto.getOrderNo(), e.getMessage()));
                    skipCount++;
                }
            }

            String message = String.format("导入完成！成功%d条，跳过%d条", successCount, skipCount);
            if (!errorMessages.isEmpty()) {
                message += "。错误详情：" + String.join("；", errorMessages);
            }
            return AjaxResult.success(message);
        } catch (Exception e) {
            log.error("导入销售台账失败", e);
            throw new RuntimeException("导入失败：" + e.getMessage(), e);
        }
    }

    @SneakyThrows
    private void exportProcessContractToWord(@NotNull ExportProcessContractVo exportProcessContract) {
@@ -611,11 +746,12 @@
        List<Long> productIds = products.stream()
                .map(SalesLedgerProduct::getId)
                .collect(Collectors.toList());
        //删除生产数据
        salesLedgerProductServiceImpl.deleteProductionData(productIds);


        // æ‰¹é‡åˆ é™¤äº§å“å­è¡¨
        if (!productIds.isEmpty()) {
            //删除生产数据
            salesLedgerProductServiceImpl.deleteProductionData(productIds);
            salesLedgerProductMapper.deleteBatchIds(productIds);
        }


 src/main/java/com/ruoyi/stock/service/impl/StockInventoryServiceImpl.java

@@ -6,12 +6,10 @@
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.enums.StockInQualifiedRecordTypeEnum;
import com.ruoyi.common.enums.StockOutQualifiedRecordTypeEnum;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.framework.web.domain.R;
import com.ruoyi.sales.dto.SimpleProductDto;
import com.ruoyi.sales.mapper.SalesLedgerProductMapper;
import com.ruoyi.sales.pojo.SalesLedgerProduct;
import com.ruoyi.stock.dto.StockInRecordDto;
import com.ruoyi.stock.dto.StockInventoryDto;
import com.ruoyi.stock.dto.StockOutRecordDto;

 src/main/resources/mapper/basic/CustomerPrivatePoolMapper.xml

@@ -99,7 +99,7 @@
               coalesce(c.tenant_id, cp.tenant_id) as tenant_id,
               coalesce(c.basic_bank_account, cp.basic_bank_account) as basic_bank_account,
               coalesce(c.bank_account, cp.bank_account) as bank_account,
               coalesce(c.bank_code, cp.bank_code) as bank_code
               coalesce(c.bank_code, cp.bank_code) as bank_code,
            coalesce(c.corporation, cp.corporation) as corporation,
               coalesce(c.fax, cp.fax) as fax,
               coalesce(c.agent, cp.agent) as agent,
@@ -108,5 +108,38 @@
            left join customer c on c.id = cpp.customer_id and cpp.type = 1
            left join customer_private cp on cp.id = cpp.customer_id and cpp.type = 0
    </select>
    <select id="selectByCusterNames" resultType="com.ruoyi.basic.dto.CustomerPrivatePoolDto">
        select cpp.id,
        cpp.bound_id,
        cpp.type,
        coalesce(c.id, cp.id) as customer_id,
        coalesce(c.customer_name, cp.customer_name) as customer_name,
        coalesce(c.customer_type, cp.customer_type) as customer_type,
        coalesce(c.taxpayer_identification_number, cp.taxpayer_identification_number) as taxpayer_identification_number,
        coalesce(c.company_address, cp.company_address) as company_address,
        coalesce(c.company_phone, cp.company_phone) as company_phone,
        coalesce(c.contact_person, cp.contact_person) as contact_person,
        coalesce(c.contact_phone, cp.contact_phone) as contact_phone,
        coalesce(c.maintainer, cp.maintainer) as maintainer,
        coalesce(c.maintenance_time, cp.maintenance_time) as maintenance_time,
        coalesce(c.tenant_id, cp.tenant_id) as tenant_id,
        coalesce(c.basic_bank_account, cp.basic_bank_account) as basic_bank_account,
        coalesce(c.bank_account, cp.bank_account) as bank_account,
        coalesce(c.bank_code, cp.bank_code) as bank_code,
        coalesce(c.corporation, cp.corporation) as corporation,
        coalesce(c.fax, cp.fax) as fax,
        coalesce(c.agent, cp.agent) as agent,
        coalesce(c.bank_name, cp.bank_name) as bank_name
        from customer_private_pool cpp
        left join customer c on c.id = cpp.customer_id and cpp.type = 1
        left join customer_private cp on cp.id = cpp.customer_id and cpp.type = 0
        where c.customer_name in
        <foreach item="item" collection="collect" separator="," open="(" close=")">
            #{item}
        </foreach> or cp.customer_name in
        <foreach item="item" collection="collect" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

</mapper>

 src/main/resources/mapper/basic/ProductModelMapper.xml

@@ -126,7 +126,8 @@
        INNER JOIN tree t ON t2.id = t.id;
    </select>
    <select id="selectOldProductModel" resultType="com.ruoyi.basic.pojo.ProductModel">
        select * from product_model left join
        select product_model.*,product.product_name
        from product_model left join
            product on product_model.product_id = product.id
        where product_model.model = #{model}
        and product.product_name = #{productName}

 src/main/resources/static/ÏúÊŲ̂Õ˵¼ÈëÄ£°å.xlsx

Binary files differ