yys 采购台账模板导入，模板下载接口

maven

昨天 05ae94ea868f4bc85984b0e6d38512c163dc3c33

yys 采购台账模板导入，模板下载接口

 src/main/java/com/ruoyi/basic/pojo/SupplierManage.java

@@ -14,7 +14,7 @@
public class SupplierManage {

    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;
    private Long id;

    @ApiModelProperty(value = "供应商名称")
    @Excel(name = "供应商名称")

 src/main/java/com/ruoyi/purchase/controller/PurchaseLedgerController.java

@@ -22,12 +22,19 @@
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 lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
@@ -42,6 +49,7 @@
@RequestMapping("/purchase/ledger")
@AllArgsConstructor
@Api(tags = "采购台账")
@Slf4j
public class PurchaseLedgerController extends BaseController {
    private IPurchaseLedgerService purchaseLedgerService;

@@ -52,6 +60,57 @@

    private SalesLedgerProductTemplateMapper salesLedgerProductTemplateMapper;


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

    @ApiOperation("导出采购台账模板")
    @PostMapping("/exportTemplate")
    public void exportTemplate(HttpServletResponse response) {
        // 1. æ¨¡æ¿æ–‡ä»¶åœ¨resources/static下的路径
        String templatePath = "static/采购台账导入模板.xlsx";

        // 2. èŽ·å–模板文件的输入流
        try (InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(templatePath)) {
            if (inputStream == null) {
                throw new FileNotFoundException("模板文件不存在：" + templatePath);
            }

            // 3. è®¾ç½®å“åº”头，触发浏览器下载
            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);

            // 4. å°†æ¨¡æ¿æ–‡ä»¶å†™å…¥å“åº”输出流
            try (OutputStream outputStream = response.getOutputStream()) {
                byte[] buffer = new byte[1024];
                int len;
                while ((len = inputStream.read(buffer)) > 0) {
                    outputStream.write(buffer, 0, len);
                }
                outputStream.flush();
            }
        } catch (IOException e) {
            log.error("导出销售台账模板失败", e);
            // è‹¥æ¨¡æ¿æ–‡ä»¶è¯»å–失败，返回错误提示
            try {
                response.getWriter().write("模板导出失败：" + e.getMessage());
            } catch (IOException ex) {
                log.error("响应输出错误", ex);
            }
        }
    }

    /**
     * æŸ¥è¯¢é‡‡è´­å°è´¦åˆ—表
     */

 src/main/java/com/ruoyi/purchase/dto/PurchaseLedgerImportDto.java

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

import com.fasterxml.jackson.annotation.JsonFormat;
import com.ruoyi.framework.aspectj.lang.annotation.Excel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.util.Date;

/**
 * @author :yys
 * @date : 2026/1/26 16:01
 */
@Data
public class PurchaseLedgerImportDto extends PurchaseLedgerProductImportDto{

    @Excel(name = "采购单号")
    private String purchaseContractNumber;
    @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 supplierName;
    @Excel(name = "项目名称")
    private String projectName;
    /**
     * å½•å…¥äººå§“名
     */
    @Excel(name = "录入人")
    private String recorderName;
    @Excel(name = "备注")
    private String remarks;

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

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

    @ApiModelProperty(value = "审核人(多个用，隔开)")
    @Excel(name = "审核人(多个用，隔开)")
    private String approveUserIds;



}

 src/main/java/com/ruoyi/purchase/dto/PurchaseLedgerProductImportDto.java

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

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

import java.math.BigDecimal;

/**
 * @author :yys
 * @date : 2026/1/26 16:03
 */
@Data
public class PurchaseLedgerProductImportDto{

    @Excel(name = "采购单号")
    private String purchaseContractNumber;

    /**
     * é¢„警数量
     */
    @Excel(name = "库存预警数量")
    private BigDecimal warnNum;

    @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 Integer isChecked;

}

 src/main/java/com/ruoyi/purchase/service/IPurchaseLedgerService.java

@@ -3,9 +3,11 @@

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.purchase.dto.PurchaseLedgerDto;
import com.ruoyi.purchase.pojo.PurchaseLedger;
import com.ruoyi.sales.pojo.InvoiceRegistrationProduct;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;
@@ -40,4 +42,5 @@

    String getPurchaseNo();

