所有2级部门

本文档内容来源于 http://doc.myapps.cn/docs/common-script/common-script-1f2a4t12paer6

脚本说明

该脚本用于获取所有2级部门。在MyApps平台中，2级部门是指直接隶属于1级部门（根部门）的子部门。可以通过查询部门表，找出所有parentid为1级部门ID的部门。

常见实现方式

方式1：通过SQL查询获取所有2级部门

(function(){
    var domainId = getWebUser().getDomainid();

    // 先获取所有1级部门（parentid为空或null的部门）
    var sql1 = "SELECT id FROM t_department WHERE (parentid IS NULL OR parentid = '') AND domainid = '" + domainId + "'";
    var level1Depts = queryBySQL(sql1);

    var level1Ids = [];
    if(level1Depts != null && level1Depts.size() > 0){
        for(var it = level1Depts.iterator(); it.hasNext();){
            var dept = it.next();
            level1Ids.push("'" + dept.getId() + "'");
        }
    }

    if(level1Ids.length == 0){
        return "[]";
    }

    // 获取所有2级部门（parentid在1级部门ID列表中的部门）
    var sql2 = "SELECT id, name, code, parentid FROM t_department WHERE parentid IN (" + level1Ids.join(",") + ") AND domainid = '" + domainId + "'";
    var level2Depts = queryBySQL(sql2);

    var result = [];
    if(level2Depts != null && level2Depts.size() > 0){
        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            result.push({
                id: dept.getId(),
                name: dept.getItemValueAsString("name"),
                code: dept.getItemValueAsString("code"),
                parentId: dept.getItemValueAsString("parentid")
            });
        }
    }

    return JSON.stringify(result);
})();

方式2：通过部门对象获取所有2级部门

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT id FROM t_department WHERE (parentid IS NULL OR parentid = '') AND domainid = '" + domainId + "'";
    var level1Depts = queryBySQL(sql);

    var level2Depts = [];

    if(level1Depts != null && level1Depts.size() > 0){
        for(var it = level1Depts.iterator(); it.hasNext();){
            var level1Dept = it.next();
            var level1DeptId = level1Dept.getId();
            var level1DeptObj = getDepartmentById(level1DeptId);

            // 获取该1级部门的所有子部门（2级部门）
            var children = level1DeptObj.getChildren();
            if(children != null && children.size() > 0){
                for(var i = 0; i < children.size(); i++){
                    var child = children.get(i);
                    level2Depts.push({
                        id: child.getId(),
                        name: child.getName(),
                        code: child.getCode(),
                        parentId: level1DeptId,
                        parentName: level1DeptObj.getName()
                    });
                }
            }
        }
    }

    return JSON.stringify(level2Depts);
})();

方式3：使用JOIN查询获取所有2级部门

(function(){
    var domainId = getWebUser().getDomainid();

    // 使用JOIN查询，直接获取2级部门及其1级父部门信息
    var sql = "SELECT d2.id, d2.name, d2.code, d2.parentid, d1.name as parentName " +
              "FROM t_department d2 " +
              "INNER JOIN t_department d1 ON d2.parentid = d1.id " +
              "WHERE (d1.parentid IS NULL OR d1.parentid = '') " +
              "AND d2.domainid = '" + domainId + "' " +
              "AND d1.domainid = '" + domainId + "'";

    var level2Depts = queryBySQL(sql);

    var result = [];
    if(level2Depts != null && level2Depts.size() > 0){
        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            result.push({
                id: dept.getId(),
                name: dept.getItemValueAsString("name"),
                code: dept.getItemValueAsString("code"),
                parentId: dept.getItemValueAsString("parentid"),
                parentName: dept.getItemValueAsString("parentName")
            });
        }
    }

    return JSON.stringify(result);
})();

方式4：获取所有2级部门（简化版）

(function(){
    var domainId = getWebUser().getDomainid();

    // 直接查询parentid不为空，且parentid对应的部门是1级部门的记录
    var sql = "SELECT d2.id, d2.name, d2.code, d2.parentid " +
              "FROM t_department d2 " +
              "WHERE d2.parentid IN (SELECT id FROM t_department WHERE (parentid IS NULL OR parentid = '') AND domainid = '" + domainId + "') " +
              "AND d2.domainid = '" + domainId + "'";

    var level2Depts = queryBySQL(sql);

    var result = [];
    if(level2Depts != null && level2Depts.size() > 0){
        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            result.push({
                id: dept.getId(),
                name: dept.getItemValueAsString("name"),
                code: dept.getItemValueAsString("code"),
                parentId: dept.getItemValueAsString("parentid")
            });
        }
    }

    return JSON.stringify(result);
})();

