导出工资xlsx

huminmin

昨天 ff09f8e85d51c5d127796bfbd097f5fad9267d4b

 src/main/java/com/ruoyi/staff/service/impl/StaffSalaryMainServiceImpl.java

@@ -2,33 +2,59 @@

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.account.mapper.AccountExpenseMapper;
import com.ruoyi.account.pojo.AccountExpense;
import com.ruoyi.common.utils.SecurityUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.basic.mapper.ProductMapper;
import com.ruoyi.basic.pojo.Product;
import com.ruoyi.framework.web.domain.AjaxResult;
import com.ruoyi.project.system.domain.SysUser;
import com.ruoyi.project.system.domain.SysUserDept;
import com.ruoyi.project.system.domain.SysDept;
import com.ruoyi.project.system.mapper.SysDeptMapper;
import com.ruoyi.project.system.mapper.SysUserDeptMapper;
import com.ruoyi.project.system.mapper.SysUserMapper;
import com.ruoyi.production.mapper.ProductionAccountMapper;
import com.ruoyi.production.mapper.ProductionProductMainMapper;
import com.ruoyi.production.bean.dto.ProductionAccountDto;
import com.ruoyi.production.bean.dto.ProductionProductMainDto;
import com.ruoyi.production.pojo.ProductionAccount;
import com.ruoyi.technology.mapper.TechnologyOperationMapper;
import com.ruoyi.technology.pojo.TechnologyOperation;
import com.ruoyi.staff.excel.StaffSalaryExportData;
import jakarta.servlet.http.HttpServletResponse;
import com.ruoyi.staff.dto.CalculateSalaryDto;
import com.ruoyi.staff.mapper.StaffOnJobMapper;
import com.ruoyi.staff.dto.StaffSalaryCalculateDto;
import com.ruoyi.staff.mapper.StaffLeaveMapper;
import com.ruoyi.staff.mapper.StaffContractMapper;
import com.ruoyi.staff.mapper.StaffSalaryDetailMapper;
import com.ruoyi.staff.pojo.SchemeApplicableStaff;
import com.ruoyi.staff.pojo.StaffOnJob;
import com.ruoyi.staff.mapper.StaffSalaryMainMapper;
import com.ruoyi.staff.pojo.StaffLeave;
import com.ruoyi.staff.pojo.StaffContract;
import com.ruoyi.staff.pojo.StaffSalaryDetail;
import com.ruoyi.staff.pojo.StaffSalaryMain;
import com.ruoyi.staff.mapper.StaffSalaryMainMapper;
import com.ruoyi.staff.service.StaffSalaryDetailService;
import com.ruoyi.staff.service.StaffSalaryMainService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.util.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.stream.Collectors;
import java.time.LocalDate;
import java.time.YearMonth;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.text.SimpleDateFormat;

/**
 * <p>
@@ -39,31 +65,21 @@
 * @since 2026-03-06 01:22:05
 */
@Service
@RequiredArgsConstructor
public class StaffSalaryMainServiceImpl extends ServiceImpl<StaffSalaryMainMapper, StaffSalaryMain> implements StaffSalaryMainService {

    @Autowired
    private StaffSalaryMainMapper staffSalaryMainMapper;

    @Autowired
    private StaffSalaryDetailService staffSalaryDetailService;

    @Autowired
    private StaffSalaryDetailMapper staffSalaryDetailMapper;

    @Autowired
    private SchemeApplicableStaffServiceImpl schemeApplicableStaffService;

    @Autowired
    private SysUserDeptMapper sysUserDeptMapper;

    @Autowired
    private SysUserMapper sysUserMapper;

    @Autowired
    private StaffOnJobMapper staffOnJobMapper;

    @Autowired
    private AccountExpenseMapper accountExpenseMapper;
    private final StaffSalaryMainMapper staffSalaryMainMapper;
    private final StaffSalaryDetailService staffSalaryDetailService;
    private final StaffSalaryDetailMapper staffSalaryDetailMapper;
    private final SchemeApplicableStaffServiceImpl schemeApplicableStaffService;
    private final SysUserDeptMapper sysUserDeptMapper;
    private final StaffLeaveMapper staffLeaveMapper;
    private final StaffContractMapper staffContractMapper;
    private final SysDeptMapper sysDeptMapper;
    private final ProductMapper productMapper;
    private final ProductionAccountMapper productionAccountMapper;
    private final ProductionProductMainMapper productionProductMainMapper;
    private final TechnologyOperationMapper technologyOperationMapper;

    @Override
    public AjaxResult listPage(Page page, StaffSalaryMain staffSalaryMain) {
@@ -79,9 +95,22 @@
                staffSalaryMainLambdaQueryWrapper.eq(StaffSalaryMain::getStatus, staffSalaryMain.getStatus());
            }
        }
        staffSalaryMainLambdaQueryWrapper.orderByDesc(StaffSalaryMain::getId);
        Page<StaffSalaryMain> page1 = staffSalaryMainMapper.selectPage(page, staffSalaryMainLambdaQueryWrapper);
        page1.getRecords().forEach(main -> {
            List<StaffSalaryDetail> staffSalaryDetailList = staffSalaryDetailMapper.selectList(new LambdaQueryWrapper<StaffSalaryDetail>().eq(StaffSalaryDetail::getMainId, main.getId()));
            staffSalaryDetailList.forEach(detail -> {
                if (detail.getEntryDate() == null && detail.getStaffOnJobId() != null) {
                    StaffContract staffContract = staffContractMapper.selectOne(new LambdaQueryWrapper<StaffContract>()
                            .eq(StaffContract::getStaffOnJobId, detail.getStaffOnJobId())
                            .orderByAsc(StaffContract::getContractStartTime)
                            .orderByAsc(StaffContract::getId)
                            .last("limit 1"));
                    if (staffContract != null && staffContract.getContractStartTime() != null) {
                        detail.setEntryDate(formatEntryDate(staffContract.getContractStartTime()));
                    }
                }
            });
            main.setStaffSalaryDetailList(staffSalaryDetailList);
        });
        return AjaxResult.success(page1);
@@ -89,6 +118,26 @@

    @Override
    public AjaxResult add(StaffSalaryMain staffSalaryMain) {
        if (staffSalaryMain == null || StringUtils.isEmpty(staffSalaryMain.getSalaryMonth())) {
            return AjaxResult.error("参数错误");
        }

        Integer status = staffSalaryMain.getStatus();
        if (status != null && status == 5) {
            String salaryMonth = staffSalaryMain.getSalaryMonth();
            String deptIds = staffSalaryMain.getDeptIds();

            List<StaffSalaryMain> issuedList = staffSalaryMainMapper.selectList(new LambdaQueryWrapper<StaffSalaryMain>()
                    .eq(StaffSalaryMain::getSalaryMonth, salaryMonth)
                    .eq(StaffSalaryMain::getStatus, 5));

            for (StaffSalaryMain issued : issuedList) {
                if (hasOverlappingDept(deptIds, issued.getDeptIds())) {
                    return AjaxResult.error("同一月份同一部门工资不可以发放多次");
                }
            }
        }

        staffSalaryMainMapper.insert(staffSalaryMain);
        staffSalaryMain.getStaffSalaryDetailList().forEach(detail -> {
            detail.setMainId(staffSalaryMain.getId());
@@ -106,6 +155,30 @@
        if(staffSalaryMain1 == null){
            return AjaxResult.error("参数错误");
        }

        Integer newStatus = staffSalaryMain.getStatus();
        if (newStatus != null && newStatus == 5) {
            String salaryMonth = staffSalaryMain.getSalaryMonth();
            if (StringUtils.isEmpty(salaryMonth)) {
                salaryMonth = staffSalaryMain1.getSalaryMonth();
            }
            String deptIds = staffSalaryMain.getDeptIds();
            if (StringUtils.isEmpty(deptIds)) {
                deptIds = staffSalaryMain1.getDeptIds();
            }

            List<StaffSalaryMain> issuedList = staffSalaryMainMapper.selectList(new LambdaQueryWrapper<StaffSalaryMain>()
                    .eq(StaffSalaryMain::getSalaryMonth, salaryMonth)
                    .eq(StaffSalaryMain::getStatus, 5)
                    .ne(StaffSalaryMain::getId, staffSalaryMain.getId()));

            for (StaffSalaryMain issued : issuedList) {
                if (hasOverlappingDept(deptIds, issued.getDeptIds())) {
                    return AjaxResult.error("同一月份同一部门工资不可以发放多次");
                }
            }
        }

        // 待审核不可编辑
//        if(staffSalaryMain1.getStatus() > 3){
//            return AjaxResult.error("待审核不可编辑");
@@ -117,21 +190,6 @@
                detail.setMainId(staffSalaryMain.getId());
            });
            staffSalaryDetailService.saveBatch(staffSalaryMain.getStaffSalaryDetailList());
        }
        // 和财务联动，新增支出
        if(staffSalaryMain.getStatus().equals(5)){
            AccountExpense accountExpense = new AccountExpense();
            accountExpense.setBusinessType(3);
            accountExpense.setExpenseMoney(staffSalaryMain.getTotalSalary());
            accountExpense.setBusinessId(staffSalaryMain.getId());
            accountExpense.setExpenseDate(new Date());
            accountExpense.setExpenseMethod("2");
            accountExpense.setExpenseType("1");
            accountExpense.setExpenseDescribed(staffSalaryMain.getSalaryTitle());
            accountExpense.setNote(staffSalaryMain.getRemark());
            accountExpense.setInputUser(SecurityUtils.getLoginUser().getNickName());
            accountExpense.setInputTime(new Date());
            accountExpenseMapper.insert(accountExpense);
        }
        return AjaxResult.success("修改成功");
    }
