MLAY/websrc/com/kingdee/eas/custom/perfweb/handler/HumanEfficiencyLedgerReportHandler.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.jdbc.rowset.IRowSet;
import com.kingdee.eas.custom.perfweb.util.ReportDateUtil;
import com.kingdee.eas.custom.perfweb.util.ReportFilterUtil;
import com.kingdee.eas.util.app.DbUtil;
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.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.SQLException;
import java.util.*;

/**
 * 类名称: HumanEfficiencyLedgerReportHandler
 * 功能描述: 人效台账报表(人效基础资料手工数据 + 薪酬汇总 + 人效比/人工成本)
 * 创建日期: 2026-06-23
 * 作    者: 青梧
 * 版    本: 1.0
 *
 * 返回字段说明：
 *   month           - 月份(合计行显示合计)
 *   shipAmt         - 出货金额(人效基础资料CFShipAmt)
 *   formalCnt       - 正式员工人数(薪酬表去重)
 *   formalPay       - 正式员工实发工资(S103)
 *   formalSS        - 正式员工社保公司部分(S119)
 *   formalHF        - 正式员工公积金公司部分(S118)
 *   welfare         - 福利费(CFWelfare)
 *   eduFee          - 教育经费(CFEduFee)
 *   formalCost      - 正式员工人工成本合计(工资+社保+公积金+福利+教育)
 *   dispatchCnt     - 派遣员工人数(CFDispatchCnt)
 *   dispatchWage    - 派遣员工工资(CFDispatchWage)
 *   totalLaborCost  - 总人工成本(正式成本+派遣工资)
 *   humEff          - 人效比(出货金额/总人工成本)
 */
public class HumanEfficiencyLedgerReportHandler extends ListHandler {

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

    /** 薪酬汇总表 */
    private static final String PAYROLL_TABLE = "T_HR_SCmpCalTable";
    /** 薪酬计算完成状态 */
    private static final int PAYROLL_CAL_STATE = 20;
    /** 实发工资列 */
    private static final String COL_ACTUAL_PAY = "S103";
    /** 社保(公司)列 */
    private static final String COL_SS_COMPANY = "S119";
    /** 公积金(公司)列 */
    private static final String COL_HF_COMPANY = "S118";

