修改导入模板

wujianwei

2025-12-24 cab8f5284fd5bef35584195246f2f5dc7ad4fd38

 service/src/main/java/com/ruoyi/cwgl/service/impl/ReceivableFeeManagementServiceImpl.java

@@ -1,13 +1,22 @@
package com.ruoyi.cwgl.service.impl;

import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;

import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.enums.SystemDataNoEnum;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.file.DownloadExportUtil;
import com.ruoyi.common.utils.file.DownloadExportUtil.ExprotStatus;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.cwgl.domain.ReceivableFeeDetail;
import com.ruoyi.cwgl.domain.ReceivableFeeManagementLog;
import com.ruoyi.cwgl.domain.vo.ReceivableFeeStatisticsVo;
@@ -26,6 +35,9 @@
import com.ruoyi.common.annotation.DataSource;
import com.ruoyi.common.enums.DataSourceType;
import com.ruoyi.common.core.service.BaseService;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.exception.UtilException;
import com.ruoyi.common.core.redis.RedisCache;

import com.ruoyi.cwgl.mapper.ReceivableFeeManagementMapper;
import com.ruoyi.cwgl.domain.ReceivableFeeManagement;
@@ -35,6 +47,8 @@
import com.ruoyi.common.core.text.Convert;

import javax.annotation.Resource;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * 应收费用管理Service业务层处理
@@ -66,6 +80,9 @@

    @Autowired
    private IReceivableFeeManagementLogService logService;

    @Autowired
    private RedisCache redisCache;

    /**
     * 查询应收费用管理
@@ -151,7 +168,7 @@
            String receivableAmountStr = calculateReceivableAmountStr(receivableFeeManagement.getReceivableFeeDetailList());
            receivableFeeManagement.setReceivableAmountStr(receivableAmountStr);
        }

        receivableFeeManagement.setCreateBy(SecurityUtils.getUsername());
        // 保存主表信息
        int result = receivableFeeManagementMapper.insertReceivableFeeManagement(receivableFeeManagement);
        
@@ -215,7 +232,7 @@
            // 设置每个明细的应收费用管理ID
            receivableFeeManagement.getReceivableFeeDetailList().forEach(detail -> {
                detail.setReceivableFeeId(receivableFeeId);
                detail.setUpdateTime(DateUtils.getNowDate()); // 设置更新时间
                detail.setCreateTime(DateUtils.getNowDate()); // 设置更新时间
            });
            
            // 调用批量插入方法
@@ -368,23 +385,52 @@
        SysConfig sysConfig = sysConfigMapper.selectConfig(new SysConfig() {{
            setConfigKey("sys.hk.rmb.rate");
        }});
        // 计算总应收金额
        BigDecimal totalReceivableAmount = receivableFeeList.stream()
                .map(ReceivableFeeManagement::getReceivableAmount)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        // 计算总应收金额（按币种分别计算）
        BigDecimal totalAmountRmb = BigDecimal.ZERO;
        BigDecimal totalAmountHkd = BigDecimal.ZERO;
        

        // 获取所有应收费用明细，按币种汇总
        if (!receivableFeeList.isEmpty()) {
            // 获取所有应收费用ID
            Integer[] feeIds = receivableFeeList.stream()
                    .map(ReceivableFeeManagement::getId)
                    .toArray(Integer[]::new);
            
            // 查询所有明细记录
            List<ReceivableFeeDetail> allDetails = receivableFeeDetailService.selectReceivableFeeDetailByReceivableFeeIds(feeIds);
            
            // 按币种汇总金额
            for (ReceivableFeeDetail detail : allDetails) {
                if (detail.getBillingAmount() != null && detail.getCurrency() != null) {
                    if ("RMB".equals(detail.getCurrency())) {
                        totalAmountRmb = totalAmountRmb.add(detail.getBillingAmount());
                    } else if ("HKD".equals(detail.getCurrency())) {
                        totalAmountHkd = totalAmountHkd.add(detail.getBillingAmount());
                    }
                }
            }
        }
        
        BigDecimal exchangeRate = new BigDecimal(sysConfig.getConfigValue());
        BigDecimal totalAmountHkd = totalReceivableAmount.divide(exchangeRate, 2, RoundingMode.HALF_UP);
        
        // 计算人民币总金额（人民币金额 + 港币换算成人民币）
        BigDecimal totalAmountRmbWithConversion = totalAmountRmb.add(
                totalAmountHkd.multiply(exchangeRate).setScale(2, RoundingMode.HALF_UP)
        );
        
        // 计算港币总金额（港币金额 + 人民币换算成港币）
        BigDecimal totalAmountHkdWithConversion = totalAmountHkd.add(
                totalAmountRmb.divide(exchangeRate, 2, RoundingMode.HALF_UP)
        );
        

        // 组装返回结果
        ReceivableFeeStatisticsVo result = new ReceivableFeeStatisticsVo();
        result.setDocumentCount(documentCount);
        result.setRate(exchangeRate);
        result.setTotalReceivableAmount(totalReceivableAmount);
        result.setTotalAmountRmb(totalReceivableAmount);
        result.setTotalAmountHkd(totalAmountHkd);
        result.setTotalReceivableAmount(totalAmountRmbWithConversion); // 默认使用人民币总金额
        result.setTotalAmountRmb(totalAmountRmbWithConversion);
        result.setTotalAmountHkd(totalAmountHkdWithConversion);
        result.setIds(ids);
        
        return result;
@@ -422,12 +468,13 @@
        billManagement.setCreateTime(DateUtils.getNowDate());
        billManagement.setBillName(billCreateVo.getBillName());
        billManagement.setCustomerName(billCreateVo.getCustomerName());
        billManagement.setCreateBy(SecurityUtils.getUsername());
        billManagement.setIsInternalSettlement(billCreateVo.getIsInternalSettlement());
        billManagement.setInternalSettlementUnit(billCreateVo.getInternalSettlementUnit());
        // 3. 根据账单类型设置币种和总金额
        if (billType == 0) {
            // 人民币账单
            billManagement.setCurrency("CNY");
            billManagement.setCurrency("RMB");
            billManagement.setTotalAmount(statisticsVo.getTotalAmountRmb());
        } else if (billType == 1) {
            // 港币账单
@@ -436,6 +483,7 @@
        } else {
            throw new IllegalArgumentException("无效的账单类型：" + billType);
        }
        billManagement.setPendingAmount(billManagement.getTotalAmount());
        String noByKey = systemDataNoService.getNoByKey(SystemDataNoEnum.YSZD);
        billManagement.setSystemNo(noByKey);
        // 4. 保存主账单记录
@@ -501,7 +549,7 @@
        Map<String, BigDecimal> currencyAmountMap = new HashMap<>();
        for (ReceivableFeeDetail detail : detailList) {
            String currency = detail.getCurrency();
            BigDecimal billingAmount = detail.getBillingAmount();
            BigDecimal billingAmount = detail.getActualAmount();
            
            if (currency != null && billingAmount != null) {
                currencyAmountMap.merge(currency, billingAmount, BigDecimal::add);
@@ -531,4 +579,289 @@
        
        return sb.toString();
    }

    /**
     * 导入应收费用管理数据（包含明细数据）
     * 
     * @param receivableFeeList 应收费用管理数据列表（包含明细数据）
     * @param operName 操作用户
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public String importReceivableFee(List<ReceivableFeeManagement> receivableFeeList, String operName) {
        if (receivableFeeList == null || receivableFeeList.isEmpty()) {
            throw new ServiceException("导入应收费用数据不能为空！");
        }
        int successNum = 0;
        int failureNum = 0;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        
        // 获取当前日期
        Date now = DateUtils.getNowDate();
        
        for (ReceivableFeeManagement receivableFee : receivableFeeList) {
            try {
                // 自动生成系统编号（和新增功能一样）
                String noByKey = systemDataNoService.getNoByKey(SystemDataNoEnum.YS);
                receivableFee.setSystemNo(noByKey);
                
                // 设置创建时间和创建人
                receivableFee.setCreateTime(now);
                receivableFee.setCreateBy(operName);
                
                // 计算应收金额字符串描述
                if (receivableFee.getReceivableFeeDetailList() != null && !receivableFee.getReceivableFeeDetailList().isEmpty()) {
                    String receivableAmountStr = calculateReceivableAmountStr(receivableFee.getReceivableFeeDetailList());
                    receivableFee.setReceivableAmountStr(receivableAmountStr);
                }
                
                // 插入主表数据
                receivableFeeManagementMapper.insertReceivableFeeManagement(receivableFee);
                
                // 处理明细数据（如果有）
                if (receivableFee.getReceivableFeeDetailList() != null && !receivableFee.getReceivableFeeDetailList().isEmpty()) {
                    for (ReceivableFeeDetail detail : receivableFee.getReceivableFeeDetailList()) {
                        detail.setReceivableFeeId(receivableFee.getId());
                        detail.setCreateBy(operName);
                        detail.setCreateTime(now);
                        receivableFeeDetailService.insertReceivableFeeDetail(detail);
                    }
                }
                
                successNum++;
                successMsg.append("<br/>" + successNum + ".应收费用 " + receivableFee.getSystemNo() + " 导入成功");
            } catch (Exception e) {
                failureNum++;
                String msg = "<br/>" + failureNum + ".应收费用 " + receivableFee.getSystemNo() + " 导入失败：";
                failureMsg.append(msg + e.getMessage());
                logger.error(msg, e);
            }
        }
        
        if (failureNum > 0) {
            failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
            throw new ServiceException(failureMsg.toString());
        } else {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
        }
        return successMsg.toString();
    }

    /**
     * 下载导入模板（包含Sheet1主表和Sheet2明细表）
     * 
     * @param exportKey 导出功能的唯一标识
     */
    @DataSource(DataSourceType.SLAVE)
    @Async
    @Override
    public void importTemplate(String exportKey) {
        String fileName = ExcelUtil.encodeFileName("应收费用导入模板");

        // 设置当前任务为"下载中"状态
        DownloadExportUtil.deleteDownloadFile(redisCache, exportKey, ExprotStatus.XZZ.getStatus());
        
        try {
            // 创建多Sheet导入模板
            createMultiSheetImportTemplate(exportKey,fileName);
            
            // 设置下载完成状态
            DownloadExportUtil.setDownloadFile(redisCache, exportKey, fileName);
            logger.info("导入模板导出完成: {}, file: {}", exportKey, fileName);
        } catch (Exception e) {
            logger.error("导入模板导出失败: {}, error: {}", exportKey, e.getMessage(), e);
            DownloadExportUtil.deleteDownloadFile(redisCache, exportKey, ExprotStatus.XZYC.getStatus()); // 设置失败状态
            throw e;
        }
    }
    
    /**
     * 创建多Sheet导入模板
     * 包含Sheet1：应收费用主表模板
     * 包含Sheet2：应收费用明细表模板
     *
     * @param exportKey 导出功能的唯一标识
     * @param fileName
     */
    private void createMultiSheetImportTemplate(String exportKey, String fileName) {

        
        try {
            // 创建新的工作簿，包含两个Sheet
            SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
            
            // 创建主表Sheet
            Sheet mainSheet = workbook.createSheet("应收费用主表");
            createMainSheetTemplateWithExcelUtil(mainSheet);
            
            // 创建明细表Sheet
            Sheet detailSheet = workbook.createSheet("应收费用明细");
            createDetailSheetTemplateWithExcelUtil(detailSheet);
            
            // 保存工作簿到文件
            try (OutputStream out = Files.newOutputStream(Paths.get(getAbsoluteFile(fileName)))) {
                workbook.write(out);
            }
            
            workbook.close();
            
        } catch (Exception e) {
            logger.error("创建多Sheet导入模板失败: {}", e.getMessage(), e);
            throw new UtilException("导入模板生成失败！");
        }
    }
    
    /**
     * 使用ExcelUtil创建主表Sheet模板
     * 
     * @param sheet Sheet对象
     */
    private void createMainSheetTemplateWithExcelUtil(Sheet sheet) {
        try {
            // 使用ExcelUtil创建主表模板
            ExcelUtil<ReceivableFeeManagement> mainSheetUtil = new ExcelUtil<>(ReceivableFeeManagement.class);
            
            // 初始化ExcelUtil
            mainSheetUtil.initialize("应收费用主表", "应收费用主表导入模板（请勿修改序号列）", Excel.Type.EXPORT);
            
            // 通过反射获取workbook字段
            Field workbookField = ExcelUtil.class.getDeclaredField("workbook");
            workbookField.setAccessible(true);
            SXSSFWorkbook excelUtilWorkbook = (SXSSFWorkbook) workbookField.get(mainSheetUtil);
            
            // 获取ExcelUtil生成的Sheet
            Sheet generatedSheet = excelUtilWorkbook.getSheetAt(0);
            
            // 复制标题行到目标Sheet
            copyRow(generatedSheet.getRow(0), sheet.createRow(0));
            
            // 复制表头行到目标Sheet
            copyRow(generatedSheet.getRow(1), sheet.createRow(1));
            
            // 添加说明行
            Row instructionRow = sheet.createRow(2);
            Cell instructionCell = instructionRow.createCell(0);
            instructionCell.setCellValue("说明：序号用于关联主表和明细表，请确保主表和明细表中的序号一致");
            
            // 设置列宽
            for (int i = 0; i < generatedSheet.getRow(1).getLastCellNum(); i++) {
                sheet.setColumnWidth(i, generatedSheet.getColumnWidth(i));
            }
            
        } catch (Exception e) {
            logger.error("使用ExcelUtil创建主表模板失败: {}", e.getMessage(), e);
            throw new UtilException("主表模板生成失败！");
        }
    }
    
    /**
     * 使用ExcelUtil创建明细表Sheet模板
     * 
     * @param sheet Sheet对象
     */
    private void createDetailSheetTemplateWithExcelUtil(Sheet sheet) {
        try {
            // 使用ExcelUtil创建明细表模板
            ExcelUtil<ReceivableFeeDetail> detailSheetUtil = new ExcelUtil<>(ReceivableFeeDetail.class);
            
            // 初始化ExcelUtil
            detailSheetUtil.initialize("应收费用明细", "应收费用明细表导入模板（序号与主表对应）", Excel.Type.EXPORT);
            
            // 通过反射获取workbook字段
            Field workbookField = ExcelUtil.class.getDeclaredField("workbook");
            workbookField.setAccessible(true);
            SXSSFWorkbook excelUtilWorkbook = (SXSSFWorkbook) workbookField.get(detailSheetUtil);
            
            // 获取ExcelUtil生成的Sheet
            Sheet generatedSheet = excelUtilWorkbook.getSheetAt(0);
            
            // 复制标题行到目标Sheet
            copyRow(generatedSheet.getRow(0), sheet.createRow(0));
            
            // 复制表头行到目标Sheet
            copyRow(generatedSheet.getRow(1), sheet.createRow(1));
            
            // 添加说明行
            Row instructionRow = sheet.createRow(2);
            Cell instructionCell = instructionRow.createCell(0);
            instructionCell.setCellValue("说明：序号必须与主表中的序号一致，用于关联主表和明细表数据");
            
            // 设置列宽
            for (int i = 0; i < generatedSheet.getRow(1).getLastCellNum(); i++) {
                sheet.setColumnWidth(i, generatedSheet.getColumnWidth(i));
            }
            
        } catch (Exception e) {
            logger.error("使用ExcelUtil创建明细表模板失败: {}", e.getMessage(), e);
            throw new UtilException("明细表模板生成失败！");
        }
    }
    
    /**
     * 复制行内容
     * 
     * @param sourceRow 源行
     * @param targetRow 目标行
     */
    private void copyRow(Row sourceRow, Row targetRow) {
        if (sourceRow == null) return;
        
        targetRow.setHeight(sourceRow.getHeight());
        
        for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            Cell sourceCell = sourceRow.getCell(i);
            if (sourceCell != null) {
                Cell targetCell = targetRow.createCell(i);
                copyCell(sourceCell, targetCell);
            }
        }
    }
    
    /**
     * 复制单元格内容和样式
     * 
     * @param sourceCell 源单元格
     * @param targetCell 目标单元格
     */
    private void copyCell(Cell sourceCell, Cell targetCell) {
        // 复制单元格样式
        targetCell.setCellStyle(sourceCell.getCellStyle());
        
        // 复制单元格值
        switch (sourceCell.getCellType()) {
            case STRING:
                targetCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                targetCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case BOOLEAN:
                targetCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                targetCell.setCellFormula(sourceCell.getCellFormula());
                break;
            default:
                targetCell.setCellValue(sourceCell.getStringCellValue());
        }
    }
    
    /**
     * 获取文件的绝对路径
     * 
     * @param fileName 文件名
     * @return 绝对路径
     */
    private String getAbsoluteFile(String fileName) {
        String downloadPath = RuoYiConfig.getDownloadPath();
        File desc = new File(downloadPath + File.separator + fileName);
        if (!desc.exists()) {
            if (!desc.getParentFile().exists()) {
                desc.getParentFile().mkdirs();
            }
        }
        return desc.getAbsolutePath();
    }
}