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 com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.github.yulichang.base.MPJBaseServiceImpl;
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.CourseTimeTypeEnum;
import com.xjrsoft.common.exception.MyException;
import com.xjrsoft.module.classtime.dto.AddClassTimeStatisticsDto;
import com.xjrsoft.module.classtime.entity.ClassTimeDelete;
import com.xjrsoft.module.classtime.entity.ClassTimeStatistics;
import com.xjrsoft.module.classtime.entity.ClassTimeStatisticsRecord;
import com.xjrsoft.module.classtime.entity.ClassTimeStatisticsSet;
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.ClassTimeStatisticsRecordVo;
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 lombok.AllArgsConstructor;
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 java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
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;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean add(ClassTimeStatistics classTimeStatistics) {
        statisticsMapper.insert(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 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.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、属于节假日的课程不查询
     * @param statistics
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean refreshRecord(ClassTimeStatistics statistics) {
        try {
            // 1、查询教师
            List<TeacherListVo> teacherList = this.baseMapper.getTeacherList();
            // 2、查询补课课时
            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 HashMap<>();
            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 HashMap<>();
            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<CourseListVo> allCourseList = this.baseMapper.getCourseList(statistics);

            //查询删除课时的数据，将每个班删除的具体日期统计出来
            List<ClassTimeDelete> deleteList = deleteService.list(
                    new QueryWrapper<ClassTimeDelete>().lambda()
                            .ne(ClassTimeDelete::getStatus, 2)
            );
            Map<Long, List<ClassTimeDelete>> deleteDataMap = deleteList.stream().collect(Collectors.groupingBy(ClassTimeDelete::getClassId));
            Map<Long, Set<LocalDate>> deleteMap = new HashMap<>();//将每个班级计算出来所有被删除的日期存入map
            for (Long classId : deleteDataMap.keySet()) {
                List<LocalDate> dateList = new ArrayList<>();
                for (ClassTimeDelete classTimeDelete : deleteDataMap.get(classId)) {
                    LocalDate currentDate = classTimeDelete.getStartDate();
                    while (!currentDate.isAfter(classTimeDelete.getEndDate())) {
                        dateList.add(currentDate);
                        currentDate = currentDate.plusDays(1); // 增加一天
                    }
                }
                //去重并存到map中
                deleteMap.put(classId, new HashSet<>(dateList));
            }
            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;
                //计算补充课时，算作正课课时，但是计算超出课时时不计算这部分
                for (String courseTimeType : courseTimeMap.keySet()) {
                    List<WfTeacherCourseTime> courseTimes = courseTimeMap.get(courseTimeType);
                    double sum = courseTimes.stream()
                            .filter(x -> x.getTeacherIds().contains(teacher.getId().toString()))
                            .mapToDouble(WfTeacherCourseTime::getCourseTime).sum();
                    allClassTime = allClassTime + sum;
                    if(CourseTimeTypeEnum.CTT001.getCode().equals(courseTimeType)){
                        record.setClassTime1(sum);
                    }else if(CourseTimeTypeEnum.CTT002.getCode().equals(courseTimeType)){
                        record.setClassTime2(sum);
                    }else if(CourseTimeTypeEnum.CTT003.getCode().equals(courseTimeType)){
                        record.setClassTime3(sum);
                    }else if(CourseTimeTypeEnum.CTT004.getCode().equals(courseTimeType)){
                        record.setClassTime4(sum);
                    }else if(CourseTimeTypeEnum.CTT005.getCode().equals(courseTimeType)){
                        record.setClassTime5(sum);
                    }else if(CourseTimeTypeEnum.CTT006.getCode().equals(courseTimeType)){
                        record.setClassTime6(sum);
                    }
                }
                //早自习、正课、晚辅、顶课、调课
                Double classTime7 = 0D,classTime8 = 0D,classTime9 = 0D,classTime10 = 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) {
                    //如果这个课程数据包含在被删除的课时中，跳过不计算
                    Set<LocalDate> deleteDates = deleteMap.get(courseListVo.getClassId());
                    if(deleteDates != null && deleteDates.contains(courseListVo.getScheduleDate())){
                        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());
                        }
                    }
                    JsonObject courseJson = new JsonObject();
                    courseJson.addProperty("type", courseListVo.getShortName());
                    courseJson.addProperty("scheduleDate", courseListVo.getScheduleDate().format(formatter));
                    courseJson.addProperty("content", courseListVo.getClassName() + "," + courseListVo.getCourseName());
                    courseJson.addProperty("adjustType", 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 dkTimes = 0d;
                    Double tkTimes = 0d;
                    for (CourseListVo courseListVo : courseList) {
                        LocalDate scheduleDate = courseListVo.getScheduleDate();
                        //如果这个课程数据包含在被删除的课时中，跳过不计算
                        Set<LocalDate> deleteDates = deleteMap.get(courseListVo.getClassId());
                        if(deleteDates != null && deleteDates.contains(scheduleDate)){
                            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.courseSubstitute.getCode().equals(courseListVo.getAdjustType())){
                                dkTimes += weightSetMap.get(courseListVo.getShortName());
                            }else if(CourseAdjustTypeEnum.courseExchange.getCode().equals(courseListVo.getAdjustType())){
                                tkTimes += weightSetMap.get(courseListVo.getShortName());
                            }
                        }
                    }
                    timeRangeVo.setZkTimes(zkTimes);
                    timeRangeVo.setWzxTimes(wzxTimes);
                    timeRangeVo.setDkTimes(dkTimes);
                    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());

                //计算该老师发起的事假、病假顶课数据
                List<CourseListVo> substituteList = allSubstituteList.stream().filter(x -> x.getExchangeTeacherId().equals(teacher.getId()))
                        .collect(Collectors.toList());
                Double reduceTime = 0d;
                for (CourseListVo courseListVo : substituteList) {
                    Set<LocalDate> deleteDates = deleteMap.get(courseListVo.getClassId());
                    if(deleteDates !=null && deleteDates.contains(courseListVo.getScheduleDate())){
                        continue;
                    }
                    reduceTime += weightSetMap.get(courseListVo.getShortName());
                }

                substituteList = allSubstituteList.stream().filter(x -> x.getTeacherId().equals(teacher.getId().toString()))
                        .collect(Collectors.toList());
                for (CourseListVo courseListVo : substituteList) {
                    Set<LocalDate> deleteDates = deleteMap.get(courseListVo.getClassId());
                    if(deleteDates !=null && deleteDates.contains(courseListVo.getScheduleDate())){
                        continue;
                    }
                    reduceTime = reduceTime - weightSetMap.get(courseListVo.getShortName());
                }

                record.setClassTime10(reduceTime);
                //计算总课时
                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
                        + (record.getClassTime1() == null ? 0d:record.getClassTime1())
                        + (record.getClassTime2() == null ? 0d:record.getClassTime2())
                        + (record.getClassTime3() == null ? 0d:record.getClassTime3())
                        + (record.getClassTime4() == null ? 0d:record.getClassTime4())
                        + (record.getClassTime5() == null ? 0d:record.getClassTime5())
                        + (record.getClassTime6() == null ? 0d:record.getClassTime6());
                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();

                BigDecimal reduceTimeAmount = BigDecimal.ZERO;
                BigDecimal dkCost = BigDecimal.ZERO;//顶课费用，顶课老师加钱
                if("FB1601".equals(teacher.getEmployType())){
                    dkCost = BigDecimal.valueOf(costSetMap.get("cost11"));
                }else{//外聘FB1605、合作人员FB1609
                    dkCost = BigDecimal.valueOf(costSetMap.get("cost12"));
                }
                if(record.getClassTime10() != null && record.getClassTime10() > 0){
                    reduceTimeAmount = BigDecimal.valueOf(record.getClassTime10()).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"));
                }
                //计算被扣除的费用
                if(record.getClassTime10() != null && record.getClassTime10() < 0){
                    reduceTimeAmount = bdkCost.multiply(BigDecimal.valueOf(record.getClassTime10()));
                }
                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);

                insertList.add(record);
            }

            if(!insertList.isEmpty()){
                //先删除
                recordMapper.delete(
                        new QueryWrapper<ClassTimeStatisticsRecord>().lambda()
                                .eq(ClassTimeStatisticsRecord::getClassTimeStatisticsId, statistics.getId())
                );
                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());
            statistics.setStatus(1);
            this.updateById(statistics);
            return true;
        }catch (Exception e){
            log.error(e.getMessage());
            throw new MyException("统计出错，请联系管理员");
        }
    }

    @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);
    }

    /**
     * 导出课时统计明细
     * @param id
     * @return
     */
    @Override
    public byte[] recordExport(Long id) throws IOException {
        try {
            //1、查询数据
            List<ClassTimeStatisticsRecordVo> recordList = this.baseMapper.getRecordList(id);
            //计算出一共有多少周
            ClassTimeStatistics statistics = this.getById(id);
            List<WeekTimeRangeVo> weekTimeRangeVos = calculateNaturalWeeks(statistics.getStartDate(), statistics.getEndDate());
            /**
             * 2、将查询出来的数据按照表头转换成二维数组
             * 前端固定表头：序号、工号、姓名、教研会、督导听课、临近三年退休政策、出题、阅卷、周末培优、早自习、正课(含调顶课时）、晚辅(含调顶课时）、顶课、调课
             * 中间动态表头：每周，总课时(含调顶课节)、顶课(节)、调课(节)、晚辅(节)......课时晚辅合计
             * 后端固定表头：总课时、课时费(元)、外聘教师超课时费(元)、总金额(元)
             */
            //计算一共多少列
            int column = 19 + (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());//姓名
                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);

            //第三行表头
            createThirdTitle(workbook, sheet, statistics.getMonth(), weekTimeRangeVos, 13);
            //第四行表头
            createFourthTitle(workbook, sheet, weekTimeRangeVos, 13);
            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);
                    row1cell2.setCellValue(rowData.get(i));
                    row1cell2.setCellStyle(cellStyle);
                }

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

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

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

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

        Cell row1cell4 = row1.createCell(3);
        row1cell4.setCellValue("教研会");
        row1cell4.setCellStyle(cellStyle);

        Cell row1cell5 = row1.createCell(4);
        row1cell5.setCellValue("督导听课");
        row1cell5.setCellStyle(cellStyle);

        Cell row1cell6 = row1.createCell(5);
        row1cell6.setCellValue("临近三年退休政策");
        row1cell6.setCellStyle(cellStyle);

        Cell row1cell7 = row1.createCell(6);
        row1cell7.setCellValue("出题");
        row1cell7.setCellStyle(cellStyle);

        Cell row1cell8 = row1.createCell(7);
        row1cell8.setCellValue("阅卷");
        row1cell8.setCellStyle(cellStyle);

        Cell row1cell9 = row1.createCell(8);
        row1cell9.setCellValue("周末培优");
        row1cell9.setCellStyle(cellStyle);

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

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

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

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

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

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

        int index = 14 + mergeCoulmn;
        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 < 14; i ++){
            sheet.addMergedRegion(new CellRangeAddress(1, 3, i, i));
        }

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

    /**
     * 生成第三行表头
     * 关于周的表头，周一日期到周二日期，第几周，最后拼接一个单独的月份
     *
     * @param workbook
     * @param sheet
     * @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 workbook
     * @param sheet
     * @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;
    }

}