api/src/main/java/com/xjrsoft/module/attendance/service/impl/AttendanceStatisticsServiceImpl.java

package com.xjrsoft.module.attendance.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.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.yulichang.base.MPJBaseServiceImpl;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
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.DeleteMark;
import com.xjrsoft.common.exception.MyException;
import com.xjrsoft.common.mybatis.SqlRunnerAdapter;
import com.xjrsoft.common.utils.VoToColumnUtil;
import com.xjrsoft.module.attendance.dto.AddAttendanceStatisticsDto;
import com.xjrsoft.module.attendance.dto.AttendanceStatisticsPageDto;
import com.xjrsoft.module.attendance.dto.AttendanceStatisticsRecordDto;
import com.xjrsoft.module.attendance.entity.AttendanceRuleDetails;
import com.xjrsoft.module.attendance.entity.AttendanceStatistics;
import com.xjrsoft.module.attendance.entity.AttendanceStatisticsRecord;
import com.xjrsoft.module.attendance.entity.AttendanceUserRelation;
import com.xjrsoft.module.attendance.entity.TeacherAttendanceRecord;
import com.xjrsoft.module.attendance.mapper.AttendanceStatisticsMapper;
import com.xjrsoft.module.attendance.service.IAttendanceRuleCategoryService;
import com.xjrsoft.module.attendance.service.IAttendanceStatisticsRecordService;
import com.xjrsoft.module.attendance.service.IAttendanceStatisticsService;
import com.xjrsoft.module.attendance.service.ITeacherAttendanceRecordService;
import com.xjrsoft.module.attendance.vo.AttendanceRuleDetailsUserVo;
import com.xjrsoft.module.attendance.vo.AttendanceStatisticsPageVo;
import com.xjrsoft.module.attendance.vo.AttendanceStatisticsRecordVo;
import com.xjrsoft.module.hikvision.util.OutInRecordUtil;
import com.xjrsoft.module.holiday.entity.HolidayDate;
import com.xjrsoft.module.holiday.service.IHolidayDateService;
import com.xjrsoft.module.job.AttendanceRecordTask;
import com.xjrsoft.module.organization.entity.User;
import com.xjrsoft.module.organization.service.IUserService;
import com.xjrsoft.module.organization.vo.UserIdDeptNameVo;
import com.xjrsoft.module.outint.service.ICarOutInRecordService;
import com.xjrsoft.module.outint.service.ITeacherOutInRecordService;
import com.xjrsoft.module.teacher.entity.BaseTeacher;
import com.xjrsoft.module.teacher.mapper.FaceImportMapper;
import com.xjrsoft.module.teacher.service.IWfTeacherleaveService;
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.IndexedColors;
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.text.ParseException;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
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.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @title: 考勤统计
 * @Author dzx
 * @Date: 2024-10-19
 * @Version 1.0
 */
@Service
@AllArgsConstructor
public class AttendanceStatisticsServiceImpl extends MPJBaseServiceImpl<AttendanceStatisticsMapper, AttendanceStatistics> implements IAttendanceStatisticsService {
    private final AttendanceStatisticsMapper statisticsMapper;

    private final IAttendanceStatisticsRecordService statisticsRecordService;

    private final ITeacherAttendanceRecordService recordService;

    private final IUserService userService;

    private final IAttendanceRuleCategoryService ruleCategoryService;

    private final IHolidayDateService holidayDateService;

    private final FaceImportMapper faceImportMapper;

    private final IWfTeacherleaveService wfTeacherleaveService;

    private final ITeacherOutInRecordService outInRecordService;

