package com.estate.expert.admin.initdata;
|
|
import com.estate.expert.admin.domain.standard.hierachy.Hierarchy;
|
import com.estate.expert.admin.domain.standard.hierachy.HierarchyData;
|
import com.estate.expert.admin.domain.standard.hierachy.HierarchyIds;
|
import com.estate.expert.admin.domain.standard.problem.Problem;
|
import com.estate.expert.admin.domain.standard.problem.detail.NumberFactor;
|
import com.estate.expert.admin.domain.standard.problem.detail.ScaleFactor;
|
import com.estate.expert.admin.domain.standard.problem.enums.IncidentType;
|
import com.estate.expert.admin.initdata.common.ImportUtil;
|
import com.estate.expert.admin.service.HierarchyService;
|
import com.estate.expert.admin.service.ProblemService;
|
import com.estate.expert.admin.service.UserService;
|
import com.estate.expert.admin.utils.tree.TreeNode;
|
import lombok.Builder;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.io.FileUtils;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.UUID;
|
|
/**
|
* @ClassName ImportProblemJob
|
* @Description
|
* @Author daisy
|
* @Date 2023/12/15
|
**/
|
@Builder
|
@Slf4j
|
public class ImportProblemJob {
|
|
private UserService userService;
|
|
private HierarchyService hierarchyService;
|
|
private ProblemService problemService;
|
|
public void importAllProblem() {
|
importProblems("/综合体系-标准-问题", HierarchyIds.SYSTEM);
|
importProblems("/设备体系-标准-问题", HierarchyIds.DEVICE);
|
}
|
|
public void importProblems(String rootPath, Integer hierarchyId) {
|
var hierarchy = hierarchyService.get(hierarchyId);
|
List<String> fileList = loadDataFiles(rootPath);
|
List<Problem> allProblems = new ArrayList<>();
|
for (String filePath: fileList) {
|
var node = getHierarchyNode(hierarchy, filePath);
|
var problems = loadProblems(filePath);
|
if(node != null){
|
for(Problem problem: problems){
|
problem.setHierarchyPath(node.getPath());
|
problem.setHierarchyId(hierarchy.getId());
|
problem.setName(UUID.randomUUID().toString());
|
}
|
allProblems.addAll(problems);
|
}
|
}
|
problemService.addEntities(null, allProblems);
|
}
|
|
private TreeNode<HierarchyData> getHierarchyNode(Hierarchy hierarchy, String path){
|
path = path.replace("\\", "/");
|
var namePath = path.substring(path.indexOf("业务标准") + 4, path.lastIndexOf("/"));
|
namePath = namePath.replace("/设备体系-标准-问题", "");
|
namePath = namePath.replace("/综合体系-标准-问题", "");
|
var node = hierarchy.getContent().getByNamePath(namePath);
|
|
if(node == null){
|
log.error("node is null, filePath: " + namePath);
|
}
|
return node;
|
}
|
|
public void testLoadProblem(){
|
var path = ImportUtil.docsRoot("/综合体系-标准-问题/安全检查/市政工程（成都）/临时消防/临时消防/消防实体安全/临时用房防火/data.xlsx");
|
// var path = ImportUtil.docsRoot("/综合体系-标准-问题/安全检查/建筑工程（通用）/模板支架/模板支架/一般项目/底座与托撑/data.xlsx");
|
loadProblems(path);
|
}
|
|
/*
|
* 读取文件夹下的data.xlsx文件
|
*/
|
private List<String> loadDataFiles(String path){
|
List<String> fileList = new ArrayList<>();
|
FileUtils.listFiles(new File(ImportUtil.docsRoot(path)), new String[]{"xlsx"}, true).forEach(f -> {
|
if (f.getName().equals("data.xlsx"))
|
fileList.add(f.getAbsolutePath());
|
});
|
return fileList;
|
}
|
|
/**
|
* 从excel文件中读取数据，生成problem数据
|
*/
|
private List<Problem> loadProblems(String filePath) {
|
var problems = new ArrayList<Problem>();
|
|
// 从第1列到第10列,分别是: 描述 规范与条款 建议措施 数值 事故类型 点评范式 责任人 责任部门 事故案例 正确做法
|
// 11列:名称、16列:对应数值、21:扣除分数、26:附加分数
|
// 然后我们最多会循环5次,
|
|
try {
|
FileInputStream fileInputStream = new FileInputStream(filePath);
|
Workbook workbook = WorkbookFactory.create(fileInputStream);
|
Sheet sheet = workbook.getSheetAt(0);
|
int rowCount = sheet.getLastRowNum();
|
for (int i = 0; i <= rowCount; i++) {
|
if (sheet.getRow(i) == null || sheet.getRow(i).getCell(0) == null) {
|
continue;
|
}
|
String description = sheet.getRow(i).getCell(0).getStringCellValue();
|
if (StringUtils.isBlank(description)) {
|
continue;
|
}
|
// 规范与条款和建议措施都放到problem_info里
|
String standardAndClause = sheet.getRow(i).getCell(1).getStringCellValue();
|
String suggestion = sheet.getRow(i).getCell(2).getStringCellValue();
|
String numValue = sheet.getRow(i).getCell(3).getStringCellValue();
|
String incidentTypes = sheet.getRow(i).getCell(4).getStringCellValue();
|
String comment = sheet.getRow(i).getCell(5).getStringCellValue();
|
String responsiblePerson = sheet.getRow(i).getCell(6).getStringCellValue();
|
String responsibleDepartment = sheet.getRow(i).getCell(7).getStringCellValue();
|
String accidentCase = sheet.getRow(i).getCell(8).getStringCellValue();
|
String correctMethod = sheet.getRow(i).getCell(9).getStringCellValue();
|
|
// 读取第11列到第15列
|
// String name = sheet.getRow(i).getCell(10).getStringCellValue();
|
// String value1 = sheet.getRow(i).getCell(15).getStringCellValue();
|
// String value2 = sheet.getRow(i).getCell(20).getStringCellValue();
|
// String value3 = sheet.getRow(i).getCell(25).getStringCellValue();
|
|
// 插入到problem表
|
Problem problem = new Problem();
|
problem.setDescription(description);
|
// 如果能解析成数字, 就设置numValue，否则设为1
|
try {
|
problem.setNumValue(Float.parseFloat(numValue));
|
} catch (Exception e) {
|
problem.setNumValue(1.0f);
|
}
|
problem.setIncidentTypes(IncidentType.parse(incidentTypes));
|
problem.setReviewParadigm(comment);
|
problem.setResponsiblePerson(responsiblePerson);
|
problem.setResponsibleDepartment(responsibleDepartment);
|
// problem.setIncidentCase(EntityRef.of(2));
|
problem.setCorrectPractice(correctMethod);
|
|
// 插入到problem_info表
|
if (!StringUtils.isBlank(standardAndClause)) {
|
problem.setSpecificTerms(List.of(standardAndClause));
|
}
|
|
if (!StringUtils.isBlank(suggestion)) {
|
problem.setSuggestActions(List.of(suggestion));
|
}
|
|
// 11列:名称、16列:对应数值、21:扣除分数、26:附加分数
|
// 11, 12, 13
|
// 然后我们最多会循环5次
|
var numberFactorList = new ArrayList<NumberFactor>();
|
for (int k = 0; k < 5; k++) {
|
var name = sheet.getRow(i).getCell(10 + k).getStringCellValue();
|
if (StringUtils.isBlank(name)) {
|
break;
|
}
|
|
try {
|
var v1 = Float.parseFloat(sheet.getRow(i).getCell(15 + k).getStringCellValue());
|
var v2 = Float.parseFloat(sheet.getRow(i).getCell(20 + k).getStringCellValue());
|
var v3 = Float.parseFloat(sheet.getRow(i).getCell(25 + k).getStringCellValue());
|
|
NumberFactor factor = new NumberFactor();
|
factor.setName(name);
|
factor.setValue(v1);
|
factor.setDeductPoint(v2);
|
factor.setAdditionPoint(v3);
|
numberFactorList.add(factor);
|
}catch (Exception e){
|
log.error("read excel error, filePath: {}", filePath);
|
}
|
}
|
problem.setNumberFactors(numberFactorList);
|
|
// 36列:name 、39列:factor
|
// 36,39; 37,40; 38,41
|
var scaleFactorList = new ArrayList<ScaleFactor>();
|
for (int k = 0; k < 3; k++) {
|
String name = sheet.getRow(i).getCell(35 + k).getStringCellValue();
|
if (StringUtils.isBlank(name)) {
|
break;
|
}
|
|
var v1 = Float.parseFloat(sheet.getRow(i).getCell(38 + k).getStringCellValue());
|
var scaleFactor = new ScaleFactor();
|
scaleFactor.setName(name);
|
scaleFactor.setFactor(v1);
|
scaleFactorList.add(scaleFactor);
|
}
|
problem.setScaleFactors(scaleFactorList);
|
problems.add(problem);
|
}
|
} catch (Exception e) {
|
log.error("read excel error, filePath: {}", filePath, e);
|
}
|
return problems;
|
}
|
|
}
|
