managingroledao.java

公司自己开发的工作流引擎

package cn.com.iaspec.workflow.sysmanage.dao;

import java.util.List;
import java.util.ArrayList;
import cn.com.iaspec.workflow.db.WorkflowDBConnectionManager;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;
import cn.com.iaspec.workflow.client.web.formbean.RoleNodeFormbean;
import org.apache.log4j.Logger;
import java.util.Map;
import java.util.HashMap;
import java.util.Set;
import java.util.StringTokenizer;
import java.util.HashSet;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: IASPEC Technologies</p>
 *
 * @author leigm
 * @version 1.0
 */

public class ManagingRoleDAO{
  private Connection conn=null;
  private PreparedStatement ptmt=null;
  private ResultSet rowset=null;
  private final String DB_TYPE = WorkflowDBConnectionManager.getInstance().getDataBaseType();
  private static transient Logger logger=Logger.getLogger(ManagingRoleDAO.class);

  /**
   * 获取所有的管理角色
   * @return List
   */
  public List getAllManagingRoles(){
    List roleList=new ArrayList();
    RoleNodeFormbean roleForm=null;
    String sql = null;
    if("ORACLE".equalsIgnoreCase(DB_TYPE)){
      sql = "select a.*,func_qry_role_has_managed_ROLE(role_id) as has_children "+
        " from wf_role a where role_type = 5 and role_state = 1";
    }else{
      sql = "select a.*,dbo.func_qry_role_has_managed_ROLE(role_id) as has_children "+
        " from wf_role a where role_type = 5 and role_state = 1";
    }

    try{

      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      ptmt=conn.prepareStatement(sql);
      rowset=ptmt.executeQuery();

      while(rowset.next()){
        roleForm=new RoleNodeFormbean();

        roleForm.setRoleId(rowset.getString("role_id"));
        roleForm.setRoleName(rowset.getString("role_name"));
        roleForm.setHasChildren(rowset.getString("has_children"));
        roleForm.setRoleType("0"); //该管理角色下所有的子角色是从根（0）结点开始
        roleForm.setManagingRoleId(rowset.getString("role_id"));

        roleList.add(roleForm);
      }
    }
    catch(SQLException sqle){

      sqle.printStackTrace();
    }
    finally{

      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return roleList;
  }

  /**
   * 根据用户ID（该用户所属的管理角色），获取所有管辖范围下的管理角色
   *
   * @param usrId String 用户ID
   * @return List
   */
  public List getManagingRolesByUsrId(String userId){
    List roleList=new ArrayList();
    RoleNodeFormbean roleForm=null;
    String sql= null;

    if("Oracle".equalsIgnoreCase(DB_TYPE)){
      sql = "select wf_role.*,FUNC_QUERY_ROLE_HAS_CHILDREN(0) as has_children "+
        "from wf_role where role_state = 1 and role_type = 5 and ( "+
        "role_id in (select managed_role_id from wf_role_manage_role,wf_org_user_role " +
        "where wf_role_manage_role.role_id = wf_org_user_role.role_id and "+
        "wf_org_user_role.user_id = ?) or creator = ?)";
    }else{
      sql = "select wf_role.*,dbo.FUNC_QUERY_ROLE_HAS_CHILDREN(0) as has_children "+
        "from wf_role where role_state = 1 and role_type = 5 and ( "+
        "role_id in (select managed_role_id from wf_role_manage_role inner join " +
        "wf_org_user_role on wf_role_manage_role.role_id = wf_org_user_role.role_id and "+
        "wf_org_user_role.user_id = ?) or creator = ?)";

    }

    try{

      conn=WorkflowDBConnectionManager.getInstance().getConnection();

      ptmt=conn.prepareStatement(sql);
      ptmt.setString(1,userId);
      ptmt.setString(2,userId);

      rowset=ptmt.executeQuery();
      while(rowset.next()){
        roleForm=new RoleNodeFormbean();

        roleForm.setRoleId(rowset.getString("role_id"));
        roleForm.setRoleName(rowset.getString("role_name"));
        roleForm.setHasChildren(rowset.getString("has_children"));
        roleForm.setRoleType(rowset.getString("role_type"));
        roleForm.setManagingRoleId(rowset.getString("role_id"));

        roleList.add(roleForm);
      }
    }
    catch(SQLException sqle){

      sqle.printStackTrace();
    }
    finally{

      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return roleList;
  }

  /**
   * 根据管理角色所管理的角色范围和要查询的角色的类型，查询普通角色
   *
   * @param roleType String 角色类型
   * @param managingRoleId String 管理角色ID
   * @return List
   */
  public List getManagedRoles(String roleType,String managingRoleId,String userId){
    List roleList=new ArrayList();
    RoleNodeFormbean roleForm=null;
    String sql= null;

    if("Oracle".equalsIgnoreCase(DB_TYPE)){
      sql = "select wf_role.*,FUNC_QUERY_ROLE_HAS_CHILDREN(role_id) as has_children "+
        "from wf_role where role_state = 1 and role_type = ? and "+
        "role_id in (select managed_role_id from wf_role_manage_role "+
        "where role_id = ?)";
    }else{
      sql = "select wf_role.*,dbo.FUNC_QUERY_ROLE_HAS_CHILDREN(role_id) as has_children "+
        "from wf_role where role_state = 1 and role_type = ? and "+
        "role_id in (select managed_role_id from wf_role_manage_role "+
        "where role_id = ?)";
    }

    System.out.println("query role sql : " + sql);

    try{

      conn=WorkflowDBConnectionManager.getInstance().getConnection();

      ptmt=conn.prepareStatement(sql);
      ptmt.setString(1,roleType);
      ptmt.setString(2,managingRoleId);

      rowset=ptmt.executeQuery();

      while(rowset.next()){
        roleForm=new RoleNodeFormbean();

        roleForm.setRoleId(rowset.getString("role_id"));
        roleForm.setRoleName(rowset.getString("role_name"));
        roleForm.setHasChildren(rowset.getString("has_children"));
        roleForm.setRoleType(rowset.getString("role_type"));
        roleForm.setManagingRoleId(managingRoleId);

        roleList.add(roleForm);
      }
    }
    catch(SQLException sqle){

      sqle.printStackTrace();
    }
    finally{

      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return roleList;
  }

  /**
   * 查询角色的详细信息
   *
   * @param roleId String  角色ID
   * @return RoleNodeFormbean
   * @throws Exception
   */
  public RoleNodeFormbean getRoleInfo(String roleId){
    RoleNodeFormbean roleForm=new RoleNodeFormbean();
    String sql="select * from wf_role where role_id = ?";

    try{

      conn=WorkflowDBConnectionManager.getInstance().getConnection();

      ptmt=conn.prepareStatement(sql);
      ptmt.setString(1,roleId);
      rowset=ptmt.executeQuery();

      if(rowset.next()){
        roleForm.setRoleId(rowset.getString("role_id"));
        roleForm.setRoleName(rowset.getString("role_name"));
        roleForm.setRoleCode(rowset.getString("role_code"));
        roleForm.setRoleDesc(rowset.getString("role_desc"));
        roleForm.setRoleType(rowset.getString("role_type"));
        roleForm.setOrder(rowset.getString("role_order"));
      }
    }
    catch(SQLException sqle){
      logger.debug("\n=========读取角色信息失败(id = "+roleId+" )==========");
      sqle.printStackTrace();
    }
    finally{

      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return roleForm;
  }

  /**
   * 查询附属在某管理角色下的普通角色的操作权限
   *
   * @param roleId String  普通角色ID
   * @param managingRoleId String  管理角色ID
   * @return Map
   * @throws Exception
   */
  public Map getRoleGrantedOprMap(String roleId,String managingRoleId)
      throws Exception{
    Map grantedAuthMap=new HashMap();
    String sql= "select a.operation_id,a.operation_name,b.operation_value "+
        "from wf_managed_operation a, "+
        "(select * from wf_managed_operation_auth c, "+
        "        (select role_managedRole_id from wf_role_manage_role "+
        "                where role_id = ? and managed_role_id = ?) d "+
        "        where c.role_managed_id = d.role_managedRole_id) b "+
        "where a.operation_id = b.operation_id";

    try{

      conn=WorkflowDBConnectionManager.getInstance().getConnection();

      ptmt=conn.prepareStatement(sql);
      ptmt.setString(1,managingRoleId);
      ptmt.setString(2,roleId);
      rowset=ptmt.executeQuery();

      while(rowset.next()){
        grantedAuthMap.put(rowset.getString("operation_name"),
            rowset.getString("operation_value"));
      }
    }
    catch(SQLException sqle){
      logger.debug("\n=========读取角色操作授权信息失败(roleId = "+roleId+
          "\tmanagingRoleId = "+managingRoleId+")==========");

      sqle.printStackTrace();
      throw sqle;
    }
    finally{

      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return grantedAuthMap;
  }

  /**
   * 删除某管理角色下普通角色的操作权限
   *
   * @param roleId String  普通角色ID
   * @param managingRoleId String  管理角色ID
   */
  public void delRoleGrantedOperation(String roleId,String managingRoleId)
      throws SQLException{
    String sql=  "delete from wf_managed_operation_auth "+
        "where role_managed_id in (select role_managedRole_id "+
        "from wf_role_manage_role where role_id = ? and managed_role_id = ?)";

    try{

      conn=WorkflowDBConnectionManager.getInstance().getConnection();

      ptmt=conn.prepareStatement(sql);
      ptmt.setString(1,managingRoleId);
      ptmt.setString(2,roleId);
      ptmt.execute();
    }
    catch(SQLException sqle){
      logger.debug("\n=========删除角色的操作授权失败(roleId = "+roleId+
          "\tmanagingRoleId = "+managingRoleId+")==========");

      sqle.printStackTrace();
      throw sqle;
    }
    finally{

      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 为某管理角色管辖下的普通角色添加操作权限
   *
   * @param roleId String  普通角色ID
   * @param managingRoleId String  管理角色ID