selectactordao.java

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

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

import java.util.List;
import cn.com.iaspec.workflow.db.WorkflowDBConnectionManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import org.apache.log4j.Logger;
import cn.com.iaspec.workflow.vo.db.WfUser;
import java.sql.SQLException;
import java.util.ArrayList;
import cn.com.iaspec.workflow.vo.workflow.SelectActorParam;
import cn.com.iaspec.workflow.vo.db.WfOrganization;
import cn.com.iaspec.workflow.vo.db.WfRole;
import java.sql.PreparedStatement;
import java.util.Arrays;

/**
 * <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 SelectActorDAO{
  private static Logger logger=Logger.getLogger(SelectActorDAO.class);
  private String dataBaseType=WorkflowDBConnectionManager.getInstance().
      getDataBaseType();

  public SelectActorDAO(){
  }

  /**
   * 根据部门、角色取得人员信息
   * @param orgList 部门id列表
   * @param roleList 角色id列表
   * @return 由WfUser组成的人员信息列表
   */
  public List getUserByOrgRole(List orgList,List roleList)
      throws Exception{
    Connection conn=null;
    Statement stm=null;
    try{
      String orgIds="";
      String roleIds="";
      if(orgList!=null&&orgList.size()>0){
        for(int i=0;i<orgList.size();i++){
          WfOrganization org=(WfOrganization)orgList.get(i);
          orgIds=orgIds+",'"+org.getOrgId()+"'";
        }
      }
      if(orgIds!=null&&orgIds.length()>0){
        //去掉“，”，加上（）
        orgIds="("+orgIds.substring(1)+")";
      }
      else{
        orgIds="('-1')";
      }

      if(roleList!=null&&roleList.size()>0){
        for(int i=0;i<roleList.size();i++){
          WfRole role=(WfRole)roleList.get(i);
          roleIds=roleIds+",'"+role.getRoleId()+"'";
        }
      }
      if(roleIds!=null&&roleIds.length()>0){
        //去掉“，”，加上（）
        roleIds="("+roleIds.substring(1)+")";
      }
      else{
        roleIds="('-1')";
      }
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select distinct b.org_id,b.org_name,e.role_id,e.role_name,c.* from wf_org_user_role a "+
          " inner join wf_organization b on a.org_id=b.org_id "+
          " inner join wf_user c on a.user_id=c.user_id and c.user_id in "+
          " (select distinct user_id from wf_org_user_role where role_id in "+
          roleIds+") "+" inner join wf_org_user_role d on c.user_id=d.user_id "+
          " inner join wf_role e on d.role_id=e.role_id where a.org_id in "+
          orgIds;
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToUser(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 查询属于某一角色的用户
   *
   * @param roleId String  角色ID
   * @return List<WfUser>
   * @throws Exception
   */
  public List getUserByRoleId(String roleId)
      throws Exception{
    String sql="Select e.*,b.org_id,b.org_name "+
        "From (Select a.*,c.role_id,c.role_name "+
        "     From wf_user a,wf_role c,wf_org_user_role d "+"     Where a.User_Id = d.user_id And c.role_id = d.role_id(+) And c.role_id = ?) e, "+
        "     wf_organization b,wf_org_user_role f "+
        "Where e.user_id = f.user_id And f.Org_Id(+) = b.Org_Id";
    Connection conn=null;
    PreparedStatement ptmt=null;
    ResultSet rowset=null;
    List userList=null;

    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      ptmt=conn.prepareStatement(sql);
      ptmt.setString(1,roleId);

      rowset=ptmt.executeQuery();
      userList=this.resultsetToUser(rowset);
    }
    catch(SQLException sqle){
      sqle.printStackTrace();
      throw sqle;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return userList;
  }

  /**
   * 根据角色取得人员信息
   * @param roleList 角色id列表
   * @param areaId 指定行政区
   * @return 由WfUser组成的人员信息列表
   * @throws Exception
   */
  public List getUserByRole(List roleList,String areaId)
      throws Exception{
    Connection conn=null;
    Statement stm=null;
    try{
      String roleIds="";
      String strAreaId="";
      if(areaId!=null&&areaId.endsWith("-1")){
        strAreaId="";
      }
      else{
        strAreaId=" e.area_id='"+areaId+"' and ";
      }
      if(roleList!=null&&roleList.size()>0){
        for(int i=0;i<roleList.size();i++){
          WfRole role=(WfRole)roleList.get(i);
          roleIds=roleIds+",'"+role.getRoleId()+"'";
        }
      }
      if(roleIds!=null&&roleIds.length()>0){
        //去掉“，”，加上（）
        roleIds="("+roleIds.substring(1)+")";
      }
      else{
        roleIds="('-1')";
      }
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql="select distinct e.org_id,e.org_name,c.role_id,c.role_name,b.* from wf_org_user_role a "+" inner join wf_user b on a.user_id=b.user_id inner join wf_role c on a.role_id=c.role_id "+
          "inner join wf_org_user_role d on a.user_id=d.user_id "+
          "left join wf_organization e on d.org_id=e.org_id where "+strAreaId+
          " a.role_id in "+roleIds;
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToUser(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 查询属于某一组织的用户
   *
   * @param orgId String  角色ID
   * @return List<WfUser>
   * @throws Exception
   */
  public List getUserByOrgId(String orgId)
      throws Exception{
    String sql="Select e.*,b.role_id,b.role_name "+
        "From (Select a.*,c.org_id,c.org_name "+
        "     From wf_user a,wf_organization c,wf_org_user_role d "+"     Where a.User_Id = d.user_id And c.org_id = d.org_id(+) And c.org_id = ?) e, "+
        "     wf_role b,wf_org_user_role f "+
        "Where e.user_id = f.user_id And f.role_Id(+) = b.role_Id";
    Connection conn=null;
    PreparedStatement ptmt=null;
    ResultSet rowset=null;
    List userList=null;

    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      ptmt=conn.prepareStatement(sql);
      ptmt.setString(1,orgId);

      rowset=ptmt.executeQuery();
      userList=this.resultsetToUser(rowset);
    }
    catch(SQLException sqle){
      sqle.printStackTrace();
      throw sqle;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rowset);
      WorkflowDBConnectionManager.getInstance().close(ptmt,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return userList;
  }

  /**
   * 根据部门、角色取得人员信息
   * @param orgList 部门id列表
   * @return 由WfUser组成的人员信息列表
   */
  public List getUserByOrg(List orgList)
      throws Exception{
    Connection conn=null;
    Statement stm=null;
    try{
      String orgIds="";
      if(orgList!=null&&orgList.size()>0){
        for(int i=0;i<orgList.size();i++){
          WfOrganization org=(WfOrganization)orgList.get(i);
          orgIds=orgIds+",'"+org.getOrgId()+"'";
        }
      }
      if(orgIds!=null&&orgIds.length()>0){
        //去掉“，”，加上（）
        orgIds="("+orgIds.substring(1)+")";
      }
      else{
        orgIds="('-1')";
      }
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select distinct b.org_id,b.org_name,e.role_id,e.role_name,c.* from wf_org_user_role a "+
          " inner join wf_organization b on a.org_id=b.org_id "+
          " inner join wf_user c on a.user_id=c.user_id "+
          " inner join wf_org_user_role d on c.user_id=d.user_id "+
          " left join wf_role e on e.role_id=d.role_id "+" where a.org_id in "+
          orgIds;
      logger.info("......query sql:"+sql);
      ResultSet rs=stm.executeQuery(sql);
      List list=resultsetToUser(rs);