MLAY/websrc/com/kingdee/eas/custom/perfweb/util/ReportOrgUtil.java

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

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.util.app.DbUtil;
import org.apache.log4j.Logger;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 类名称: ReportOrgUtil
 *
 * 功能描述: 报表组织范围过滤工具(排除130及下级、中心/部门层级查询与SQL拼接)
 * 创建日期: 2026-06-23
 * 作    者: 青梧
 * 版    本: 1.3
 */
public final class ReportOrgUtil {

  public static final String EXCLUDED_ORG_NUMBER = "130";
  public static final String LAYER_CENTER = "中心";
  public static final String LAYER_DEPT = "部门";

  private ReportOrgUtil() {
  }

  /** 按组织FID查询显示名称(FNAME_L2)。 */
  public static String getOrgDisplayName(Context ctx, String orgId) throws BOSException, SQLException {
    if (StringUtils.isEmpty(orgId)) {
      return null;
    }
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT FNAME_L2 FROM T_ORG_Admin WHERE FID = ?");
    IRowSet rs = DbUtil.executeQuery(ctx, sql.toString(), new Object[]{orgId});
    if (rs.next()) {
      return rs.getString("FNAME_L2");
    }
    return null;
  }

  /**
   * 按行政组织FID查询其下所有部门(跨公司同名合并，排除130)。
   */
  public static List<OrgNameCode> getDistinctDeptNameCodesUnderAdminOrg(Context ctx, String adminOrgId, String companyId)
      throws BOSException, SQLException {
    Map<String, String> nameToCode = new LinkedHashMap<String, String>();
    if (StringUtils.isEmpty(adminOrgId)) {
      return new ArrayList<OrgNameCode>();
    }
    String longNumber = getOrgLongNumber(ctx, adminOrgId);
    if (StringUtils.isEmpty(longNumber)) {
      return new ArrayList<OrgNameCode>();
    }
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT dept.FNAME_L2 AS orgName, dept.FNUMBER AS orgCode ");
    sql.append("FROM T_ORG_Admin root ");
    sql.append("INNER JOIN T_ORG_Admin dept ");
    sql.append("  ON LEFT(dept.FLongNumber, LEN(root.FLongNumber)) = root.FLongNumber ");
    sql.append("INNER JOIN T_Org_LayerType ltDept ON ltDept.FID = dept.FLAYERTYPEID ");
    sql.append("WHERE root.FID = '").append(escapeSql(adminOrgId)).append("' ");
    sql.append("AND ltDept.FNUMBER = '").append(escapeSql(LAYER_DEPT)).append("' ");
    sql.append("AND root.FIsSealUp = 0 AND dept.FIsSealUp = 0 ");
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND root.FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
    sql.append("ORDER BY dept.FNAME_L2, dept.FNUMBER");
    IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
    while (rs.next()) {
      String name = rs.getString("orgName");
      String code = rs.getString("orgCode");
      if (StringUtils.isEmpty(name) || StringUtils.isEmpty(code)) {
        continue;
      }
      if (!nameToCode.containsKey(name)) {
        nameToCode.put(name, code);
      }
    }
    List<OrgNameCode> result = new ArrayList<OrgNameCode>();
    for (Map.Entry<String, String> entry : nameToCode.entrySet()) {
      result.add(new OrgNameCode(entry.getKey(), entry.getValue()));
    }
    return result;
  }

  /** 按组织FID查询长编码(FLongNumber)。 */
  public static String getOrgLongNumber(Context ctx, String orgId) throws BOSException, SQLException {
    if (StringUtils.isEmpty(orgId)) {
      return null;
    }
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT FLONGNUMBER FROM T_ORG_Admin WHERE FID = ?");
    IRowSet rs = DbUtil.executeQuery(ctx, sql.toString(), new Object[]{orgId});
    if (rs.next()) {
      return rs.getString("FLONGNUMBER");
    }
    return null;
  }

  /** 查询排除组织130的长编码前缀。 */
  public static String getExcludedOrgLongNumber(Context ctx) throws BOSException, SQLException {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT FLONGNUMBER FROM T_ORG_Admin WHERE FNUMBER = ? AND FIsSealUp = 0");
    IRowSet rs = DbUtil.executeQuery(ctx, sql.toString(), new Object[]{EXCLUDED_ORG_NUMBER});
    if (rs.next()) {
      return rs.getString("FLONGNUMBER");
    }
    return null;
  }

