package cn.iocoder.lyzsys.module.tjt.service.report;

import cn.iocoder.lyzsys.framework.common.util.object.BeanUtils;
import cn.iocoder.lyzsys.framework.excel.core.util.ExcelUtils;
import cn.iocoder.lyzsys.framework.mybatis.core.query.LambdaQueryWrapperX;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.EmployeeOutputSummaryExcelRespVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.EmployeeOutputSummaryExportReqVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.ProjectBudgetExportReqVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.ProjectLeadQuarterOutputExportReqVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.ProjectOverviewExcelRespVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.ProjectOverviewExportReqVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.ProjectQuarterOutputExportReqVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.report.vo.SpecialtyPersonOutputExportReqVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.specialtyrolesplit.vo.SpecialtyRolePersonRespVO;
import cn.iocoder.lyzsys.module.tjt.controller.admin.specialtyrolesplit.vo.SpecialtyRoleSplitRespVO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.employee.EmployeeDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.employeeyearcostbudget.EmployeeYearCostBudgetDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.office.OfficeDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.outputsplit.ProjectOutputSplitDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.planning.ProjectPlanningDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.planningquarter.ProjectPlanningQuarterDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.project.ProjectDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.projectroleperson.ProjectRolePersonDO;
import cn.iocoder.lyzsys.module.tjt.dal.dataobject.yearkvalue.YearKValueDO;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.employee.EmployeeMapper;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.employeeyearcostbudget.EmployeeYearCostBudgetMapper;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.office.OfficeMapper;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.planning.ProjectPlanningMapper;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.planningquarter.ProjectPlanningQuarterMapper;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.project.ProjectMapper;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.projectroleperson.ProjectRolePersonMapper;
import cn.iocoder.lyzsys.module.tjt.dal.mysql.yearkvalue.YearKValueMapper;
import cn.iocoder.lyzsys.module.tjt.enums.OutputSplitBizConstants;
import cn.iocoder.lyzsys.module.tjt.enums.ProjectPlanningBizTypeConstants;
import cn.iocoder.lyzsys.module.tjt.service.outputsplit.ProjectOutputSplitService;
import cn.iocoder.lyzsys.module.tjt.service.report.builder.ProjectBudgetExcelBuilder;
import cn.iocoder.lyzsys.module.tjt.service.report.builder.ProjectLeadQuarterOutputExcelBuilder;
import cn.iocoder.lyzsys.module.tjt.service.report.builder.ProjectQuarterOutputExcelBuilder;
import cn.iocoder.lyzsys.module.tjt.service.report.builder.SpecialtyPersonOutputExcelBuilder;
import cn.iocoder.lyzsys.module.tjt.service.specialtyrolesplit.SpecialtyRoleSplitService;
import lombok.Data;
import org.springframework.stereotype.Service;
import org.springframework.validation.annotation.Validated;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;

import static cn.iocoder.lyzsys.framework.common.exception.util.ServiceExceptionUtil.exception;
import static cn.iocoder.lyzsys.module.tjt.enums.ErrorCodeConstants.PROJECT_NOT_EXISTS;
import static cn.iocoder.lyzsys.module.tjt.enums.ErrorCodeConstants.PROJECT_OUTPUT_SPLIT_NOT_MAJOR;
import static cn.iocoder.lyzsys.module.tjt.enums.ErrorCodeConstants.PROJECT_PLANNING_NOT_EXISTS;
import static cn.iocoder.lyzsys.module.tjt.enums.ErrorCodeConstants.SPECIALTY_ROLE_SPLIT_SPECIALTY_INVALID;

@Service
@Validated
public class ProjectOutputReportServiceImpl implements ProjectOutputReportService {

    private static final BigDecimal ZERO_AMOUNT = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
    private static final BigDecimal ZERO_RATIO = BigDecimal.ZERO.setScale(4, RoundingMode.HALF_UP);
    private static final BigDecimal DEFAULT_K_VALUE = new BigDecimal("0.4000");

    private static final String SORT_OUTPUT_DESC = "output_desc";
    private static final String SORT_OUTPUT_ASC = "output_asc";
    private static final String SORT_NAME_ASC = "name_asc";
    private static final String SORT_ANNUAL_TOTAL_DESC = "annual_total_desc";
    private static final String SORT_ANNUAL_TOTAL_ASC = "annual_total_asc";

    @Resource
    private ProjectPlanningMapper projectPlanningMapper;
    @Resource
    private ProjectMapper projectMapper;
    @Resource
    private ProjectPlanningQuarterMapper projectPlanningQuarterMapper;
    @Resource
    private ProjectRolePersonMapper projectRolePersonMapper;
    @Resource
    private EmployeeMapper employeeMapper;
    @Resource
    private OfficeMapper officeMapper;
    @Resource
    private EmployeeYearCostBudgetMapper employeeYearCostBudgetMapper;
    @Resource
    private YearKValueMapper yearKValueMapper;
    @Resource
    private ProjectOutputSplitService projectOutputSplitService;
    @Resource
    private SpecialtyRoleSplitService specialtyRoleSplitService;
    @Resource
    private ProjectBudgetExcelBuilder projectBudgetExcelBuilder;
    @Resource
    private ProjectQuarterOutputExcelBuilder projectQuarterOutputExcelBuilder;
    @Resource
    private ProjectLeadQuarterOutputExcelBuilder projectLeadQuarterOutputExcelBuilder;
    @Resource
    private SpecialtyPersonOutputExcelBuilder specialtyPersonOutputExcelBuilder;

    @Override
    public void exportProjectBudgetExcel(HttpServletResponse response, ProjectBudgetExportReqVO reqVO)
            throws IOException {
        ProjectDO project = validateProjectExists(reqVO.getProjectId());
        List<ProjectPlanningDO> planningList = sortPlanningList(projectPlanningMapper.selectListByProjectId(project.getId()));
        List<ProjectRolePersonDO> rolePersons = projectRolePersonMapper.selectListByProjectId(project.getId());
        Map<Long, List<ProjectPlanningQuarterDO>> quarterMap = getQuarterMap(planningList);

        ProjectBudgetExcelBuilder.ExportData data = new ProjectBudgetExcelBuilder.ExportData();
        data.setProjectCode("");
        data.setProjectName(project.getProjectName());
        data.setProjectManagerName(joinProjectRoleNames(rolePersons, OutputSplitBizConstants.ROLE_PROJECT_MANAGER));
        data.setEngineeringPrincipalName(joinProjectRoleNames(rolePersons, OutputSplitBizConstants.ROLE_ENGINEERING_PRINCIPAL));
        data.setTotalConstructionArea(amount(project.getTotalConstructionArea()));
        data.setContractUnitPrice(firstNonZero(planningList.stream()
                .map(ProjectPlanningDO::getContractUnitPrice)
                .collect(Collectors.toList())));
        data.setInternalGuidanceUnitPrice(firstNonZero(planningList.stream()
                .map(ProjectPlanningDO::getInternalGuidanceUnitPrice)
                .collect(Collectors.toList())));
        data.setRemark(joinPlanningRemarks(planningList));

        BigDecimal totalAssessmentArea = ZERO_AMOUNT;
        BigDecimal totalAssessmentOutputValue = ZERO_AMOUNT;
        for (ProjectPlanningDO planning : planningList) {
            totalAssessmentArea = totalAssessmentArea.add(amount(planning.getAssessmentArea())).setScale(2, RoundingMode.HALF_UP);
            totalAssessmentOutputValue = totalAssessmentOutputValue.add(amount(planning.getAssessmentOutputValue()))
                    .setScale(2, RoundingMode.HALF_UP);
        }
        data.setBudgetRows(buildProjectBudgetRows(planningList));
        data.setTotalAssessmentArea(totalAssessmentArea);
        data.setTotalAssessmentOutputValue(totalAssessmentOutputValue);
        data.setTotalAssessmentOutputValueWan(amountToWan(totalAssessmentOutputValue));
        data.setQuarterBudgetRows(buildQuarterBudgetRows(planningList, quarterMap, LocalDate.now().getYear()));
        data.setYearRangeText(buildProjectBudgetYearRangeText(planningList, quarterMap));

        projectBudgetExcelBuilder.writeWorkbook(response,
                buildFileName(project.getProjectName(), "项目考核产值预算表"),
                projectBudgetExcelBuilder.build(data));
    }