    private final ICarOutInRecordService carOutInRecordService;


    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean add(AddAttendanceStatisticsDto dto) {
        AttendanceStatistics statistics = BeanUtil.toBean(dto, AttendanceStatistics.class);
        statistics.setCreateDate(new Date());
        statistics.setStatus(0);
        statisticsMapper.insert(statistics);

        //1、同步出入记录的数据
        //2、固化考勤数据
        refreshRecord(statistics.getId());
        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean update(AttendanceStatistics attendanceStatistics) {
        statisticsMapper.updateById(attendanceStatistics);
        //********************************* AttendanceStatisticsRecord  增删改  开始 *******************************************/
        {
            // 查出所有子级的id
            List<AttendanceStatisticsRecord> attendanceStatisticsRecordList = statisticsRecordService.list(Wrappers.lambdaQuery(AttendanceStatisticsRecord.class).eq(AttendanceStatisticsRecord::getAttendanceStatisticsId, attendanceStatistics.getId()).select(AttendanceStatisticsRecord::getId));
            List<Long> attendanceStatisticsRecordIds = attendanceStatisticsRecordList.stream().map(AttendanceStatisticsRecord::getId).collect(Collectors.toList());
            //原有子表单 没有被删除的主键
            List<Long> attendanceStatisticsRecordOldIds = attendanceStatistics.getAttendanceStatisticsRecordList().stream().map(AttendanceStatisticsRecord::getId).filter(Objects::nonNull).collect(Collectors.toList());
            //找到需要删除的id
            List<Long> attendanceStatisticsRecordRemoveIds = attendanceStatisticsRecordIds.stream().filter(item -> !attendanceStatisticsRecordOldIds.contains(item)).collect(Collectors.toList());

            for (AttendanceStatisticsRecord attendanceStatisticsRecord : attendanceStatistics.getAttendanceStatisticsRecordList()) {
                //如果不等于空则修改
                if (attendanceStatisticsRecord.getId() != null) {
                    statisticsRecordService.updateById(attendanceStatisticsRecord);
                }
                //如果等于空 则新增
                else {
                    //已经不存在的id 删除
                    attendanceStatisticsRecord.setAttendanceStatisticsId(attendanceStatistics.getId());
                    statisticsRecordService.save(attendanceStatisticsRecord);
                }
            }
            //已经不存在的id 删除
            if (attendanceStatisticsRecordRemoveIds.size() > 0) {
                statisticsRecordService.removeBatchByIds(attendanceStatisticsRecordRemoveIds);
            }
        }
        //********************************* AttendanceStatisticsRecord  增删改  结束 *******************************************/

        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean delete(List<Long> ids) {
        statisticsMapper.deleteBatchIds(ids);
        statisticsRecordService.remove(Wrappers.lambdaQuery(AttendanceStatisticsRecord.class).in(AttendanceStatisticsRecord::getAttendanceStatisticsId, ids));

        return true;
    }

    @Override
    public Page<AttendanceStatisticsPageVo> getPage(Page<AttendanceStatisticsPageVo> page, AttendanceStatisticsPageDto dto) {
        return this.baseMapper.getPage(page, dto);
    }

    @Override
    public List<AttendanceStatisticsRecordVo> getRecordList(AttendanceStatisticsRecordDto dto) {
        return this.baseMapper.getRecordList(dto);
    }

    /**
     * 1、修改状态为统计中
     * 2、删除以前的数据
     * 3、重新计算数据并入库
     * 4、将状态改为统计完成
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean refreshRecord(Long id) {
        String remarks = null;
        int personCount = 0;
        int attendanceDays = 0;
        try {
            //1、修改状态为统计中
            AttendanceStatistics statistics = this.getById(id);
            statistics.setStatus(0);
            statistics.setModifyDate(new Date());
            this.updateById(statistics);

            List<LocalDate> dateList2 = getDatesBetween(statistics.getStartDate(), statistics.getEndDate());
            LocalDateTime now = LocalDateTime.now();

            if (dateList2.contains(now.toLocalDate()) && (statistics.getTimePeriod() == 1 && now.toLocalTime().isBefore(LocalTime.MIDNIGHT))) {
                DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                String sql = "DELETE FROM teacher_attendance_record WHERE attendance_date = '" + now.toLocalDate().format(dtf) + "'" +
                        " AND time_interval = " + statistics.getTimePeriod();
                SqlRunnerAdapter.db().delete(sql);

                OutInRecordUtil outInRecordUtil = new OutInRecordUtil();
                LocalDateTime startDateTime = now.withHour(0).withMinute(0).withSecond(0).withNano(0);
                LocalDateTime endDateTime = startDateTime.plusDays(1).plusSeconds(-1);

                Map<Long, AttendanceRuleDetailsUserVo> teacherRules = ruleCategoryService.getAllTeacherTodyRule(now.getDayOfWeek().name());

                try {
                    //教师
                    outInRecordUtil.getTeacherRecords(faceImportMapper, startDateTime, endDateTime, teacherRules);
                    //拉取车辆数据
                    outInRecordUtil.GetVehicleRecord(faceImportMapper, startDateTime, endDateTime);
                }catch (Exception e){
                    Log.error(e.getMessage());
                }


                AttendanceRecordTask recordTask = new AttendanceRecordTask();
                recordTask.teacherAttendanceRecord(startDateTime, statistics.getTimePeriod(),
                        userService,
                        ruleCategoryService,
                        holidayDateService,
                        wfTeacherleaveService,
                        outInRecordService,
                        carOutInRecordService,
                        recordService
                );
            }

            //2、删除以前的数据
            statisticsRecordService.remove(
                    new QueryWrapper<AttendanceStatisticsRecord>().lambda()
                            .eq(AttendanceStatisticsRecord::getAttendanceStatisticsId, id)
            );

            /**
             * 3、重新计算数据并入库
             */
            //3.1、根据考勤规则和统计的时间段查询这个规则下面所涉及到的老师
            List<User> userList = userService.list(
                    new MPJLambdaWrapper<User>()
                            .select(User::getId)
                            .select(User.class, x -> VoToColumnUtil.fieldsToColumns(User.class).contains(x.getProperty()))
                            .innerJoin(AttendanceUserRelation.class, AttendanceUserRelation::getUserId, User::getId)
                            .innerJoin(BaseTeacher.class, BaseTeacher::getUserId, User::getId)
                            .eq(AttendanceUserRelation::getAttendanceRuleCategoryId, statistics.getAttendanceRuleCategoryId())
                            .eq(AttendanceUserRelation::getDeleteMark, DeleteMark.NODELETE.getCode())
            );
            if (userList.isEmpty()) {
                throw new MyException("该规则下无考勤人员");
            }
            List<Long> userIds = userList.stream().map(User::getId).collect(Collectors.toList());

            //查询固化的考勤数据
            List<TeacherAttendanceRecord> attendanceRecords = recordService.list(
                    new QueryWrapper<TeacherAttendanceRecord>().lambda()
                            .eq(TeacherAttendanceRecord::getTimeInterval, statistics.getTimePeriod())
                            .in(TeacherAttendanceRecord::getUserId, userIds)
                            .between(TeacherAttendanceRecord::getAttendanceDate, statistics.getStartDate(), statistics.getEndDate())
                            .eq(TeacherAttendanceRecord::getDeleteMark, DeleteMark.NODELETE.getCode())
            );

            //3.2、准备相关需要的数据
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy年MM月dd日");
            DateTimeFormatter timeDtf = DateTimeFormatter.ofPattern("HH:mm");
            Map<String, String> weekCnMap = initWeekCn();
            Map<String, String> weekEnMap = initWeekEn();
            List<UserIdDeptNameVo> teacherDeptName = userService.getTeacherDeptName();
            Map<Long, UserIdDeptNameVo> userDeptMap = teacherDeptName.stream().collect(Collectors.toMap(UserIdDeptNameVo::getId, x -> x));

            //3.3、查询这个考勤规则下面的所有细则并转换成map
            List<AttendanceRuleDetails> ruleList = ruleCategoryService.getRules(statistics.getAttendanceRuleCategoryId());
            Map<String, AttendanceRuleDetails> ruleMap = ruleList.stream().collect(Collectors.toMap(AttendanceRuleDetails::getDateType, x -> x));

            //3.4、计算出所有的天数
            List<LocalDate> dateList = getDatesBetween(statistics.getStartDate(), statistics.getEndDate(), ruleMap, weekEnMap);
            attendanceDays = dateList.size();

            //3.5、循环计算，并存入insertList，方便后续批量入库
            List<AttendanceStatisticsRecord> insertList = new ArrayList<>();
            List<String> leaveTypeList = new ArrayList<>();
            leaveTypeList.add("事假");
            leaveTypeList.add("公假");
            leaveTypeList.add("病假");
            leaveTypeList.add("会议活动");
            for (User user : userList) {
                AttendanceStatisticsRecord record = new AttendanceStatisticsRecord();
                record.setUserId(user.getId());
                record.setAttendanceStatisticsId(statistics.getId());
                Long normalCount = 0L;
                Long leaveCount = 0L;
                Long absenteeCount = 0L;
                JsonArray daysData = new JsonArray();
                int sortCode = 1;
                for (LocalDate localDate : dateList) {
                    String dayOfWeekName = localDate.getDayOfWeek().name();
                    AttendanceRuleDetails ruleDetails = ruleMap.get(weekEnMap.get(dayOfWeekName));
                    if (ruleDetails != null && (ruleDetails.getIsAttendance() == null || ruleDetails.getIsAttendance() == 0)) {
                        continue;
                    }
                    JsonObject daysJson = new JsonObject();
                    daysJson.addProperty("date", localDate.format(dtf));
                    daysJson.addProperty("week", weekCnMap.get(dayOfWeekName));
                    String content = "";
                    if (statistics.getTimePeriod() == 1) {
                        content += "(" + ruleDetails.getAmStartTime().toLocalTime().format(timeDtf) + ")上班 ";
                    } else if (statistics.getTimePeriod() == 2) {
                        content += "(" + ruleDetails.getPmStartTime().toLocalTime().format(timeDtf) + ")上班 ";
                    }
                    List<TeacherAttendanceRecord> collect = attendanceRecords.stream()
                            .filter(el -> el.getAttendanceDate().equals(localDate) && el.getUserId().equals(user.getId())
                                    && !"不考勤".equals(el.getAttendanceStatus())
                            )
                            .collect(Collectors.toList());
                    for (TeacherAttendanceRecord attendanceRecord : collect) {
                        if ("到校".equals(attendanceRecord.getAttendanceStatus())) {
                            normalCount++;
                            content += "正常(" + attendanceRecord.getRecordTime().toLocalTime().format(timeDtf) + ")";
                        } else if (leaveTypeList.contains(attendanceRecord.getAttendanceStatus())) {
                            leaveCount++;
                            content += "请假";
                        } else {
                            absenteeCount++;
                            content += "缺勤";
                        }
                    }
                    daysJson.addProperty("content", content);
                    daysJson.addProperty("sortCode", sortCode);
                    daysData.add(daysJson);
                    sortCode++;
                }
                record.setNormalCount(normalCount);
                record.setLeaveCount(leaveCount);
                record.setAbsenteeCount(absenteeCount);
                record.setDaysData(daysData.toString());
                UserIdDeptNameVo deptNameVo = userDeptMap.get(record.getUserId());
                if (deptNameVo != null) {
                    record.setDeptIds(deptNameVo.getDeptIds());
                    record.setDeptName(deptNameVo.getDeptName());
                }

                insertList.add(record);
            }

            //3.6、插入数据
            if (!insertList.isEmpty()) {
                statisticsRecordService.saveBatch(insertList);
                personCount = insertList.size();
            }

            return true;
        } catch (Exception e) {
            Log.error(e.getMessage(), e);
            if (e.getClass().equals(MyException.class)) {
                throw new MyException(e.getMessage());
            }else if(e.getClass().equals(ParseException.class) || e.getClass().equals(ClassCastException.class)){
                remarks = "未能拉取海康数据，统计可能不准确";
            } else {
                throw new MyException("刷新出错，请联系管理员");
            }
        }finally {
            //4、将状态改为统计完成
            AttendanceStatistics statistics = this.getById(id);
            statistics.setStatus(1);
            statistics.setModifyDate(new Date());
            statistics.setPersonCount(personCount);
            statistics.setAttendanceDays(attendanceDays);
            statistics.setRemarks(remarks);
            this.updateById(statistics);
            return true;
        }
    }


