purviewmanagedao.java

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

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

import java.sql.*;
/**
 * <p>Title: 权限管理</p>
 *
 * <p>Description: 深圳市劳动仲裁信息管理系统</p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: 永泰软件工程有限公司</p>
 *
 * @author syj
 * @version 1.0
 */
import java.util.*;
import cn.com.iaspec.workflow.db.*;
import cn.com.iaspec.workflow.organize.*;

public class PurviewManageDAO{
  /**
   * 返回最大的ID号（条件是字段名字和表名字）
   * @param fieldname String
   * @param tablename String
   * @return String
   */
  public String getCreatOrgani_idDAO11(String fieldname,String tablename){
    String org_id="0";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;

    String sql="select max(to_number("+fieldname+")) from "+tablename;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      while(rs.next()){
        org_id=rs.getString(1);
      }

    }
    catch(Exception e){
      e.printStackTrace();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);

    }
    return org_id;
  }

  /**
   *取得组织的所有信息(条件是up_org)
   * @param up_org String
   * @return ArrayList
   */
  public ArrayList queryOrganizationDAO(String area_id,String up_org){
    PreparedStatement prep=null;
    Connection conn=null;
    Collection coll=null;
    ResultSet rs=null;
    String sql="";
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    if(dataBaseType.equals("ORACLE")){
      sql=
          "select org_id,org_name,up_org,org_level,area_id from wf_organization "+
          " where area_id=? start with up_org = ? connect by prior org_id = up_org";
    }
    else if(dataBaseType.toUpperCase().equals("MSSQLSERVER")){
      sql=
          "select org_id,org_name,up_org,org_level,area_id from wf_organization "+" where area_id=? and org_id in (select tree_id as org_id from FUNC_GET_ORG_INFO_INCLUDE_SUB(?))";
    }

    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,area_id);
      prep.setString(2,up_org);
      rs=prep.executeQuery();
      if(rs!=null){
        coll=RsToStringTool.getCollection(rs,1);
      }

    }
    catch(Exception e){
      e.printStackTrace();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);

    }
    return new ArrayList(coll);
  }

  /**
   * 取得组织的所有信息(条件是up_org)(是系统管理员的时候)
   * @param area_id String
   * @param up_org String
   * @return ArrayList
   */
  public ArrayList queryOrganizationDAO(String up_org){
    PreparedStatement prep=null;
    Connection conn=null;
    Collection coll=null;
    ResultSet rs=null;
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    String sql="";
    if(dataBaseType.equals("ORACLE")){
      sql=
          "select org_id,org_name,up_org,org_level,area_id from wf_organization "+
          "  start with up_org = ? connect by prior org_id = up_org";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql=
          "select org_id,org_name,up_org,org_level,area_id from wf_organization "+" where org_id in (select tree_id as org_id from FUNC_GET_ORG_INFO_INCLUDE_SUB(?))";
    }
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,up_org);
      rs=prep.executeQuery();
      if(rs!=null){
        coll=RsToStringTool.getCollection(rs,1);
      }

    }
    catch(Exception e){
      e.printStackTrace();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);

    }
    return new ArrayList(coll);
  }

  /**
   * 查询在条件满足下的组织ID的人员集合
   * @param up_org String
   * @return ArrayList
   */
  public ArrayList queryUserInfo(String area_id,String up_org){
    PreparedStatement prep=null;
    Connection conn=null;
    Collection coll=null;
    ResultSet rs=null;
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    String sql="";
    if(dataBaseType.equals("ORACLE")){
      sql="select e.org_id,e.role_id,e.user_id,f.user_name,f.login_id,g.org_level,g.area_id"+
          " from wf_org_user_role e,wf_user f,wf_organization g where f.user_id=e.user_id"+"  and g.org_id=e.org_id and e.org_id in(select a.org_id from wf_organization a "+" where area_id=? start with  a.up_org = ? connect by prior a.org_id = a.up_org)";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql="select e.org_id,e.role_id,e.user_id,f.user_name,f.login_id,g.org_level,g.area_id"+
          " from wf_org_user_role e,wf_user f,wf_organization g where f.user_id=e.user_id"+"  and g.org_id=e.org_id and e.org_id in(select a.org_id from wf_organization a "+" where area_id=? and a.org_id in (select tree_id as org_id from FUNC_GET_ORG_INFO_INCLUDE_SUB(?)))";
    }
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,area_id);
      prep.setString(2,up_org);
      rs=prep.executeQuery();
      if(rs!=null){
        coll=RsToStringTool.getCollection(rs,2);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return new ArrayList(coll);

  }

  /**
   * 是系统管理员的时候取所有的
   * @param up_org String
   * @return ArrayList
   */
  public ArrayList queryUserInfo(String up_org){
    PreparedStatement prep=null;
    Connection conn=null;
    Collection coll=null;
    ResultSet rs=null;
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    String sql="";
    if(dataBaseType.equals("ORACLE")){
      sql="select e.org_id,e.role_id,e.user_id,f.user_name,f.login_id,g.org_level,g.area_id"+" from wf_org_user_role e,wf_user f,wf_organization g where f.user_id(+)=e.user_id"+"  and g.org_id=e.org_id and e.org_id in(select a.org_id from wf_organization a "+
          "  start with  a.up_org = ? connect by prior a.org_id = a.up_org)";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql="select e.org_id,e.role_id,e.user_id,f.user_name,f.login_id,g.org_level,g.area_id"+" from wf_org_user_role e,wf_user f,wf_organization g where f.user_id(+)=e.user_id"+"  and g.org_id=e.org_id and e.org_id in(select a.org_id from wf_organization a "+" where a.org_id in (select tree_id as org_id from FUNC_GET_ORG_INFO_INCLUDE_SUB(?)))";
    }

    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,up_org);
      rs=prep.executeQuery();
      if(rs!=null){
        coll=RsToStringTool.getCollection(rs,2);
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return new ArrayList(coll);

  }

}