From bf5b01b14dc7bfc214e646425a62f5593890d7e3 Mon Sep 17 00:00:00 2001
From: jinlin <jinlin>
Date: 星期六, 08 十一月 2025 22:05:37 +0800
Subject: [PATCH] 清洗数据库日期错误，并且导入日期通用化处理
---
src/main/java/com/example/server/progressTrack/service/DjJdgzNetworkLevel3Service.java | 288 ++++++++++++++++++++++++++++++++++-----------------------
1 files changed, 173 insertions(+), 115 deletions(-)
diff --git a/src/main/java/com/example/server/progressTrack/service/DjJdgzNetworkLevel3Service.java b/src/main/java/com/example/server/progressTrack/service/DjJdgzNetworkLevel3Service.java
index c17dd52..89805e3 100644
--- a/src/main/java/com/example/server/progressTrack/service/DjJdgzNetworkLevel3Service.java
+++ b/src/main/java/com/example/server/progressTrack/service/DjJdgzNetworkLevel3Service.java
@@ -2,22 +2,27 @@
import com.example.client.dto.JComboBoxItem;
import com.example.client.service.BaseService;
+import com.example.client.utils.TranslateToPlainStrUtils;
+import com.example.server.ExportExcel.dto.ExcelColumnDto;
+import com.example.server.ExportExcel.dto.ExprotExcelDto;
+import com.example.server.ExportExcel.method.ExcelExport;
import com.example.server.cabin.service.CabinService;
+import com.example.server.progressTrack.Dto.Level3ExportCloum;
import com.example.server.progressTrack.Dto.NetworkNodeStatusDto;
import com.example.server.progressTrack.Dto.StatistProductDto;
import com.example.server.progressTrack.Dto.TableNodeDto;
import com.example.server.progressTrack.dao.DjJdgzNetworkLevel3Dao;
import com.example.server.progressTrack.model.DjJdgzNetworkLevel3;
import com.example.server.progressTrack.model.DjJdgzNetworkLevel3List;
+import com.example.server.progressTrack.model.DjJdgzTrackRecord;
import com.example.server.teamGroup.service.SysTeamGroupClassService;
+import com.example.server.utils.ExcelImportException;
import com.example.server.utils.FileUtils;
import com.example.server.utils.ImportUtil;
import com.example.server.utils.UUIDUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
-import org.apache.poi.ss.usermodel.Row;
-import org.apache.poi.ss.usermodel.Sheet;
-import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
@@ -53,6 +58,69 @@
public List<StatistProductDto> getStatist(Long level1Id,String type) {
return baseDao.getStatist(level1Id,type);
+ }
+
+ public void exportExcel(String path, Long level1NetworkId, Long deptId, Long teamGroupId, String status) {
+ List<ExprotExcelDto> dtos = new ArrayList<>();
+ ExprotExcelDto dto = new ExprotExcelDto();
+ dto.setSheetName("璁惧鑺傜偣鐘舵�佽褰�");
+
+ List<Level3ExportCloum> list = baseDao.getdata(level1NetworkId, deptId, teamGroupId, status);
+ Date today = new Date();
+ for (Level3ExportCloum data :list){
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+ Date requiredCompletionDate = null;
+ Date actualCompletionDate = null;
+ try {
+ if (StringUtils.isNotBlank(data.getRequiredCompletionTime())) {
+ requiredCompletionDate = sdf.parse(data.getRequiredCompletionTime());
+ }
+ if (StringUtils.isNotBlank(data.getActualCompletion())) {
+ actualCompletionDate = sdf.parse(data.getActualCompletion());
+ }
+
+ } catch (ParseException e) {
+ e.printStackTrace();
+ }
+ if (data.getCurrentStatus() == null || data.getCurrentStatus() == 0) {
+ if (today.after(requiredCompletionDate)) {
+ int days = (int) ((today.getTime() - requiredCompletionDate.getTime()) / (1000 * 60 * 60 * 24));
+ data.setStatus("宸查�炬湡" + days + "澶�");
+ } else if ((requiredCompletionDate.getTime() - today.getTime()) / (1000 * 60 * 60 * 24) < 7) {
+ int days = (int) ((requiredCompletionDate.getTime() - today.getTime()) / (1000 * 60 * 60 * 24));
+ data.setStatus("杩樻湁" + days + "澶╀复鏈�");
+ } else {
+ data.setStatus("杩涜涓�");
+ }
+ } else if (data.getCurrentStatus() == 1) {
+ // 宸插畬鎴�
+ if (data.getActualCompletion() != null && actualCompletionDate.after(requiredCompletionDate)) {
+ int days = (int) ((actualCompletionDate.getTime() - requiredCompletionDate.getTime()) / (1000 * 60 * 60 * 24));
+ data.setStatus("瓒呮湡" + days + "澶╁畬鎴�");
+ } else {
+ data.setStatus("姝ｅ父瀹屾垚");
+ }
+ }
+
+ data.setDeptName(sysTeamGroupClassService.get(data.getDeptId()).getName());
+ data.setTeamName(sysTeamGroupClassService.get(data.getTeamgroupId()).getName());
+ }
+ dto.setDataList(list);
+ List<ExcelColumnDto> columnDto2 = new ArrayList<>();
+ columnDto2.add(new ExcelColumnDto("閮ㄩ棬", "deptName", 25, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("涓撲笟", "teamName", 25, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("璁惧鍚嶇О", "name", 50, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("鑺傜偣鍚嶇О", "processName", 25, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("瑕佹眰瀹屾垚鏃堕棿", "requiredCompletionTime", 25, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("鐘舵��", "status", 25, HorizontalAlignment.LEFT));
+ dto.setColumnDto(columnDto2);
+ dtos.add(dto);
+ try {
+ ExcelExport.getHSSFWorkbook(path, dtos);
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+
}
enum StatusEnum {zy, jxz, lq, yq, zcwc, cqwc}
@@ -171,11 +239,13 @@
String teamGroupStr = ImportUtil.getCellValue(row, 4, pattern);
String cabinStr = ImportUtil.getCellValue(row, 5, pattern);
String typeStr = ImportUtil.getCellValue(row, 6, pattern);
- String time1 = ImportUtil.getCellValue(row, 7, pattern);
- String time2 = ImportUtil.getCellValue(row, 8, pattern);
- String time3 = ImportUtil.getCellValue(row, 9, pattern);
- String time4 = ImportUtil.getCellValue(row, 10, pattern);
- String time5 = ImportUtil.getCellValue(row, 11, pattern);
+
+ Cell time1 = row.getCell(7);
+ Cell time2 = row.getCell(8);
+ Cell time3 = row.getCell(9);
+ Cell time4 = row.getCell(10);
+ Cell time5 = row.getCell(11);
+
String repairUnit = ImportUtil.getCellValue(row, 12, pattern);
String repairUnitDirector = ImportUtil.getCellValue(row, 13, pattern);
String repairUnitContact = ImportUtil.getCellValue(row, 14, pattern);
@@ -268,7 +338,7 @@
data.setCabinId(idBuilder.toString());
}
- if (StringUtils.isNotBlank(time1)) {
+ if (time1 != null) {
String Pname = "";
if (typeStr.equals("鏀规崲瑁�")||typeStr.equals("鏀硅繘鎬т慨鐞�")){
Pname = "璁惧鍒板巶";
@@ -277,30 +347,27 @@
}else if(typeStr.equals("涓嶅瑁呰澶�")){
Pname = "鎷嗗嵏鍑鸿埍";
}
- if (time1.matches("[0-9/]+")) {
- time1 = time1.replace("/", "-");
- node.setRequiredCompletionTime(time1);
- node.setProcessName(Pname);
- node.setSort(1);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time1.matches("[0-9.]+")) {
- time1 = time1.replace(".", "-");
- node.setRequiredCompletionTime(time1);
- node.setProcessName(Pname);
- node.setSort(1);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time1.matches("[0-9-]+")) {
- node.setRequiredCompletionTime(time1);
- node.setProcessName(Pname);
- node.setSort(1);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else {
- result = "绗�" + j + "琛�" + "绗�" + 1 + "涓�" + "鑺傜偣鏃堕棿鏃ユ湡鏍煎紡涓嶈鑼�";
+ try {
+ String date = ImportUtil.getDateStrFromCell(time1);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鑺傜偣鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ node.setRequiredCompletionTime(date);
+ node.setProcessName(Pname);
+ node.setSort(1);
+ DjJdgzNetworkLevel3ListService.insert(node);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "绗�" + 1 + "涓�" + "鑺傜偣鏃堕棿鏃ユ湡鏍煎紡涓嶆纭�";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿鏃ユ湡鏍煎紡涓嶆纭�", sheetName, row1);
isErr = true;
}
}
- if (StringUtils.isNotBlank(time2)) {
+ if (time2 != null) {
if (node.getId() != null) {
node.setId(null);
}
@@ -312,29 +379,27 @@
}else if(typeStr.equals("涓嶅瑁呰澶�")){
Pname = "鍏ュ簱";
}
- if (time2.matches("[0-9/]+")) {
- node.setRequiredCompletionTime(time2);
- node.setProcessName(Pname);
- node.setSort(2);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time2.matches("[0-9.]+")) {
- time2 = time2.replace(".", "-");
- node.setRequiredCompletionTime(time2);
- node.setProcessName(Pname);
- node.setSort(2);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time2.matches("[0-9-]+")) {
- node.setRequiredCompletionTime(time2);
- node.setProcessName(Pname);
- node.setSort(2);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else {
- result = "绗�" + j + "琛�" + "绗�" + 2 + "涓�" + "鑺傜偣鏃堕棿鐨勬棩鏈熸牸寮忎笉瑙勮寖";
+ try {
+ String date = ImportUtil.getDateStrFromCell(time2);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鑺傜偣鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ node.setRequiredCompletionTime(date);
+ node.setProcessName(Pname);
+ node.setSort(2);
+ DjJdgzNetworkLevel3ListService.insert(node);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "绗�" + 2 + "涓�" + "鑺傜偣鏃堕棿鐨勬棩鏈熸牸寮忎笉姝ｇ‘";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿鏃ユ湡鏍煎紡涓嶆纭�", sheetName, row1);
isErr = true;
}
}
- if (StringUtils.isNotBlank(time3)) {
+ if (time3 != null) {
if (node.getId() != null) {
node.setId(null);
}
@@ -344,30 +409,28 @@
}else if(typeStr.equals("涓嶅瑁呰澶�")){
Pname = "瀛樻斁浣嶇疆";
}
- if (time3.matches("[0-9/]+")) {
- time3 = time3.replace("/", "-");
- node.setRequiredCompletionTime(time3);
- node.setProcessName(Pname);
- node.setSort(3);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time3.matches("[0-9.]+")) {
- time3 = time3.replace(".", "-");
- node.setRequiredCompletionTime(time3);
- node.setProcessName(Pname);
- node.setSort(3);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time3.matches("[0-9-]+")) {
- node.setRequiredCompletionTime(time3);
- node.setProcessName(Pname);
- node.setSort(3);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else {
- result = "绗�" + j + "琛�" + "绗�" + 3 + "涓�" + "鑺傜偣鏃堕棿鐨勬棩鏈熸牸寮忎笉瑙勮寖";
+ try {
+ String date = ImportUtil.getDateStrFromCell(time3);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鑺傜偣鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ node.setRequiredCompletionTime(date);
+ node.setProcessName(Pname);
+ node.setSort(3);
+ DjJdgzNetworkLevel3ListService.insert(node);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "绗�" + 3 + "涓�" + "鑺傜偣鏃堕棿鐨勬棩鏈熸牸寮忎笉姝ｇ‘";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿鏃ユ湡鏍煎紡涓嶆纭�", sheetName, row1);
isErr = true;
}
}
- if (StringUtils.isNotBlank(time4)) {
+
+ if (time4 != null) {
if (node.getId() != null) {
node.setId(null);
}
@@ -377,52 +440,52 @@
}else if(typeStr.equals("涓嶅瑁呰澶�")){
Pname = "绉讳氦T闃�";
}
- if (time4.matches("[0-9/]+")) {
- time4 = time4.replace("/", "-");
- node.setRequiredCompletionTime(time4);
- node.setProcessName(Pname);
- node.setSort(4);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time4.matches("[0-9.]+")) {
- time4 = time4.replace(".", "-");
- node.setRequiredCompletionTime(time4);
- node.setProcessName(Pname);
- node.setSort(4);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time4.matches("[0-9-]+")) {
- node.setRequiredCompletionTime(time4);
- node.setProcessName(Pname);
- node.setSort(4);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else {
- result = "绗�" + j + "琛�" + "绗�" + 4 + "涓�" + "鑺傜偣鏃堕棿鐨勬棩鏈熸牸寮忎笉瑙勮寖";
+ try {
+ String date = ImportUtil.getDateStrFromCell(time4);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鑺傜偣鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ node.setRequiredCompletionTime(date);
+ node.setProcessName(Pname);
+ node.setSort(4);
+ DjJdgzNetworkLevel3ListService.insert(node);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "绗�" + 4 + "涓�" + "鑺傜偣鏃堕棿鐨勬棩鏈熸牸寮忎笉姝ｇ‘";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿鏃ユ湡鏍煎紡涓嶆纭�", sheetName, row1);
isErr = true;
}
}
- if (StringUtils.isNotBlank(time5)) {
+ if (time5 != null) {
if (node.getId() != null) {
node.setId(null);
}
- if (time5.matches("[0-9/]+")) {
- time5 = time5.replace("/", "-");
- node.setRequiredCompletionTime(time5);
- node.setProcessName("鍥炶");
- node.setSort(5);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time5.matches("[0-9.]+")) {
- time5 = time5.replace(".", "-");
- node.setRequiredCompletionTime(time5);
- node.setProcessName("鍥炶");
- node.setSort(5);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else if (time5.matches("[0-9-]+")) {
- node.setRequiredCompletionTime(time5);
- node.setProcessName("鍥炶");
- node.setSort(5);
- DjJdgzNetworkLevel3ListService.insert(node);
- } else {
- result = "绗�" + j + "琛�" + "绗�" + 5 + "涓�" + "瑕佹眰瀹屾垚鏃堕棿鐨勬棩鏈熸牸寮忎笉瑙勮寖";
+ String Pname = "";
+ if(typeStr.equals("淇悊")){
+ Pname = "璁惧杩斿巶";
+ }else if(typeStr.equals("涓嶅瑁呰澶�")){
+ Pname = "绉讳氦T闃�";
+ }
+ try {
+ String date = ImportUtil.getDateStrFromCell(time5);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鑺傜偣鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ node.setRequiredCompletionTime(date);
+ node.setProcessName(Pname);
+ node.setSort(5);
+ DjJdgzNetworkLevel3ListService.insert(node);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "绗�" + 5 + "涓�" + "鑺傜偣鏃堕棿鐨勬棩鏈熸牸寮忎笉姝ｇ‘";
+ ImportUtil.updateErrMap(errMap, "鑺傜偣鏃堕棿鏃ユ湡鏍煎紡涓嶆纭�", sheetName, row1);
isErr = true;
}
}
@@ -435,6 +498,7 @@
data.setGeneralRepairUnit(generalRepairUnit);
}
if (StringUtils.isNotBlank(generalRepairUnitContact)) {
+ generalRepairUnitContact = TranslateToPlainStrUtils.translateToPlainStr(generalRepairUnitContact);
data.setGeneralRepairUnitContact(generalRepairUnitContact);
}
if (StringUtils.isNotBlank(generalRepairUnitDirector)) {
@@ -444,6 +508,7 @@
data.setRepairUnit(repairUnit);
}
if (StringUtils.isNotBlank(repairUnitContact)) {
+ repairUnitContact = TranslateToPlainStrUtils.translateToPlainStr(repairUnitContact);
data.setRepairUnitContact(repairUnitContact);
}
if (StringUtils.isNotBlank(repairUnitDirector)) {
@@ -512,7 +577,7 @@
// 宸插畬鎴�
if (node.getActualCompletion() != null && actualCompletionDate.after(requiredCompletionDate)) {
statusMap.put(node.getId(), StatusEnum.cqwc);
- int days = (int) ((today.getTime() - requiredCompletionDate.getTime()) / (1000 * 60 * 60 * 24));
+ int days = (int) ((actualCompletionDate.getTime() - requiredCompletionDate.getTime()) / (1000 * 60 * 60 * 24));
node.setCqText("瑕佹眰鏃堕棿涓�" + sdf.format(requiredCompletionDate) + "瓒呮湡" + days + "澶╁畬鎴�");
} else {
statusMap.put(node.getId(), StatusEnum.zcwc);
@@ -568,11 +633,4 @@
return dataList;
}
- /* public void exportExcelTemplate(HttpServletResponse response, HttpServletRequest request) throws IOException {
- String excelName="涓夌骇缃戠粶鍥炬壒閲忔ā鐗�";
- String excelPath = path + "template/jx-model/" + excelName + ".xlsx"; //妯℃澘璺緞
- File file = new File(excelPath);
- excelName = excelName + "_" + DateUtil.now() + ".xlsx";
- DownloadService.exportModelPath(excelPath, excelName, file, response, request);
- }*/
}
--
Gitblit v1.9.1