    private List<LocalDate> getDatesBetween(LocalDate startDate, LocalDate endDate, Map<String, AttendanceRuleDetails> ruleMap, Map<String, String> weekEnMap) {
        List<LocalDate> dates = new ArrayList<>();
        List<Integer> list = Arrays.asList(1, 3);
        List<HolidayDate> holidayDates = holidayDateService.list(
                new QueryWrapper<HolidayDate>().lambda()
                        .in(HolidayDate::getStatus, list)
        );
        List<String> holidayDateList = holidayDates.stream().map(HolidayDate::getDate).collect(Collectors.toList());

        List<HolidayDate> workHolidayDates = holidayDateService.list(
                new QueryWrapper<HolidayDate>().lambda()
                        .eq(HolidayDate::getStatus, 2)
        );
        List<String> workHolidayDateListg = workHolidayDates.stream().map(HolidayDate::getDate).collect(Collectors.toList());

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        List<String> weekList = Arrays.asList("SUNDAY", "SATURDAY");
        long numOfDaysBetween = ChronoUnit.DAYS.between(startDate, endDate) + 1; // +1 包含结束日期
        for (long i = 0; i < numOfDaysBetween; i++) {
            LocalDate localDate = startDate.plusDays(i);
            AttendanceRuleDetails holidays = ruleMap.get("holidays");
            Integer holidaysIsAttendance = holidays.getIsAttendance();
            if (
                    (
                            (!workHolidayDateListg.contains(localDate.format(formatter)) && weekList.contains(localDate.getDayOfWeek().name()))
                                    || holidayDateList.contains(localDate.format(formatter)))
                            && (holidaysIsAttendance == null || holidaysIsAttendance == 0)
            ) {
                continue;
            }
            AttendanceRuleDetails ruleDetails = ruleMap.get(weekEnMap.get(localDate.getDayOfWeek().name()));
            if (ruleDetails != null && ruleDetails.getIsAttendance() != null && ruleDetails.getIsAttendance() == 1) {
                dates.add(localDate);
            }
        }

        return dates;
    }

