package com.kcim.service.impl; import cn.hutool.core.date.DatePattern; import cn.hutool.core.date.DateTime; import cn.hutool.core.date.DateUtil; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.kcim.common.constants.Constant; import com.kcim.common.constants.NumberConstant; import com.kcim.common.exception.CostException; import com.kcim.common.file.MinioConfig; import com.kcim.common.file.MinioFileUtil; import com.kcim.common.util.DateUtils; import com.kcim.common.util.PageUtils; import com.kcim.common.util.UserContext; import com.kcim.dao.model.KpiComputeImport; import com.kcim.dao.repository.KpiComputeImportRepository; import com.kcim.service.CenterService; import com.kcim.service.KpiComputeImportService; import com.kcim.vo.CommonTitleVo; import com.kcim.vo.DictDataVo; import com.kcim.vo.SheetImportResultVO; import com.kcim.vo.UserInfoVO; import lombok.SneakyThrows; import lombok.extern.slf4j.Slf4j; import org.apache.commons.io.FileUtils; import org.apache.ibatis.jdbc.SqlRunner; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import org.springframework.util.CollectionUtils; import org.springframework.web.multipart.MultipartFile; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.*; import java.util.stream.Collectors; /** * @program: CostAccount * @description: * @author: Wang.YS * @create: 2024-06-03 13:58 **/ @Service("kpiComputeImportService") @Slf4j public class KpiComputeImportServiceImpl implements KpiComputeImportService { private final KpiComputeImportRepository repository; private final MinioConfig minioConfig; private final MinioFileUtil minioFileUtil; private final CenterService centerService; @Value("${spring.datasource.driver-class-name}") private String driver; @Value("${spring.datasource.url}") private String url; @Value("${spring.datasource.username}") private String username; @Value("${spring.datasource.password}") private String password; public KpiComputeImportServiceImpl(KpiComputeImportRepository repository, MinioConfig minioConfig, MinioFileUtil minioFileUtil, CenterService centerService) { this.repository = repository; this.minioConfig = minioConfig; this.minioFileUtil = minioFileUtil; this.centerService = centerService; } /** * 查询导入列表 * * @param current 当前页 * @param pageSize 页容量 * @param computeDate 核算年月 * @param tableName 表名 * @return 导入列表 */ @Override public Object getList(Integer current, Integer pageSize, String computeDate, String tableName) { List centerEmployee = centerService.getCenterUserInfo(null); Map userName = centerEmployee.stream().collect(Collectors.toMap(UserInfoVO::getId, UserInfoVO::getName, (a, b) -> b)); DictDataVo dict = centerService.getDict(Constant.SPECIAL_SHEET_MAP); if (Objects.isNull(dict)) { throw new CostException("未找到特殊项目导入字典"); } List dataVoList = dict.getDataVoList(); if (CollectionUtils.isEmpty(dataVoList)) { throw new CostException("未找到特殊项目导入字典"); } Map dictionaryCodeNameMap = dataVoList.stream().collect(Collectors.toMap(DictDataVo::getCode, DictDataVo::getName, (a, b) -> b)); Page page = repository.getPage(current, pageSize, computeDate, tableName); List list = page.getRecords(); if (CollectionUtils.isEmpty(list)) { return new PageUtils(new ArrayList<>(), NumberConstant.ZERO, pageSize, current); } for (KpiComputeImport computeImport : list) { computeImport.setCreateUserName(userName.get(Long.valueOf(computeImport.getCreateUser()))); computeImport.setTableNameDisplay(dictionaryCodeNameMap.get(computeImport.getTableName())); } return new PageUtils(list, Math.toIntExact(page.getTotal()), pageSize, current); } /** * @return */ @Override public Object getTableList() { DictDataVo dict = centerService.getDict(Constant.SPECIAL_SHEET_MAP); if (Objects.isNull(dict)) { throw new CostException("未找到特殊项目导入字典"); } List dataVoList = dict.getDataVoList(); if (CollectionUtils.isEmpty(dataVoList)) { throw new CostException("未找到特殊项目导入字典"); } Map dictionaryCodeNameMap = dataVoList.stream().collect(Collectors.toMap(DictDataVo::getCode, DictDataVo::getName, (a, b) -> b)); List list = new ArrayList<>(); if (!CollectionUtils.isEmpty(dictionaryCodeNameMap)) { list = dictionaryCodeNameMap.keySet().stream().map(s -> new CommonTitleVo(s, dictionaryCodeNameMap.get(s))).collect(Collectors.toList()); } return list; } /** * 导入数据 * * @param computeDate 核算年月 * @param tableName 表名 * @param file 导入文件 */ @Override @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED) public void importData(String computeDate, String tableName, MultipartFile file) { //excel 数据导入 String fileName = file.getOriginalFilename(); //导入文件上传 String uploadFileUrl = uploadFile(file, fileName); //导入记录添加 KpiComputeImport kpiComputeImport = new KpiComputeImport(); kpiComputeImport.setComputeDate(computeDate); kpiComputeImport.setImportStatus(NumberConstant.ZERO); kpiComputeImport.setCreateTime(new Date()); kpiComputeImport.setCreateUser(String.valueOf(UserContext.getCurrentUser().getId())); kpiComputeImport.setUrl(uploadFileUrl); kpiComputeImport.setTableName(tableName); kpiComputeImport.setFileName(fileName); kpiComputeImport.setHospId(UserContext.getCurrentLoginHospId()); kpiComputeImport.setDelFlag(NumberConstant.ZERO); repository.save(kpiComputeImport); insertExcelData(kpiComputeImport.getId(), tableName, file, fileName, computeDate); } private void insertExcelData(Integer id, String tableName, MultipartFile file, String fileName, String computeDate) { try { assert fileName != null; File file1 = new File(fileName); FileUtils.copyInputStreamToFile(file.getInputStream(), file1); String excelSql = readExcelSql(file1, tableName, id); SqlRunner sqlRunner = new SqlRunner(getConnection()); sqlRunner.insert(excelSql); //更新新增人和时间 String updateSql = "update " + "`" + tableName + "`" + "set" + "`create_user` = '" + UserContext.getCurrentUser().getId() + "' , `create_time` = '" + DateUtils.formatDate2String(new Date()) + "' where `compute_date` = '" + computeDate + "' and `hosp_id` = " + UserContext.getCurrentLoginHospId() + ";"; sqlRunner.run(updateSql); } catch (IOException | SQLException | ClassNotFoundException e) { throw new RuntimeException(e); } } private String uploadFile(MultipartFile file, String fileName) { DateTime date = DateUtil.date(); int month = DateUtil.month(date) + 1; int year = DateUtil.year(date); int day = DateUtil.dayOfMonth(date); Long hospId = UserContext.getCurrentLoginHospId(); String fileUrl = ""; try { String format = DateUtil.format(date, DatePattern.PURE_DATETIME_PATTERN); String originalFilename = format + fileName; String dataDirectory = minioConfig.getBucketName(); String uploadFileName = "upload" + "/" + hospId + "/" + year + "/" + month + "/" + day + "/" + originalFilename; InputStream inputStream = file.getInputStream(); minioFileUtil.putObject(dataDirectory, uploadFileName, inputStream); fileUrl = minioFileUtil.getObjectUrl(dataDirectory, uploadFileName); } catch (IOException e) { throw new RuntimeException(e); } return fileUrl; } /** * 作废导入数据 * * @param id 记录id */ @Override @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED) public void removeImport(Integer id) { KpiComputeImport kpiComputeImport = repository.getById(id); if (Objects.nonNull(kpiComputeImport)) { //更新表信息 // String computeDate = kpiComputeImport.getComputeDate(); String tableName = kpiComputeImport.getTableName(); Long hospId = kpiComputeImport.getHospId(); // TODO: 2023/7/24 导入的数据可能不是这个月的，用记录id 进行删除，有问题再看 StringBuilder delSql = new StringBuilder().append("update " + "`").append(tableName).append("`").append("set") .append("`del_flag` = 1 , `update_user` = '").append(UserContext.getCurrentUser().getId()) .append("' , `update_time` = '").append(DateUtils.formatDate2String(new Date())).append("' where `hosp_id` = ").append(hospId) .append(" and `import_id` = ").append(id).append(";"); try { log.info("执行的sql语句：" + delSql); SqlRunner sqlRunner = new SqlRunner(getConnection()); sqlRunner.run(delSql.toString()); } catch (ClassNotFoundException | SQLException e) { throw new RuntimeException("删除导入数据错误" + e); } //更新 记录表信息 kpiComputeImport.setImportStatus(NumberConstant.ONE); kpiComputeImport.setUpdateUser(String.valueOf(UserContext.getCurrentUser().getId())); kpiComputeImport.setUpdateTime(new Date()); repository.updateById(kpiComputeImport); } } @Override @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED) public List importMultipleSheets(String computeDate, MultipartFile file) { String fileName = file.getOriginalFilename(); String uploadFileUrl = uploadFile(file, fileName); List sheetResults = new ArrayList<>(); try { File tempFile = File.createTempFile("excel-", ".tmp"); file.transferTo(tempFile); List tableList = (List) getTableList(); try (Workbook workbook = WorkbookFactory.create(tempFile)) { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); String sheetName = sheet.getSheetName(); SheetImportResultVO resultItem = new SheetImportResultVO(); resultItem.setSheetName(sheetName); resultItem.setSheetIndex(i); Optional mapping = tableList.stream() .filter(t -> t.getName().equals(sheetName)) .findFirst(); if (!mapping.isPresent()) { log.warn("未找到sheet页 '{}' 对应的数据表配置，跳过导入", sheetName); resultItem.setSuccess(false); resultItem.setErrorMessage("未找到对应的数据库表配置"); sheetResults.add(resultItem); continue; } String tableName = mapping.get().getCode(); resultItem.setTableName(tableName); try { KpiComputeImport kpiComputeImport = new KpiComputeImport(); kpiComputeImport.setComputeDate(computeDate); kpiComputeImport.setImportStatus(NumberConstant.ZERO); kpiComputeImport.setCreateTime(new Date()); kpiComputeImport.setCreateUser(String.valueOf(UserContext.getCurrentUser().getId())); kpiComputeImport.setUrl(uploadFileUrl); kpiComputeImport.setTableName(tableName); kpiComputeImport.setFileName(String.format("%s_%s", fileName, sheetName)); kpiComputeImport.setHospId(UserContext.getCurrentLoginHospId()); kpiComputeImport.setDelFlag(NumberConstant.ZERO); repository.save(kpiComputeImport); insertExcelDataForSheet(kpiComputeImport.getId(), tableName, file, fileName, computeDate, sheet); resultItem.setSuccess(true); resultItem.setImportId(kpiComputeImport.getId()); resultItem.setImportTime(kpiComputeImport.getCreateTime()); } catch (Exception e) { log.error("导入sheet页 '{}' 出错: {}", sheetName, e.getMessage(), e); resultItem.setSuccess(false); resultItem.setErrorMessage("导入失败: " + e.getMessage()); } sheetResults.add(resultItem); } } // 删除临时文件 tempFile.delete(); } catch (Exception e) { throw new RuntimeException("导入发生异常", e); } return sheetResults; } @SneakyThrows private void insertExcelDataForSheet(Integer id, String tableName, MultipartFile file, String fileName, String computeDate, Sheet sheet) { try { // 将MultipartFile转换为InputStream InputStream inputStream = file.getInputStream(); // 这里假设有一个方法可以从给定的InputStream和sheet对象中读取SQL语句 String excelSql = readExcelSqlFromSheet(inputStream, sheet, tableName, id); // 执行SQL插入操作 SqlRunner sqlRunner = new SqlRunner(getConnection()); sqlRunner.insert(excelSql); // 更新新增人和时间 String updateSql = "UPDATE `" + tableName + "` SET " + "`create_user` = '" + UserContext.getCurrentUser().getId() + "', " + "`create_time` = '" + DateUtils.formatDate2String(new Date()) + "' " + "WHERE `compute_date` = '" + computeDate + "' AND `hosp_id` = " + UserContext.getCurrentLoginHospId() + ";"; sqlRunner.run(updateSql); } catch (IOException | SQLException e) { // 记录日志而不是抛出异常，以避免中断其他sheet页的处理 log.error("Error inserting data for sheet: {}", sheet.getSheetName(), e); } } private String readExcelSqlFromSheet(InputStream inputStream, Sheet sheet, String tableName, Integer id) throws IOException { StringBuilder sqlBuilder = new StringBuilder(); boolean firstRow = true; Row headerRow = null; DataFormatter dataFormatter = new DataFormatter(); int rowCount = 0; for (Row row : sheet) { // 第一行作为标题行 if (rowCount == 0) { headerRow = row; rowCount++; continue; } if (rowCount == 1) { // 第二行为说明行，跳过 rowCount++; continue; } sqlBuilder.append("INSERT INTO `").append(tableName).append("` ("); // 构建字段名部分 for (int i = 0; i < headerRow.getLastCellNum(); i++) { if (i > 0) { sqlBuilder.append(", "); } Cell headerCell = headerRow.getCell(i); sqlBuilder.append("`").append(headerCell.getStringCellValue()).append("`"); } sqlBuilder.append(", `import_id`) VALUES ("); // 构建值部分 for (int i = 0; i < row.getLastCellNum(); i++) { if (i > 0) { sqlBuilder.append(", "); } Cell cell = row.getCell(i); if (cell == null) { sqlBuilder.append("NULL"); continue; } switch (cell.getCellType()) { case STRING: sqlBuilder.append("'").append(cell.getStringCellValue()).append("'"); break; case NUMERIC: String formattedValue = dataFormatter.formatCellValue(cell); // 判断是否是日期类型 if (HSSFDateUtil.isCellDateFormatted(cell)) { sqlBuilder.append("'").append(cell.getDateCellValue()).append("'"); } else { sqlBuilder.append(formattedValue); } break; case BOOLEAN: sqlBuilder.append(cell.getBooleanCellValue() ? 1 : 0); break; case FORMULA: sqlBuilder.append("'").append(cell.getCellFormula()).append("'"); break; case BLANK: sqlBuilder.append("NULL"); break; default: sqlBuilder.append("NULL"); } } sqlBuilder.append(", ").append(id).append(");\n"); rowCount++; } return sqlBuilder.toString(); } /** * 复原导入数据 * * @param id 记录id */ @Override @Transactional(rollbackFor = Throwable.class, propagation = Propagation.REQUIRED) public void recoverImport(Integer id) { KpiComputeImport kpiComputeImport = repository.getById(id); if (Objects.nonNull(kpiComputeImport)) { //更新表信息 String computeDate = kpiComputeImport.getComputeDate(); String tableName = kpiComputeImport.getTableName(); Long hospId = kpiComputeImport.getHospId(); StringBuilder delSql = new StringBuilder().append("update " + "`").append(tableName).append("`").append("set") .append("`del_flag` = 0 , `update_user` = '").append(UserContext.getCurrentUser().getId()) .append("' , `update_time` = '").append(DateUtils.formatDate2String(new Date())).append("' where `hosp_id` = ").append(hospId) .append(" and `import_id` = ").append(id).append(";"); try { log.info("执行的sql语句：" + delSql); SqlRunner sqlRunner = new SqlRunner(getConnection()); sqlRunner.run(delSql.toString()); } catch (ClassNotFoundException | SQLException e) { throw new RuntimeException("复原数据错误" + e); } //更新 记录表信息 kpiComputeImport.setImportStatus(NumberConstant.ZERO); kpiComputeImport.setUpdateUser(String.valueOf(UserContext.getCurrentUser().getId())); kpiComputeImport.setUpdateTime(new Date()); repository.updateById(kpiComputeImport); } } public static String readExcelSql(File file, String tableName, Integer id) throws IOException { // 获取excel工作簿对象 String sql = null; String fileName = file.getName(); String substring = fileName.substring(fileName.lastIndexOf(".")); if (".xls".equals(substring)) { HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file)); for (Sheet sheet : workbook) { List headerList = new ArrayList<>(); List> dataList = new ArrayList<>(); System.out.println(sheet.getSheetName()); int i = 0; for (Row row : sheet) { if (i == 1) { i++; continue; } if (i == 0) { if (i == 0) { for (Cell cell : row) { headerList.add(cell.getStringCellValue()); } // if(headerList.size() > 0) { // String createTableSql = createTable(headerList, sheetName); // System.out.println("生成的创建表语句："+ createTableSql); // } } } else { Map map = new HashMap<>(); int j = 0; for (Cell cell : row) { //设置单元格类型 cell.setCellType(CellType.STRING); map.put(headerList.get(j), cell.getStringCellValue()); j++; } dataList.add(map); } i++; } //组装记录表和hospId setCommonData(id, dataList); sql = insertFromMap(dataList, tableName); } } else if (".xlsx".equals(substring)) { XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file)); for (Sheet sheet : workbook) { List headerList = new ArrayList<>(); List> dataList = new ArrayList<>(); System.out.println(sheet.getSheetName()); String sheetName = sheet.getSheetName(); int i = 0; for (Row row : sheet) { if (i == 1) { i++; continue; } if (i == 0) { if (i == 0) { for (Cell cell : row) { headerList.add(cell.getStringCellValue()); } // if(headerList.size() > 0) { // String createTableSql = createTable(headerList, sheetName); // System.out.println("生成的创建表语句："+ createTableSql); // } } } else { Map map = new HashMap<>(); int j = 0; for (Cell cell : row) { //设置单元格类型 cell.setCellType(CellType.STRING); map.put(headerList.get(j), cell.getStringCellValue()); j++; } dataList.add(map); } i++; } setCommonData(id, dataList); sql = insertFromMap(dataList, tableName); } } return sql; } private static void setCommonData(Integer id, List> dataList) { if (!CollectionUtils.isEmpty(dataList)) { dataList.forEach(stringObjectMap -> { stringObjectMap.put("hosp_id", UserContext.getCurrentLoginHospId()); stringObjectMap.put("import_id", id); }); } } /** * 生成创建表结构 * * @param headerList 表头 * @param sheetName sheet名 * @return */ public static String createTable(List headerList, String sheetName) { StringBuffer createTableSql = new StringBuffer(); if (headerList.size() > 0) { createTableSql.append("SET NAMES utf8mb4;\n"); createTableSql.append("SET FOREIGN_KEY_CHECKS = 0;\n"); createTableSql.append("DROP TABLE IF EXISTS `" + sheetName + "`;\n"); createTableSql.append("CREATE TABLE `" + sheetName + "`"); createTableSql.append("(\n"); createTableSql.append("`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',\n"); int k = 0; for (String key : headerList) { createTableSql.append("`" + key + "` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,\n"); k++; if (k == headerList.size()) { createTableSql.append("PRIMARY KEY (`id`) USING BTREE)\n"); createTableSql.append("ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '' ROW_FORMAT = Dynamic;\n"); createTableSql.append("SET FOREIGN_KEY_CHECKS = 1;"); } } } return createTableSql.toString(); } /** * map对象生成insert插入语句 * * @param dataList * @param tableName */ public static String insertFromMap(List> dataList, String tableName) { String sql = null; if (dataList.size() > 0) { int i = 0; StringBuilder strKey = new StringBuilder(); //插入sql语句 StringBuilder insertSql = new StringBuilder().append("INSERT INTO " + "`").append(tableName).append("`"); StringBuilder value = new StringBuilder(); for (Map map : dataList) { //存入key的字符串数组 //存入value的字符串数组 ArrayList arrValue = new ArrayList<>(); //拼接sql ArrayList arrKey = new ArrayList<>(map.keySet()); for (String keys : map.keySet()) { arrValue.add(map.get(keys)); } if (i == 0) { //遍历存的key字符串数组拼接sql for (int j = 0; j < arrKey.size(); j++) { strKey.append("`").append(arrKey.get(j)).append("`"); if (j != arrKey.size() - 1) {//拼上","最后一个不拼 strKey.append(","); } } } i++; StringBuilder strVal = new StringBuilder(); //遍历存的value字符串数组拼接sql for (int j = 0; j < arrValue.size(); j++) { if (null != arrValue.get(j) && !"".equals(arrValue.get(j))) { strVal.append("'").append(arrValue.get(j)).append("'");//拼接单引号 } else if ("".equals(arrValue.get(j))) { strVal.append((String) null); } else { strVal.append(arrValue.get(j)); } if (j != arrValue.size() - 1) {//拼上","最后一个不拼 strVal.append(","); } } String stringEntryVal = strVal.toString(); value.append("(").append(stringEntryVal).append(")"); if (i < dataList.size()) { value.append(","); } } insertSql.append("(").append(strKey).append(")"); insertSql.append(" VALUES "); insertSql.append(value).append(";"); System.out.println("生成插入数据sql:" + insertSql.toString()); sql = insertSql.toString(); } return sql; } private Connection getConnection() throws ClassNotFoundException, SQLException { Class.forName(driver); return DriverManager.getConnection(url, username, password); } }