From e301d5c4e6de0aca423c54481d100e6ca2886b01 Mon Sep 17 00:00:00 2001
From: jinlin <jinlin>
Date: 星期六, 08 十一月 2025 22:57:12 +0800
Subject: [PATCH] 清洗数据库日期错误，并且导入日期通用化处理
---
src/main/java/com/example/server/progressTrack/service/DjJdgzDismantTrackService.java | 408 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 402 insertions(+), 6 deletions(-)
diff --git a/src/main/java/com/example/server/progressTrack/service/DjJdgzDismantTrackService.java b/src/main/java/com/example/server/progressTrack/service/DjJdgzDismantTrackService.java
index 7b8a17b..b72e13c 100644
--- a/src/main/java/com/example/server/progressTrack/service/DjJdgzDismantTrackService.java
+++ b/src/main/java/com/example/server/progressTrack/service/DjJdgzDismantTrackService.java
@@ -1,26 +1,422 @@
package com.example.server.progressTrack.service;
+import com.example.client.dto.ColumnDto;
+import com.example.client.dto.JComboBoxItem;
import com.example.client.service.BaseService;
-import com.example.client.utils.UUIDUtil;
+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.StatistDismantDto;
import com.example.server.progressTrack.dao.DjJdgzDismantTrackDao;
-import com.example.server.progressTrack.dao.DjJdgzHandoverDao;
import com.example.server.progressTrack.model.DjJdgzDismantTrack;
-import com.example.server.progressTrack.model.DjJdgzHandover;
+import com.example.server.progressTrack.model.DjJdgzNetworkLevel3;
+import com.example.server.progressTrack.model.DjJdgzTrackRecord;
+import com.example.server.utils.*;
+import org.apache.commons.lang3.StringUtils;
+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.Autowired;
import org.springframework.stereotype.Service;
+import org.springframework.web.multipart.MultipartFile;
-import java.util.List;
+import java.io.IOException;
+import java.util.*;
/**
* dj_jdgz_track_record
*
- * @author zt generator
+ * @author zt generator
* @since 1.0.0 2024-12-17
*/
@Service
public class DjJdgzDismantTrackService extends BaseService<DjJdgzDismantTrackDao, DjJdgzDismantTrack> {
+ @Autowired
+ DjJdgzNetworkLevel3Service djJdgzNetworkLevel3Service;
+ @Autowired
+ CabinService cabinService;
public List<DjJdgzDismantTrack> getList(Long level3Id, Long cabinId) {
- return baseDao.getList(level3Id,cabinId);
+ return baseDao.getList(level3Id, cabinId);
+ }
+ public List<DjJdgzDismantTrack> getAll() {
+ return baseDao.getAll();
+ }
+
+ public void save(List<DjJdgzDismantTrack> list) {
+ for (DjJdgzDismantTrack data : list) {
+ if (data.getId() != null) {
+ this.update(data);
+ } else {
+ this.insert(data);
+ }
+ }
+
+ }
+
+ public String importExcel(String path) {
+ String result = "true";
+ Integer sum = 0; // 鎬诲叡鐨勬潯鏁�
+ Integer suc = 0; // 鎴愬姛鐨勬潯鏁�
+ Integer err = 0; // 澶辫触鐨勬潯鏁�
+ Map<String, Object> errMap = new HashMap<>();
+ MultipartFile mutFile = null;
+ try {
+ mutFile = FileUtils.convertFileToMultipartFile(path);
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ String sheetName = null;
+ int row1 = 0;
+ try {
+ // 鑾峰彇瀵煎叆鏂囦欢鐨勫悗缂�鍚�
+ String fileName = mutFile.getOriginalFilename();
+ Workbook workbook = null;
+ //瑙ｅ喅excel鐗堟湰闂
+ if (fileName != null && fileName.endsWith(".xls")) {
+ workbook = new HSSFWorkbook(mutFile.getInputStream());
+ } else if (fileName != null && fileName.endsWith(".xlsx")) {
+ workbook = new XSSFWorkbook(mutFile.getInputStream());
+ }
+ assert workbook != null; //鏂█濡傛灉[boolean琛ㄨ揪寮廬涓簍rue锛屽垯绋嬪簭缁х画鎵ц銆� 濡傛灉涓篺alse锛屽垯绋嬪簭鎶涘嚭AssertionError锛屽苟缁堟鎵ц銆�
+
+ Sheet sheet = workbook.getSheetAt(0);
+ int num = sheet.getLastRowNum(); // 涓�鍏辨湁澶氬皯琛�
+ sheetName = sheet.getSheetName(); //鑾峰彇褰撳墠sheet鍚嶇О
+ sum = sum + num;
+
+ List<DjJdgzNetworkLevel3> list = djJdgzNetworkLevel3Service.getList(null, null, null, null, null, null, null, null);
+ JComboBoxItem[] list1 = cabinService.getList();
+
+
+ Map<String, Long> level3Map = new HashMap<>();
+ Map<String, Long> cabinMap = new HashMap<>();
+ Map<String, Integer> statusMap = new HashMap<>();
+ statusMap.put("杩涜涓�", 0);
+ statusMap.put("宸插畬鎴�", 1);
+
+ for (DjJdgzNetworkLevel3 level3 : list) {
+ level3Map.put(level3.getName(), level3.getId());
+ }
+ for (JComboBoxItem item : list1) {
+ cabinMap.put(item.getName(), item.getId());
+ }
+
+ String curSbName = "";
+ Long curSbId = null;
+
+ for (int j = 2; j <= num; j++) {
+ DjJdgzDismantTrack data = new DjJdgzDismantTrack();
+
+ boolean isErr = false;
+ row1 = j + 1;
+ String pattern = "yyyy-MM-dd";
+ Row row = sheet.getRow(j);
+
+ String sbName = ImportUtil.getCellValue(row, 0, pattern);
+ String name = ImportUtil.getCellValue(row, 1, pattern);
+ String cabin = ImportUtil.getCellValue(row, 2, pattern);
+
+ Cell cxTime = row.getCell(3);
+
+ String cxUnit = ImportUtil.getCellValue(row, 4, pattern);
+ String cxStaff = ImportUtil.getCellValue(row, 5, pattern);
+ String cxAssis = ImportUtil.getCellValue(row, 6, pattern);
+
+ Cell ccTime = row.getCell(7);
+
+ String ccUnit = ImportUtil.getCellValue(row, 8, pattern);
+ String ccStaff = ImportUtil.getCellValue(row, 9, pattern);
+ String ccAssis = ImportUtil.getCellValue(row, 10, pattern);
+
+ Cell tzTime = row.getCell(11);
+
+ String czr = ImportUtil.getCellValue(row, 12, pattern);
+ String tzWeight = ImportUtil.getCellValue(row, 13, pattern);
+ String tzAssis = ImportUtil.getCellValue(row, 14, pattern);
+
+ Cell rkTime = row.getCell(15);
+
+ String rkStaff = ImportUtil.getCellValue(row, 16, pattern);
+ String local = ImportUtil.getCellValue(row, 17, pattern);
+ String statusStr = ImportUtil.getCellValue(row, 18, pattern);
+
+ if (StringUtils.isBlank(sbName)) {
+ if (StringUtils.isBlank(curSbName)) {
+ result = "绗�" + j + "琛�" + "璁惧涓嶈兘涓虹┖";
+ isErr = true;
+ } else {
+ sbName = curSbName;
+ curSbId = level3Map.get(sbName);
+ }
+ } else {
+ if (StringUtils.isBlank(curSbName) || !sbName.equals(curSbName)) {
+ curSbName = sbName;
+ if (level3Map.get(curSbName) == null) {
+ result = "绗�" + j + "琛�" + "璁惧涓嶅瓨鍦�";
+ isErr = true;
+ } else {
+ curSbId = level3Map.get(curSbName);
+ }
+ }
+ }
+
+ Long id = UUIDUtil.generateId();
+ if (StringUtils.isEmpty(name)) {
+ result = "绗�" + j + "琛�" + "瀛愰儴浠跺悕绉颁笉鑳戒负绌�";
+ isErr = true;
+ } else {
+ data.setId(id);
+ data.setName(name);
+ data.setLevel3NetworkId(curSbId);
+ baseDao.deleteByName(name);
+ }
+
+ if (StringUtils.isEmpty(cabin)) {
+ result = "绗�" + j + "琛�" + "鑸卞涓嶈兘涓虹┖";
+ isErr = true;
+ } else {
+ if (cabinMap.get(cabin) == null) {
+ result = "绗�" + j + "琛�" + "鑸卞涓嶅瓨鍦�";
+ isErr = true;
+ } else {
+ Long cabinId = cabinMap.get(cabin);
+ data.setCabinId(cabinId);
+ }
+ }
+
+ if (cxTime != null) {
+ try {
+ String date = ImportUtil.getDateStrFromCell(cxTime);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鎷嗗嵏鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鎷嗗嵏鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ data.setDismantTime(date);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "鎷嗗嵏鏃堕棿鏍煎紡涓嶆纭�";
+ ImportUtil.updateErrMap(errMap, "鎷嗗嵏鏃堕棿鏍煎紡涓嶆纭�", sheetName, row1);
+ isErr = true;
+ }
+ }
+
+
+ if (StringUtils.isNotBlank(cxUnit)) {
+ data.setDismantUnit(cxUnit);
+ }
+ if (StringUtils.isNotBlank(cxStaff)) {
+ data.setDismantStaff(cxStaff);
+ }
+ if (StringUtils.isNotBlank(cxAssis)) {
+ data.setDismantAssistant(cxAssis);
+ }
+
+ if (ccTime != null) {
+ try {
+ String date = ImportUtil.getDateStrFromCell(ccTime);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鍑鸿埍鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鍑鸿埍鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ data.setExitTime(date);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "鍑鸿埍鏃堕棿鏍煎紡涓嶆纭�";
+ ImportUtil.updateErrMap(errMap, "鍑鸿埍鏃堕棿鏍煎紡涓嶆纭�", sheetName, row1);
+ isErr = true;
+ }
+ }
+
+
+ if (StringUtils.isNotBlank(ccUnit)) {
+ data.setExitUnit(ccUnit);
+ }
+ if (StringUtils.isNotBlank(ccStaff)) {
+ data.setExitStaff(ccStaff);
+ }
+ if (StringUtils.isNotBlank(ccAssis)) {
+ data.setExitAssistant(ccAssis);
+ }
+
+ if (tzTime != null) {
+ try {
+ String date = ImportUtil.getDateStrFromCell(tzTime);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "閫�閲嶆椂闂翠笉鑳戒负绌�";
+ ImportUtil.updateErrMap(errMap, "閫�閲嶆椂闂翠笉鑳戒负绌�", sheetName, row1);
+ isErr = true;
+ } else {
+ data.setReturnWeightTime(date);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "閫�閲嶆椂闂存牸寮忎笉姝ｇ‘";
+ ImportUtil.updateErrMap(errMap, "閫�閲嶆椂闂存牸寮忎笉姝ｇ‘", sheetName, row1);
+ isErr = true;
+ }
+ }
+
+
+ if (StringUtils.isNotBlank(czr)) {
+ data.setWeigher(czr);
+ }
+ if (StringUtils.isNotBlank(tzWeight)) {
+ data.setReturnWeight(tzWeight);
+ }
+ if (StringUtils.isNotBlank(tzAssis)) {
+ data.setReturnWeightAssistant(tzAssis);
+ }
+
+ if (rkTime != null) {
+ try {
+ String date = ImportUtil.getDateStrFromCell(rkTime);
+
+ if (date == null) {
+ result = "绗�" + j + "琛�" + "鍏ュ簱鏃堕棿涓嶈兘涓虹┖";
+ ImportUtil.updateErrMap(errMap, "鍏ュ簱鏃堕棿涓嶈兘涓虹┖", sheetName, row1);
+ isErr = true;
+ } else {
+ data.setWarehouseTime(date);
+ }
+ } catch (ExcelImportException e) {
+ result = "绗�" + j + "琛�" + "鍏ュ簱鏃堕棿鏍煎紡涓嶆纭�";
+ ImportUtil.updateErrMap(errMap, "鍏ュ簱鏃堕棿鏍煎紡涓嶆纭�", sheetName, row1);
+ isErr = true;
+ }
+ }
+
+ if (StringUtils.isNotBlank(rkStaff)) {
+ data.setWarehouseStaff(rkStaff);
+ }
+
+ if (StringUtils.isNotBlank(local)) {
+ data.setCurrentLocation(local);
+ }
+ if (StringUtils.isNotBlank(statusStr)) {
+ data.setStatus(statusMap.get(statusStr));
+ }
+
+ if (!isErr) {
+ this.insert(data);
+ }
+ if (isErr) {
+ err++;
+ continue;
+ }
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ result = "瀵煎叆" + row1 + "琛屾暟鎹椂妫�娴嬪埌寮傚父锛屼腑鏂鍏ユ搷浣溿��";
+ }
+
+ suc = sum - err;
+
+ return result;
+ }
+
+ public void exportExcel(String filePath, Long deptId, Long teamGroupId, Long level1NetworkId, Long level3NetworkId, Integer status) {
+ List<ExprotExcelDto> dtos = new ArrayList<>();
+
+ Map<Integer, String> statusMap = new HashMap<>();
+ statusMap.put(0, "杩涜涓�");
+ statusMap.put(1, "宸插畬鎴�");
+
+ ExprotExcelDto dto = new ExprotExcelDto();
+ dto.setSheetName("鎷嗗嵏杩涘害鏄庣粏");
+ List<ExcelColumnDto> columnDto = new ArrayList<>();
+
+ columnDto.add(new ExcelColumnDto("涓�绾ц妭鐐�", "Level1NodeName", 20, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("浜岀骇鑺傜偣", "Level2NodeName", 20, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("璁惧鍚嶇О", "Level3NetworkName", 20, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("閮ㄩ棬", "deptName", 20, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("涓撲笟", "teamGroupName", 20, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鑸卞", "cabinName", 10, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("瀛愰儴浠跺悕绉�", "name", 25, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鎷嗗嵏鏃堕棿", "dismantTime", 15, HorizontalAlignment.CENTER));
+ columnDto.add(new ExcelColumnDto("鎷嗗嵏鍗曚綅", "dismantUnit", 25, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鎷嗗嵏浜哄憳", "dismantStaff", 15, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鎷嗗嵏鍔╀慨T鍛�", "dismantAssistant", 10, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鍑鸿埍鏃堕棿", "exitTime", 10, HorizontalAlignment.CENTER));
+ columnDto.add(new ExcelColumnDto("鍑鸿埍鍗曚綅", "exitUnit", 10, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鍑鸿埍浜哄憳", "exitStaff", 15, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鍑鸿埍鍔╀慨T鍛�", "exitAssistant", 20, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("閫�閲嶆椂闂�", "returnWeightTime", 20, HorizontalAlignment.CENTER));
+ columnDto.add(new ExcelColumnDto("绉伴噸鍛�", "weigher", 15, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("閫�閲嶉噸閲�", "returnWeight", 15, HorizontalAlignment.RIGHT));
+ columnDto.add(new ExcelColumnDto("閫�閲嶅姪淇甌鍛�", "returnWeightAssistant", 20, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鍏ュ簱鏃堕棿", "warehouseTime", 15, HorizontalAlignment.CENTER));
+ columnDto.add(new ExcelColumnDto("鍏ュ簱浜哄憳", "warehouseStaff", 15, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("褰撳墠瀛樻斁浣嶇疆", "currentLocation", 15, HorizontalAlignment.LEFT));
+ columnDto.add(new ExcelColumnDto("鐘舵��", "currStatus", 10, HorizontalAlignment.CENTER));
+
+ List<DjJdgzDismantTrack> list = baseDao.getdata(deptId, teamGroupId, level1NetworkId, level3NetworkId, status);
+
+ for (int i = 0; i < list.size(); i++) {
+ DjJdgzDismantTrack data = list.get(i);
+
+ if (data.getStatus() != null) {
+ data.setCurrStatus(statusMap.get(data.getStatus()));
+ }
+ }
+ String[] filed = {"Level1NodeName", "Level2NodeName", "Level3NetworkName"};
+ TreeFieldUtils.build(list, filed);
+ dto.setColumnDto(columnDto);
+ //dto.setValues(content);
+ dto.setDataList(list);
+
+
+ /* ExprotExcelDto dto2 = new ExprotExcelDto();
+ dto2.setSheetName("鑺傜偣姹囨��");
+ List<ExcelColumnDto> columnDto2 = new ArrayList<>();
+ List<DjJdgzTrackRecord> list2 = baseDao.getSummaryData(teamgroupId, level1NetworkId, level3NetworkId, level3NodeId, status, beginDate, endDate, istq);
+
+
+ columnDto2.add(new ExcelColumnDto("涓�绾ц妭鐐�", "Level1NodeName", 25, HorizontalAlignment.LEFT));
+ columnDto2.add(new ExcelColumnDto("浜岀骇鑺傜偣", "Level2NodeName", 25, HorizontalAlignment.LEFT));
+ columnDto2.add(new ExcelColumnDto("涓夌骇缃戠粶鍚嶇О\n" + "锛堣澶囧悕绉帮級", "Level3NetworkName", 25, HorizontalAlignment.LEFT));
+ columnDto2.add(new ExcelColumnDto("涓夌骇鑺傜偣", "Level3NodeName", 15, HorizontalAlignment.LEFT));
+ columnDto2.add(new ExcelColumnDto("璁″垝\n" + "瀹屾垚鏃堕棿", "RequiredCompletionTime", 12, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("鏄惁鑴辨湡", "Istq", 5, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("鑴辨湡娆℃暟", "Tqcs", 5, HorizontalAlignment.RIGHT));
+ columnDto2.add(new ExcelColumnDto("鏈�澶ч璁n" + "瀹屾垚鏃堕棿", "Maxtime", 12, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("鏈�澶ц劚鏈熷ぉ鏁�", "MaxDay", 10, HorizontalAlignment.RIGHT));
+ columnDto2.add(new ExcelColumnDto("鏈�鏂伴璁n" + "瀹屾垚鏃堕棿", "Zxtime", 12, HorizontalAlignment.CENTER));
+ columnDto2.add(new ExcelColumnDto("鏈�鏂拌劚鏈熷ぉ鏁�", "ZxDay", 10, HorizontalAlignment.RIGHT));
+
+ TreeFieldUtils.build(list2, filed);
+ dto2.setColumnDto(columnDto2);
+ //dto2.setValues(content2);
+ dto2.setDataList(list2);*/
+
+ dtos.add(dto);
+ //dtos.add(dto2);
+ try {
+ ExcelExport.getHSSFWorkbook(filePath, dtos);
+ } catch (IOException e) {
+ e.printStackTrace();
+ }
+ }
+
+ public List<StatistDismantDto> getStatist(Long level1Id) {
+ return baseDao.getStatist(level1Id);
+ }
+
+ public Boolean getStatus(Long level3NetworkId) {
+ return baseDao.getStatus(level3NetworkId);
+ }
+
+ public void delete(Long id) {
+ baseDao.deleteById(id);
+ }
+
+ public List<StatistDismantDto> getStatist2(Long level1Id, Long id) {
+ return baseDao.getStatist2(level1Id, id);
}
}
--
Gitblit v1.9.1