    private 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++) {
            LocalDate localDate = startDate.plusDays(i);
            dates.add(localDate);
        }

        return dates;
    }

    private Map<String, String> initWeekCn() {
        Map<String, String> result = new HashMap<>();

        result.put("MONDAY", "周一");
        result.put("TUESDAY", "周二");
        result.put("WEDNESDAY", "周三");
        result.put("THURSDAY", "周四");
        result.put("FRIDAY", "周五");
        result.put("SATURDAY", "周六");
        result.put("SUNDAY", "周日");

        return result;
    }

    private Map<String, String> initWeekEn() {
        Map<String, String> result = new HashMap<>();

        result.put("MONDAY", "monday");
        result.put("TUESDAY", "tuesday");
        result.put("WEDNESDAY", "wednesday");
        result.put("THURSDAY", "thursday");
        result.put("FRIDAY", "friday");
        result.put("SATURDAY", "saturday");
        result.put("SUNDAY", "sunday");

        return result;
    }

    @Override
    public byte[] recordExport(Long id) throws IOException {
        AttendanceStatistics statistics = this.getById(id);
        List<AttendanceStatisticsRecordVo> recordList = this.getRecordList(new AttendanceStatisticsRecordDto() {{
            setId(id);
        }});

        List<ArrayList<String>> dataList = new ArrayList<>();
        int sortCode = 1;
        JsonParser parser = new JsonParser();
        List<String> dateList = new ArrayList<>();
        List<String> weekList = new ArrayList<>();
        for (AttendanceStatisticsRecordVo recordVo : recordList) {
            ArrayList<String> data = new ArrayList<>();
            data.add(sortCode + "");
            data.add(recordVo.getName());
            data.add(recordVo.getUserName());
            data.add(recordVo.getDeptName());
            data.add(statistics.getAttendanceDays() + "");
            data.add(recordVo.getNormalCount() + "");
            data.add(recordVo.getLeaveCount() + "");
            data.add(recordVo.getAbsenteeCount() + "");
            JsonArray daysData = parser.parse(recordVo.getDaysData()).getAsJsonArray();
            for (JsonElement daysDatum : daysData) {
                JsonObject daysJson = daysDatum.getAsJsonObject();
                data.add(daysJson.get("content").getAsString());
                if (!dateList.contains(daysJson.get("date").getAsString())) {
                    dateList.add(daysJson.get("date").getAsString());
                }
                if (!weekList.contains(daysJson.get("week").getAsString())) {
                    weekList.add(daysJson.get("week").getAsString());
                }
            }
            dataList.add(data);
            sortCode++;
        }

        int allColumn = 8 + statistics.getAttendanceDays();//总列数
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表(sheet)
        String sheetName = "数据";
        Sheet sheet = workbook.createSheet(sheetName);

        // 第一行表头
        createFirstTitle(workbook, sheet, allColumn - 1);
        // 第二行表头
        createSecondTitle(workbook, sheet, statistics, 7);
        // 第三行表头
        createThirdTitle(workbook, sheet, weekList);
        // 第四行表头
        createFourthTitle(workbook, sheet, 8, dateList);
        //生成数据
        int dataRowNumber = 4;

        for (ArrayList<String> rowData : dataList) {
            Row dataRow = sheet.createRow(dataRowNumber);
            for (int i = 0; i < rowData.size(); i++) {
                if (i > 7) {
                    sheet.autoSizeColumn(i);
                }
                String content = rowData.get(i);
                Font font = workbook.createFont();
                font.setBold(false);// 设置为粗体
                font.setFontName("宋体");
                if (content != null && content.contains("缺勤")) {
                    font.setColor(IndexedColors.RED.getIndex()); // 设置字体颜色为红色
                } else if (content != null && content.contains("正常")) {
                    font.setColor(IndexedColors.BLUE.getIndex()); // 设置字体颜色为蓝色
                } else if (content != null && content.contains("请假")) {
                    font.setColor(IndexedColors.BROWN.getIndex()); // 设置字体颜色为橙色
                }
                font.setFontHeightInPoints((short) 12);

                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setFont(font); // 将字体应用到样式
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                if (i > 3 && i < 8) {
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                }

                Cell row1cell2 = dataRow.createCell(i);
                row1cell2.setCellValue(content);
                row1cell2.setCellStyle(cellStyle);
            }

            dataRowNumber++;
        }
        //写入文件
        ByteArrayOutputStream bot = new ByteArrayOutputStream();
        workbook.write(bot);
        return bot.toByteArray();
    }

    /**
     * 创建第一行表头
     *
     * @param mergeCoulmn 合并后面多少列(不包含自己)
     */
    void createFirstTitle(Workbook workbook, Sheet sheet, int mergeCoulmn) {
        int rowNumber = 0;
        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(rowNumber);
        //合并第一行的列
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, mergeCoulmn));

        //3、处理表头
        String title1 = "坐班考勤 - 汇总统计";
        // 创建单元格并设置值
        Cell cell = row.createCell(0);
        cell.setCellValue(title1);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 生成第二行表头
     *
     * @param mergeCoulmn 合并后面多少列(不包含自己)
     */
    void createSecondTitle(Workbook workbook, Sheet sheet, AttendanceStatistics statistics, int mergeCoulmn) {
        int rowNumber = 1;
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);

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

        Row row1 = sheet.createRow(rowNumber);
        Cell row1cell1 = row1.createCell(0);
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        String content = "查询的考勤日期范围：" + statistics.getStartDate().format(dtf) + "至" + statistics.getEndDate() + ";";
        if (statistics.getTimePeriod() == 1) {
            content += "时间段：上午";
        } else if (statistics.getTimePeriod() == 2) {
            content += "时间段：下午";
        }
        row1cell1.setCellValue(content);
        row1cell1.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, mergeCoulmn));
    }

    /**
     * 生成第三行表头
     * 表头：序号、姓名、工号、部门、正常考勤次数、请假次数、缺勤次数，后续周几
     *
     * @param weekList 周几
     */
    void createThirdTitle(Workbook workbook, Sheet sheet, List<String> weekList) {
        int rowNumber = 2;
        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(rowNumber);
        Cell row1cell1 = row1.createCell(0);
        row1cell1.setCellValue("序号");
        row1cell1.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 0, 0));


        Cell row1cell2 = row1.createCell(1);
        row1cell2.setCellValue("姓名");
        row1cell2.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 1, 1));

        Cell row1cell3 = row1.createCell(2);
        row1cell3.setCellValue("工号");
        row1cell3.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 2, 2));

        Cell row1cell4 = row1.createCell(3);
        row1cell4.setCellValue("组织机构");
        row1cell4.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 3, 3));

        Cell row1cell8 = row1.createCell(4);
        row1cell8.setCellValue("考勤组应出勤天数");
        row1cell8.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 4, 4));

        Cell row1cell5 = row1.createCell(5);
        row1cell5.setCellValue("正常考勤次数");
        row1cell5.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 5, 5));

        Cell row1cell6 = row1.createCell(6);
        row1cell6.setCellValue("请假次数");
        row1cell6.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 6, 6));

        Cell row1cell7 = row1.createCell(7);
        row1cell7.setCellValue("缺勤次数");
        row1cell7.setCellStyle(cellStyle);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber + 1, 7, 7));

        for (int i = 0; i < 7; i++) {
            sheet.autoSizeColumn(i);
        }
        sheet.setColumnWidth(4, 24 * 256);
        sheet.setColumnWidth(5, 24 * 256);

        int cellNumber = 8;
        for (String dayOfWeek : weekList) {
            Cell row1cell = row1.createCell(cellNumber);
            row1cell.setCellValue(dayOfWeek);
            row1cell.setCellStyle(cellStyle);
            cellNumber++;
        }
    }

    /**
     * @param startColumn 开始列
     * @param dateList    2024年10月20日
     */
    void createFourthTitle(Workbook workbook, Sheet sheet, int startColumn, List<String> dateList) {
        int rowNumber = 3;
        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(rowNumber);

        for (String date : dateList) {
            Cell row1cell = row1.createCell(startColumn);
            row1cell.setCellValue(date);
            row1cell.setCellStyle(cellStyle);
            startColumn++;
        }
    }
}