api/src/main/java/com/xjrsoft/module/student/service/impl/BaseStudentAssessmentInspectionServiceImpl.java

package com.xjrsoft.module.student.service.impl;

import cn.dev33.satoken.stp.StpUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.yulichang.base.MPJBaseServiceImpl;
import com.github.yulichang.toolkit.MPJWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.xjrsoft.common.enums.*;
import com.xjrsoft.common.exception.MyException;
import com.xjrsoft.common.model.result.RT;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.common.utils.excel.ExcelMergeUtil;
import com.xjrsoft.config.CommonPropertiesConfig;
import com.xjrsoft.module.base.entity.BaseClass;
import com.xjrsoft.module.base.mapper.BaseClassMapper;
import com.xjrsoft.module.evaluate.dto.TeaEvaluateClassDto;
import com.xjrsoft.module.evaluate.mapper.EvaluateResultMapper;
import com.xjrsoft.module.evaluate.vo.TeaEvaluateClassListVo;
import com.xjrsoft.module.organization.dto.WeChatSendMessageDto;
import com.xjrsoft.module.organization.entity.User;
import com.xjrsoft.module.organization.service.IUserService;
import com.xjrsoft.module.organization.service.IWeChatService;
import com.xjrsoft.module.student.dto.*;
import com.xjrsoft.module.student.entity.*;
import com.xjrsoft.module.student.mapper.*;
import com.xjrsoft.module.student.service.IBaseStudentAssessmentInspectionService;
import com.xjrsoft.module.student.service.IBaseStudentService;
import com.xjrsoft.module.student.service.IQuotaFormulaRuleService;
import com.xjrsoft.module.student.vo.*;
import com.xjrsoft.module.system.entity.File;
import com.xjrsoft.module.system.service.IFileService;
import com.xjrsoft.module.teacher.entity.XjrUser;
import lombok.AllArgsConstructor;
import me.zhyd.oauth.log.Log;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.stream.Collectors;


/**
 * @title: 学生班级巡查考核
 * @Author dzx
 * @Date: 2023-11-16
 * @Version 1.0
 */
@Service
@AllArgsConstructor
public class BaseStudentAssessmentInspectionServiceImpl extends MPJBaseServiceImpl<BaseStudentAssessmentInspectionMapper, BaseStudentAssessmentInspection> implements IBaseStudentAssessmentInspectionService {

    private final BaseStudentAssessmentInspectionMapper assessmentInspectionMapper;

    private final BaseStudentAssessmentCategoryMapper categoryMapper;

    private final BaseStudentAssessmentProjectMapper projectMapper;

    private final BaseStudentAssessmentStudentRelationMapper assessmentStudentRelationMapper;

    private final BaseStudentAssessmentClassRelationMapper assessmentClassRelationMapper;

    private final BaseClassMapper baseClassMapper;

    private final BaseStudentSchoolRollMapper baseStudentSchoolRollMapper;

    private final IFileService fileService;

    private final IUserService userService;

    private final IWeChatService weChatService;

    private final EvaluateResultMapper evaluateResultMapper;

    private final IQuotaFormulaRuleService quotaFormulaRuleService;

    private final CommonPropertiesConfig commonPropertiesConfig;

    private final IBaseStudentService studentService;

    private final BaseStudentAssessmentItemMapper itemMapper;

    @Override
    public Page<BaseStudentAssessmentInspectionPageVo> getPage(Page<BaseStudentAssessmentInspectionPageDto> page, BaseStudentAssessmentInspectionPageDto dto) {
        Page<BaseStudentAssessmentInspectionPageVo> result = assessmentInspectionMapper.getPage(page, dto);
        // 处理加减分
        result.getRecords().forEach((node) -> {
            if (node.getScoreType().equals(ScoreTypeEnum.ScoreMinus.getCode())) {
                node.setScore(node.getScore() * -1);
            }
        });
        return result;
    }

    @Override
    public BaseStudentAssessmentInspectionVo getInfo(Long id) {
        BaseStudentAssessmentInspectionVo result = assessmentInspectionMapper.getInfo(id);
        if (result == null) {
            throw new MyException("该记录已被作废");
        }
        // 获取班级信息
        List<BaseStudentAssessmentClassListVo> baseStudentAssessmentClassListVos = assessmentClassRelationMapper.selectJoinList(BaseStudentAssessmentClassListVo.class,
                MPJWrappers.<BaseStudentAssessmentClassRelation>lambdaJoin()
                        .leftJoin(BaseClass.class, BaseClass::getId, BaseStudentAssessmentClassRelation::getClassId)
                        .leftJoin(XjrUser.class, XjrUser::getId, BaseClass::getTeacherId)
                        .eq(BaseStudentAssessmentClassRelation::getBaseStudentAssessmentInspectionId, id)
                        .select(BaseStudentAssessmentClassRelation::getId)
                        .selectAs(BaseClass::getName, BaseStudentAssessmentClassListVo::getClassName)
                        .selectAs(XjrUser::getName, BaseStudentAssessmentClassListVo::getTeacherName)
                        .selectAs(XjrUser::getMobile, BaseStudentAssessmentClassListVo::getMobile)
                        .select(BaseStudentAssessmentClassRelation.class, x -> VoToColumnUtil.fieldsToColumns(BaseStudentAssessmentClassRelation.class).contains(x.getProperty()))
        );
        result.setClassList(baseStudentAssessmentClassListVos);

        // 获取学生信息
        result.setStudentList(assessmentInspectionMapper.getStudentListByInspectionId(id));

        // 获取文件列表
        result.setFileInfos(fileService.list(Wrappers.<File>query().lambda().eq(File::getFolderId, result.getFileId())));

        return result;
    }

    @Override
    public Page<BaseStudentAssessmentInspectionMobilePageVo> getMobilePage(Page<BaseStudentAssessmentInspectionMobilePageDto> page, BaseStudentAssessmentInspectionMobilePageDto dto) {
        if (dto.getClassIds() == null || dto.getClassIds().isEmpty()) {
            // 获取当前用户所管班级
            List<Long> classIds = baseClassMapper.selectList(
                    Wrappers.<BaseClass>query().lambda()
                            .eq(BaseClass::getTeacherId, StpUtil.getLoginIdAsLong())
                            .select(BaseClass::getId)
            ).stream().map(BaseClass::getId).collect(Collectors.toList());
            dto.setClassIds(classIds);
        }
        if (dto.getClassIds() == null || dto.getClassIds().isEmpty()) {
            return null;
        }
        return assessmentInspectionMapper.getMobilePage(page, dto);
    }