    @Override
    public void exportProjectQuarterOutputExcel(HttpServletResponse response, ProjectQuarterOutputExportReqVO reqVO)
            throws IOException {
        ProjectPlanningDO anchorPlanning = validateMajorPlanning(reqVO.getPlanningId());
        ProjectDO project = validateProjectExists(anchorPlanning.getProjectId());
        Integer reportYear = resolveReportYear(anchorPlanning);
        List<ProjectPlanningDO> planningList = sortPlanningList(getProjectPlanningScope(anchorPlanning, true));
        Map<Long, List<ProjectPlanningQuarterDO>> quarterMap = getQuarterMap(planningList);
        Map<Long, ProjectOutputSplitDO> outputSplitMap = getOutputSplitMap(planningList);

        ProjectQuarterOutputExcelBuilder.ExportData data = new ProjectQuarterOutputExcelBuilder.ExportData();
        data.setProjectName(project.getProjectName());
        data.setYear(reportYear);

        List<ProjectQuarterOutputExcelBuilder.QuarterRow> rows = new ArrayList<>();
        for (ProjectPlanningDO planning : planningList) {
            QuarterSummary quarterSummary = buildQuarterSummary(planning, quarterMap.get(planning.getId()), reportYear);
            ProjectOutputSplitDO outputSplit = outputSplitMap.get(planning.getId());
            if (outputSplit == null) {
                continue;
            }
            ProjectQuarterOutputExcelBuilder.QuarterRow row = new ProjectQuarterOutputExcelBuilder.QuarterRow();
            row.setOwnershipType(planning.getOwnershipType());
            row.setPlanningContent(planning.getPlanningContent());
            row.setHistoricalIssuedRatio(quarterSummary.getHistoricalIssuedRatio());
            row.setCurrentYearRatio(quarterSummary.getCurrentYearRatio());
            row.setPendingRatio(quarterSummary.getPendingRatio());
            row.setQuarterOneAmount(quarterSummary.getQuarterOneAmount());
            row.setQuarterTwoAmount(quarterSummary.getQuarterTwoAmount());
            row.setQuarterThreeAmount(quarterSummary.getQuarterThreeAmount());
            row.setQuarterFourAmount(quarterSummary.getQuarterFourAmount());
            row.setYearTotalAmount(quarterSummary.getYearTotalAmount());
            row.setProjectLeadAmount(multiplyAmount(quarterSummary.getYearTotalAmount(), outputSplit.getProjectLeadRatio()));
            row.setOfficeAmount(multiplyAmount(quarterSummary.getYearTotalAmount(), outputSplit.getOfficeRatio()));
            row.setSpecialtySummaryText(buildSpecialtySummary(outputSplit, quarterSummary.getYearTotalAmount()));
            row.setAssessmentOutputValue(amount(planning.getAssessmentOutputValue()));
            rows.add(row);
        }
        data.setRows(rows);

        projectQuarterOutputExcelBuilder.writeWorkbook(response,
                buildFileName(project.getProjectName(), "项目级年度季度计取表"),
                projectQuarterOutputExcelBuilder.build(data));
    }

    @Override
    public void exportProjectLeadQuarterOutputExcel(HttpServletResponse response,
                                                    ProjectLeadQuarterOutputExportReqVO reqVO)
            throws IOException {
        ProjectPlanningDO anchorPlanning = validateMajorPlanning(reqVO.getPlanningId());
        ProjectDO project = validateProjectExists(anchorPlanning.getProjectId());
        Integer reportYear = resolveReportYear(anchorPlanning);
        List<ProjectPlanningDO> planningList = sortPlanningList(getProjectPlanningScope(anchorPlanning, true));
        Map<Long, List<ProjectPlanningQuarterDO>> quarterMap = getQuarterMap(planningList);
        Map<Long, ProjectOutputSplitDO> outputSplitMap = getOutputSplitMap(planningList);
        Map<Long, List<SpecialtyRoleSplitRespVO>> roleSplitMap = getRoleSplitMap(planningList);

        ProjectLeadQuarterOutputExcelBuilder.ExportData data = new ProjectLeadQuarterOutputExcelBuilder.ExportData();
        data.setProjectName(project.getProjectName());
        data.setYear(reportYear);

        List<ProjectLeadQuarterOutputExcelBuilder.LeadQuarterRow> rows = new ArrayList<>();
        for (ProjectPlanningDO planning : planningList) {
            ProjectOutputSplitDO outputSplit = outputSplitMap.get(planning.getId());
            if (outputSplit == null) {
                continue;
            }
            BigDecimal projectLeadRatio = ratio(outputSplit.getProjectLeadRatio());
            QuarterSummary quarterSummary = buildQuarterSummary(planning, quarterMap.get(planning.getId()), reportYear);
            List<SpecialtyRoleSplitRespVO> roleList = roleSplitMap.getOrDefault(planning.getId(), Collections.emptyList());
            SpecialtyRoleSplitRespVO projectManagerRow = findRoleRow(roleList,
                    OutputSplitBizConstants.SPECIALTY_PROJECT_LEAD, OutputSplitBizConstants.ROLE_PROJECT_MANAGER);
            SpecialtyRoleSplitRespVO engineeringPrincipalRow = findRoleRow(roleList,
                    OutputSplitBizConstants.SPECIALTY_PROJECT_LEAD, OutputSplitBizConstants.ROLE_ENGINEERING_PRINCIPAL);

            BigDecimal projectManagerRatio = projectManagerRow == null ? ZERO_RATIO : ratio(projectManagerRow.getRoleRatio());
            BigDecimal engineeringPrincipalRatio = engineeringPrincipalRow == null
                    ? ZERO_RATIO : ratio(engineeringPrincipalRow.getRoleRatio());

            ProjectLeadQuarterOutputExcelBuilder.LeadQuarterRow row =
                    new ProjectLeadQuarterOutputExcelBuilder.LeadQuarterRow();
            row.setPlanningContent(planning.getPlanningContent());
            row.setProjectManagerNames(joinRoleSplitPersonNames(projectManagerRow));
            row.setProjectManagerRatio(projectManagerRatio);
            row.setEngineeringPrincipalNames(joinRoleSplitPersonNames(engineeringPrincipalRow));
            row.setEngineeringPrincipalRatio(engineeringPrincipalRatio);
            row.setProjectManagerQuarterOneAmount(multiplyAmount(quarterSummary.getQuarterOneAmount(),
                    projectLeadRatio, projectManagerRatio));
            row.setProjectManagerQuarterTwoAmount(multiplyAmount(quarterSummary.getQuarterTwoAmount(),
                    projectLeadRatio, projectManagerRatio));
            row.setProjectManagerQuarterThreeAmount(multiplyAmount(quarterSummary.getQuarterThreeAmount(),
                    projectLeadRatio, projectManagerRatio));
            row.setProjectManagerQuarterFourAmount(multiplyAmount(quarterSummary.getQuarterFourAmount(),
                    projectLeadRatio, projectManagerRatio));
            row.setProjectManagerYearTotalAmount(multiplyAmount(quarterSummary.getYearTotalAmount(),
                    projectLeadRatio, projectManagerRatio));
            row.setEngineeringPrincipalQuarterOneAmount(multiplyAmount(quarterSummary.getQuarterOneAmount(),
                    projectLeadRatio, engineeringPrincipalRatio));
            row.setEngineeringPrincipalQuarterTwoAmount(multiplyAmount(quarterSummary.getQuarterTwoAmount(),
                    projectLeadRatio, engineeringPrincipalRatio));
            row.setEngineeringPrincipalQuarterThreeAmount(multiplyAmount(quarterSummary.getQuarterThreeAmount(),
                    projectLeadRatio, engineeringPrincipalRatio));
            row.setEngineeringPrincipalQuarterFourAmount(multiplyAmount(quarterSummary.getQuarterFourAmount(),
                    projectLeadRatio, engineeringPrincipalRatio));
            row.setEngineeringPrincipalYearTotalAmount(multiplyAmount(quarterSummary.getYearTotalAmount(),
                    projectLeadRatio, engineeringPrincipalRatio));
            row.setTotalAmount(multiplyAmount(quarterSummary.getYearTotalAmount(), projectLeadRatio));
            rows.add(row);
        }
        data.setRows(rows);

        projectLeadQuarterOutputExcelBuilder.writeWorkbook(response,
                buildFileName(project.getProjectName(), "项目负责人年度季度计取表"),
                projectLeadQuarterOutputExcelBuilder.build(data));
    }

