getrolemanagedao.java

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

      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return new ArrayList(coll);
  }

  /**
  * 查询登陆用户管理权限范围内的角色下面所有的人员
  * @return ArrayList
  * @throws RoleManageException
  */
 public ArrayList getUserInfo(String area_id,String userId)
     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 )))"+
         "and (m.user_id in(SELECT dbo.WF_ROLE_MANAGE_USER.USER_ID "+
         "                  FROM dbo.WF_ORG_USER_ROLE INNER JOIN dbo.WF_ROLE_MANAGE_USER "+
         "                  ON dbo.WF_ORG_USER_ROLE.ROLE_ID = dbo.WF_ROLE_MANAGE_USER.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);
     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);
     WorkflowDBConnectionManager.getInstance().close(prep,false);
     WorkflowDBConnectionManager.getInstance().close(conn);
   }
   return new ArrayList(coll);
 }


  /**
   * 添加角色
   * @param roleinfo RoleInfo
   * @return int
   * @throws RoleManageException
   */
  public int insertRole(RoleInfo roleinfo)
      throws RoleManageException{
    int i=0;
    PreparedStatement prep=null;
    Connection conn=null;
    String sql="insert into wf_role(role_id,role_name,role_desc,del_permit,role_type) values(?,?,?,?,?)";
    try{
      if(!chickRole(roleinfo.getRole_id())){
        String role_id=GetTableSequenceId.getTableId("wf_role");
        conn=WorkflowDBConnectionManager.getInstance().getConnection();
        prep=conn.prepareStatement(sql);
        prep.setString(1,role_id);
        prep.setString(2,roleinfo.getRole_name());
        prep.setString(3,roleinfo.getRole_desc());
        prep.setString(4,roleinfo.getDel_permit());
        prep.setString(5,roleinfo.getRole_type());
        i=prep.executeUpdate();
      }
    }
    catch(Exception e){
      throw new RoleManageException("------添加角色信息异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;
  }

  /**
   * 更新角色信息
   * @param roleinfo RoleInfo
   * @return int
   */
  public int updateRoleInfo(RoleInfo roleinfo)
      throws RoleManageException{
    int i=0;
    PreparedStatement prep=null;
    Connection conn=null;
    String sql="update wf_role set role_name=?,role_desc=?,del_permit=?,role_type=?  where role_id=?";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,roleinfo.getRole_name());
      prep.setString(2,roleinfo.getRole_desc());
      prep.setString(3,roleinfo.getDel_permit());
      prep.setString(4,roleinfo.getRole_type());
      prep.setString(5,roleinfo.getRole_id());
      i=prep.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
      throw new RoleManageException("------更新角色信息异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;

  }

  /**
   * 判断角色是否存在
   * @param role_id String
   * @return boolean
   * @throws RoleManageException
   */
  public boolean chickRole(String role_id)
      throws RoleManageException{
    String sql="select count(*) from wf_role where role_id=?";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    boolean flag=false;
    int i=0;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,role_id);
      rs=prep.executeQuery();
      while(rs.next()){
        i=rs.getInt(1);
      }
    }
    catch(Exception e){
      throw new RoleManageException("------判断角色是否存在异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    if(i>0){
      flag=true;
    }
    return flag;

  }

  /**
   * 删除角色信息
   * @param role_id String
   * @return int
   * @throws RoleManageException
   */
  public int deleteRole(String role_id)
      throws RoleManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    //String sql="delete wf_role where role_id=?";
    String sql="{?=call f_rolemanage(?)}";
    CallableStatement callStm=null;
    String pId="0";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      callStm=conn.prepareCall(sql);
      callStm.registerOutParameter(1,java.sql.Types.INTEGER);
      callStm.setString(2,role_id);
      callStm.executeUpdate();
      pId=callStm.getString(1);
      //System.out.println("tempId=="+pId);
    }
    catch(Exception e){
      throw new RoleManageException("------删除角色是异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return Integer.parseInt(pId);

  }

  /**
   * 更改和角色有关联的用户关联信息
   * @param role_id String
   * @return int
   */
  public int deletewf_role_user(String role_id)
      throws RoleManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    int i=0;
    String sql="update wf_org_user_role set role_id='0'  where role_id=?";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,role_id);
      i=prep.executeUpdate();

    }
    catch(SQLException e){
      throw new RoleManageException("--删除角色关联信息出错--");
    }
    return i;

  }

  /**
   * 修改角色和用户有关联的信息
   * @param role_id String
   * @return int
   */
  public int updatewf_role_user(String user_id,String role_id,String role_value)
      throws RoleManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    int i=0;
    String sql=
        "update wf_org_user_role set role_id=? where user_id=? and role_id=?";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,role_value);
      prep.setString(2,user_id);
      prep.setString(3,role_id);
      i=prep.executeUpdate();

    }
    catch(SQLException e){
      throw new RoleManageException("--删除用户角色关联信息异常--");
    }
    return i;

  }

  /**
   * 更新角色关联的信息数据
   * @param role_id String
   * @param role_value String
   * @return int
   * @throws RoleManageException
   */
  public int updatewf_role_user(String role_id,String role_value)
      throws RoleManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    String sql="update wf_org_user_role set role_id=? where role_id=?";
    int i=0;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,role_value);
      prep.setString(2,role_id);
      i=prep.executeUpdate();
    }
    catch(Exception e){
      throw new RoleManageException("------删除角色关联信息--异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;

  }

  /**
   * 添加角色和用户信息
   * @param role_id String
   * @param user_id String
   * @return int
   * @throws RoleManageException
   */
  public int addwf_role_user(Connection conn,String role_id,String user_id,
      String org_id)
      throws RoleManageException{
    int i=0;
    PreparedStatement prep=null;
    String sql=
        "insert into wf_org_user_role(user_id,role_id,org_id ) values(?,?,?)";
    try{
      i=updateRole_user(conn,role_id,user_id,org_id);
      if(i==0){
        prep=conn.prepareStatement(sql);
        prep.setString(1,user_id);
        prep.setString(2,role_id);
        prep.setString(3,org_id);
        i=prep.executeUpdate();
      }
    }
    catch(SQLException e){
      throw new RoleManageException("------添加用户角色关联信息--异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
    }

    return i;
  }

  /**
   *判断用户是否在角色下
   * @param conn Connection
   * @param role_id String
   * @param user_id String
   * @return boolean
   * @throws RoleManageException
   */
  public int updateRole_user(Connection conn,String role_id,String user_id,
      String org_id)
      throws RoleManageException{
    String sql="update wf_org_user_role set role_id=? where org_id=? and user_id=? and role_id='0'";
    PreparedStatement prep=null;
    ResultSet rs=null;
    int i=0;
    try{
      prep=conn.prepareStatement(sql);
      prep.setString(1,role_id);
      prep.setString(2,org_id);
      prep.setString(3,user_id);
      i=prep.executeUpdate();
    }
    catch(Exception e){
      throw new RoleManageException("------判断角色是否存在异常-----"+e.getMessage());
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
    }
    return i;
  }

  /**
   * 判断角色是否同名角色
   * @param roleinfo RoleInfo
   * @return int
   * @throws RoleManageException
   */
  public int check_role(RoleInfo roleinfo)
      throws RoleManageException{
    int i=0;
    String sql="select * from wf_role where role_name='"+roleinfo.getRole_name()+
        "'";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;

    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      while(rs.next()){
        i=2;
      }
    }
    catch(Exception e){
      throw new RoleManageException("------判断角色是否存在异常-----"+e.getMessage());
    }
    return i;
  }

}