package com.template.controller;


import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.template.annotation.DESRespondSecret;
import com.template.api.SmartScoreControllerAPI;
import com.template.common.utils.AesUtils;
import com.template.common.utils.ExcelUtils;
import com.template.common.utils.paramUtils;
import com.template.model.enumModel.*;
import com.template.model.evaluate.student.SmartEvaluateStudent;
import com.template.model.evaluate.student.SmartSubjectTemplate;
import com.template.model.pojo.*;
import com.template.model.request.deleteSmartScoreRequest;
import com.template.model.request.insertScoreRequest;
import com.template.model.request.updateScoreRequest;
import com.template.model.result.CommonResult;
import com.template.model.result.PageUtils;
import com.template.model.vo.*;
import com.template.services.*;
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.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.omg.CORBA.INTERNAL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.validation.BindingResult;

import org.springframework.web.bind.annotation.RequestHeader;
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.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 楼栋表 前端控制器
 * </p>
 *
 * @author ceshi
 * @since 2024-01-02
 */
@RestController
//返回参数加密注解
@DESRespondSecret
public class SmartScoreController implements SmartScoreControllerAPI {

    @Autowired
    private SmartScoreService smartScoreService;

    @Autowired
    private SmartUserService smartUserService;

    @Autowired
    private SmartGradeService smartGradeService;

    @Autowired
    private SmartClassService smartClassService;

    @Autowired
    private SmartSemesterService smartSemesterService;

    @Autowired
    private SmartSubjectService smartSubjectService;

    @Autowired
    private SmartExamtypeService smartExamtypeService;

    @Autowired
    private SmartAuthorGroupService smartAuthorGroupService;

    @Autowired
    private SmartEvaluateStudentService smartEvaluateStudentService;