  /** 按组织别名追加排除130子树条件(FLongNumber NOT LIKE)。 */
  public static void appendExcludeOrg130(StringBuilder sql, String orgAlias, Context ctx)
      throws BOSException, SQLException {
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND ").append(orgAlias).append(".FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
  }

  /** 按组织FID字段追加排除130子树条件(NOT IN 子查询)。 */
  public static void appendExcludeOrg130ById(StringBuilder sql, String orgIdField, Context ctx)
      throws BOSException, SQLException {
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND ").append(orgIdField).append(" NOT IN (");
      sql.append("SELECT sub.FID FROM T_ORG_Admin sub WHERE sub.FLongNumber LIKE '");
      sql.append(excludedLongNumber).append("%') ");
    }
  }

  /**
   * 组织过滤范围：按名称(跨公司)或按行政组织FID筛选。
   */
  public static final class OrgScope {
    /** 中心/部门名称 FNAME_L2 */
    public final String orgName;
    /** 组织层级类型，如 中心 / 部门 */
    public final String orgLayer;
    /** 行政组织FID(快筛选组织时生效) */
    public final String adminOrg;

    public OrgScope(String orgName, String orgLayer, String adminOrg) {
      this.orgName = orgName;
      this.orgLayer = orgLayer;
      this.adminOrg = adminOrg;
    }
  }

  /**
   * 按行政组织FID及其下级追加 IN 条件(长编码 LIKE 前缀匹配)。
   */
  public static void appendOrgFilter(StringBuilder sql, Context ctx, String adminOrg, String orgField)
      throws BOSException, SQLException {
    if (StringUtils.isEmpty(adminOrg)) {
      return;
    }
    String longNumber = getOrgLongNumber(ctx, adminOrg);
    if (StringUtils.isEmpty(longNumber)) {
      return;
    }
    sql.append("AND ").append(orgField).append(" IN (");
    sql.append("SELECT admin.FID FROM T_ORG_Admin admin ");
    sql.append("WHERE admin.FLongNumber LIKE '").append(escapeSql(longNumber)).append("%' ");
    sql.append("AND admin.FIsSealUp = 0 ");
    sql.append(") ");
  }

  /**
   * 按组织名称+层级追加 IN 条件(跨公司同名合并，排除130)。
   */
  public static void appendOrgFilterByName(StringBuilder sql, Context ctx, String orgName,
      String layerType, String orgField) throws BOSException, SQLException {
    if (StringUtils.isEmpty(orgName)) {
      return;
    }
    String layer = StringUtils.isEmpty(layerType) ? LAYER_CENTER : layerType;
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);