    @Override
    public BaseStudentAssessmentInspectionMobileVo getMobileInfo(Long id) {
        BaseStudentAssessmentInspectionMobileVo result = assessmentInspectionMapper.getMobileInfo(id);
        // 处理加减分
        if (result.getScoreType().equals(ScoreTypeEnum.ScoreMinus.getCode())) {
            result.setScore(result.getScore() * -1);
        }

        MPJLambdaWrapper<BaseStudentAssessmentStudentRelation> baseStudentAssessmentStudentRelationWrapper = new MPJLambdaWrapper<>();
        baseStudentAssessmentStudentRelationWrapper
                .select(BaseStudentAssessmentStudentRelation::getId)
                .selectAs(XjrUser::getName, BaseStudentAssessmentStudentRelation::getName)
                .selectAs(XjrUser::getCredentialNumber, BaseStudentAssessmentStudentRelation::getStudentId)
                .eq(BaseStudentAssessmentStudentRelation::getBaseStudentAssessmentInspectionId, result.getBaseStudentAssessmentInspectionId())
                .eq(BaseStudentAssessmentStudentRelation::getClassId, result.getClassId())
                .leftJoin(XjrUser.class, XjrUser::getId, BaseStudentAssessmentStudentRelation::getUserId)
                .eq(BaseStudentAssessmentStudentRelation::getDeleteMark, DeleteMark.NODELETE.getCode())
                .eq(BaseStudentAssessmentStudentRelation::getEnabledMark, EnabledMark.ENABLED.getCode())
        ;
        // 获取学生信息
        List<BaseStudentAssessmentStudentRelation> baseStudentAssessmentStudentRelations = assessmentStudentRelationMapper.selectList(baseStudentAssessmentStudentRelationWrapper);
        result.setStudentList(baseStudentAssessmentStudentRelations);
        result.setStudentCount(baseStudentAssessmentStudentRelations.size());

        // 获取文件列表
        result.setFileInfos(fileService.list(Wrappers.<File>query().lambda().eq(File::getFolderId, result.getFileId())));

        return result;
    }

