MLAY/websrc/com/kingdee/eas/custom/perfweb/handler/KeyTalentTurnoverReportHandler.java

package com.kingdee.eas.custom.perfweb.handler;

import com.alibaba.fastjson.JSONObject;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.eas.custom.perfweb.util.ReportDateUtil;
import com.kingdee.eas.custom.perfweb.util.ReportFilterUtil;
import com.kingdee.eas.custom.perfweb.util.ReportOrgUtil;
import com.kingdee.eas.custom.perfweb.util.ReportTurnoverUtil;
import com.kingdee.shr.base.syssetting.context.SHRContext;
import com.kingdee.shr.base.syssetting.exception.ShrWebBizException;
import com.kingdee.shr.base.syssetting.json.GridDataEntity;
import com.kingdee.shr.base.syssetting.web.handler.ListHandler;
import org.apache.log4j.Logger;
import org.springframework.ui.ModelMap;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.SQLException;
import java.util.*;

/**
 * 类名称: KeyTalentTurnoverReportHandler
 * 功能描述: 关键人才流失率报表(期初/主动离职/被动离职/离职合计/新增/期末/累计主动流失率)
 * 创建日期: 2026-06-23
 * 作    者: 青梧
 * 版    本: 1.1
 *
 * 期初/期末口径(B方案)：
 *   - 1月期初：1月1日关键人才快照
 *   - 期末：期初 - 离职 + 新增
 *   - 2~12月期初：上月推算期末(链式)
 *
 * 返回字段说明：
 *   month           - 月份(合计行显示合计)
 *   beginKeyTalent  - 期初关键人才人数
 *   activeResign    - 期间关键人才主动离职
 *   passiveResign   - 期间关键人才被动离职
 *   totalResign     - 期间离职合计
 *   newKeyTalent    - 期间新增关键人才
 *   endKeyTalent    - 期末关键人才(链式推算)
 *   ytdActiveResign - 累计主动离职
 *   ytdNewKeyTalent - 累计新增关键人才(截止目前)
 *   ytdActiveRate   - 累计主动流失率(保留两位小数%)
 */
public class KeyTalentTurnoverReportHandler extends ListHandler {

    private static Logger logger = Logger.getLogger(KeyTalentTurnoverReportHandler.class);
    Context ctx = SHRContext.getInstance().getContext();

    /** 关键人才标识：CFGjrc=1 */
    private static final String KEY_TALENT_FLAG = "1";
    /** 排除的员工类型FID(非在职) */
    private static final String EXCLUDED_EMPLOYEE_TYPE_ID = "00000000-0000-0000-0000-000000000007A29E85B3";

