getmenumanagedao.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.manage.business.*;
import cn.com.iaspec.workflow.organize.*;
import cn.com.iaspec.workflow.util.*;
import cn.com.iaspec.workflow.vo.workflow.*;

/**
 * <p>Title:菜单控制 </p>
 *
 * <p>Description: 深圳市劳动仲裁信息管理系统</p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: 永泰软件工程有限公司</p>
 *
 * @author syj
 * @version 1.0
 */
public class GetMenuManageDAO
    implements MenuInterface{
  /**
   * 根据上级菜单编号来查询所有的菜单信息
   * @param up_func_id String
   * @return ArrayList
   * @throws MenuManageException
   */
  public ArrayList getMenuInfo(String up_func_id)
      throws MenuManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    Collection menulist=null;
    String sql="";
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    if(dataBaseType.equals("ORACLE")){
      sql="select * from wf_func_info start with up_func_id=?"+
          " connect by prior func_id=up_func_id";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql="select * from wf_func_info where func_id in "+
          " (select tree_id as func_id from FUNC_GET_FUNCTION_INFO_INCLUDE_SUB(?))";
    }
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,up_func_id);
      rs=prep.executeQuery();
      if(rs!=null){
        menulist=RsToStringTool.getCollection(rs,5);
      }

    }
    catch(Exception e){
      e.printStackTrace();
      throw new MenuManageException("--取得菜单信息异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return new ArrayList(menulist);
  }

  /**
   * 添加菜单信息
   * @param la_menu La_Menu
   */
  public int addMenuInfo(WfMenu la_menu)
      throws MenuManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    int i=0;
    String sql=
        "insert into wf_func_info(func_id,func_name,up_func_id,fun_desc,page_name,"+
        " activityid,fireflag,func_order,func_image,open_type,window_width,"+
        " window_height,func_level) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    try{
      String func_id=GetTableSequenceId.getTableId("wf_func_info");
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,func_id);
      prep.setString(2,la_menu.getFunc_name());
      prep.setString(3,la_menu.getUp_func_id());
      prep.setString(4,la_menu.getFun_desc());
      prep.setString(5,la_menu.getPage_name());
      prep.setLong(6,la_menu.getActivityid());
      prep.setInt(7,la_menu.getFireflag());
      prep.setInt(8,la_menu.getFunc_order());
      prep.setString(9,la_menu.getFunc_image());
      prep.setString(10,la_menu.getOpen_type());
      prep.setInt(11,la_menu.getWindow_width());
      prep.setInt(12,la_menu.getWindow_height());
      prep.setInt(13,la_menu.getFunc_level());
      i=prep.executeUpdate();

    }
    catch(Exception e){
      e.printStackTrace();
      throw new MenuManageException("--添加菜单信息异常");
    }
    finally{

      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;
  }

  /**
   * 更新菜单信息
   * @param la_menu La_Menu
   * @throws MenuManageException
   */
  public int updateMenuInfo(WfMenu la_menu)
      throws MenuManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    int i=0;
    String sql=
        "update wf_func_info set func_name=?,up_func_id=?,fun_desc=?,page_name=?,"+
        " activityid=?,fireflag=?,func_order=?,func_image=?,open_type=?,window_width=?,"+
        " window_height=?,func_level=? where func_id=?";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,la_menu.getFunc_name());
      prep.setString(2,la_menu.getUp_func_id());
      prep.setString(3,la_menu.getFun_desc());
      prep.setString(4,la_menu.getPage_name());
      prep.setLong(5,la_menu.getActivityid());
      prep.setInt(6,la_menu.getFireflag());
      prep.setInt(7,la_menu.getFunc_order());
      prep.setString(8,la_menu.getFunc_image());
      prep.setString(9,la_menu.getOpen_type());
      prep.setInt(10,la_menu.getWindow_width());
      prep.setInt(11,la_menu.getWindow_height());
      prep.setInt(12,la_menu.getFunc_level());
      prep.setString(13,la_menu.getFunc_id());
      i=prep.executeUpdate();

    }
    catch(Exception e){
      throw new MenuManageException("--更新菜单信息异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;
  }

  /**
   * 删除菜单信息
   * @param func_id String
   * @return int
   */
  public int deleteMenuInfo(String func_id)
      throws MenuManageException{
    int i=0;
    PreparedStatement prep=null;
    Connection conn=null;
    String sql="";
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    if(dataBaseType.equals("ORACLE")){
      sql="delete wf_func_info where func_id in(select func_id from "+
          " wf_func_info start with func_id = ? connect by prior func_id = up_func_id)";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql="delete wf_func_info where func_id in(select tree_id as func_id from FUNC_GET_FUNCTION_INFO_INCLUDE_SUB(?))";
    }
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,func_id);
      i=prep.executeUpdate();

    }
    catch(Exception e){
      throw new MenuManageException("--删除菜单信息异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;
  }

  /**
   * 根据菜单的id查询信息
   * @param func_id String
   * @return La_Menu
   */
  public WfMenu queryMenuInfo(String func_id)
      throws MenuManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    WfMenu la_Menu=new WfMenu();
    String sql="select *  from wf_func_info  where func_id=?";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,func_id);
      rs=prep.executeQuery();
      while(rs.next()){
        la_Menu.setFunc_id(rs.getString("func_id"));
        la_Menu.setFunc_name(rs.getString("func_name"));
        la_Menu.setUp_func_id(rs.getString("up_func_id"));
        la_Menu.setFun_desc(rs.getString("fun_desc"));
        la_Menu.setPage_name(rs.getString("page_name"));
        la_Menu.setActivityid(rs.getLong("activityid"));
        la_Menu.setFireflag(rs.getInt("fireflag"));
        la_Menu.setFunc_order(rs.getInt("func_order"));
        la_Menu.setFunc_image(rs.getString("func_image"));
        la_Menu.setOpen_type(rs.getString("open_type"));
        la_Menu.setWindow_width(rs.getInt("window_width"));
        la_Menu.setWindow_height(rs.getInt("window_height"));
        la_Menu.setFunc_level(rs.getInt("func_level"));
      }

    }
    catch(Exception e){
      throw new MenuManageException("--查询菜单信息异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return la_Menu;
  }

  /**
   * 添加角色和功能菜单
   * @param wf_role_func wf_role_Func
   * @return int
   * @throws MenuManageException
   */
  public int add_Role_func(WfRoleFunc wf_role_func)
      throws MenuManageException{
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    String sql="";
    if(dataBaseType.equals("ORACLE")){
      sql="insert into wf_role_func(role_id,func_id) select '"+
          wf_role_func.getRole_id()+"',func_id "+
          " from wf_func_info where not exists(select 'x' from wf_role_func "+
          " where wf_role_func.role_id ='"+wf_role_func.getRole_id()+
          "' and wf_role_func.func_id = wf_func_info.func_id)"+
          " start with func_id='"+wf_role_func.getFunc_id()+
          "' connect by prior up_func_id=func_id";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql="insert into wf_role_func(role_id,func_id) select '"+
          wf_role_func.getRole_id()+"',func_id "+
          " from wf_func_info where not exists(select 'x' from wf_role_func "+
          " where wf_role_func.role_id ='"+wf_role_func.getRole_id()+
          "' and wf_role_func.func_id = wf_func_info.func_id)"+"  and func_id in (select tree_id as func_id from FUNC_GET_FUNCTION_INFO_INCLUDE_SUB('"+
          wf_role_func.getFunc_id()+"'))";
    }
    int i=1;
    PreparedStatement prep=null;
    Connection conn=null;
    //System.out.println("-----sql="+sql);
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.executeUpdate();
      i=1;

    }
    catch(Exception e){
      e.printStackTrace();
      i=0;
      throw new MenuManageException("-添加角色和功能信息异常-");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return i;
  }

  /**
   * 判断该关联信息是否存在
   * @param wf_role_func wf_role_Func
   * @return int
   * @throws MenuManageException
   */
  public int check_Role_func(WfRoleFunc wf_role_func,Connection conn)
      throws MenuManageException{
    String sql="select * from wf_role_func where role_id=? and func_id=?";
    int i=0;
    PreparedStatement prep=null;
    ResultSet rs=null;
    try{
      prep=conn.prepareStatement(sql);
      prep.setString(1,wf_role_func.getRole_id());
      prep.setString(2,wf_role_func.getFunc_id());
      rs=prep.executeQuery();
      if(rs!=null){
        i=1;
      }
    }
    catch(Exception e){
      throw new MenuManageException("-判断角色和功能信息是否存在异常-");
    }
    return i;

  }

  /**
   * 删除角色和功能的关联信息
   * @param wf_role_func wf_role_Func
   * @return int
   */
  public int delete_Role_func(WfRoleFunc wf_role_func)
      throws MenuManageException{
    int i=0;
    PreparedStatement prep=null;
    Connection conn=null;
    String sql="";
    String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
    if(dataBaseType.equals("ORACLE")){
      sql="delete wf_role_func where role_id=? and func_id in(select func_id"+
          " from wf_func_info start with func_id=? connect by prior func_id=up_func_id)";
    }
    else if(dataBaseType.equals("MSSQLSERVER")){
      sql=
          "delete wf_role_func where role_id=? and func_id in(select tree_id as func_id"+
          " from FUNC_GET_FUNCTION_INFO_INCLUDE_SUB(?))";
    }
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,wf_role_func.getRole_id());
      prep.setString(2,wf_role_func.getFunc_id());
      i=prep.executeUpdate();

    }
    catch(Exception e){
      throw new MenuManageException("--删除角色和功能菜单信息异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    System.out.println("wf_role_func.getRole_id()="+wf_role_func.getRole_id()+
        "--wf_role_func.getFunc_id()="+wf_role_func.getFunc_id()+
        "--------i=========="+i);
    return i;

  }

  /**
   * 取得功能角色信息
   * @return ArrayList
   * @throws MenuManageException
   */
  public ArrayList getRole_info(String type)
      throws MenuManageException{
    String sql="select t.* from wf_role t where t.role_type='"+type+"'";
    PreparedStatement prep=null;
    Connection conn=null;
    Collection c=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      if(rs!=null){
        c=RsToStringTool.getCollection(rs,6);
      }
    }
    catch(Exception e){
      e.printStackTrace();
      throw new MenuManageException("-取功能角色信息异常--");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return new ArrayList(c);
  }

  /**
   * 取得角色和菜单的关联信息
   * @return ArrayList
   * @throws MenuManageException
   */
  public ArrayList getRole_func_info()
      throws MenuManageException{
    String sql=
        "select a.role_id,a.role_name,a.role_type,b.func_id,b.func_name,"+
        " b.up_func_id,b.func_level from wf_role_func t,wf_role a,wf_func_info b "+
        " where a.role_id=t.role_id and b.func_id=t.func_id";

    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    Collection c=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      if(rs!=null){
        c=RsToStringTool.getCollection(rs,7);
      }
    }
    catch(Exception e){
      throw new MenuManageException("-取功能角色和菜单关联信息异常--");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return new ArrayList(c);
  }

}