    @Override
    public ResponseEntity<byte[]> getQuantitativeAssessmentExcelByte(QuantitativeAssessmentExcelDto dto) {
        //导出的文件名，字节流
        String fileName = "StudentAssessmentInspection" + ExcelTypeEnum.XLSX.getValue();
        ByteArrayOutputStream bot = new ByteArrayOutputStream();

        //获取需要导出的子表
        List<Long> baseStudentAssessmentCategoryIdList = new ArrayList<>();
        baseStudentAssessmentCategoryIdList.add(1768178717588197377L);
        baseStudentAssessmentCategoryIdList.add(1768179375900987394L);
        baseStudentAssessmentCategoryIdList.add(1769927649167151105L);

        LambdaQueryWrapper<BaseStudentAssessmentCategory> baseStudentAssessmentCategoryLambdaQueryWrapper = new LambdaQueryWrapper<>();
        baseStudentAssessmentCategoryLambdaQueryWrapper
                .in(BaseStudentAssessmentCategory::getId, baseStudentAssessmentCategoryIdList);
        List<BaseStudentAssessmentCategory> baseStudentAssessmentCategorieList = categoryMapper.selectList(baseStudentAssessmentCategoryLambdaQueryWrapper);

        //导出子表
        if (!baseStudentAssessmentCategorieList.isEmpty()) {
            //需要合并的列
            int[] mergeColumeIndex = {0, 1, 2, 3, 4};
            ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(1, mergeColumeIndex);

            //开始处理写入
            try (ExcelWriter excelWriter = EasyExcel.write(bot, QuantitativeAssessmentSubTableExcelVo.class).registerWriteHandler(excelFillCellMergeStrategy).build()) {
                int no = 1;
                for (BaseStudentAssessmentCategory bsac : baseStudentAssessmentCategorieList) {
                    dto.setBaseStudentAssessmentCategoryId(bsac.getId());
                    //sheet名
                    String sheetName = bsac.getName();

                    WriteSheet writeSheet = EasyExcel.writerSheet(no++, sheetName).build();

                    //求所有的个人行为集合
                    List<QuantitativeAssessmentSubTableExcelVo> quantitativeAssessmentSubTableExcelVoList = assessmentInspectionMapper.getQuantitativeAssessmentSubTableExcelVoList(dto);

                    excelWriter.write(quantitativeAssessmentSubTableExcelVoList, writeSheet);
                }
                //TODO 班级量化考核统计表总
                //以班级为数据的基础进行数据的填充
                MPJLambdaWrapper<BaseClass> baseClassMPJLambdaWrapper = new MPJLambdaWrapper<>();
                baseClassMPJLambdaWrapper
                        .selectAs(BaseClass::getTeacherId, CalssQuantitativeAssessmentPageVo::getHeadTeacherId)
                        .selectAs(XjrUser::getUserName, CalssQuantitativeAssessmentPageVo::getOa)
                        .selectAs(XjrUser::getName, CalssQuantitativeAssessmentPageVo::getHeadTeacherName)
                        .selectAs(BaseClass::getId, CalssQuantitativeAssessmentPageVo::getClassId)
                        .selectAs(BaseClass::getName, CalssQuantitativeAssessmentPageVo::getClassName)
                        .select("(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1" +
                                " LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id" +
                                " WHERE a1.delete_mark = 0 AND a2.delete_mark = 0" +
                                " AND a2.class_id = t1.id" +
                                " AND a2.archives_status = 'FB2901') as student_num")
                        .selectAs(BaseClass::getIsGraduate, CalssQuantitativeAssessmentPageVo::getClassStatus)
                        .leftJoin(XjrUser.class, XjrUser::getId, BaseClass::getTeacherId)
                        .eq(dto.getGradeId() != null, BaseClass::getGradeId, dto.getGradeId())
                        .eq(dto.getClassId() != null, BaseClass::getId, dto.getClassId())
                        .eq(dto.getClassStatus() != null, BaseClass::getIsGraduate, dto.getClassStatus())
                        .like(dto.getName() != null && !dto.getName().isEmpty(), XjrUser::getName, dto.getName());

                List<CalssQuantitativeAssessmentPageVo> resultList = baseClassMapper.selectJoinList(CalssQuantitativeAssessmentPageVo.class, baseClassMPJLambdaWrapper);

                //当前数据记录班级id
                List<Long> classIdList = new ArrayList<>();
                for (CalssQuantitativeAssessmentPageVo c : resultList) {
                    classIdList.add(Long.parseLong(c.getClassId()));
                }
                dto.setClassIdList(classIdList);

                if (dto.getStartTime() != null) {
                    // 获取上个月的最后一秒
                    LocalDateTime lastMonthEnd = dto.getStartTime().minusMonths(1).with(TemporalAdjusters.lastDayOfMonth()).withHour(23).withMinute(59).withSecond(59);

                    // 获取下个月的第一秒
                    LocalDateTime nextMonthStart = dto.getStartTime().plusMonths(1).with(TemporalAdjusters.firstDayOfMonth()).withHour(0).withMinute(0).withSecond(0);
                    dto.setStartTime(lastMonthEnd);
                    dto.setEndTime(nextMonthStart);
                }

                if (!classIdList.isEmpty()) {
                    CalssQuantitativeAssessmentPageDto calssQuantitativeAssessmentPageDto = new CalssQuantitativeAssessmentPageDto();
                    BeanUtils.copyProperties(dto, calssQuantitativeAssessmentPageDto);

                    //学生个人行为
                    dto.setBaseStudentAssessmentCategoryId(1782327704603373570L);
                    Map<Long, QuantitativeAssessmentSingleScoreVo> individualBehaviorMap = getQuantitativeAssessmentSingleScoreMap(calssQuantitativeAssessmentPageDto);

                    //常规管理
                    dto.setBaseStudentAssessmentCategoryId(1782329720935329794L);
                    Map<Long, QuantitativeAssessmentSingleScoreVo> conventionalManagementMap = getQuantitativeAssessmentSingleScoreMap(calssQuantitativeAssessmentPageDto);

                    //获奖及荣誉奖彰
                    dto.setBaseStudentAssessmentCategoryId(1787316203484131329L);
                    Map<Long, QuantitativeAssessmentSingleScoreVo> awardsAndHonorableMap = getQuantitativeAssessmentSingleScoreMap(calssQuantitativeAssessmentPageDto);

                    //准军事化考核
                    dto.setBaseStudentAssessmentCategoryId(1868835208799236098L);
                    Map<Long, QuantitativeAssessmentSingleScoreVo> paramilitaryMap = getQuantitativeAssessmentSingleScoreMap(calssQuantitativeAssessmentPageDto);


                    //查询任课教师查询班级的数据
                    TeaEvaluateClassDto evaluateClassDto = new TeaEvaluateClassDto();
                    evaluateClassDto.setEvaluateType(EvaluateTypeEnum.TEACHER_EVALUATE_CLASS.getCode());
                    evaluateClassDto.setYear(dto.getYear());
                    LocalDate now = LocalDate.now();
                    if (dto.getYear() == null) {
                        evaluateClassDto.setYear(now.getYear());
                    }
                    evaluateClassDto.setMonth(dto.getMonth());
                    if (dto.getMonth() == null) {
                        evaluateClassDto.setMonth(now.getMonthValue());
                    }
                    List<TeaEvaluateClassListVo> teaEvaluateClassList = evaluateResultMapper.getTeaEvaluateClassList(evaluateClassDto);
                    Map<Long, List<TeaEvaluateClassListVo>> classEvaluateMap = teaEvaluateClassList.stream().collect(Collectors.groupingBy(TeaEvaluateClassListVo::getEvaluatedObjectId));
                    String jskhFormula = quotaFormulaRuleService.getFormulaByQuota("teacher_assessment");

                    //将结果写进对应的班级里
                    for (CalssQuantitativeAssessmentPageVo c : resultList) {
                        if (c.getOa() == null) {
                            c.setOa(" ");
                        }
                        if (c.getHeadTeacherName() == null) {
                            c.setHeadTeacherName(" ");
                        }
                        Long classId = Long.parseLong(c.getClassId());
                        c.setStuPersonalBehaviorSubScore(" ");
                        c.setStuPersonalBehaviorScore(" ");
                        if (individualBehaviorMap.get(classId) != null) {
                            Double sumScore = individualBehaviorMap.get(classId).getSumScore();
                            if (sumScore != null) {
                                c.setStuPersonalBehaviorSubScore(sumScore.toString());
                                double v = (100 + sumScore) / 100 * 25;
                                c.setStuPersonalBehaviorScore(v + "");
                            }
                        }

                        c.setClassConventionalManageSubScore(" ");
                        c.setClassConventionalManageScore(" ");
                        if (conventionalManagementMap.get(classId) != null) {
                            Double sumScore = conventionalManagementMap.get(classId).getSumScore();
                            if (sumScore != null) {
                                c.setClassConventionalManageSubScore(sumScore.toString());
                                double v = (100 + sumScore) / 100 * 25;
                                c.setClassConventionalManageScore(v + "");
                            }
                        }

                        c.setHonorSumScore(" ");
                        c.setHonorScore(" ");
                        if (awardsAndHonorableMap.get(classId) != null) {
                            Double sumScore = awardsAndHonorableMap.get(classId).getSumScore();
                            if (sumScore != null) {
                                c.setHonorSumScore(sumScore.toString());
                                if (sumScore < 10) {
                                    c.setHonorScore(sumScore.toString());
                                } else {
                                    c.setHonorScore("10");
                                }
                            }
                        }

                        if (c.getClassStatus() != null) {
                            if (c.getClassStatus() == 1) {
                                c.setClassStatusCn("在读");
                            }
                            if (c.getClassStatus() == 2) {
                                c.setClassStatusCn("毕业");
                            }
                        }

                        c.setTeacherAssessComprehensiveIndex(" ");
                        c.setTeacherAssessScore(" ");
                        if (classEvaluateMap.containsKey(classId)) {
                            Map<Long, List<TeaEvaluateClassListVo>> evaluateCategoryMap = classEvaluateMap.get(classId).stream().collect(Collectors.groupingBy(TeaEvaluateClassListVo::getId));
                            int size = evaluateCategoryMap.size();

                            List<Double> evaluateSocreList = new ArrayList<>();//存每个项的平均分
                            for (Long id : evaluateCategoryMap.keySet()) {
                                List<TeaEvaluateClassListVo> evaluateList = evaluateCategoryMap.get(id);
                                int allSorce = evaluateList.stream().mapToInt(TeaEvaluateClassListVo::getScore).sum();
                                double avgScore = BigDecimal.valueOf(allSorce).divide(BigDecimal.valueOf(resultList.size()), 2, RoundingMode.HALF_UP).doubleValue();
                                evaluateSocreList.add(avgScore);
                            }

                            double sum = evaluateSocreList.stream().mapToDouble(Double::doubleValue).sum();
                            double jskhValue = BigDecimal.valueOf(sum).divide(BigDecimal.valueOf(size), 2, RoundingMode.HALF_UP).doubleValue();
                            c.setTeacherAssessComprehensiveIndex(jskhValue + "");
                            String replacedExpression = jskhFormula.replace("JSKH", Double.toString(jskhValue));

                            ScriptEngineManager manager = new ScriptEngineManager();
                            ScriptEngine engine = manager.getEngineByName("JavaScript");
                            try {
                                double result = ((Number) engine.eval(replacedExpression)).doubleValue();
                                c.setTeacherAssessScore(result + " ");
                            } catch (ScriptException e) {
                                Log.error(e.getMessage(), e);
                            }
                        }
                        if (paramilitaryMap.get(classId) != null) {
                            Double sumScore = awardsAndHonorableMap.get(classId).getSumScore();
                            if (sumScore != null) {
                                c.setMilitaryManageScore(sumScore.toString());
                                if (sumScore < 10) {
                                    c.setHonorScore(sumScore.toString());
                                } else {
                                    c.setHonorScore("10");
                                }

                                double v = (100 + sumScore) / 100 * 25;
                                c.setMilitaryManageComprehensiveIndex(v + "");
                            }
                        }

                        c.setAssessMoney(" ");
                        c.setAssessSumScore(" ");
                    }
                }
                //sheet名
                String sheetName = "量化考核总表";

                WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).build();
//
                excelWriter.write(resultList, writeSheet);
            }
            /*for (BaseStudentAssessmentCategory bsac : baseStudentAssessmentCategorieList) {
                dto.setBaseStudentAssessmentCategoryId(bsac.getId());
                //sheet名
                String sheetName = bsac.getName();

                //求所有的个人行为集合
                List<QuantitativeAssessmentSubTableExcelVo> quantitativeAssessmentSubTableExcelVoList = assessmentInspectionMapper.getQuantitativeAssessmentSubTableExcelVoList(dto);

                //需要合并的列
                int[] mergeColumeIndex = {0, 1, 2, 3, 4};

                //写字节流
                ExcelMergeUtil excelFillCellMergeStrategy = new ExcelMergeUtil(1, mergeColumeIndex);
                EasyExcel.write(bot, QuantitativeAssessmentSubTableExcelVo.class)
                        .registerWriteHandler(excelFillCellMergeStrategy)
                        .sheet(sheetName)
                        .doWrite(quantitativeAssessmentSubTableExcelVoList);
            }*/
        }

