api/src/main/java/com/xjrsoft/module/classtime/service/impl/ClassTimeStatisticsServiceImpl.java

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

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.github.yulichang.base.MPJBaseServiceImpl;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.xjrsoft.common.enums.CourseAdjustTypeEnum;
import com.xjrsoft.common.enums.DeleteMark;
import com.xjrsoft.common.enums.EnabledMark;
import com.xjrsoft.common.exception.MyException;
import com.xjrsoft.common.mybatis.SqlRunnerAdapter;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.classtime.dto.AddClassTimeStatisticsDto;
import com.xjrsoft.module.classtime.dto.CourseRecordDto;
import com.xjrsoft.module.classtime.entity.ClassTimeDelete;
import com.xjrsoft.module.classtime.entity.ClassTimeStatistics;
import com.xjrsoft.module.classtime.entity.ClassTimeStatisticsAdministration;
import com.xjrsoft.module.classtime.entity.ClassTimeStatisticsRecord;
import com.xjrsoft.module.classtime.entity.ClassTimeStatisticsSet;
import com.xjrsoft.module.classtime.mapper.ClassTimeStatisticsAdministrationMapper;
import com.xjrsoft.module.classtime.mapper.ClassTimeStatisticsMapper;
import com.xjrsoft.module.classtime.mapper.ClassTimeStatisticsRecordMapper;
import com.xjrsoft.module.classtime.service.IClassTimeDeleteService;
import com.xjrsoft.module.classtime.service.IClassTimeStatisticsService;
import com.xjrsoft.module.classtime.service.IClassTimeStatisticsSetService;
import com.xjrsoft.module.classtime.vo.ClassTimeStatisticsAdministrationExcelVo;
import com.xjrsoft.module.classtime.vo.ClassTimeStatisticsRecordVo;
import com.xjrsoft.module.classtime.vo.CourseClassTimeStatisticsRecordVo;
import com.xjrsoft.module.classtime.vo.CourseListVo;
import com.xjrsoft.module.classtime.vo.TeacherListVo;
import com.xjrsoft.module.classtime.vo.WeekTimeRangeVo;
import com.xjrsoft.module.oa.entity.WfTeacherCourseTime;
import com.xjrsoft.module.organization.entity.User;
import com.xjrsoft.module.organization.service.IUserService;
import com.xjrsoft.module.system.entity.DictionaryDetail;
import com.xjrsoft.module.system.service.IDictionarydetailService;
import com.xjrsoft.module.teacher.entity.BaseTeacher;
import com.xjrsoft.module.teacher.entity.XjrUser;
import lombok.AllArgsConstructor;
import me.zhyd.oauth.log.Log;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CompletableFuture;
import java.util.stream.Collectors;

/**
* @title: 课时统计
* @Author dzx
* @Date: 2024-09-26
* @Version 1.0
*/
@Service
@AllArgsConstructor
public class ClassTimeStatisticsServiceImpl extends MPJBaseServiceImpl<ClassTimeStatisticsMapper, ClassTimeStatistics> implements IClassTimeStatisticsService {
    private final ClassTimeStatisticsMapper statisticsMapper;

    private final ClassTimeStatisticsRecordMapper recordMapper;

    private final IClassTimeStatisticsSetService statisticsSetService;

    private final IClassTimeDeleteService deleteService;
    private final IDictionarydetailService dictionaryService;

    private final ClassTimeStatisticsAdministrationMapper administrationMapper;

