wfuserdao.java

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

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

import java.sql.*;
import java.util.*;
import org.apache.log4j.*;
import cn.com.iaspec.workflow.*;
import cn.com.iaspec.workflow.db.*;
import cn.com.iaspec.workflow.vo.db.*;
import cn.com.iaspec.workflow.vo.workflow.WfCheckExistsResult;

/**
 *
 * <p>Title: 用DAO</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: IASPEC Technologies</p>
 * @author xiesonglin
 * @version 1.0
 */
public class WfUserDAO{
  private static Logger logger=Logger.getLogger(WfUserDAO.class);
  private static final String DB_TYPE = WorkflowDBConnectionManager.getInstance().getDataBaseType();
  Connection conn = null;
  PreparedStatement ptmt = null;
  ResultSet rowset = null;

  public WfUserDAO(){

  }

  /**
   * 根据sWhereSQL条件进行查询
   * @param sWhereSQL where条件查询语句
   * @return List
   * @throws SQLException
   */
  public List queryWhere(String sWhereSQL)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      if (sWhereSQL==null)
        sWhereSQL="";
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_user ";
      sql=sql+sWhereSQL;
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 查询用户信息
   * @param user WfUser
   * @return List
   * @throws SQLException
   */
  public List query(WfUser user)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_user ";
      StringBuffer sbWhere=getQueryString(user);
      sql=sql+sbWhere.toString();
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
* 获取登陆用户对管理权限范围内的指定用户的操作权限集合
* @param String orgId,String userId
* @throws SQLException
* @return Hashtable
*/
public Hashtable getManagedUserOperationAuthorization(String managedUserId,String userId)
  throws SQLException{
Connection conn=null;
Statement stm=null;
ResultSet rs=null;
Hashtable managedUserOperationAuthorizationHashtable = 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 OPERATION_VALUE = '1' and ROLE_MANAGED_ID in                                                 "+
             "                  (SELECT WF_ROLE_MANAGE_USER.ROLE_USER_ID                                      "+
             "                  FROM WF_ORG_USER_ROLE INNER JOIN WF_ROLE_MANAGE_USER                      "+
             "                  ON WF_ORG_USER_ROLE.ROLE_ID = WF_ROLE_MANAGE_USER.ROLE_ID                 "+
             "                  WHERE WF_ORG_USER_ROLE.USER_ID = '"+userId+"' and  WF_ROLE_MANAGE_USER.USER_ID='"+managedUserId+"')";
  }else{
    sql = " SELECT dbo.WF_MANAGED_OPERATION_AUTH.OPERATION_ID,dbo.WF_MANAGED_OPERATION.OPERATION_NAME "+
             " FROM WF_MANAGED_OPERATION_AUTH INNER JOIN WF_MANAGED_OPERATION                            "+
             " on dbo.WF_MANAGED_OPERATION_AUTH.OPERATION_ID=dbo.WF_MANAGED_OPERATION.OPERATION_ID       "+
             " WHERE OPERATION_VALUE = '1' and ROLE_MANAGED_ID in                                                 "+
             "                  (SELECT dbo.WF_ROLE_MANAGE_USER.ROLE_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+"' and  dbo.WF_ROLE_MANAGE_USER.USER_ID='"+managedUserId+"')";
  }

  logger.info("......query sql:"+sql);
  rs=stm.executeQuery(sql);
  while(rs.next()){
     managedUserOperationAuthorizationHashtable.put(rs.getString("OPERATION_NAME"),rs.getString("OPERATION_ID"));
  }
  return managedUserOperationAuthorizationHashtable;
}
finally{
  WorkflowDBConnectionManager.getInstance().close(rs);
  WorkflowDBConnectionManager.getInstance().close(stm,false);
  WorkflowDBConnectionManager.getInstance().close(conn);
}
}

  /**
   * 取出指定部门中的人员
   * @param orgId String
   * @return List
   * @throws SQLException
   */
  public List query(String orgId)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_user ";
      StringBuffer sbWhere=new StringBuffer();
      sbWhere.append(" where user_id in ( select distinct user_id from "+
          " wf_org_user_role where user_state='1' and role_id='0' and org_id='"+orgId+"') ");
      sql=sql+sbWhere.toString();
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
 * 取出指定部门中的登陆用户管理范围内的人员
 * @param orgId String
 * @return List
 * @throws SQLException
 */
public List query(String orgId,String userId)
    throws SQLException{
  Connection conn=null;
  Statement stm=null;
  try{
    conn=WorkflowDBConnectionManager.getInstance().getConnection();
    stm=conn.createStatement();
    String sql=" select * from wf_user ";
    StringBuffer sbWhere=new StringBuffer();
//    sbWhere.append(" where user_id in ( select distinct user_id from "+
//        " wf_org_user_role where user_state='1' and role_id='0' and org_id='"+orgId+"') ");
    sbWhere.append(" where user_id in ( select distinct user_id from "+
        " wf_org_user_role where user_state='1' and role_id='0' and org_id='"+orgId+"') "+
        " and (user_id in(SELECT WF_ROLE_MANAGE_USER.USER_ID "+
        " FROM WF_ORG_USER_ROLE INNER JOIN WF_ROLE_MANAGE_USER "+
        " ON WF_ORG_USER_ROLE.ROLE_ID = WF_ROLE_MANAGE_USER.ROLE_ID " +
        " WHERE WF_ORG_USER_ROLE.USER_ID = '"+userId+"')  or CREATOR='"+userId+"')");

    sql=sql+sbWhere.toString();
    logger.info("......query sql:"+sql);
    ResultSet rs=stm.executeQuery(sql);
    List list=resultsetToVO(rs);
    return list;
  }
  finally{
    WorkflowDBConnectionManager.getInstance().close(stm,false);
    WorkflowDBConnectionManager.getInstance().close(conn);
  }
}

