MLAY/websrc/com/kingdee/eas/custom/perfweb/handler/PersonnelTurnoverReportHandler.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.*;

/**
 * 类名称: PersonnelTurnoverReportHandler
 * 功能描述: 人员流失率报表(期初/离职/入职/期末/流失率/入职率)
 * 创建日期: 2026-06-08
 * 作    者: 青梧
 * 版    本: 1.2
 *
 * 期初/期末口径(B方案)：
 *   - 1月期初：1月1日在岗快照
 *   - 期末：期初 + 入职 - 离职
 *   - 2~12月期初：上月推算期末(链式)
 *
 * 合计行比率口径：
 *   - 分子：1~当前月累计人数 / 当前月数
 *   - 分母：(1月期初 + 当前月期末) / 2
 *
 * 返回字段说明：
 *   month          - 月份(1月~12月/合计)
 *   beginOnDuty    - 期初在岗人数
 *   activeResign   - 主动离职人数
 *   passiveResign  - 被动离职人数
 *   totalResign    - 离职合计
 *   newEntry       - 新入职人数
 *   endOnDuty      - 期末在岗人数(链式推算)
 *   activeRate     - 主动流失率
 *   passiveRate    - 被动流失率
 *   totalRate      - 总流失率
 *   entryRate      - 入职率
 */
public class PersonnelTurnoverReportHandler extends ListHandler {

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

    /**
     * 列表查询入口：按月份汇总流失指标，末尾追加合计行。
     */
    @Override
    protected GridDataEntity getGridRequestData(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws ShrWebBizException {
        logger.error("PersonnelTurnoverReportHandler 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 previousEndOnDuty = 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 = getOnDutyCount(monthStart, scope);
            } else {
                beginCount = previousEndOnDuty;
            }
            monthData.put("beginOnDuty", 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 newEntryCount = getNewEntryCount(monthStart, monthEnd, scope);
            monthData.put("newEntry", newEntryCount);

            int endOnDutyCount = ReportTurnoverUtil.calculateDerivedEndOnDuty(beginCount, newEntryCount, totalResignCount);
            monthData.put("endOnDuty", endOnDutyCount);
            previousEndOnDuty = endOnDutyCount;

            monthData.put("activeRate", ReportTurnoverUtil.formatPercent(
                    ReportTurnoverUtil.calculateAvgTurnoverRate(activeResignCount, beginCount, endOnDutyCount)));
            monthData.put("passiveRate", ReportTurnoverUtil.formatPercent(
                    ReportTurnoverUtil.calculateAvgTurnoverRate(passiveResignCount, beginCount, endOnDutyCount)));
            monthData.put("totalRate", ReportTurnoverUtil.formatPercent(
                    ReportTurnoverUtil.calculateAvgTurnoverRate(totalResignCount, beginCount, endOnDutyCount)));
            monthData.put("entryRate", ReportTurnoverUtil.formatPercent(
                    ReportTurnoverUtil.calculateEntryRate(newEntryCount, beginCount, endOnDutyCount)));

            dataList.add(monthData);
        }

        return dataList;
    }

    /**
     * 统计指定日期的在岗人数(仅用于1月期初快照)。
     */
    private int getOnDutyCount(String snapshotDate, ReportOrgUtil.OrgScope scope) throws BOSException, SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) as count ");
        sql.append("FROM T_BD_PERSON person ");
        sql.append("LEFT JOIN T_HR_PersonPosition personPos ON person.FID = personPos.FPERSONID ");
        sql.append("INNER JOIN T_HR_BDEmployeeType type ON type.FID = person.FEMPLOYEETYPEID ");
        sql.append("WHERE type.FIsOnTheStrength = 1 ");
        sql.append("AND person.FHireDate < '").append(snapshotDate).append("' ");
        sql.append("AND (personPos.FLeftDate IS NULL OR personPos.FLeftDate >= '").append(snapshotDate).append("') ");

        ReportOrgUtil.appendPersonDepScopeFilter(sql, ctx, scope);
        return ReportOrgUtil.executeCountQuery(ctx, sql, "在岗人数[" + snapshotDate + "]", 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("WHERE bill.FBillState = 3 ");
        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);
    }

    private int getNewEntryCount(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("WHERE 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~当前月累计，比率按YTD月均分子与(1月期初+当前月期末)/2分母计算。
     */
    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 janBeginOnDuty = 0;
        int currentMonthEndOnDuty = 0;
        int totalActiveResign = 0;
        int totalPassiveResign = 0;
        int totalResign = 0;
        int totalNewEntry = 0;

        if (!monthDataList.isEmpty()) {
            janBeginOnDuty = (Integer) monthDataList.get(0).get("beginOnDuty");
        }

        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");
            totalNewEntry += (Integer) monthData.get("newEntry");
            currentMonthEndOnDuty = (Integer) monthData.get("endOnDuty");
        }

        summary.put("beginOnDuty", janBeginOnDuty);
        summary.put("activeResign", totalActiveResign);
        summary.put("passiveResign", totalPassiveResign);
        summary.put("totalResign", totalResign);
        summary.put("newEntry", totalNewEntry);
        summary.put("endOnDuty", currentMonthEndOnDuty);

        summary.put("activeRate", ReportTurnoverUtil.formatPercent(
                ReportTurnoverUtil.calculateSummaryYtdRate(totalActiveResign, currentMonth, janBeginOnDuty, currentMonthEndOnDuty)));
        summary.put("passiveRate", ReportTurnoverUtil.formatPercent(
                ReportTurnoverUtil.calculateSummaryYtdRate(totalPassiveResign, currentMonth, janBeginOnDuty, currentMonthEndOnDuty)));
        summary.put("totalRate", ReportTurnoverUtil.formatPercent(
                ReportTurnoverUtil.calculateSummaryYtdRate(totalResign, currentMonth, janBeginOnDuty, currentMonthEndOnDuty)));
        summary.put("entryRate", ReportTurnoverUtil.formatPercent(
                ReportTurnoverUtil.calculateSummaryYtdRate(totalNewEntry, currentMonth, janBeginOnDuty, currentMonthEndOnDuty)));

        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;
    }

}