    AjaxResult importData(MultipartFile file);
}

 src/main/java/com/ruoyi/purchase/service/impl/PaymentRegistrationServiceImpl.java

@@ -344,7 +344,6 @@
        List<Long> supplierIds = suppliers.stream()
                .map(SupplierManage::getId) // å…ˆèŽ·å–Integer类型的ID
                .filter(Objects::nonNull)    // è¿‡æ»¤æŽ‰å¯èƒ½çš„null值
                .map(Integer::longValue)    // å°†Integer转换为Long
                .collect(Collectors.toList());

        // èŽ·å–当月的开始和结束日期

 src/main/java/com/ruoyi/purchase/service/impl/PurchaseLedgerServiceImpl.java

@@ -17,6 +17,7 @@
import com.ruoyi.basic.mapper.ProductMapper;
import com.ruoyi.basic.mapper.ProductModelMapper;
import com.ruoyi.basic.mapper.SupplierManageMapper;
import com.ruoyi.basic.pojo.Customer;
import com.ruoyi.basic.pojo.Product;
import com.ruoyi.basic.pojo.ProductModel;
import com.ruoyi.basic.pojo.SupplierManage;
@@ -25,7 +26,9 @@
import com.ruoyi.common.utils.DateUtils;
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.procurementrecord.mapper.ProcurementRecordMapper;
@@ -33,11 +36,15 @@
import com.ruoyi.project.system.domain.SysUser;
import com.ruoyi.project.system.mapper.SysUserMapper;
import com.ruoyi.purchase.dto.PurchaseLedgerDto;
import com.ruoyi.purchase.dto.PurchaseLedgerImportDto;
import com.ruoyi.purchase.dto.PurchaseLedgerProductImportDto;
import com.ruoyi.purchase.mapper.*;
import com.ruoyi.purchase.pojo.*;
import com.ruoyi.purchase.service.IPurchaseLedgerService;
import com.ruoyi.quality.mapper.*;
import com.ruoyi.quality.pojo.*;
import com.ruoyi.sales.dto.SalesLedgerImportDto;
import com.ruoyi.sales.dto.SalesLedgerProductImportDto;
import com.ruoyi.sales.mapper.*;
import com.ruoyi.sales.pojo.CommonFile;
import com.ruoyi.sales.pojo.InvoiceRegistrationProduct;
@@ -51,9 +58,12 @@
import org.springframework.data.redis.core.StringRedisTemplate;
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.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
@@ -93,6 +103,8 @@
    private final ProductMapper productMapper;

    private final ProductModelMapper productModelMapper;

    private final SysUserMapper sysUserMapper;

    private final TicketRegistrationMapper ticketRegistrationMapper;