    private final IUserService userService;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean add(ClassTimeStatistics classTimeStatistics) {
        statisticsMapper.insert(classTimeStatistics);
        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean addCourse(AddClassTimeStatisticsDto dto) {
        ClassTimeStatistics classTimeStatistics = BeanUtil.toBean(dto, ClassTimeStatistics.class);
        classTimeStatistics.setStatus(0);
        classTimeStatistics.setCategory(2);
        classTimeStatistics.setOverWorkloadNumberStatus(0);
        classTimeStatistics.setCreateDate(new Date());

        List<WeekTimeRangeVo> weekTimeRangeVos = calculateNaturalWeeks(classTimeStatistics.getStartDate(), classTimeStatistics.getEndDate());
        classTimeStatistics.setWeeks(weekTimeRangeVos.size());
        //查询最新权重并保存到统计中
        Integer category = 3;
        List<ClassTimeStatisticsSet> list = statisticsSetService.list(
                new QueryWrapper<ClassTimeStatisticsSet>().lambda()
                        .eq(ClassTimeStatisticsSet::getCategory, category)
                        .orderByDesc(ClassTimeStatisticsSet::getCreateDate)
        );
        if(list.isEmpty()){
            throw new MyException("请先设置权重并进行保存");
        }
        classTimeStatistics.setWeightSetJson(list.get(0).getJsonContent());

        this.save(classTimeStatistics);
        refreshCourseRecord(classTimeStatistics);
        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean update(ClassTimeStatistics classTimeStatistics) {
        statisticsMapper.updateById(classTimeStatistics);
        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean updateCourse(ClassTimeStatistics classTimeStatistics) {
        List<WeekTimeRangeVo> weekTimeRangeVos = calculateNaturalWeeks(classTimeStatistics.getStartDate(), classTimeStatistics.getEndDate());
        classTimeStatistics.setWeeks(weekTimeRangeVos.size());
        statisticsMapper.updateById(classTimeStatistics);
        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean delete(List<Long> ids) {
        statisticsMapper.deleteBatchIds(ids);
        recordMapper.delete(Wrappers.lambdaQuery(ClassTimeStatisticsRecord.class).in(ClassTimeStatisticsRecord::getClassTimeStatisticsId, ids));

        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean add(AddClassTimeStatisticsDto dto) {
        ClassTimeStatistics classTimeStatistics = BeanUtil.toBean(dto, ClassTimeStatistics.class);
        classTimeStatistics.setStatus(0);
        classTimeStatistics.setCategory(1);
        classTimeStatistics.setCreateDate(new Date());

        //查询最新权重并保存到统计中
        Integer category = 1;
        List<ClassTimeStatisticsSet> list = statisticsSetService.list(
                new QueryWrapper<ClassTimeStatisticsSet>().lambda()
                        .eq(ClassTimeStatisticsSet::getCategory, category)
                        .orderByDesc(ClassTimeStatisticsSet::getCreateDate)
        );
        if(list.isEmpty()){
            throw new MyException("请先设置权重并进行保存");
        }
        classTimeStatistics.setWeightSetJson(list.get(0).getJsonContent());

        //查询最新费用并保存到统计中
        category = 2;
        list = statisticsSetService.list(
                new QueryWrapper<ClassTimeStatisticsSet>().lambda()
                        .eq(ClassTimeStatisticsSet::getCategory, category)
                        .orderByDesc(ClassTimeStatisticsSet::getCreateDate)
        );
        if(list.isEmpty()){
            throw new MyException("请先设置费用并进行保存");
        }
        classTimeStatistics.setCostSetJson(list.get(0).getJsonContent());
        this.save(classTimeStatistics);

        CompletableFuture.runAsync(() -> {
            refreshRecord(classTimeStatistics);
        });

        return true;
    }

    /**
     * 1、通过xjr_user和base_teacher查询所有教师，查询聘用类型为：正式聘用和外聘的
     * 2、根据统计的开始日期和结束日期查询课时补充(wf_teacher_course_time)中的教研会、督导听课、临近三年退休政策、出题、阅卷、周末培优
     * 3、根绝统计的开始日期和结束日期查询课程表(course_table)中的所有课程数据
     * 4、查询补班日期下的所有课程
     * 5、属于节假日的课程不查询
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean refreshRecord(ClassTimeStatistics statistics) {
        try {
            // 1、查询教师
            List<TeacherListVo> teacherList = this.baseMapper.getTeacherList();
            // 2、查询补课课时
            List<DictionaryDetail> CourseTimeTypeList = dictionaryService.list(
                    new QueryWrapper<DictionaryDetail>().lambda()
                            .eq(DictionaryDetail::getItemId, 1833772737004875778L)
                            .eq(DictionaryDetail::getDeleteMark, DeleteMark.NODELETE.getCode())
                            .eq(DictionaryDetail::getEnabledMark, EnabledMark.ENABLED.getCode())
                            .orderByAsc(DictionaryDetail::getCode)
            );
            Map<String, String> CourseTimeTypeMap = CourseTimeTypeList.stream().collect(Collectors.toMap(DictionaryDetail::getCode, DictionaryDetail::getName));
            List<WfTeacherCourseTime> courseTimeList = this.baseMapper.getWfTeacherCourseTimeList(statistics);
            //按照课时补充类型分类统计
            Map<String, List<WfTeacherCourseTime>> courseTimeMap = courseTimeList.stream().collect(Collectors.groupingBy(WfTeacherCourseTime::getCourseTimeType));
            JsonParser parser = new JsonParser();

            //费用设置jsonArray
            JsonArray costSetArray = parser.parse(statistics.getCostSetJson()).getAsJsonArray();
            Map<String, Double> costSetMap = new LinkedHashMap<>();
            for (JsonElement jsonElement : costSetArray) {
                JsonObject object = jsonElement.getAsJsonObject();
                costSetMap.put(object.get("field").getAsString(), object.get("value").getAsDouble());
            }

            //权重设置jsonArray
            JsonArray weightSetArray = parser.parse(statistics.getWeightSetJson()).getAsJsonArray();
            Map<String, Double> weightSetMap = new LinkedHashMap<>();
            for (JsonElement jsonElement : weightSetArray) {
                JsonObject object = jsonElement.getAsJsonObject();
                weightSetMap.put(object.get("label").getAsString(), object.get("value").getAsDouble());
            }
            //计算出这个时间段内一共多少周
            List<WeekTimeRangeVo> weekTimeRangeVos = calculateNaturalWeeks(statistics.getStartDate(), statistics.getEndDate());
            //取出所有的日期
            List<LocalDate> allDateList = getDatesBetween(statistics.getStartDate(), statistics.getEndDate());
            //查询课程数据，排除开节假日的数据
            List<CourseListVo> allCourseList = this.baseMapper.getCourseList(statistics);
            allCourseList.addAll(this.baseMapper.getHolidayReplaceCourseList(statistics));

            //查询删除课时的数据，将每个班删除的具体日期统计出来
            List<ClassTimeDelete> deleteList = deleteService.list(
                    new QueryWrapper<ClassTimeDelete>().lambda()
                            .ne(ClassTimeDelete::getStatus, 2)
                            .eq(ClassTimeDelete::getEnabledMark, EnabledMark.ENABLED.getCode())
                            .eq(ClassTimeDelete::getDeleteMark, DeleteMark.NODELETE.getCode())
            );
            Map<Long, List<ClassTimeDelete>> deleteDataMap = deleteList.stream().collect(Collectors.groupingBy(ClassTimeDelete::getClassId));
            Map<Long, Map<LocalDate, String>> deleteMap = new HashMap<>();//将每个班级计算出来所有被删除的日期存入map
            for (Long classId : deleteDataMap.keySet()) {
                Map<LocalDate, String> dateMap = new HashMap<>();
                for (ClassTimeDelete classTimeDelete : deleteDataMap.get(classId)) {
                    LocalDate currentDate = classTimeDelete.getStartDate();
                    while (!currentDate.isAfter(classTimeDelete.getEndDate())) {
                        dateMap.put(currentDate, classTimeDelete.getTimePeriod());
                        currentDate = currentDate.plusDays(1); // 增加一天
                    }
                }
                //去重并存到map中
                deleteMap.put(classId, dateMap);
            }
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

            //查询所有老师发起顶课通过的数量，也要分别计算早自习、晚自习、正课、如果日期出入课时删除中，也需要跳过
            List<CourseListVo> allSubstituteList = this.baseMapper.getSubstituteList(statistics);
            List<ClassTimeStatisticsRecord> insertList = new ArrayList<>();
            //循环教师，准备开始计算
            for (TeacherListVo teacher : teacherList) {
                ClassTimeStatisticsRecord record = new ClassTimeStatisticsRecord();
                record.setUserId(teacher.getId());
                record.setClassTimeStatisticsId(statistics.getId());
                record.setEmployType(teacher.getEmployType());

                JsonArray allClassTimeDataArray = new JsonArray();

                Double allClassTime = 0D;
                //计算补充课时，算作正课课时，但是计算超出课时时不计算这部分
                JsonObject courseTimeTypeJson = new JsonObject();
                Double courseTimeTypeTime = 0D;
                for (String courseTimeType : CourseTimeTypeMap.keySet()) {
                    List<WfTeacherCourseTime> courseTimes = courseTimeMap.get(courseTimeType);
                    if(courseTimes == null){
                        courseTimeTypeJson.addProperty(CourseTimeTypeMap.get(courseTimeType), 0);
                        continue;
                    }
                    double sum = courseTimes.stream()
                            .filter(x -> x.getTeacherIds().contains(teacher.getId().toString()))
                            .mapToDouble(WfTeacherCourseTime::getCourseTime).sum();
                    allClassTime = allClassTime + sum;
                    courseTimeTypeTime = courseTimeTypeTime + sum;
                    courseTimeTypeJson.addProperty(CourseTimeTypeMap.get(courseTimeType), sum);

                    for (LocalDate localDate : allDateList) {
                        double sum1 = courseTimes.stream()
                                .filter(x -> x.getTeacherIds().contains(teacher.getId().toString()) && localDate.equals(x.getScheduleDate()))
                                .mapToDouble(WfTeacherCourseTime::getCourseTime).sum();
                        JsonObject courseJson = new JsonObject();
                        courseJson.addProperty("type", CourseTimeTypeMap.get(courseTimeType));
                        courseJson.addProperty("scheduleDate", localDate.format(formatter));
                        courseJson.addProperty("content", sum1);
                        courseJson.addProperty("adjustType", "");
                        allClassTimeDataArray.add(courseJson);
                    }
                }
                record.setCourseTimeTypeData(courseTimeTypeJson.toString());

                //早自习、正课、晚辅
                Double classTime7 = 0D,classTime8 = 0D,classTime9 = 0D,classTime11 = 0D;

                List<String> zkList = Arrays.asList("上1","上2","上3","上4","下1","下2","下3","下4");
                List<String> wzxList = Arrays.asList("晚1","晚2","晚3");
                //查询出老师的课程
                List<CourseListVo> courseList = allCourseList.stream()
                        .filter(x -> x.getTeacherId().contains(teacher.getId().toString()))
                        .collect(Collectors.toList());

                //循环，统计出各项数据
                for (CourseListVo courseListVo : courseList) {
                    //如果这个课程数据包含在被删除的课时中，跳过不计算
                    Map<LocalDate, String> deleteDates = deleteMap.get(courseListVo.getClassId());
                    JsonObject courseJson = new JsonObject();
                    if(deleteDates != null && deleteDates.containsKey(courseListVo.getScheduleDate()) && deleteDates.get(courseListVo.getScheduleDate()) != null
                            && deleteDates.get(courseListVo.getScheduleDate()).contains(courseListVo.getTimeNumber()) ){
                        courseJson.addProperty("type", courseListVo.getShortName());
                        courseJson.addProperty("scheduleDate", courseListVo.getScheduleDate().format(formatter));
                        courseJson.addProperty("content", "");
                        courseJson.addProperty("adjustType", "course_delete");
                        allClassTimeDataArray.add(courseJson);
                        continue;
                    }
                    if("早自习".equals(courseListVo.getShortName())){
                        classTime7 += weightSetMap.get(courseListVo.getShortName()) == null ? 0d : weightSetMap.get(courseListVo.getShortName());
                    }else if(zkList.contains(courseListVo.getShortName())){
                        classTime8 += weightSetMap.get(courseListVo.getShortName()) == null ? 0d : weightSetMap.get(courseListVo.getShortName());
                    }else if(wzxList.contains(courseListVo.getShortName())){
                        classTime9 += weightSetMap.get(courseListVo.getShortName()) == null ? 0d : weightSetMap.get(courseListVo.getShortName());
                    }

                    if(courseListVo.getAdjustType() != null && !courseListVo.getAdjustType().isEmpty()){
                        if(CourseAdjustTypeEnum.courseExchange.getCode().equals(courseListVo.getAdjustType())){
                            classTime11 += weightSetMap.get(courseListVo.getShortName());
                        }
                    }
                    courseJson.addProperty("type", courseListVo.getShortName());
                    courseJson.addProperty("scheduleDate", courseListVo.getScheduleDate().format(formatter));
                    courseJson.addProperty("content", courseListVo.getClassName() + "," + courseListVo.getCourseName());
                    courseJson.addProperty("adjustType", courseListVo.getAdjustType() == null?"":courseListVo.getAdjustType());
                    allClassTimeDataArray.add(courseJson);
                }
                record.setClassTime7(classTime7);
                record.setClassTime8(classTime8);
                record.setClassTime9(classTime9);
                record.setClassTime11(classTime11);
                JsonObject weekDataJson = new JsonObject();
                Double allTimes = 0d;
                Double allCcksTime = 0d;//总的超出课时，从每周的正课课时中计算
                BigDecimal ccksTime = BigDecimal.ZERO;//超出课时标准(每周)
                if("FB1601".equals(teacher.getEmployType())){
                    ccksTime = BigDecimal.valueOf(costSetMap.get("cost7"));
                }else{//外聘FB1605、合作人员FB1609
                    ccksTime = BigDecimal.valueOf(costSetMap.get("cost8"));
                }
                //存每周的数据
                for (WeekTimeRangeVo timeRangeVo : weekTimeRangeVos) {
                    Double zkTimes = 0d;
                    Double wzxTimes = 0d;
                    Double tkTimes = 0d;
                    for (CourseListVo courseListVo : courseList) {
                        LocalDate scheduleDate = courseListVo.getScheduleDate();
                        //如果这个课程数据包含在被删除的课时中，跳过不计算
                        Map<LocalDate, String> deleteDates = deleteMap.get(courseListVo.getClassId());
                        if(deleteDates != null && deleteDates.containsKey(scheduleDate) && deleteDates.get(courseListVo.getScheduleDate()) != null
                                && deleteDates.get(courseListVo.getScheduleDate()).contains(courseListVo.getTimeNumber())){
                            continue;
                        }

                        if(!( (scheduleDate.equals(timeRangeVo.getMondayDate()) || scheduleDate.isAfter(timeRangeVo.getMondayDate()))
                                && (scheduleDate.equals(timeRangeVo.getSundayDate()) || scheduleDate.isBefore(timeRangeVo.getSundayDate())))){
                            continue;
                        }

                        if("早自习".equals(courseListVo.getShortName())){
                            zkTimes += weightSetMap.get(courseListVo.getShortName()) == null ? 0d : weightSetMap.get(courseListVo.getShortName());
                        }else if(zkList.contains(courseListVo.getShortName())){
                            zkTimes += weightSetMap.get(courseListVo.getShortName()) == null ? 0d : weightSetMap.get(courseListVo.getShortName());
                        }else if(wzxList.contains(courseListVo.getShortName())){
                            wzxTimes += weightSetMap.get(courseListVo.getShortName()) == null ? 0d : weightSetMap.get(courseListVo.getShortName());
                        }

                        if(courseListVo.getAdjustType() != null && !courseListVo.getAdjustType().isEmpty()){
                            if(CourseAdjustTypeEnum.courseExchange.getCode().equals(courseListVo.getAdjustType())){
                                tkTimes += weightSetMap.get(courseListVo.getShortName());
                            }
                        }
                    }
                    //计算该老师发起的事假、病假顶课数据
                    List<CourseListVo> substituteList = allSubstituteList.stream().filter(x -> x.getExchangeTeacherId().equals(teacher.getId()))
                            .collect(Collectors.toList());
                    Double reduceTime = 0d;//顶课课时
                    for (CourseListVo courseListVo : substituteList) {
                        Map<LocalDate, String> deleteDates = deleteMap.get(courseListVo.getClassId());
                        if(deleteDates != null && deleteDates.get(courseListVo.getScheduleDate()) != null && deleteDates.get(courseListVo.getScheduleDate()).contains(courseListVo.getTimeNumber())){
                            continue;
                        }
                        if(!( (courseListVo.getScheduleDate().equals(timeRangeVo.getMondayDate()) || courseListVo.getScheduleDate().isAfter(timeRangeVo.getMondayDate()))
                                && (courseListVo.getScheduleDate().equals(timeRangeVo.getSundayDate()) || courseListVo.getScheduleDate().isBefore(timeRangeVo.getSundayDate())))){
                            continue;
                        }
                        reduceTime += weightSetMap.get(courseListVo.getShortName());
                    }

                    substituteList = allSubstituteList.stream().filter(x -> x.getTeacherId().equals(teacher.getId().toString()))
                            .collect(Collectors.toList());
                    for (CourseListVo courseListVo : substituteList) {
                        Map<LocalDate, String> deleteDates = deleteMap.get(courseListVo.getClassId());
                        if(deleteDates != null && deleteDates.get(courseListVo.getScheduleDate()) != null
                        ){
                            String timeNumbers = deleteDates.get(courseListVo.getScheduleDate());
                            if(StrUtil.isNotEmpty(timeNumbers) && timeNumbers.contains(courseListVo.getTimeNumber())){
                                continue;
                            }
                        }
                        if(!( (courseListVo.getScheduleDate().equals(timeRangeVo.getMondayDate()) || courseListVo.getScheduleDate().isAfter(timeRangeVo.getMondayDate()))
                                && (courseListVo.getScheduleDate().equals(timeRangeVo.getSundayDate()) || courseListVo.getScheduleDate().isBefore(timeRangeVo.getSundayDate())))){
                            continue;
                        }
                        reduceTime = reduceTime - weightSetMap.get(courseListVo.getShortName());
                    }

                    timeRangeVo.setZkTimes(zkTimes);
                    timeRangeVo.setWzxTimes(wzxTimes);
                    timeRangeVo.setDkTimes(reduceTime);
                    timeRangeVo.setTkTimes(tkTimes);
                    allTimes += (zkTimes + wzxTimes);
                    if(zkTimes > ccksTime.doubleValue()){
                        allCcksTime += zkTimes - ccksTime.doubleValue();
                    }
                }
                weekDataJson.add("weekData", new Gson().toJsonTree(weekTimeRangeVos));
                weekDataJson.addProperty("allTimes", allTimes);
                record.setWeekData(weekDataJson.toString());
                //计算总课时
                allClassTime = allClassTime + classTime7 + classTime8 + classTime9;
                record.setAllClassTime(allClassTime);

                //计算费用，根据聘用类型判断费用问题
                Double classTimeAmount = 0d;
                Double beyondClassTimeAmount = 0d;

                BigDecimal zzxCost = BigDecimal.ZERO;//早自习费用
                if("FB1601".equals(teacher.getEmployType())){
                    zzxCost = BigDecimal.valueOf(costSetMap.get("cost1"));
                }else{//外聘FB1605、合作人员FB1609
                    zzxCost = BigDecimal.valueOf(costSetMap.get("cost2"));
                }

                classTimeAmount += BigDecimal.valueOf(classTime7).multiply(zzxCost).doubleValue();

                BigDecimal zkCost = BigDecimal.ZERO;//正课费用
                if("FB1601".equals(teacher.getEmployType())){
                    zkCost = BigDecimal.valueOf(costSetMap.get("cost3"));
                }else{//外聘FB1605、合作人员FB1609
                    zkCost = BigDecimal.valueOf(costSetMap.get("cost4"));
                }

                double classTime = classTime8
                        + courseTimeTypeTime;
                classTimeAmount += BigDecimal.valueOf(classTime).multiply(zkCost).doubleValue();

                BigDecimal wzxCost = BigDecimal.ZERO;//晚自习费用
                if("FB1601".equals(teacher.getEmployType())){
                    wzxCost = BigDecimal.valueOf(costSetMap.get("cost5"));
                }else{//外聘FB1605、合作人员FB1609
                    wzxCost = BigDecimal.valueOf(costSetMap.get("cost6"));
                }
                classTimeAmount += BigDecimal.valueOf(classTime9).multiply(wzxCost).doubleValue();
                /**
                 * 顶课：
                 * 1、流程发起人（被顶老师）的课被顶课老师上了，那么被顶老师的课时就会-1，顶课老师课时就会+1
                 * 2、顶课老师会根据规则额外加一笔费用，根据设置1课时额外加4元
                 * 3、顶课类型为事假和病假的时候会扣除被顶老师的费用，根据设置为扣除4元
                 */
                BigDecimal reduceTimeAmount = BigDecimal.ZERO;
                {
                    //计算该老师发起的事假、病假顶课数据
                    List<CourseListVo> substituteList = allSubstituteList.stream().filter(x -> x.getExchangeTeacherId().equals(teacher.getId()))
                            .collect(Collectors.toList());
                    Double reduceTime = 0d;//顶课课时
                    Double dkClassTime = 0d;//去顶课的数量,所有顶课数量
                    for (CourseListVo courseListVo : substituteList) {
                        Map<LocalDate, String> deleteDates = deleteMap.get(courseListVo.getClassId());
                        if(deleteDates != null && deleteDates.get(courseListVo.getScheduleDate()) != null && deleteDates.get(courseListVo.getScheduleDate()).contains(courseListVo.getTimeNumber())){
                            continue;
                        }
                        reduceTime += weightSetMap.get(courseListVo.getShortName());
                        dkClassTime += weightSetMap.get(courseListVo.getShortName());
                    }


                    Double bdkClassTime = 0d;//发起顶课的数量，只统计事假和病假的数量
                    substituteList = allSubstituteList.stream().filter(x -> x.getTeacherId().equals(teacher.getId().toString()))
                            .collect(Collectors.toList());
                    for (CourseListVo courseListVo : substituteList) {
                        Map<LocalDate, String> deleteDates = deleteMap.get(courseListVo.getClassId());
                        if(deleteDates != null && deleteDates.get(courseListVo.getScheduleDate()) != null && deleteDates.get(courseListVo.getScheduleDate()).contains(courseListVo.getTimeNumber())){
                            continue;
                        }
                        reduceTime = reduceTime - weightSetMap.get(courseListVo.getShortName());
                        if("sick _leave".equals(courseListVo.getReason()) || "leave_absence".equals(courseListVo.getReason())){
                            bdkClassTime += weightSetMap.get(courseListVo.getShortName());
                        }
                    }
                    record.setClassTime10(reduceTime);

                    BigDecimal dkCost = BigDecimal.ZERO;//顶课费用，顶课老师加钱
                    if("FB1601".equals(teacher.getEmployType())){
                        dkCost = BigDecimal.valueOf(costSetMap.get("cost11"));
                    }else{//外聘FB1605、合作人员FB1609
                        dkCost = BigDecimal.valueOf(costSetMap.get("cost12"));
                    }
                    reduceTimeAmount = BigDecimal.valueOf(dkClassTime).multiply(dkCost);
                    BigDecimal bdkCost = BigDecimal.ZERO;//顶课费用，被顶课老师扣钱
                    if("FB1601".equals(teacher.getEmployType())){
                        bdkCost = BigDecimal.valueOf(costSetMap.get("cost13")==null?0:costSetMap.get("cost13"));
                    }else{//外聘FB1605、合作人员FB1609
                        bdkCost = BigDecimal.valueOf(costSetMap.get("cost14")==null?0:costSetMap.get("cost14"));
                    }
                    //计算被扣除的费用
                    reduceTimeAmount = reduceTimeAmount.subtract(bdkCost.multiply(BigDecimal.valueOf(bdkClassTime)));
                }

                record.setClassTimeAmount(classTimeAmount);
                //计算超出的课时，需要先计算出每个周的开始结束时间
                BigDecimal ccksCost = BigDecimal.ZERO;
                if("FB1601".equals(teacher.getEmployType())){
                    ccksCost = BigDecimal.valueOf(costSetMap.get("cost9"));
                }else{//外聘FB1605、合作人员FB1609
                    ccksCost = BigDecimal.valueOf(costSetMap.get("cost10"));
                }
                //计算出总的超出课时并计算超课时费
                beyondClassTimeAmount = BigDecimal.valueOf(allCcksTime).multiply(ccksCost).doubleValue();
                if(!"FB1601".equals(teacher.getEmployType())){
                    record.setBeyondClassTimeAmount(beyondClassTimeAmount);
                }

                //计算总金额，课时费+超课时费
                Double totalAmount = classTimeAmount + beyondClassTimeAmount + reduceTimeAmount.doubleValue();
                record.setTotalAmount(totalAmount);
                record.setAllClassTimeData(calculateClassTime(allClassTimeDataArray, weightSetMap, allDateList, new ArrayList<>(CourseTimeTypeMap.values())));
                record.setCreateDate(new Date());

                insertList.add(record);
            }

            if(!insertList.isEmpty()){
                //先删除
                String sql = "delete from class_time_statistics_record where class_time_statistics_id = " + statistics.getId();
                SqlRunnerAdapter.db().delete(sql);
                for (ClassTimeStatisticsRecord record : insertList) {
                    recordMapper.insert(record);
                }
            }
            statistics.setAllClassTime(insertList.stream().filter(x -> x.getAllClassTime() != null).mapToDouble(ClassTimeStatisticsRecord::getAllClassTime).sum());
            statistics.setTotalAmount(insertList.stream().filter(x -> x.getTotalAmount() != null).mapToDouble(ClassTimeStatisticsRecord::getTotalAmount).sum());
            statistics.setBeyondClassTimeAmount(insertList.stream().filter(x -> x.getBeyondClassTimeAmount() != null).mapToDouble(ClassTimeStatisticsRecord::getBeyondClassTimeAmount).sum());
            statistics.setClassTimeAmount(insertList.stream().filter(x -> x.getClassTimeAmount() != null).mapToDouble(ClassTimeStatisticsRecord::getClassTimeAmount).sum());
        }catch (Exception e){
            Log.error(e.getMessage(), e);
            throw new MyException("统计出错，请联系管理员");
        }finally {
            statistics.setStatus(1);
            this.updateById(statistics);
        }
        return true;
    }

    @Override
    public Boolean lockData(Long id) {
        ClassTimeStatistics statistics = this.getById(id);
        return null;
    }

    @Override
    public List<ClassTimeStatisticsRecordVo> getRecordList(Long id) {
        return this.baseMapper.getRecordList(id);
    }

    @Override
    public List<CourseClassTimeStatisticsRecordVo> getCourseRecordList(CourseRecordDto dto) {
        return this.baseMapper.getCourseRecordList(dto);
    }

    /**
     * 导出课时统计明细
     */
    @Override
    public byte[] recordExport(Long id) {
        try {
            //1、查询数据
            List<ClassTimeStatisticsRecordVo> recordList = this.baseMapper.getRecordList(id);
            //计算出一共有多少周
            ClassTimeStatistics statistics = this.getById(id);
            List<WeekTimeRangeVo> weekTimeRangeVos = calculateNaturalWeeks(statistics.getStartDate(), statistics.getEndDate());

            List<DictionaryDetail> CourseTimeTypeList = dictionaryService.list(
                    new QueryWrapper<DictionaryDetail>().lambda()
                            .eq(DictionaryDetail::getItemId, 1833772737004875778L)
                            .eq(DictionaryDetail::getDeleteMark, DeleteMark.NODELETE.getCode())
                            .orderByAsc(DictionaryDetail::getCode)
            );

            /**
             * 2、将查询出来的数据按照表头转换成二维数组
             * 前端固定表头：序号、工号、姓名、教研会、督导听课、临近三年退休政策、出题、阅卷、周末培优、早自习、正课(含调顶课时）、晚辅(含调顶课时）、顶课、调课
             * 中间动态表头：每周，总课时(含调顶课节)、顶课(节)、调课(节)、晚辅(节)......课时晚辅合计
             * 后端固定表头：总课时、课时费(元)、外聘教师超课时费(元)、总金额(元)
             */
            //计算一共多少列
            int column = 12 + CourseTimeTypeList.size() + (4 * weekTimeRangeVos.size());
            List<ArrayList<String>> dataList = new ArrayList<>();
            Integer sortCode = 1;
            JsonParser parser = new JsonParser();
            for (ClassTimeStatisticsRecordVo recordVo : recordList) {
                ArrayList<String> rowData = new ArrayList<>();
                rowData.add(sortCode.toString());//序号
                rowData.add(recordVo.getUserName());//工号
                rowData.add(recordVo.getName());//姓名
                JsonObject courseTimeTypeJson = parser.parse(recordVo.getCourseTimeTypeData()).getAsJsonObject();
                for (DictionaryDetail dictionaryDetail : CourseTimeTypeList) {
                    if (courseTimeTypeJson.get(dictionaryDetail.getName()) == null) {
                        continue;
                    }
                    Double courseTimeTypeTime = courseTimeTypeJson.get(dictionaryDetail.getName()).getAsDouble();
                    rowData.add(courseTimeTypeTime.toString());//教研会
                }

//                rowData.add(recordVo.getClassTime1()==null?"":recordVo.getClassTime1().toString());//教研会
//                rowData.add(recordVo.getClassTime2()==null?"":recordVo.getClassTime2().toString());//督导听课
//                rowData.add(recordVo.getClassTime3()==null?"":recordVo.getClassTime3().toString());//临近三年退休政策
//                rowData.add(recordVo.getClassTime4()==null?"":recordVo.getClassTime4().toString());//出题
//                rowData.add(recordVo.getClassTime5()==null?"":recordVo.getClassTime5().toString());//阅卷
//                rowData.add(recordVo.getClassTime6()==null?"":recordVo.getClassTime6().toString());//周末培优

                rowData.add(recordVo.getClassTime7()==null?"":recordVo.getClassTime7().toString());//早自习
                rowData.add(recordVo.getClassTime8()==null?"":recordVo.getClassTime8().toString());//正课(含调顶课时）
                rowData.add(recordVo.getClassTime9()==null?"":recordVo.getClassTime9().toString());//晚辅(含调顶课时）
                rowData.add(recordVo.getClassTime10()==null?"":recordVo.getClassTime10().toString());//顶课
                rowData.add(recordVo.getClassTime11()==null?"":recordVo.getClassTime11().toString());//调课

                //每周数据
                JsonObject weekDataJson = parser.parse(recordVo.getWeekData()).getAsJsonObject();
                JsonArray weekDataArray = weekDataJson.getAsJsonArray("weekData");
                for (int i = 0; i < weekDataArray.size(); i ++){
                    JsonObject jsonElement = weekDataArray.get(i).getAsJsonObject();
                    rowData.add(jsonElement.get("zkTimes").getAsString());//总课时(含调顶课节)
                    rowData.add(jsonElement.get("dkTimes").getAsString());//顶课(节)
                    rowData.add(jsonElement.get("tkTimes").getAsString());//调课(节)
                    rowData.add(jsonElement.get("wzxTimes").getAsString());//晚辅(节)
                }
                rowData.add(weekDataJson.get("allTimes").getAsString());//课时晚辅合计

                rowData.add(recordVo.getAllClassTime().toString());//总课时
                rowData.add(recordVo.getClassTimeAmount().toString());//课时费(元)
                rowData.add(recordVo.getBeyondClassTimeAmount()==null?"":recordVo.getBeyondClassTimeAmount().toString());//外聘教师超课时费(元)
                rowData.add(recordVo.getTotalAmount().toString());//总金额(元)
                dataList.add(rowData);
                sortCode ++;
            }

            // 创建一个新的工作簿
            Workbook workbook = new XSSFWorkbook();
            // 创建一个工作表(sheet)
            String sheetName = "数据";
            Sheet sheet = workbook.createSheet(sheetName);

            // 创建一个字体对象
            Font font = workbook.createFont();
            font.setBold(true);// 设置为粗体
            font.setFontName("宋体");
            //font.setColor(IndexedColors.RED.getIndex()); // 设置字体颜色为红色
            font.setFontHeightInPoints((short)24);


            // 创建一个单元格样式对象
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFont(font); // 将字体应用到样式
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            // 第一行表头
            {
                Row row = sheet.createRow(0);
                //合并第一行的列
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1));

                //3、处理表头
                String title1 = statistics.getYear() + "年" + statistics.getMonth() + "月课时统计";
                // 创建单元格并设置值
                Cell cell = row.createCell(0);
                cell.setCellValue(title1);
                cell.setCellStyle(cellStyle);

            }

            //第二行表头
            createSecondTitle(workbook, sheet, statistics, weekTimeRangeVos.size() * 4 + 1, CourseTimeTypeList);

            //第三行表头
            createThirdTitle(workbook, sheet, statistics.getMonth(), weekTimeRangeVos, 7 + CourseTimeTypeList.size());
            //第四行表头
            createFourthTitle(workbook, sheet, weekTimeRangeVos, 7 + CourseTimeTypeList.size());
            int dataRowNumber = 4;
            //设置样式
            font = workbook.createFont();
            font.setBold(false);// 设置为粗体
            font.setFontName("宋体");
            //font.setColor(IndexedColors.RED.getIndex()); // 设置字体颜色为红色
            font.setFontHeightInPoints((short)12);


            // 创建一个单元格样式对象
            cellStyle = workbook.createCellStyle();
            cellStyle.setFont(font); // 将字体应用到样式
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            for (ArrayList<String> rowData : dataList) {
                Row dataRow = sheet.createRow(dataRowNumber);
                for (int i = 0; i < rowData.size(); i ++){
                    Cell row1cell2 = dataRow.createCell(i);
                    String value = rowData.get(i);
                    if("0".equals(value) || "0.0".equals(value)){
                        value = "";
                    }
                    row1cell2.setCellValue(value);
                    row1cell2.setCellStyle(cellStyle);
                }

                dataRowNumber ++;
            }
            //写入文件
            ByteArrayOutputStream bot = new ByteArrayOutputStream();
            workbook.write(bot);
            return bot.toByteArray();
        }catch (Exception e){
            Log.error(e.getMessage(), e);
            throw new MyException("导出出错，请联系管理员");
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean refreshCourseRecord(ClassTimeStatistics statistics) {
        try {
            // 1、查询教师
            List<TeacherListVo> teacherList = this.baseMapper.getTeacherList();

            //查询课程数据，排除开节假日的数据
            List<CourseListVo> allCourseList = this.baseMapper.getCourseList(statistics);
            allCourseList.addAll(this.baseMapper.getHolidayReplaceCourseList(statistics));

            //查询删除课时的数据，将每个班删除的具体日期统计出来
            List<ClassTimeDelete> deleteList = deleteService.list(
                    new QueryWrapper<ClassTimeDelete>().lambda()
                            .ne(ClassTimeDelete::getStatus, 2)
                            .eq(ClassTimeDelete::getDeleteMark, DeleteMark.NODELETE.getCode())
                            .eq(ClassTimeDelete::getEnabledMark, EnabledMark.ENABLED.getCode())
            );
            Map<Long, List<ClassTimeDelete>> deleteDataMap = deleteList.stream().collect(Collectors.groupingBy(ClassTimeDelete::getClassId));
            Map<Long, Map<LocalDate, String>> deleteMap = new HashMap<>();//将每个班级计算出来所有被删除的日期存入map
            for (Long classId : deleteDataMap.keySet()) {
                Map<LocalDate, String> dateMap = new HashMap<>();
                for (ClassTimeDelete classTimeDelete : deleteDataMap.get(classId)) {
                    LocalDate currentDate = classTimeDelete.getStartDate();
                    while (!currentDate.isAfter(classTimeDelete.getEndDate())) {
                        dateMap.put(currentDate, classTimeDelete.getTimePeriod());
                        currentDate = currentDate.plusDays(1); // 增加一天
                    }
                }
                //去重并存到map中
                deleteMap.put(classId, dateMap);
            }
            JsonParser parser = new JsonParser();

            //权重设置jsonArray
            JsonArray weightSetArray = parser.parse(statistics.getWeightSetJson()).getAsJsonArray();
            Map<String, Double> weightLabelMap = new LinkedHashMap<>();
            Map<String, Double> weightFieldMap = new LinkedHashMap<>();
            for (JsonElement jsonElement : weightSetArray) {
                JsonObject object = jsonElement.getAsJsonObject();
                weightLabelMap.put(object.get("label").getAsString(), object.get("value").getAsDouble());
                weightFieldMap.put(object.get("field").getAsString(), object.get("value").getAsDouble());
            }

            //查询行政工作量并转成map
            List<ClassTimeStatisticsAdministration> administrationList = administrationMapper.selectList(
                    new QueryWrapper<ClassTimeStatisticsAdministration>().lambda()
                            .eq(ClassTimeStatisticsAdministration::getClassTimeStatisticsId, statistics.getId())
                            .eq(ClassTimeStatisticsAdministration::getDeleteMark, DeleteMark.NODELETE.getCode())
                            .eq(ClassTimeStatisticsAdministration::getEnabledMark, EnabledMark.ENABLED.getCode())
            );
            Map<Long, Double> administrationMap = administrationList.stream().collect(Collectors.toMap(ClassTimeStatisticsAdministration::getUserId, ClassTimeStatisticsAdministration::getWorkload));


            List<ClassTimeStatisticsRecord> insertList = new ArrayList<>();
            for (TeacherListVo teacher : teacherList) {
                ClassTimeStatisticsRecord record = new ClassTimeStatisticsRecord();
                record.setUserId(teacher.getId());
                record.setClassTimeStatisticsId(statistics.getId());
                record.setEmployType(teacher.getEmployType());
                record.setClassTime1(statistics.getWeeks());

                //早自习、正课、晚辅、顶课、调课
                Double classTime7 = 0D,classTime8 = 0D,classTime9 = 0D;

                List<String> zkList = Arrays.asList("上1","上2","上3","上4","下1","下2","下3","下4");
                List<String> wzxList = Arrays.asList("晚1","晚2","晚3");
                //查询出老师的课程
                List<CourseListVo> courseList = allCourseList.stream()
                        .filter(x -> x.getTeacherId().contains(teacher.getId().toString()))
                        .collect(Collectors.toList());

                //循环，统计出各项数据
                for (CourseListVo courseListVo : courseList) {
                    Map<LocalDate, String> deleteDates = deleteMap.get(courseListVo.getClassId());
                    if(deleteDates != null && deleteDates.containsKey(courseListVo.getScheduleDate()) && deleteDates.get(courseListVo.getScheduleDate()) != null
                            && deleteDates.get(courseListVo.getScheduleDate()).contains(courseListVo.getTimeNumber()) ){
                        continue;
                    }

                    if("早自习".equals(courseListVo.getShortName())){
                        classTime8 += weightLabelMap.get(courseListVo.getShortName()) == null ? 0d : weightLabelMap.get(courseListVo.getShortName());
                    }else if(zkList.contains(courseListVo.getShortName())){
                        classTime8 += weightLabelMap.get(courseListVo.getShortName()) == null ? 0d : weightLabelMap.get(courseListVo.getShortName());
                    }else if(wzxList.contains(courseListVo.getShortName())){
                        classTime9 += weightLabelMap.get(courseListVo.getShortName()) == null ? 0d : weightLabelMap.get(courseListVo.getShortName());
                    }
                }
                record.setClassTime7(classTime7);
                record.setClassTime8(classTime8);
                record.setClassTime9(classTime9);
                //计算总课时：正课课时+晚辅
                Double allTimes = classTime8 + classTime9;
                record.setAllClassTime(allTimes);
                //计算周平均课时：总课时÷总周次
                double classTime2 = BigDecimal.valueOf(allTimes).divide(BigDecimal.valueOf(record.getClassTime1()),2, RoundingMode.HALF_UP).doubleValue();
                record.setClassTime2(classTime2);
                //行政工作量
                record.setClassTime3(administrationMap.get(record.getUserId()));

                //总平均课时
                Double classTime4 = (record.getClassTime3()==null?0D:record.getClassTime3()) + (record.getClassTime2()==null?0D:record.getClassTime2());
                record.setClassTime4(classTime4);

                Double time13 = weightFieldMap.get("time13");
                if(classTime4 > time13){
                    record.setClassTime5(time13);
                }else{
                    record.setClassTime5(classTime4);
                }

                insertList.add(record);
            }

            if(!insertList.isEmpty()){
                Double overWorkloadNumber = statistics.getOverWorkloadNumber() == null ? 0D : statistics.getOverWorkloadNumber();
                Double time14 = weightFieldMap.get("time14");//工作量得分
                Double time13 = weightFieldMap.get("time13");//超工作量得分
                if(statistics.getOverWorkloadNumberStatus() != 1){
                    //取出最大总平均课时
                    Double maxClassTime4 = insertList.stream().mapToDouble(ClassTimeStatisticsRecord::getClassTime4).max().getAsDouble();
                    //计算超工作量基数
                    if(!maxClassTime4.equals(time13)){

                        overWorkloadNumber = BigDecimal.valueOf(time14).divide((BigDecimal.valueOf(maxClassTime4).subtract(BigDecimal.valueOf(time13))),2, RoundingMode.HALF_UP).doubleValue();
                    }
                }
                double allClassTime = insertList.stream().filter(x -> x.getAllClassTime() != null).mapToDouble(ClassTimeStatisticsRecord::getAllClassTime).sum();
                statistics.setAllClassTime(allClassTime);
                statistics.setOverWorkloadNumber(overWorkloadNumber);
                //先删除
                String sql = "delete from class_time_statistics_record where class_time_statistics_id = " + statistics.getId();
                SqlRunnerAdapter.db().delete(sql);
                for (ClassTimeStatisticsRecord record : insertList) {
                    if(overWorkloadNumber != 0D && time13 < record.getClassTime4()){
                        double classTime6 = BigDecimal.valueOf(record.getClassTime4())
                                .subtract(BigDecimal.valueOf(time13))
                                .multiply(BigDecimal.valueOf(overWorkloadNumber)).doubleValue();
                        if(classTime6 > 0d){
                            record.setClassTime6(classTime6);
                        }
                    }
                    recordMapper.insert(record);
                }
            }
        }catch (Exception e){
            Log.error(e.getMessage(), e);
            throw new MyException("刷新统计出错，请联系管理员");
        }finally {
            statistics.setStatus(1);
            this.updateById(statistics);
        }
        return true;
    }

    @Override
    public Boolean importAdministration(Long classTimeStatisticsId, MultipartFile file){
        try {
            List<Map<Integer, Object>> excelDataList = EasyExcel.read(file.getInputStream()).sheet().headRowNumber(2).doReadSync();
            List<User> teacherList = userService.list(
                    new MPJLambdaWrapper<User>()
                            .select(User::getId)
                            .select(XjrUser.class, x -> VoToColumnUtil.fieldsToColumns(XjrUser.class).contains(x.getProperty()))
                            .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
            );
            List<ClassTimeStatisticsAdministration> insertList = new ArrayList<>();
            Map<String, Long> userMap = teacherList.stream().collect(Collectors.toMap(User::getUserName, User::getId));
            for (Map<Integer, Object> rowData : excelDataList) {
                String userName = rowData.get(1).toString();
                ClassTimeStatisticsAdministration data = new ClassTimeStatisticsAdministration(){{
                    setClassTimeStatisticsId(classTimeStatisticsId);
                    setUserId(userMap.get(userName));
                    setWorkload(Double.parseDouble(rowData.get(3).toString()));
                }};
                insertList.add(data);
            }
            if(!insertList.isEmpty()){
                String sql = "delete from class_time_statistics_administration where class_time_statistics_id = " + classTimeStatisticsId;
                SqlRunnerAdapter.db().delete(sql);
                for (ClassTimeStatisticsAdministration administration : insertList) {
                    administrationMapper.insert(administration);
                }

                CompletableFuture.runAsync(() -> {
                    ClassTimeStatistics statistics = this.getById(classTimeStatisticsId);
                    refreshCourseRecord(statistics);
                });
            }
            return true;
        }catch (Exception e){
            Log.error(e.getMessage(), e);
            throw  new MyException("导入行政工作量报错，请联系管理员");
        }
    }

    @Override
    public byte[] exportAdministration(Long classTimeStatisticsId) {
        CourseRecordDto dto = new CourseRecordDto() {{
            setId(classTimeStatisticsId);
        }};
        List<CourseClassTimeStatisticsRecordVo> recordList = this.getCourseRecordList(dto);
        List<ClassTimeStatisticsAdministrationExcelVo> list = new ArrayList<>();
        int sortCode = 1;
        for (CourseClassTimeStatisticsRecordVo el : recordList) {
            ClassTimeStatisticsAdministrationExcelVo excelVo = BeanUtil.toBean(el, ClassTimeStatisticsAdministrationExcelVo.class);
            excelVo.setSortCode(sortCode);
            list.add(excelVo);
            sortCode ++;
        }
        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        EasyExcel.write(bot, ClassTimeStatisticsAdministrationExcelVo.class).automaticMergeHead(false).excelType(ExcelTypeEnum.XLSX).sheet().doWrite(list);

        return bot.toByteArray();
    }

    /**
     * 生成第二行表头
     * 前端固定表头：序号、工号、姓名、教研会、督导听课、临近三年退休政策、出题、阅卷、周末培优、早自习、正课(含调顶课时)、晚辅(含调顶课时)、顶课、调课
     * 中间动态表头：每周，总课时(含调顶课节)、顶课(节)、调课(节)、晚辅(节)......课时晚辅合计
     * 后端固定表头：总课时、课时费(元)、外聘教师超课时费(元)、总金额(元)
     */
    void createSecondTitle(Workbook workbook, Sheet sheet, ClassTimeStatistics statistics, int mergeCoulmn, List<DictionaryDetail> CourseTimeTypeList){
        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 row1 = sheet.createRow(1);
        int cellNumber = 0;
        Cell row1cell1 = row1.createCell(cellNumber);
        row1cell1.setCellValue("序号");
        row1cell1.setCellStyle(cellStyle);
        cellNumber ++;

        Cell row1cell2 = row1.createCell(cellNumber);
        row1cell2.setCellValue("工号");
        row1cell2.setCellStyle(cellStyle);
        cellNumber ++;

        Cell row1cell3 = row1.createCell(cellNumber);
        row1cell3.setCellValue("姓名");
        row1cell3.setCellStyle(cellStyle);
        cellNumber ++;

        for (DictionaryDetail detail : CourseTimeTypeList) {
            Cell row1cell4 = row1.createCell(cellNumber);
            row1cell4.setCellValue(detail.getName());
            row1cell4.setCellStyle(cellStyle);
            cellNumber ++;
        }

        Cell row1cell10 = row1.createCell(cellNumber);
        row1cell10.setCellValue("早自习");
        row1cell10.setCellStyle(cellStyle);
        cellNumber ++;

        Cell row1cell11 = row1.createCell(cellNumber);
        row1cell11.setCellValue("正课(含调顶课时)");
        row1cell11.setCellStyle(cellStyle);
        cellNumber ++;

        Cell row1cell12 = row1.createCell(cellNumber);
        row1cell12.setCellValue("晚辅(含调顶课时)");
        row1cell12.setCellStyle(cellStyle);
        cellNumber ++;

        Cell row1cell13 = row1.createCell(cellNumber);
        row1cell13.setCellValue("顶课");
        row1cell13.setCellStyle(cellStyle);
        cellNumber ++;

        Cell row1cell14 = row1.createCell(cellNumber);
        row1cell14.setCellValue("调课");
        row1cell14.setCellStyle(cellStyle);
        cellNumber ++;

        Cell row1cell15 = row1.createCell(cellNumber);
        row1cell15.setCellValue("正课课时数(平台导出" + statistics.getMonth() + "月)");
        row1cell15.setCellStyle(cellStyle);
        cellNumber ++;

        int index = cellNumber + mergeCoulmn - 1;
        Cell row1cell16 = row1.createCell(index);
        row1cell16.setCellValue("总课时");
        row1cell16.setCellStyle(cellStyle);

        Cell row1cell17 = row1.createCell(index + 1);
        row1cell17.setCellValue("课时费(元)");
        row1cell17.setCellStyle(cellStyle);

        Cell row1cell18 = row1.createCell(index + 2);
        row1cell18.setCellValue("外聘教师超课时费(元)");
        row1cell18.setCellStyle(cellStyle);

        Cell row1cell19 = row1.createCell(index + 3);
        row1cell19.setCellValue("总金额(元)");
        row1cell19.setCellStyle(cellStyle);
        //合并表头
        for(int i = 0; i < 8 + CourseTimeTypeList.size(); i ++){
            sheet.addMergedRegion(new CellRangeAddress(1, 3, i, i));
        }

        //合并中间动态表头
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 8 + CourseTimeTypeList.size(), index - 1));
        //合并前端表头
        for(int i = index; i <  index + 4; i ++){
            sheet.addMergedRegion(new CellRangeAddress(1, 3, i, i));
        }
    }