    @Override
    public void exportSpecialtyPersonOutputExcel(HttpServletResponse response,
                                                 SpecialtyPersonOutputExportReqVO reqVO) throws IOException {
        validateSpecialtyCode(reqVO.getSpecialtyCode());
        ProjectPlanningDO planning = validateMajorPlanning(reqVO.getPlanningId());
        ProjectDO project = validateProjectExists(planning.getProjectId());
        Integer reportYear = resolveReportYear(planning);
        ProjectOutputSplitDO outputSplit = projectOutputSplitService.getOrCreateProjectOutputSplit(planning.getId());
        QuarterSummary quarterSummary = buildQuarterSummary(planning,
                projectPlanningQuarterMapper.selectListByPlanningId(planning.getId()), reportYear);
        BigDecimal specialtyBaseRatio = multiplyRatio(outputSplit.getOfficeRatio(),
                getSpecialtyRatio(outputSplit, reqVO.getSpecialtyCode()));

        List<SpecialtyRoleSplitRespVO> roleList = specialtyRoleSplitService.getSpecialtyRoleSplitListByPlanningId(planning.getId());
        List<SpecialtyRoleSplitRespVO> specialtyRoleList = roleList.stream()
                .filter(item -> Objects.equals(item.getSpecialtyCode(), reqVO.getSpecialtyCode()))
                .collect(Collectors.toList());

        SpecialtyPersonOutputExcelBuilder.ExportData data = new SpecialtyPersonOutputExcelBuilder.ExportData();
        data.setProjectName(project.getProjectName());
        data.setPlanningContent(planning.getPlanningContent());
        data.setSpecialtyName(OutputSplitBizConstants.getSpecialtyName(reqVO.getSpecialtyCode()));
        data.setYear(reportYear);

        List<SpecialtyPersonOutputExcelBuilder.PersonQuarterRow> rows = new ArrayList<>();
        for (SpecialtyRoleSplitRespVO roleRow : specialtyRoleList) {
            BigDecimal roleRatio = ratio(roleRow.getRoleRatio());
            List<SpecialtyRolePersonRespVO> persons = roleRow.getPersons() == null
                    ? Collections.emptyList() : roleRow.getPersons();
            if (persons.isEmpty()) {
                SpecialtyPersonOutputExcelBuilder.PersonQuarterRow row =
                        new SpecialtyPersonOutputExcelBuilder.PersonQuarterRow();
                row.setRoleName(roleRow.getRoleName());
                row.setEmployeeName("-");
                row.setRoleRatio(roleRatio);
                row.setPersonRatio(ZERO_RATIO);
                row.setQuarterOneAmount(ZERO_AMOUNT);
                row.setQuarterTwoAmount(ZERO_AMOUNT);
                row.setQuarterThreeAmount(ZERO_AMOUNT);
                row.setQuarterFourAmount(ZERO_AMOUNT);
                row.setYearTotalAmount(ZERO_AMOUNT);
                rows.add(row);
                continue;
            }
            for (SpecialtyRolePersonRespVO person : persons) {
                BigDecimal personRatio = ratio(person.getPersonRatio());
                BigDecimal totalRatio = multiplyRatio(specialtyBaseRatio, roleRatio, personRatio);
                SpecialtyPersonOutputExcelBuilder.PersonQuarterRow row =
                        new SpecialtyPersonOutputExcelBuilder.PersonQuarterRow();
                row.setRoleName(roleRow.getRoleName());
                row.setEmployeeName(person.getEmployeeName());
                row.setRoleRatio(roleRatio);
                row.setPersonRatio(personRatio);
                row.setQuarterOneAmount(multiplyAmount(quarterSummary.getQuarterOneAmount(), totalRatio));
                row.setQuarterTwoAmount(multiplyAmount(quarterSummary.getQuarterTwoAmount(), totalRatio));
                row.setQuarterThreeAmount(multiplyAmount(quarterSummary.getQuarterThreeAmount(), totalRatio));
                row.setQuarterFourAmount(multiplyAmount(quarterSummary.getQuarterFourAmount(), totalRatio));
                row.setYearTotalAmount(multiplyAmount(quarterSummary.getYearTotalAmount(), totalRatio));
                rows.add(row);
            }
        }
        data.setRows(rows);

        specialtyPersonOutputExcelBuilder.writeWorkbook(response,
                buildFileName(project.getProjectName(),
                        data.getSpecialtyName() + "专业内人员年度季度计取表"),
                specialtyPersonOutputExcelBuilder.build(data));
    }

    @Override
    public void exportProjectOverviewExcel(HttpServletResponse response, ProjectOverviewExportReqVO reqVO)
            throws IOException {
        validateSpecialtyCode(reqVO.getSpecialtyCode());
        List<ProjectPlanningDO> majorPlanningList = projectPlanningMapper.selectList(
                new LambdaQueryWrapperX<ProjectPlanningDO>()
                        .eq(ProjectPlanningDO::getOwnershipType, ProjectPlanningBizTypeConstants.OWNERSHIP_TYPE_MAJOR));
        if (majorPlanningList.isEmpty()) {
            ExcelUtils.write(response, "项目总览表.xlsx", "项目总览", ProjectOverviewExcelRespVO.class,
                    Collections.emptyList());
            return;
        }

        Map<Long, ProjectDO> projectMap = getProjectMap(majorPlanningList);
        Map<Long, List<ProjectPlanningQuarterDO>> quarterMap = getQuarterMap(majorPlanningList);
        Map<Long, ProjectOutputSplitDO> outputSplitMap = getOutputSplitMap(majorPlanningList);

        List<ProjectOverviewExcelRespVO> rows = new ArrayList<>();
        for (ProjectPlanningDO planning : majorPlanningList) {
            ProjectOutputSplitDO outputSplit = outputSplitMap.get(planning.getId());
            if (outputSplit == null) {
                continue;
            }
            QuarterSummary quarterSummary = buildQuarterSummary(planning, quarterMap.get(planning.getId()), reqVO.getYear());
            BigDecimal combinedRatio = addRatio(outputSplit.getProjectLeadRatio(),
                    multiplyRatio(outputSplit.getOfficeRatio(), getSpecialtyRatio(outputSplit, reqVO.getSpecialtyCode())));
            if (combinedRatio.compareTo(ZERO_RATIO) <= 0 || quarterSummary.getYearTotalAmount().compareTo(ZERO_AMOUNT) <= 0) {
                continue;
            }
            ProjectDO project = projectMap.get(planning.getProjectId());
            ProjectOverviewExcelRespVO row = new ProjectOverviewExcelRespVO();
            row.setProjectName(project == null ? "" : project.getProjectName());
            row.setPlanningContent(planning.getPlanningContent());
            row.setProgressText(buildProgressText(project, planning));
            row.setDesignStage(planning.getDesignStage());
            row.setTotalOutputAmount(multiplyAmount(amount(planning.getAssessmentOutputValue()),
                    defaultDistributionRatio(planning), combinedRatio));
            row.setHistoricalIssuedRatioText(percentText(quarterSummary.getHistoricalIssuedRatio()));
            row.setCurrentSettlementAmount(multiplyAmount(quarterSummary.getYearTotalAmount(), combinedRatio));
            row.setPendingRatioText(percentText(quarterSummary.getPendingRatio()));
            row.setQuarterOneAmount(multiplyAmount(quarterSummary.getQuarterOneAmount(), combinedRatio));
            row.setQuarterTwoAmount(multiplyAmount(quarterSummary.getQuarterTwoAmount(), combinedRatio));
            row.setQuarterThreeAmount(multiplyAmount(quarterSummary.getQuarterThreeAmount(), combinedRatio));
            row.setQuarterFourAmount(multiplyAmount(quarterSummary.getQuarterFourAmount(), combinedRatio));
            row.setYearTotalAmount(multiplyAmount(quarterSummary.getYearTotalAmount(), combinedRatio));
            rows.add(row);
        }

        sortProjectOverviewRows(rows, reqVO.getSortType());
        for (int i = 0; i < rows.size(); i++) {
            rows.get(i).setSerialNo(i + 1);
        }
        ExcelUtils.write(response,
                buildFileName(OutputSplitBizConstants.getSpecialtyName(reqVO.getSpecialtyCode()), "项目总览表"),
                "项目总览", ProjectOverviewExcelRespVO.class, rows);
    }

