repair_backend/src/main/java/com/repair/controller/RepairClassesSettingsController.java

package com.repair.controller;

import com.repair.api.RepairClassesSettingsControllerAPI;
import com.repair.common.utils.ExcelExportUtil;
import com.repair.common.utils.ExcelUtils;
import com.repair.common.utils.TimeExchange;
import com.repair.common.utils.paramUtils;
import com.repair.model.enumModel.eFileType;
import com.repair.model.enumModel.eSchool;
import com.repair.model.pojo.RepairClassesSettings;
import com.repair.model.pojo.RepairShiftSettings;
import com.repair.model.pojo.RepairUser;
import com.repair.model.request.insertRepairClassesRequest;
import com.repair.model.request.updateRepairClassesRequest;
import com.repair.model.result.CommonResult;
import com.repair.model.result.PageUtils;
import com.repair.model.vo.*;
import com.repair.services.RepairClassesSettingsService;
import com.repair.services.RepairShiftSettingsService;
import com.repair.services.RepairUserService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.text.ParseException;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 前端控制器
 * </p>
 *
 * @author ceshi
 * @since 2023-07-13
 */
@RestController
public class RepairClassesSettingsController implements RepairClassesSettingsControllerAPI {

    @Autowired
    private RepairUserService repairUserService;
    @Autowired
    private RepairShiftSettingsService repairShiftSettingsService;
    @Autowired
    private RepairClassesSettingsService repairClassesSettingsService;

    /**
     * 新增排班数据
     * @param ircr 排版数据
     * @param bindingResult 是否为空判断
     * @return
     * @throws ParseException
     */
    @Override
    public CommonResult InsertRepairClassesSettings(insertRepairClassesRequest ircr, BindingResult bindingResult) throws ParseException {
        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        if (ircr.getStartDate() == null) {
            return CommonResult.fail("开始日期不能为空");
        }

        //防止选择的班次重叠
        List<RepairShiftSettings> datas = repairShiftSettingsService.getRepairsByShiftIds(ircr.getShiftIds());
        for (int i = 0; i < datas.size(); i++) {
            for (int j = i + 1; j < datas.size(); j++) {
                boolean isOver = TimeExchange.overlapping(datas.get(i).getStartTime(), datas.get(i).getEndTime(), datas.get(j).getStartTime(), datas.get(j).getEndTime());
                if (isOver) {
                    return CommonResult.fail(datas.get(i).getStartTime() + "-" + datas.get(i).getEndTime() + "时间段和" + datas.get(j).getStartTime() + "-" + datas.get(j).getEndTime() + "时间段存在重叠");
                }
            }
        }

        RepairClassesSettings rcs = new RepairClassesSettings();

        rcs.setUserId(ircr.getUserId());
        rcs.setShiftId(StringUtils.join(ircr.getShiftIds(), ','));
        rcs.setStartDate(ircr.getStartDate());

        int result = repairClassesSettingsService.insertRepairClassesSettings(rcs);

        return result > 0 ? CommonResult.ok("添加成功") : CommonResult.fail("添加失败");
    }

    /**
     * 根据ID更新排班数据
     * @param urcr 排班数据
     * @param bindingResult 是否为空判断
     * @return
     * @throws ParseException
     */
    @Override
    public CommonResult updateRepairClassesSettingsById(updateRepairClassesRequest urcr, BindingResult bindingResult) throws ParseException {
        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        if (urcr.getStartDate() == null) {
            return CommonResult.fail("开始日期不能为空");
        }

        RepairClassesSettings rcs = repairClassesSettingsService.getRepairById(urcr.getId());
        if (rcs == null) {
            return CommonResult.fail("当前数据不存在，修改失败！");
        }

        LocalDate dateNow = LocalDate.now();

        //小于等于当天时间的数据不能修改
        if(rcs.getStartDate().isBefore(dateNow) || rcs.getStartDate().isEqual(dateNow)){
            return CommonResult.fail("当天及当天以前的排班数据无法进行修改操作！");
        }

        //防止选择的班次重叠
        List<RepairShiftSettings> datas = repairShiftSettingsService.getRepairsByShiftIds(urcr.getShiftIds());
        for (int i = 0; i < datas.size(); i++) {
            for (int j = i + 1; j < datas.size(); j++) {
                boolean isOver = TimeExchange.overlapping(datas.get(i).getStartTime(), datas.get(i).getEndTime(), datas.get(j).getStartTime(), datas.get(j).getEndTime());
                if (isOver) {
                    return CommonResult.fail(datas.get(i).getStartTime() + "-" + datas.get(i).getEndTime() + "时间段和" + datas.get(j).getStartTime() + "-" + datas.get(j).getEndTime() + "时间段存在重叠");
                }
            }
        }

        rcs.setId(urcr.getId());
        rcs.setUserId(urcr.getUserId());
        rcs.setShiftId(StringUtils.join(urcr.getShiftIds(), ','));
        rcs.setStartDate(urcr.getStartDate());

        int result = repairClassesSettingsService.updateRepairClassesSettings(rcs);

        return result > 0 ? CommonResult.ok("修改成功") : CommonResult.fail("修改失败");
    }