扩展示例

示例1：获取所有2级部门并统计数量

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT d2.id, d2.name, d2.code, d2.parentid, d1.name as parentName " +
              "FROM t_department d2 " +
              "INNER JOIN t_department d1 ON d2.parentid = d1.id " +
              "WHERE (d1.parentid IS NULL OR d1.parentid = '') " +
              "AND d2.domainid = '" + domainId + "' " +
              "AND d1.domainid = '" + domainId + "'";

    var level2Depts = queryBySQL(sql);

    var result = {
        total: 0,
        departments: []
    };

    if(level2Depts != null && level2Depts.size() > 0){
        result.total = level2Depts.size();

        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            result.departments.push({
                id: dept.getId(),
                name: dept.getItemValueAsString("name"),
                code: dept.getItemValueAsString("code"),
                parentId: dept.getItemValueAsString("parentid"),
                parentName: dept.getItemValueAsString("parentName")
            });
        }
    }

    return JSON.stringify(result);
})();

示例2：按1级部门分组获取2级部门

(function(){
    var domainId = getWebUser().getDomainid();

    // 先获取所有1级部门
    var sql1 = "SELECT id, name FROM t_department WHERE (parentid IS NULL OR parentid = '') AND domainid = '" + domainId + "'";
    var level1Depts = queryBySQL(sql1);

    var result = {};

    if(level1Depts != null && level1Depts.size() > 0){
        for(var it = level1Depts.iterator(); it.hasNext();){
            var level1Dept = it.next();
            var level1Id = level1Dept.getId();
            var level1Name = level1Dept.getItemValueAsString("name");

            // 获取该1级部门下的所有2级部门
            var sql2 = "SELECT id, name, code FROM t_department WHERE parentid = '" + level1Id + "' AND domainid = '" + domainId + "'";
            var level2Depts = queryBySQL(sql2);

            var children = [];
            if(level2Depts != null && level2Depts.size() > 0){
                for(var it2 = level2Depts.iterator(); it2.hasNext();){
                    var level2Dept = it2.next();
                    children.push({
                        id: level2Dept.getId(),
                        name: level2Dept.getItemValueAsString("name"),
                        code: level2Dept.getItemValueAsString("code")
                    });
                }
            }

            result[level1Name] = children;
        }
    }

    return JSON.stringify(result);
})();

示例3：获取所有2级部门并格式化输出

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT d2.id, d2.name, d2.code, d2.parentid, d1.name as parentName " +
              "FROM t_department d2 " +
              "INNER JOIN t_department d1 ON d2.parentid = d1.id " +
              "WHERE (d1.parentid IS NULL OR d1.parentid = '') " +
              "AND d2.domainid = '" + domainId + "' " +
              "AND d1.domainid = '" + domainId + "'";

    var level2Depts = queryBySQL(sql);

    var html = "<table border='1'><tr><th>部门ID</th><th>部门名称</th><th>部门代码</th><th>上级部门</th></tr>";

    if(level2Depts != null && level2Depts.size() > 0){
        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            html += "<tr>";
            html += "<td>" + dept.getId() + "</td>";
            html += "<td>" + dept.getItemValueAsString("name") + "</td>";
            html += "<td>" + dept.getItemValueAsString("code") + "</td>";
            html += "<td>" + dept.getItemValueAsString("parentName") + "</td>";
            html += "</tr>";
        }
    }

    html += "</table>";
    return html;
})();

示例4：获取所有2级部门的ID列表

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT d2.id " +
              "FROM t_department d2 " +
              "INNER JOIN t_department d1 ON d2.parentid = d1.id " +
              "WHERE (d1.parentid IS NULL OR d1.parentid = '') " +
              "AND d2.domainid = '" + domainId + "' " +
              "AND d1.domainid = '" + domainId + "'";

    var level2Depts = queryBySQL(sql);

    var ids = [];
    if(level2Depts != null && level2Depts.size() > 0){
        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            ids.push(dept.getId());
        }
    }

    return ids.join(";");
})();