/**
* 查询指定部门、指定管理角色的管理权限范围内的用户信息
* @param orgId String
* @return List
* @throws SQLException
*/
public List queryUserByOrgManageRole(String orgId,String roleId,String userId)
  throws SQLException{
Connection conn=null;
Statement stm=null;
try{
  conn=WorkflowDBConnectionManager.getInstance().getConnection();
  stm=conn.createStatement();
  String sql= null;

  if("oracle".equalsIgnoreCase(DB_TYPE)){
    sql = "select * from wf_user a "+
             " where user_id in ( "+
             " select distinct user_id"+
             " from  wf_org_user_role where user_state='1' and role_id='0' and org_id='"+orgId+"')"+
             " and (user_id in(SELECT WF_ROLE_MANAGE_USER.USER_ID"+
             " FROM WF_ROLE_MANAGE_USER ,wf_org_user_role "+
             " WHERE WF_ROLE_MANAGE_USER.ROLE_ID = wf_org_user_role.role_id and wf_role_manage_user.role_id = '" + roleId + "' and wf_org_user_role.user_id ='"+userId+"')) ";
  }else{
    sql = "select * from wf_user a "+
             " where user_id in ( "+
             " select distinct user_id"+
             " from  wf_org_user_role where user_state='1' and role_id='0' and org_id='"+orgId+"')"+
             " and (user_id in(SELECT dbo.WF_ROLE_MANAGE_USER.USER_ID"+
             " FROM dbo.WF_ROLE_MANAGE_USER,dbo.wf_org_user_role "+
             " WHERE dbo.WF_ROLE_MANAGE_USER.ROLE_ID dbo.wf_org_user_role.role_id and dbo.wf_role_manage_user.role_id = '" + roleId + "' and dbo.wf_org_user_role.user_id ='"+userId+"')) ";
  }

  logger.info("......query sql:"+sql);
  ResultSet rs=stm.executeQuery(sql);
  List list=resultsetToVO(rs);
  return list;
}
finally{
  WorkflowDBConnectionManager.getInstance().close(stm,false);
  WorkflowDBConnectionManager.getInstance().close(conn);
}
}

  /**
  * 查询指定部门、指定管理角色的管理权限范围内的用户信息,系统管理员，所有
  * @param orgId String
  * @return List
  * @throws SQLException
  */
  public List queryUserByOrgManageRole(String orgId,String roleId)
    throws SQLException{
  Connection conn=null;
  Statement stm=null;
  try{
    conn=WorkflowDBConnectionManager.getInstance().getConnection();
    stm=conn.createStatement();
    String sql= null;

    if("oracle".equalsIgnoreCase(DB_TYPE)){
      sql = "select * from wf_user a "+
               " where user_id in ( "+
               " select distinct user_id"+
               " from  wf_org_user_role where user_state='1' and role_id='0' and org_id='"+orgId+"')"+
               " and (user_id in(SELECT WF_ROLE_MANAGE_USER.USER_ID"+
               " FROM WF_ROLE_MANAGE_USER "+
               " WHERE wf_role_manage_user.role_id = '" + roleId + "')) ";
    }else{
      sql = "select * from wf_user a "+
               " where user_id in ( "+
               " select distinct user_id"+
               " from  wf_org_user_role where user_state='1' and role_id='0' and org_id='"+orgId+"')"+
               " and (user_id in(SELECT dbo.WF_ROLE_MANAGE_USER.USER_ID"+
               " FROM dbo.WF_ROLE_MANAGE_USER "+
               " WHERE dbo.wf_role_manage_user.role_id = '" + roleId + "')) ";
    }

    logger.info("......query sql:"+sql);
    ResultSet rs=stm.executeQuery(sql);
    List list=resultsetToVO(rs);
    return list;
  }
  finally{
    WorkflowDBConnectionManager.getInstance().close(stm,false);
    WorkflowDBConnectionManager.getInstance().close(conn);
  }
  }


  /**
   * 查询未归入部门的人员
   * @param orgId String
   * @return List
   * @throws SQLException
   */
  public List queryFreeUser()
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_user where user_state='1' and user_id not in "+
          " (select distinct user_id from wf_org_user_role where org_id<>'0')";
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 查询已经删除的人员
   * @param orgId String
   * @return List
   * @throws SQLException
   */
  public List queryDeletedUser()
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_user where user_state='0'";
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }