fix: 销售台账导入模板下载,导入数据验证

gongchunyi

6 小时以前 727405950dd5be7d8f99cb7c8a7a18ccb26f9b5a

fix: 销售台账导入模板下载,导入数据验证

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

@@ -71,7 +71,8 @@
    public AjaxResult importData(@RequestParam("file")
                                 @ApiParam(value = "Excel文件", required = true)
                                 MultipartFile file) {
        return salesLedgerService.importData(file);
        salesLedgerService.importData(file);
        return AjaxResult.success();
    }

    @ApiOperation("导出销售台账模板")

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

@@ -52,7 +52,7 @@
    private Date entryDate;

    @ApiModelProperty(value = "付款方式")
    @Excel(name = "付款方式")
//    @Excel(name = "付款方式")
    private String paymentMethod;

    @Excel(name = "备注")

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

@@ -39,7 +39,7 @@
    @Excel(name = "数量")
    private BigDecimal quantity;

    @Excel(name = "含税总价")
    @Excel(name = "含税总价", type = Excel.Type.EXPORT)
    private BigDecimal taxInclusiveTotalPrice;

    @Excel(name = "发票类型")

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

@@ -49,7 +49,7 @@

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

    AjaxResult importData(MultipartFile file);
    void importData(MultipartFile file);

    List<LossProductModelDto> getSalesLedgerWithProductsLoss(Long salesLedgerId);


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