    @Override
    public void exportEmployeeOutputSummaryExcel(HttpServletResponse response,
                                                 EmployeeOutputSummaryExportReqVO reqVO) throws IOException {
        List<EmployeeDO> employeeList = employeeMapper.selectList(new LambdaQueryWrapperX<EmployeeDO>()
                .eqIfPresent(EmployeeDO::getId, reqVO.getEmployeeId())
                .eqIfPresent(EmployeeDO::getOfficeId, reqVO.getOfficeId())
                .eqIfPresent(EmployeeDO::getEmployeeStatus, reqVO.getEmployeeStatus())
                .eq(EmployeeDO::getEnabledFlag, Boolean.TRUE)
                .orderByAsc(EmployeeDO::getSortNo)
                .orderByAsc(EmployeeDO::getId));
        if (employeeList.isEmpty()) {
            ExcelUtils.write(response, "员工个人考核产值汇总.xlsx", "员工汇总",
                    EmployeeOutputSummaryExcelRespVO.class, Collections.emptyList());
            return;
        }

        Set<Long> employeeIds = employeeList.stream().map(EmployeeDO::getId).collect(Collectors.toCollection(LinkedHashSet::new));
        Map<Long, EmployeeDO> employeeMap = employeeList.stream()
                .collect(Collectors.toMap(EmployeeDO::getId, item -> item, (a, b) -> a, LinkedHashMap::new));
        Map<Long, OfficeDO> officeMap = getOfficeMap(employeeList);

        List<ProjectPlanningDO> majorPlanningList = projectPlanningMapper.selectList(
                new LambdaQueryWrapperX<ProjectPlanningDO>()
                        .eq(ProjectPlanningDO::getOwnershipType, ProjectPlanningBizTypeConstants.OWNERSHIP_TYPE_MAJOR));
        Map<Long, List<ProjectPlanningQuarterDO>> quarterMap = getQuarterMap(majorPlanningList);

        List<ProjectPlanningDO> relevantPlanningList = majorPlanningList.stream()
                .filter(item -> hasYearAmount(quarterMap.get(item.getId()), reqVO.getYear()))
                .collect(Collectors.toList());

        Map<Long, ProjectOutputSplitDO> outputSplitMap = getOutputSplitMap(relevantPlanningList);
        Map<Long, List<SpecialtyRoleSplitRespVO>> roleSplitMap = getRoleSplitMap(relevantPlanningList);
        Map<Long, EmployeeAccumulator> accumulatorMap = new LinkedHashMap<>();
        employeeIds.forEach(id -> accumulatorMap.put(id, new EmployeeAccumulator()));

        for (ProjectPlanningDO planning : relevantPlanningList) {
            QuarterSummary quarterSummary = buildQuarterSummary(planning, quarterMap.get(planning.getId()), reqVO.getYear());
            ProjectOutputSplitDO outputSplit = outputSplitMap.get(planning.getId());
            if (outputSplit == null) {
                continue;
            }
            List<SpecialtyRoleSplitRespVO> roleList = roleSplitMap.getOrDefault(planning.getId(), Collections.emptyList());
            for (SpecialtyRoleSplitRespVO roleRow : roleList) {
                BigDecimal categoryRatio = getCategoryRatio(outputSplit, roleRow.getSpecialtyCode());
                BigDecimal roleRatio = ratio(roleRow.getRoleRatio());
                List<SpecialtyRolePersonRespVO> persons = roleRow.getPersons() == null
                        ? Collections.emptyList() : roleRow.getPersons();
                for (SpecialtyRolePersonRespVO person : persons) {
                    if (person.getEmployeeId() == null || !employeeIds.contains(person.getEmployeeId())) {
                        continue;
                    }
                    BigDecimal totalRatio = multiplyRatio(categoryRatio, roleRatio, person.getPersonRatio());
                    EmployeeAccumulator accumulator = accumulatorMap.get(person.getEmployeeId());
                    accumulator.addQuarterAmount(1, multiplyAmount(quarterSummary.getQuarterOneAmount(), totalRatio));
                    accumulator.addQuarterAmount(2, multiplyAmount(quarterSummary.getQuarterTwoAmount(), totalRatio));
                    accumulator.addQuarterAmount(3, multiplyAmount(quarterSummary.getQuarterThreeAmount(), totalRatio));
                    accumulator.addQuarterAmount(4, multiplyAmount(quarterSummary.getQuarterFourAmount(), totalRatio));
                }
            }
        }

        Map<Long, EmployeeYearCostBudgetDO> costBudgetMap = employeeYearCostBudgetMapper.selectList(
                        new LambdaQueryWrapperX<EmployeeYearCostBudgetDO>()
                                .eq(EmployeeYearCostBudgetDO::getBudgetYear, reqVO.getYear())
                                .eq(EmployeeYearCostBudgetDO::getEnabledFlag, Boolean.TRUE)
                                .inIfPresent(EmployeeYearCostBudgetDO::getEmployeeId, employeeIds))
                .stream()
                .collect(Collectors.toMap(EmployeeYearCostBudgetDO::getEmployeeId, item -> item, (a, b) -> a));
        YearKValueDO yearKValue = yearKValueMapper.selectEnabledByKYear(reqVO.getYear());
        BigDecimal kValue = yearKValue == null ? DEFAULT_K_VALUE : ratio(yearKValue.getKValue());

        List<EmployeeOutputSummaryExcelRespVO> rows = new ArrayList<>();
        for (EmployeeDO employee : employeeList) {
            EmployeeAccumulator accumulator = accumulatorMap.get(employee.getId());
            if (accumulator == null) {
                accumulator = new EmployeeAccumulator();
            }
            EmployeeYearCostBudgetDO costBudget = costBudgetMap.get(employee.getId());
            BigDecimal expectedCostAmount = costBudget == null ? ZERO_AMOUNT : amount(costBudget.getExpectedCostAmount());
            BigDecimal officeLeaderOrBimAmount = ZERO_AMOUNT;
            BigDecimal annualTotalAmount = accumulator.getAnnualTotalAmount();
            BigDecimal totalAssessmentOutputAmount = annualTotalAmount.add(officeLeaderOrBimAmount)
                    .setScale(2, RoundingMode.HALF_UP);
            BigDecimal basicAssessmentOutputAmount = kValue.compareTo(BigDecimal.ZERO) == 0
                    ? ZERO_AMOUNT
                    : expectedCostAmount.divide(kValue, 2, RoundingMode.HALF_UP);
            BigDecimal remainingOutputAmount = totalAssessmentOutputAmount.subtract(basicAssessmentOutputAmount)
                    .setScale(2, RoundingMode.HALF_UP);
            BigDecimal estimatedYearEndPerformanceAmount = remainingOutputAmount.multiply(kValue)
                    .setScale(2, RoundingMode.HALF_UP);

            EmployeeOutputSummaryExcelRespVO row = new EmployeeOutputSummaryExcelRespVO();
            row.setEmployeeName(employee.getEmployeeName());
            row.setOfficeName(getOfficeName(officeMap, employee.getOfficeId()));
            row.setQuarterOneAmount(accumulator.getQuarterOneAmount());
            row.setQuarterTwoAmount(accumulator.getQuarterTwoAmount());
            row.setQuarterThreeAmount(accumulator.getQuarterThreeAmount());
            row.setQuarterFourAmount(accumulator.getQuarterFourAmount());
            row.setAnnualTotalAmount(annualTotalAmount);
            row.setOfficeLeaderOrBimAmount(officeLeaderOrBimAmount);
            row.setTotalAssessmentOutputAmount(totalAssessmentOutputAmount);
            row.setExpectedCostAmount(expectedCostAmount);
            row.setBasicAssessmentOutputAmount(basicAssessmentOutputAmount);
            row.setRemainingOutputAmount(remainingOutputAmount);
            row.setEstimatedYearEndPerformanceAmount(estimatedYearEndPerformanceAmount);
            rows.add(row);
        }

        sortEmployeeRows(rows, reqVO.getSortType());
        for (int i = 0; i < rows.size(); i++) {
            rows.get(i).setSerialNo(i + 1);
        }
        ExcelUtils.write(response, buildFileName(String.valueOf(reqVO.getYear()), "员工个人考核产值汇总"),
                "员工汇总", EmployeeOutputSummaryExcelRespVO.class, rows);
    }

    private ProjectPlanningDO validatePlanningExists(Long planningId) {
        ProjectPlanningDO planning = projectPlanningMapper.selectById(planningId);
        if (planning == null) {
            throw exception(PROJECT_PLANNING_NOT_EXISTS);
        }
        return planning;
    }

    private ProjectPlanningDO validateMajorPlanning(Long planningId) {
        ProjectPlanningDO planning = validatePlanningExists(planningId);
        if (!ProjectPlanningBizTypeConstants.isMajor(planning.getOwnershipType())) {
            throw exception(PROJECT_OUTPUT_SPLIT_NOT_MAJOR);
        }
        return planning;
    }

    private ProjectDO validateProjectExists(Long projectId) {
        ProjectDO project = projectMapper.selectById(projectId);
        if (project == null) {
            throw exception(PROJECT_NOT_EXISTS);
        }
        return project;
    }

    private void validateSpecialtyCode(String specialtyCode) {
        if (!OutputSplitBizConstants.isValidSpecialtyCode(specialtyCode)
                || OutputSplitBizConstants.SPECIALTY_PROJECT_LEAD.equals(specialtyCode)) {
            throw exception(SPECIALTY_ROLE_SPLIT_SPECIALTY_INVALID);
        }
    }

    private List<ProjectPlanningDO> getProjectPlanningScope(ProjectPlanningDO anchorPlanning, boolean majorOnly) {
        List<ProjectPlanningDO> projectPlanningList = projectPlanningMapper.selectListByProjectId(anchorPlanning.getProjectId());
        Integer targetYear = resolveReportYear(anchorPlanning);
        return projectPlanningList.stream()
                .filter(item -> Objects.equals(resolveReportYear(item), targetYear))
                .filter(item -> !majorOnly || ProjectPlanningBizTypeConstants.isMajor(item.getOwnershipType()))
                .collect(Collectors.toList());
    }

