getrolemanagedao.java

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

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

import java.sql.*;
import java.util.*;
import cn.com.iaspec.workflow.db.*;
import cn.com.iaspec.workflow.manage.*;
import cn.com.iaspec.workflow.organize.*;
import cn.com.iaspec.workflow.util.*;
import cn.com.iaspec.workflow.vo.workflow.*;
import cn.com.iaspec.workflow.sysmanage.tree.OrgUserTree;
import cn.com.iaspec.workflow.sysmanage.tree.RoleOrgUserTree;

/**
 * <p>Title: 角色维护</p>
 *
 * <p>Description: 深圳市劳动仲裁信息管理系统</p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: 永泰软件工程有限公司</p>
 *
 * @author syj
 * @version 1.0
 */
public class GetRoleManageDAO{
  private static final String DB_TYPE = WorkflowDBConnectionManager.getInstance().getDataBaseType();
  public GetRoleManageDAO(){

  }

  /**
   * 角色信息的查询
   * @return ArrayList
   * @throws RoleManageException
   */
  public ArrayList getRoleManageInfo()
      throws RoleManageException{
    String sql="select *  from wf_role where ROLE_STATE='1'";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    Collection coll=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      if(rs!=null){
        coll=RsToStringTool.getCollection(rs,3);
      }

    }
    catch(Exception e){
      throw new RoleManageException("------查询角色信息异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return new ArrayList(coll);
  }

  /**
  * 查询登陆用户管理权限范围内的角色信息
  * @return ArrayList
  * @throws RoleManageException
  */
 public ArrayList getRoleManageInfo(String userId)
     throws RoleManageException{
   String sql=null;

   if("oracle".equalsIgnoreCase(DB_TYPE)){
     sql = "select *  from wf_role where ROLE_STATE='1'"+
              "and (ROLE_ID  in (SELECT WF_ROLE_MANAGE_ROLE.MANAGED_ROLE_ID "+
              "                  FROM WF_ORG_USER_ROLE INNER JOIN WF_ROLE_MANAGE_ROLE "+
              "                  ON WF_ORG_USER_ROLE.ROLE_ID = WF_ROLE_MANAGE_ROLE.ROLE_ID "+
              "                  WHERE WF_ORG_USER_ROLE.USER_ID = '"+userId+"') or CREATOR='"+userId+"')";
   }else{
     sql = "select *  from wf_role where ROLE_STATE='1'"+
              "and (ROLE_ID  in (SELECT dbo.WF_ROLE_MANAGE_ROLE.MANAGED_ROLE_ID "+
              "                  FROM dbo.WF_ORG_USER_ROLE INNER JOIN dbo.WF_ROLE_MANAGE_ROLE "+
              "                  ON dbo.WF_ORG_USER_ROLE.ROLE_ID = dbo.WF_ROLE_MANAGE_ROLE.ROLE_ID "+
              "                  WHERE dbo.WF_ORG_USER_ROLE.USER_ID = '"+userId+"') or CREATOR='"+userId+"')";
   }

   PreparedStatement prep=null;
   Connection conn=null;
   ResultSet rs=null;
   Collection coll=null;
   try{
     conn=WorkflowDBConnectionManager.getInstance().getConnection();
     prep=conn.prepareStatement(sql);
     rs=prep.executeQuery();
     if(rs!=null){
       coll=RsToStringTool.getCollection(rs,3);
     }

   }
   catch(Exception e){
     throw new RoleManageException("------查询角色信息异常-----"+e.getMessage());
   }
   finally{
     WorkflowDBConnectionManager.getInstance().close(rs);
     WorkflowDBConnectionManager.getInstance().close(prep,false);
     WorkflowDBConnectionManager.getInstance().close(conn);
   }

   return new ArrayList(coll);
 }

 /**
* 查询所有管理角色信息
* @return ArrayList
* @throws RoleManageException
*/
public ArrayList getAllManageRoleList()
   throws RoleManageException{
 String sql=null;
 if("oracle".equalsIgnoreCase(DB_TYPE)){
   sql="select wf_role.*,FUNC_QRY_ROLE_HAS_MANAGED_ORG(ROLE_ID) AS HAS_CHILDREN from wf_role where ROLE_STATE='1' AND ROLE_TYPE='5' ";
 }else{
   sql="select wf_role.*,dbo.FUNC_QRY_ROLE_HAS_MANAGED_ORG(ROLE_ID) AS HAS_CHILDREN from wf_role where ROLE_STATE='1' AND ROLE_TYPE='5' ";
 }

 PreparedStatement prep=null;
 Connection conn=null;
 ResultSet rs=null;
 ArrayList list=new ArrayList();
 try{
   conn=WorkflowDBConnectionManager.getInstance().getConnection();
   prep=conn.prepareStatement(sql);
   rs=prep.executeQuery();
   while(rs.next()){
     RoleOrgUserTree tree=new RoleOrgUserTree();
     tree.setId(rs.getString("ROLE_ID"));
     tree.setName(rs.getString("ROLE_NAME"));
     tree.setParentId("0");
     tree.setType("role");
     if(rs.getString("HAS_CHILDREN")!=null&&rs.getString("HAS_CHILDREN").equals("1")){
          tree.setHasChildren(true);
     }
     tree.setOrder(rs.getLong("ROLE_ORDER"));
     tree.setManageRoleId(rs.getString("ROLE_ID"));
     list.add(tree);
   }
 }
 catch(Exception e){
   throw new RoleManageException("------查询角色信息异常-----"+e.getMessage());
 }
 finally{
   WorkflowDBConnectionManager.getInstance().close(rs);
   WorkflowDBConnectionManager.getInstance().close(prep,false);
   WorkflowDBConnectionManager.getInstance().close(conn);
 }
 return list;
}


 /**
 * 查询登陆用户管理权限范围内的管理角色信息
 * @return ArrayList
 * @throws RoleManageException
 */
public ArrayList getManageRoleList(String userId)
    throws RoleManageException{
  String sql=null;
  if("oracle".equalsIgnoreCase(DB_TYPE)){
    sql="select wf_role.*,FUNC_QRY_ROLE_HAS_MANAGED_ORG(ROLE_ID) AS HAS_CHILDREN from wf_role where ROLE_STATE='1' AND ROLE_TYPE='5' "+
             " and (ROLE_ID  in (SELECT WF_ROLE_MANAGE_ROLE.MANAGED_ROLE_ID "+
             "                  FROM WF_ORG_USER_ROLE INNER JOIN WF_ROLE_MANAGE_ROLE "+
             "                  ON WF_ORG_USER_ROLE.ROLE_ID = WF_ROLE_MANAGE_ROLE.ROLE_ID "+
             "                  WHERE WF_ORG_USER_ROLE.USER_ID = '"+userId+"') or CREATOR='"+userId+"')";
  }else{
    sql="select wf_role.*,dbo.FUNC_QRY_ROLE_HAS_MANAGED_ORG(ROLE_ID) AS HAS_CHILDREN from wf_role where ROLE_STATE='1' AND ROLE_TYPE='5' "+
             " and (ROLE_ID  in (SELECT dbo.WF_ROLE_MANAGE_ROLE.MANAGED_ROLE_ID "+
             "                  FROM dbo.WF_ORG_USER_ROLE INNER JOIN dbo.WF_ROLE_MANAGE_ROLE "+
             "                  ON dbo.WF_ORG_USER_ROLE.ROLE_ID = dbo.WF_ROLE_MANAGE_ROLE.ROLE_ID "+
             "                  WHERE dbo.WF_ORG_USER_ROLE.USER_ID = '"+userId+"') or CREATOR='"+userId+"')";
  }

  PreparedStatement prep=null;
  Connection conn=null;
  ResultSet rs=null;
  ArrayList list=new ArrayList();
  try{
    conn=WorkflowDBConnectionManager.getInstance().getConnection();
    prep=conn.prepareStatement(sql);
    rs=prep.executeQuery();
    while(rs.next()){
      RoleOrgUserTree tree=new RoleOrgUserTree();
      tree.setId(rs.getString("ROLE_ID"));
      tree.setName(rs.getString("ROLE_NAME"));
      tree.setParentId("0");
      tree.setType("role");
      if(rs.getString("HAS_CHILDREN")!=null&&rs.getString("HAS_CHILDREN").equals("1")){
           tree.setHasChildren(true);
      }
      tree.setOrder(rs.getLong("ROLE_ORDER"));
      tree.setManageRoleId(rs.getString("ROLE_ID"));
      list.add(tree);
    }
  }
  catch(Exception e){
    throw new RoleManageException("------查询角色信息异常-----"+e.getMessage());
  }
  finally{
    WorkflowDBConnectionManager.getInstance().close(rs);
    WorkflowDBConnectionManager.getInstance().close(prep,false);
    WorkflowDBConnectionManager.getInstance().close(conn);
  }
  return list;
 }

 /**
* 获取登陆用户对管理权限范围内的指定部门的操作权限集合
* @param String orgId,String userId
* @throws SQLException
* @return Hashtable
*/
public Hashtable getManagedRoleOperationAuthorization(String managedRoleId,String userId)
   throws SQLException{
 Connection conn=null;
 Statement stm=null;
 ResultSet rs=null;
 Hashtable operationAuthorizationHashtable = new Hashtable();
 try{
   conn=WorkflowDBConnectionManager.getInstance().getConnection();
   stm=conn.createStatement();
   String sql=null;
   if("oracle".equalsIgnoreCase(DB_TYPE)){
     sql = "SELECT WF_MANAGED_OPERATION_AUTH.OPERATION_ID, "+
              " WF_MANAGED_OPERATION.OPERATION_NAME"+
              " FROM WF_MANAGED_OPERATION_AUTH INNER JOIN"+
              " WF_MANAGED_OPERATION ON "+
              " WF_MANAGED_OPERATION_AUTH.OPERATION_ID = WF_MANAGED_OPERATION.OPERATION_ID"+
              " WHERE (WF_MANAGED_OPERATION_AUTH.OPERATION_VALUE = '1') AND "+
              " (WF_MANAGED_OPERATION_AUTH.ROLE_MANAGED_ID IN"+
              " (SELECT WF_ROLE_MANAGE_ROLE.ROLE_MANAGEDROLE_ID"+
              "  FROM WF_ORG_USER_ROLE INNER JOIN"+
              "  WF_ROLE_MANAGE_ROLE ON "+
              "  WF_ORG_USER_ROLE.ROLE_ID = WF_ROLE_MANAGE_ROLE.ROLE_ID"+
              " WHERE WF_ORG_USER_ROLE.USER_ID = '"+userId+"' AND "+
              "  WF_ROLE_MANAGE_ROLE.MANAGED_ROLE_ID = '"+managedRoleId+"'))";

   }else{
     sql = "SELECT WF_MANAGED_OPERATION_AUTH.OPERATION_ID, "+
              " WF_MANAGED_OPERATION.OPERATION_NAME"+
              " FROM WF_MANAGED_OPERATION_AUTH INNER JOIN"+
              " WF_MANAGED_OPERATION ON "+
              " WF_MANAGED_OPERATION_AUTH.OPERATION_ID = WF_MANAGED_OPERATION.OPERATION_ID"+
              " WHERE (WF_MANAGED_OPERATION_AUTH.OPERATION_VALUE = '1') AND "+
              " (WF_MANAGED_OPERATION_AUTH.ROLE_MANAGED_ID IN"+
              " (SELECT dbo.WF_ROLE_MANAGE_ROLE.ROLE_MANAGEDROLE_ID"+
              "  FROM dbo.WF_ORG_USER_ROLE INNER JOIN"+
              "  dbo.WF_ROLE_MANAGE_ROLE ON "+
              "  dbo.WF_ORG_USER_ROLE.ROLE_ID = dbo.WF_ROLE_MANAGE_ROLE.ROLE_ID"+
              " WHERE dbo.WF_ORG_USER_ROLE.USER_ID = '"+userId+"' AND "+
              "  dbo.WF_ROLE_MANAGE_ROLE.MANAGED_ROLE_ID = '"+managedRoleId+"'))";
   }


   rs=stm.executeQuery(sql);
   while(rs.next()){
      operationAuthorizationHashtable.put(rs.getString("OPERATION_NAME"),rs.getString("OPERATION_ID"));
   }
   return operationAuthorizationHashtable;
 }
 finally{
   WorkflowDBConnectionManager.getInstance().close(rs);
   WorkflowDBConnectionManager.getInstance().close(stm,false);
   WorkflowDBConnectionManager.getInstance().close(conn);
 }
}

  /**
  * 判断指定角色是否是登陆用户创建
  * @param String userId,String OrgId
  * @throws SQLException
  * @return boolean
  */
  public boolean isLoginUserCreateRole(String userId,String roleId)
  throws SQLException{
  Connection conn=null;
  Statement stm=null;
  ResultSet rs=null;
  boolean isLoginUserCreateRole = false;
  try{
    conn=WorkflowDBConnectionManager.getInstance().getConnection();
    stm=conn.createStatement();
    String sql=" SELECT * FROM WF_ROLE WHERE ROLE_ID = '"+roleId+"' AND CREATOR = '"+userId+"'";
    rs=stm.executeQuery(sql);
    if(rs.next()){
       isLoginUserCreateRole = true;
    }
    return isLoginUserCreateRole;
  }
  finally{
    WorkflowDBConnectionManager.getInstance().close(rs);
    WorkflowDBConnectionManager.getInstance().close(stm,false);
    WorkflowDBConnectionManager.getInstance().close(conn);
  }
}
  /**
   * 根据角色id查询信息
   * @param role_id String
   * @return ArrayList
   * @throws RoleManageException
   */
  public RoleInfo getRoleInfo(String role_id)
      throws RoleManageException{
    String sql="select *  from wf_role where role_id=?";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    RoleInfo roleInfo=new RoleInfo();
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,role_id);
      rs=prep.executeQuery();
      while(rs.next()){
        roleInfo.setRole_id(rs.getString("role_id"));
        roleInfo.setRole_name(rs.getString("role_name"));
        roleInfo.setRole_type(rs.getString("role_type"));
        roleInfo.setRole_desc(rs.getString("role_desc"));
        roleInfo.setDel_permit(rs.getString("del_permit"));

      }

    }
    catch(Exception e){
      throw new RoleManageException("------查询角色信息异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return roleInfo;
  }

  /**
   * 查寻角色下面所有的人员
   * @return ArrayList
   * @throws RoleManageException
   */
  public ArrayList getUserInfo(String area_id)
      throws RoleManageException{
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    String sql="";
    if(dataBaseType.equals("ORACLE")){
      sql="select m.role_id,m.user_id,f.user_name,f.login_id"+
          " from wf_org_user_role m,wf_user f where f.user_id(+)=m.user_id "+
          " and m.role_id>0 and m.user_id in(select distinct t.user_id from "+
          " wf_org_user_role t,wf_organization k where k.org_id(+)=t.org_id and "+
          " k.area_id=? and t.user_id in(select distinct  user_id from "+
          " wf_org_user_role  where role_id in(select a.role_id from wf_role a )))";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql="select m.role_id,m.user_id,f.user_name,f.login_id"+
          " from wf_org_user_role m left outer join wf_user f on f.user_id=m.user_id where  "+
          " m.role_id>0 and m.user_id in(select distinct t.user_id from "+
          " wf_org_user_role t left join wf_organization k on k.org_id=t.org_id where "+
          " k.area_id=? and t.user_id in(select distinct  user_id from "+
          " wf_org_user_role  where role_id in(select a.role_id from wf_role a )))";
    }

    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    Collection coll=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,area_id);
      rs=prep.executeQuery();
      if(rs!=null){
        coll=RsToStringTool.getCollection(rs,4);
      }
    }
    catch(Exception e){
      e.printStackTrace();
      throw new RoleManageException("------查询角色下面所有的人员信息异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);