package com.ruoyi.performance.service.impl; import cn.hutool.core.bean.BeanUtil; import cn.hutool.core.util.ObjectUtil; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.metadata.WriteSheet; import com.baomidou.mybatisplus.core.conditions.Wrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.google.common.util.concurrent.AtomicDouble; import com.ruoyi.common.core.domain.entity.User; import com.ruoyi.common.enums.*; import com.ruoyi.common.utils.api.icc.IccApiUtil; import com.ruoyi.common.utils.api.icc.model.GetResultPageRequest; import com.ruoyi.common.utils.api.icc.model.GetResultPageResponse; import com.ruoyi.performance.dto.StaffAttendanceDTO; import com.ruoyi.performance.excel.StaffAttendanceAnnotationTextExcelData; import com.ruoyi.performance.excel.StaffAttendanceExcelData; import com.ruoyi.performance.excel.handler.attendance.CommentWriteHandler; import com.ruoyi.performance.mapper.StaffAttendanceTrackingRecordMapper; import com.ruoyi.performance.pojo.StaffAttendanceTrackingRecord; import com.ruoyi.performance.service.StaffAttendanceTrackingRecordService; import com.ruoyi.performance.dto.PerformanceShiftMapDto; import com.ruoyi.performance.mapper.PerformanceShiftMapper; import com.ruoyi.performance.mapper.ShiftTimeMapper; import com.ruoyi.performance.pojo.ShiftTime; import com.ruoyi.performance.utils.HourDiffCalculator; import com.ruoyi.performance.utils.TimeDiffCalculator; import com.ruoyi.performance.vo.StaffAttendanceVO; import com.ruoyi.system.mapper.UserMapper; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.ObjectUtils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.servlet.http.HttpServletResponse; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.math.RoundingMode; import java.net.URLEncoder; import java.nio.charset.StandardCharsets; import java.time.*; import java.time.format.DateTimeFormatter; import java.util.*; import java.util.concurrent.atomic.AtomicInteger; import java.util.stream.Collectors; /** * @author 27233 * @description 针对表【staff_attendance_tracking_record(人员考勤-考勤记录)】的数据库操作Service实现 * @createDate 2026-03-09 17:42:25 */ @Slf4j @Service public class StaffAttendanceTrackingRecordServiceImpl extends ServiceImpl implements StaffAttendanceTrackingRecordService { @Autowired private IccApiUtil iccApiUtil; @Autowired private PerformanceShiftMapper performanceShiftMapper; @Autowired private UserMapper userMapper; @Autowired private ShiftTimeMapper shiftTimeMapper; private DateTimeFormatter yyyMMdd = DateTimeFormatter.ofPattern("yyyy-MM-dd"); private DateTimeFormatter HHmm = DateTimeFormatter.ofPattern("HH:mm"); private DateTimeFormatter yyyMMddHHmmss = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); private DateTimeFormatter yyyMMStr = DateTimeFormatter.ofPattern("yyyy年MM月dd日"); /** 质量部id */ private final static String deptIds = "6"; // 固定前缀 private static final String PREFIX = "ZT-"; // 数字部分固定长度 private static final int DIGIT_LENGTH = 6; private static final String holidayLeaveKeyword = "休";//休假，调休假班次关键字 private static final String personalLeaveKeyword = "事";//事假班次关键字 private static final String annualLeaveKeyword = "年";//年假班次关键字 private static final String officialTripKeyword = "公";//公差班次关键字 private static final String marriageLeaveKeyword = "婚";//婚假班次关键字 private static final String bereavementLeaveKeyword = "丧";//丧假班次关键字 private static final String sickLeaveKeyword = "病";//病假班次关键字 private static final String maternityLeaveKeyword = "产";//产假班次关键字 /** * 同步的门禁设备列表 * channel_name device_code * 10.100.22.2_门禁通道_1 1001538 * 10.100.22.3_门禁通道_1 1001539 * 10.100.22.4_门禁通道_1 1001540 * 10.100.22.5_门禁通道_1 1001541 * 10.100.22.12_门禁通道_1 1001626 * 10.100.22.13_门禁通道_1 1001627 * 10.100.22.14_门禁通道_1 1001628 * 10.100.22.15_门禁通道_1 1001629 */ private final static List syncDeviceCode = Arrays.asList("1001538", "1001539", "1001540", "1001541", "1001626", "1001627", "1001628", "1001629"); /** * 上班时间判定边界小时数 */ private final static long WORK_TIME_BOUNDARY = 2L; @Override @Transactional(rollbackFor = Exception.class) public boolean syncAttendanceRecord(LocalDateTime startDate, LocalDateTime endDate) { if (ObjectUtil.isAllEmpty(startDate, endDate)) { throw new RuntimeException("同步日期不能为空"); } // 查询已同步的记录 List trackingRecordIccIdList = baseMapper.selectIccIdList(); try { // 查询icc开放平台的考勤记录 GetResultPageRequest getResultPageRequest = new GetResultPageRequest(); getResultPageRequest.setPageNum(1); getResultPageRequest.setPageSize(9999); getResultPageRequest.setDeptIds(deptIds); getResultPageRequest.setStartSwingTime(startDate.format(yyyMMddHHmmss)); getResultPageRequest.setEndSwingTime(endDate.format(yyyMMddHHmmss)); GetResultPageResponse trackingRecordResponse = iccApiUtil.getAttendanceResultPage(getResultPageRequest); if (trackingRecordResponse.isSuccess()) { if (ObjectUtil.isNotNull(trackingRecordResponse.getData()) && !trackingRecordResponse.getData().getPageData().isEmpty()) { List recordList = trackingRecordResponse.getData().getPageData() .stream() .filter(f -> !trackingRecordIccIdList.contains(f.getId()) && syncDeviceCode.contains(f.getDeviceCode())) .map(result -> { StaffAttendanceTrackingRecord trackingRecord = new StaffAttendanceTrackingRecord(); BeanUtil.copyProperties(result, trackingRecord); trackingRecord.setIccId(result.getId()); trackingRecord.setPersonCode(restorePersonCode(result.getPersonCode())); trackingRecord.setId(null); return trackingRecord; }).collect(Collectors.toList()); if (!recordList.isEmpty()) this.saveBatch(recordList); log.info("同步ICC考勤记录条数->,{}", recordList.size()); } } else { log.error("同步ICC开放平台考勤记录错误,{}", trackingRecordResponse.getErrMsg()); } } catch (Exception e) { throw new RuntimeException(e); } return true; } /** * 查询考勤记录 * @param performanceShifts 班次信息 * @param staffAttendanceDTO 考勤查询条件 * @return */ @Override public List getAttendanceRecord(List performanceShifts, StaffAttendanceDTO staffAttendanceDTO) { // 查询打卡记录 Wrapper queryWrapper = Wrappers.lambdaQuery() .eq(StaffAttendanceTrackingRecord::getEnableReport, Boolean.TRUE) .between(ObjectUtils.allNotNull(staffAttendanceDTO.getStartDate(),staffAttendanceDTO.getEndDate()), StaffAttendanceTrackingRecord::getSwingTime, staffAttendanceDTO.getStartDate(),ObjectUtils.isNotEmpty(staffAttendanceDTO.getEndDate())?staffAttendanceDTO.getEndDate().plusDays(1L):null) .and(StringUtils.isNotEmpty(staffAttendanceDTO.getKeyword()), i -> i.like(StaffAttendanceTrackingRecord::getPersonCode, staffAttendanceDTO.getKeyword()) .or() .like(StaffAttendanceTrackingRecord::getPersonName, staffAttendanceDTO.getKeyword())); List recordList = baseMapper.selectList(queryWrapper); // 组装数据 List resultList = new ArrayList<>(); for (int i = 0; i < performanceShifts.size(); i++) { PerformanceShiftMapDto p = performanceShifts.get(i); StaffAttendanceVO vo = new StaffAttendanceVO(); // 获取对应班次小时数 if (ObjectUtil.isAllNotEmpty(p.getStartTime(), p.getEndTime()) && !recordList.isEmpty()) { double hourDiff = HourDiffCalculator.getHourDiff(p.getStartTime(), p.getEndTime()); /* * 上班时间取值： * 正常：当前班次开始前最后一次进门时间 * 异常(迟到)：无当前班次开始前进门记录，取当前班次开始后第一次进门时间 * 下班时间取值： * 正常：当前班次结束后第一次出门时间 * 异常(早退)：无当前班次结束到下一班次开始前的出门记录并且最后一次出门在当前班次时间范围内，取当前班次最后一次出门时间 */ //当前时间 LocalDateTime now = LocalDateTime.now(ZoneId.of("Asia/Shanghai")); // 当前班次开始天0点时间 LocalDateTime startDateTime = LocalDateTime.of(p.getWorkTime().toLocalDate(), LocalTime.MIN); // 当前班次结束天24点时间 LocalDateTime endDateTime = LocalDateTime.of(p.getWorkTime().toLocalDate(), LocalTime.MAX); // 当前班次开始时间 LocalTime currentShiftStartTime = LocalTime.parse(p.getStartTime(), HHmm); LocalDateTime currentShiftStartDateTime = LocalDateTime.of(p.getWorkTime().toLocalDate(), currentShiftStartTime); // 下一班次开始时间 LocalDateTime nextShiftStartDateTime = getShiftStartDateTime(p.getPersonCode(), performanceShifts, startDateTime.plusDays(1L)); if (Double.compare(hourDiff, 0) == -1) { // 如果小时差为负数，表示跨天，结束时间需加一 endDateTime = endDateTime.plusDays(1L); } // 当前班次结束时间 LocalTime currentShiftEndTime = LocalTime.parse(p.getEndTime(), HHmm); LocalDateTime currentShiftEndDateTime = LocalDateTime.of(endDateTime.toLocalDate(), currentShiftEndTime); // 过滤出当前人员当前班次的进/出记录 LocalDateTime workDateTime = null; LocalDateTime offWorkDateTime = null; List enterRecords = filterAttendanceRecord(p.getPersonCode(), EnterOrExitType.ENTER.getValue(), startDateTime, endDateTime, recordList); //上班时间判定边界 LocalDateTime boundaryTime = currentShiftStartDateTime.minusHours(WORK_TIME_BOUNDARY); if (!enterRecords.isEmpty()) { // 上班时间和状态 StaffAttendanceTrackingRecord enterRecord = enterRecords.stream() .filter(s -> (!s.getSwingTime().isAfter(currentShiftStartDateTime) && !s.getSwingTime().isBefore(boundaryTime)) || StringUtils.equals(s.getWorkStateFlag(), StaffWorkStateFlag.WORK.getValue())) .max(Comparator.comparing(StaffAttendanceTrackingRecord::getSwingTime)) .orElse(new StaffAttendanceTrackingRecord()); if (BeanUtil.isEmpty(enterRecord)) { enterRecord = enterRecords.stream() .filter(s -> (s.getSwingTime().isAfter(currentShiftStartDateTime) && s.getSwingTime().isBefore(currentShiftEndDateTime)) || StringUtils.equals(s.getWorkStateFlag(), StaffWorkStateFlag.WORK.getValue())) .min(Comparator.comparing(StaffAttendanceTrackingRecord::getSwingTime)) .orElse(new StaffAttendanceTrackingRecord()); workDateTime = enterRecord.getSwingTime(); vo.setWorkClockInState(ClockInState.ABNORMAL.getValue()); vo.setWorkDataId(enterRecord.getId()); } else { workDateTime = enterRecord.getSwingTime(); vo.setWorkClockInState(ClockInState.NORMAL.getValue()); vo.setWorkDataId(enterRecord.getId()); } } List exitRecords = filterAttendanceRecord(p.getPersonCode(), EnterOrExitType.EXIT.getValue(), startDateTime, endDateTime, recordList); if (!exitRecords.isEmpty()) { // 下班时间和状态 StaffAttendanceTrackingRecord exitRecord = exitRecords.stream() .filter(s -> (!s.getSwingTime().isBefore(currentShiftEndDateTime) && s.getSwingTime().isBefore(nextShiftStartDateTime)) || StringUtils.equals(s.getWorkStateFlag(), StaffWorkStateFlag.OFF_WORK.getValue())) .min(Comparator.comparing(StaffAttendanceTrackingRecord::getSwingTime)) .orElse(new StaffAttendanceTrackingRecord()); if (BeanUtil.isEmpty(exitRecord) && !now.isBefore(currentShiftEndDateTime)) { exitRecord = exitRecords.stream() .filter(s -> (s.getSwingTime().isAfter(currentShiftStartDateTime) && s.getSwingTime().isBefore(currentShiftEndDateTime)) || StringUtils.equals(s.getWorkStateFlag(), StaffWorkStateFlag.OFF_WORK.getValue())) .max(Comparator.comparing(StaffAttendanceTrackingRecord::getSwingTime)) .orElse(new StaffAttendanceTrackingRecord()); offWorkDateTime = exitRecord.getSwingTime(); vo.setOffClockInState(ClockInState.ABNORMAL.getValue()); vo.setOffWorkDataId(exitRecord.getId()); } else { offWorkDateTime = exitRecord.getSwingTime(); vo.setOffClockInState(ClockInState.NORMAL.getValue()); vo.setOffWorkDataId(exitRecord.getId()); } } if (ObjectUtils.allNotNull(workDateTime, offWorkDateTime)) { vo.setActualWorkHours(TimeDiffCalculator.getHourDiff(workDateTime, offWorkDateTime)); } // 赋值 vo.setShiftId(p.getShift()); vo.setPersonCode(p.getPersonCode()); vo.setPersonName(p.getUserName()); vo.setUserId(p.getUserId()); //应勤时长 double plannedWorkHours = Math.abs(hourDiff); vo.setDiffHour(hourDiff); vo.setPlannedWorkHours(plannedWorkHours); vo.setSwingDate(startDateTime); vo.setWorkDateTime(workDateTime); vo.setOffWorkDateTime(offWorkDateTime); vo.setDeptName(recordList.get(0).getDeptName()); vo.setIsSync(recordList.get(0).getIsSync()); vo.setCreateUser(recordList.get(0).getCreateUser()); vo.setCreateTime(recordList.get(0).getCreateTime()); vo.setUpdateUser(recordList.get(0).getUpdateUser()); vo.setUpdateTime(recordList.get(0).getUpdateTime()); //当前时间在下班时间过后才判断考勤结果 if(!now.isBefore(currentShiftEndDateTime)){ if(ObjectUtils.allNotNull(vo.getWorkClockInState(),vo.getOffClockInState())){ vo.setResult(Integer.min(vo.getWorkClockInState(),vo.getOffClockInState())); }else{ vo.setResult(ClockInState.ABNORMAL.getValue()); } } // 计算缺勤时长 if (ObjectUtils.allNotNull(vo.getActualWorkHours(), vo.getPlannedWorkHours())) { double absenceWorkHours = BigDecimal.valueOf(vo.getPlannedWorkHours()) .subtract(BigDecimal.valueOf(vo.getActualWorkHours())).setScale(2, RoundingMode.HALF_EVEN) .doubleValue(); if (Double.compare(absenceWorkHours, 0) > 0) { vo.setAbsenceWorkHours(absenceWorkHours); } } if (!enterRecords.isEmpty() || !exitRecords.isEmpty()) resultList.add(vo); } } return resultList; } @Override public IPage pageAttendanceRecord(Page page, StaffAttendanceDTO staffAttendanceDTO) { // 查询班次 List performanceShifts = performanceShiftMapper.selectListByWorkTime( staffAttendanceDTO.getStartDate(), staffAttendanceDTO.getEndDate(), staffAttendanceDTO.getKeyword()); return limitPages(page, getAttendanceRecord(performanceShifts,staffAttendanceDTO)); } @Override public List getClockInRecord(StaffAttendanceDTO staffAttendanceDTO) { ShiftTime shiftTime = shiftTimeMapper.selectOne(Wrappers.lambdaQuery().eq(ShiftTime::getShift, staffAttendanceDTO.getShiftId())); if(ObjectUtils.isEmpty(shiftTime)){ throw new RuntimeException("未查询到当前班次的时间配置"); } LocalDateTime startDateTime = LocalDateTime.of(staffAttendanceDTO.getSwingDate(),LocalTime.MIN); LocalDateTime endDateTime = LocalDateTime.of(staffAttendanceDTO.getSwingDate(),LocalTime.MAX); //判断当前班次是否要跨天 double hourDiff = HourDiffCalculator.getHourDiff(shiftTime.getStartTime(), shiftTime.getEndTime()); if(Double.compare(hourDiff,0)<0){ endDateTime = endDateTime.plusDays(1L); } return baseMapper.selectList(Wrappers.lambdaQuery() .eq(StaffAttendanceTrackingRecord::getPersonCode,staffAttendanceDTO.getPersonCode()) .between(ObjectUtil.isAllNotEmpty(startDateTime, endDateTime),StaffAttendanceTrackingRecord::getSwingTime, startDateTime,endDateTime) .orderByAsc(StaffAttendanceTrackingRecord::getSwingTime) ); } @Override public PerformanceShiftMapDto checkDutyDate(StaffAttendanceDTO staffAttendanceDTO) { if(ObjectUtils.isEmpty(staffAttendanceDTO.getSwingDate())){ throw new RuntimeException("考勤日期不能为空"); } LocalDateTime startDateTime = LocalDateTime.of(staffAttendanceDTO.getSwingDate(),LocalTime.MIN); LocalDateTime endDateTime = LocalDateTime.of(staffAttendanceDTO.getSwingDate(),LocalTime.MAX); Long count = baseMapper.selectCount(Wrappers.lambdaQuery() .eq(StaffAttendanceTrackingRecord::getPersonCode, staffAttendanceDTO.getPersonCode()) .between(StaffAttendanceTrackingRecord::getSwingTime, startDateTime, endDateTime)); if(count>0){ throw new RuntimeException("所选日期已存在考勤记录！"); } //查询人员当天班次配置 List shiftMapDtos = performanceShiftMapper.selectListByWorkTime(startDateTime, endDateTime, staffAttendanceDTO.getPersonCode()); if(shiftMapDtos.isEmpty()){ throw new RuntimeException("未找到人员所选考勤时间的班次配置，请先配置班次"); } return shiftMapDtos.get(0); } @Override @Transactional(rollbackFor = Exception.class) public boolean saveOrUpdateRecord(StaffAttendanceDTO staffAttendanceDTO) { if(ObjectUtils.isEmpty(staffAttendanceDTO)){ throw new RuntimeException("传参不能为空"); } LocalDateTime workDateTime = LocalDateTime.of(staffAttendanceDTO.getSwingDate(),staffAttendanceDTO.getWorkDateTime()); LocalDateTime offWorkDateTime = LocalDateTime.of(staffAttendanceDTO.getSwingDate(),staffAttendanceDTO.getOffWorkDateTime()); //校验上下班时间是否跨天 double hourDiff = HourDiffCalculator.getHourDiff(staffAttendanceDTO.getWorkDateTime().format(HHmm), staffAttendanceDTO.getOffWorkDateTime().format(HHmm)); if(Double.compare(hourDiff,0)<0){ offWorkDateTime = offWorkDateTime.plusDays(1L); } //组装上/下班考勤记录 StaffAttendanceTrackingRecord workRecord = new StaffAttendanceTrackingRecord( staffAttendanceDTO.getWorkDataId(), workDateTime, staffAttendanceDTO.getPersonCode(), staffAttendanceDTO.getPersonName(), staffAttendanceDTO.getDeptName(), staffAttendanceDTO.getResult(), EnterOrExitType.ENTER.getValue(), SyncStatus.INERT.getValue()); StaffAttendanceTrackingRecord offWorkRecord = new StaffAttendanceTrackingRecord( staffAttendanceDTO.getOffWorkDataId(), offWorkDateTime, staffAttendanceDTO.getPersonCode(), staffAttendanceDTO.getPersonName(), staffAttendanceDTO.getDeptName(), staffAttendanceDTO.getResult(), EnterOrExitType.EXIT.getValue(), SyncStatus.INERT.getValue()); List records = Arrays.asList(workRecord, offWorkRecord); return this.saveOrUpdateBatch(records); } /** * 根据导出日期格式化导出时间范围 * @param staffAttendanceDTO */ private static void formatExportDateRange(StaffAttendanceDTO staffAttendanceDTO){ if(StringUtils.isNoneBlank(staffAttendanceDTO.getReportDate())){ LocalTime startTime = LocalTime.of(0,0,0); LocalTime endTime = LocalTime.of(23,59,59); YearMonth yearMonth; if(StringUtils.equals(staffAttendanceDTO.getAttendanceReportType(),CalendarType.MONTH.name())){ yearMonth = YearMonth.parse(staffAttendanceDTO.getReportDate(),DateTimeFormatter.ofPattern("yyyy-MM")); LocalDateTime startDateTime = LocalDateTime.of(yearMonth.minusMonths(1L).atDay(26),startTime); LocalDateTime endDateTime = LocalDateTime.of(yearMonth.atDay(25),endTime); staffAttendanceDTO.setStartDate(startDateTime); staffAttendanceDTO.setEndDate(endDateTime); }else if(StringUtils.equals(staffAttendanceDTO.getAttendanceReportType(),CalendarType.YEAR.name())){ Year year = Year.parse(staffAttendanceDTO.getReportDate(),DateTimeFormatter.ofPattern("yyyy")); yearMonth = year.atMonth(1); LocalDateTime startDateTime = LocalDateTime.of(yearMonth.minusMonths(1L).atDay(26),startTime); LocalDateTime endDateTime = LocalDateTime.of(yearMonth.withMonth(12).atDay(25),endTime); staffAttendanceDTO.setStartDate(startDateTime); staffAttendanceDTO.setEndDate(endDateTime); } } } @Override public void exportStaffAttendanceRecords(HttpServletResponse response, StaffAttendanceDTO staffAttendanceDTO) { response.reset(); try{ formatExportDateRange(staffAttendanceDTO); //查询人员架构 List userList = userMapper.selectUserListByPerformance(false); List userIdList = userList.stream().map(User::getId).collect(Collectors.toList()); List attendanceDateList = buildAttendanceDateList(staffAttendanceDTO); //批注信息坐标信息 List annotationTextList = new ArrayList<>(); // 查询班次 List performanceShifts = performanceShiftMapper.selectListByWorkTime( staffAttendanceDTO.getStartDate(), staffAttendanceDTO.getEndDate(), staffAttendanceDTO.getKeyword()); //获取考勤数据 List attendanceRecords = getAttendanceRecord(performanceShifts,staffAttendanceDTO); //组装导出数据 List excelData = new ArrayList<>(); Map> groupByUserId = performanceShifts.stream().collect(Collectors.groupingBy(PerformanceShiftMapDto::getUserId)); List userIdKeys = groupByUserId.keySet().stream().sorted(Comparator.comparing(userIdList::indexOf)).collect(Collectors.toList()); for (int i = 0; i < userIdKeys.size(); i++) { List shiftMapDtos = groupByUserId.get(userIdKeys.get(i)); StaffAttendanceExcelData attendanceExcelData = new StaffAttendanceExcelData(); List shiftList = new ArrayList<>(); attendanceExcelData.setPersonName(shiftMapDtos.get(0).getUserName()); attendanceExcelData.setExcelIndex(i+1); AtomicInteger holidayCount = new AtomicInteger(0);//休息天数 AtomicInteger personalLeaveCount = new AtomicInteger(0);//事假天数 AtomicInteger annualLeaveCount = new AtomicInteger(0);//年假天数 AtomicInteger officialTripCount = new AtomicInteger(0);//公差天数 AtomicInteger marriageLeaveCount = new AtomicInteger(0);//婚假天数 AtomicInteger bereavementLeaveCount = new AtomicInteger(0);//丧假天数 AtomicInteger sickLeaveCount = new AtomicInteger(0);//病假天数 AtomicInteger maternityLeaveCount = new AtomicInteger(0);//产假天数 AtomicInteger attendanceDayCount = new AtomicInteger(0);//出勤天数 AtomicDouble attendanceWorkHourCount = new AtomicDouble(0D);//出勤总时间 for (int j = 0; j < shiftMapDtos.size(); j++) { PerformanceShiftMapDto shiftMapDto = shiftMapDtos.get(j); //统计各假期和公差的天数 if(StringUtils.contains(shiftMapDto.getShiftName(),holidayLeaveKeyword)){ holidayCount.getAndIncrement(); }else if(StringUtils.contains(shiftMapDto.getShiftName(),personalLeaveKeyword)){ personalLeaveCount.getAndIncrement(); }else if(StringUtils.contains(shiftMapDto.getShiftName(),annualLeaveKeyword)){ annualLeaveCount.getAndIncrement(); }else if(StringUtils.contains(shiftMapDto.getShiftName(),officialTripKeyword)){ officialTripCount.getAndIncrement(); }else if(StringUtils.contains(shiftMapDto.getShiftName(),marriageLeaveKeyword)){ marriageLeaveCount.getAndIncrement(); }else if(StringUtils.contains(shiftMapDto.getShiftName(),bereavementLeaveKeyword)){ bereavementLeaveCount.getAndIncrement(); }else if(StringUtils.contains(shiftMapDto.getShiftName(),sickLeaveKeyword)){ sickLeaveCount.getAndIncrement(); }else if(StringUtils.contains(shiftMapDto.getShiftName(),maternityLeaveKeyword)){ maternityLeaveCount.getAndIncrement(); } if(StringUtils.isAllBlank(shiftMapDto.getStartTime(),shiftMapDto.getEndTime())){ shiftList.add(shiftMapDto.getShiftName()); }else{ //过滤当前人员的班次信息 StaffAttendanceVO vo = attendanceRecords.stream().filter(f->StringUtils.isNotBlank(f.getPersonCode())).filter(f -> StringUtils.equals(f.getPersonCode(), shiftMapDto.getPersonCode()) && f.getSwingDate().isEqual(shiftMapDto.getWorkTime())).findFirst().orElse(null); if(ObjectUtils.isEmpty(vo)){ shiftList.add(""); }else{ String actualWorkHours = Objects.toString(vo.getActualWorkHours(), ""); Double diffHour = ObjectUtils.defaultIfNull(vo.getDiffHour(), 0D); if (StringUtils.isBlank(actualWorkHours)) { shiftList.add(""); } else { shiftList.add(Double.compare(diffHour, 0D) < 0 ? "-" + actualWorkHours : actualWorkHours); attendanceDayCount.getAndIncrement(); attendanceWorkHourCount.getAndAdd(Double.parseDouble(actualWorkHours)); } } } //月度统计才插入批注数据 if(StringUtils.isNoneBlank(staffAttendanceDTO.getAttendanceReportType()) && StringUtils.equals(staffAttendanceDTO.getAttendanceReportType(), CalendarType.MONTH.name())){ if(StringUtils.isNotBlank(shiftMapDto.getAnnotationText())){ annotationTextList.add(new StaffAttendanceAnnotationTextExcelData(i,j,shiftMapDto.getAnnotationText())); } } } if(StringUtils.isNoneBlank(staffAttendanceDTO.getAttendanceReportType()) && StringUtils.equals(staffAttendanceDTO.getAttendanceReportType(), CalendarType.MONTH.name())){ attendanceExcelData.setShiftList(shiftList); } attendanceExcelData.setAttendanceDayCount(attendanceDayCount.get()); attendanceExcelData.setAttendanceWorkHourCount(attendanceWorkHourCount.get()); //班次考勤天数 attendanceExcelData.setHolidayCount(holidayCount.get()); attendanceExcelData.setPersonalLeaveCount(personalLeaveCount.get()); attendanceExcelData.setAnnualLeaveCount(annualLeaveCount.get()); attendanceExcelData.setOfficialTripCount(officialTripCount.get()); attendanceExcelData.setMarriageLeaveCount(marriageLeaveCount.get()); attendanceExcelData.setBereavementLeaveCount(bereavementLeaveCount.get()); attendanceExcelData.setSickLeaveCount(sickLeaveCount.get()); attendanceExcelData.setMaternityLeaveCount(maternityLeaveCount.get()); excelData.add(attendanceExcelData); } //导出 String fileName = "中天耐丝质量考勤汇总"+ ExcelTypeEnum.XLSX; fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString()); response.setContentType("application/vnd.ms-excel"); response.setHeader("Cache-Control", "no-cache"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.setHeader("Access-Control-Expose-Headers", "Content-Disposition"); InputStream resourceAsStream = buildAttendanceTemplate(attendanceDateList,staffAttendanceDTO.getAttendanceReportType()); try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(resourceAsStream).registerWriteHandler(new CommentWriteHandler(excelData, annotationTextList)).relativeHeadRowIndex(4).build()) { WriteSheet writeSheet = EasyExcel.writerSheet().build(); excelWriter.fill(excelData, writeSheet); if(StringUtils.equals(staffAttendanceDTO.getAttendanceReportType(), CalendarType.YEAR.name())){ String startDate = staffAttendanceDTO.getStartDate().format(yyyMMStr); String endDate = staffAttendanceDTO.getEndDate().format(yyyMMStr); Map dateMap = new HashMap<>(); dateMap.put("startDate",startDate); dateMap.put("endDate",endDate); excelWriter.fill(dateMap, writeSheet); } } } catch (Exception e) { throw new RuntimeException(e); } } private List buildAttendanceDateList(StaffAttendanceDTO staffAttendanceDTO) { if (staffAttendanceDTO == null || staffAttendanceDTO.getStartDate() == null || staffAttendanceDTO.getEndDate() == null) { throw new IllegalArgumentException("导出时间范围不能为空"); } LocalDate startDate = staffAttendanceDTO.getStartDate().toLocalDate(); LocalDate endDate = staffAttendanceDTO.getEndDate().toLocalDate(); if (startDate.isAfter(endDate)) { throw new IllegalArgumentException("开始时间不能晚于结束时间"); } List attendanceDateList = new ArrayList<>(); for (LocalDate currentDate = startDate; !currentDate.isAfter(endDate); currentDate = currentDate.plusDays(1)) { attendanceDateList.add(currentDate); } if (attendanceDateList.size() > 31 && StringUtils.equals(staffAttendanceDTO.getAttendanceReportType(), CalendarType.MONTH.name())) { throw new IllegalArgumentException("导出时间范围不能超过31天"); } return attendanceDateList; } private InputStream buildAttendanceTemplate(List attendanceDateList,String attendanceReportType) throws IOException { String templateName = "/static/staff_attendance_month_template.xlsx"; if(StringUtils.equals(attendanceReportType, CalendarType.YEAR.name())){ templateName = "/static/staff_attendance_year_template.xlsx"; } try (InputStream templateStream = this.getClass().getResourceAsStream(templateName)) { assert templateStream != null; try (Workbook workbook = WorkbookFactory.create(templateStream); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) { Sheet sheet = workbook.getSheetAt(0); if(StringUtils.equals(attendanceReportType, CalendarType.MONTH.name())){ fillAttendanceHeader(sheet, attendanceDateList); } workbook.write(outputStream); return new ByteArrayInputStream(outputStream.toByteArray()); } } catch (Exception e) { throw new IOException("构建考勤导出模板失败", e); } } private void fillAttendanceHeader(Sheet sheet, List attendanceDateList) { if (sheet == null || attendanceDateList == null || attendanceDateList.isEmpty()) { return; } Row titleRow = sheet.getRow(1); if (titleRow != null) { Cell titleCell = titleRow.getCell(0); if (titleCell != null) { titleCell.setCellValue(attendanceDateList.get(attendanceDateList.size() - 1).format(DateTimeFormatter.ofPattern("yyyy年M月"))); } } Row weekRow = sheet.getRow(2); Row dayRow = sheet.getRow(3); if (weekRow == null || dayRow == null) { return; } final int startColumnIndex = 2; final int maxDateColumnCount = 31; for (int i = 0; i < maxDateColumnCount; i++) { Cell weekCell = getOrCreateCell(weekRow, startColumnIndex + i, startColumnIndex); Cell dayCell = getOrCreateCell(dayRow, startColumnIndex + i, startColumnIndex); if (i < attendanceDateList.size()) { LocalDate currentDate = attendanceDateList.get(i); weekCell.setCellValue(resolveWeekOfYear(currentDate)); dayCell.setCellValue(currentDate.getDayOfMonth()); } else { weekCell.setBlank(); dayCell.setBlank(); } } } private Cell getOrCreateCell(Row row, int cellIndex, int templateCellIndex) { Cell cell = row.getCell(cellIndex); if (cell != null) { return cell; } Cell templateCell = row.getCell(templateCellIndex); cell = row.createCell(cellIndex); if (templateCell != null && templateCell.getCellStyle() != null) { cell.setCellStyle(templateCell.getCellStyle()); } return cell; } private String resolveWeekOfYear(LocalDate date) { switch (date.getDayOfWeek()) { case MONDAY: return "一"; case TUESDAY: return "二"; case WEDNESDAY: return "三"; case THURSDAY: return "四"; case FRIDAY: return "五"; case SATURDAY: return "六"; case SUNDAY: return "日"; default: return ""; } } /** * 自定义分页方法 * * @param page 分页对象 * @param resultList 数据列表 * @return */ private IPage limitPages(Page page, List resultList) { IPage resultPage = new Page<>(); long current = page.getCurrent(); long size = page.getSize(); if (current < 1) current = 1; long total = resultList.size(); long pages = getPages(size,total); int startIndex = Math.toIntExact((current - 1) * size >= total ? (pages - 1) * size : (current - 1) * size); int endIndex = Math.toIntExact(Math.min(current * size, total)); List records; if(resultList.isEmpty()){ records = new ArrayList<>(); }else{ records = resultList.subList(startIndex, endIndex); } resultPage.setRecords(records); resultPage.setTotal(total); resultPage.setSize(size); resultPage.setCurrent(current); if(current>=pages)resultPage.setCurrent(pages); return resultPage; } /** * 当前分页总页数 */ private long getPages(long size,long total) { if (size == 0) { return 0L; } long pages = total / size; if (total % size != 0) { pages++; } return pages; } /** * 获取当前班次日期的下一班次开始时间 * @param personCode 人员编号 * @param dtoList 班次列表 * @param nextShiftTime 下一班次时间 * @return */ private LocalDateTime getShiftStartDateTime(String personCode,List dtoList, LocalDateTime nextShiftTime) { if (dtoList.isEmpty()) { return LocalDateTime.of(nextShiftTime.toLocalDate(), LocalTime.MAX); } //过滤当前人员的下一班次信息 PerformanceShiftMapDto shiftMapDto = dtoList.stream().filter(f -> StringUtils.equals(f.getPersonCode(), personCode) && f.getWorkTime().isEqual(nextShiftTime)).findFirst().orElse(new PerformanceShiftMapDto()); if(StringUtils.isEmpty(shiftMapDto.getStartTime())){ return LocalDateTime.of(nextShiftTime.toLocalDate(), LocalTime.MAX); } LocalTime nextShiftStartTime = LocalTime.parse(shiftMapDto.getStartTime(), HHmm); return LocalDateTime.of(nextShiftTime.toLocalDate(), nextShiftStartTime); } /** * 过滤指定时间范围的进出记录 * * @param personCode 人员编号 * @param enterOrExit 进门/出门 * @param startDateTime 开始时间 * @param endDateTime 结束时间 * @param recordList 进出记录列表 * @return */ public List filterAttendanceRecord(String personCode, Integer enterOrExit, LocalDateTime startDateTime, LocalDateTime endDateTime, List recordList) { if (recordList.isEmpty()) { return Collections.emptyList(); } return recordList.stream() .filter(s -> ObjectUtil.equal(s.getEnterOrExit(), enterOrExit)) .filter(s -> (s.getSwingTime().isAfter(startDateTime) && s.getSwingTime().isBefore(endDateTime)) && StringUtils.equals(s.getPersonCode(), personCode)) .collect(Collectors.toList()); } /** * 将纯数字复原为标准员工编号 * * @param number 传入的数字（员工编号去除前缀和前置0后的数字，支持int/long/String类型） * @return 标准格式员工编号（如输入123 → ZT-000123） * @throws IllegalArgumentException 传入非数字/负数时抛出异常 */ public static String restorePersonCode(Object number) { // 1. 空值校验 if (number == null) { throw new IllegalArgumentException("传入数字不能为空"); } // 2. 统一转换为字符串并去除首尾空格 String numStr = number.toString().trim(); // 3. 校验是否为纯数字（排除负数、非数字字符） if (!numStr.matches("\\d+")) { throw new IllegalArgumentException("传入的不是有效正整数：" + numStr); } // 4. 补前置0到指定长度（6位），超出则保留原数字 String paddedNum = String.format("%0" + DIGIT_LENGTH + "d", Long.parseLong(numStr)); // 5. 拼接前缀返回 return PREFIX + paddedNum; } }