    @Autowired
    private SmartSubjectTemplateService smartSubjectTemplateService;

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult querySmartSemesters() {
        List<SmartSemester> semesters = smartSemesterService.list(null);
//        //判断当前时间的月份
//        // 2月-6月底是春季学期
//        // 9月-次年1月底是秋季学期
//        List<SmartScore> scores = smartScoreService.list(null);
//        List<String> semesters = scores.stream().map(SmartScore::getSemester).distinct().collect(Collectors.toList());

        return CommonResult.ok(semesters);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult queryUserGrade(int userId) {
        //判断学生是否有效
        SmartUser user = smartUserService.getSmartById(userId);
        if (user == null) {
            return CommonResult.fail("学生信息无效，无法获取年级、班级和学号");
        }

        if (eIdentityStatu.Student.getValue() != user.getIdentityId().intValue()) {
            return CommonResult.fail("选择的用户身份非学生，无法获取年级、班级和学号");
        }

        GradeVo result = new GradeVo();
        result.setGrade(user.getGrade());
        result.setSchoolClass(user.getSchoolClass());
        result.setCardNo(user.getCardNo());

        return CommonResult.ok(result);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult querySubjects(int gradeId) {
        SmartGrade grade = smartGradeService.querySmartGradeById(gradeId);
        List<SmartSubject> result = new ArrayList<>();
        if (grade == null) {
            return CommonResult.ok(result);
        }

        if (grade.getSubjectId() == null) {
            return CommonResult.ok(result);
        }
        List<String> subjectIds = Arrays.asList(grade.getSubjectId().split(","));
        result = smartSubjectService.getSmartSubjectIds(subjectIds);
//        List<SemesterVo> semesters = new ArrayList<>();
//        for (int i = 1; i <= 12; i++) {
//            SemesterVo semester = new SemesterVo();
//            semester.setId(i);
//            semester.setName(eSubjectStatu.stringOf(i));
//            semesters.add(semester);
//        }

        return CommonResult.ok(result);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult queryExamTypes(int gradeId) {
        List<SmartExamtype> exams = smartExamtypeService.getSmartByGradeId(gradeId);

        List<SemesterVo> semesters = new ArrayList<>();
        for (SmartExamtype type : exams) {
            SemesterVo semester = new SemesterVo();
            semester.setId(type.getId());
            semester.setName(type.getName());
            semesters.add(semester);
        }
        return CommonResult.ok(semesters);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult querySmartSecordPage(int currentPage, int pageCount, String name, Integer userId) {
        PageUtils<GradeVo> result = smartUserService.querySmartSecordPage(currentPage, pageCount, name, userId);
        return CommonResult.ok(result);
    }

    /**
     * 明天补查询条件
     *
     * @param currentPage
     * @param pageCount
     * @return
     */
    /**
     * 按学期、年级、班级、学科、考试类型 和 成绩进行查询
     * 获取成绩分页数据
     *
     * @param currentPage 当前页数
     * @param pageCount   一页数据条数
     * @param name        学号或名称
     * @param semester    学期 需要转换为int
     * @param grade       年级
     * @param schoolClass 班级
     * @param subject     科目 需要转换为int
     * @param examType    考试类型 需要转换为int
     * @param MinScore    最低成绩
     * @param MaxScore    最高成绩
     * @return
     */
    @Override
    @DESRespondSecret(validated = true)
    public CommonResult querySmartScorePage(int currentPage, int pageCount, /*@RequestHeader("user_head") String userhead, */String name, String semester, String grade, String schoolClass, String subject, String examType, Double MinScore, Double MaxScore) {

        PageUtils<ScoreVo> result = null;
        Integer gradeId = null, calssId = null;
        //region
        //2024-03-13 添加逻辑：每个班级班主任只能看到自己班的，超级管理员可看到全部
//        String userId = AesUtils.decrypt(userhead);
//
//        if(userId != null){
//            SmartUser userData = smartUserService.getSmartById(Integer.valueOf(userId));
//            if (userData == null) {
//                return CommonResult.ok(result);
//            }
//
//            //是否是超级管理员
//            List<SmartAuthorGroup> sags = smartAuthorGroupService.smartAuthorGroupSuperAdmins(userData.getId());
//            if (sags == null) {
//                //班主任
//                if (userData.getDuties().intValue() == eDuties.ClassTeacher.getValue()) {
//                    gradeId = Integer.valueOf(userData.getGrade());
//                    calssId = userData.getSchoolClass();
//                } else {
//                    return CommonResult.ok(result);
//                }
//            }
//        }

        //endregion

        Integer subjectInt = subject == null ? null : eSubjectStatu.integerOf(subject);
        Integer examTypeInt = examType == null ? null : eExamType.integerOf(examType);

        PageUtils<ScorePageVo> pageData = smartScoreService.querySmartScorePage(currentPage, pageCount, name, semester, grade, schoolClass, subjectInt, examTypeInt, MinScore, MaxScore, gradeId, calssId);

        List<SmartSubject> subjects = smartSubjectService.list(null);
        List<SmartExamtype> examtypes = smartExamtypeService.list(null);

        if (pageData != null) {
            List<ScoreVo> datas = new ArrayList<>();
            for (ScorePageVo spv : pageData.getList()) {
                ScoreVo data = new ScoreVo();
                data.setId(spv.getId());
                data.setSemester(spv.getSemester());
                data.setSemesterId(spv.getSemesterId());
                data.setGrade(spv.getGrade());
                data.setGradeId(spv.getGradeId());
                data.setSchoolClass(spv.getSchoolClass());
                data.setSchoolClassId(spv.getSchoolClassId());
                data.setSubject(spv.getSubjectId());
                Optional<SmartSubject> subjectData = subjects.stream().filter(e -> e.getId().equals(spv.getSubjectId())).findFirst();
                if (subjectData != null && subjectData.isPresent()) {
                    data.setSubjectStr(subjectData.get().getName());
                }

                data.setExamType(spv.getExamType());
                Optional<SmartExamtype> examtypeData = examtypes.stream().filter(e -> e.getId().equals(spv.getExamType())).findFirst();
                if (examtypeData != null && examtypeData.isPresent()) {
                    data.setExamTypeStr(examtypeData.get().getName());
                }

                data.setName(spv.getName());
                data.setUserId(spv.getUserId());
                data.setCardNo(spv.getCardNo());
                data.setScore(spv.getScore());
                datas.add(data);
            }

            result = new PageUtils<ScoreVo>(datas, pageData.getTotalCount(), pageData.getPageSize(), pageData.getCurrPage());
        }

        return CommonResult.ok(result);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult insertSmartScore(insertScoreRequest isr, BindingResult bindingResult) {
        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        //判断学生是否有效
        SmartUser user = smartUserService.getSmartById(isr.getUserId());
        if (user == null) {
            return CommonResult.fail("学生信息无效，无法添加学生成绩");
        }

        if (eIdentityStatu.Student.getValue() != user.getIdentityId().intValue()) {
            return CommonResult.fail("选择的用户身份非学生，无法添加成绩");
        }

        SmartSubject subject = smartSubjectService.getSmartById(isr.getSubjectId());
        if (subject == null) {
            return CommonResult.fail(subject.getName() + "的科目数据为空，新增成绩失败");
        }

        SmartExamtype examtype = smartExamtypeService.getSmartById(isr.getExamType());
        if (examtype == null) {
            return CommonResult.fail(examtype.getName() + "的考试类型数据为空，新增成绩失败");
        }

        //成绩去重判断  一个学生同一个学期同一个考试类型同一个科目只能加一次
        int existCount = smartScoreService.getExistScore(isr.getUserId(), isr.getSemesterId(), isr.getExamType(), isr.getSubjectId());
        if (existCount > 0) {
            return CommonResult.fail(user.getName() + "的" + isr.getSemester() + "的" + examtype.getName() + "的" + subject.getName() + "的成绩在系统中已存在");
        }

        SmartScore ss = new SmartScore();
        ss.setSemester(isr.getSemester());
        ss.setSemesterId(isr.getSemesterId());
        ss.setGrade(isr.getGrade());
        ss.setGradeId(isr.getGradeId());
        ss.setSchoolClass(isr.getSchoolClass());
        ss.setSchoolClassId(isr.getSchoolClassId());
        ss.setCardNo(isr.getCardNo());
        ss.setUserId(isr.getUserId());
        ss.setName(user.getName());
        ss.setSubjectId(isr.getSubjectId());
        ss.setExamType(isr.getExamType());
        ss.setScore(isr.getScore());


        SmartExamtype examType = smartExamtypeService.getSmartById(isr.getExamType());
        if (examType == null) {
            return CommonResult.fail("考试类型数据为空，添加失败");
        }

        int result = smartScoreService.insertSmartScore(ss);
        if (result <= 0) {
            return CommonResult.fail("添加失败");
        }

        //region 学生综合素质评价
        SmartEvaluateStudent evaStudent = smartEvaluateStudentService.getEvaluateStudent(isr.getUserId(), isr.getSemesterId());
        SmartSubjectTemplate subjectTemplate = smartSubjectTemplateService.queryTemplateByTermId(isr.getSemesterId(), isr.getGradeId());
        if (subjectTemplate != null) {
            List<String> examTypes = Arrays.asList(subjectTemplate.getExamType().split(","));
            List<SmartScore> userScores = smartScoreService.getScores(isr.getUserId(), isr.getSemesterId(), examTypes);
            BigDecimal totalScoreNum = new BigDecimal(0.0);
            Integer scoreNum = 0;
            if (userScores != null && userScores.size() > 0) {
                for (SmartScore userScore : userScores) {
                    totalScoreNum = totalScoreNum.add(new BigDecimal(userScore.getScore()));
                    scoreNum++;
                }
            }

            if (evaStudent != null) {
                evaStudent.setScoreNum(totalScoreNum.doubleValue());
                evaStudent.setSubjectNum(scoreNum);
                int updateEva = smartEvaluateStudentService.updateSmartEvaluateStudent(evaStudent);
                if (updateEva <= 0) {
                    return CommonResult.fail("添加失败");
                }
            } else {
                //region 添加到学生综合素质评价表中
                SmartEvaluateStudent ses = new SmartEvaluateStudent();
                ses.setTermId(isr.getSemesterId());
                ses.setTerm(isr.getSemester());
                ses.setGradeName(isr.getGrade());
                ses.setGradeId(isr.getGradeId());
                ses.setClassId(isr.getSchoolClassId());
                ses.setClassName(isr.getSchoolClass());
                ses.setCardNo(isr.getCardNo());
                ses.setName(user.getName());
                ses.setUserId(isr.getUserId());
                ses.setScoreNum(totalScoreNum.doubleValue());
                ses.setSubjectNum(scoreNum);

                Integer insertStudent = smartEvaluateStudentService.insertSmartEvaluateStudent(ses);

                if (insertStudent <= 0) {
                    return CommonResult.fail("添加失败");
                }
                //endregion
            }
        }
        //endregion

        return CommonResult.ok("添加成功");
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult updateSmartScoreById(updateScoreRequest usr, BindingResult bindingResult) {
        if (bindingResult.hasErrors()) {
            String st = paramUtils.getParamError(bindingResult);
            return CommonResult.fail(st);
        }

        SmartScore score = smartScoreService.getSmartById(usr.getId());
        if (score == null) {
            return CommonResult.fail("成绩数据无效，无法编辑");
        }

        //判断学生是否有效
        SmartUser user = smartUserService.getSmartById(usr.getUserId());
        if (user == null) {
            return CommonResult.fail("学生信息无效，无法编辑学生成绩");
        }

        if (eIdentityStatu.Student.getValue() != user.getIdentityId().intValue()) {
            return CommonResult.fail("选择的用户身份非学生，无法编辑成绩");
        }

        score.setSemester(usr.getSemester());
        score.setSemesterId(usr.getSemesterId());
        score.setGradeId(usr.getGradeId());
        score.setGrade(usr.getGrade());
        score.setSchoolClassId(usr.getSchoolClassId());
        score.setSchoolClass(usr.getSchoolClass());
        score.setCardNo(usr.getCardNo());
        score.setUserId(usr.getUserId());
        score.setName(user.getName());
        score.setSubjectId(usr.getSubjectId());
        score.setExamType(usr.getExamType());
        score.setScore(usr.getScore());

        int result = smartScoreService.updateSmartScore(score);
        if (result <= 0) {
            return CommonResult.fail("编辑失败");
        }

        //region 学生综合素质评价
        SmartEvaluateStudent evaStudent = smartEvaluateStudentService.getEvaluateStudent(usr.getUserId(), usr.getSemesterId());
        SmartSubjectTemplate subjectTemplate = smartSubjectTemplateService.queryTemplateByTermId(usr.getSemesterId(), usr.getGradeId());
        if (subjectTemplate != null) {
            List<String> examTypes = Arrays.asList(subjectTemplate.getExamType().split(","));
            List<SmartScore> userScores = smartScoreService.getScores(usr.getUserId(), usr.getSemesterId(), examTypes);
            BigDecimal totalScoreNum = new BigDecimal(0.0);
            Integer scoreNum = 0;
            if (userScores != null && userScores.size() > 0) {
                for (SmartScore userScore : userScores) {
                    totalScoreNum = totalScoreNum.add(new BigDecimal(userScore.getScore()));
                    scoreNum++;
                }
            }

            if (evaStudent != null) {
                evaStudent.setScoreNum(totalScoreNum.doubleValue());
                evaStudent.setSubjectNum(scoreNum);
                int updateEva = smartEvaluateStudentService.updateSmartEvaluateStudent(evaStudent);
                if (updateEva <= 0) {
                    return CommonResult.fail("添加失败");
                }
            } else {
                //region 添加到学生综合素质评价表中
                SmartEvaluateStudent ses = new SmartEvaluateStudent();
                ses.setTermId(usr.getSemesterId());
                ses.setTerm(usr.getSemester());
                ses.setGradeName(usr.getGrade());
                ses.setGradeId(usr.getGradeId());
                ses.setClassId(usr.getSchoolClassId());
                ses.setClassName(usr.getSchoolClass());
                ses.setCardNo(usr.getCardNo());
                ses.setName(user.getName());
                ses.setUserId(usr.getUserId());
                ses.setScoreNum(totalScoreNum.doubleValue());
                ses.setSubjectNum(scoreNum);

                Integer insertStudent = smartEvaluateStudentService.insertSmartEvaluateStudent(ses);

                if (insertStudent <= 0) {
                    return CommonResult.fail("添加失败");
                }
                //endregion
            }
        }
        //endregion

        return CommonResult.ok("编辑成功");
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult querySmartScore() {
        return null;
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult queryPageSmartScores(int currentPage, int pageCount, String name) {
        return null;
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult deleteSmartScoreById(int id) {
        return null;
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult deleteSmartScoresByIds(deleteSmartScoreRequest dssr, BindingResult bindingResult) {
        List<SmartScore> scores = smartScoreService.querySmartScoreByIds(dssr.getIds());

        if (scores != null && scores.size() > 0) {

            int result = smartScoreService.deleteSmartScoreByIds(dssr.getIds());
            if (result <= 0) {
                return CommonResult.fail("删除失败");
            }

            //用户 学期 考试类型
            List<Integer> userIds = scores.stream().map(SmartScore::getUserId).collect(Collectors.toList());
            List<Integer> semesterIds = scores.stream().map(SmartScore::getSemesterId).collect(Collectors.toList());
            List<Integer> gradeIds = scores.stream().map(SmartScore::getGradeId).collect(Collectors.toList());
            List<SmartEvaluateStudent> students = smartEvaluateStudentService.getEvaluateStudents(userIds, semesterIds);
            List<SmartSubjectTemplate> subjectTemplates = smartSubjectTemplateService.queryTemplateByTermIds(semesterIds, gradeIds);
            List<Integer> deleteStudents = new ArrayList<>();
            List<SmartEvaluateStudent> updateStudents = new ArrayList<>();
            for (SmartScore score : scores) {
                Optional<SmartSubjectTemplate> oSubject = subjectTemplates.stream().filter(e -> e.getTermId().equals(score.getSemesterId()) && e.getGradeId().equals(score.getGradeId())).findFirst();
                if (oSubject != null && oSubject.isPresent()) {//有设置规则
                    List<String> examTypes = Arrays.asList(oSubject.get().getExamType().split(","));
                    if (examTypes.contains(score.getExamType())) {
                        Optional<SmartEvaluateStudent> oEStudent = students.stream().filter(e -> e.getUserId().equals(score.getUserId()) && e.getTermId().equals(score.getSemesterId())).findFirst();
                        BigDecimal restScore = new BigDecimal(0.0);
                        restScore = new BigDecimal(oEStudent.get().getScoreNum()).subtract(new BigDecimal(score.getScore()));
                        if (restScore.doubleValue() > 0) {
                            oEStudent.get().setScoreNum(restScore.doubleValue());
                            oEStudent.get().setSubjectNum(oEStudent.get().getSubjectNum() - 1);
                            updateStudents.add(oEStudent.get());
                        } else {
                            deleteStudents.add(oEStudent.get().getId());
                        }
                    }
                }
            }

            //删除和更新已有的综合评价数据
            if (updateStudents != null && updateStudents.size() > 0) {
                boolean updateResult = smartEvaluateStudentService.updateBatchById(students);
                if (!updateResult) {
                    return CommonResult.fail("删除失败");
                }
            }

            if (deleteStudents != null && deleteStudents.size() > 0) {
                int deleteStudentDatas = smartEvaluateStudentService.deleteEvaluateStudents(deleteStudents);
                if (deleteStudentDatas <= 0) {
                    return CommonResult.fail("删除失败");
                }
            }

        }

        return CommonResult.ok("删除成功");
    }

    @Override
    @DESRespondSecret(validated = true)
    public void smartScoreExport(HttpServletResponse response, String name, String semester, String grade, String schoolClass, String subject, String examType, Double MinScore, Double MaxScore) {

        Integer subjectInt = subject == null ? null : eSubjectStatu.integerOf(subject);
        Integer examTypeInt = examType == null ? null : eExamType.integerOf(examType);

        //获取成绩
        List<ScorePageVo> scores = smartScoreService.querySmartScores(name, semester, grade, schoolClass, subjectInt, examTypeInt, MinScore, MaxScore);

        //导出
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("成绩管理");
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("序号");
        headerRow.createCell(1).setCellValue("学期");
        headerRow.createCell(2).setCellValue("年级");
        headerRow.createCell(3).setCellValue("班级");
        headerRow.createCell(4).setCellValue("学科");
        headerRow.createCell(5).setCellValue("考试类型");
        headerRow.createCell(6).setCellValue("姓名");
        headerRow.createCell(7).setCellValue("学号");
        headerRow.createCell(8).setCellValue("成绩");

        for (int i = 0; i < scores.size(); i++) {
            ScorePageVo score = scores.get(i);

            Row dataRow = sheet.createRow(i + 1);
            dataRow.createCell(0).setCellValue(i + 1);
            dataRow.createCell(1).setCellValue(score.getSemester());
            dataRow.createCell(2).setCellValue(score.getGrade());
            dataRow.createCell(3).setCellValue(score.getSchoolClass());
            dataRow.createCell(4).setCellValue(eSubjectStatu.stringOf(score.getSubjectId()));
            dataRow.createCell(5).setCellValue(eExamType.stringOf(score.getExamType()));
            dataRow.createCell(6).setCellValue(score.getName());
            dataRow.createCell(7).setCellValue(score.getCardNo());
            dataRow.createCell(8).setCellValue(score.getScore());
        }

        // 将工作簿写入文件
        ExcelUtils.excelDownload(workbook, "成绩信息.xlsx", response);
    }

    @Override
    @DESRespondSecret(validated = false)
    public void testSmartUserExport(HttpServletResponse response) {
        //导出
        Workbook workbook = new XSSFWorkbook();
        CellStyle style = workbook.createCellStyle();//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);//设置边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        Sheet sheet = workbook.createSheet("成绩管理");
        Row headerRow = sheet.createRow(0);

        headerRow.createCell(0).setCellValue("序号");
        headerRow.getCell(0).setCellStyle(style);
        headerRow.createCell(1).setCellValue("学期");
        headerRow.getCell(1).setCellStyle(style);
        headerRow.createCell(2).setCellValue("年级");
        headerRow.getCell(2).setCellStyle(style);
        headerRow.createCell(3).setCellValue("班级");
        headerRow.getCell(3).setCellStyle(style);
        headerRow.createCell(4).setCellValue("学科");
        headerRow.getCell(4).setCellStyle(style);
        headerRow.getCell(5).setCellStyle(style);
        headerRow.createCell(6).setCellValue("姓名");
        headerRow.getCell(6).setCellStyle(style);
        headerRow.createCell(7).setCellValue("学号");
        headerRow.getCell(7).setCellStyle(style);
        headerRow.createCell(8).setCellStyle(style);//不设置合并的单元格则剩余单元格没有黑边框
        headerRow.createCell(9).setCellStyle(style);//不设置合并的单元格则剩余单元格没有黑边框
        headerRow.createCell(10).setCellValue("成绩");
        headerRow.getCell(10).setCellStyle(style);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 7, 9));//Row:行 Col:列   合并单元格

        // 将工作簿写入文件
        ExcelUtils.excelDownload(workbook, "成绩信息.xlsx", response);
    }

    @Override
    @DESRespondSecret(validated = false)
    public void smartUserExport(HttpServletResponse response, String name, String semester, String grade, String schoolClass, String subject, String examType, Double MinScore, Double MaxScore) {
        List<TimeGroupVo> subjects = smartSubjectService.queryGradeSubjects(grade);

        //导出
        Workbook workbook = new XSSFWorkbook();
        CellStyle style = workbook.createCellStyle();//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderBottom(BorderStyle.THIN);//设置边框
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        Sheet sheet = workbook.createSheet("总分");
        //参数说明：
        //ａ表示要冻结的列数；
        //ｂ表示要冻结的行数；
        //ｃ表示右边区域[可见]的首列序号；
        //ｄ表示下边区域[可见]的首行序号；
        sheet.createFreezePane(6, 3, 0, 0);

        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < (subjects.size() + 6); i++) {
            if (i == 0) {
                headerRow.createCell(i).setCellValue("排行榜");
            } else {
                headerRow.createCell(i).setCellValue("");
            }

            headerRow.getCell(i).setCellStyle(style);
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, subjects.size() + 5));//Row:行 Col:列   合并单元格 下标从0开始所以是+9

        Row headerRowTwo = sheet.createRow(1);
        headerRowTwo.createCell(0).setCellValue("姓名");
        headerRowTwo.getCell(0).setCellStyle(style);
        headerRowTwo.createCell(1).setCellValue("学号");
        headerRowTwo.getCell(1).setCellStyle(style);
        headerRowTwo.createCell(2).setCellValue("班级");
        headerRowTwo.getCell(2).setCellStyle(style);
        headerRowTwo.createCell(3).setCellValue("年级");
        headerRowTwo.getCell(3).setCellStyle(style);
        headerRowTwo.createCell(4).setCellValue("考试类型");
        headerRowTwo.getCell(4).setCellStyle(style);
        headerRowTwo.createCell(5).setCellValue("学期");
        headerRowTwo.getCell(5).setCellStyle(style);

        int num = 6;
        for (TimeGroupVo sub : subjects) {
            headerRowTwo.createCell(num).setCellValue(sub.getName());
            headerRowTwo.getCell(num).setCellStyle(style);
            ++num;
        }

        Row headerRowThree = sheet.createRow(2);
        for (int i = 0; i < 6; i++) {
            headerRowThree.createCell(i).setCellValue("");
            headerRowThree.getCell(i).setCellStyle(style);
            sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));//Row:行 Col:列   合并单元格
        }

        for (int i = 6; i < (subjects.size() + 6); i++) {
            headerRowThree.createCell(i).setCellValue("分数");
            headerRowThree.getCell(i).setCellStyle(style);
        }

        Integer subjectInt = null;
        if (subject != null && subjects != null) {
            Optional<TimeGroupVo> subejct = subjects.stream().filter(e -> e.getName().equals(subject)).findFirst();
            if (subejct != null && subejct.isPresent()) {
                subjectInt = subejct.get().getId();
            }
        }

        List<SmartExamtype> examtypes = smartExamtypeService.list(null);
        Integer examTypeInt = null;
        if (examType != null && examtypes != null) {
            Optional<SmartExamtype> et = examtypes.stream().filter(e -> e.getName().equals(examType)).findFirst();
            if (et != null && et.isPresent()) {
                examTypeInt = et.get().getId();
            }
        }

        List<SmartExamtype> exams = smartExamtypeService.list(null);
        int i = 3;
        //获取成绩
        List<ScorePageVo> scores = smartScoreService.querySmartScores(name, semester, grade, schoolClass, subjectInt, examTypeInt, MinScore, MaxScore);
        if (scores != null && scores.size() > 0) {
            List<String> semesters = scores.stream().map(ScorePageVo::getSemester).distinct().collect(Collectors.toList());
            for (String semStr : semesters) {
                List<ScorePageVo> scoreDatas = scores.stream().filter(e -> e.getSemester().equals(semStr)).collect(Collectors.toList());
                List<Integer> gradeIds = scoreDatas.stream().map(ScorePageVo::getGradeId).distinct().collect(Collectors.toList());
                for (Integer gradeId : gradeIds) {

                    if (scoreDatas != null && scoreDatas.size() > 0) {
                        List<Integer> userIds = scoreDatas.stream().map(ScorePageVo::getUserId).distinct().collect(Collectors.toList());

                        for (Integer userId : userIds) {
                            List<ScorePageVo> userScores = scoreDatas.stream().filter(e -> e.getGradeId().equals(gradeId) && e.getSemester().equals(semStr) && e.getUserId().equals(userId)).collect(Collectors.toList());
                            if (userScores != null && userScores.size() > 0) {
                                Row dataRow = sheet.createRow(i);
                                dataRow.createCell(0).setCellValue(userScores.get(0).getName());//姓名
                                dataRow.getCell(0).setCellStyle(style);
                                dataRow.createCell(1).setCellValue(userScores.get(0).getCardNo());//学号
                                dataRow.getCell(1).setCellStyle(style);
                                dataRow.createCell(2).setCellValue(userScores.get(0).getSchoolClass());//班级
                                dataRow.getCell(2).setCellStyle(style);
                                dataRow.createCell(3).setCellValue(userScores.get(0).getGrade());//年级
                                dataRow.getCell(3).setCellStyle(style);

                                String examStr = null;
                                if (exams != null && exams.size() > 0) {
                                    Optional<SmartExamtype> examtype = exams.stream().filter(e -> e.getGradeId().equals(gradeId) && e.getId().equals(userScores.get(0).getExamType())).findFirst();
                                    if (examtype != null && examtype.isPresent()) {
                                        examStr = examtype.get().getName();
                                    }
                                }
                                dataRow.createCell(4).setCellValue(examStr);//考试类型
                                dataRow.getCell(4).setCellStyle(style);
                                dataRow.createCell(5).setCellValue(userScores.get(0).getSemester());//学期
                                dataRow.getCell(5).setCellStyle(style);

                                for (int j = 6; j < subjects.size() + 6; j++) {
                                    String subjectStr = headerRowTwo.getCell(j).getStringCellValue();
                                    Optional<TimeGroupVo> subjectData = subjects.stream().filter(e -> e.getName().equals(subjectStr)).findFirst();
                                    if (subjectData != null && subjectData.isPresent()) {
                                        Optional<ScorePageVo> subjectScore = userScores.stream().filter(e -> e.getSubjectId().equals(subjectData.get().getId())).findFirst();
                                        if (subjectScore != null && subjectScore.isPresent()) {
                                            dataRow.createCell(j).setCellValue(subjectScore.get().getScore());//分数
                                            dataRow.getCell(j).setCellStyle(style);
                                        } else {
                                            dataRow.createCell(j).setCellStyle(style);
                                        }
                                    }
                                }
                                i++;
                            }
                        }
                    }
                }
            }
        }

        // 将工作簿写入文件
        ExcelUtils.excelDownload(workbook, "排行榜.xlsx", response);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult importSmartScoreExcel(MultipartFile file) throws IOException {
        if (file.isEmpty() || file.getSize() == 0) {
            return CommonResult.fail("导入文件不能为空");
        }

        String ContentType = file.getContentType();
        InputStream inputStream = file.getInputStream();
        List<SmartScore> result = new ArrayList<>();

        //xls格式文件
        if (ContentType.equals(eFileType.Xls.getValue())) {
            CommonResult<List<SmartScore>> resultData = readXls(inputStream);
            if (!resultData.isSuccess()) {
                return resultData;
            }
            result = resultData.getData();
        } else if (ContentType.equals(eFileType.Xlsx.getValue())) {
            CommonResult<List<SmartScore>> resultData = readXlsx(inputStream);
            if (!resultData.isSuccess()) {
                return resultData;
            }
            result = resultData.getData();
        } else {
            return CommonResult.fail("耗材数据导入只支持Xls、Xlsx");
        }

        boolean resultBool = smartScoreService.saveBatch(result);
        if (!resultBool) {
            CommonResult.fail("导入失败");
        }

        //region 循环成绩加进综合评价学生里
        List<SmartEvaluateStudent> updateStudents = new ArrayList<>();
        List<SmartEvaluateStudent> insertStudents = new ArrayList<>();
        if (result != null && result.size() > 0) {
            List<Integer> userIds = result.stream().map(SmartScore::getUserId).distinct().collect(Collectors.toList());
            List<Integer> semesterIds = result.stream().map(SmartScore::getSemesterId).distinct().collect(Collectors.toList());
            List<Integer> gradeIds = result.stream().map(SmartScore::getGradeId).distinct().collect(Collectors.toList());
            List<SmartEvaluateStudent> students = smartEvaluateStudentService.getEvaluateStudents(userIds, semesterIds);
            List<SmartSubjectTemplate> subjectTemplates = smartSubjectTemplateService.queryTemplateByTermIds(semesterIds, gradeIds);
            List<SmartScore> scores = smartScoreService.getScores(userIds, semesterIds);
            for (SmartSubjectTemplate sst : subjectTemplates) {
                List<String> examTypes = Arrays.asList(sst.getExamType().split(","));
                for (Integer userId : userIds) {
                    List<SmartScore> userScores = scores.stream().filter(e -> e.getUserId().equals(userId) && examTypes.contains(String.valueOf(e.getExamType()))).collect(Collectors.toList());
                    BigDecimal totalScoreNum = new BigDecimal(0.0);
                    Integer scoreNum = 0;
                    if (userScores != null && userScores.size() > 0) {
                        Optional<SmartEvaluateStudent> oStudent = students.stream().filter(e -> e.getTermId().equals(sst.getTermId()) && e.getUserId().equals(userId)).findFirst();
                        for (SmartScore userScore : userScores) {
                            totalScoreNum = totalScoreNum.add(new BigDecimal(userScore.getScore()));
                            scoreNum++;
                        }

                        if (oStudent != null && oStudent.isPresent()) {
                            oStudent.get().setScoreNum(totalScoreNum.doubleValue());
                            oStudent.get().setSubjectNum(scoreNum);
                            updateStudents.add(oStudent.get());
                        } else {
                            Optional<SmartScore> oSmartScore = result.stream().filter(e -> e.getSemesterId().equals(sst.getTermId()) && e.getUserId().equals(userId)).findFirst();
                            SmartEvaluateStudent ses = new SmartEvaluateStudent();
                            ses.setTermId(sst.getTermId());
                            ses.setUserId(userId);
                            if (oSmartScore != null && oSmartScore.isPresent()) {
                                ses.setTerm(oSmartScore.get().getSemester());
                                ses.setGradeName(oSmartScore.get().getGrade());
                                ses.setGradeId(oSmartScore.get().getGradeId());
                                ses.setClassId(oSmartScore.get().getSchoolClassId());
                                ses.setClassName(oSmartScore.get().getSchoolClass());
                                ses.setCardNo(oSmartScore.get().getCardNo());
                                ses.setName(oSmartScore.get().getName());
                            }
                            ses.setScoreNum(totalScoreNum.doubleValue());
                            ses.setSubjectNum(scoreNum);
                            insertStudents.add(ses);
                        }
                    }
                }
            }

            if (insertStudents != null && insertStudents.size() > 0) {
                boolean insertBatch = smartEvaluateStudentService.saveBatch(insertStudents);
                if (!insertBatch) {
                    return CommonResult.fail("导入失败");
                }
            }

            if (updateStudents != null && updateStudents.size() > 0) {
                boolean updateBatch = smartEvaluateStudentService.updateBatchById(updateStudents);
                if (!updateBatch) {
                    return CommonResult.fail("导入失败");
                }
            }
        }

        //endregion


        return CommonResult.ok("导入成功");
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult downloadScoreExcel() {
        return CommonResult.ok("200", "操作成功", "https://wanzai-1306339220.cos.ap-shanghai.myqcloud.com/excelModel/成绩信息表.xlsx");
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult averageScore(int userId, int semesterId) {
//        LocalDateTime now = LocalDateTime.now();
//        SmartSemester smartSemester = smartSemesterService.getSemester(now);
//        if (ObjectUtils.isEmpty(smartSemester)) {
//            return CommonResult.ok("当前时间不属于任何学期");
//        }
//
//        Integer semesterId = smartSemester.getId();
//        获取所有学科
        List<SmartSubject> list = smartSubjectService.list(new LambdaQueryWrapper<>());
        ArrayList<AverageScoreVo> averageScoreVos = new ArrayList<>();
        for (SmartSubject smartSubject : list) {
            AverageScoreVo averageScoreVo = new AverageScoreVo();
            Integer smartSemesterId = smartSubject.getId();
            Double score = smartScoreService.getAverageScore(userId, semesterId, smartSemesterId);
            averageScoreVo.setSubjectName(smartSubject.getName());
            if (ObjectUtils.isNotEmpty(score)) {
                averageScoreVo.setScore(score);
            } else {
                averageScoreVo.setScore(0.0);
            }

            averageScoreVos.add(averageScoreVo);
        }
        return CommonResult.ok(averageScoreVos);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult teacherMessage(String cardNo,String termId) {
//        LocalDateTime now = LocalDateTime.now();
//        SmartSemester smartSemester = smartSemesterService.getSemester(now);
//        if (ObjectUtils.isEmpty(smartSemester)) {
//            return CommonResult.ok("当前时间不属于任何学期");
//        }
//        String name = smartSemester.getName();
        SmartEvaluateStudent smartEvaluateStudent = smartEvaluateStudentService.getTeacherMessage(cardNo, termId);
        HashMap<String, String> map = new HashMap<>();
        if (ObjectUtils.isNotEmpty(smartEvaluateStudent)) {
            String scoreComment = smartEvaluateStudent.getScoreComment();
            map.put("teacherMessage", scoreComment);
        } else {
            map.put("teacherMessage", "暂无");
        }
        return CommonResult.ok(map);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult studentExamName(int userId) {
        List<StudentExamNameVo> vos = smartScoreService.getstudentExamName(userId);
        return CommonResult.ok(vos);
    }

    @Override
    @DESRespondSecret(validated = true)
    public CommonResult historicalExam(int userId, int semesterId, int examTypeId) {
        List<HistoricalExamVo> list = smartScoreService.getHistoricalExam(userId, semesterId, examTypeId);
        return CommonResult.ok(list);
    }

    @Override
    @DESRespondSecret(validated = false)
    public CommonResult teachingResultsExamName(int userId) {
        SmartUser smartUser = smartUserService.getSmartById(userId);
        Integer schoolClassId = smartUser.getSchoolClass();
        List<StudentExamNameVo> vos = smartScoreService.getTeachingResultsExamName(schoolClassId);
        return CommonResult.ok(vos);
    }

    @Override
    public CommonResult teachingResults(int schoolClassId, int semesterId, int examTypeId) {


        return null;
    }

    /**
     * 导入Xls文件方法
     *
     * @param inputStream 文件流
     * @return
     * @throws IOException
     */
    private CommonResult<List<SmartScore>> readXls(InputStream inputStream) throws IOException {
        HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
        List<SmartScore> result = new ArrayList<>();
        List<String> scoreNames = new ArrayList<>();
        //读取第一张sheet
        HSSFSheet sheetAt = sheets.getSheetAt(0);

        //获取成绩数据
        List<SmartScore> scores = smartScoreService.list(null);
        //获取学生数据
        List<SmartUser> users = smartUserService.queryStudentDatas();
        //获取年级数据
        List<SmartGrade> grades = smartGradeService.list(null);
        if (grades == null) {
            return CommonResult.fail("系统年级数据为空，导入失败");
        }
        //获取科目
        List<SmartSubject> subjects = smartSubjectService.list(null);
        if (subjects == null) {
            return CommonResult.fail("科目数据为空，导入失败");
        }
        //考试类型
        List<SmartExamtype> examtypes = smartExamtypeService.list(null);
        if (examtypes == null) {
            return CommonResult.fail("考试类型数据为空，导入失败");
        }
        //学期
        List<SmartSemester> semesters = smartSemesterService.list(null);
        if (semesters == null) {
            return CommonResult.fail("学期数据为空，导入失败");
        }
        //获取班级
        List<SmartClass> classes = smartClassService.list(null);
        if (classes == null) {
            return CommonResult.fail("系统班级数据为空，导入失败");
        }

        DataFormatter dataFormatter = new DataFormatter();

        try {
            HSSFRow titleRow = sheetAt.getRow(1);
            //sheetAt.getLastRowNum():xls从1开始统计数量 所以得+1
            for (int rowNum = 1; rowNum < sheetAt.getLastRowNum() + 1; rowNum++) {

                HSSFRow row = sheetAt.getRow(rowNum);
                //判断每一列单元格是否符合规则
                if (rowNum == 1) {
                    String name = dataFormatter.formatCellValue(titleRow.getCell(0));//姓名
                    if (!name.equals("姓名")) {
                        return CommonResult.fail("导入数据第一列为姓名");
                    }
                    String cardNo = dataFormatter.formatCellValue(titleRow.getCell(1));//学号
                    if (!cardNo.equals("学号")) {
                        return CommonResult.fail("导入数据第二列为学号");
                    }
                    String schoolClass = dataFormatter.formatCellValue(titleRow.getCell(2));//班级
                    if (!schoolClass.equals("班级")) {
                        return CommonResult.fail("导入数据第三列为班级");
                    }
                    String grade = dataFormatter.formatCellValue(titleRow.getCell(3));//年级
                    if (!grade.equals("年级")) {
                        return CommonResult.fail("导入数据第四列为年级");
                    }
                    String examType = dataFormatter.formatCellValue(titleRow.getCell(4));//考试类型
                    if (!examType.equals("考试类型")) {
                        return CommonResult.fail("导入数据第五列为考试类型");
                    }
                    String semester = dataFormatter.formatCellValue(titleRow.getCell(5));//学期
                    if (!semester.equals("学期")) {
                        return CommonResult.fail("导入数据第六列为学期");
                    }
                } else if (rowNum > 2) {

                    String name = dataFormatter.formatCellValue(row.getCell(0));//姓名

                    if (name.equals("")) {
                        continue;
                    }

                    String cardNo = dataFormatter.formatCellValue(row.getCell(1));//学号

                    Optional<SmartUser> user = users.stream().filter(e -> e.getCardNo().equals(cardNo)).findFirst();
                    if (!(user != null && user.isPresent())) {
                        return CommonResult.fail("当前系统中不存在" + name + "的学生信息，无法导入成绩");
                    }
                    Integer userId = user.get().getId();

                    String grade = dataFormatter.formatCellValue(row.getCell(3));//年级
                    Optional<SmartGrade> nowGrade = grades.stream().filter(e -> e.getName().equals(grade)).findFirst();
                    if (!(nowGrade != null && nowGrade.isPresent())) {
                        return CommonResult.fail(name + "的年级数据失效，导入失败");
                    }
                    Integer gradeId = nowGrade.get().getId();

                    String schoolClass = dataFormatter.formatCellValue(row.getCell(2));//班级
                    Optional<SmartClass> nowClass = classes.stream().filter(e -> e.getName().equals(schoolClass)).findFirst();
                    if (!(nowClass != null && nowClass.isPresent())) {
                        return CommonResult.fail(name + "的班级数据失效，导入失败");
                    }
                    Integer schoolClassId = nowClass.get().getId();

                    String examType = dataFormatter.formatCellValue(row.getCell(4));//考试类型
                    Optional<SmartExamtype> examtype = examtypes.stream().filter(e -> e.getGradeId().equals(gradeId) && e.getName().equals(examType)).findFirst();
                    if (!(examtype != null && examtype.isPresent())) {
                        return CommonResult.fail(name + "的考试类型数据失效，导入失败");
                    }
                    Integer examTypeId = examtype.get().getId();

                    String semesterStr = dataFormatter.formatCellValue(row.getCell(5));//学期
                    Optional<SmartSemester> semester = semesters.stream().filter(e -> e.getName().equals(semesterStr)).findFirst();
                    if (!(semester != null && semester.isPresent())) {
                        return CommonResult.fail(name + "的学期数据失效，导入失败");
                    }
                    Integer semesterId = semester.get().getId();

                    int sds = titleRow.getLastCellNum();
                    for (int i = 6; i < titleRow.getLastCellNum(); i++) {
                        String score = dataFormatter.formatCellValue(row.getCell(i));//成绩
                        if (StringUtils.hasText(score)) {
                            String subjectStr = dataFormatter.formatCellValue(titleRow.getCell(i));//科目
                            Optional<SmartSubject> subject = subjects.stream().filter(e -> e.getName().equals(subjectStr)).findFirst();
                            if (subject != null && subject.isPresent()) {
                                //数据重复性判断 学期 + 学号 + 科目
                                String existData = semester.get().getName() + " " + name + " " + cardNo + " " + subject.get().getName();
                                scoreNames.add(existData);
                                if (scoreNames.stream().distinct().count() != scoreNames.size()) {
                                    return CommonResult.fail("导入的Excel中的" + existData + "存在重复数据");
                                }
                                //重复数据提示
                                long exist = scores.stream().filter(e -> e.getSemester().equals(semester.get().getName()) && e.getCardNo().equals(cardNo) && e.getSubjectId().equals(subject.get().getId())).count();
                                if (exist > 0) {
                                    return CommonResult.fail(existData + "成绩数据在系统中已存在，请勿重复导入");
                                }
                                SmartScore data = new SmartScore();
                                data.setSemesterId(semesterId);
                                data.setSemester(semesterStr);
                                data.setGradeId(gradeId);
                                data.setGrade(grade);
                                data.setSchoolClassId(schoolClassId);
                                data.setSchoolClass(schoolClass);
                                data.setCardNo(cardNo);
                                data.setName(name);
                                data.setUserId(userId);
                                data.setSubjectId(subject.get().getId());
                                data.setExamType(examTypeId);
                                data.setScore(Double.valueOf(score));
                                result.add(data);
                            } else {
                                return CommonResult.fail(subjectStr + "科目数据在系统中不存在，导入失败");
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            return CommonResult.fail("请按模板格式导入数据");
        }

        return CommonResult.ok(result);
    }

    /**
     * 导入Xlsx文件方法
     *
     * @param inputStream 文件流
     * @return
     * @throws IOException
     */
    private CommonResult<List<SmartScore>> readXlsx(InputStream inputStream) throws IOException {
        XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
        List<SmartScore> result = new ArrayList<>();
        List<String> scoreNames = new ArrayList<>();
        //读取第一张sheet
        XSSFSheet sheetAt = sheets.getSheetAt(0);

        //获取成绩数据
        List<SmartScore> scores = smartScoreService.list(null);
        //获取学生数据
        List<SmartUser> users = smartUserService.queryStudentDatas();
        //获取年级数据
        List<SmartGrade> grades = smartGradeService.list(null);
        if (grades == null) {
            return CommonResult.fail("系统年级数据为空，导入失败");
        }
        //获取科目
        List<SmartSubject> subjects = smartSubjectService.list(null);
        if (subjects == null) {
            return CommonResult.fail("科目数据为空，导入失败");
        }
        //考试类型
        List<SmartExamtype> examtypes = smartExamtypeService.list(null);
        if (examtypes == null) {
            return CommonResult.fail("考试类型数据为空，导入失败");
        }
        //学期
        List<SmartSemester> semesters = smartSemesterService.list(null);
        if (semesters == null) {
            return CommonResult.fail("学期数据为空，导入失败");
        }
        //获取班级
        List<SmartClass> classes = smartClassService.list(null);
        if (classes == null) {
            return CommonResult.fail("系统班级数据为空，导入失败");
        }

        DataFormatter dataFormatter = new DataFormatter();

        try {
            XSSFRow titleRow = sheetAt.getRow(1);
            //sheetAt.getLastRowNum()：从0开始统计数量 所以得+1
            for (int rowNum = 1; rowNum < sheetAt.getLastRowNum() + 1; rowNum++) {

                XSSFRow row = sheetAt.getRow(rowNum);

                //判断每一列单元格是否符合规则
                if (rowNum == 1) {
                    String name = dataFormatter.formatCellValue(titleRow.getCell(0));//姓名
                    if (!name.equals("姓名")) {
                        return CommonResult.fail("导入数据第一列为姓名");
                    }
                    String cardNo = dataFormatter.formatCellValue(titleRow.getCell(1));//学号
                    if (!cardNo.equals("学号")) {
                        return CommonResult.fail("导入数据第二列为学号");
                    }
                    String schoolClass = dataFormatter.formatCellValue(titleRow.getCell(2));//班级
                    if (!schoolClass.equals("班级")) {
                        return CommonResult.fail("导入数据第三列为班级");
                    }
                    String grade = dataFormatter.formatCellValue(titleRow.getCell(3));//年级
                    if (!grade.equals("年级")) {
                        return CommonResult.fail("导入数据第四列为年级");
                    }
                    String examType = dataFormatter.formatCellValue(titleRow.getCell(4));//考试类型
                    if (!examType.equals("考试类型")) {
                        return CommonResult.fail("导入数据第五列为考试类型");
                    }
                    String semester = dataFormatter.formatCellValue(titleRow.getCell(5));//学期
                    if (!semester.equals("学期")) {
                        return CommonResult.fail("导入数据第六列为学期");
                    }
                } else if (rowNum > 2) {

                    String name = dataFormatter.formatCellValue(row.getCell(0));//姓名

                    if (name.equals("")) {
                        continue;
                    }
                    String cardNo = dataFormatter.formatCellValue(row.getCell(1));//学号

                    Optional<SmartUser> user = users.stream().filter(e -> e.getCardNo().equals(cardNo)).findFirst();
                    if (!(user != null && user.isPresent())) {
                        return CommonResult.fail("当前系统中不存在" + name + "的学生信息，无法导入成绩");
                    }
                    Integer userId = user.get().getId();

                    String grade = dataFormatter.formatCellValue(row.getCell(3));//年级
                    Optional<SmartGrade> nowGrade = grades.stream().filter(e -> e.getName().equals(grade)).findFirst();
                    if (!(nowGrade != null && nowGrade.isPresent())) {
                        return CommonResult.fail(name + "的年级数据失效，导入失败");
                    }
                    Integer gradeId = nowGrade.get().getId();

                    String schoolClass = dataFormatter.formatCellValue(row.getCell(2));//班级
                    Optional<SmartClass> nowClass = classes.stream().filter(e -> e.getName().equals(schoolClass)).findFirst();
                    if (!(nowClass != null && nowClass.isPresent())) {
                        return CommonResult.fail(name + "的班级数据失效，导入失败");
                    }
                    Integer schoolClassId = nowClass.get().getId();

                    String examType = dataFormatter.formatCellValue(row.getCell(4));//考试类型
                    Optional<SmartExamtype> examtype = examtypes.stream().filter(e -> e.getGradeId().equals(gradeId) && e.getName().equals(examType)).findFirst();
                    if (!(examtype != null && examtype.isPresent())) {
                        return CommonResult.fail(name + "的考试类型数据失效，导入失败");
                    }
                    Integer examTypeId = examtype.get().getId();

                    String semesterStr = dataFormatter.formatCellValue(row.getCell(5));//学期
                    Optional<SmartSemester> semester = semesters.stream().filter(e -> e.getName().equals(semesterStr)).findFirst();
                    if (!(semester != null && semester.isPresent())) {
                        return CommonResult.fail(name + "的学期数据失效，导入失败");
                    }
                    Integer semesterId = semester.get().getId();

                    int sds = titleRow.getLastCellNum();
                    for (int i = 6; i < titleRow.getLastCellNum(); i++) {
                        String score = dataFormatter.formatCellValue(row.getCell(i));//成绩
                        if (StringUtils.hasText(score)) {
                            String subjectStr = dataFormatter.formatCellValue(titleRow.getCell(i));//科目
                            Optional<SmartSubject> subject = subjects.stream().filter(e -> e.getName().equals(subjectStr)).findFirst();
                            if (subject != null && subject.isPresent()) {
                                //数据重复性判断 学期 + 学号 + 科目
                                String existData = semester.get().getName() + " " + name + " " + cardNo + " " + subject.get().getName();
                                scoreNames.add(existData);
                                if (scoreNames.stream().distinct().count() != scoreNames.size()) {
                                    return CommonResult.fail("导入的Excel中的" + existData + "存在重复数据");
                                }
                                //重复数据提示
                                long exist = scores.stream().filter(e -> e.getSemester().equals(semester.get().getName()) && e.getCardNo().equals(cardNo) && e.getSubjectId().equals(subject.get().getId())).count();
                                if (exist > 0) {
                                    return CommonResult.fail(existData + "成绩数据在系统中已存在，请勿重复导入");
                                }
                                SmartScore data = new SmartScore();
                                data.setSemesterId(semesterId);
                                data.setSemester(semesterStr);
                                data.setGradeId(gradeId);
                                data.setGrade(grade);
                                data.setSchoolClassId(schoolClassId);
                                data.setSchoolClass(schoolClass);
                                data.setCardNo(cardNo);
                                data.setName(name);
                                data.setUserId(userId);
                                data.setSubjectId(subject.get().getId());
                                data.setExamType(examTypeId);
                                data.setScore(Double.valueOf(score));
                                result.add(data);
                            } else {
                                return CommonResult.fail(subjectStr + "科目数据在系统中不存在，导入失败");
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            return CommonResult.fail("请按模板格式导入数据");
        }

        return CommonResult.ok(result);
    }
}

