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

jinlin

2 天以前 e301d5c4e6de0aca423c54481d100e6ca2886b01

 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.*;


/**
@@ -49,6 +40,9 @@

    public List<DjJdgzDismantTrack> getList(Long level3Id, Long cabinId) {
        return baseDao.getList(level3Id, cabinId);
    }
    public List<DjJdgzDismantTrack> getAll() {
        return baseDao.getAll();
    }

    public void save(List<DjJdgzDismantTrack> list) {
@@ -93,15 +87,15 @@
            sheetName = sheet.getSheetName(); //获取当前sheet名称
            sum = sum + num;

            List<DjJdgzNetworkLevel3> list = djJdgzNetworkLevel3Service.getList(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());
@@ -113,7 +107,7 @@
            String curSbName = "";
            Long curSbId = null;

            for (int j = 1; j <= num; j++) {
            for (int j = 2; j <= num; j++) {
                DjJdgzDismantTrack data = new DjJdgzDismantTrack();

                boolean isErr = false;
@@ -124,19 +118,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 +187,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 +216,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 +245,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 +274,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;
                    }
                }
@@ -325,21 +342,21 @@
        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("拆卸助修艇员", "dismantAssistant", 10, 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("出舱助修艇员", "exitAssistant", 20, 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("退重助修T员", "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);
        List<DjJdgzDismantTrack> list = baseDao.getdata(deptId, teamGroupId, level1NetworkId, level3NetworkId, status);

        for (int i = 0; i < list.size(); i++) {
            DjJdgzDismantTrack data = list.get(i);
@@ -390,4 +407,16 @@
    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);
    }
}