    private List<ProjectBudgetExcelBuilder.BudgetRow> buildProjectBudgetRows(List<ProjectPlanningDO> planningList) {
        if (planningList == null || planningList.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, List<ProjectPlanningDO>> planningContentMap = new LinkedHashMap<>();
        planningList.stream()
                .sorted(Comparator.comparing(ProjectPlanningDO::getId))
                .forEach(planning -> planningContentMap
                        .computeIfAbsent(resolveBudgetPlanningGroupKey(planning), key -> new ArrayList<>())
                        .add(planning));

        List<ProjectBudgetExcelBuilder.BudgetRow> rows = new ArrayList<>();
        for (List<ProjectPlanningDO> planningGroup : planningContentMap.values()) {
            appendBudgetPlanningRows(rows, planningGroup);
        }
        return rows;
    }

    private void appendBudgetPlanningRows(List<ProjectBudgetExcelBuilder.BudgetRow> rows,
                                          List<ProjectPlanningDO> planningGroup) {
        if (planningGroup == null || planningGroup.isEmpty()) {
            return;
        }
        List<ProjectPlanningDO> groundPlanningList = filterBudgetPartPlanningList(
                planningGroup, ProjectPlanningBizTypeConstants.DESIGN_PART_REAL_ESTATE);
        List<ProjectPlanningDO> undergroundPlanningList = filterBudgetPartPlanningList(
                planningGroup, ProjectPlanningBizTypeConstants.DESIGN_PART_UNDERGROUND);

        appendBudgetPartRows(rows, ProjectPlanningBizTypeConstants.DESIGN_PART_REAL_ESTATE, groundPlanningList);
        appendBudgetPartRows(rows, ProjectPlanningBizTypeConstants.DESIGN_PART_UNDERGROUND, undergroundPlanningList);
        rows.add(buildBudgetSummaryRow(ProjectBudgetExcelBuilder.BudgetRowType.PLANNING_TOTAL, "总计", planningGroup));
    }

    private void appendBudgetPartRows(List<ProjectBudgetExcelBuilder.BudgetRow> rows, String designPart,
                                      List<ProjectPlanningDO> partPlanningList) {
        if (partPlanningList == null || partPlanningList.isEmpty()) {
            rows.add(buildBudgetEmptyRow(designPart));
            return;
        }
        for (ProjectPlanningDO planning : partPlanningList) {
            rows.add(buildBudgetDetailRow(designPart, planning));
        }
        if (partPlanningList.size() > 2) {
            rows.add(buildBudgetSummaryRow(ProjectBudgetExcelBuilder.BudgetRowType.PART_SUBTOTAL,
                    designPart + "总计", partPlanningList));
        }
    }

    private List<ProjectPlanningDO> filterBudgetPartPlanningList(List<ProjectPlanningDO> planningGroup, String designPart) {
        return planningGroup.stream()
                .filter(item -> Objects.equals(item.getDesignPart(), designPart))
                .sorted(Comparator.comparing(ProjectPlanningDO::getId))
                .collect(Collectors.toList());
    }

    private String resolveBudgetPlanningGroupKey(ProjectPlanningDO planning) {
        String planningContent = defaultString(planning == null ? null : planning.getPlanningContent());
        return planningContent.isEmpty() ? "__PLANNING__" + (planning == null ? "" : planning.getId()) : planningContent;
    }

    private ProjectBudgetExcelBuilder.BudgetRow buildBudgetDetailRow(String designPart, ProjectPlanningDO planning) {
        ProjectBudgetExcelBuilder.BudgetRow row = initBudgetRow(ProjectBudgetExcelBuilder.BudgetRowType.DETAIL, designPart);
        row.setPlanningContent(defaultString(planning.getPlanningContent()));
        row.setDisplayBuildingType(defaultString(planning.getBuildingType()));
        row.setInternalGuidanceUnitPrice(planning.getInternalGuidanceUnitPrice());
        row.setDesignArea(planning.getPlanningArea());
        row.setBuildingOrUnitCount(planning.getBuildingOrUnitCount());
        row.setDrawingSetFactor(planning.getDrawingSetFactor());
        row.setScaleFactor(planning.getScaleFactor());
        row.setModificationFactor(planning.getModificationFactor());
        row.setComplexityFactor(planning.getComplexityFactor());
        row.setSubtotalArea(planning.getAssessmentArea());
        row.setCurrentDesignStageRatio(planning.getCurrentDesignStageRatio());
        row.setAssessmentOutputValueWan(planning.getAssessmentOutputValue() == null
                ? null : amountToWan(planning.getAssessmentOutputValue()));
        return row;
    }

    private ProjectBudgetExcelBuilder.BudgetRow buildBudgetEmptyRow(String designPart) {
        return initBudgetRow(ProjectBudgetExcelBuilder.BudgetRowType.PART_EMPTY, designPart);
    }

    private ProjectBudgetExcelBuilder.BudgetRow buildBudgetSummaryRow(ProjectBudgetExcelBuilder.BudgetRowType rowType,
                                                                      String displayDesignPart,
                                                                      List<ProjectPlanningDO> planningList) {
        ProjectBudgetExcelBuilder.BudgetRow row = initBudgetRow(rowType, displayDesignPart);
        row.setDesignArea(sumPlanningArea(planningList));
        row.setSubtotalArea(sumAssessmentArea(planningList));
        row.setAssessmentOutputValueWan(amountToWan(sumAssessmentOutputValue(planningList)));
        return row;
    }

    private ProjectBudgetExcelBuilder.BudgetRow initBudgetRow(ProjectBudgetExcelBuilder.BudgetRowType rowType,
                                                              String displayDesignPart) {
        ProjectBudgetExcelBuilder.BudgetRow row = new ProjectBudgetExcelBuilder.BudgetRow();
        row.setRowType(rowType);
        row.setDisplayDesignPart(displayDesignPart);
        return row;
    }

    private BigDecimal sumPlanningArea(List<ProjectPlanningDO> planningList) {
        BigDecimal total = ZERO_AMOUNT;
        if (planningList == null || planningList.isEmpty()) {
            return total;
        }
        for (ProjectPlanningDO planning : planningList) {
            total = total.add(amount(planning.getPlanningArea())).setScale(2, RoundingMode.HALF_UP);
        }
        return total;
    }

    private BigDecimal sumAssessmentArea(List<ProjectPlanningDO> planningList) {
        BigDecimal total = ZERO_AMOUNT;
        if (planningList == null || planningList.isEmpty()) {
            return total;
        }
        for (ProjectPlanningDO planning : planningList) {
            total = total.add(amount(planning.getAssessmentArea())).setScale(2, RoundingMode.HALF_UP);
        }
        return total;
    }

    private BigDecimal sumAssessmentOutputValue(List<ProjectPlanningDO> planningList) {
        BigDecimal total = ZERO_AMOUNT;
        if (planningList == null || planningList.isEmpty()) {
            return total;
        }
        for (ProjectPlanningDO planning : planningList) {
            total = total.add(amount(planning.getAssessmentOutputValue())).setScale(2, RoundingMode.HALF_UP);
        }
        return total;
    }

    private List<ProjectBudgetExcelBuilder.QuarterBudgetRow> buildQuarterBudgetRows(
            List<ProjectPlanningDO> planningList, Map<Long, List<ProjectPlanningQuarterDO>> quarterMap,
            Integer reportYear) {
        List<ProjectBudgetExcelBuilder.QuarterBudgetRow> rows = new ArrayList<>();
        BigDecimal totalQuarterOneAmount = ZERO_AMOUNT;
        BigDecimal totalQuarterTwoAmount = ZERO_AMOUNT;
        BigDecimal totalQuarterThreeAmount = ZERO_AMOUNT;
        BigDecimal totalQuarterFourAmount = ZERO_AMOUNT;
        BigDecimal totalYearAmount = ZERO_AMOUNT;
        int serialNo = 1;

        for (ProjectPlanningDO planning : planningList) {
            QuarterSummary quarterSummary = buildQuarterSummary(planning, quarterMap.get(planning.getId()), reportYear);
            ProjectBudgetExcelBuilder.QuarterBudgetRow row = new ProjectBudgetExcelBuilder.QuarterBudgetRow();
            row.setSerialNo(serialNo++);
            row.setOutputType(resolveBudgetCategoryLabel(planning.getOwnershipType()));
            row.setDesignContent(defaultString(planning.getPlanningContent()));
            row.setBudgetYear(reportYear);
            row.setTotalDesignArea(amount(planning.getPlanningArea()));
            row.setTotalAssessmentArea(amount(planning.getAssessmentArea()));
            row.setTotalAssessmentOutputWan(resolveTotalAssessmentOutputWan(planning));
            row.setDesignStageRatio(ratio(planning.getCurrentDesignStageRatio()));
            row.setDesignStageOutputWan(amountToWan(planning.getAssessmentOutputValue()));
            row.setHistoricalIssuedRatio(quarterSummary.getHistoricalIssuedRatio());
            row.setQuarterOneRatio(quarterSummary.getQuarterOneRatio());
            row.setQuarterTwoRatio(quarterSummary.getQuarterTwoRatio());
            row.setQuarterThreeRatio(quarterSummary.getQuarterThreeRatio());
            row.setQuarterFourRatio(quarterSummary.getQuarterFourRatio());
            row.setCurrentYearRatio(quarterSummary.getCurrentYearRatio());
            row.setPendingRatio(quarterSummary.getPendingRatio());
            row.setQuarterOneAmountWan(amountToWan(quarterSummary.getQuarterOneAmount()));
            row.setQuarterTwoAmountWan(amountToWan(quarterSummary.getQuarterTwoAmount()));
            row.setQuarterThreeAmountWan(amountToWan(quarterSummary.getQuarterThreeAmount()));
            row.setQuarterFourAmountWan(amountToWan(quarterSummary.getQuarterFourAmount()));
            row.setYearTotalAmountWan(amountToWan(quarterSummary.getYearTotalAmount()));
            row.setRatioRemark(buildQuarterBudgetRemark(reportYear, quarterSummary));
            rows.add(row);

            totalQuarterOneAmount = totalQuarterOneAmount.add(quarterSummary.getQuarterOneAmount()).setScale(2, RoundingMode.HALF_UP);
            totalQuarterTwoAmount = totalQuarterTwoAmount.add(quarterSummary.getQuarterTwoAmount()).setScale(2, RoundingMode.HALF_UP);
            totalQuarterThreeAmount = totalQuarterThreeAmount.add(quarterSummary.getQuarterThreeAmount()).setScale(2, RoundingMode.HALF_UP);
            totalQuarterFourAmount = totalQuarterFourAmount.add(quarterSummary.getQuarterFourAmount()).setScale(2, RoundingMode.HALF_UP);
            totalYearAmount = totalYearAmount.add(quarterSummary.getYearTotalAmount()).setScale(2, RoundingMode.HALF_UP);
        }

        if (!planningList.isEmpty()) {
            ProjectBudgetExcelBuilder.QuarterBudgetRow totalRow = new ProjectBudgetExcelBuilder.QuarterBudgetRow();
            totalRow.setTotalRow(true);
            totalRow.setTotalDesignArea(sumPlanningArea(planningList));
            totalRow.setTotalAssessmentArea(sumAssessmentArea(planningList));
            totalRow.setTotalAssessmentOutputWan(sumQuarterBudgetTotalAssessmentOutputWan(planningList));
            totalRow.setDesignStageOutputWan(amountToWan(sumAssessmentOutputValue(planningList)));
            totalRow.setQuarterOneAmountWan(amountToWan(totalQuarterOneAmount));
            totalRow.setQuarterTwoAmountWan(amountToWan(totalQuarterTwoAmount));
            totalRow.setQuarterThreeAmountWan(amountToWan(totalQuarterThreeAmount));
            totalRow.setQuarterFourAmountWan(amountToWan(totalQuarterFourAmount));
            totalRow.setYearTotalAmountWan(amountToWan(totalYearAmount));
            rows.add(totalRow);
        }
        return rows;
    }

    private BigDecimal sumQuarterBudgetTotalAssessmentOutputWan(List<ProjectPlanningDO> planningList) {
        BigDecimal total = ZERO_AMOUNT;
        if (planningList == null || planningList.isEmpty()) {
            return total;
        }
        for (ProjectPlanningDO planning : planningList) {
            total = total.add(resolveTotalAssessmentOutputWan(planning)).setScale(2, RoundingMode.HALF_UP);
        }
        return total;
    }

    private BigDecimal resolveTotalAssessmentOutputWan(ProjectPlanningDO planning) {
        if (planning == null) {
            return ZERO_AMOUNT;
        }
        BigDecimal stageRatio = ratio(planning.getCurrentDesignStageRatio());
        if (stageRatio.compareTo(BigDecimal.ZERO) <= 0) {
            return amountToWan(planning.getAssessmentOutputValue());
        }
        return amount(planning.getAssessmentOutputValue())
                .divide(stageRatio, 2, RoundingMode.HALF_UP)
                .divide(new BigDecimal("10000"), 2, RoundingMode.HALF_UP);
    }

    private String buildQuarterBudgetRemark(Integer budgetYear, QuarterSummary summary) {
        List<String> parts = new ArrayList<>();
        if (budgetYear != null) {
            parts.add("预算年度：" + budgetYear);
        }
        if (summary == null) {
            return String.join("；", parts);
        }
        if (summary.getCurrentYearRatio().compareTo(BigDecimal.ZERO) == 0
                && summary.getPendingRatio().compareTo(BigDecimal.ZERO) > 0) {
            parts.add("未配置本年度发放比例");
            return String.join("；", parts);
        }
        if (summary.getPendingRatio().compareTo(BigDecimal.ZERO) > 0) {
            parts.add("本年度发放比例合计 " + percentText(summary.getCurrentYearRatio()));
            parts.add("未发放比例 " + percentText(summary.getPendingRatio()));
        }
        return String.join("；", parts);
    }

    private List<ProjectPlanningDO> sortPlanningList(List<ProjectPlanningDO> planningList) {
        return planningList.stream()
                .sorted(Comparator
                        .comparingInt((ProjectPlanningDO item) -> designPartOrder(item.getDesignPart()))
                        .thenComparing(ProjectPlanningDO::getId))
                .collect(Collectors.toList());
    }

    private int designPartOrder(String designPart) {
        if (Objects.equals(designPart, ProjectPlanningBizTypeConstants.DESIGN_PART_REAL_ESTATE)) {
            return 1;
        }
        if (Objects.equals(designPart, ProjectPlanningBizTypeConstants.DESIGN_PART_UNDERGROUND)) {
            return 2;
        }
        return 9;
    }

    private Integer resolveReportYear(ProjectPlanningDO planning) {
        return planning.getPlanningStartYear() == null ? LocalDate.now().getYear() : planning.getPlanningStartYear();
    }

    private List<Integer> resolveBudgetYears(ProjectPlanningDO planning, List<ProjectPlanningQuarterDO> quarterList) {
        Set<Integer> yearSet = new LinkedHashSet<>();
        if (planning != null && planning.getPlanningStartYear() != null) {
            yearSet.add(planning.getPlanningStartYear());
        }
        if (quarterList != null) {
            quarterList.stream()
                    .map(ProjectPlanningQuarterDO::getDistributionYear)
                    .filter(Objects::nonNull)
                    .sorted()
                    .forEach(yearSet::add);
        }
        if (yearSet.isEmpty()) {
            yearSet.add(LocalDate.now().getYear());
        }
        return new ArrayList<>(yearSet);
    }

    private Map<Long, List<ProjectPlanningQuarterDO>> getQuarterMap(Collection<ProjectPlanningDO> planningList) {
        if (planningList == null || planningList.isEmpty()) {
            return Collections.emptyMap();
        }
        List<Long> planningIds = planningList.stream().map(ProjectPlanningDO::getId).collect(Collectors.toList());
        return projectPlanningQuarterMapper.selectListByPlanningIds(planningIds).stream()
                .collect(Collectors.groupingBy(ProjectPlanningQuarterDO::getPlanningId, LinkedHashMap::new, Collectors.toList()));
    }

    private Map<Long, ProjectOutputSplitDO> getOutputSplitMap(Collection<ProjectPlanningDO> planningList) {
        if (planningList == null || planningList.isEmpty()) {
            return Collections.emptyMap();
        }
        Map<Long, ProjectOutputSplitDO> map = new LinkedHashMap<>();
        for (ProjectPlanningDO planning : planningList) {
            map.put(planning.getId(), projectOutputSplitService.getOrCreateProjectOutputSplit(planning.getId()));
        }
        return map;
    }

    private Map<Long, List<SpecialtyRoleSplitRespVO>> getRoleSplitMap(Collection<ProjectPlanningDO> planningList) {
        if (planningList == null || planningList.isEmpty()) {
            return Collections.emptyMap();
        }
        Map<Long, List<SpecialtyRoleSplitRespVO>> map = new LinkedHashMap<>();
        for (ProjectPlanningDO planning : planningList) {
            map.put(planning.getId(), specialtyRoleSplitService.getSpecialtyRoleSplitListByPlanningId(planning.getId()));
        }
        return map;
    }

    private Map<Long, ProjectDO> getProjectMap(Collection<ProjectPlanningDO> planningList) {
        if (planningList == null || planningList.isEmpty()) {
            return Collections.emptyMap();
        }
        Set<Long> projectIds = planningList.stream().map(ProjectPlanningDO::getProjectId)
                .collect(Collectors.toCollection(LinkedHashSet::new));
        return projectMapper.selectBatchIds(projectIds).stream()
                .collect(Collectors.toMap(ProjectDO::getId, item -> item, (a, b) -> a));
    }

    private Map<Long, OfficeDO> getOfficeMap(Collection<EmployeeDO> employeeList) {
        if (employeeList == null || employeeList.isEmpty()) {
            return Collections.emptyMap();
        }
        Set<Long> officeIds = employeeList.stream()
                .map(EmployeeDO::getOfficeId)
                .filter(Objects::nonNull)
                .collect(Collectors.toCollection(LinkedHashSet::new));
        if (officeIds.isEmpty()) {
            return Collections.emptyMap();
        }
        return officeMapper.selectBatchIds(officeIds).stream()
                .collect(Collectors.toMap(OfficeDO::getId, item -> item, (a, b) -> a));
    }

    private QuarterSummary buildQuarterSummary(ProjectPlanningDO planning, List<ProjectPlanningQuarterDO> quarterList, Integer reportYear) {
        QuarterSummary summary = new QuarterSummary();
        summary.setHistoricalIssuedRatio(ZERO_RATIO);
        summary.setQuarterOneRatio(ZERO_RATIO);
        summary.setQuarterTwoRatio(ZERO_RATIO);
        summary.setQuarterThreeRatio(ZERO_RATIO);
        summary.setQuarterFourRatio(ZERO_RATIO);
        summary.setCurrentYearRatio(ZERO_RATIO);
        summary.setPendingRatio(defaultDistributionRatio(planning));
        summary.setQuarterOneAmount(ZERO_AMOUNT);
        summary.setQuarterTwoAmount(ZERO_AMOUNT);
        summary.setQuarterThreeAmount(ZERO_AMOUNT);
        summary.setQuarterFourAmount(ZERO_AMOUNT);
        summary.setYearTotalAmount(ZERO_AMOUNT);
        if (quarterList == null || quarterList.isEmpty()) {
            return summary;
        }

        BigDecimal allocatedRatio = ZERO_RATIO;
        for (ProjectPlanningQuarterDO quarter : quarterList) {
            BigDecimal distributionRatio = ratio(quarter.getDistributionRatio());
            allocatedRatio = allocatedRatio.add(distributionRatio).setScale(4, RoundingMode.HALF_UP);
            if (quarter.getDistributionYear() != null && quarter.getDistributionYear() < reportYear) {
                summary.setHistoricalIssuedRatio(summary.getHistoricalIssuedRatio().add(distributionRatio)
                        .setScale(4, RoundingMode.HALF_UP));
                continue;
            }
            if (!Objects.equals(quarter.getDistributionYear(), reportYear)) {
                continue;
            }
            BigDecimal distributionAmount = amount(quarter.getDistributionAmount());
            summary.setCurrentYearRatio(summary.getCurrentYearRatio().add(distributionRatio)
                    .setScale(4, RoundingMode.HALF_UP));
            if (Objects.equals(quarter.getQuarterNo(), 1)) {
                summary.setQuarterOneRatio(summary.getQuarterOneRatio().add(distributionRatio)
                        .setScale(4, RoundingMode.HALF_UP));
                summary.setQuarterOneAmount(summary.getQuarterOneAmount().add(distributionAmount).setScale(2, RoundingMode.HALF_UP));
            } else if (Objects.equals(quarter.getQuarterNo(), 2)) {
                summary.setQuarterTwoRatio(summary.getQuarterTwoRatio().add(distributionRatio)
                        .setScale(4, RoundingMode.HALF_UP));
                summary.setQuarterTwoAmount(summary.getQuarterTwoAmount().add(distributionAmount).setScale(2, RoundingMode.HALF_UP));
            } else if (Objects.equals(quarter.getQuarterNo(), 3)) {
                summary.setQuarterThreeRatio(summary.getQuarterThreeRatio().add(distributionRatio)
                        .setScale(4, RoundingMode.HALF_UP));
                summary.setQuarterThreeAmount(summary.getQuarterThreeAmount().add(distributionAmount).setScale(2, RoundingMode.HALF_UP));
            } else if (Objects.equals(quarter.getQuarterNo(), 4)) {
                summary.setQuarterFourRatio(summary.getQuarterFourRatio().add(distributionRatio)
                        .setScale(4, RoundingMode.HALF_UP));
                summary.setQuarterFourAmount(summary.getQuarterFourAmount().add(distributionAmount).setScale(2, RoundingMode.HALF_UP));
            }
            summary.setYearTotalAmount(summary.getYearTotalAmount().add(distributionAmount).setScale(2, RoundingMode.HALF_UP));
        }

        BigDecimal pendingRatio = defaultDistributionRatio(planning).subtract(allocatedRatio)
                .setScale(4, RoundingMode.HALF_UP);
        summary.setPendingRatio(pendingRatio.compareTo(BigDecimal.ZERO) < 0 ? ZERO_RATIO : pendingRatio);
        return summary;
    }

    private BigDecimal getCategoryRatio(ProjectOutputSplitDO outputSplit, String specialtyCode) {
        if (OutputSplitBizConstants.SPECIALTY_PROJECT_LEAD.equals(specialtyCode)) {
            return ratio(outputSplit.getProjectLeadRatio());
        }
        return multiplyRatio(outputSplit.getOfficeRatio(), getSpecialtyRatio(outputSplit, specialtyCode));
    }

    private BigDecimal getSpecialtyRatio(ProjectOutputSplitDO outputSplit, String specialtyCode) {
        if (outputSplit == null || specialtyCode == null) {
            return ZERO_RATIO;
        }
        switch (specialtyCode) {
            case OutputSplitBizConstants.SPECIALTY_ARCH:
                return ratio(outputSplit.getArchRatio());
            case OutputSplitBizConstants.SPECIALTY_DECOR:
                return ratio(outputSplit.getDecorRatio());
            case OutputSplitBizConstants.SPECIALTY_STRUCT:
                return ratio(outputSplit.getStructRatio());
            case OutputSplitBizConstants.SPECIALTY_WATER:
                return ratio(outputSplit.getWaterRatio());
            case OutputSplitBizConstants.SPECIALTY_ELEC:
                return ratio(outputSplit.getElecRatio());
            case OutputSplitBizConstants.SPECIALTY_HVAC:
                return ratio(outputSplit.getHvacRatio());
            case OutputSplitBizConstants.SPECIALTY_DIGITAL:
                return ratio(outputSplit.getDigitalRatio());
            default:
                return ZERO_RATIO;
        }
    }

    private String buildSpecialtySummary(ProjectOutputSplitDO outputSplit, BigDecimal baseAmount) {
        List<String> summaryList = new ArrayList<>();
        summaryList.add("建筑 " + multiplyAmount(baseAmount,
                multiplyRatio(outputSplit.getOfficeRatio(), outputSplit.getArchRatio())).toPlainString());
        summaryList.add("装饰 " + multiplyAmount(baseAmount,
                multiplyRatio(outputSplit.getOfficeRatio(), outputSplit.getDecorRatio())).toPlainString());
        summaryList.add("结构 " + multiplyAmount(baseAmount,
                multiplyRatio(outputSplit.getOfficeRatio(), outputSplit.getStructRatio())).toPlainString());
        summaryList.add("给排水 " + multiplyAmount(baseAmount,
                multiplyRatio(outputSplit.getOfficeRatio(), outputSplit.getWaterRatio())).toPlainString());
        summaryList.add("电气 " + multiplyAmount(baseAmount,
                multiplyRatio(outputSplit.getOfficeRatio(), outputSplit.getElecRatio())).toPlainString());
        summaryList.add("暖通 " + multiplyAmount(baseAmount,
                multiplyRatio(outputSplit.getOfficeRatio(), outputSplit.getHvacRatio())).toPlainString());
        summaryList.add("数字化设计 " + multiplyAmount(baseAmount,
                multiplyRatio(outputSplit.getOfficeRatio(), outputSplit.getDigitalRatio())).toPlainString());
        return String.join("；", summaryList);
    }

    private String resolveBudgetCategoryLabel(String ownershipType) {
        if (ProjectPlanningBizTypeConstants.isMajor(ownershipType)) {
            return "六大专业考核产值";
        }
        if (ProjectPlanningBizTypeConstants.isSubcontract(ownershipType)) {
            return "专业分包产值";
        }
        if (ProjectPlanningBizTypeConstants.isComprehensive(ownershipType)) {
            return "内部协作产值";
        }
        return "其他产值";
    }

    private String buildPlanningDisplayName(ProjectPlanningDO planning) {
        if (planning == null) {
            return "";
        }
        String planningContent = defaultString(planning.getPlanningContent());
        String ownershipType = defaultString(planning.getOwnershipType());
        if (planningContent.isEmpty()) {
            return ownershipType;
        }
        if (ownershipType.isEmpty()) {
            return planningContent;
        }
        return planningContent + "（" + ownershipType + "）";
    }

    private String joinPlanningRemarks(List<ProjectPlanningDO> planningList) {
        if (planningList == null || planningList.isEmpty()) {
            return "";
        }
        return planningList.stream()
                .map(ProjectPlanningDO::getProgressRemark)
                .map(this::defaultString)
                .filter(item -> !item.isEmpty())
                .distinct()
                .collect(Collectors.joining("；"));
    }

    private String buildProjectBudgetYearRangeText(List<ProjectPlanningDO> planningList,
                                                   Map<Long, List<ProjectPlanningQuarterDO>> quarterMap) {
        if (planningList == null || planningList.isEmpty()) {
            return String.valueOf(LocalDate.now().getYear());
        }
        Set<Integer> yearSet = new LinkedHashSet<>();
        for (ProjectPlanningDO planning : planningList) {
            yearSet.addAll(resolveBudgetYears(planning, quarterMap.get(planning.getId())));
        }
        List<Integer> years = yearSet.stream().sorted().collect(Collectors.toList());
        if (years.isEmpty()) {
            return String.valueOf(LocalDate.now().getYear());
        }
        if (years.size() == 1) {
            return String.valueOf(years.get(0));
        }
        return years.get(0) + "-" + years.get(years.size() - 1);
    }

    private String joinProjectRoleNames(List<ProjectRolePersonDO> rolePersons, String roleCode) {
        if (rolePersons == null || rolePersons.isEmpty()) {
            return "";
        }
        return rolePersons.stream()
                .filter(item -> Objects.equals(item.getRoleCode(), roleCode))
                .map(ProjectRolePersonDO::getEmployeeName)
                .filter(Objects::nonNull)
                .collect(Collectors.joining("、"));
    }

    private SpecialtyRoleSplitRespVO findRoleRow(List<SpecialtyRoleSplitRespVO> roleList,
                                                 String specialtyCode, String roleCode) {
        if (roleList == null || roleList.isEmpty()) {
            return null;
        }
        return roleList.stream()
                .filter(item -> Objects.equals(item.getSpecialtyCode(), specialtyCode)
                        && Objects.equals(item.getRoleCode(), roleCode))
                .findFirst()
                .orElse(null);
    }

    private String joinRoleSplitPersonNames(SpecialtyRoleSplitRespVO roleRow) {
        if (roleRow == null || roleRow.getPersons() == null || roleRow.getPersons().isEmpty()) {
            return "";
        }
        return roleRow.getPersons().stream()
                .map(SpecialtyRolePersonRespVO::getEmployeeName)
                .filter(Objects::nonNull)
                .collect(Collectors.joining("、"));
    }

    private String buildProgressText(ProjectDO project, ProjectPlanningDO planning) {
        String projectStatus = project == null ? "" : defaultString(project.getProjectStatus());
        String planningRemark = planning == null ? "" : defaultString(planning.getProgressRemark());
        if (projectStatus.isEmpty()) {
            return planningRemark;
        }
        if (planningRemark.isEmpty()) {
            return projectStatus;
        }
        return projectStatus + "；" + planningRemark;
    }

    private String getOfficeName(Map<Long, OfficeDO> officeMap, Long officeId) {
        if (officeId == null || officeMap == null || !officeMap.containsKey(officeId)) {
            return "";
        }
        return defaultString(officeMap.get(officeId).getOfficeName());
    }

    private void sortProjectOverviewRows(List<ProjectOverviewExcelRespVO> rows, String sortType) {
        Comparator<ProjectOverviewExcelRespVO> comparator;
        if (Objects.equals(sortType, SORT_OUTPUT_ASC)) {
            comparator = Comparator.comparing(ProjectOverviewExcelRespVO::getTotalOutputAmount, this::compareAmount)
                    .thenComparing(ProjectOverviewExcelRespVO::getProjectName, String.CASE_INSENSITIVE_ORDER);
        } else if (Objects.equals(sortType, SORT_NAME_ASC)) {
            comparator = Comparator.comparing(ProjectOverviewExcelRespVO::getProjectName, String.CASE_INSENSITIVE_ORDER)
                    .thenComparing(ProjectOverviewExcelRespVO::getPlanningContent, String.CASE_INSENSITIVE_ORDER);
        } else {
            comparator = Comparator.comparing(ProjectOverviewExcelRespVO::getTotalOutputAmount, this::compareAmount)
                    .reversed()
                    .thenComparing(ProjectOverviewExcelRespVO::getProjectName, String.CASE_INSENSITIVE_ORDER);
        }
        rows.sort(comparator);
    }

    private void sortEmployeeRows(List<EmployeeOutputSummaryExcelRespVO> rows, String sortType) {
        Comparator<EmployeeOutputSummaryExcelRespVO> comparator;
        if (Objects.equals(sortType, SORT_NAME_ASC)) {
            comparator = Comparator.comparing(EmployeeOutputSummaryExcelRespVO::getEmployeeName,
                    String.CASE_INSENSITIVE_ORDER);
        } else if (Objects.equals(sortType, SORT_ANNUAL_TOTAL_ASC)) {
            comparator = Comparator.comparing(EmployeeOutputSummaryExcelRespVO::getAnnualTotalAmount, this::compareAmount)
                    .thenComparing(EmployeeOutputSummaryExcelRespVO::getEmployeeName, String.CASE_INSENSITIVE_ORDER);
        } else {
            comparator = Comparator.comparing(EmployeeOutputSummaryExcelRespVO::getAnnualTotalAmount, this::compareAmount)
                    .reversed()
                    .thenComparing(EmployeeOutputSummaryExcelRespVO::getEmployeeName, String.CASE_INSENSITIVE_ORDER);
        }
        rows.sort(comparator);
    }

    private int compareAmount(BigDecimal left, BigDecimal right) {
        return amount(left).compareTo(amount(right));
    }

    private boolean hasYearAmount(List<ProjectPlanningQuarterDO> quarterList, Integer year) {
        if (quarterList == null || quarterList.isEmpty()) {
            return false;
        }
        return quarterList.stream()
                .anyMatch(item -> Objects.equals(item.getDistributionYear(), year)
                        && amount(item.getDistributionAmount()).compareTo(ZERO_AMOUNT) > 0);
    }

    private BigDecimal firstNonZero(List<BigDecimal> values) {
        if (values == null || values.isEmpty()) {
            return ZERO_AMOUNT;
        }
        return values.stream()
                .filter(Objects::nonNull)
                .map(this::amount)
                .filter(value -> value.compareTo(BigDecimal.ZERO) > 0)
                .findFirst()
                .orElse(ZERO_AMOUNT);
    }

    private BigDecimal defaultDistributionRatio(ProjectPlanningDO planning) {
        if (planning == null || planning.getTotalDistributionAmount() == null) {
            return new BigDecimal("1.0000");
        }
        return ratio(planning.getTotalDistributionAmount());
    }

    private BigDecimal amount(BigDecimal value) {
        return value == null ? ZERO_AMOUNT : value.setScale(2, RoundingMode.HALF_UP);
    }

    private BigDecimal amountToWan(BigDecimal value) {
        return amount(value).divide(new BigDecimal("10000"), 2, RoundingMode.HALF_UP);
    }

    private BigDecimal ratio(BigDecimal value) {
        return value == null ? ZERO_RATIO : value.setScale(4, RoundingMode.HALF_UP);
    }

    private BigDecimal multiplyAmount(BigDecimal amount, BigDecimal... ratios) {
        BigDecimal result = amount(amount);
        for (BigDecimal ratio : ratios) {
            result = result.multiply(ratio(ratio));
        }
        return result.setScale(2, RoundingMode.HALF_UP);
    }

    private BigDecimal multiplyRatio(BigDecimal... ratios) {
        BigDecimal result = BigDecimal.ONE;
        for (BigDecimal ratio : ratios) {
            result = result.multiply(ratio(ratio));
        }
        return result.setScale(4, RoundingMode.HALF_UP);
    }

    private BigDecimal addRatio(BigDecimal left, BigDecimal right) {
        return ratio(left).add(ratio(right)).setScale(4, RoundingMode.HALF_UP);
    }

    private String percentText(BigDecimal value) {
        return ratio(value).multiply(new BigDecimal("100")).setScale(2, RoundingMode.HALF_UP) + "%";
    }

    private String buildFileName(String prefix, String suffix) {
        return sanitizeFileName(prefix) + "_" + sanitizeFileName(suffix) + ".xlsx";
    }

    private String sanitizeFileName(String value) {
        if (value == null || value.trim().isEmpty()) {
            return "报表";
        }
        return value.replaceAll("[\\\\/:*?\"<>|]", "_");
    }

    private String defaultString(String value) {
        return value == null ? "" : value.trim();
    }

    @Data
    private static class QuarterSummary {
        private BigDecimal historicalIssuedRatio;
        private BigDecimal quarterOneRatio;
        private BigDecimal quarterTwoRatio;
        private BigDecimal quarterThreeRatio;
        private BigDecimal quarterFourRatio;
        private BigDecimal currentYearRatio;
        private BigDecimal pendingRatio;
        private BigDecimal quarterOneAmount;
        private BigDecimal quarterTwoAmount;
        private BigDecimal quarterThreeAmount;
        private BigDecimal quarterFourAmount;
        private BigDecimal yearTotalAmount;
    }

    @Data
    private static class EmployeeAccumulator {
        private BigDecimal quarterOneAmount = ZERO_AMOUNT;
        private BigDecimal quarterTwoAmount = ZERO_AMOUNT;
        private BigDecimal quarterThreeAmount = ZERO_AMOUNT;
        private BigDecimal quarterFourAmount = ZERO_AMOUNT;

        public void addQuarterAmount(int quarterNo, BigDecimal amount) {
            if (quarterNo == 1) {
                quarterOneAmount = quarterOneAmount.add(amount == null ? ZERO_AMOUNT : amount).setScale(2, RoundingMode.HALF_UP);
                return;
            }
            if (quarterNo == 2) {
                quarterTwoAmount = quarterTwoAmount.add(amount == null ? ZERO_AMOUNT : amount).setScale(2, RoundingMode.HALF_UP);
                return;
            }
            if (quarterNo == 3) {
                quarterThreeAmount = quarterThreeAmount.add(amount == null ? ZERO_AMOUNT : amount).setScale(2, RoundingMode.HALF_UP);
                return;
            }
            if (quarterNo == 4) {
                quarterFourAmount = quarterFourAmount.add(amount == null ? ZERO_AMOUNT : amount).setScale(2, RoundingMode.HALF_UP);
            }
        }

        public BigDecimal getAnnualTotalAmount() {
            return quarterOneAmount.add(quarterTwoAmount).add(quarterThreeAmount).add(quarterFourAmount)
                    .setScale(2, RoundingMode.HALF_UP);
        }
    }

}