    @Override
    protected GridDataEntity getGridRequestData(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws ShrWebBizException {
        logger.error("KeyTalentTurnoverReportHandler getGridRequestData------------");

        GridDataEntity gridDataEntity = new GridDataEntity();

        try {
            String filterItem = ReportFilterUtil.getFilterItems(request);
            JSONObject fastFilterItemsJson = ReportFilterUtil.parseFastFilterJson(request);
            logger.error("filterItem: " + filterItem);
            logger.error("fastFilterItemsJson: " + fastFilterItemsJson);

            ReportOrgUtil.OrgScope scope = ReportFilterUtil.buildCenterOrgScope(ctx, fastFilterItemsJson, logger);

            String year = ReportFilterUtil.resolveYear(filterItem, fastFilterItemsJson, logger);
            logger.error("查询年度: " + year);

            List<Map<String, Object>> monthDataList = getMonthTurnoverData(year, scope);
            monthDataList.add(calculateSummary(monthDataList, year));

            gridDataEntity.setRows(monthDataList);
            gridDataEntity.setTotal(monthDataList.size());
            gridDataEntity.setPage(1);
            gridDataEntity.setRecords(monthDataList.size());

            logger.error("关键人才流失率报表数据查询成功，总记录数: " + monthDataList.size());

        } catch (Exception e) {
            e.fillInStackTrace();
            throw new ShrWebBizException(e.getMessage());
        }
        ReportFilterUtil.applyGridUserData(request, gridDataEntity);
        return gridDataEntity;
    }

    /**
     * 循环1~12月，逐月统计关键人才期初/离职/新增/期末及累计指标(B方案链式推算)。
     */
    private List<Map<String, Object>> getMonthTurnoverData(String year, ReportOrgUtil.OrgScope scope) throws BOSException, SQLException {
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

        int yearBeginKeyTalent = getBeginKeyTalentCount(year + "-01-01", scope);
        int ytdActiveResign = 0;
        int ytdNewKeyTalent = 0;
        int previousEndKeyTalent = 0;

        for (int month = 1; month <= 12; month++) {
            Map<String, Object> monthData = new LinkedHashMap<String, Object>();
            monthData.put("month", ReportDateUtil.formatMonthLabel(year, month));

            String monthStart = ReportDateUtil.getMonthStart(year, month);
            String monthEnd = ReportDateUtil.getMonthEnd(year, month);

            int beginCount;
            if (month == 1) {
                beginCount = getBeginKeyTalentCount(monthStart, scope);
            } else {
                beginCount = previousEndKeyTalent;
            }
            monthData.put("beginKeyTalent", beginCount);

            int activeResignCount = getResignCount(monthStart, monthEnd, false, scope);
            monthData.put("activeResign", activeResignCount);

            int passiveResignCount = getResignCount(monthStart, monthEnd, true, scope);
            monthData.put("passiveResign", passiveResignCount);

            int totalResignCount = activeResignCount + passiveResignCount;
            monthData.put("totalResign", totalResignCount);

            int newKeyTalentCount = getNewKeyTalentCount(monthStart, monthEnd, scope);
            monthData.put("newKeyTalent", newKeyTalentCount);

            int endKeyTalentCount = ReportTurnoverUtil.calculateDerivedEndOnDuty(beginCount, newKeyTalentCount, totalResignCount);
            monthData.put("endKeyTalent", endKeyTalentCount);
            previousEndKeyTalent = endKeyTalentCount;

            ytdActiveResign += activeResignCount;
            ytdNewKeyTalent += newKeyTalentCount;
            monthData.put("ytdActiveResign", ytdActiveResign);
            monthData.put("ytdNewKeyTalent", ytdNewKeyTalent);
            monthData.put("ytdActiveRate", ReportTurnoverUtil.formatRatioPercent(
                    ReportTurnoverUtil.calculateRatio(ytdActiveResign, yearBeginKeyTalent + ytdNewKeyTalent)));

            dataList.add(monthData);
        }

        return dataList;
    }

    /**
     * 统计期初关键人才人数(CFGjrc=1，仅用于1月期初快照)。
     */
    private int getBeginKeyTalentCount(String monthStart, ReportOrgUtil.OrgScope scope) throws BOSException, SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(DISTINCT person.FID) as count ");
        sql.append("FROM T_BD_PERSON person ");
        sql.append("LEFT JOIN T_HR_PersonPosition personPos ON person.FID = personPos.FPERSONID ");
        sql.append("WHERE person.CFGjrc = '").append(KEY_TALENT_FLAG).append("' ");
        sql.append("AND person.FEmployeeTypeID <> '").append(EXCLUDED_EMPLOYEE_TYPE_ID).append("' ");
        sql.append("AND (personPos.FLeftDate IS NULL OR personPos.FLeftDate >= '").append(monthStart).append("') ");
        sql.append("AND person.FHireDate IS NOT NULL ");
        sql.append("AND person.FHireDate <= '").append(monthStart).append("' ");

        ReportOrgUtil.appendPersonDepScopeFilter(sql, ctx, scope);
        return ReportOrgUtil.executeCountQuery(ctx, sql, "期初关键人才人数", logger);
    }