@@ -155,7 +167,7 @@
        if (purchaseLedger.getId() == null) {
            purchaseLedgerMapper.insert(purchaseLedger);
        } else {
            // åˆ é™¤é‡‡è´­å®¡æ‰¹ï¼Œé‡æ–°æäº¤
            // åˆ é™¤é‡‡è´­å®¡æ ¸ï¼Œé‡æ–°æäº¤
            ApproveProcess one = approveProcessService.getOne(new LambdaQueryWrapper<ApproveProcess>()
                    .eq(ApproveProcess::getApproveType, 5)
                    .eq(ApproveProcess::getApproveReason, purchaseLedger.getPurchaseContractNumber())
@@ -166,15 +178,9 @@
            }
            purchaseLedgerMapper.updateById(purchaseLedger);
        }
        // 6.采购审批新增
        ApproveProcessVO approveProcessVO = new ApproveProcessVO();
        approveProcessVO.setApproveType(5);
        approveProcessVO.setApproveDeptId(loginUser.getCurrentDeptId());
        approveProcessVO.setApproveReason(purchaseLedger.getPurchaseContractNumber());
        approveProcessVO.setApproveUserIds(purchaseLedgerDto.getApproveUserIds());
        approveProcessVO.setApproveUser(loginUser.getUserId());
        approveProcessVO.setApproveTime(LocalDate.now().toString());
        approveProcessService.addApprove(approveProcessVO);
        // 6.采购审核新增
        addApproveByPurchase(loginUser, purchaseLedger);

        // 4. å¤„理子表数据
        List<SalesLedgerProduct> productList = purchaseLedgerDto.getProductData();
        if (productList != null && !productList.isEmpty()) {
@@ -610,6 +616,125 @@
        return purchaseNo + String.format("%03d", sequence);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public AjaxResult importData(MultipartFile file) {
        LoginUser loginUser = SecurityUtils.getLoginUser();
        try {
            InputStream inputStream = file.getInputStream();
            ExcelUtil<PurchaseLedgerImportDto> salesLedgerImportDtoExcelUtil = new ExcelUtil<>(PurchaseLedgerImportDto.class);
            Map<String, List<PurchaseLedgerImportDto>> stringListMap = salesLedgerImportDtoExcelUtil.importExcelMultiSheet(Arrays.asList("采购台账数据", "采购产品数据"), inputStream, 0);
            if (CollectionUtils.isEmpty(stringListMap)) return AjaxResult.error("采购表格为空！");
            // ä¸šåŠ¡å±‚合并
            List<PurchaseLedgerImportDto> salesLedgerImportDtoList = stringListMap.get("采购台账数据");
            if (CollectionUtils.isEmpty(salesLedgerImportDtoList)) return AjaxResult.error("采购台账数据为空！");
            List<PurchaseLedgerImportDto> salesLedgerProductImportDtoList = stringListMap.get("采购产品数据");
            if (CollectionUtils.isEmpty(salesLedgerProductImportDtoList)) return AjaxResult.error("采购产品数据为空！");
            // ä¾›åº”商数据
            List<SupplierManage> customers = supplierManageMapper.selectList(new LambdaQueryWrapper<SupplierManage>().in(SupplierManage::getSupplierName,
                    salesLedgerImportDtoList.stream().map(PurchaseLedgerImportDto::getSupplierName).collect(Collectors.toList())));
            List<Map<String,Object>> list = productModelMapper.getProductAndModelList();
            // å½•å…¥äººæ•°æ®
            List<SysUser> sysUsers = sysUserMapper.selectList(new LambdaQueryWrapper<SysUser>().in(SysUser::getNickName,
                    salesLedgerImportDtoList.stream().map(PurchaseLedgerImportDto::getRecorderName).collect(Collectors.toList())));
            for (PurchaseLedgerImportDto salesLedgerImportDto : salesLedgerImportDtoList) {
                PurchaseLedger salesLedger = new PurchaseLedger();
                BeanUtils.copyProperties(salesLedgerImportDto, salesLedger);
                // é€šè¿‡ä¾›åº”商名称查询ID
                salesLedger.setSupplierId(customers.stream()
                        .filter(customer -> customer.getSupplierName().equals(salesLedger.getSupplierName()))
                        .findFirst()
                        .map(SupplierManage::getId)
                        .orElse(null));
                Long aLong = sysUsers.stream()
                        .filter(sysUser -> sysUser.getNickName().equals(salesLedger.getRecorderName()))
                        .findFirst()
                        .map(SysUser::getUserId)
                        .orElse(null);
                if (aLong == null)
                    throw new RuntimeException("录入人:" + salesLedger.getRecorderName() + ",无对应用户！");
                salesLedger.setRecorderId(aLong);
                // é‡‡è´­äº§å“æ•°æ®ç»‘定，通过采购单号获取对应采购产品数据
                List<PurchaseLedgerProductImportDto> salesLedgerProductImportDtos = salesLedgerProductImportDtoList.stream()
                        .filter(salesLedgerProductImportDto -> salesLedgerProductImportDto.getPurchaseContractNumber().equals(salesLedger.getPurchaseContractNumber()))
                        .collect(Collectors.toList());
                if (CollectionUtils.isEmpty(salesLedgerProductImportDtos))
                    throw new RuntimeException("采购单号:" + salesLedgerImportDto.getPurchaseContractNumber() + ",无对应产品数据！");
                salesLedger.setContractAmount(salesLedgerProductImportDtos.stream()
                        .map(PurchaseLedgerProductImportDto::getTaxInclusiveTotalPrice)
                        .reduce(BigDecimal.ZERO,BigDecimal::add));
                // é€šè¿‡é”€å”®å•å·ç»‘定销售
                SalesLedger salesLedger1 = salesLedgerMapper.selectOne(new LambdaQueryWrapper<SalesLedger>()
                        .eq(SalesLedger::getSalesContractNo, salesLedger.getSalesContractNo())
                        .last("LIMIT 1"));
                if(salesLedger1 != null){
                    salesLedger.setSalesLedgerId(salesLedger1.getId());
                }
                // é‡‡è´­å®¡æ ¸
                // é€šè¿‡æ˜µç§°èŽ·å–用户ID
                String[] split = salesLedger.getApproveUserIds().split("，");
                List<Long> ids = new ArrayList<>();
                for (int i = 0; i < split.length; i++) {
                    SysUser sysUser = sysUserMapper.selectOne(new LambdaQueryWrapper<SysUser>().eq(SysUser::getNickName, split[i])
                            .last("LIMIT 1"));
                    if (sysUser != null) {
                        ids.add(sysUser.getUserId());
                    }
                }
                // å°†é›†åˆè½¬ä¸ºå­—符串，隔开
                String collect = ids.stream().map(Object::toString).collect(Collectors.joining(","));
                salesLedger.setApproveUserIds(collect);
                purchaseLedgerMapper.insert(salesLedger);

                for (PurchaseLedgerProductImportDto salesLedgerProductImportDto : salesLedgerProductImportDtos) {
                    SalesLedgerProduct salesLedgerProduct = new SalesLedgerProduct();
                    BeanUtils.copyProperties(salesLedgerProductImportDto, salesLedgerProduct);
                    salesLedgerProduct.setSalesLedgerId(salesLedger.getId());
                    salesLedgerProduct.setType(2);
                    // è®¡ç®—不含税总价
                    salesLedgerProduct.setTaxExclusiveTotalPrice(salesLedgerProduct.getTaxInclusiveTotalPrice().divide(new BigDecimal(1).add(salesLedgerProduct.getTaxRate().divide(new BigDecimal(100))), 2, RoundingMode.HALF_UP));
                    salesLedgerProduct.setFutureTickets(salesLedgerProduct.getQuantity());
                    salesLedgerProduct.setFutureTicketsAmount(salesLedgerProduct.getTaxExclusiveTotalPrice());
                    list.stream()
                            .filter(map -> map.get("productName").equals(salesLedgerProduct.getProductCategory()) && map.get("model").equals(salesLedgerProduct.getSpecificationModel()))
                            .findFirst()
                            .ifPresent(map -> {
                                salesLedgerProduct.setProductModelId(Long.parseLong(map.get("modelId").toString()));
                                salesLedgerProduct.setProductId(Long.parseLong(map.get("id").toString()));
                            });
                    salesLedgerProduct.setRegister(loginUser.getNickName());
                    salesLedgerProduct.setRegisterDate(LocalDateTime.now());
                    salesLedgerProduct.setApproveStatus(0);
                    salesLedgerProduct.setPendingTicketsTotal(salesLedgerProductImportDto.getTaxInclusiveTotalPrice());
                    // æ˜¯å¦è´¨æ£€åˆ¤æ–­
                    salesLedgerProduct.setIsChecked(salesLedgerProductImportDto.getIsChecked() == 1);
                    if(salesLedgerProductImportDto.getIsChecked() == 1){
                        addQualityInspect(salesLedger, salesLedgerProduct);
                    }
                    salesLedgerProductMapper.insert(salesLedgerProduct);
                }
                // é‡‡è´­å®¡æ ¸
                addApproveByPurchase(loginUser,salesLedger);
            }

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

    public void addApproveByPurchase(LoginUser loginUser,PurchaseLedger purchaseLedger) throws Exception {
        ApproveProcessVO approveProcessVO = new ApproveProcessVO();
        approveProcessVO.setApproveType(5);
        approveProcessVO.setApproveDeptId(loginUser.getCurrentDeptId());
        approveProcessVO.setApproveReason(purchaseLedger.getPurchaseContractNumber());
        approveProcessVO.setApproveUserIds(purchaseLedger.getApproveUserIds());
        approveProcessVO.setApproveUser(loginUser.getUserId());
        approveProcessVO.setApproveTime(LocalDate.now().toString());
        approveProcessService.addApprove(approveProcessVO);
    }

    /**
     * ä¸‹åˆ’线命名转驼峰命名
     */

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

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

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

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

 src/main/resources/static/²É¹ºÌ¨Õ˵¼ÈëÄ£°å.xlsx

Binary files differ