示例5：获取所有2级部门并包含用户数量

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT d2.id, d2.name, d2.code, d2.parentid, d1.name as parentName " +
              "FROM t_department d2 " +
              "INNER JOIN t_department d1 ON d2.parentid = d1.id " +
              "WHERE (d1.parentid IS NULL OR d1.parentid = '') " +
              "AND d2.domainid = '" + domainId + "' " +
              "AND d1.domainid = '" + domainId + "'";

    var level2Depts = queryBySQL(sql);

    var result = [];
    if(level2Depts != null && level2Depts.size() > 0){
        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            var deptId = dept.getId();

            // 统计该部门下的用户数量
            var userCountSql = "SELECT COUNT(*) as count FROM t_user WHERE departmentid = '" + deptId + "' AND domainid = '" + domainId + "'";
            var userCount = countBySQL(userCountSql);

            result.push({
                id: deptId,
                name: dept.getItemValueAsString("name"),
                code: dept.getItemValueAsString("code"),
                parentId: dept.getItemValueAsString("parentid"),
                parentName: dept.getItemValueAsString("parentName"),
                userCount: userCount
            });
        }
    }

    return JSON.stringify(result);
})();

示例6：判断部门是否为2级部门

(function(){
    var departmentId = "部门ID";
    var department = getDepartmentById(departmentId);

    if(department != null){
        var parentId = department.getParentid();

        if(parentId != null && parentId.trim().length > 0){
            var parentDept = getDepartmentById(parentId);
            if(parentDept != null){
                var grandParentId = parentDept.getParentid();
                // 如果父部门的父部门为空，则当前部门是2级部门
                if(grandParentId == null || grandParentId.trim().length == 0){
                    return true;
                }
            }
        }
    }

    return false;
})();

示例7：获取所有2级部门并排序

(function(){
    var domainId = getWebUser().getDomainid();
    var sql = "SELECT d2.id, d2.name, d2.code, d2.parentid, d1.name as parentName " +
              "FROM t_department d2 " +
              "INNER JOIN t_department d1 ON d2.parentid = d1.id " +
              "WHERE (d1.parentid IS NULL OR d1.parentid = '') " +
              "AND d2.domainid = '" + domainId + "' " +
              "AND d1.domainid = '" + domainId + "' " +
              "ORDER BY d1.name, d2.name";

    var level2Depts = queryBySQL(sql);

    var result = [];
    if(level2Depts != null && level2Depts.size() > 0){
        for(var it = level2Depts.iterator(); it.hasNext();){
            var dept = it.next();
            result.push({
                id: dept.getId(),
                name: dept.getItemValueAsString("name"),
                code: dept.getItemValueAsString("code"),
                parentId: dept.getItemValueAsString("parentid"),
                parentName: dept.getItemValueAsString("parentName")
            });
        }
    }

    return JSON.stringify(result);
})();

关键函数说明

• getWebUser().getDomainid(): 获取当前用户的域ID
• queryBySQL(sql): 执行SQL查询并返回多条记录
• getDepartmentById(departmentId): 根据部门ID获取部门对象
• department.getParentid(): 获取上级部门ID
• department.getName(): 获取部门名称
• department.getCode(): 获取部门代码
• department.getChildren(): 获取子部门列表（如果支持）
• countBySQL(sql): 执行SQL查询并返回计数

部门层级说明

在MyApps平台中： - 1级部门（根部门）: parentid为空或null的部门 - 2级部门: parentid为1级部门ID的部门 - 3级部门: parentid为2级部门ID的部门 - 以此类推...

注意事项

• ⚠️ 域ID: 查询时建议加上域ID条件，避免跨域数据
• ⚠️ 空值处理: 1级部门的parentid可能为空字符串或null，需要同时判断
• ⚠️ 数据一致性: 确保部门数据的parentid引用正确
• ⚠️ 性能优化: 如果部门数据量很大，注意SQL查询性能
• ⚠️ JOIN查询: 使用JOIN查询可以一次性获取2级部门及其1级父部门信息
• 2级部门是指直接隶属于1级部门的子部门
• 可以通过查询parentid在1级部门ID列表中的部门来获取所有2级部门
• 也可以使用JOIN查询，直接关联1级和2级部门表

使用场景

• 获取所有2级部门用于权限控制
• 部门层级统计和分析
• 组织架构展示
• 数据权限过滤
• 部门管理功能

---

如需查看完整脚本代码，请访问源文档链接。