    /**
     * 查询排班分页数据
     * @param currentPage 当前页数
     * @param pageCount 一页数据条数
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return
     */
    @Override
    public CommonResult queryPageRepairClassesSettingss(int currentPage, int pageCount, String startTime, String endTime) {
        PageUtils<RepairUser> result = repairUserService.queryPageRepairUser(currentPage, pageCount);
        List<Integer> userIds = result.getList().stream().map(RepairUser::getId).collect(Collectors.toList());

        if (startTime == null || endTime == null) {
            String[] weeks = TimeExchange.getCurrentWeekTimeFrame();
            startTime = weeks[0];
            endTime = weeks[1];
        }

        LocalDate StartDate = LocalDate.parse(startTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        LocalDate EndDate = LocalDate.parse(endTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<ClassSettingDateVo> dates = TimeExchange.getDatesBetween(StartDate, EndDate);

        try {
            startTime = startTime == null ? startTime : TimeExchange.getStartOfDayStr(TimeExchange.StringToDate(startTime, "yyyy-MM-dd"));
            endTime = endTime == null ? endTime : TimeExchange.getEndOfDayStr(TimeExchange.StringToDate(endTime, "yyyy-MM-dd"));
        } catch (Exception e) {

        }

        List<ClassesSettingVo> datas = repairClassesSettingsService.getRepairByUserIds(userIds, startTime, endTime);
        List<ClassesSettingPageDataVo> cspds = new ArrayList<>();
        for (RepairUser ru : result.getList()) {
            ClassesSettingPageDataVo cspd = new ClassesSettingPageDataVo();
            cspd.setSchoolId(ru.getSchoolId());
            cspd.setSchoolName(eSchool.stringOf(ru.getSchoolId()));
            cspd.setUserId(ru.getId());
            cspd.setUserName(ru.getUserName());
            List<ClassesSettingVo> classDatas = datas.stream().filter(e -> e.getUserId() == ru.getId()).collect(Collectors.toList());

            for (ClassSettingDateVo d : dates) {
                Optional<ClassesSettingVo> o = classDatas.stream().filter(e -> e.getStartDate().equals(d.getDateStr())).findFirst();
                if (!(o != null && o.isPresent())) {
                    ClassesSettingVo newData = new ClassesSettingVo();
                    newData.setStartDate(d.getDateStr());
                    newData.setUserId(ru.getId());
                    classDatas.add(newData);
                }
            }
            classDatas.sort(Comparator.comparing(ClassesSettingVo::getStartDate));
            cspd.setClassSettings(classDatas);
            cspds.add(cspd);
        }

        ClassesSettingPageVo data = new ClassesSettingPageVo();
        data.setDates(dates);
        PageUtils ssss = new PageUtils(cspds, result.getTotalCount(), result.getTotalPage(), result.getCurrPage());
        data.setPageDatas(ssss);
        return CommonResult.ok(data);
    }

    /**
     * 排班数据导出
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param response 响应流
     */
    @Override
    public void downloadRepairClassesExcel(String startTime, String endTime, HttpServletResponse response){
        List<RepairUser> result = repairUserService.queryRepairUsers();

        List<Integer> userIds = result.stream().map(RepairUser::getId).collect(Collectors.toList());

        if (startTime == null || endTime == null) {
            String[] weeks = TimeExchange.getCurrentWeekTimeFrame();
            startTime = weeks[0];
            endTime = weeks[1];
        }

        LocalDate StartDate = LocalDate.parse(startTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        LocalDate EndDate = LocalDate.parse(endTime, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        List<ClassSettingDateVo> dates = TimeExchange.getDatesBetween(StartDate, EndDate);

        try {
            startTime = startTime == null ? startTime : TimeExchange.getStartOfDayStr(TimeExchange.StringToDate(startTime, "yyyy-MM-dd"));
            endTime = endTime == null ? endTime : TimeExchange.getEndOfDayStr(TimeExchange.StringToDate(endTime, "yyyy-MM-dd"));
        } catch (Exception e) {

        }

        List<ClassesSettingVo> datas = repairClassesSettingsService.getRepairByUserIds(userIds, startTime, endTime);
        List<Map<String, Object>> list = new ArrayList<>();
        for (RepairUser ru : result) {
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("名称", ru.getUserName());
            List<ClassesSettingVo> classDatas = datas.stream().filter(e -> e.getUserId() == ru.getId()).collect(Collectors.toList());

            for (ClassSettingDateVo d : dates) {
                Optional<ClassesSettingVo> o = classDatas.stream().filter(e -> e.getStartDate().equals(d.getDateStr())).findFirst();
                if (!(o != null && o.isPresent())) {
                    map.put(d.getDateStr() + "(" + d.getWeekStr() + ")", "");
                } else {
                    map.put(d.getDateStr() + "(" + d.getWeekStr() + ")", o.get().getShiftName());
                }
            }

            list.add(map);
        }

        ExcelExportUtil.createExcel(list, "testExcel.xlsx", "排班文档", response);
    }

    /**
     * 根据数据ID删除排班数据
     * @param id 数据ID
     * @return
     */
    @Override
    public CommonResult deleteRepairClassesSettingsById(int id) {
        RepairClassesSettings data = repairClassesSettingsService.getRepairById(id);
        if (data == null) {
            return CommonResult.fail("当前数据不存在，删除失败！");
        }

        LocalDate dateNow = LocalDate.now();
        //小于等于当天时间的数据不能删除
        if(data.getStartDate().isBefore(dateNow) || data.getStartDate().isEqual(dateNow)){
            return CommonResult.fail("当天及当天以前的排班数据无法进行删除操作！");
        }

        int result = repairClassesSettingsService.deleteRepairClassesSettingsById(id);

        return result > 0 ? CommonResult.ok("删除成功") : CommonResult.fail("删除失败");
    }

    /**
     * 排班模板导出
     * @param response 响应流
     */
    @Override
    public void downloadRepairClassesSettingExcel(HttpServletResponse response) {
        ExcelUtils.fileDownload("/自定义排班模板.xlsx", response);
    }

    /**
     * 维修师傅首页当天排班分页数据
     * @param currentPage 当前页数
     * @param pageCount 一页数据条数
     * @param schoolId 校区ID
     * @return
     */
    @Override
    public CommonResult queryPageUserClasses(int currentPage, int pageCount, int schoolId) {
        PageUtils<AddressBookVo> result = repairUserService.queryUserPageBySchoolId(currentPage, pageCount, schoolId);
        List<Integer> userIds = result.getList().stream().map(AddressBookVo::getId).collect(Collectors.toList());

        List<ClassesDataVo> users = repairClassesSettingsService.queryClassesDatas(userIds, schoolId);

        List<UserScheduleVo> results = new ArrayList<>();
        for (AddressBookVo ru : result.getList()) {
            UserScheduleVo dataV = new UserScheduleVo();
            dataV.setId(ru.getId());
            dataV.setUserName(ru.getUserName());
            dataV.setUserPhone(ru.getUserPhone());
            dataV.setWorkTypeName(ru.getWorkTypeName());
            dataV.setState(ru.getState());
            dataV.setStateStr(ru.getStateStr());
            Optional<ClassesDataVo> data = users.stream().filter(e -> Integer.valueOf(e.getUserId()).equals(ru.getId())).findFirst();
            if (data != null && data.isPresent()) {
                dataV.setShifts(data.get().getShifts());
                dataV.setClassId(data.get().getId());
            }
            results.add(dataV);
        }

        PageUtils resultPage = new PageUtils(results, result.getTotalCount(), result.getTotalPage(), result.getCurrPage());
        return CommonResult.ok(resultPage);
    }

    /**
     * 后勤首页修改或新增当天排班数据
     * @param usv 排班数据
     * @param bindingResult 是否为空判断
     * @return
     * @throws ParseException
     */
    @Override
    public CommonResult updateUserClasses(UserScheduleVo usv, BindingResult bindingResult) throws ParseException {
        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        RepairUser user = repairUserService.getRepairById(usv.getId());
        if (user == null) {
            return CommonResult.fail("用户信息失效，修改失败");
        }

        if (!user.getState().equals(usv.getState())) {
            user.setState(usv.getState());
            int updateUser = repairUserService.updateRepairUser(user);
            if (updateUser <= 0) {
                return CommonResult.fail("用户信息更新失败");
            }
        }
        if (usv.getShifts() != null && usv.getShifts().size() > 0) {
            List<String> shifts = usv.getShifts().stream().map(ShiftSettingDataVo::getId).distinct().collect(Collectors.toList()).stream().map(String::valueOf).collect(Collectors.toList());

            List<RepairShiftSettings> datas = repairShiftSettingsService.getRepairsByShiftIds(shifts);
            for (int i = 0; i < datas.size(); i++) {
                for (int j = i + 1; j < datas.size(); j++) {
                    boolean isOver = TimeExchange.overlapping(datas.get(i).getStartTime(), datas.get(i).getEndTime(), datas.get(j).getStartTime(), datas.get(j).getEndTime());
                    if (isOver) {
                        return CommonResult.fail(datas.get(i).getStartTime() + "-" + datas.get(i).getEndTime() + "时间段和" + datas.get(j).getStartTime() + "-" + datas.get(j).getEndTime() + "时间段存在重叠");
                    }
                }
            }

            //为空则新增排班数据
            if (usv.getClassId() == null) {
                RepairClassesSettings rcs = new RepairClassesSettings();
                rcs.setUserId(usv.getId());
                Date date = new Date();
                Instant instant = date.toInstant();
                ZoneId zoneId = ZoneId.systemDefault();
                // atZone()方法返回在指定时区从此Instant生成的ZonedDateTime。
                LocalDate localDate = instant.atZone(zoneId).toLocalDate();
                rcs.setStartDate(localDate);
                rcs.setShiftId(StringUtils.join(shifts, ","));

                int insertClasses = repairClassesSettingsService.insertRepairClassesSettings(rcs);
                if (insertClasses <= 0) {
                    return CommonResult.fail("排班信息新增失败");
                }
            } else {
                //判断对应师傅当天排班数据是否存在
                RepairClassesSettings csv = repairClassesSettingsService.getRepairById(usv.getClassId());

                if(csv == null){
                    return CommonResult.fail("排班数据不存在更新失败");
                }

                csv.setShiftId(StringUtils.join(shifts, ","));
                int updateClasses = repairClassesSettingsService.updateRepairClassesSettings(csv);
                if (updateClasses <= 0) {
                    return CommonResult.fail("排班信息更新失败");
                }
            }
        }

        return CommonResult.ok("操作成功");

    }

    /**
     * 排班数据导入
     * @param file 导入文件
     * @return
     * @throws IOException
     * @throws ParseException
     */
    @Override
    public CommonResult importRepairClassesSettingExcel(MultipartFile file) throws IOException, ParseException {
        System.out.println("导入排班");
        if (file.isEmpty() || file.getSize() == 0) {
            return CommonResult.fail("导入文件不能为空");
        }
        String ContentType = file.getContentType();
        InputStream inputStream = file.getInputStream();
        List<RepairClassesSettings> result = new ArrayList<>();

        //xls格式文件
        if (ContentType.equals(eFileType.Xls.getValue())) {

            CommonResult<List<RepairClassesSettings>> resultData = readXls(inputStream);
            if (!resultData.isSuccess()) {
                return resultData;
            }
            result = resultData.getData();

        } else if (ContentType.equals(eFileType.Xlsx.getValue())) {
            CommonResult<List<RepairClassesSettings>> resultData = readXlsx(inputStream);
            if (!resultData.isSuccess()) {
                return resultData;
            }
            result = resultData.getData();

        } else {
            return CommonResult.fail("排班数据导入只支持Xls、Xlsx");
        }

        boolean resultBool = repairClassesSettingsService.saveBatch(result);
        System.out.println("导入排班1");
        return resultBool ? CommonResult.ok("导入成功") : CommonResult.fail("导入失败");
    }

    /**
     * xls文件读取方法
     * @param inputStream
     * @return
     * @throws IOException
     * @throws ParseException
     */
    private CommonResult<List<RepairClassesSettings>> readXls(InputStream inputStream) throws IOException, ParseException {
        int month = 0;
        int year = 0;
        List<String> datas = new ArrayList<>();
        HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
        List<RepairClassesSettings> result = new ArrayList<>();
        //读取第一张sheet
        HSSFSheet sheetAt = sheets.getSheetAt(0);

        List<String> UserNames = new ArrayList<>();
        List<Integer> userZZs = new ArrayList<>();
        userZZs.add(1);
        userZZs.add(2);
        List<RepairUser> users = repairUserService.getRepairByUserZZ(userZZs, null);
        List<RepairShiftSettings> shifts = repairShiftSettingsService.getRepairs();
        List<RepairClassesSettings> clssses = new ArrayList<>();
        //rowNum = 3 从第三行开始获取值
        //sheetAt.getLastRowNum()：从0开始统计数量 所以得+1
        for (int rowNum = 0; rowNum < sheetAt.getLastRowNum() + 1; rowNum++) {

            HSSFRow row = sheetAt.getRow(rowNum);


            if (row != null) {
                //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
                //所以先使用setCellType()方法先将该单元格的类型设置为STRING
                //然后poi会根据字符串读取它
                //第一行数据获取月份
                if (rowNum == 0) {
                    try {
                        row.getCell(0).setCellType(CellType.STRING);
                        month = Integer.valueOf(row.getCell(0).getStringCellValue().substring(4, 6));
                        year = Integer.valueOf(row.getCell(0).getStringCellValue().substring(0, 4));
                    } catch (Exception e) {
                        return CommonResult.fail("请按模板格式导入数据");
                    }
                    clssses = repairClassesSettingsService.getRepairByMonths(year + "-" + String.format("%02d", month));
                } else if (rowNum == 3) {//日期

                    for (int d = 3; d <= row.getPhysicalNumberOfCells() - 1; d++) {
                        row.getCell(d).setCellType(CellType.STRING);
                        String stringCellValue = year + "-" + String.format("%02d", month) + "-" + String.format("%02d", Integer.valueOf(row.getCell(d).getStringCellValue()));
                        datas.add(stringCellValue);
                    }
                } else if (rowNum >= 4) {
                    row.getCell(0).setCellType(CellType.STRING);
                    String UserName = row.getCell(0).getStringCellValue();
                    row.getCell(1).setCellType(CellType.STRING);
                    String CardNumber = row.getCell(1).getStringCellValue();
                    int userId = 0;
                    Optional<RepairUser> userData = users.stream().filter(e -> e.getUserName().equals(UserName)).findFirst();
                    if (userData != null && userData.isPresent()) {
                        userId = userData.get().getId();
                    }
                    if (userId == 0) {
                        return CommonResult.fail(UserName + "用户数据不存在");
                    }
                    UserNames.add(UserName);
                    if (UserNames.stream().distinct().count() != UserNames.size()) {
                        return CommonResult.fail("导入的Excel中" + UserName + "存在重复数据");
                    }

                    for (int d = 1; d <= datas.size(); d++) {
                        Integer userid = userId;
                        String dateStr = datas.get(d - 1);
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                        //重复数据不让导入
                        long exist = clssses.stream().filter(e -> e.getUserId().equals(userid) && e.getStartDate().format(formatter).equals(dateStr)).count();
                        if (exist > 0) {
                            return CommonResult.fail(UserName + "的" + dateStr + "日数据在系统中已存在，请勿重复导入");
                        }

                        //用户ID
                        RepairClassesSettings classData = new RepairClassesSettings();
                        classData.setUserId(userId);
                        //日期
                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                        LocalDate dateParam = LocalDate.parse(datas.get(d - 1), df);
                        classData.setStartDate(dateParam);
                        //班次ID
                        row.getCell(d+2).setCellType(CellType.STRING);
                        String stringCellValue = row.getCell(d+2).getStringCellValue();
                        String[] shiftStrs = stringCellValue.split("、");
                        List<String> shiftIds = new ArrayList<>();
                        List<RepairShiftSettings> shiftDatas = new ArrayList<>();
                        for (String shiftStr : shiftStrs) {
                            if (!shiftStr.equals("")) {
                                Optional<RepairShiftSettings> shiftData = shifts.stream().filter(e -> e.getName().equals(shiftStr)).findFirst();
                                if (shiftData != null && shiftData.isPresent()) {
                                    shiftIds.add(shiftData.get().getId().toString());
                                    shiftDatas.add(shiftData.get());
                                } else {
                                    return CommonResult.fail(UserName + "的" + datas.get(d - 1) + "的" + shiftStr + "数据不存在");
                                }
                            }
                        }

                        for (int i = 0; i < shiftDatas.size(); i++) {
                            for (int j = i + 1; j < shiftDatas.size(); j++) {
                                boolean isOver = TimeExchange.overlapping(shiftDatas.get(i).getStartTime(), shiftDatas.get(i).getEndTime(), shiftDatas.get(j).getStartTime(), shiftDatas.get(j).getEndTime());
                                if (isOver) {
                                    return CommonResult.fail(UserName + "师傅的" + datas.get(d - 1) + "日" + shiftDatas.get(i).getStartTime() + "-" + shiftDatas.get(i).getEndTime() + "时间段和" + shiftDatas.get(j).getStartTime() + "-" + shiftDatas.get(j).getEndTime() + "时间段存在重叠");
                                }
                            }
                        }
                        classData.setShiftId(StringUtils.join(shiftIds, ','));
                        result.add(classData);
                    }
                }
            }
        }
        return CommonResult.ok(result);
    }

    /**
     * Xlsx文件读取方法
     * @param inputStream
     * @return
     * @throws IOException
     * @throws ParseException
     */
    private CommonResult<List<RepairClassesSettings>> readXlsx(InputStream inputStream) throws IOException, ParseException {
        int month = 0;
        int year = 0;
        List<String> datas = new ArrayList<>();
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
        List<RepairClassesSettings> result = new ArrayList<>();
        //读取第一张sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);

        List<String> UserNames = new ArrayList<>();
        List<Integer> userZZs = new ArrayList<>();
        userZZs.add(1);
        userZZs.add(2);
        List<RepairUser> users = repairUserService.getRepairByUserZZ(userZZs, null);
        List<RepairShiftSettings> shifts = repairShiftSettingsService.getRepairs();
        List<RepairClassesSettings> clssses = new ArrayList<>();
        //rowNum = 3 从第三行开始获取值
        //sheetAt.getLastRowNum()：从0开始统计数量 所以得+1
        for (int rowNum = 0; rowNum < sheetAt.getLastRowNum() + 1; rowNum++) {

            XSSFRow row = sheetAt.getRow(rowNum);


            if (row != null) {
                //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
                //所以先使用setCellType()方法先将该单元格的类型设置为STRING
                //然后poi会根据字符串读取它
                //第一行数据获取月份
                if (rowNum == 0) {
                    try {
                        row.getCell(0).setCellType(CellType.STRING);
                        month = Integer.valueOf(row.getCell(0).getStringCellValue().substring(4, 6));
                        year = Integer.valueOf(row.getCell(0).getStringCellValue().substring(0, 4));
                    } catch (Exception e) {
                        return CommonResult.fail("请按模板格式导入数据");
                    }
                    clssses = repairClassesSettingsService.getRepairByMonths(year + "-" + String.format("%02d", month));

                } else if (rowNum == 3) {//日期
                    for (int d = 3; d <= row.getPhysicalNumberOfCells() - 1; d++) {
                        row.getCell(d).setCellType(CellType.STRING);
                        String stringCellValue = year + "-" + String.format("%02d", month) + "-" + String.format("%02d", Integer.valueOf(row.getCell(d).getStringCellValue()));
                        datas.add(stringCellValue);
                    }
                } else if (rowNum >= 4) {
                    row.getCell(0).setCellType(CellType.STRING);
                    String UserName = row.getCell(0).getStringCellValue();
                    row.getCell(1).setCellType(CellType.STRING);
                    String CardNumber = row.getCell(1).getStringCellValue();
                    int userId = 0;
                    Optional<RepairUser> userData = users.stream().filter(e -> e.getUserName().equals(UserName) && e.getCardNumber().equals(CardNumber)).findFirst();
                    if (userData != null && userData.isPresent()) {
                        userId = userData.get().getId();
                    }
                    if (userId == 0) {
                        return CommonResult.fail(UserName + "用户数据不存在");
                    }
                    UserNames.add(UserName);
                    if (UserNames.stream().distinct().count() != UserNames.size()) {
                        return CommonResult.fail("导入的Excel中" + UserName + "存在重复数据");
                    }

                    for (int d = 1; d <= datas.size(); d++) {
                        Integer userid = userId;
                        String dateStr = datas.get(d - 1);
                        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                        //重复数据不让导入
                        long exist = clssses.stream().filter(e -> e.getUserId().equals(userid) && e.getStartDate().format(formatter).equals(dateStr)).count();
                        if (exist > 0) {
                            return CommonResult.fail(UserName + "的" + dateStr + "日数据在系统中已存在，请勿重复导入");
                        }

                        //用户ID
                        RepairClassesSettings classData = new RepairClassesSettings();
                        classData.setUserId(userId);
                        //日期
                        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                        LocalDate dateParam = LocalDate.parse(datas.get(d - 1), df);
                        classData.setStartDate(dateParam);
                        //班次ID
                        row.getCell(d+2).setCellType(CellType.STRING);
                        String stringCellValue = row.getCell(d+2).getStringCellValue();
                        String[] shiftStrs = stringCellValue.split("、");
                        List<String> shiftIds = new ArrayList<>();
                        List<RepairShiftSettings> shiftDatas = new ArrayList<>();
                        for (String shiftStr : shiftStrs) {
                            if (!shiftStr.equals("")) {
                                Optional<RepairShiftSettings> shiftData = shifts.stream().filter(e -> e.getName().equals(shiftStr)).findFirst();
                                if (shiftData != null && shiftData.isPresent()) {
                                    shiftIds.add(shiftData.get().getId().toString());
                                    shiftDatas.add(shiftData.get());
                                } else {
                                    return CommonResult.fail(UserName + "的" + datas.get(d - 1) + "的" + shiftStr + "数据不存在");
                                }
                            }
                        }

                        for (int i = 0; i < shiftDatas.size(); i++) {
                            for (int j = i + 1; j < shiftDatas.size(); j++) {
                                boolean isOver = TimeExchange.overlapping(shiftDatas.get(i).getStartTime(), shiftDatas.get(i).getEndTime(), shiftDatas.get(j).getStartTime(), shiftDatas.get(j).getEndTime());
                                if (isOver) {
                                    return CommonResult.fail(UserName + "师傅的" + datas.get(d - 1) + "日" + shiftDatas.get(i).getStartTime() + "-" + shiftDatas.get(i).getEndTime() + "时间段和" + shiftDatas.get(j).getStartTime() + "-" + shiftDatas.get(j).getEndTime() + "时间段存在重叠");
                                }
                            }
                        }
                        classData.setShiftId(StringUtils.join(shiftIds, ','));
                        result.add(classData);
                    }
                }
            }
        }
        return CommonResult.ok(result);
    }

}