    /**
     * 生成第三行表头
     * 关于周的表头，周一日期到周二日期，第几周，最后拼接一个单独的月份
     *
     * @param month 月份
     * @param weekTimeRangeVos 周的数据，包含周一的日期周二的日期
     * @param lastIndex 前一个表头的单元格
     */
    void createThirdTitle(Workbook workbook, Sheet sheet, int month, List<WeekTimeRangeVo> weekTimeRangeVos, int lastIndex){
        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);

        //设置内容
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM.dd");
        int rowNumber = 2;
        Row row2 = sheet.createRow(rowNumber);

        for(int i = 0; i < weekTimeRangeVos.size(); i ++){
            WeekTimeRangeVo rangeVo = weekTimeRangeVos.get(i);
            String cellValue = rangeVo.getMondayDate().format(formatter) + "-"
                    + rangeVo.getSundayDate().format(formatter) + "(第" + rangeVo.getWeeks() + "周)";
            Cell row1cell1 = row2.createCell(lastIndex + 1 + (i * 4));
            row1cell1.setCellValue(cellValue);
            row1cell1.setCellStyle(cellStyle);
        }

        //合并单元格
        int monthIndex = 0;
        for(int i = 0; i < weekTimeRangeVos.size(); i ++){
            int startCoulmn = lastIndex + 1 + (i * 4);
            int endCoulmn = startCoulmn + 3;
            sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, startCoulmn, endCoulmn));
            monthIndex = endCoulmn;
        }

        Cell row1cell2 = row2.createCell(monthIndex + 1);
        row1cell2.setCellValue(month + "月");
        row1cell2.setCellStyle(cellStyle);
    }

    /**
     * 生成第四行表头
     * 表头内容：总课时(含调顶课节)、顶课(节)、调课(节)、晚辅(节)、课时晚辅合计
     *
     * @param weekTimeRangeVos 周的数据，包含周一的日期周二的日期
     * @param lastIndex 前一个表头的单元格
     */
    void createFourthTitle(Workbook workbook, Sheet sheet, List<WeekTimeRangeVo> weekTimeRangeVos, int lastIndex){
        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);

        int rowNumber = 3;
        Row row2 = sheet.createRow(rowNumber);
        int index = lastIndex + 1;
        for(int i = 0; i < weekTimeRangeVos.size(); i ++){
            Cell row1cell1 = row2.createCell(index);
            row1cell1.setCellValue("总课时(含调顶课节)");
            row1cell1.setCellStyle(cellStyle);

            index ++;
            Cell row1cell2 = row2.createCell(index);
            row1cell2.setCellValue("顶课(节)");
            row1cell2.setCellStyle(cellStyle);

            index ++;
            Cell row1cell3 = row2.createCell(index);
            row1cell3.setCellValue("调课(节)");
            row1cell3.setCellStyle(cellStyle);
            index ++;
            Cell row1cell4 = row2.createCell(index);
            row1cell4.setCellValue("晚辅(节)");
            row1cell4.setCellStyle(cellStyle);

            index ++;
        }
        Cell row1cell5 = row2.createCell(index);
        row1cell5.setCellValue("课时晚辅合计");
        row1cell5.setCellStyle(cellStyle);
    }


    //计算日期内一共几个周，并返回每周的周一日期和周日日期
    private static List<WeekTimeRangeVo> calculateNaturalWeeks(LocalDate startDate, LocalDate endDate) {
        List<WeekTimeRangeVo> result = new ArrayList<>();
        // 获取第一个周一
        LocalDate currentMonday = startDate.with(DayOfWeek.MONDAY);
        // 如果 startDate 不是周一，则用下一个周一
        if (!startDate.isEqual(currentMonday) && startDate.isAfter(currentMonday)) {
            currentMonday = currentMonday.plusWeeks(1);
        }

        int weeks = 1;
        // 遍历每个周
        while (!currentMonday.isAfter(endDate)) {
            // 计算当前周的周日
            LocalDate currentSunday = currentMonday.plusDays(6);

            // 限制周日的范围
            if (currentSunday.isAfter(endDate)) {
                currentSunday = endDate;
            }

            // 添加结果
            WeekTimeRangeVo timeRangeVo = new WeekTimeRangeVo();
            timeRangeVo.setMondayDate(currentMonday);
            timeRangeVo.setSundayDate(currentSunday);
            timeRangeVo .setWeeks(weeks);
            result.add(timeRangeVo);

            // 移动到下一个周的周一
            currentMonday = currentMonday.plusWeeks(1);
            weeks ++;
        }

        return result;
    }

    /**
     * 取出所有日期
     * @param startDate 开始时间
     * @param endDate 结束时间
     * @return 返回所有日期的集合
     */
    private static List<LocalDate> getDatesBetween(LocalDate startDate, LocalDate endDate) {
        List<LocalDate> dates = new ArrayList<>();

        long numOfDaysBetween = ChronoUnit.DAYS.between(startDate, endDate) + 1; // +1 包含结束日期
        for (long i = 0; i < numOfDaysBetween; i++) {
            dates.add(startDate.plusDays(i));
        }

        return dates;
    }

    String calculateClassTime(JsonArray allClassTimeDataArray, Map<String, Double> weightSetMap, List<LocalDate> allDateList, List<String> CourseTimeTypeSet) throws JsonProcessingException {
        JsonObject result = new JsonObject();
        //计算纵向的合计
        Double allClassTime = 0d;
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM.dd");
        JsonArray rowTitle = new JsonArray();
        for (LocalDate localDate : allDateList) {
            JsonObject rowTitleJson = new JsonObject();
            rowTitleJson.addProperty("scheduleDate", localDate.format(formatter));
            Double sum = 0d;
            for (JsonElement jsonElement : allClassTimeDataArray) {
                JsonObject object = jsonElement.getAsJsonObject();
                LocalDate scheduleDate = LocalDate.parse(object.get("scheduleDate").getAsString());
                if(!scheduleDate.equals(localDate)){
                    continue;
                }
                String adjustType = object.get("adjustType").getAsString();
                if("course_delete".equals(adjustType)){
                    continue;
                }
                if(weightSetMap.get(object.get("type").getAsString()) == null){
                    sum += object.get("content").getAsDouble();
                    allClassTime += object.get("content").getAsDouble();
                }else{
                    sum += weightSetMap.get(object.get("type").getAsString());
                    allClassTime += weightSetMap.get(object.get("type").getAsString());
                }
            }
            rowTitleJson.addProperty("content", sum);
            rowTitleJson.addProperty("type", "");
            rowTitleJson.addProperty("adjustType", "");
            rowTitle.add(rowTitleJson);
        }
        result.add("rowTitle", rowTitle);
        
        //计算横向的合计
        JsonArray columnTitle = new JsonArray();
        for (String type : weightSetMap.keySet()) {
            Double sum = 0d;
            JsonObject columnTitleJson = new JsonObject();
            for (JsonElement jsonElement : allClassTimeDataArray) {
                JsonObject object = jsonElement.getAsJsonObject();
                String typeStr = object.get("type").getAsString();

                if(!type.equals(typeStr)){
                    continue;
                }
                String adjustType = object.get("adjustType").getAsString();
                if("course_delete".equals(adjustType)){
                    continue;
                }
                sum += weightSetMap.get(typeStr);
            }
            columnTitleJson.addProperty("type", type);
            columnTitleJson.addProperty("content", sum);
            columnTitleJson.addProperty("scheduleDate", "");
            columnTitleJson.addProperty("adjustType", "");
            columnTitle.add(columnTitleJson);
        }
        for (String courseTimeType : CourseTimeTypeSet) {
            double sum = 0d;
            JsonObject columnTitleJson = new JsonObject();
            for (JsonElement jsonElement : allClassTimeDataArray) {
                JsonObject object = jsonElement.getAsJsonObject();
                String typeStr = object.get("type").getAsString();
                if (!courseTimeType.equals(typeStr)) {
                    continue;
                }
                String adjustType = object.get("adjustType").getAsString();
                if("course_delete".equals(adjustType)){
                    continue;
                }
                sum += object.get("content").getAsDouble();
            }
            columnTitleJson.addProperty("type", courseTimeType);
            columnTitleJson.addProperty("content", sum);
            columnTitleJson.addProperty("scheduleDate", "");
            columnTitleJson.addProperty("adjustType", "");
            columnTitle.add(columnTitleJson);
        }

        result.add("columnTitle", columnTitle);

        result.add("data", allClassTimeDataArray);


        ObjectMapper objectMapper = new ObjectMapper();
        JsonNode rootNode = objectMapper.readTree(columnTitle.toString());
        double sum = 0;

        // 遍历 JSON 数组中的每一个元素
        Iterator<JsonNode> elements = rootNode.elements();
        while (elements.hasNext()) {
            JsonNode element = elements.next();

            // 获取 "value" 键对应的数值，并累加到总和中
            if (element.has("content") && element.get("content").isNumber()) {
                sum += element.get("content").asDouble();
            }
        }


        result.addProperty("allClassTime", sum);
        return result.toString();
    }

}