    private int getResignCount(String monthStart, String monthEnd, boolean isPassive, ReportOrgUtil.OrgScope scope) throws BOSException, SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) as count ");
        sql.append("FROM T_HR_ResignBizBillEntry entry ");
        sql.append("INNER JOIN T_HR_ResignBizBill bill ON entry.FBillID = bill.FID ");
        sql.append("INNER JOIN T_BD_PERSON person ON entry.FPersonID = person.FID ");
        sql.append("WHERE bill.FBillState = 3 ");
        sql.append("AND person.CFGjrc = '").append(KEY_TALENT_FLAG).append("' ");
        sql.append("AND entry.FBizDate >= '").append(monthStart).append("' ");
        sql.append("AND entry.FBizDate <= '").append(monthEnd).append("' ");

        ReportTurnoverUtil.appendPassiveResignFilter(sql, isPassive);
        ReportOrgUtil.appendAdminOrgScopeFilter(sql, ctx, scope, "entry.FAdminOrgID");

        return ReportOrgUtil.executeCountQuery(ctx, sql,
                "关键人才" + (isPassive ? "被动" : "主动") + "离职人数", logger);
    }

    /**
     * 统计新增关键人才人数(入职且CFGjrc=1)。
     */
    private int getNewKeyTalentCount(String monthStart, String monthEnd, ReportOrgUtil.OrgScope scope) throws BOSException, SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) as count ");
        sql.append("FROM T_HR_EmpEnrollBizBillEntry entry ");
        sql.append("INNER JOIN T_BD_PERSON person ON entry.FPersonID = person.FID ");
        sql.append("WHERE person.CFGjrc = '").append(KEY_TALENT_FLAG).append("' ");
        sql.append("AND entry.FBizDate >= '").append(monthStart).append("' ");
        sql.append("AND entry.FBizDate <= '").append(monthEnd).append("' ");

        ReportOrgUtil.appendAdminOrgScopeFilter(sql, ctx, scope, "entry.FAdminOrgID");
        return ReportOrgUtil.executeCountQuery(ctx, sql, "新增关键人才人数", logger);
    }

    /**
     * 合计行：1~当前月累计，期初取1月、期末取当前月链式推算结果。
     */
    private Map<String, Object> calculateSummary(List<Map<String, Object>> monthDataList, String year) {
        Map<String, Object> summary = new LinkedHashMap<String, Object>();
        summary.put("month", "合计");

        int currentMonth = resolveSummaryMonth(year);
        int yearBeginKeyTalent = 0;
        int currentMonthEndKeyTalent = 0;
        int totalActiveResign = 0;
        int totalPassiveResign = 0;
        int totalResign = 0;
        int totalNewKeyTalent = 0;

        if (!monthDataList.isEmpty()) {
            yearBeginKeyTalent = (Integer) monthDataList.get(0).get("beginKeyTalent");
        }

        for (int i = 0; i < monthDataList.size() && i < currentMonth; i++) {
            Map<String, Object> monthData = monthDataList.get(i);
            totalActiveResign += (Integer) monthData.get("activeResign");
            totalPassiveResign += (Integer) monthData.get("passiveResign");
            totalResign += (Integer) monthData.get("totalResign");
            totalNewKeyTalent += (Integer) monthData.get("newKeyTalent");
            currentMonthEndKeyTalent = (Integer) monthData.get("endKeyTalent");
        }

        summary.put("beginKeyTalent", yearBeginKeyTalent);
        summary.put("activeResign", totalActiveResign);
        summary.put("passiveResign", totalPassiveResign);
        summary.put("totalResign", totalResign);
        summary.put("newKeyTalent", totalNewKeyTalent);
        summary.put("endKeyTalent", currentMonthEndKeyTalent);
        summary.put("ytdActiveResign", totalActiveResign);
        summary.put("ytdNewKeyTalent", totalNewKeyTalent);
        summary.put("ytdActiveRate", ReportTurnoverUtil.formatRatioPercent(
                ReportTurnoverUtil.calculateRatio(totalActiveResign, yearBeginKeyTalent + totalNewKeyTalent)));

        return summary;
    }

    /**
     * 合计统计截止月：查询年为今年取系统当前月，往年取12月，未来年取1月。
     */
    private int resolveSummaryMonth(String year) {
        int queryYear = Integer.parseInt(year);
        Calendar calendar = Calendar.getInstance();
        int currentYear = calendar.get(Calendar.YEAR);
        if (queryYear < currentYear) {
            return 12;
        }
        if (queryYear > currentYear) {
            return 1;
        }
        return calendar.get(Calendar.MONTH) + 1;
    }
}