    sql.append("AND ").append(orgField).append(" IN (");
    sql.append("SELECT subOrg.FID FROM T_ORG_Admin orgNode ");
    sql.append("INNER JOIN T_Org_LayerType lt ON lt.FID = orgNode.FLAYERTYPEID ");
    sql.append("INNER JOIN T_ORG_Admin subOrg ");
    sql.append("  ON LEFT(subOrg.FLongNumber, LEN(orgNode.FLongNumber)) = orgNode.FLongNumber ");
    sql.append("WHERE orgNode.FNAME_L2 = '").append(escapeSql(orgName)).append("' ");
    sql.append("AND lt.FNUMBER = '").append(escapeSql(layer)).append("' ");
    sql.append("AND orgNode.FIsSealUp = 0 AND subOrg.FIsSealUp = 0 ");
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND orgNode.FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
    sql.append(") ");
  }

  /**
   * 统一组织范围过滤：优先名称，其次行政组织FID，最后排除130。
   */
  public static void appendOrgScopeFilter(StringBuilder sql, Context ctx, String orgField, OrgScope scope)
      throws BOSException, SQLException {
    if (scope != null && !StringUtils.isEmpty(scope.orgName)) {
      appendOrgFilterByName(sql, ctx, scope.orgName, scope.orgLayer, orgField);
      return;
    }
    if (scope != null && !StringUtils.isEmpty(scope.adminOrg)) {
      appendOrgFilter(sql, ctx, scope.adminOrg, orgField);
    }
    appendExcludeOrg130ById(sql, orgField, ctx);
  }

  /** 人员任职部门范围过滤。 */
  public static void appendPersonDepScopeFilter(StringBuilder sql, Context ctx, OrgScope scope)
      throws BOSException, SQLException {
    appendOrgScopeFilter(sql, ctx, "personPos.FPersonDep", scope);
  }

  /** 行政组织字段范围过滤。 */
  public static void appendAdminOrgScopeFilter(StringBuilder sql, Context ctx, OrgScope scope, String orgField)
      throws BOSException, SQLException {
    appendOrgScopeFilter(sql, ctx, orgField, scope);
  }

  /**
   * 中心人员范围：可选公司 + 按中心名称过滤任职部门(跨公司同名，排除130)。
   */
  public static void appendCenterPersonScope(StringBuilder sql, Context ctx, String centerName, String companyId)
      throws BOSException, SQLException {
    if (!StringUtils.isEmpty(companyId)) {
      sql.append("AND personPos.FCompanyID = '").append(companyId).append("' ");
    }
    appendOrgFilterByName(sql, ctx, centerName, LAYER_CENTER, "personPos.FPersonDep");
  }

  /**
   * 部门人员范围：可选公司 + 按部门名称过滤任职部门(跨公司同名，排除130)。
   */
  public static void appendDeptPersonScope(StringBuilder sql, Context ctx, String deptName, String companyId)
      throws BOSException, SQLException {
    if (!StringUtils.isEmpty(companyId)) {
      sql.append("AND personPos.FCompanyID = '").append(companyId).append("' ");
    }
    appendOrgFilterByName(sql, ctx, deptName, LAYER_DEPT, "personPos.FPersonDep");
  }

  /**
   * 查询指定中心下的部门名称编码列表(跨公司同名合并，排除130)。
   */
  public static List<OrgNameCode> getDistinctDeptNameCodesUnderCenter(Context ctx, String centerName, String companyId)
      throws BOSException, SQLException {
    Map<String, String> nameToCode = new LinkedHashMap<String, String>();
    if (StringUtils.isEmpty(centerName)) {
      return new ArrayList<OrgNameCode>();
    }
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT dept.FNAME_L2 AS orgName, dept.FNUMBER AS orgCode ");
    sql.append("FROM T_ORG_Admin center ");
    sql.append("INNER JOIN T_Org_LayerType ltCenter ON ltCenter.FID = center.FLAYERTYPEID ");
    sql.append("INNER JOIN T_ORG_Admin dept ");
    sql.append("  ON LEFT(dept.FLongNumber, LEN(center.FLongNumber)) = center.FLongNumber ");
    sql.append("INNER JOIN T_Org_LayerType ltDept ON ltDept.FID = dept.FLAYERTYPEID ");
    sql.append("WHERE center.FNAME_L2 = '").append(escapeSql(centerName)).append("' ");
    sql.append("AND ltCenter.FNUMBER = '").append(escapeSql(LAYER_CENTER)).append("' ");
    sql.append("AND ltDept.FNUMBER = '").append(escapeSql(LAYER_DEPT)).append("' ");
    sql.append("AND center.FIsSealUp = 0 AND dept.FIsSealUp = 0 ");
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND center.FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
    sql.append("ORDER BY dept.FNAME_L2, dept.FNUMBER");
    IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
    while (rs.next()) {
      String name = rs.getString("orgName");
      String code = rs.getString("orgCode");
      if (StringUtils.isEmpty(name) || StringUtils.isEmpty(code)) {
        continue;
      }
      if (!nameToCode.containsKey(name)) {
        nameToCode.put(name, code);
      }
    }
    List<OrgNameCode> result = new ArrayList<OrgNameCode>();
    for (Map.Entry<String, String> entry : nameToCode.entrySet()) {
      result.add(new OrgNameCode(entry.getKey(), entry.getValue()));
    }
    return result;
  }

  /**
   * 行政组织FID+部门名称追加 IN 条件(部门须在该行政组织树下)。
   */
  public static void appendDeptUnderAdminOrgFilter(StringBuilder sql, Context ctx, String adminOrgId,
      String deptName, String orgField) throws BOSException, SQLException {
    if (StringUtils.isEmpty(adminOrgId) || StringUtils.isEmpty(deptName)) {
      return;
    }
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    sql.append("AND ").append(orgField).append(" IN (");
    sql.append("SELECT sub.FID FROM T_ORG_Admin root ");
    sql.append("INNER JOIN T_ORG_Admin deptNode ON deptNode.FNAME_L2 = '").append(escapeSql(deptName)).append("' ");
    sql.append("INNER JOIN T_Org_LayerType ltDept ON ltDept.FID = deptNode.FLAYERTYPEID ");
    sql.append("INNER JOIN T_ORG_Admin sub ");
    sql.append("  ON LEFT(sub.FLongNumber, LEN(deptNode.FLongNumber)) = deptNode.FLongNumber ");
    sql.append("WHERE root.FID = '").append(escapeSql(adminOrgId)).append("' ");
    sql.append("AND ltDept.FNUMBER = '").append(escapeSql(LAYER_DEPT)).append("' ");
    sql.append("AND LEFT(deptNode.FLongNumber, LEN(root.FLongNumber)) = root.FLongNumber ");
    sql.append("AND root.FIsSealUp = 0 AND deptNode.FIsSealUp = 0 AND sub.FIsSealUp = 0 ");
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND root.FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
    sql.append(") ");
  }

  /**
   * 行政组织FID+部门编码追加 IN 条件(部门须在该组织树下)。
   */
  public static void appendDeptUnderAdminOrgFilterByCode(StringBuilder sql, Context ctx, String adminOrgId,
      String deptCode, String orgField) throws BOSException, SQLException {
    if (StringUtils.isEmpty(adminOrgId) || StringUtils.isEmpty(deptCode)) {
      return;
    }
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    sql.append("AND ").append(orgField).append(" IN (");
    sql.append("SELECT sub.FID FROM T_ORG_Admin root ");
    sql.append("INNER JOIN T_ORG_Admin deptNode ON deptNode.FNUMBER = '").append(escapeSql(deptCode)).append("' ");
    sql.append("INNER JOIN T_ORG_Admin sub ");
    sql.append("  ON LEFT(sub.FLongNumber, LEN(deptNode.FLongNumber)) = deptNode.FLongNumber ");
    sql.append("WHERE root.FID = '").append(escapeSql(adminOrgId)).append("' ");
    sql.append("AND LEFT(deptNode.FLongNumber, LEN(root.FLongNumber)) = root.FLongNumber ");
    sql.append("AND root.FIsSealUp = 0 AND deptNode.FIsSealUp = 0 AND sub.FIsSealUp = 0 ");
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND root.FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
    sql.append(") ");
  }

  /**
   * 中心名称+部门编码追加 IN 条件(跨公司同名，排除130)。
   */
  public static void appendDeptUnderCenterFilterByCode(StringBuilder sql, Context ctx, String centerName,
      String deptCode, String orgField) throws BOSException, SQLException {
    if (StringUtils.isEmpty(centerName) || StringUtils.isEmpty(deptCode)) {
      return;
    }
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    sql.append("AND ").append(orgField).append(" IN (");
    sql.append("SELECT sub.FID FROM T_ORG_Admin center ");
    sql.append("INNER JOIN T_Org_LayerType ltCenter ON ltCenter.FID = center.FLAYERTYPEID ");
    sql.append("INNER JOIN T_ORG_Admin deptNode ON deptNode.FNUMBER = '").append(escapeSql(deptCode)).append("' ");
    sql.append("INNER JOIN T_ORG_Admin sub ");
    sql.append("  ON LEFT(sub.FLongNumber, LEN(deptNode.FLongNumber)) = deptNode.FLongNumber ");
    sql.append("WHERE center.FNAME_L2 = '").append(escapeSql(centerName)).append("' ");
    sql.append("AND ltCenter.FNUMBER = '").append(escapeSql(LAYER_CENTER)).append("' ");
    sql.append("AND LEFT(deptNode.FLongNumber, LEN(center.FLongNumber)) = center.FLongNumber ");
    sql.append("AND center.FIsSealUp = 0 AND deptNode.FIsSealUp = 0 AND sub.FIsSealUp = 0 ");
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND center.FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
    sql.append(") ");
  }

  /**
   * 中心名称+部门名称追加 IN 条件(跨公司同名，排除130)。
   */
  public static void appendDeptUnderCenterFilter(StringBuilder sql, Context ctx, String centerName,
      String deptName, String orgField) throws BOSException, SQLException {
    if (StringUtils.isEmpty(centerName) || StringUtils.isEmpty(deptName)) {
      return;
    }
    String excludedLongNumber = getExcludedOrgLongNumber(ctx);
    sql.append("AND ").append(orgField).append(" IN (");
    sql.append("SELECT sub.FID FROM T_ORG_Admin center ");
    sql.append("INNER JOIN T_Org_LayerType ltCenter ON ltCenter.FID = center.FLAYERTYPEID ");
    sql.append("INNER JOIN T_ORG_Admin deptNode ON deptNode.FNAME_L2 = '").append(escapeSql(deptName)).append("' ");
    sql.append("INNER JOIN T_Org_LayerType ltDept ON ltDept.FID = deptNode.FLAYERTYPEID ");
    sql.append("INNER JOIN T_ORG_Admin sub ");
    sql.append("  ON LEFT(sub.FLongNumber, LEN(deptNode.FLongNumber)) = deptNode.FLongNumber ");
    sql.append("WHERE center.FNAME_L2 = '").append(escapeSql(centerName)).append("' ");
    sql.append("AND ltCenter.FNUMBER = '").append(escapeSql(LAYER_CENTER)).append("' ");
    sql.append("AND ltDept.FNUMBER = '").append(escapeSql(LAYER_DEPT)).append("' ");
    sql.append("AND LEFT(deptNode.FLongNumber, LEN(center.FLongNumber)) = center.FLongNumber ");
    sql.append("AND center.FIsSealUp = 0 AND deptNode.FIsSealUp = 0 AND sub.FIsSealUp = 0 ");
    if (!StringUtils.isEmpty(excludedLongNumber)) {
      sql.append("AND center.FLongNumber NOT LIKE '").append(excludedLongNumber).append("%' ");
    }
    sql.append(") ");
  }

  /**
   * 组织名称与编码(跨公司同名时编码取第一个)。
   */
  public static final class OrgNameCode {
    /** 组织名称 FNAME_L2 */
    public final String orgName;
    /** 组织编码 FNUMBER，同名跨公司时取第一个 */
    public final String orgCode;

    public OrgNameCode(String orgName, String orgCode) {
      this.orgName = orgName;
      this.orgCode = orgCode;
    }
  }

  /**
   * 按快筛文本过滤组织列表(文本包含组织名称则保留，无匹配则返回原列表)。
   */
  public static List<OrgNameCode> filterOrgNameCodes(List<OrgNameCode> source, String filterText) {
    if (source == null || source.isEmpty() || StringUtils.isEmpty(filterText)) {
      return source;
    }
    List<OrgNameCode> filtered = new ArrayList<OrgNameCode>();
    for (OrgNameCode item : source) {
      if (filterText.contains(item.orgName)) {
        filtered.add(item);
      }
    }
    return filtered.isEmpty() ? source : filtered;
  }

  /**
   * 查询指定层级的去重组织名称编码列表(跨公司同名合并，排除130)。
   *
   * @param layerType 组织层级，如"中心"/"部门"
   * @param companyId 公司FID，可选限定范围
   * @return 去重后的组织名称编码列表
   */
  public static List<OrgNameCode> getDistinctOrgNameCodes(Context ctx, String layerType, String companyId)
      throws BOSException, SQLException {
    Map<String, String> nameToCode = new LinkedHashMap<String, String>();
    StringBuilder sql = new StringBuilder();
    List<Object> params = new ArrayList<Object>();
    sql.append("SELECT admin.FNAME_L2 AS orgName, admin.FNUMBER AS orgCode ");
    sql.append("FROM T_ORG_Admin admin ");
    sql.append("INNER JOIN T_Org_LayerType lt ON lt.FID = admin.FLAYERTYPEID ");
    sql.append("WHERE lt.FNUMBER = ? AND admin.FIsSealUp = 0 ");
    params.add(layerType);
    if (!StringUtils.isEmpty(companyId)) {
      sql.append("AND admin.FParentID = ? ");
      params.add(companyId);
    }
    appendExcludeOrg130(sql, "admin", ctx);
    sql.append("ORDER BY admin.FNAME_L2, admin.FNUMBER");
    IRowSet rs = DbUtil.executeQuery(ctx, sql.toString(), params.toArray());
    while (rs.next()) {
      String name = rs.getString("orgName");
      String code = rs.getString("orgCode");
      if (StringUtils.isEmpty(name) || StringUtils.isEmpty(code)) {
        continue;
      }
      if (!nameToCode.containsKey(name)) {
        nameToCode.put(name, code);
      }
    }
    List<OrgNameCode> result = new ArrayList<OrgNameCode>();
    for (Map.Entry<String, String> entry : nameToCode.entrySet()) {
      result.add(new OrgNameCode(entry.getKey(), entry.getValue()));
    }
    return result;
  }

  /** 执行 COUNT 查询并返回结果，可选打日志。 */
  public static int executeCountQuery(Context ctx, StringBuilder sql, String logPrefix, Logger logger)
      throws BOSException, SQLException {
    if (logger != null) {
      logger.error(logPrefix + "SQL: " + sql.toString());
    }
    IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
    if (rs.next()) {
      return rs.getInt("count");
    }
    return 0;
  }

  /** SQL 单引号转义。 */
  private static String escapeSql(String value) {
    if (value == null) {
      return "";
    }
    return value.replace("'", "''");
  }

}