        return RT.fileStream(bot.toByteArray(), fileName);
    }

    @Override
    public IPage<CalssQuantitativeAssessmentPageVo> getCalssQuantitativeAssessmentPage(Page<CalssQuantitativeAssessmentPageDto> page, CalssQuantitativeAssessmentPageDto dto) {
        //以班级为数据的基础进行数据的填充
        MPJLambdaWrapper<BaseClass> baseClassMPJLambdaWrapper = new MPJLambdaWrapper<>();
        baseClassMPJLambdaWrapper
                .selectAs(BaseClass::getTeacherId, CalssQuantitativeAssessmentPageVo::getHeadTeacherId)
                .selectAs(XjrUser::getUserName, CalssQuantitativeAssessmentPageVo::getOa)
                .selectAs(XjrUser::getName, CalssQuantitativeAssessmentPageVo::getHeadTeacherName)
                .selectAs(BaseClass::getId, CalssQuantitativeAssessmentPageVo::getClassId)
                .selectAs(BaseClass::getName, CalssQuantitativeAssessmentPageVo::getClassName)
                .select("(SELECT COUNT(DISTINCT(a1.id)) FROM xjr_user a1" +
                        " LEFT JOIN base_student_school_roll a2 ON a1.id = a2.user_id" +
                        " WHERE a1.delete_mark = 0 AND a2.delete_mark = 0" +
                        " AND a2.class_id = t1.id" +
                        " AND a2.archives_status = 'FB2901') as student_num")
                .selectAs(BaseClass::getIsGraduate, CalssQuantitativeAssessmentPageVo::getClassStatus)
                .leftJoin(XjrUser.class, XjrUser::getId, BaseClass::getTeacherId)
                .eq(dto.getGradeId() != null, BaseClass::getGradeId, dto.getGradeId())
                .eq(dto.getClassId() != null, BaseClass::getId, dto.getClassId())
                .eq(dto.getClassStatus() != null, BaseClass::getIsGraduate, dto.getClassStatus())
                .like(dto.getName() != null && !dto.getName().isEmpty(), XjrUser::getName, dto.getName());

        IPage<CalssQuantitativeAssessmentPageVo> resultPage = baseClassMapper.selectJoinPage(page, CalssQuantitativeAssessmentPageVo.class, baseClassMPJLambdaWrapper);

        //当前数据记录班级id
        List<Long> classIdList = new ArrayList<>();
        for (CalssQuantitativeAssessmentPageVo c : resultPage.getRecords()) {
            classIdList.add(Long.parseLong(c.getClassId()));
        }
        dto.setClassIdList(classIdList);

        if (dto.getStartTime() != null) {
            // 获取上个月的最后一秒
            LocalDateTime lastMonthEnd = dto.getStartTime().minusMonths(1).with(TemporalAdjusters.lastDayOfMonth()).withHour(23).withMinute(59).withSecond(59);

            // 获取下个月的第一秒
            LocalDateTime nextMonthStart = dto.getStartTime().plusMonths(1).with(TemporalAdjusters.firstDayOfMonth()).withHour(0).withMinute(0).withSecond(0);
            dto.setStartTime(lastMonthEnd);
            dto.setEndTime(nextMonthStart);
        }

        if (!classIdList.isEmpty()) {
            //学生个人行为
            dto.setCategoryCode("student_behavior");
            Map<Long, QuantitativeAssessmentSingleScoreVo> individualBehaviorMap = getQuantitativeAssessmentSingleScoreMap(dto);

            //常规管理
            dto.setCategoryCode("convention_manage");
            Map<Long, QuantitativeAssessmentSingleScoreVo> conventionalManagementMap = getQuantitativeAssessmentSingleScoreMap(dto);

            //获奖及荣誉奖彰
            dto.setCategoryCode("awards_and_honors");
            Map<Long, QuantitativeAssessmentSingleScoreVo> awardsAndHonorableMap = getQuantitativeAssessmentSingleScoreMap(dto);

            //准军事化考核
            dto.setCategoryCode("militarize_manage");
            Map<Long, QuantitativeAssessmentSingleScoreVo> paramilitaryMap = getQuantitativeAssessmentSingleScoreMap(dto);

            //班级文化建设
            dto.setCategoryCode("classe_culture");
            Map<Long, QuantitativeAssessmentSingleScoreVo> classCultureMap = getQuantitativeAssessmentSingleScoreMap(dto);

            //查询任课教师查询班级的数据
            TeaEvaluateClassDto evaluateClassDto = new TeaEvaluateClassDto();
            evaluateClassDto.setEvaluateType(EvaluateTypeEnum.TEACHER_EVALUATE_CLASS.getCode());
            evaluateClassDto.setYear(dto.getYear());
            LocalDate now = LocalDate.now();
            if (dto.getYear() == null) {
                evaluateClassDto.setYear(now.getYear());
            }
            evaluateClassDto.setMonth(dto.getMonth());
            if (dto.getMonth() == null) {
                evaluateClassDto.setMonth(now.getMonthValue());
            }
            List<TeaEvaluateClassListVo> teaEvaluateClassList = evaluateResultMapper.getTeaEvaluateClassList(evaluateClassDto);
            Map<Long, List<TeaEvaluateClassListVo>> classEvaluateMap = teaEvaluateClassList.stream().collect(Collectors.groupingBy(TeaEvaluateClassListVo::getEvaluatedObjectId));
            String jskhFormula = quotaFormulaRuleService.getFormulaByQuota("teacher_assessment");
            String gfjyFormula = quotaFormulaRuleService.getFormulaByQuota("militarize_manage");
            String xsgrFormula = quotaFormulaRuleService.getFormulaByQuota("student_behavior");
            String cgglFormula = quotaFormulaRuleService.getFormulaByQuota("convention_manage");
            String huojiangFormula = quotaFormulaRuleService.getFormulaByQuota("awards_and_honors");
            String classCultureFormula = quotaFormulaRuleService.getFormulaByQuota("classe_culture");

            //将结果写进对应的班级里
            for (CalssQuantitativeAssessmentPageVo c : resultPage.getRecords()) {
                Long classId = Long.parseLong(c.getClassId());
                //学生个人行为
                if (individualBehaviorMap.get(classId) != null && individualBehaviorMap.get(classId) != null) {
                    Double sumScore = individualBehaviorMap.get(classId).getSumScore();
                    c.setStuPersonalBehaviorSubScore(sumScore == null ? "" : sumScore.toString());
                    String replacedExpression = xsgrFormula.replace("xsgrKouFen", Double.toString(sumScore));
                    ScriptEngineManager manager = new ScriptEngineManager();
                    ScriptEngine engine = manager.getEngineByName("JavaScript");
                    try {
                        double result = ((Number) engine.eval(replacedExpression)).doubleValue();
                        c.setStuPersonalBehaviorScore(BigDecimal.valueOf(result).setScale(2, RoundingMode.HALF_UP).doubleValue() + "");
                    } catch (ScriptException e) {
                        Log.error(e.getMessage(), e);
                    }
                }
                //常规管理
                if (conventionalManagementMap.get(classId) != null && conventionalManagementMap.get(classId) != null) {
                    Double sumScore = conventionalManagementMap.get(classId).getSumScore();
                    if (sumScore != null) {
                        String scoreStr = sumScore.toString();
                        c.setClassConventionalManageSubScore(scoreStr);

                        String replacedExpression = cgglFormula.replace("cgglKouFen", Double.toString(sumScore));
                        ScriptEngineManager manager = new ScriptEngineManager();
                        ScriptEngine engine = manager.getEngineByName("JavaScript");
                        try {
                            double result = ((Number) engine.eval(replacedExpression)).doubleValue();
                            c.setClassConventionalManageScore(BigDecimal.valueOf(result).setScale(2, RoundingMode.HALF_UP).doubleValue() + "");
                        } catch (ScriptException e) {
                            Log.error(e.getMessage(), e);
                        }
                    }
                }
                //获奖和荣誉
                if (awardsAndHonorableMap.get(classId) != null && awardsAndHonorableMap.get(classId) != null) {
                    Double sumScore = awardsAndHonorableMap.get(classId).getSumScore();
                    if (sumScore != null) {
                        c.setHonorSumScore(sumScore.toString());

                        String replacedExpression = huojiangFormula.replace("HuoJiang", Double.toString(sumScore));
                        ScriptEngineManager manager = new ScriptEngineManager();
                        ScriptEngine engine = manager.getEngineByName("JavaScript");
                        try {
                            double result = ((Number) engine.eval(replacedExpression)).doubleValue();
                            c.setHonorScore(BigDecimal.valueOf(result).setScale(2, RoundingMode.HALF_UP).doubleValue() + "");
                        } catch (ScriptException e) {
                            Log.error(e.getMessage(), e);
                        }
                    }
                }
                //军事化
                if (paramilitaryMap.get(classId) != null && paramilitaryMap.get(classId) != null) {
                    Double sumScore = paramilitaryMap.get(classId).getSumScore();
                    if (sumScore != null) {
                        c.setMilitaryManageComprehensiveIndex(sumScore.toString());
                        //此项得分
                        String replacedExpression = gfjyFormula.replace("gfjyKouFen", Double.toString(sumScore));
                        ScriptEngineManager manager = new ScriptEngineManager();
                        ScriptEngine engine = manager.getEngineByName("JavaScript");
                        try {
                            double result = ((Number) engine.eval(replacedExpression)).doubleValue();
                            c.setMilitaryManageScore(BigDecimal.valueOf(result).setScale(2, RoundingMode.HALF_UP).doubleValue() + "");
                        } catch (ScriptException e) {
                            Log.error(e.getMessage(), e);
                        }
                    }
                }
                //任课教师考核
                if (classEvaluateMap.containsKey(classId)) {
                    Map<Long, List<TeaEvaluateClassListVo>> evaluateCategoryMap = classEvaluateMap.get(classId).stream().collect(Collectors.groupingBy(TeaEvaluateClassListVo::getId));
                    int size = evaluateCategoryMap.size();
                    List<Double> evaluateSocreList = new ArrayList<>();//存每个项的平均分
                    for (Long id : evaluateCategoryMap.keySet()) {
                        List<TeaEvaluateClassListVo> resultList = evaluateCategoryMap.get(id);
                        int allSorce = resultList.stream().mapToInt(TeaEvaluateClassListVo::getScore).sum();
                        double avgScore = BigDecimal.valueOf(allSorce).divide(BigDecimal.valueOf(resultList.size()), 2, RoundingMode.HALF_UP).doubleValue();
                        evaluateSocreList.add(avgScore);
                    }
                    double sum = evaluateSocreList.stream().mapToDouble(Double::doubleValue).sum();
                    double jskhValue = BigDecimal.valueOf(sum).divide(BigDecimal.valueOf(size), 2, RoundingMode.HALF_UP).doubleValue();
                    c.setTeacherAssessComprehensiveIndex(jskhValue + "");
                    String replacedExpression = jskhFormula.replace("JSKH", Double.toString(jskhValue));

                    ScriptEngineManager manager = new ScriptEngineManager();
                    ScriptEngine engine = manager.getEngineByName("JavaScript");
                    try {
                        double result = ((Number) engine.eval(replacedExpression)).doubleValue();
                        c.setTeacherAssessScore(BigDecimal.valueOf(result).setScale(2, RoundingMode.HALF_UP).doubleValue() + "");
                    } catch (ScriptException e) {
                        Log.error(e.getMessage(), e);
                    }
                }
                //班级文化建设
                if (classCultureMap.containsKey(classId) && classCultureMap.get(classId) != null) {
                    Double sumScore = classCultureMap.get(classId).getSumScore();
                    if (sumScore != null) {
                        c.setClassCultureScore(sumScore.toString());

                        String replacedExpression = classCultureFormula.replace("bjwhKouFen", Double.toString(sumScore));

                        ScriptEngineManager manager = new ScriptEngineManager();
                        ScriptEngine engine = manager.getEngineByName("JavaScript");
                        try {
                            double result = ((Number) engine.eval(replacedExpression)).doubleValue();
                            c.setClassCultureIndex(BigDecimal.valueOf(result).setScale(2, RoundingMode.HALF_UP).doubleValue() + "");
                        } catch (ScriptException e) {
                            Log.error(e.getMessage(), e);
                        }
                    }
                }
            }
        }

        return resultPage;
    }

    private Map<Long, QuantitativeAssessmentSingleScoreVo> getQuantitativeAssessmentSingleScoreMap(CalssQuantitativeAssessmentPageDto dto) {
        List<QuantitativeAssessmentSingleScoreVo> individualBehaviorList = assessmentInspectionMapper.getAssessmentInspectionByClassIdList(new QuantitativeAssessmentSingleScoreDto() {{
            setClassIdList(dto.getClassIdList());
            setBaseStudentAssessmentCategoryId(dto.getBaseStudentAssessmentCategoryId());
            setCategoryCode(dto.getCategoryCode());
            if (dto.getBaseSemesterId() != null) {
                setBaseSemesterId(dto.getBaseSemesterId());
            }
            if (dto.getStartTime() != null) {
                setStartTime(dto.getStartTime());
            }
            if (dto.getEndTime() != null) {
                setStartTime(dto.getEndTime());
            }
            setYear(dto.getYear());
            setMonth(dto.getMonth());
        }});
        Map<Long, QuantitativeAssessmentSingleScoreVo> individualBehaviorMap = new HashMap<>();
        for (QuantitativeAssessmentSingleScoreVo q : individualBehaviorList) {
            if (q.getClassId() != null) {
                individualBehaviorMap.put(q.getClassId(), q);
            }
        }
        return individualBehaviorMap;
    }

    @Override
    @Transactional
    public Boolean dataHandle(Long id) {
        //获取考核记录
        BaseStudentAssessmentInspection baseStudentAssessmentInspection = this.getById(id);
        if (baseStudentAssessmentInspection != null) {
            double totalScore = 0d;
            if (StrUtil.isNotEmpty(baseStudentAssessmentInspection.getClassIds())) {
                //同步数据到考核关联班级表
                String[] classIds = baseStudentAssessmentInspection.getClassIds().split(",");
                for (String classId : classIds) {
                    assessmentClassRelationMapper.insert(new BaseStudentAssessmentClassRelation() {{
                        setClassId(Long.parseLong(classId));
                        setBaseStudentAssessmentInspectionId(baseStudentAssessmentInspection.getId());
                        QueryWrapper<BaseStudentAssessmentClassRelation> queryWrapperSortcode = new QueryWrapper<>();
                        queryWrapperSortcode.select("IFNULL(MAX(sort_code),0) as sortCode");
                        BaseStudentAssessmentClassRelation b = assessmentClassRelationMapper.selectOne(queryWrapperSortcode);
                        setSortCode(b.getSortCode() + 1);
                    }});
                }
                totalScore = baseStudentAssessmentInspection.getScore();
            }
            //同步数据到考核关联学生表
            if (StrUtil.isNotEmpty(baseStudentAssessmentInspection.getPersonalStudentUserIds())) {
                String[] studentIds = baseStudentAssessmentInspection.getPersonalStudentUserIds().split(",");
                for (String studentId : studentIds) {
                    assessmentStudentRelationMapper.insert(new BaseStudentAssessmentStudentRelation() {{
                        MPJLambdaWrapper<BaseStudentSchoolRoll> baseStudentSchoolRollMPJLambdaWrapper = new MPJLambdaWrapper<>();
                        baseStudentSchoolRollMPJLambdaWrapper
                                .select(BaseStudentSchoolRoll::getId)
                                .select(BaseStudentSchoolRoll.class, x -> VoToColumnUtil.fieldsToColumns(BaseStudentSchoolRoll.class).contains(x.getProperty()))
                                .eq(BaseStudentSchoolRoll::getUserId, studentId);
                        BaseStudentSchoolRoll baseStudentSchoolRoll = baseStudentSchoolRollMapper.selectOne(baseStudentSchoolRollMPJLambdaWrapper);
                        if (baseStudentSchoolRoll != null) {
                            setClassId(baseStudentSchoolRoll.getClassId());
                        }
                        setUserId(Long.parseLong(studentId));
                        setBaseStudentAssessmentInspectionId(baseStudentAssessmentInspection.getId());
                        QueryWrapper<BaseStudentAssessmentStudentRelation> queryWrapperSortcode = new QueryWrapper<>();
                        queryWrapperSortcode.select("IFNULL(MAX(sort_code),0) as sortCode");
                        BaseStudentAssessmentStudentRelation b = assessmentStudentRelationMapper.selectOne(queryWrapperSortcode);
                        setSortCode(b.getSortCode() + 1);
                        setScore(baseStudentAssessmentInspection.getScore());

                    }});
                }

                totalScore = studentIds.length * baseStudentAssessmentInspection.getScore();
            }
            baseStudentAssessmentInspection.setTotalScore(totalScore);
            this.updateById(baseStudentAssessmentInspection);

        }
        return true;
    }

    @Override
    public Boolean noticeTeacher(Long id) {
        try {
            BaseStudentAssessmentInspection inspection = this.getById(id);
            String classIds = inspection.getClassIds();
            BaseClass baseClass = baseClassMapper.selectById(classIds);
            User user = userService.getById(baseClass.getTeacherId());
            String wechatTemplate = "Xb21V8au0Ur9puQs4hIDJTl8LP6GTgVOHQtOeie1Oco";

            WeChatSendMessageDto weChatSendMessageDto = new WeChatSendMessageDto();
            weChatSendMessageDto.setUserId(user.getOpenId());
            weChatSendMessageDto.setTemplateId(wechatTemplate);
            weChatSendMessageDto.setMsgId(IdUtil.getSnowflakeNextId() + "");
            weChatSendMessageDto.setUrl(StrUtil.format("{}pages/student/mentinspection/index", commonPropertiesConfig.getDomainApp()));
            JSONObject data = new JSONObject();
            String thing4 = "无";
            String thing1 = "无";
            if (AssessmentTypeEnum.CLASS.getCode().equals(inspection.getAssessmentType())) {
                data.put("const3", new JSONObject() {{
                    put("value", AssessmentTypeEnum.CLASS.getValue());
                }});

                BaseStudentAssessmentProject project = projectMapper.selectById(inspection.getBaseStudentAssessmentProjectId());
                thing4 = project.getName();

            } else if (AssessmentTypeEnum.PERSONAL.getCode().equals(inspection.getAssessmentType())) {
                data.put("const3", new JSONObject() {{
                    put("value", AssessmentTypeEnum.PERSONAL.getValue());
                }});
                String[] studentUsers = inspection.getPersonalStudentUserIds().split(",");
                List<String> studentIds = new ArrayList<>();
                for (String studentUser : studentUsers) {
                    studentIds.add(studentUser.trim());
                }
                List<User> userList = userService.listByIds(studentIds);
                for (int i = 0; i < userList.size(); i++) {
                    if (i > 0) {
                        thing1 += ",";
                    }
                    thing1 += userList.get(i).getName();
                }
                if (thing1.length() > 20) {
                    thing1 = thing1.substring(0, 16) + "...";
                }

                BaseStudentAssessmentCategory category = categoryMapper.selectById(inspection.getBaseStudentAssessmentCategoryId());
                thing4 = category.getName();
            }
            //得分
            JSONObject thing4Json = new JSONObject();
            thing4Json.put("value", thing4);
            data.put("thing4", thing4Json);

            //学生
            JSONObject thing1Json = new JSONObject();
            thing1Json.put("value", thing1);
            data.put("thing1", thing1Json);

            //得分
            data.put("character_string5", new JSONObject() {{
                put("value", inspection.getScore());
            }});

            //考核时间
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
            data.put("time2", new JSONObject() {{
                put("value", sdf.format(inspection.getAssessmentDate()));
            }});

            weChatSendMessageDto.setContent(data);
            weChatService.sendTemplateMessage(weChatSendMessageDto);
            return true;
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
            return false;
        }
    }

    @Override
    public byte[] getExcelByte(CalssQuantitativeAssessmentPageDto dto) throws IOException {
        IPage<CalssQuantitativeAssessmentPageVo> page = this.getCalssQuantitativeAssessmentPage(new Page<>(1, 10000), dto);

        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表(sheet)
        String sheetName = "数据";
        Sheet sheet = workbook.createSheet(sheetName);
        createFirstTitle(workbook, sheet);
        createSecondTitle(workbook, sheet, 6);

        Font font = workbook.createFont();
        font.setBold(false);// 设置为粗体
        font.setFontName("宋体");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font); // 将字体应用到样式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        int sortCode = 1;
        for (CalssQuantitativeAssessmentPageVo pageVo : page.getRecords()) {
            Row dataRow = sheet.createRow(sortCode + 1);

            int columnNumber = 0;
            Cell cell = dataRow.createCell(columnNumber);
            cell.setCellValue(sortCode + "");
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getOa());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getClassName());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getHeadTeacherName());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getStudentNum() == null ? "" : pageVo.getStudentNum().toString());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            if (pageVo.getClassStatus() != null && pageVo.getClassStatus() == 1) {
                cell.setCellValue("在读");
            } else {
                cell.setCellValue("毕业");
            }

            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getStuPersonalBehaviorSubScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getStuPersonalBehaviorScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getClassConventionalManageSubScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getClassConventionalManageScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getHonorSumScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getHonorScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getMilitaryManageComprehensiveIndex());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getMilitaryManageScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getTeacherAssessComprehensiveIndex());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getTeacherAssessScore());
            cell.setCellStyle(cellStyle);


            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getClassCultureScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getClassCultureIndex());
            cell.setCellStyle(cellStyle);


            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getAssessSumScore());
            cell.setCellStyle(cellStyle);

            columnNumber++;
            cell = dataRow.createCell(columnNumber);
            cell.setCellValue(pageVo.getAssessMoney());
            cell.setCellStyle(cellStyle);

            sortCode++;
        }

        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        workbook.write(bot);
        return bot.toByteArray();
    }

    @Override
    public Boolean importData(List<ImportBaseStudentAssessmentInspectionDto> savedDataList) {
        try {
            // 提取出所有班级名字
            Set<String> classNames = savedDataList.stream().map(ImportBaseStudentAssessmentInspectionDto::getClassName).collect(Collectors.toSet());

            Set<String> categorys = savedDataList.stream().map(ImportBaseStudentAssessmentInspectionDto::getAssessmentCategory).collect(Collectors.toSet());

            // 提取出所有身份证
            Set<String> credentialNumbers = savedDataList.stream().map(ImportBaseStudentAssessmentInspectionDto::getCredentialNumber).collect(Collectors.toSet());

            List<BaseClass> classList = baseClassMapper.selectList(
                    new QueryWrapper<BaseClass>().lambda()
                            .in(BaseClass::getName, classNames)
            );
            Map<String, BaseClass> classMap = classList.stream().collect(Collectors.toMap(BaseClass::getName, x -> x));

            Set<String> checkClassNames = new HashSet<>(classMap.values().stream().map(BaseClass::getName).collect(Collectors.toSet()));
            checkClassNames.removeAll(classMap.keySet());
            if (!checkClassNames.isEmpty()) {
                throw new MyException("导入表格中的班级：" + checkClassNames + " 在系统中不存在");
            }


            List<BaseStudentAssessmentProject> projectList = projectMapper.selectList(
                    new QueryWrapper<BaseStudentAssessmentProject>().lambda()
                            .eq(BaseStudentAssessmentProject::getDeleteMark, DeleteMark.NODELETE.getCode())
                            .eq(BaseStudentAssessmentProject::getEnabledMark, EnabledMark.ENABLED.getCode())
            );
            Map<Long, Map<String, BaseStudentAssessmentProject>> projectMap = projectList.stream().collect(
                    Collectors.groupingBy(
                            BaseStudentAssessmentProject::getBaseStudentAssessmentCategoryId,
                            Collectors.toMap(BaseStudentAssessmentProject::getName, x -> x)
                    )
            );

            Map<String, Long> categoryMap = categoryMapper.selectList(
                            new QueryWrapper<BaseStudentAssessmentCategory>().lambda()
                                    .eq(BaseStudentAssessmentCategory::getDeleteMark, DeleteMark.NODELETE.getCode())
                                    .eq(BaseStudentAssessmentCategory::getEnabledMark, EnabledMark.ENABLED.getCode())
                    )
                    .stream().collect(Collectors.toMap(BaseStudentAssessmentCategory::getName, BaseStudentAssessmentCategory::getId));

            Set<String> checkCategorys = new HashSet<>(categorys);
            checkCategorys.removeAll(categoryMap.keySet());
            if (!checkCategorys.isEmpty()) {
                throw new MyException("导入表格中的考核类别：" + checkCategorys + " 在系统中不存在");
            }

            List<BaseStudentAssessmentItem> itemList = itemMapper.selectList(
                    new QueryWrapper<BaseStudentAssessmentItem>().lambda()
                            .eq(BaseStudentAssessmentItem::getDeleteMark, DeleteMark.NODELETE.getCode())
                            .eq(BaseStudentAssessmentItem::getEnabledMark, EnabledMark.ENABLED.getCode())
            );
            Map<Long, Map<String, Long>> itemMap = itemList.stream().collect(
                    Collectors.groupingBy(
                            BaseStudentAssessmentItem::getBaseStudentAssessmentCategoryId,
                            Collectors.toMap(BaseStudentAssessmentItem::getName, BaseStudentAssessmentItem::getId)
                    )
            );

            List<BaseStudentUserPageVo> studentList = studentService.getStudentList(new BaseStudentUserPageDto() {{
                setCredentialNumbers(new ArrayList<>(credentialNumbers));
            }});
            Map<String, String> studentMap = studentList.stream().collect(Collectors.toMap(BaseStudentUserPageVo::getCredentialNumber, BaseStudentUserPageVo::getId));

            Set<String> checkStudents = new HashSet<>();
            Set<String> checkProjects = new HashSet<>();
            Set<String> checkItems = new HashSet<>();

            List<BaseStudentAssessmentInspection> insertList = new ArrayList<>();
            List<BaseStudentAssessmentStudentRelation> childInsertList = new ArrayList<>();
            List<BaseStudentAssessmentClassRelation> child2InsertList = new ArrayList<>();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
            int sortCode = 1;
            for (ImportBaseStudentAssessmentInspectionDto dto : savedDataList) {
                long id = IdUtil.getSnowflakeNextId();
                BaseStudentAssessmentInspection data = new BaseStudentAssessmentInspection();

                data.setId(id);

                data.setEnabledMark(EnabledMark.ENABLED.getCode());
                data.setDeleteMark(DeleteMark.NODELETE.getCode());
                data.setCreateDate(new Date());
                data.setCreateUserId(StpUtil.getLoginIdAsLong());
                BaseClass baseClass = classMap.get(dto.getClassName());
                data.setClassIds(baseClass.getId().toString());
                data.setGradeId(baseClass.getGradeId());
                data.setBaseStudentAssessmentCategoryId(categoryMap.get(dto.getAssessmentCategory()));


                Map<String, Long> itemGroup = itemMap.get(data.getBaseStudentAssessmentCategoryId());
                if (itemGroup == null || !itemGroup.containsKey(dto.getAssessmentItem())) {
                    checkItems.add("考核类别“" + dto.getAssessmentCategory() + "”中不存在考核项“" + dto.getAssessmentItem() + "”");
                }
                data.setBaseStudentAssessmentItemId(itemGroup.get(dto.getAssessmentItem()));


                Map<String, BaseStudentAssessmentProject> projectGroup = projectMap.get(data.getBaseStudentAssessmentItemId());
                if (projectGroup == null || !projectGroup.containsKey(dto.getAssessmentProject())) {
                    checkProjects.add("考核项目“" + dto.getAssessmentItem() + "”中不存在考核项“" + dto.getAssessmentProject() + "”");
                    continue;
                }
                BaseStudentAssessmentProject project = projectGroup.get(dto.getAssessmentProject());
                data.setBaseStudentAssessmentProjectId(project.getId());
                data.setScore(project.getBasicPoints());
                data.setTotalScore(project.getBasicPoints());
                if (project.getBasicPoints() > 0) {
                    data.setScoreType("score_add");
                } else {
                    data.setScoreType("score_minus");
                }

                data.setReason(dto.getReason());
                data.setAssessmentUserId(StpUtil.getLoginIdAsLong());
                data.setAssessmentDate(sdf.parse(dto.getAssessmentDate()));

                BaseStudentAssessmentClassRelation children2 = new BaseStudentAssessmentClassRelation();
                children2.setBaseStudentAssessmentInspectionId(id);
                children2.setClassId(baseClass.getId());
                children2.setSortCode(sortCode);
                child2InsertList.add(children2);
                sortCode++;

                if ("班级".equals(dto.getAssessmentType())) {
                    data.setAssessmentType("class");
                } else if ("个人".equals(dto.getAssessmentType())) {
                    data.setAssessmentType("personal");
                    if (!studentMap.containsKey(dto.getCredentialNumber())) {
                        checkStudents.add(dto.getStudentName());
                        continue;
                    }
                    data.setPersonalStudentUserIds(studentMap.get(dto.getCredentialNumber()));

                    BaseStudentAssessmentStudentRelation children = new BaseStudentAssessmentStudentRelation();
                    children.setBaseStudentAssessmentInspectionId(id);
                    children.setScore(project.getBasicPoints());
                    children.setUserId(Long.parseLong(studentMap.get(dto.getCredentialNumber())));
                    children.setClassId(baseClass.getId());
                    children.setEnabledMark(EnabledMark.ENABLED.getCode());
                    children.setDeleteMark(DeleteMark.NODELETE.getCode());
                    childInsertList.add(children);
                }

                insertList.add(data);


            }
            if (!checkStudents.isEmpty()) {
                throw new MyException("导入表格中的学生：" + checkStudents + " 在系统中不存在");
            }
            if (!checkProjects.isEmpty()) {
                throw new MyException(checkProjects.toString().replace("[", "").replace("]", ""));
            }
            if (!checkItems.isEmpty()) {
                throw new MyException(checkItems.toString().replace("[", "").replace("]", ""));
            }
            if (!insertList.isEmpty()) {
                this.saveBatch(insertList);
            }
            if (!childInsertList.isEmpty()) {
                for (BaseStudentAssessmentStudentRelation baseStudentAssessmentStudentRelation : childInsertList) {
                    assessmentStudentRelationMapper.insert(baseStudentAssessmentStudentRelation);
                }
            }
            if (!child2InsertList.isEmpty()) {
                for (BaseStudentAssessmentClassRelation baseStudentAssessmentStudentRelation : child2InsertList) {
                    assessmentClassRelationMapper.insert(baseStudentAssessmentStudentRelation);
                }
            }
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
            if (e.getClass().equals(MyException.class)) {
                throw new MyException(e.getMessage());
            } else {
                throw new MyException("导入报错，请联系管理员");
            }
        }
        return true;
    }

    void createFirstTitle(Workbook workbook, Sheet sheet) {
        int rowNumber = 0;
        Font font = workbook.createFont();
        font.setFontName("宋体");
        //font.setColor(IndexedColors.RED.getIndex()); // 设置字体颜色为红色
        font.setFontHeightInPoints((short) 12);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font); // 将字体应用到样式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        Row row = sheet.createRow(rowNumber);
        //3、处理表头
        int columnNumber = 0;
        Cell cell = row.createCell(columnNumber);
        cell.setCellValue("序号");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("工号");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("班级");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("班主任");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("人数");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("状态");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("学生个人行为");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, columnNumber, columnNumber + 1));
        columnNumber++;

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("常规管理");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, columnNumber, columnNumber + 1));
        columnNumber++;

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("获奖及荣誉奖章");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, columnNumber, columnNumber + 1));
        columnNumber++;

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("国防教学特色");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, columnNumber, columnNumber + 1));
        columnNumber++;

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("任课教师考核");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, columnNumber, columnNumber + 1));
        columnNumber++;

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("班级文化建设");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, columnNumber, columnNumber + 1));
        columnNumber++;


        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("月份总得分");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));

        columnNumber++;
        cell = row.createCell(columnNumber);
        cell.setCellValue("月考核金额");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, columnNumber, columnNumber));
    }

    void createSecondTitle(Workbook workbook, Sheet sheet, int startColunm) {
        int rowNumber = 1;
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font); // 将字体应用到样式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        Row row = sheet.createRow(rowNumber);

        int columnNumber = startColunm;
        Cell cell = row.createCell(columnNumber);
        cell.setCellValue("扣分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("此项得分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("扣分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("此项得分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("汇总");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("此项得分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("扣分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("此项得分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("指数");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("此项得分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("扣分");
        cell.setCellStyle(cellStyle);
        columnNumber++;

        cell = row.createCell(columnNumber);
        cell.setCellValue("此项得分");
        cell.setCellStyle(cellStyle);
    }
}