清洗数据库日期错误，并且导入日期通用化处理

jinlin

1 天以前 bf5b01b14dc7bfc214e646425a62f5593890d7e3

 src/main/java/com/example/server/progressTrack/service/DjJdgzDismantTrackService.java

@@ -12,26 +12,17 @@
import com.example.server.progressTrack.model.DjJdgzDismantTrack;
import com.example.server.progressTrack.model.DjJdgzNetworkLevel3;
import com.example.server.progressTrack.model.DjJdgzTrackRecord;
import com.example.server.utils.FileUtils;
import com.example.server.utils.ImportUtil;
import com.example.server.utils.TreeFieldUtils;
import com.example.server.utils.UUIDUtil;
import com.example.server.utils.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.*;


/**
@@ -93,15 +84,15 @@
            sheetName = sheet.getSheetName(); //获取当前sheet名称
            sum = sum + num;

            List<DjJdgzNetworkLevel3> list = djJdgzNetworkLevel3Service.getList(null, null, null, null, null,null,null,null);
            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);
            statusMap.put("进行中", 0);
            statusMap.put("已完成", 1);

            for (DjJdgzNetworkLevel3 level3 : list) {
                level3Map.put(level3.getName(), level3.getId());
@@ -124,19 +115,27 @@
                String sbName = ImportUtil.getCellValue(row, 0, pattern);
                String name = ImportUtil.getCellValue(row, 1, pattern);
                String cabin = ImportUtil.getCellValue(row, 2, pattern);
                String cxTime = ImportUtil.getCellValue(row, 3, 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);
                String ccTime = ImportUtil.getCellValue(row, 7, 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);
                String tzTime = ImportUtil.getCellValue(row, 11, 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);
                String rkTime = ImportUtil.getCellValue(row, 15, 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);
@@ -185,20 +184,24 @@
                    }
                }

                if (StringUtils.isNotBlank(cxTime)) {
                    if (cxTime.matches("[0-9/]+")) {
                        cxTime = cxTime.replace("/", "-");
                        data.setDismantTime(cxTime);
                    } else if (cxTime.matches("[0-9.]+")) {
                        cxTime = cxTime.replace(".", "-");
                        data.setDismantTime(cxTime);
                    } else if (cxTime.matches("[0-9-]+")) {
                        data.setDismantTime(cxTime);
                    } else {
                        result = "第" + j + "行" + "拆卸时间日期格式不规范";
                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);
@@ -210,20 +213,24 @@
                    data.setDismantAssistant(cxAssis);
                }

                if (StringUtils.isNotBlank(ccTime)) {
                    if (ccTime.matches("[0-9/]+")) {
                        ccTime = ccTime.replace("/", "-");
                        data.setExitTime(ccTime);
                    } else if (ccTime.matches("[0-9.]+")) {
                        ccTime = ccTime.replace(".", "-");
                        data.setExitTime(ccTime);
                    } else if (ccTime.matches("[0-9-]+")) {
                        data.setExitTime(ccTime);
                    } else {
                        result = "第" + j + "行" + "出舱时间日期格式不规范";
                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);
@@ -235,20 +242,24 @@
                    data.setExitAssistant(ccAssis);
                }

                if (StringUtils.isNotBlank(tzTime)) {
                    if (tzTime.matches("[0-9/]+")) {
                        tzTime = tzTime.replace("/", "-");
                        data.setReturnWeightTime(tzTime);
                    } else if (tzTime.matches("[0-9.]+")) {
                        tzTime = tzTime.replace(".", "-");
                        data.setReturnWeightTime(tzTime);
                    } else if (tzTime.matches("[0-9-]+")) {
                        data.setReturnWeightTime(tzTime);
                    } else {
                        result = "第" + j + "行" + "退重时间日期格式不规范";
                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);
@@ -260,17 +271,20 @@
                    data.setReturnWeightAssistant(tzAssis);
                }

                if (StringUtils.isNotBlank(rkTime)) {
                    if (rkTime.matches("[0-9/]+")) {
                        rkTime = rkTime.replace("/", "-");
                        data.setWarehouseTime(rkTime);
                    } else if (rkTime.matches("[0-9.]+")) {
                        rkTime = rkTime.replace(".", "-");
                        data.setWarehouseTime(rkTime);
                    } else if (rkTime.matches("[0-9-]+")) {
                        data.setWarehouseTime(rkTime);
                    } else {
                        result = "第" + j + "行" + "入库时间日期格式不规范";
                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;
                    }
                }
@@ -339,7 +353,7 @@
        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);
        List<DjJdgzDismantTrack> list = baseDao.getdata(deptId, teamGroupId, level1NetworkId, level3NetworkId, status);

        for (int i = 0; i < list.size(); i++) {
            DjJdgzDismantTrack data = list.get(i);
@@ -400,6 +414,6 @@
    }

    public List<StatistDismantDto> getStatist2(Long level1Id, Long id) {
        return baseDao.getStatist2(level1Id,id);
        return baseDao.getStatist2(level1Id, id);
    }
}