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

jinlin

2 天以前 bf5b01b14dc7bfc214e646425a62f5593890d7e3

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

@@ -1,5 +1,6 @@
package com.example.server.progressTrack.service;

import com.example.client.utils.TranslateToPlainStrUtils;
import com.example.server.ExportExcel.dto.ExcelColumnDto;
import com.example.client.service.BaseService;
import com.example.server.ExportExcel.dto.ExprotExcelDto;
@@ -13,16 +14,10 @@
import com.example.server.progressTrack.model.DjJdgzTrackRecord;
import com.example.server.teamGroup.service.SysTeamGroupClassService;
import com.example.server.user.service.UserService;
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;
@@ -58,7 +53,7 @@
    private SysTeamGroupClassService sysTeamGroupClassService;


    public List<DjJdgzTrackRecord> getList(Long level1NetworkId, Long deptId, Long teamId, Long cabinId, String type, Integer status,Integer isealCabin, String name) {
    public List<DjJdgzTrackRecord> getList(Long level1NetworkId, Long deptId, Long teamId, Long cabinId, String type, Integer status, Integer isealCabin, String name) {
        Long userId = null;
       /* Map adminRole = sysUserService.adminRole();
        Object isTyRole = adminRole.get("isTyRole");
@@ -66,7 +61,7 @@
        if (isTyRole.equals(true) || isYwzRole.equals(true)) {
            userId =  UserContext.getUser().getId();
        }*/
        List<DjJdgzTrackRecord> list = baseDao.getList(level1NetworkId, deptId, teamId, cabinId, type, status, isealCabin,name);
        List<DjJdgzTrackRecord> list = baseDao.getList(level1NetworkId, deptId, teamId, cabinId, type, status, isealCabin, name);
        /*if (list != null && list.size() > 0) {
            sysOssService.setListOsses(list, "ProjectProgressFileUpload");
        }*/
@@ -76,18 +71,6 @@
                Integer trackNum = baseDao.getTrackNum(data.getLevel3NodeId());
                data.setActualCompletion(data.getEstimatedCompletionTime());
                data.setTrackNum(trackNum);
                int weekNo = this.getWeekNo(data.getLevel1NetworkId());
                if (data.getWeekNo() != null) {
                    if (data.getWeekNo() != weekNo) {
                        data.setWeekNo(weekNo);
                        data.setIsUpdate(false);
                    } else {
                        data.setIsUpdate(true);
                    }
                } else {
                    data.setWeekNo(weekNo);
                    data.setIsUpdate(false);
                }
            }
        }
        return list;