@@ -27,7 +27,6 @@
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.mapper.*;
@@ -418,18 +417,33 @@

    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult importData(MultipartFile file) {
    public void 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("销售表格为空！");

        Map<String, List<SalesLedgerImportDto>> stringListMap;
        try (InputStream inputStream = file.getInputStream()) {
            ExcelUtil<SalesLedgerImportDto> excelUtil = new ExcelUtil<>(SalesLedgerImportDto.class);
            stringListMap = excelUtil.importExcelMultiSheet(Arrays.asList("销售台账数据", "销售产品数据"), inputStream, 0);
        } catch (IOException e) {
            log.error("销售台账导入失败：读取/解析Excel异常", e);
            throw new ServiceException("导入失败：读取/解析Excel异常");
        } catch (Exception e) {
            log.error("销售台账导入失败：解析Excel异常", e);
            throw new ServiceException("导入失败：解析Excel异常");
        }

        if (CollectionUtils.isEmpty(stringListMap)) {
            throw new ServiceException("导入失败,销售表格为空");
        }
            // ä¸šåŠ¡å±‚合并
            List<SalesLedgerImportDto> salesLedgerImportDtoList = stringListMap.get("销售台账数据");
            if (CollectionUtils.isEmpty(salesLedgerImportDtoList)) return AjaxResult.error("销售台账数据为空！");
        if (CollectionUtils.isEmpty(salesLedgerImportDtoList)) {
            throw new ServiceException("导入失败,销售台账数据为空");
        }
            List<SalesLedgerImportDto> salesLedgerProductImportDtoList = stringListMap.get("销售产品数据");
            if (CollectionUtils.isEmpty(salesLedgerProductImportDtoList)) return AjaxResult.error("销售产品数据为空！");
        if (CollectionUtils.isEmpty(salesLedgerProductImportDtoList)) {
            throw new ServiceException("导入失败,销售产品数据为空");
        }
            // å®¢æˆ·æ•°æ®
            List<Customer> customers = customerMapper.selectList(new LambdaQueryWrapper<Customer>().in(Customer::getCustomerName,
                    salesLedgerImportDtoList.stream().map(SalesLedgerImportDto::getCustomerName).collect(Collectors.toList())));
@@ -446,7 +460,21 @@
                }
                SalesLedger salesLedger = new SalesLedger();
                BeanUtils.copyProperties(salesLedgerImportDto, salesLedger);
            // æ ¡éªŒï¼šç­¾è®¢æ—¥æœŸã€å½•å…¥æ—¥æœŸä¸èƒ½ä¸ºç©º
            if (salesLedgerImportDto.getExecutionDate() == null) {
                throw new ServiceException("导入失败：合同号[" + salesLedgerImportDto.getSalesContractNo() + "] ç­¾è®¢æ—¥æœŸä¸èƒ½ä¸ºç©º");
            }
            if (salesLedgerImportDto.getEntryDate() == null) {
                throw new ServiceException("导入失败：合同号[" + salesLedgerImportDto.getSalesContractNo() + "] å½•å…¥æ—¥æœŸä¸èƒ½ä¸ºç©º");
            }
                salesLedger.setExecutionDate(DateUtils.toLocalDate(salesLedgerImportDto.getExecutionDate()));

            LocalDate expectedDeliveryDate = DateUtils.toLocalDate(salesLedgerImportDto.getEntryDate()).plusDays(7);
            LocalDate importDeliveryDate = salesLedgerImportDto.getDeliveryDate() == null
                    ? null
                    : DateUtils.toLocalDate(salesLedgerImportDto.getDeliveryDate());
            // äº¤ä»˜æ—¥æœŸä¸ºç©ºåˆ™é»˜è®¤å–录入日期后7天
            salesLedger.setDeliveryDate(importDeliveryDate == null ? expectedDeliveryDate : importDeliveryDate);
                // é€šè¿‡å®¢æˆ·åç§°æŸ¥è¯¢å®¢æˆ·ID，客户合同号
                salesLedger.setCustomerId(customers.stream()
                        .filter(customer -> customer.getCustomerName().equals(salesLedger.getCustomerName()))
@@ -463,21 +491,22 @@
                        .findFirst()
                        .map(SysUser::getUserId)
                        .orElse(null);
                if (aLong == null)
            if (aLong == null) {
                    throw new ServiceException("录入人:" + salesLedger.getEntryPerson() + ",无对应用户！");
            }
                salesLedger.setEntryPerson(aLong.toString());
                // é”€å”®äº§å“æ•°æ®ç»‘定，通过销售单号获取对应销售产品数据
                List<SalesLedgerProductImportDto> salesLedgerProductImportDtos = salesLedgerProductImportDtoList.stream()
                        .filter(salesLedgerProductImportDto -> salesLedgerProductImportDto.getSalesContractNo().equals(salesLedger.getSalesContractNo()))
                        .collect(Collectors.toList());
                if (CollectionUtils.isEmpty(salesLedgerProductImportDtos))
                    throw new RuntimeException("销售单号:" + salesLedgerImportDto.getSalesContractNo() + ",无对应产品数据！");
                salesLedger.setContractAmount(salesLedgerProductImportDtos.stream()
                        .map(SalesLedgerProductImportDto::getTaxInclusiveTotalPrice)
                        .reduce(BigDecimal.ZERO, BigDecimal::add));
            if (CollectionUtils.isEmpty(salesLedgerProductImportDtos)) {
                throw new ServiceException("销售单号:" + salesLedgerImportDto.getSalesContractNo() + ",无对应产品数据！");
            }
                //  å‘货状态
                salesLedger.setDeliveryStatus(1);
            salesLedger.setContractAmount(BigDecimal.ZERO);
                salesLedgerMapper.insert(salesLedger);
            BigDecimal contractAmount = BigDecimal.ZERO;

                for (SalesLedgerProductImportDto salesLedgerProductImportDto : salesLedgerProductImportDtos) {
                    SalesLedgerProduct salesLedgerProduct = new SalesLedgerProduct();
@@ -487,12 +516,48 @@
                    salesLedgerProduct.setRemark(salesLedgerProductImportDto.getRemarks());
                    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());

                BigDecimal quantity = defaultDecimal(salesLedgerProduct.getQuantity());
                BigDecimal width = defaultDecimal(salesLedgerProduct.getWidth());
                BigDecimal height = defaultDecimal(salesLedgerProduct.getHeight());
                BigDecimal taxRateRaw = salesLedgerProduct.getTaxRate();
                BigDecimal taxRate = defaultDecimal(taxRateRaw);
                BigDecimal unitPrice = defaultDecimal(salesLedgerProduct.getTaxInclusiveUnitPrice());

                // å¯¼å…¥é™åˆ¶ï¼šå®½/高/数量/金额(单价)/税率 ä¸èƒ½ä¸º0或负数
                String locate = buildImportLocate(salesLedger.getSalesContractNo(), salesLedgerProductImportDto);
                assertPositive(quantity, "数量", locate);
                assertPositive(width, "宽(mm)", locate);
                assertPositive(height, "高(mm)", locate);
                assertPositive(unitPrice, "含税单价", locate);
                assertNonNegative(taxRateRaw, "税率", locate);

                BigDecimal actualPieceArea = BigDecimal.ZERO;
                if (width.compareTo(BigDecimal.ZERO) > 0 && height.compareTo(BigDecimal.ZERO) > 0) {
                    actualPieceArea = width.multiply(height).divide(new BigDecimal("1000000"), 4, RoundingMode.HALF_UP);
                }
                salesLedgerProduct.setActualPieceArea(actualPieceArea);
                salesLedgerProduct.setActualTotalArea(actualPieceArea.multiply(quantity).setScale(4, RoundingMode.HALF_UP));

                BigDecimal settlePieceArea = salesLedgerProduct.getSettlePieceArea() == null
                        ? actualPieceArea
                        : salesLedgerProduct.getSettlePieceArea();
                salesLedgerProduct.setSettlePieceArea(settlePieceArea);
                salesLedgerProduct.setSettleTotalArea(settlePieceArea.multiply(quantity).setScale(4, RoundingMode.HALF_UP));

                BigDecimal perimeter = BigDecimal.ZERO;
                if (width.compareTo(BigDecimal.ZERO) > 0 && height.compareTo(BigDecimal.ZERO) > 0) {
                    perimeter = width.add(height)
                            .multiply(new BigDecimal("2"))
                            .divide(new BigDecimal("10"), 2, RoundingMode.HALF_UP);
                }
                salesLedgerProduct.setPerimeter(perimeter);

                BigDecimal extraProcessAmountPerPiece = BigDecimal.ZERO;
                    list.stream()
                            .filter(map -> map.get("productName").equals(salesLedgerProduct.getProductCategory()) && map.get("model").equals(salesLedgerProduct.getSpecificationModel()))
                        .filter(Objects::nonNull)
                        .filter(map -> Objects.equals(Objects.toString(map.get("productName"), null), salesLedgerProduct.getProductCategory())
                                && Objects.equals(Objects.toString(map.get("model"), null), salesLedgerProduct.getSpecificationModel()))
                            .findFirst()
                            .ifPresent(map -> {
                                salesLedgerProduct.setProductModelId(Long.parseLong(map.get("modelId").toString()));
@@ -501,13 +566,12 @@
                    salesLedgerProduct.setRegister(loginUser.getNickName());
                    salesLedgerProduct.setRegisterDate(LocalDateTime.now());
                    salesLedgerProduct.setApproveStatus(0);
                    salesLedgerProduct.setPendingInvoiceTotal(salesLedgerProductImportDto.getTaxInclusiveTotalPrice());
                    salesLedgerProductMapper.insert(salesLedgerProduct);
                salesLedgerProduct.setProductStockStatus(0);

                    // å¤„理额外加工信息
                    String extraProcessing = salesLedgerProductImportDto.getExtraProcessing();
                    if (StringUtils.hasText(extraProcessing)) {
                        List<SalesLedgerProductProcess> processList = new ArrayList<>();
                if (StringUtils.hasText(extraProcessing)) {
                        //  ä¸­è‹±æ–‡åˆ†å·
                        String[] items = extraProcessing.split("[;；]");
                        for (String item : items) {
@@ -517,7 +581,7 @@
                                    String processName = parts[0].trim();
                                    String qtyStr = parts[1].trim();
                                    try {
                                        BigDecimal quantity = new BigDecimal(qtyStr);
                                    BigDecimal processQty = new BigDecimal(qtyStr);
                                        SalesLedgerProductProcess process = salesLedgerProductProcessService.getOne(
                                                new LambdaQueryWrapper<SalesLedgerProductProcess>()
                                                        .eq(SalesLedgerProductProcess::getProcessName, processName)
@@ -526,8 +590,11 @@
                                        if (process != null) {
                                            SalesLedgerProductProcess p = new SalesLedgerProductProcess();
                                            p.setId(process.getId());
                                            p.setQuantity(quantity.intValue());
                                        p.setQuantity(processQty.intValue());
                                            processList.add(p);
                                        extraProcessAmountPerPiece = extraProcessAmountPerPiece.add(
                                                defaultDecimal(process.getUnitPrice()).multiply(processQty)
                                        );
                                        }
                                    } catch (Exception e) {
                                        log.error("解析额外加工数量失败: {}", qtyStr);
@@ -535,30 +602,73 @@
                                }
                            }
                        }
                }

                // å«ç¨Žæ€»ä»· = å•ä»· * ç»“算面积 * æ•°é‡ + é¢å¤–加工金额 * æ•°é‡
                BigDecimal taxInclusiveTotalPrice = unitPrice.multiply(settlePieceArea)
                        .multiply(quantity)
                        .add(extraProcessAmountPerPiece.multiply(quantity))
                        .setScale(2, RoundingMode.HALF_UP);
                salesLedgerProduct.setTaxInclusiveTotalPrice(taxInclusiveTotalPrice);
                assertPositive(taxInclusiveTotalPrice, "含税总价", locate);

                // ç¨ŽçŽ‡å…è®¸ä¸ºç©ºï¼Œç©ºå€¼æŒ‰0处理
                BigDecimal taxDivisor = BigDecimal.ONE.add(taxRate.divide(new BigDecimal("100"), 6, RoundingMode.HALF_UP));
                salesLedgerProduct.setTaxExclusiveTotalPrice(
                        taxInclusiveTotalPrice.divide(taxDivisor, 2, RoundingMode.HALF_UP)
                );
                salesLedgerProduct.setNoInvoiceNum(quantity);
                salesLedgerProduct.setNoInvoiceAmount(salesLedgerProduct.getTaxExclusiveTotalPrice());
                salesLedgerProduct.setPendingInvoiceTotal(taxInclusiveTotalPrice);
                salesLedgerProductMapper.insert(salesLedgerProduct);
                        if (!processList.isEmpty()) {
                            salesLedgerProductProcessBindService.updateProductProcessBind(processList, salesLedgerProduct.getId());
                        }
                    }

                    // æ·»åŠ ç”Ÿäº§æ•°æ®
                    salesLedgerProductServiceImpl.addProductionData(salesLedgerProduct);
                contractAmount = contractAmount.add(taxInclusiveTotalPrice);
            }
            salesLedger.setContractAmount(contractAmount);
            salesLedgerMapper.updateById(salesLedger);
                }
            }

            return AjaxResult.success("导入成功");
        } catch (Exception e) {
            e.printStackTrace();
    private BigDecimal defaultDecimal(BigDecimal value) {
        return value == null ? BigDecimal.ZERO : value;
        }
        return AjaxResult.success("导入失败");

    private void assertPositive(BigDecimal value, String fieldName, String locate) {
        if (value == null || value.compareTo(BigDecimal.ZERO) <= 0) {
            throw new ServiceException("导入失败：" + locate + "【" + fieldName + "】必须大于0");
        }
    }

    private void assertNonNegative(BigDecimal value, String fieldName, String locate) {
        if (value != null && value.compareTo(BigDecimal.ZERO) < 0) {
            throw new ServiceException("导入失败：" + locate + "【" + fieldName + "】不能为负数");
        }
    }

    private String buildImportLocate(String salesContractNo, SalesLedgerProductImportDto dto) {
        StringBuilder sb = new StringBuilder();
        sb.append("销售单号[").append(salesContractNo == null ? "" : salesContractNo).append("]");
        if (dto != null) {
            if (StringUtils.hasText(dto.getProductCategory())) {
                sb.append(" äº§å“å¤§ç±»[").append(dto.getProductCategory()).append("]");
            }
            if (StringUtils.hasText(dto.getSpecificationModel())) {
                sb.append(" è§„格型号[").append(dto.getSpecificationModel()).append("]");
            }
            if (StringUtils.hasText(dto.getFloorNo())) {
                sb.append(" æ¥¼å±‚编号[").append(dto.getFloorNo()).append("]");
            }
        }
        return sb.toString();
    }

    @Override
    public List<LossProductModelDto> getSalesLedgerWithProductsLoss(Long salesLedgerId) {


        List<LossProductModelDto> lossProductModelDtos = salesLedgerProductMapper.selectProductBomStructure(salesLedgerId);


        return lossProductModelDtos;
    }


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

Binary files differ