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.bos.ctrl.swing.StringUtils; import com.kingdee.jdbc.rowset.IRowSet; import com.kingdee.eas.custom.perfweb.util.ReportFilterUtil; import com.kingdee.eas.custom.perfweb.util.ReportOrgUtil; import com.kingdee.eas.util.app.DbUtil; import com.kingdee.shr.base.syssetting.context.SHRContext; import com.kingdee.shr.base.syssetting.exception.SHRWebException; 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.*; /** * 类名称: PersonnelStructureAnalysisHandler * * 功能描述: 人员属性分析报表(按中心统计学历/年龄/司龄/性别结构，末尾追加品质部与合计行) * 创建日期: 2026-06-08 * 作 者: 青梧 * 版 本: 1.3 * * 返回字段说明： * centerCode - 中心/部门编码(FNUMBER，同名取第一个) * centerName - 中心/部门名称(跨公司同名合并，排除130；品质部为单独汇总行) * masterAbove - 硕士及以上人数(学历06/07/08) * masterAboveRate - 硕士及以上人数占比(分母totalCount) * bachelor - 本科人数(学历05) * bachelorRate - 本科人数占比 * college - 大专人数(学历04) * collegeRate - 大专人数占比 * highSchool - 高中/中专人数(学历02/03) * highSchoolRate - 高中/中专人数占比 * juniorBelow - 初中及以下人数(学历01) * juniorBelowRate - 初中及以下人数占比 * eduTotal - 学历合计(=totalCount) * below25 - 25岁以下人数 * below25Rate - 25岁以下人数占比 * age26_30 - 26-30岁人数 * age26_30Rate - 26-30岁人数占比 * age31_35 - 31-35岁人数 * age31_35Rate - 31-35岁人数占比 * age36_40 - 36-40岁人数 * age36_40Rate - 36-40岁人数占比 * above40 - 40岁以上人数 * above40Rate - 40岁以上人数占比 * ageTotal - 年龄合计 * below6Months - 司龄6个月以内人数 * below6MonthsRate - 司龄6个月以内人数占比 * age6M_1Y - 司龄6个月-1年人数 * age6M_1YRate - 司龄6个月-1年人数占比 * age1_3Y - 司龄1-3年人数 * age1_3YRate - 司龄1-3年人数占比 * age3_5Y - 司龄3-5年人数 * age3_5YRate - 司龄3-5年人数占比 * age5_10Y - 司龄5-10年人数 * age5_10YRate - 司龄5-10年人数占比 * above10Y - 司龄10年以上人数 * above10YRate - 司龄10年以上人数占比 * workAgeTotal - 司龄合计 * maleCount - 男性人数(FGender枚举1) * maleCountRate - 男性人数占比 * femaleCount - 女性人数(FGender枚举2) * femaleCountRate - 女性人数占比 * totalCount - 总人数(学历合计，各档占比统一分母) * eduCoefficient - 学历系数=(硕士*5+本科*4+大专*3+高中中专*2+初中及以下*1)/totalCount */ public class PersonnelStructureAnalysisHandler extends ListHandler { private static Logger logger = Logger.getLogger(PersonnelStructureAnalysisHandler.class); Context ctx = SHRContext.getInstance().getContext(); /** 单独汇总的品质部名称(跨公司合并) */ private static final String QUALITY_DEPT_NAME = "品质部"; /** * 列表查询入口：遍历中心，统计学历/年龄/司龄/性别结构，支持公司F7筛选，末尾追加品质部与合计行。 */ @Override protected GridDataEntity getGridRequestData(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws SHRWebException { logger.error("PersonnelStructureAnalysisHandler getGridRequestData------------"); GridDataEntity gridDataEntity = new GridDataEntity(); try { // 快筛: 公司FID(company字段非中心层级时) 限定统计范围 JSONObject fastFilterItemsJson = ReportFilterUtil.parseFastFilterJson(request); logger.error("fastFilterItemsJson: " + fastFilterItemsJson); // 公司F7：限定人员所属公司，不影响中心名称列表 Map paramMap = ReportFilterUtil.parseCompanyParams(fastFilterItemsJson, logger, false); String companyId = paramMap.get("companyId"); // centers: 去重后的中心名称编码列表(跨公司同名合并，排除130) List centers = ReportOrgUtil.getDistinctOrgNameCodes(ctx, ReportOrgUtil.LAYER_CENTER, companyId); List> dataList = new ArrayList>(); // ---------- 遍历中心，汇总各维度结构 ---------- List> centerRows = new ArrayList>(); for (ReportOrgUtil.OrgNameCode center : centers) { centerRows.add(buildStructureRow(center.orgCode, center.orgName, companyId, false)); } dataList.addAll(centerRows); // ---------- 品质部单独一行(跨公司合并，与中心行无互斥关系) ---------- dataList.add(buildStructureRow(resolveQualityDeptCode(companyId), QUALITY_DEPT_NAME, companyId, true)); // ---------- 合计行(仅累加各中心，不含品质部，避免重复) ---------- dataList.add(buildSummaryRow(centerRows)); gridDataEntity.setRows(dataList); gridDataEntity.setTotal(dataList.size()); gridDataEntity.setPage(1); gridDataEntity.setRecords(dataList.size()); logger.error("人员属性分析报表数据查询成功，总记录数: " + dataList.size()); } catch (SQLException e) { logger.error("数据库查询失败", e); throw new SHRWebException("数据查询失败: " + e.getMessage()); } catch (BOSException e) { logger.error("业务异常", e); throw new SHRWebException("业务处理失败: " + e.getMessage()); } catch (Exception e) { logger.error("未知异常", e); throw new SHRWebException("系统异常: " + e.getMessage()); } ReportFilterUtil.applyGridUserData(request, gridDataEntity); return gridDataEntity; } /** 查询品质部编码(跨公司同名取第一个) */ private String resolveQualityDeptCode(String companyId) throws BOSException, SQLException { List depts = ReportOrgUtil.getDistinctOrgNameCodes(ctx, ReportOrgUtil.LAYER_DEPT, companyId); for (ReportOrgUtil.OrgNameCode dept : depts) { if (QUALITY_DEPT_NAME.equals(dept.orgName)) { return dept.orgCode; } } return ""; } /** * 构建一行结构数据(人数+占比+学历系数)。 * * @param deptScope true=按部门名称跨公司统计，false=按中心统计 */ private Map buildStructureRow(String orgCode, String orgName, String companyId, boolean deptScope) throws BOSException, SQLException { Map educationStat = getEducationStat(orgName, companyId, deptScope); Map ageStat = getAgeStat(orgName, companyId, deptScope); Map workAgeStat = getWorkAgeStat(orgName, companyId, deptScope); // genderStat[0]=男, genderStat[1]=女 int[] genderStat = getGenderStat(orgName, companyId, deptScope); int totalCount = sumMap(educationStat); Map row = new LinkedHashMap(); row.put("centerCode", orgCode); // centerCode: 中心/部门编码 row.put("centerName", orgName); // centerName: 中心/部门名称 putCountWithRate(row, "masterAbove", educationStat.get("masterAbove"), totalCount); putCountWithRate(row, "bachelor", educationStat.get("bachelor"), totalCount); putCountWithRate(row, "college", educationStat.get("college"), totalCount); putCountWithRate(row, "highSchool", educationStat.get("highSchool"), totalCount); putCountWithRate(row, "juniorBelow", educationStat.get("juniorBelow"), totalCount); row.put("eduTotal", totalCount); // eduTotal: 学历合计 putCountWithRate(row, "below25", ageStat.get("below25"), totalCount); putCountWithRate(row, "age26_30", ageStat.get("age26_30"), totalCount); putCountWithRate(row, "age31_35", ageStat.get("age31_35"), totalCount); putCountWithRate(row, "age36_40", ageStat.get("age36_40"), totalCount); putCountWithRate(row, "above40", ageStat.get("above40"), totalCount); row.put("ageTotal", sumMap(ageStat)); // ageTotal: 年龄合计 putCountWithRate(row, "below6Months", workAgeStat.get("below6Months"), totalCount); putCountWithRate(row, "age6M_1Y", workAgeStat.get("age6M_1Y"), totalCount); putCountWithRate(row, "age1_3Y", workAgeStat.get("age1_3Y"), totalCount); putCountWithRate(row, "age3_5Y", workAgeStat.get("age3_5Y"), totalCount); putCountWithRate(row, "age5_10Y", workAgeStat.get("age5_10Y"), totalCount); putCountWithRate(row, "above10Y", workAgeStat.get("above10Y"), totalCount); row.put("workAgeTotal", sumMap(workAgeStat)); // workAgeTotal: 司龄合计 putCountWithRate(row, "maleCount", genderStat[0], totalCount); putCountWithRate(row, "femaleCount", genderStat[1], totalCount); row.put("totalCount", totalCount); // totalCount: 总人数 row.put("eduCoefficient", formatEduCoefficient(calculateEduCoefficient(educationStat, totalCount))); // eduCoefficient: 学历系数 return row; } /** * 合计行：各中心人数字段累加，占比与学历系数按合计totalCount重算。 */ private Map buildSummaryRow(List> centerRows) { Map educationStat = initEducationStat(); educationStat.put("masterAbove", sumIntField(centerRows, "masterAbove")); educationStat.put("bachelor", sumIntField(centerRows, "bachelor")); educationStat.put("college", sumIntField(centerRows, "college")); educationStat.put("highSchool", sumIntField(centerRows, "highSchool")); educationStat.put("juniorBelow", sumIntField(centerRows, "juniorBelow")); Map ageStat = initAgeStat(); ageStat.put("below25", sumIntField(centerRows, "below25")); ageStat.put("age26_30", sumIntField(centerRows, "age26_30")); ageStat.put("age31_35", sumIntField(centerRows, "age31_35")); ageStat.put("age36_40", sumIntField(centerRows, "age36_40")); ageStat.put("above40", sumIntField(centerRows, "above40")); Map workAgeStat = initWorkAgeStat(); workAgeStat.put("below6Months", sumIntField(centerRows, "below6Months")); workAgeStat.put("age6M_1Y", sumIntField(centerRows, "age6M_1Y")); workAgeStat.put("age1_3Y", sumIntField(centerRows, "age1_3Y")); workAgeStat.put("age3_5Y", sumIntField(centerRows, "age3_5Y")); workAgeStat.put("age5_10Y", sumIntField(centerRows, "age5_10Y")); workAgeStat.put("above10Y", sumIntField(centerRows, "above10Y")); int maleCount = sumIntField(centerRows, "maleCount"); int femaleCount = sumIntField(centerRows, "femaleCount"); int totalCount = sumMap(educationStat); Map row = new LinkedHashMap(); row.put("centerCode", ""); row.put("centerName", "合计"); putCountWithRate(row, "masterAbove", educationStat.get("masterAbove"), totalCount); putCountWithRate(row, "bachelor", educationStat.get("bachelor"), totalCount); putCountWithRate(row, "college", educationStat.get("college"), totalCount); putCountWithRate(row, "highSchool", educationStat.get("highSchool"), totalCount); putCountWithRate(row, "juniorBelow", educationStat.get("juniorBelow"), totalCount); row.put("eduTotal", totalCount); putCountWithRate(row, "below25", ageStat.get("below25"), totalCount); putCountWithRate(row, "age26_30", ageStat.get("age26_30"), totalCount); putCountWithRate(row, "age31_35", ageStat.get("age31_35"), totalCount); putCountWithRate(row, "age36_40", ageStat.get("age36_40"), totalCount); putCountWithRate(row, "above40", ageStat.get("above40"), totalCount); row.put("ageTotal", sumMap(ageStat)); putCountWithRate(row, "below6Months", workAgeStat.get("below6Months"), totalCount); putCountWithRate(row, "age6M_1Y", workAgeStat.get("age6M_1Y"), totalCount); putCountWithRate(row, "age1_3Y", workAgeStat.get("age1_3Y"), totalCount); putCountWithRate(row, "age3_5Y", workAgeStat.get("age3_5Y"), totalCount); putCountWithRate(row, "age5_10Y", workAgeStat.get("age5_10Y"), totalCount); putCountWithRate(row, "above10Y", workAgeStat.get("above10Y"), totalCount); row.put("workAgeTotal", sumMap(workAgeStat)); putCountWithRate(row, "maleCount", maleCount, totalCount); putCountWithRate(row, "femaleCount", femaleCount, totalCount); row.put("totalCount", totalCount); row.put("eduCoefficient", formatEduCoefficient(calculateEduCoefficient(educationStat, totalCount))); return row; } /** 累加各行指定整型字段 */ private int sumIntField(List> rows, String field) { int sum = 0; for (Map row : rows) { Object value = row.get(field); if (value instanceof Integer) { sum += ((Integer) value).intValue(); } } return sum; } /** 写入人数字段及对应占比(分母totalCount) */ private void putCountWithRate(Map row, String countKey, int count, int totalCount) { row.put(countKey, count); row.put(countKey + "Rate", formatPercent(count, totalCount)); } /** 格式化占比为百分数字符串(保留两位小数) */ private String formatPercent(int count, int totalCount) { if (totalCount <= 0) { return "0.00%"; } return String.format("%.2f", count * 100.0 / totalCount) + "%"; } /** * 计算学历系数。 * 公式:(硕士*5+本科*4+大专*3+高中中专*2+初中及以下*1)/totalCount */ private double calculateEduCoefficient(Map educationStat, int totalCount) { if (totalCount <= 0) { return 0.0; } double weighted = educationStat.get("masterAbove") * 5 + educationStat.get("bachelor") * 4 + educationStat.get("college") * 3 + educationStat.get("highSchool") * 2 + educationStat.get("juniorBelow"); return weighted / totalCount; } /** 格式化学历系数(保留两位小数) */ private String formatEduCoefficient(double coefficient) { return String.format("%.2f", coefficient); } /** 追加人员组织范围(中心或部门) */ private void appendPersonScope(StringBuilder sql, String orgName, String companyId, boolean deptScope) throws BOSException, SQLException { if (deptScope) { ReportOrgUtil.appendDeptPersonScope(sql, ctx, orgName, companyId); } else { ReportOrgUtil.appendCenterPersonScope(sql, ctx, orgName, companyId); } } /** * 按最高学历(FIsHighest=1)分组统计各学历区间人数。 * * @param orgName 中心或部门名称(FNAME_L2) * @param companyId 公司FID，可为空 * @param deptScope true=部门范围，false=中心范围 * @return key=学历区间编码, value=人数 */ private Map getEducationStat(String orgName, String companyId, boolean deptScope) throws BOSException, SQLException { Map stat = initEducationStat(); StringBuilder sql = new StringBuilder(); sql.append("SELECT diploma.FNumber as eduLevel, COUNT(*) as personCount "); appendPersonBaseFrom(sql); sql.append("LEFT JOIN T_HR_PersonDegree edu ON edu.FPersonId = person.FID "); sql.append("LEFT JOIN T_BD_HRDiploma diploma ON diploma.FID = edu.FDiploma "); sql.append("WHERE type.FIsOnTheStrength = 1 "); sql.append("AND edu.FIsHighest = 1 "); appendPersonScope(sql, orgName, companyId, deptScope); sql.append("GROUP BY diploma.FNumber"); logger.error("学历统计SQL[" + orgName + "]: " + sql.toString()); IRowSet rs = DbUtil.executeQuery(ctx, sql.toString()); while (rs.next()) { // 学历编码 String eduLevel = rs.getString("eduLevel"); // 该学历人数 int count = rs.getInt("personCount"); if (StringUtils.isEmpty(eduLevel)) { continue; } if ("06".equals(eduLevel) || "07".equals(eduLevel) || "08".equals(eduLevel)) { stat.put("masterAbove", stat.get("masterAbove") + count); } else if ("05".equals(eduLevel)) { stat.put("bachelor", stat.get("bachelor") + count); } else if ("04".equals(eduLevel)) { stat.put("college", stat.get("college") + count); } else if ("02".equals(eduLevel) || "03".equals(eduLevel)) { stat.put("highSchool", stat.get("highSchool") + count); } else if ("01".equals(eduLevel)) { stat.put("juniorBelow", stat.get("juniorBelow") + count); } } logger.error("学历统计结果[" + orgName + "]: " + stat); return stat; } /** * 按出生日期计算年龄并分组统计。 * * @return key=年龄区间, value=人数 */ private Map getAgeStat(String orgName, String companyId, boolean deptScope) throws BOSException, SQLException { Map stat = initAgeStat(); StringBuilder sql = new StringBuilder(); sql.append("SELECT DATEDIFF(YEAR, person.FBirthday, GETDATE()) as age, COUNT(*) as personCount "); appendPersonBaseFrom(sql); sql.append("WHERE type.FIsOnTheStrength = 1 "); sql.append("AND person.FBirthday IS NOT NULL "); appendPersonScope(sql, orgName, companyId, deptScope); sql.append("GROUP BY DATEDIFF(YEAR, person.FBirthday, GETDATE())"); logger.error("年龄统计SQL[" + orgName + "]: " + sql.toString()); IRowSet rs = DbUtil.executeQuery(ctx, sql.toString()); while (rs.next()) { // 年龄值 int age = rs.getInt("age"); // 该年龄人数 int count = rs.getInt("personCount"); if (age < 25) { stat.put("below25", stat.get("below25") + count); } else if (age <= 30) { stat.put("age26_30", stat.get("age26_30") + count); } else if (age <= 35) { stat.put("age31_35", stat.get("age31_35") + count); } else if (age <= 40) { stat.put("age36_40", stat.get("age36_40") + count); } else { stat.put("above40", stat.get("above40") + count); } } logger.error("年龄统计结果[" + orgName + "]: " + stat); return stat; } /** * 按入司日期(FJoinDate)计算司龄月数并分组统计。 * * @return key=司龄区间, value=人数 */ private Map getWorkAgeStat(String orgName, String companyId, boolean deptScope) throws BOSException, SQLException { Map stat = initWorkAgeStat(); StringBuilder sql = new StringBuilder(); sql.append("SELECT DATEDIFF(MONTH, personPos.FJoinDate, GETDATE()) as workMonths, COUNT(*) as personCount "); appendPersonBaseFrom(sql); sql.append("WHERE type.FIsOnTheStrength = 1 "); sql.append("AND personPos.FJoinDate IS NOT NULL "); appendPersonScope(sql, orgName, companyId, deptScope); sql.append("GROUP BY DATEDIFF(MONTH, personPos.FJoinDate, GETDATE())"); logger.error("司龄统计SQL[" + orgName + "]: " + sql.toString()); IRowSet rs = DbUtil.executeQuery(ctx, sql.toString()); while (rs.next()) { // 司龄月数 int workMonths = rs.getInt("workMonths"); // 该区间人数 int count = rs.getInt("personCount"); if (workMonths < 6) { stat.put("below6Months", stat.get("below6Months") + count); } else if (workMonths < 12) { stat.put("age6M_1Y", stat.get("age6M_1Y") + count); } else if (workMonths < 36) { stat.put("age1_3Y", stat.get("age1_3Y") + count); } else if (workMonths < 60) { stat.put("age3_5Y", stat.get("age3_5Y") + count); } else if (workMonths < 120) { stat.put("age5_10Y", stat.get("age5_10Y") + count); } else { stat.put("above10Y", stat.get("above10Y") + count); } } logger.error("司龄统计结果[" + orgName + "]: " + stat); return stat; } /** * 按性别FGender枚举(Genders: 1=男, 2=女)统计男女人数。 * * @return int[0]=男性人数, int[1]=女性人数 */ private int[] getGenderStat(String orgName, String companyId, boolean deptScope) throws BOSException, SQLException { int male = 0; int female = 0; StringBuilder sql = new StringBuilder(); sql.append("SELECT person.FGender as genderValue, COUNT(*) as personCount "); appendPersonBaseFrom(sql); sql.append("WHERE type.FIsOnTheStrength = 1 "); sql.append("AND person.FGender IS NOT NULL "); appendPersonScope(sql, orgName, companyId, deptScope); sql.append("GROUP BY person.FGender"); logger.error("性别统计SQL[" + orgName + "]: " + sql.toString()); IRowSet rs = DbUtil.executeQuery(ctx, sql.toString()); while (rs.next()) { // 性别枚举(1=男, 2=女) int genderValue = rs.getInt("genderValue"); // 该性别人数 int count = rs.getInt("personCount"); if (isMale(genderValue)) { male += count; } else if (isFemale(genderValue)) { female += count; } } logger.error("性别统计结果[" + orgName + "]: male=" + male + ", female=" + female); return new int[]{male, female}; } /** * 人员统计公共FROM子句(人员+任职+员工类型)。 */ private void appendPersonBaseFrom(StringBuilder sql) { sql.append("FROM T_BD_PERSON person "); sql.append("LEFT JOIN T_HR_PersonPosition personPos ON person.FID = personPos.FPERSONID "); sql.append("LEFT JOIN T_HR_BDEmployeeType type ON type.FID = person.FEMPLOYEETYPEID "); } /** 判断是否为男性(FGender枚举1) */ private boolean isMale(int genderValue) { return genderValue == 1; } /** 判断是否为女性(FGender枚举2) */ private boolean isFemale(int genderValue) { return genderValue == 2; } /** 汇总Map中所有计数值 */ private int sumMap(Map stat) { int total = 0; for (Integer value : stat.values()) { total += value == null ? 0 : value.intValue(); } return total; } /** 初始化学历统计Map，各区间默认0 */ private Map initEducationStat() { Map stat = new HashMap(); stat.put("masterAbove", 0); stat.put("bachelor", 0); stat.put("college", 0); stat.put("highSchool", 0); stat.put("juniorBelow", 0); return stat; } /** 初始化年龄统计Map */ private Map initAgeStat() { Map stat = new HashMap(); stat.put("below25", 0); stat.put("age26_30", 0); stat.put("age31_35", 0); stat.put("age36_40", 0); stat.put("above40", 0); return stat; } /** 初始化司龄统计Map */ private Map initWorkAgeStat() { Map stat = new HashMap(); stat.put("below6Months", 0); stat.put("age6M_1Y", 0); stat.put("age1_3Y", 0); stat.put("age3_5Y", 0); stat.put("age5_10Y", 0); stat.put("above10Y", 0); return stat; } }