@@ -102,63 +85,75 @@
        super.deleteLogic(ids);
    }

    public Long save(DjJdgzTrackRecord djJdgzTrackRecord,String type) {
    public Long save(DjJdgzTrackRecord djJdgzTrackRecord, String type) {
        Long id = null;
        int weekNo = this.getWeekNo(djJdgzTrackRecord.getLevel1NetworkId());
        LocalDate currentDate = LocalDate.now();
        // 定义日期格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        // 将当前日期格式化为字符串
        String formattedDate = currentDate.format(formatter);
        djJdgzTrackRecord.setUpdateDate(formattedDate);
        if (djJdgzTrackRecord.getCurrentStatus() == 1 && type.equals("save")) {
            //时间比较
            djJdgzTrackRecord.setEstimatedCompletionTime(formattedDate);
        }
        if (djJdgzTrackRecord.getIsUpdate()) {
            this.update(djJdgzTrackRecord);
            id = djJdgzTrackRecord.getId();
        } else {
            id = UUIDUtil.generateId();
            djJdgzTrackRecord.setWeekNo(weekNo);
            djJdgzTrackRecord.setId(id);
            djJdgzTrackRecord.setCreateDate(formattedDate);
            this.insert(djJdgzTrackRecord);
            id = djJdgzTrackRecord.getId();
        }
        int weekNo = this.getWeekNo(djJdgzTrackRecord.getLevel1NetworkId(),new Date());

        if (djJdgzTrackRecord.getCurrentStatus() == 1) {
            DjJdgzNetworkLevel3List nextNode = djJdgzNetworkLevel3ListService.getNextNode(djJdgzTrackRecord.getLevel3NodeId(), djJdgzTrackRecord.getLevel3NetworkId());
            if (nextNode != null) {
                DjJdgzTrackRecord data = new DjJdgzTrackRecord();
                data.setLevel3NodeId(nextNode.getId());
                data.setProcessName(nextNode.getProcessName());
                data.setLevel3NetworkId(djJdgzTrackRecord.getLevel3NetworkId());
                data.setLevel3NetworkName(djJdgzTrackRecord.getLevel3NetworkName());
                data.setEstimatedCompletionTime(nextNode.getRequiredCompletionTime());
                data.setGeneralRepairUnit(djJdgzTrackRecord.getGeneralRepairUnit());
                data.setGeneralRepairUnitContact(djJdgzTrackRecord.getGeneralRepairUnitContact());
                data.setGeneralRepairUnitDirector(djJdgzTrackRecord.getGeneralRepairUnitDirector());
                data.setRepairUnit(djJdgzTrackRecord.getRepairUnit());
                data.setRepairUnitContact(djJdgzTrackRecord.getRepairUnitContact());
                data.setRepairUnitDirector(djJdgzTrackRecord.getRepairUnitDirector());
                data.setTrackMethod(1);
                data.setWeekNo(weekNo);
                data.setUpdateDate(formattedDate);
                data.setCreateDate(formattedDate);
                this.insert(data);
        if (type.equals("import")) {
            Long oldId = baseDao.getIsExit(djJdgzTrackRecord.getLevel3NetworkId(), djJdgzTrackRecord.getProcessName(), djJdgzTrackRecord.getWeekNo());
            if (oldId != null) {
                djJdgzTrackRecord.setId(oldId);
                djJdgzTrackRecord.setIsUpdate(true);
            } else {
                djJdgzTrackRecord.setIsUpdate(false);
            }
        }else{
            if (djJdgzTrackRecord.getWeekNo() != null && djJdgzTrackRecord.getWeekNo() == weekNo) {
                djJdgzTrackRecord.setIsUpdate(true);
            } else {
                djJdgzTrackRecord.setWeekNo(weekNo);
                djJdgzTrackRecord.setIsUpdate(false);
            }
            LocalDate currentDate = LocalDate.now();
            // 定义日期格式
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            // 将当前日期格式化为字符串
            String formattedDate = currentDate.format(formatter);

            if (djJdgzTrackRecord.getCurrentStatus() == 1) {
                djJdgzTrackRecord.setEstimatedCompletionTime(formattedDate);
            }
        }
        //改变三级网络图状态

        if (djJdgzTrackRecord.getIsUpdate()) {
            this.update(djJdgzTrackRecord);
        } else {
            djJdgzTrackRecord.setId(null);
            if(type.equals("save")){
                djJdgzTrackRecord.setWeekNo(weekNo);
            }
            this.insert(djJdgzTrackRecord);
        }
        id = djJdgzTrackRecord.getId();

        this.setLevel3Status(djJdgzTrackRecord.getLevel3NodeId(), djJdgzTrackRecord.getCurrentStatus(), djJdgzTrackRecord.getEstimatedCompletionTime());

        if (djJdgzTrackRecord.getCurrentStatus() == 1) {
            DjJdgzNetworkLevel3List nextNode = baseDao.isExistTrackRecord(djJdgzNetworkLevel3ListService.get(djJdgzTrackRecord.getLevel3NodeId()).getSort(), djJdgzTrackRecord.getLevel3NetworkId());
            if (nextNode != null && nextNode.getTrackRecordId() == null) {
                djJdgzTrackRecord.setId(null);
                djJdgzTrackRecord.setLevel3NodeId(nextNode.getId());
                djJdgzTrackRecord.setProcessName(nextNode.getProcessName());
                djJdgzTrackRecord.setEstimatedCompletionTime(nextNode.getRequiredCompletionTime());
                djJdgzTrackRecord.setWeekNo(weekNo);
                djJdgzTrackRecord.setCurrentStatus(0);

                djJdgzTrackRecord.setUpdateDate(null);
                djJdgzTrackRecord.setCreateDate(null);
                djJdgzTrackRecord.setCreator(null);
                djJdgzTrackRecord.setUpdater(null);
                this.insert(djJdgzTrackRecord);
                this.setLevel3Status(djJdgzTrackRecord.getLevel3NodeId(), djJdgzTrackRecord.getCurrentStatus(), djJdgzTrackRecord.getEstimatedCompletionTime());
            }
        }
        return id;

    }

    public int getWeekNo(Long level1NetworkId) {
    public int getWeekNo(Long level1NetworkId,Date nowDate) {
        DjJdgzNetworkLevel1 level1Network = djJdgzNetworkLevel1Service.get(level1NetworkId);
        SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy-MM-dd");
        String formattedDate = sdFormat.format(new Date());
        Date nowDate = null;
        String formattedDate = sdFormat.format(nowDate);
        Date startDate = null;
        try {
            nowDate = sdFormat.parse(formattedDate);
@@ -206,8 +201,8 @@
        if (isTyRole.equals(true) || isYwzRole.equals(true)) {
            userId = UserContext.getUser().getId();
        }*/
        int weekNo = this.getWeekNo(level1NetworkId);
        List<DjJdgzTrackRecord> data = baseDao.prompt(level1NetworkId, weekNo, userId,deptId,teamId,name);
        int weekNo = this.getWeekNo(level1NetworkId,new Date());
        List<DjJdgzTrackRecord> data = baseDao.prompt(level1NetworkId, weekNo, userId, deptId, teamId, name);
        for (DjJdgzTrackRecord record : data) {
            int i = weekNo - record.getWeekNo();
            record.setStatus(i + "周未完成");
@@ -219,8 +214,6 @@
                            Long level3NetworkId, Long level3NodeId, Integer status, Date beginDate,
                            Date endDate, Integer istq, List<ExcelColumnDto> columnDto) {
        List<ExprotExcelDto> dtos = new ArrayList<>();
   /*     Map<String, String> keywordMap = dailyPlanService.getReplacement();
        String keywordT = keywordMap.get("ting");*/

        Map<Integer, String> trackMethodMap = new HashMap<>();
        trackMethodMap.put(0, "线下");
@@ -295,7 +288,8 @@
        }
    }

    public List<DjJdgzTrackRecord> getListByImport(Long level1Id, Long level2Id, Long level2NodeId, String selectedIds) {
    public List<DjJdgzTrackRecord> getListByImport(Long level1Id, Long level2Id, Long level2NodeId, String
            selectedIds) {
        return baseDao.getListByImport(level1Id, level2Id, level2NodeId, selectedIds);
    }

@@ -360,7 +354,8 @@
                String trackLocationStr = ImportUtil.getCellValue(row, 5, pattern);
                String currentStatusStr = ImportUtil.getCellValue(row, 6, pattern);
                String hasDelayRiskStr = ImportUtil.getCellValue(row, 7, pattern);
                String estimatedCompletionTimeStr = ImportUtil.getCellValue(row, 8, pattern);
                Cell estimatedCompletionTimeStr = row.getCell(8);
                //String estimatedCompletionTimeStr = ImportUtil.getCellValue(row, 8, pattern);
                String problemStr = ImportUtil.getCellValue(row, 9, pattern);
                String followupPlanStr = ImportUtil.getCellValue(row, 10, pattern);
                String remarkStr = ImportUtil.getCellValue(row, 11, pattern);
@@ -387,7 +382,8 @@
                    isErr = true;
                    continue;
                } else {
                    int weekNo = this.getWeekNo(level1NetworkId);
                    Date parsedDate = new SimpleDateFormat("yyyy-MM-dd").parse(dataDate);
                    int weekNo = this.getWeekNo(level1NetworkId, parsedDate);
                    data.setWeekNo(weekNo);
                    data.setLevel1NetworkId(level1NetworkId);
                    data.setLevel3NetworkId(level3.getId());
@@ -440,17 +436,20 @@
                    data.setHasDelayRisk(hasDelayRiskMap.get(hasDelayRiskStr));
                }

                if (StringUtils.isNotBlank(estimatedCompletionTimeStr)) {
                    if (estimatedCompletionTimeStr.matches("[0-9/]+")) {
                        estimatedCompletionTimeStr = estimatedCompletionTimeStr.replace("/", "-");
                        data.setEstimatedCompletionTime(estimatedCompletionTimeStr);
                    } else if (estimatedCompletionTimeStr.matches("[0-9.]+")) {
                        estimatedCompletionTimeStr = estimatedCompletionTimeStr.replace(".", "-");
                        data.setEstimatedCompletionTime(estimatedCompletionTimeStr);
                    } else if (estimatedCompletionTimeStr.matches("[0-9-]+")) {
                        data.setEstimatedCompletionTime(estimatedCompletionTimeStr);
                    } else {
                        result = "第" + j + "行" + "第" + 1 + "个" + "预计完成时间日期格式不规范";
                if (estimatedCompletionTimeStr != null) {
                    try {
                        String date = ImportUtil.getDateStrFromCell(estimatedCompletionTimeStr);

                        if (date == null) {
                            result = "第" + j + "行" + "预计完成时间不能为空";
                            ImportUtil.updateErrMap(errMap, "预计完成时间不能为空", sheetName, row1);
                            isErr = true;
                        } else {
                            data.setEstimatedCompletionTime(date);
                        }
                    } catch (ExcelImportException e) {
                        result = "第" + j + "行" + "第" + 1 + "个" + "预计完成时间日期格式不正确";
                        ImportUtil.updateErrMap(errMap, "预计完成时间日期格式不正确", sheetName, row1);
                        isErr = true;
                    }
                }
@@ -472,6 +471,7 @@
                }

                if (StringUtils.isNotBlank(trackPersonContact)) {
                    trackPersonContact = TranslateToPlainStrUtils.translateToPlainStr(trackPersonContact);
                    data.setTrackPersonContact(trackPersonContact);
                }

@@ -484,6 +484,7 @@
                }

                if (StringUtils.isNotBlank(trackedPersonContact)) {
                    trackedPersonContact = TranslateToPlainStrUtils.translateToPlainStr(trackedPersonContact);
                    data.setTrackedPersonContact(trackedPersonContact);
                }

@@ -495,6 +496,7 @@
                }

                if (StringUtils.isNotBlank(generalRepairUnitContact)) {
                    generalRepairUnitContact = TranslateToPlainStrUtils.translateToPlainStr(generalRepairUnitContact);
                    data.setGeneralRepairUnitContact(generalRepairUnitContact);
                }
                if (StringUtils.isNotBlank(generalRepairUnitDirector)) {
@@ -504,6 +506,7 @@
                    data.setRepairUnit(repairUnit);
                }
                if (StringUtils.isNotBlank(generalRepairUnit)) {
                    generalRepairUnit = TranslateToPlainStrUtils.translateToPlainStr(generalRepairUnit);
                    data.setRepairUnitContact(repairUnitContact);
                }
                if (StringUtils.isNotBlank(generalRepairUnit)) {
@@ -511,15 +514,7 @@
                }

                if (!isErr) {
                    Long oldId = baseDao.getIsExit(data.getLevel3NetworkId(),data.getProcessName());
                    if (oldId!=null) {
                        data.setId(oldId);
                        data.setIsUpdate(true);
                    }else{
                        data.setIsUpdate(false);
                    }

                    this.save(data,"import");
                    this.save(data, "import");
                }
                if (isErr) {
                    err++;
@@ -543,4 +538,12 @@
    public List<ReportRecordDto> getReportRecord(Long level1NetworkId, int column, Long teamgroupId) {
        return baseDao.getReportRecord(level1NetworkId, column, teamgroupId);
    }

    public List<DjJdgzTrackRecord> getName(Long level3NetworkId) {
        return baseDao.getName(level3NetworkId);
    }

    public List<DjJdgzTrackRecord> getRecordById(Long id) {
        return baseDao.getRecordById(id);
    }
}