@@ -148,25 +206,871 @@

    @Override
    public AjaxResult calculateSalary(CalculateSalaryDto calculateSalaryDto) {
        if(CollectionUtils.isEmpty(calculateSalaryDto.getIds())){
        if (CollectionUtils.isEmpty(calculateSalaryDto.getIds())) {
            return AjaxResult.error("参数错误");
        }
        java.math.BigDecimal deductAmount = calculateSalaryDto.getDeductAmount() == null
                ? java.math.BigDecimal.ZERO
                : calculateSalaryDto.getDeductAmount();
        List<Map<String, Object>> longs = setSchemeApplicableStaffUserInfo(calculateSalaryDto.getIds()); // 通过部门ids获取用户信息
        if(CollectionUtils.isEmpty(longs)){
        if (CollectionUtils.isEmpty(longs)) {
            return AjaxResult.error("无员工");
        }
        List<Map<String, Object>> mapList = new ArrayList<>();
        for (Map<String, Object> id : longs) {
            schemeApplicableStaffService.calculateByEmployeeId((Integer) id.get("id"),id,calculateSalaryDto.getDate());
            Long staffOnJobId = id.get("id") == null ? null : ((Number) id.get("id")).longValue();
            // 判断员工是否为当月离职
            if ((Integer) id.get("staffState") == 0) {
                StaffLeave id1 = staffLeaveMapper.selectOne(new LambdaQueryWrapper<StaffLeave>()
                        .eq(StaffLeave::getStaffOnJobId, staffOnJobId)
                        .like(StaffLeave::getLeaveDate, calculateSalaryDto.getDate()));
                if (id1 == null) {
                    continue;
                }
            }
            id.put("otherDeduct", deductAmount);
            if (!id.containsKey("allowance")) {
                id.put("allowance", java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
            }
            if (id.get("entryDate") == null) {
                StaffContract staffContract = staffContractMapper.selectOne(new LambdaQueryWrapper<StaffContract>()
                        .eq(StaffContract::getStaffOnJobId, staffOnJobId)
                        .orderByDesc(StaffContract::getId)
                        .last("limit 1"));
                if (staffContract != null && staffContract.getContractStartTime() != null) {
                    id.put("entryDate", formatEntryDate(staffContract.getContractStartTime()));
                }
            }
            Long sysUserId = schemeApplicableStaffService.getUidByStaffId(staffOnJobId);
            BigDecimal monthTimeTotal = resolveMonthTimeTotal(sysUserId, calculateSalaryDto.getDate());
            id.put("monthTimeTotal", monthTimeTotal);
            schemeApplicableStaffService.calculateByEmployeeId(staffOnJobId == null ? null : staffOnJobId.intValue(), id, calculateSalaryDto.getDate());
            mapList.add(id);
        }
        return AjaxResult.success(longs);
        return AjaxResult.success(mapList);
    }

    @Override
    public AjaxResult calculateSalaryByEmployee(StaffSalaryCalculateDto staffSalaryCalculateDto) {
        if (staffSalaryCalculateDto == null
                || staffSalaryCalculateDto.getStaffOnJobId() == null
                || StringUtils.isEmpty(staffSalaryCalculateDto.getSalaryMonth())) {
            return AjaxResult.error("参数错误");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("id", staffSalaryCalculateDto.getStaffOnJobId());
        map.put("staffOnJobId", staffSalaryCalculateDto.getStaffOnJobId());
        map.put("salaryMonth", staffSalaryCalculateDto.getSalaryMonth());
        map.put("staffName", staffSalaryCalculateDto.getStaffName());
        map.put("postName", staffSalaryCalculateDto.getPostName());
        map.put("deptName", staffSalaryCalculateDto.getDeptName());
        if (staffSalaryCalculateDto.getBasicSalary() != null) {
            map.put("basicSalary", staffSalaryCalculateDto.getBasicSalary());
        }
        map.put("attendanceBonus", staffSalaryCalculateDto.getAttendanceBonus());
        map.put("pieceSalary", staffSalaryCalculateDto.getPieceSalary());
        map.put("hourlySalary", staffSalaryCalculateDto.getHourlySalary());
        map.put("monthTimeTotal", staffSalaryCalculateDto.getMonthTimeTotal());
        map.put("conversionRatio", staffSalaryCalculateDto.getConversionRatio());
        map.put("actualOutput", staffSalaryCalculateDto.getActualOutput());
        map.put("standardQuantity", staffSalaryCalculateDto.getStandardQuantity());
        map.put("otherIncome", staffSalaryCalculateDto.getOtherIncome());
        map.put("allowance", staffSalaryCalculateDto.getAllowance());
        map.put("socialPersonal", staffSalaryCalculateDto.getSocialPersonal());
        map.put("fundPersonal", staffSalaryCalculateDto.getFundPersonal());
        map.put("otherDeduct", staffSalaryCalculateDto.getOtherDeduct());
        map.put("socialSupplementAmount", staffSalaryCalculateDto.getSocialSupplementAmount());
        map.put("salaryTax", staffSalaryCalculateDto.getSalaryTax());
        map.put("grossSalary", staffSalaryCalculateDto.getGrossSalary());
        map.put("deductSalary", staffSalaryCalculateDto.getDeductSalary());
        map.put("netSalary", staffSalaryCalculateDto.getNetSalary());
        map.put("remark", staffSalaryCalculateDto.getRemark());
        schemeApplicableStaffService.calculateByEmployeeId(
                staffSalaryCalculateDto.getStaffOnJobId().intValue(),
                map,
                staffSalaryCalculateDto.getSalaryMonth()
        );
        return AjaxResult.success(map);
    }

    @Override
    public void exportSalaryDetail(HttpServletResponse response, String salaryMonth) {
        LambdaQueryWrapper<StaffSalaryMain> mainWrapper = new LambdaQueryWrapper<>();
        if (StringUtils.isNotEmpty(salaryMonth)) {
            mainWrapper.eq(StaffSalaryMain::getSalaryMonth, salaryMonth);
        }
        List<StaffSalaryMain> mainList = staffSalaryMainMapper.selectList(mainWrapper);

        List<StaffSalaryExportData> exportDataList = new ArrayList<>();
        for (StaffSalaryMain main : mainList) {
            List<StaffSalaryDetail> detailList = staffSalaryDetailMapper.selectList(
                    new LambdaQueryWrapper<StaffSalaryDetail>().eq(StaffSalaryDetail::getMainId, main.getId())
            );

            for (StaffSalaryDetail detail : detailList) {
                StaffSalaryExportData exportData = new StaffSalaryExportData();
                exportData.setSalaryMonth(main.getSalaryMonth());
                exportData.setSalaryTitle(main.getSalaryTitle());
                exportData.setStaffName(detail.getStaffName());
                if (detail.getEntryDate() != null) {
                    exportData.setEntryDate(detail.getEntryDate());
                } else if (detail.getStaffOnJobId() != null) {
                    StaffContract staffContract = staffContractMapper.selectOne(new LambdaQueryWrapper<StaffContract>()
                            .eq(StaffContract::getStaffOnJobId, detail.getStaffOnJobId())
                            .orderByAsc(StaffContract::getContractStartTime)
                            .orderByAsc(StaffContract::getId)
                            .last("limit 1"));
                    if (staffContract != null && staffContract.getContractStartTime() != null) {
                        exportData.setEntryDate(formatEntryDate(staffContract.getContractStartTime()));
                    }
                }
                exportData.setDeptName(detail.getDeptName());
                exportData.setBasicSalary(detail.getBasicSalary());
                exportData.setAttendanceBonus(detail.getAttendanceBonus());
                exportData.setDailySalary(detail.getBasicSalary() != null && detail.getWorkDays() != null && detail.getWorkDays() > 0
                        ? detail.getBasicSalary().divide(new BigDecimal(detail.getWorkDays()), 3, java.math.RoundingMode.HALF_UP)
                        : null);
                exportData.setWorkDays(detail.getWorkDays());
                exportData.setConversionRatio(detail.getConversionRatio() == null
                        ? null
                        : detail.getConversionRatio().setScale(4, java.math.RoundingMode.HALF_UP));
                exportData.setMonthTimeTotal(detail.getMonthTimeTotal());
                exportData.setOtherIncome(detail.getOtherIncome());
                exportData.setPersonalLeave(detail.getPersonalLeave());
                exportData.setSickLeave(detail.getSickLeave());
                exportData.setBereavementLeave(detail.getBereavementLeave());
                exportData.setMarriageLeave(detail.getMarriageLeave());
                exportData.setAllowance(detail.getAllowance());
                exportData.setSocialPersonal(detail.getSocialPersonal());
                exportData.setFundPersonal(detail.getFundPersonal());
                exportData.setOtherDeduct(detail.getOtherDeduct());
                exportData.setSocialSupplementAmount(detail.getSocialSupplementAmount());
                exportData.setSalaryTax(detail.getSalaryTax());
                exportData.setGrossSalary(detail.getGrossSalary());
                exportData.setDeductSalary(detail.getDeductSalary());
                exportData.setNetSalary(detail.getNetSalary());
                exportData.setRemark(detail.getRemark());
                exportDataList.add(exportData);
            }
        }

        ExcelUtil<StaffSalaryExportData> util = new ExcelUtil<>(StaffSalaryExportData.class);
        util.exportExcel(response, exportDataList, "员工薪资明细");
    }

    /**
     * 汇总员工当月月时间合计。
     * 规则：
     * 1. 小提琴 / 大提琴 / 贝斯：月产折合小提琴数量 / 工序标准数量 × 8 + 修补理工时
     * 2. 其他产品：当月实际产量 / 对应产品标准数量 × 8 + 修补理工时
     * 最终结果就是工资表里的月时间合计。
     */
    private java.math.BigDecimal resolveMonthTimeTotal(Long sysUserId, String date) {
        if (sysUserId == null || sysUserId <= 0 || StringUtils.isEmpty(date)) {
            return java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
        }
        java.math.BigDecimal total = java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
        java.util.List<java.util.Map<String, Object>> items = productionAccountMapper.selectMonthSalaryCalcItems(sysUserId, date);
        for (java.util.Map<String, Object> item : items) {
            Integer operationType = item.get("operationType") == null ? null : ((Number) item.get("operationType")).intValue();
            java.math.BigDecimal finishedNum = toDecimal(item.get("finishedNum"));
            java.math.BigDecimal workHour = toDecimal(item.get("workHour"));
            java.math.BigDecimal repairWorkHour = toDecimal(item.get("repairWorkHour"));
            java.math.BigDecimal standardQuantity = toDecimal(item.get("standardQuantity"));
            java.math.BigDecimal productStandardQuantity = toDecimal(item.get("productStandardQuantity"));
            java.math.BigDecimal violinFactor = toDecimal(item.get("violinFactor"));
            java.math.BigDecimal violaFactor = toDecimal(item.get("violaFactor"));
            java.math.BigDecimal bassFactor = toDecimal(item.get("bassFactor"));
            String productName = item.get("productName") == null ? null : String.valueOf(item.get("productName"));
            java.math.BigDecimal rowTotal = calculateOperationTime(
                    operationType,
                    productName,
                    finishedNum,
                    workHour,
                    repairWorkHour,
                    standardQuantity,
                    productStandardQuantity,
                    violinFactor,
                    violaFactor,
                    bassFactor
            );
            total = total.add(rowTotal).setScale(2, java.math.RoundingMode.HALF_UP);
        }
        return total;
    }

    /**
     * 计算单条工序的折算工时。
     * 这里是月时间合计的核心分支：
     * - 小提琴 / 大提琴 / 贝斯：月产折合小提琴数量 / 工序标准数量 × 8 + 修补理工时；
     * - 其他产品：当月实际产量 / 对应产品标准数量 × 8 + 修补理工时。
     */
    private java.math.BigDecimal calculateOperationTime(Integer operationType,
                                                        String productName,
                                                        java.math.BigDecimal finishedNum,
                                                        java.math.BigDecimal workHour,
                                                        java.math.BigDecimal repairWorkHour,
                                                        java.math.BigDecimal standardQuantity,
                                                        java.math.BigDecimal productStandardQuantity,
                                                        java.math.BigDecimal violinFactor,
                                                        java.math.BigDecimal violaFactor,
                                                        java.math.BigDecimal bassFactor) {
        if (isInstrumentProduct(productName)) {
            java.math.BigDecimal foldedQuantity = calculateFoldedQuantity(
                    finishedNum,
                    resolveInstrumentFactor(productName, violinFactor, violaFactor, bassFactor)
            );
            if (standardQuantity.compareTo(java.math.BigDecimal.ZERO) <= 0) {
                return java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
            }
            return foldedQuantity
                    .divide(standardQuantity, 8, java.math.RoundingMode.HALF_UP)
                    .multiply(new java.math.BigDecimal("8"))
                    .add(repairWorkHour)
                    .setScale(2, java.math.RoundingMode.HALF_UP);
        }
        if (productStandardQuantity.compareTo(java.math.BigDecimal.ZERO) <= 0) {
            return java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
        }
        return finishedNum
                .divide(productStandardQuantity, 8, java.math.RoundingMode.HALF_UP)
                .multiply(new java.math.BigDecimal("8"))
                .add(repairWorkHour)
                .setScale(2, java.math.RoundingMode.HALF_UP);
    }

    /**
     * 按产品名称和配置系数折算产量。
     * 只对小提琴、大提琴、贝斯三类产品生效，
     * 返回的是可用于后续工序标准数量换算的统一折合产量。
     */
    private java.math.BigDecimal calculateFoldedQuantity(java.math.BigDecimal quantity,
                                                         java.math.BigDecimal factor) {
        if (quantity == null || factor == null) {
            return java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
        }
        return quantity.multiply(factor).setScale(2, java.math.RoundingMode.HALF_UP);
    }

    private boolean isInstrumentProduct(String productName) {
        return "小提琴".equals(productName)
                || "大提琴".equals(productName)
                || "贝斯".equals(productName);
    }

    private java.math.BigDecimal resolveInstrumentFactor(String productName,
                                                         java.math.BigDecimal violinFactor,
                                                         java.math.BigDecimal violaFactor,
                                                         java.math.BigDecimal bassFactor) {
        if ("小提琴".equals(productName)) {
            return violinFactor;
        }
        if ("大提琴".equals(productName)) {
            return violaFactor;
        }
        if ("贝斯".equals(productName)) {
            return bassFactor;
        }
        return java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
    }

    private String formatEntryDate(java.util.Date entryDate) {
        if (entryDate == null) {
            return null;
        }
        return new SimpleDateFormat("yyyy-MM-dd").format(entryDate);
    }

    private java.math.BigDecimal toDecimal(Object value) {
        if (value == null) {
            return java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
        }
        if (value instanceof java.math.BigDecimal) {
            return ((java.math.BigDecimal) value).setScale(2, java.math.RoundingMode.HALF_UP);
        }
        if (value instanceof Number) {
            return new java.math.BigDecimal(String.valueOf(value)).setScale(2, java.math.RoundingMode.HALF_UP);
        }
        String text = String.valueOf(value).trim();
        if (text.isEmpty()) {
            return java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
        }
        return new java.math.BigDecimal(text).setScale(2, java.math.RoundingMode.HALF_UP);
    }

    @Override
    public void exportWorkHours(HttpServletResponse response, String salaryMonth) {
        Workbook workbook = new XSSFWorkbook();
        CellStyle titleStyle = createStyle(workbook, true, HorizontalAlignment.CENTER, 14, false);
        CellStyle headerStyle = createStyle(workbook, false, HorizontalAlignment.CENTER, 11, true);
        CellStyle labelStyle = createStyle(workbook, false, HorizontalAlignment.CENTER, 10, false);
        CellStyle numStyle = createStyle(workbook, false, HorizontalAlignment.CENTER, 10, false);
        DataFormat dataFormat = workbook.createDataFormat();
        numStyle.setDataFormat(dataFormat.getFormat("0.00"));

        List<String> monthList = new ArrayList<>();
        if (StringUtils.isNotEmpty(salaryMonth)) {
            monthList.add(salaryMonth);
        } else {
            monthList = productionAccountMapper.selectAccountingMonths();
        }
        if (CollectionUtils.isEmpty(monthList)) {
            throw new IllegalArgumentException("没有可导出的生产核算月份");
        }
        Sheet nonInstrumentSheet = workbook.createSheet("琴盒部");
        Sheet instrumentSheet = workbook.createSheet("成品部");
        int nonInstrumentRowIdx = 0;
        int instrumentRowIdx = 0;
        for (String month : monthList) {
            List<ProductionProductMainDto> accountList = queryProductionDetails(month);
            Integer monthWorkDays = calculateWorkDays(month);
            nonInstrumentRowIdx = appendWorkHoursSection(nonInstrumentSheet, nonInstrumentRowIdx, month, false, accountList, monthWorkDays, titleStyle, headerStyle, labelStyle, numStyle, nonInstrumentRowIdx > 0);
            instrumentRowIdx = appendWorkHoursSection(instrumentSheet, instrumentRowIdx, month, true, accountList, monthWorkDays, titleStyle, headerStyle, labelStyle, numStyle, instrumentRowIdx > 0);
        }

        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            String fileName = java.net.URLEncoder.encode("工时表.xlsx", java.nio.charset.StandardCharsets.UTF_8);
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName);
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException("导出工时表失败", e);
        }
    }

    public List<Map<String, Object>> setSchemeApplicableStaffUserInfo(List<Long> ids) {
        if(CollectionUtils.isEmpty(ids)){
        if (CollectionUtils.isEmpty(ids)) {
            return new ArrayList<>();
        }
        // 通过部门获取人员id
        return sysUserDeptMapper.setSchemeApplicableStaffUserInfo(ids);
    }

    private int appendWorkHoursSection(Sheet sheet,
                                       int startRowIdx,
                                       String salaryMonth,
                                       boolean instrumentSheet,
                                       List<ProductionProductMainDto> accountList,
                                       Integer monthWorkDays,
                                       CellStyle titleStyle,
                                       CellStyle headerStyle,
                                       CellStyle labelStyle,
                                       CellStyle numStyle,
                                       boolean addSectionTitle) {
        int rowIdx = startRowIdx;
        List<Map<String, Object>> columns = buildWorkHourColumns(instrumentSheet);
        int lastCol = 1 + columns.size() + 2;
        Row titleRow = sheet.createRow(rowIdx++);
        titleRow.setHeightInPoints(56f);
        String sectionTitle = (StringUtils.isEmpty(salaryMonth) ? "未知月份" : salaryMonth) + "工时统计";
        titleRow.createCell(0).setCellValue(sectionTitle);
        titleRow.getCell(0).setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(), titleRow.getRowNum(), 0, lastCol));

        Row workDayRow = sheet.createRow(rowIdx++);
        createTextCell(workDayRow, 0, "工作日", numStyle);
        createDisplayNumberCell(workDayRow, 1, monthWorkDays == null ? null : new java.math.BigDecimal(monthWorkDays), numStyle);
        createTextCell(workDayRow, 2, "天", numStyle);
        createDisplayNumberCell(workDayRow, 3, monthWorkDays == null ? null : new java.math.BigDecimal(monthWorkDays * 8), numStyle);
        createTextCell(workDayRow, 4, "小时", numStyle);

        Row headRow = sheet.createRow(rowIdx++);
        Row standardRow = sheet.createRow(rowIdx++);
        createTextCell(headRow, 0, "人员", numStyle);
        createTextCell(headRow, 1, "标准数量", numStyle);
        CellRangeAddress personRegion = new CellRangeAddress(headRow.getRowNum(), standardRow.getRowNum(), 0, 0);
        CellRangeAddress standardRegion = new CellRangeAddress(headRow.getRowNum(), standardRow.getRowNum(), 1, 1);
        sheet.addMergedRegion(personRegion);
        sheet.addMergedRegion(standardRegion);
        RegionUtil.setBorderTop(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, personRegion, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, standardRegion, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, standardRegion, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, standardRegion, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, standardRegion, sheet);
        int col = 2;
        for (Map<String, Object> column : columns) {
            createTextCell(headRow, col, String.valueOf(column.get("label")), numStyle);
            createDisplayNumberCell(standardRow, col, (java.math.BigDecimal) column.get("standardQty"), numStyle);
            col++;
        }
        createTextCell(headRow, col, "月时间合计", numStyle);
        createTextCell(standardRow, col, "", numStyle);
        col++;
        createTextCell(headRow, col, "折合倍数", numStyle);
        createTextCell(standardRow, col, "", numStyle);

        Map<String, List<ProductionProductMainDto>> grouped = accountList.stream()
                .filter(item -> item != null && item.getSchedulingUserName() != null)
                .filter(item -> instrumentSheet == isInstrumentProduct(item.getProductName()))
                .sorted(java.util.Comparator
                        .comparing(ProductionProductMainDto::getSchedulingUserName, java.util.Comparator.nullsLast(String::compareTo))
                        .thenComparing(item -> StringUtils.isEmpty(item.getProductName()) ? "" : item.getProductName())
                        .thenComparing(item -> StringUtils.isEmpty(item.getProcess()) ? "" : item.getProcess()))
                .collect(Collectors.groupingBy(
                        ProductionProductMainDto::getSchedulingUserName,
                        LinkedHashMap::new,
                        Collectors.toList()
                ));

        for (Map.Entry<String, List<ProductionProductMainDto>> entry : grouped.entrySet()) {
            String staffName = entry.getKey();
            List<ProductionProductMainDto> staffItems = entry.getValue();
            java.math.BigDecimal staffMonthTimeTotal = java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP);
            Map<String, java.math.BigDecimal> quantityMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> convertedMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> foldedMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> violinQuantityMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> violaQuantityMap = new LinkedHashMap<>();
            Map<String, java.math.BigDecimal> bassQuantityMap = new LinkedHashMap<>();
            for (Map<String, Object> column : columns) {
                String key = String.valueOf(column.get("key"));
                quantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                convertedMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                foldedMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                violinQuantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                violaQuantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
                bassQuantityMap.put(key, java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP));
            }
            for (ProductionProductMainDto item : staffItems) {
                String key = instrumentSheet ? item.getProcess() : item.getProductName();
                if (!quantityMap.containsKey(key)) {
                    continue;
                }
                java.math.BigDecimal quantity = item.getQuantity() == null ? java.math.BigDecimal.ZERO : item.getQuantity();
                TechnologyOperation operation = technologyOperationMapper.selectOne(
                        new LambdaQueryWrapper<TechnologyOperation>()
                                .eq(TechnologyOperation::getName, item.getProcess())
                                .last("limit 1"));
                java.math.BigDecimal standardQty = instrumentSheet
                        ? (operation == null || operation.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : operation.getStandardQuantity())
                        : (item.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : item.getStandardQuantity());
                java.math.BigDecimal foldedQty = instrumentSheet ? calculateFoldedQuantity(item, operation) : quantity;
                java.math.BigDecimal convertedHours = calculateExportConvertedHours(foldedQty, standardQty, item.getRepairWorkHour());
                quantityMap.merge(key, quantity, java.math.BigDecimal::add);
                foldedMap.merge(key, foldedQty, java.math.BigDecimal::add);
                convertedMap.merge(key, convertedHours, java.math.BigDecimal::add);
                if (instrumentSheet) {
                    if ("小提琴".equals(item.getProductName())) {
                        violinQuantityMap.merge(key, quantity, java.math.BigDecimal::add);
                    } else if ("大提琴".equals(item.getProductName())) {
                        violaQuantityMap.merge(key, quantity, java.math.BigDecimal::add);
                    } else if ("贝斯".equals(item.getProductName())) {
                        bassQuantityMap.merge(key, quantity, java.math.BigDecimal::add);
                    }
                }
                staffMonthTimeTotal = staffMonthTimeTotal.add(convertedHours).setScale(2, java.math.RoundingMode.HALF_UP);
            }

            if (!staffItems.isEmpty()) {
                Row qtyRow = sheet.createRow(rowIdx++);
                qtyRow.setHeightInPoints(24f);
                createEdgeCell(sheet.getWorkbook(), qtyRow, 0, staffName, numStyle, true, false, true, true);
                createTextCell(qtyRow, 1, instrumentSheet ? "月产小" : "月产量", numStyle);
                int dataCol = 2;
                for (Map<String, Object> column : columns) {
                    String key = String.valueOf(column.get("key"));
                    createDisplayNumberCell(qtyRow, dataCol++, instrumentSheet ? violinQuantityMap.getOrDefault(key, java.math.BigDecimal.ZERO) : quantityMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                }
                createDisplayNumberCell(qtyRow, dataCol++, null, numStyle);
                createDisplayNumberCell(qtyRow, dataCol, null, numStyle);

                if (instrumentSheet) {
                    Row violaRow = sheet.createRow(rowIdx++);
                    violaRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), violaRow, 0, "", numStyle, false, false, true, true);
                    createTextCell(violaRow, 1, "月产大", numStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(violaRow, dataCol++, violaQuantityMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                    }
                    createDisplayNumberCell(violaRow, dataCol++, null, numStyle);
                    createDisplayNumberCell(violaRow, dataCol, null, numStyle);

                    Row bassRow = sheet.createRow(rowIdx++);
                    bassRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), bassRow, 0, "", numStyle, false, false, true, true);
                    createTextCell(bassRow, 1, "月产贝", numStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(bassRow, dataCol++, bassQuantityMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                    }
                    createDisplayNumberCell(bassRow, dataCol++, null, numStyle);
                    createDisplayNumberCell(bassRow, dataCol, null, numStyle);

                    Row foldedQtyRow = sheet.createRow(rowIdx++);
                    foldedQtyRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), foldedQtyRow, 0, "", numStyle, false, false, true, true);
                    createTextCell(foldedQtyRow, 1, "月产折合小提", numStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(foldedQtyRow, dataCol++, foldedMap.getOrDefault(key, java.math.BigDecimal.ZERO), numStyle);
                    }
                    createDisplayNumberCell(foldedQtyRow, dataCol++, null, numStyle);
                    createDisplayNumberCell(foldedQtyRow, dataCol, null, numStyle);

                    Row hoursRow = sheet.createRow(rowIdx++);
                    hoursRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), hoursRow, 0, "", headerStyle, false, true, true, true);
                    createTextCell(hoursRow, 1, "折算时长", headerStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(hoursRow, dataCol++, convertedMap.getOrDefault(key, java.math.BigDecimal.ZERO), headerStyle);
                    }
                    createDisplayNumberCell(hoursRow, dataCol++, staffMonthTimeTotal, headerStyle);
                    createDisplayNumberCell(hoursRow, dataCol, monthWorkDays == null || monthWorkDays <= 0
                            ? null
                            : staffMonthTimeTotal.divide(new java.math.BigDecimal(monthWorkDays * 8), 8, java.math.RoundingMode.HALF_UP).setScale(4, java.math.RoundingMode.HALF_UP), headerStyle);
                    CellRangeAddress staffRegion = new CellRangeAddress(qtyRow.getRowNum(), hoursRow.getRowNum(), 0, 0);
                    sheet.addMergedRegion(staffRegion);
                    RegionUtil.setBorderTop(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, staffRegion, sheet);
                } else {
                    Row hoursRow = sheet.createRow(rowIdx++);
                    hoursRow.setHeightInPoints(24f);
                    createEdgeCell(sheet.getWorkbook(), hoursRow, 0, "", headerStyle, false, true, true, true);
                    createTextCell(hoursRow, 1, "折算时长", headerStyle);
                    dataCol = 2;
                    for (Map<String, Object> column : columns) {
                        String key = String.valueOf(column.get("key"));
                        createDisplayNumberCell(hoursRow, dataCol++, convertedMap.getOrDefault(key, java.math.BigDecimal.ZERO), headerStyle);
                    }
                    createDisplayNumberCell(hoursRow, dataCol++, staffMonthTimeTotal, headerStyle);
                    createDisplayNumberCell(hoursRow, dataCol, monthWorkDays == null || monthWorkDays <= 0
                            ? null
                            : staffMonthTimeTotal.divide(new java.math.BigDecimal(monthWorkDays * 8), 8, java.math.RoundingMode.HALF_UP).setScale(4, java.math.RoundingMode.HALF_UP), headerStyle);
                    CellRangeAddress staffRegion = new CellRangeAddress(qtyRow.getRowNum(), hoursRow.getRowNum(), 0, 0);
                    sheet.addMergedRegion(staffRegion);
                    RegionUtil.setBorderTop(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderBottom(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderLeft(BorderStyle.THIN, staffRegion, sheet);
                    RegionUtil.setBorderRight(BorderStyle.THIN, staffRegion, sheet);
                }
            }
        }

        int[] widths = buildWorkHourColumnWidths(columns.size(), instrumentSheet);
        for (int i = 0; i < widths.length; i++) {
            sheet.setColumnWidth(i, widths[i] * 256);
        }
        return rowIdx;
    }

    private List<Map<String, Object>> buildWorkHourColumns(boolean instrumentSheet) {
        Map<String, Map<String, Object>> columnMap = new LinkedHashMap<>();
        if (instrumentSheet) {
            List<TechnologyOperation> operations = technologyOperationMapper.selectList(
                    new LambdaQueryWrapper<TechnologyOperation>()
                            .orderByAsc(TechnologyOperation::getId));
            for (TechnologyOperation operation : operations) {
                if (operation == null || StringUtils.isEmpty(operation.getName())) {
                    continue;
                }
                String key = operation.getName();
                if (columnMap.containsKey(key)) {
                    continue;
                }
                Map<String, Object> column = new LinkedHashMap<>();
                column.put("key", key);
                column.put("label", operation.getName());
                column.put("standardQty", operation.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : operation.getStandardQuantity());
                columnMap.put(key, column);
            }
        } else {
            List<Product> products = productMapper.selectList(
                    new LambdaQueryWrapper<Product>()
                            .isNotNull(Product::getParentId)
                            .orderByAsc(Product::getId));
            for (Product product : products) {
                if (product == null || StringUtils.isEmpty(product.getProductName())) {
                    continue;
                }
                if (isInstrumentProduct(product.getProductName())) {
                    continue;
                }
                String key = product.getProductName();
                if (columnMap.containsKey(key)) {
                    continue;
                }
                Map<String, Object> column = new LinkedHashMap<>();
                column.put("key", key);
                column.put("label", product.getProductName());
                column.put("standardQty", product.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : product.getStandardQuantity());
                columnMap.put(key, column);
            }
        }
        return new ArrayList<>(columnMap.values());
    }

    private int[] buildWorkHourColumnWidths(int columnCount, boolean instrumentSheet) {
        int baseSize = 2;
        int total = baseSize + columnCount;
        int[] widths = new int[total];
        widths[0] = 16;
        widths[1] = 14;
        for (int i = 2; i < total; i++) {
            widths[i] = 11;
        }
        return widths;
    }

    private java.math.BigDecimal calculateExportConvertedHours(java.math.BigDecimal foldedQty,
                                                               java.math.BigDecimal standardQty,
                                                               java.math.BigDecimal repairWorkHour) {
        if (foldedQty == null || standardQty == null || standardQty.compareTo(java.math.BigDecimal.ZERO) <= 0) {
            return repairWorkHour == null ? java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP)
                    : repairWorkHour.setScale(2, java.math.RoundingMode.HALF_UP);
        }
        return foldedQty.divide(standardQty, 8, java.math.RoundingMode.HALF_UP)
                .multiply(new java.math.BigDecimal("8"))
                .add(repairWorkHour == null ? java.math.BigDecimal.ZERO.setScale(2, java.math.RoundingMode.HALF_UP) : repairWorkHour.setScale(2, java.math.RoundingMode.HALF_UP))
                .setScale(2, java.math.RoundingMode.HALF_UP);
    }

    @Override
    public AjaxResult getAvailableDepts(String salaryMonth) {
        if (StringUtils.isEmpty(salaryMonth)) {
            return AjaxResult.error("工资月份不能为空");
        }

        List<String> issuedDeptIdStrings = staffSalaryMainMapper.selectIssuedDeptIds(salaryMonth);

        Set<Long> issuedDeptIds = new HashSet<>();
        for (String deptIds : issuedDeptIdStrings) {
            if (StringUtils.isNotEmpty(deptIds)) {
                String[] ids = deptIds.split(",");
                for (String id : ids) {
                    issuedDeptIds.add(Long.parseLong(id.trim()));
                }
            }
        }

        List<SysDept> allDepts = sysDeptMapper.selectList(new LambdaQueryWrapper<SysDept>()
                .eq(SysDept::getStatus, "0")
                .eq(SysDept::getDelFlag, "0"));

        List<SysDept> availableDepts = new ArrayList<>();
        for (SysDept dept : allDepts) {
            if (!issuedDeptIds.contains(dept.getDeptId())) {
                availableDepts.add(dept);
            }
        }

        return AjaxResult.success(availableDepts);
    }

    private boolean hasOverlappingDept(String deptIds1, String deptIds2) {
        if (StringUtils.isEmpty(deptIds1) || StringUtils.isEmpty(deptIds2)) {
            return false;
        }

        Set<String> deptSet1 = new HashSet<>(Arrays.asList(deptIds1.split(",")));
        String[] deptArray2 = deptIds2.split(",");

        for (String deptId : deptArray2) {
            if (deptSet1.contains(deptId.trim())) {
                return true;
            }
        }
        return false;
    }

    private String buildWorkHoursTitle(StaffSalaryMain main) {
        String month = StringUtils.isNotEmpty(main.getSalaryMonth()) ? main.getSalaryMonth() : "未知月份";
        return month + "工时统计";
    }

    private Integer calculateWorkDays(String salaryMonth) {
        if (StringUtils.isEmpty(salaryMonth)) {
            return 0;
        }
        java.time.YearMonth yearMonth = java.time.YearMonth.parse(salaryMonth);
        int workDays = 0;
        for (int day = 1; day <= yearMonth.lengthOfMonth(); day++) {
            java.time.DayOfWeek dayOfWeek = yearMonth.atDay(day).getDayOfWeek();
            if (dayOfWeek != java.time.DayOfWeek.SATURDAY && dayOfWeek != java.time.DayOfWeek.SUNDAY) {
                workDays++;
            }
        }
        return workDays;
    }

    private CellStyle createStyle(Workbook workbook, boolean bold, HorizontalAlignment alignment, int fontSize, boolean fill) {
        Font font = workbook.createFont();
        font.setFontName("Microsoft YaHei");
        font.setFontHeightInPoints((short) fontSize);
        font.setBold(bold);
        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(alignment);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        if (fill) {
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }
        return style;
    }

    private void createTextCell(Row row, int colIndex, String value, CellStyle style) {
        Cell cell = row.createCell(colIndex);
        cell.setCellValue(value == null ? "" : value);
        cell.setCellStyle(style);
    }

    private void createEdgeCell(Workbook workbook,
                                Row row,
                                int colIndex,
                                String value,
                                CellStyle baseStyle,
                                boolean topBorder,
                                boolean bottomBorder,
                                boolean leftBorder,
                                boolean rightBorder) {
        CellStyle edgeStyle = workbook.createCellStyle();
        edgeStyle.cloneStyleFrom(baseStyle);
        edgeStyle.setBorderTop(topBorder ? BorderStyle.THIN : BorderStyle.NONE);
        edgeStyle.setBorderBottom(bottomBorder ? BorderStyle.THIN : BorderStyle.NONE);
        edgeStyle.setBorderLeft(leftBorder ? BorderStyle.THIN : BorderStyle.NONE);
        edgeStyle.setBorderRight(rightBorder ? BorderStyle.THIN : BorderStyle.NONE);
        Cell cell = row.createCell(colIndex);
        cell.setCellValue(value == null ? "" : value);
        cell.setCellStyle(edgeStyle);
    }

    private void createNumberCell(Row row, int colIndex, java.math.BigDecimal value, CellStyle style) {
        Cell cell = row.createCell(colIndex);
        if (value != null) {
            cell.setCellValue(value.doubleValue());
        }
        cell.setCellStyle(style);
    }

    private void createDisplayNumberCell(Row row, int colIndex, java.math.BigDecimal value, CellStyle style) {
        Cell cell = row.createCell(colIndex);
        if (value != null && value.compareTo(java.math.BigDecimal.ZERO) != 0) {
            cell.setCellValue(value.doubleValue());
        } else {
            cell.setCellValue("");
        }
        cell.setCellStyle(style);
    }

    private void createFormulaCell(Row row, int colIndex, String formula, CellStyle style) {
        Cell cell = row.createCell(colIndex);
        cell.setCellFormula(formula.startsWith("=") ? formula.substring(1) : formula);
        cell.setCellStyle(style);
    }

    private String toCellRef(int colIndex, int rowNum1Based) {
        return cellRef(colIndex) + rowNum1Based;
    }

    private String cellRef(int colIndex) {
        StringBuilder sb = new StringBuilder();
        int col = colIndex;
        do {
            int rem = col % 26;
            sb.insert(0, (char) ('A' + rem));
            col = col / 26 - 1;
        } while (col >= 0);
        return sb.toString();
    }

    private List<ProductionProductMainDto> queryProductionDetails(String salaryMonth) {
        if (StringUtils.isEmpty(salaryMonth)) {
            return new ArrayList<>();
        }
        YearMonth ym = YearMonth.parse(salaryMonth);
        LocalDate start = ym.atDay(1);
        LocalDate end = ym.atEndOfMonth();
        ProductionAccountDto dto = new ProductionAccountDto();
        dto.setEntryDateStart(start);
        dto.setEntryDateEnd(end);
        dto.setAuditStatus(1);
        Page<ProductionAccountDto> page = new Page<>(1, 100000);
        return productionProductMainMapper.listProductionDetails(dto, page).getRecords();
    }

    private Map<String, java.math.BigDecimal> buildOperationQuantityMap(List<ProductionProductMainDto> accountList, String operationName, TechnologyOperation operation) {
        Map<String, java.math.BigDecimal> map = new LinkedHashMap<>();
        if (CollectionUtils.isEmpty(accountList) || StringUtils.isEmpty(operationName)) {
            return map;
        }
        accountList.stream()
                .filter(item -> item != null && operationName.equals(item.getProcess()))
                .filter(item -> StringUtils.isNotEmpty(item.getSchedulingUserName()))
                .forEach(item -> map.merge(item.getSchedulingUserName(),
                        calculateFoldedQuantity(item, operation),
                        java.math.BigDecimal::add));
        return map;
    }

    private java.math.BigDecimal calculateSheetWage(TechnologyOperation operation,
                                                    String operationName,
                                                    java.math.BigDecimal qty,
                                                    java.math.BigDecimal standardQty) {
        if (operation == null || qty == null) {
            return java.math.BigDecimal.ZERO;
        }
        if (standardQty == null || standardQty.compareTo(java.math.BigDecimal.ZERO) <= 0) {
            return java.math.BigDecimal.ZERO;
        }
        return qty.divide(standardQty, 2, java.math.RoundingMode.HALF_UP).multiply(new java.math.BigDecimal("8"));
    }

    private java.math.BigDecimal calculateFoldedQuantity(ProductionProductMainDto item, TechnologyOperation operation) {
        if (item == null || operation == null) {
            return java.math.BigDecimal.ZERO;
        }
        java.math.BigDecimal quantity = item.getQuantity() == null ? java.math.BigDecimal.ZERO : item.getQuantity();
        String productName = item.getProductName();
        if ("小提琴".equals(productName)) {
            return operation.getViolinFactor() == null ? java.math.BigDecimal.ZERO : quantity.multiply(operation.getViolinFactor());
        }
        if ("大提琴".equals(productName)) {
            return operation.getViolaFactor() == null ? java.math.BigDecimal.ZERO : quantity.multiply(operation.getViolaFactor());
        }
        if ("贝斯".equals(productName)) {
            return operation.getBassFactor() == null ? java.math.BigDecimal.ZERO : quantity.multiply(operation.getBassFactor());
        }
        return java.math.BigDecimal.ZERO;
    }

    private Map<String, java.math.BigDecimal> buildStandardQuantityMap(List<String> operationNames) {
        Map<String, java.math.BigDecimal> map = new LinkedHashMap<>();
        if (CollectionUtils.isEmpty(operationNames)) {
            return map;
        }
        List<TechnologyOperation> operations = technologyOperationMapper.selectList(
                new LambdaQueryWrapper<TechnologyOperation>()
                        .in(TechnologyOperation::getName, operationNames)
        );
        Map<String, java.math.BigDecimal> opMap = operations.stream()
                .filter(item -> item != null && StringUtils.isNotEmpty(item.getName()))
                .collect(Collectors.toMap(
                        TechnologyOperation::getName,
                        item -> item.getStandardQuantity() == null ? java.math.BigDecimal.ZERO : item.getStandardQuantity(),
                        (left, right) -> left,
                        LinkedHashMap::new
                ));
        for (String operationName : operationNames) {
            map.put(operationName, opMap.getOrDefault(operationName, java.math.BigDecimal.ZERO));
        }
        return map;
    }

}