    /**
     * 列表查询入口：按年度输出12个月人效台账，末尾追加合计行。
     */
    @Override
    protected GridDataEntity getGridRequestData(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws ShrWebBizException {
        logger.error("HumanEfficiencyLedgerReportHandler getGridRequestData------------");

        GridDataEntity gridDataEntity = new GridDataEntity();

        try {
            // 快筛: 年度 year
            String filterItem = ReportFilterUtil.getFilterItems(request);
            JSONObject fastFilterItemsJson = ReportFilterUtil.parseFastFilterJson(request);
            logger.error("filterItem: " + filterItem);
            logger.error("fastFilterItemsJson: " + fastFilterItemsJson);

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

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

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

    /**
     * 逐月组装人效台账行：基础资料 + 薪酬汇总 + 人效比/人工成本。
     */
    private List<Map<String, Object>> getMonthLedgerData(String year) throws BOSException, SQLException {
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();

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

            Map<String, BigDecimal> manual = loadLedgerManualData(year, month);
            row.put("shipAmt", manual.get("shipAmt")); // shipAmt: 出货金额
            row.put("welfare", manual.get("welfare")); // welfare: 福利费
            row.put("eduFee", manual.get("eduFee")); // eduFee: 教育经费
            row.put("dispatchCnt", manual.get("dispatchCnt").intValue()); // dispatchCnt: 派遣员工人数
            row.put("dispatchWage", manual.get("dispatchWage")); // dispatchWage: 派遣员工工资

            Map<String, Object> payroll = loadPayrollData(year, month);
            row.put("formalCnt", payroll.get("formalCnt")); // formalCnt: 正式员工人数
            row.put("formalPay", payroll.get("formalPay")); // formalPay: 正式员工工资
            row.put("formalSS", payroll.get("formalSS")); // formalSS: 社保(公司)
            row.put("formalHF", payroll.get("formalHF")); // formalHF: 公积金(公司)

            BigDecimal formalPay = (BigDecimal) payroll.get("formalPay");
            BigDecimal formalSS = (BigDecimal) payroll.get("formalSS");
            BigDecimal formalHF = (BigDecimal) payroll.get("formalHF");

            if (manual.get("manualSS").compareTo(BigDecimal.ZERO) > 0) {
                // 基础资料手工维护社保时，优先使用手工值
                formalSS = manual.get("manualSS");
                row.put("formalSS", formalSS);
            }
            if (manual.get("manualHF").compareTo(BigDecimal.ZERO) > 0) {
                // 基础资料手工维护公积金时，优先使用手工值
                formalHF = manual.get("manualHF");
                row.put("formalHF", formalHF);
            }

            // 正式员工人工成本 = 工资 + 社保公司 + 公积金公司 + 福利 + 教育经费
            BigDecimal formalCost = formalPay.add(formalSS).add(formalHF)
                    .add(manual.get("welfare")).add(manual.get("eduFee"));
            row.put("formalCost", formalCost); // formalCost: 正式员工人工成本

            // 总人工成本 = 正式成本 + 派遣员工工资
            BigDecimal totalLaborCost = formalCost.add(manual.get("dispatchWage"));
            row.put("totalLaborCost", totalLaborCost); // totalLaborCost: 总人工成本
            row.put("humEff", calculateHumEff(manual.get("shipAmt"), totalLaborCost)); // humEff: 人效比
            logger.error("人效比[" + year + "年" + month + "月] shipAmt=" + manual.get("shipAmt")
                    + ", totalLaborCost=" + totalLaborCost);

            dataList.add(row);
        }

        return dataList;
    }

    /**
     * 读取人效基础资料当月最新一条记录(优先CFStatDate，否则FBizDate)。
     */
    private Map<String, BigDecimal> loadLedgerManualData(String year, int month) throws BOSException, SQLException {
        Map<String, BigDecimal> result = emptyManualMap();

        String monthStart = ReportDateUtil.getMonthStart(year, month);
        String nextMonthStart = ReportDateUtil.getNextMonthStart(year, month);

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT bill.CFShipAmt, bill.CFWelfare, bill.CFEduFee, ");
        sql.append("bill.CFDispatchCnt, bill.CFDispatchWage, ");
        sql.append("bill.CFSbgs, bill.CFGjjgs ");
        sql.append("FROM CT_PER_HumanEfficiencyLedger bill ");
        sql.append("WHERE (");
        sql.append("(bill.CFStatDate IS NOT NULL AND bill.CFStatDate >= '").append(monthStart).append("' ");
        sql.append("AND bill.CFStatDate < '").append(nextMonthStart).append("') ");
        sql.append("OR (bill.CFStatDate IS NULL AND bill.FBizDate >= '").append(monthStart).append("' ");
        sql.append("AND bill.FBizDate < '").append(nextMonthStart).append("')) ");
        sql.append("ORDER BY bill.CFStatDate DESC, bill.FBizDate DESC ");

        logger.error("人效台账[" + year + "年" + month + "月]基础资料SQL: " + sql);

        IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
        if (rs.next()) {
            result.put("shipAmt", nvl(rs.getBigDecimal("CFShipAmt")));
            result.put("welfare", nvl(rs.getBigDecimal("CFWelfare")));
            result.put("eduFee", nvl(rs.getBigDecimal("CFEduFee")));
            result.put("dispatchCnt", BigDecimal.valueOf(rs.getInt("CFDispatchCnt")));
            result.put("dispatchWage", nvl(rs.getBigDecimal("CFDispatchWage")));
            result.put("manualSS", nvl(rs.getBigDecimal("CFSbgs")));
            result.put("manualHF", nvl(rs.getBigDecimal("CFGjjgs")));
        }
        return result;
    }

    /** 初始化基础资料默认值(全部为0)。 */
    private Map<String, BigDecimal> emptyManualMap() {
        Map<String, BigDecimal> result = new HashMap<String, BigDecimal>();
        result.put("shipAmt", BigDecimal.ZERO);
        result.put("welfare", BigDecimal.ZERO);
        result.put("eduFee", BigDecimal.ZERO);
        result.put("dispatchCnt", BigDecimal.ZERO);
        result.put("dispatchWage", BigDecimal.ZERO);
        result.put("manualSS", BigDecimal.ZERO);
        result.put("manualHF", BigDecimal.ZERO);
        return result;
    }

    /**
     * 读取薪酬汇总表(FCalState=20)当月正式员工数据。
     */
    private Map<String, Object> loadPayrollData(String year, int month) throws BOSException, SQLException {
        Map<String, Object> result = new HashMap<String, Object>();
        result.put("formalCnt", 0);
        result.put("formalPay", BigDecimal.ZERO);
        result.put("formalSS", BigDecimal.ZERO);
        result.put("formalHF", BigDecimal.ZERO);

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(DISTINCT pay.FPersonID) AS formalCnt, ");
        sql.append("SUM(pay.").append(COL_ACTUAL_PAY).append(") AS formalPay, ");
        sql.append("SUM(pay.").append(COL_SS_COMPANY).append(") AS formalSS, ");
        sql.append("SUM(pay.").append(COL_HF_COMPANY).append(") AS formalHF ");
        sql.append("FROM ").append(PAYROLL_TABLE).append(" pay ");
        sql.append("WHERE pay.FPeriodYear = ").append(year).append(" ");
        sql.append("AND pay.FPeriodMonth = ").append(month).append(" ");
        sql.append("AND pay.FCalState = ").append(PAYROLL_CAL_STATE).append(" ");

        logger.error("薪酬[" + year + "年" + month + "月]SQL: " + sql);

        IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
        if (rs.next()) {
            result.put("formalCnt", rs.getInt("formalCnt"));
            result.put("formalPay", nvl(rs.getBigDecimal("formalPay")));
            result.put("formalSS", nvl(rs.getBigDecimal("formalSS")));
            result.put("formalHF", nvl(rs.getBigDecimal("formalHF")));
        }
        return result;
    }

    /**
     * 人效比 = 出货金额 / 总人工成本
     */
    private BigDecimal calculateHumEff(BigDecimal shipAmt, BigDecimal totalLaborCost) {
        if (shipAmt == null || totalLaborCost == null || totalLaborCost.compareTo(BigDecimal.ZERO) == 0) {
            return BigDecimal.ZERO.setScale(4, RoundingMode.HALF_UP);
        }
        return shipAmt.divide(totalLaborCost, 4, RoundingMode.HALF_UP);
    }

    private BigDecimal nvl(BigDecimal value) {
        return value == null ? BigDecimal.ZERO : value;
    }

    /**
     * 合计行：金额/人数字段全年累加，人效比用合计金额重算。
     */
    private Map<String, Object> calculateSummary(List<Map<String, Object>> monthDataList) {
        Map<String, Object> summary = new LinkedHashMap<String, Object>();
        summary.put("month", "合计"); // month: 合计行

        BigDecimal totalShipAmt = BigDecimal.ZERO;
        int totalFormalCnt = 0;
        BigDecimal totalFormalPay = BigDecimal.ZERO;
        BigDecimal totalFormalSS = BigDecimal.ZERO;
        BigDecimal totalFormalHF = BigDecimal.ZERO;
        BigDecimal totalWelfare = BigDecimal.ZERO;
        BigDecimal totalEduFee = BigDecimal.ZERO;
        BigDecimal totalFormalCost = BigDecimal.ZERO;
        int totalDispatchCnt = 0;
        BigDecimal totalDispatchWage = BigDecimal.ZERO;
        BigDecimal totalLaborCost = BigDecimal.ZERO;

        for (Map<String, Object> row : monthDataList) {
            totalShipAmt = totalShipAmt.add((BigDecimal) row.get("shipAmt"));
            totalFormalCnt += (Integer) row.get("formalCnt");
            totalFormalPay = totalFormalPay.add((BigDecimal) row.get("formalPay"));
            totalFormalSS = totalFormalSS.add((BigDecimal) row.get("formalSS"));
            totalFormalHF = totalFormalHF.add((BigDecimal) row.get("formalHF"));
            totalWelfare = totalWelfare.add((BigDecimal) row.get("welfare"));
            totalEduFee = totalEduFee.add((BigDecimal) row.get("eduFee"));
            totalFormalCost = totalFormalCost.add((BigDecimal) row.get("formalCost"));
            totalDispatchCnt += (Integer) row.get("dispatchCnt");
            totalDispatchWage = totalDispatchWage.add((BigDecimal) row.get("dispatchWage"));
            totalLaborCost = totalLaborCost.add((BigDecimal) row.get("formalCost")).add((BigDecimal) row.get("dispatchWage"));
        }

        summary.put("shipAmt", totalShipAmt);
        summary.put("formalCnt", totalFormalCnt);
        summary.put("formalPay", totalFormalPay);
        summary.put("formalSS", totalFormalSS);
        summary.put("formalHF", totalFormalHF);
        summary.put("welfare", totalWelfare);
        summary.put("eduFee", totalEduFee);
        summary.put("formalCost", totalFormalCost);
        summary.put("dispatchCnt", totalDispatchCnt);
        summary.put("dispatchWage", totalDispatchWage);
        summary.put("totalLaborCost", totalLaborCost);
        summary.put("humEff", calculateHumEff(totalShipAmt, totalLaborCost));

        return summary;
    }
}