wforganizationdao.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.*;

public class WfOrganizationDAO{
  private static Logger logger=Logger.getLogger(WfUserDAO.class);
  private String dataBaseType=WorkflowDBConnectionManager.getInstance().
        getDataBaseType();
  private Connection conn = null;
  private PreparedStatement ptmt = null;
  private ResultSet rowset = null;

  public WfOrganizationDAO(){
  }

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

  /**
   * 查询组织信息，关联到表wf_area中
   * @param org WfOrganization
   * @throws SQLException
   * @return List
   */
  public List query(WfOrganization org)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" select * from wf_organization,wf_area ";
      StringBuffer sbWhere=getQueryString(org);
      sql=sql+sbWhere.toString();
      logger.info("......query sql:"+sql);
      rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   *指定行政区、指定父节点下部门信息
   * @param upOrgId String
   * @param areaId String
   * @param queryHasChildren 是否查询是否有子部门
   * @param queryHasUser boolean 是否查询是否有人员
   * @return List
   * @throws SQLException
   */
  public List query(String upOrgId,String areaId,boolean queryHasChildren,boolean queryHasUser)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql="";
      if (this.dataBaseType.equals("MSSQLSERVER")){
        sql=
            " select a.*,dbo.FUNC_QUERY_ORG_HAS_CHILDREN(a.org_id) AS HAS_CHILDREN,"+
            " dbo.FUNC_QUERY_ORG_HAS_USER(a.org_id) AS HAS_USER "+
            " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+
            upOrgId+"' and a.org_state='1' and "+" a.area_id='"+areaId+"' ";
      }
      else if(this.dataBaseType.equals("ORACLE")){
        sql=
            " select a.*,FUNC_QUERY_ORG_HAS_CHILDREN(a.org_id) AS HAS_CHILDREN,"+
            " FUNC_QUERY_ORG_HAS_USER(a.org_id) AS HAS_USER "+
            " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+
            upOrgId+"' and a.org_state='1' and "+" a.area_id='"+areaId+"' ";
      }
      logger.info("......query sql:"+sql);
      rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   *指定行政区、指定父节点下、登陆用户管理权限范围内的部门信息
   * @param upOrgId String
   * @param areaId String
   * @param queryHasChildren 是否查询是否有子部门
   * @param queryHasUser boolean 是否查询是否有人员
   * @return List
   * @throws SQLException
   */
  public List query(String upOrgId,String areaId,String userId,boolean queryHasChildren,
      boolean queryHasUser)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql="";
      if(this.dataBaseType.equals("MSSQLSERVER")){
        sql=" select a.*,dbo.FUNC_QUERY_ORG_HAS_MANAGED_CHILDREN(a.org_id,'"+userId+"') AS HAS_CHILDREN,"+
            " dbo.FUNC_QUERY_ORG_HAS_MANAGED_USER(a.org_id,'"+userId+"') AS HAS_USER "+
            " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+
            upOrgId+"' and a.org_state='1' and "+" a.area_id='"+areaId+"' " +
            " and (a.ORG_ID  in (SELECT dbo.WF_ROLE_MANAGE_ORG.ORG_ID " +
            " FROM dbo.WF_ORG_USER_ROLE INNER JOIN  dbo.WF_ROLE_MANAGE_ORG " +
            " ON dbo.WF_ORG_USER_ROLE.ROLE_ID = dbo.WF_ROLE_MANAGE_ORG.ROLE_ID "+
            "  WHERE dbo.WF_ORG_USER_ROLE.USER_ID = '"+userId+"') or a.CREATOR='"+userId+"')";
      }
      else if(this.dataBaseType.equals("ORACLE")){
        sql=
            " select a.*,FUNC_QUERY_ORG_HAS_CHILDREN(a.org_id) AS HAS_CHILDREN,"+
            " FUNC_QUERY_ORG_HAS_USER(a.org_id) AS HAS_USER "+
            " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+
            upOrgId+"' and a.org_state='1' and "+" a.area_id='"+areaId+"' " +
            " and (a.ORG_ID  in (SELECT WF_ROLE_MANAGE_ORG.ORG_ID " +
            " FROM WF_ORG_USER_ROLE INNER JOIN  WF_ROLE_MANAGE_ORG " +
            " ON WF_ORG_USER_ROLE.ROLE_ID = WF_ROLE_MANAGE_ORG.ROLE_ID "+
            "  WHERE WF_ORG_USER_ROLE.USER_ID = '"+userId+"') or a.CREATOR='"+userId+"')";
      }

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

  /**
    *查询指定行政区、指定部门、指定管理角色的管理权限范围内的部门信息
    * @param upOrgId String
    * @param areaId String
    * @param queryHasChildren 是否查询是否有子部门
    * @param queryHasUser boolean 是否查询是否有人员
    * @return List
    * @throws SQLException
    */
   public List queryOrgByAreaUpOrgManageRole(String upOrgId,String areaId,String roleId,String userId,boolean queryHasChildren,
       boolean queryHasUser)
       throws SQLException{
     Connection conn=null;
     Statement stm=null;
     ResultSet rs=null;
     try{
       conn=WorkflowDBConnectionManager.getInstance().getConnection();
       stm=conn.createStatement();
       String sql="";
       if(this.dataBaseType.equals("MSSQLSERVER")){
         sql="select a.*,dbo.FUNC_QUERY_ROLE_MANAGED_ORG_HAS_CHILDREN(a.org_id,'"+roleId+"') AS HAS_CHILDREN, dbo.FUNC_QUERY_ROLE_MANAGED_ORG_HAS_USER(a.org_id,'"+roleId+"') AS HAS_USER "+
             " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+upOrgId+"' and a.org_state='1' and  a.area_id='"+areaId+"' "+
             " and (a.ORG_ID  in (SELECT dbo.WF_ROLE_MANAGE_ORG.ORG_ID "+
             " FROM  dbo.WF_ROLE_MANAGE_ORG,wf_org_user_role "+
             " WHERE dbo.WF_ROLE_MANAGE_ORG.ROLE_ID = wf_org_user_role.role_id and wf_role_manage_org.role_id = '" + roleId + "' and wf_org_user_role.user_id = '"+userId+"')) ";
       }
       else if(this.dataBaseType.equals("ORACLE")){
         sql=
             " select a.*,FUNC_QUERY_ORG_HAS_CHILDREN(a.org_id) AS HAS_CHILDREN,"+
             " FUNC_QUERY_ORG_HAS_USER(a.org_id) AS HAS_USER "+
             " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+
             upOrgId+"' and a.org_state='1' and "+" a.area_id='"+areaId+"' "+
             " and (a.ORG_ID  in (SELECT WF_ROLE_MANAGE_ORG.ORG_ID "+
             " FROM WF_ROLE_MANAGE_ORG,wf_org_user_role "+
             " WHERE WF_ROLE_MANAGE_ORG.ROLE_ID = wf_org_user_role.role_id and wf_role_manage_org.role_id = '" + roleId + "' and wf_org_user_role.user_id ='"+userId+"')) ";
       }
       logger.info("......query sql:"+sql);
       rs=stm.executeQuery(sql);
       List list=resultsetToVO(rs);
       return list;
     }
     finally{
       WorkflowDBConnectionManager.getInstance().close(rs);
       WorkflowDBConnectionManager.getInstance().close(stm,false);
       WorkflowDBConnectionManager.getInstance().close(conn);
     }
   }

   /**
     *查询指定行政区、指定部门、指定管理角色的管理权限范围内的部门信息,系统管理员，所有
     * @param upOrgId String
     * @param areaId String
     * @param queryHasChildren 是否查询是否有子部门
     * @param queryHasUser boolean 是否查询是否有人员
     * @return List
     * @throws SQLException
     */
    public List queryOrgByAreaUpOrgManageRole(String upOrgId,String areaId,String roleId)
        throws SQLException{
      Connection conn=null;
      Statement stm=null;
      ResultSet rs=null;
      try{
        conn=WorkflowDBConnectionManager.getInstance().getConnection();
        stm=conn.createStatement();
        String sql="";
        if(this.dataBaseType.equals("MSSQLSERVER")){
          sql="select a.*,dbo.FUNC_QUERY_ROLE_MANAGED_ORG_HAS_CHILDREN(a.org_id,'"+roleId+"') AS HAS_CHILDREN, dbo.FUNC_QUERY_ROLE_MANAGED_ORG_HAS_USER(a.org_id,'"+roleId+"') AS HAS_USER "+
              " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+upOrgId+"' and a.org_state='1' and  a.area_id='"+areaId+"' "+
              " and (a.ORG_ID  in (SELECT dbo.WF_ROLE_MANAGE_ORG.ORG_ID "+
              " FROM  dbo.WF_ROLE_MANAGE_ORG where dbo.wf_role_manage_org.role_id = '" + roleId + "')) ";
        }
        else if(this.dataBaseType.equals("ORACLE")){
          sql=
              " select a.*,FUNC_QUERY_ORG_HAS_CHILDREN(a.org_id) AS HAS_CHILDREN,"+
              " FUNC_QUERY_ORG_HAS_USER(a.org_id) AS HAS_USER "+
              " from wf_organization a,wf_area b where a.area_id=b.area_id and a.up_org='"+
              upOrgId+"' and a.org_state='1' and "+" a.area_id='"+areaId+"' "+
              " and (a.ORG_ID  in (SELECT WF_ROLE_MANAGE_ORG.ORG_ID "+
              " FROM WF_ROLE_MANAGE_ORG where wf_role_manage_org.role_id = '" + roleId + "')) ";
        }
        logger.info("......query sql:"+sql);
        rs=stm.executeQuery(sql);
        List list=resultsetToVO(rs);
        return list;
      }
      finally{
        WorkflowDBConnectionManager.getInstance().close(rs);
        WorkflowDBConnectionManager.getInstance().close(stm,false);
        WorkflowDBConnectionManager.getInstance().close(conn);
      }
    }

  /**
   * 查询指定组织的可选择上级组织列表
   * @param orgId String 上级部门id
   * @param orgId String 当前部门id
   * @param areaId String 所属行政区id
   * @return List
   * @throws SQLException
   */
  public List querySelectParentOrgList(String upOrgId,String orgId,String areaId)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql="";
      if (this.dataBaseType.equals("MSSQLSERVER")){
        sql=
            "select *,dbo.FUNC_QUERY_ORG_HAS_CHILDREN(org_id) AS HAS_CHILDREN, "+
            " dbo.FUNC_QUERY_ORG_HAS_USER(org_id) AS HAS_USER from "+
            " wf_organization a,wf_area b where a.area_id=b.area_id and "+
            " a.org_state='1' and up_org='"+upOrgId+"' and a.org_id not in "+
            " (select tree_id from FUNC_GET_ORG_INFO_INCLUDE_SUB('"+orgId+
            "')) and a.area_id='"+areaId+"' "+" order by org_order ";
      }
      else if(this.dataBaseType.equals("ORACLE")){
        sql="select a.*,FUNC_QUERY_ORG_HAS_CHILDREN(org_id) AS HAS_CHILDREN, "+
            " FUNC_QUERY_ORG_HAS_USER(org_id) AS HAS_USER from "+
            " wf_organization a,wf_area b where a.area_id=b.area_id and "+
            " a.org_state='1' and up_org='"+upOrgId+"' and a.org_id not in "+
            " (select c.org_id from wf_organization c start with c.org_id='"+
            orgId+"' connect by prior c.up_org=prior c.org_id) and a.area_id='"+
            areaId+"' "+" order by org_order ";
      }
      logger.info("......query sql:"+sql);
      rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 查询登陆用户管理权限范围内的指定组织的可选择上级组织列表
   * @param orgId String 上级部门id
   * @param orgId String 当前部门id
   * @param areaId String 所属行政区id
   * @return List
   * @throws SQLException
   */
  public List querySelectParentOrgList(String upOrgId,String orgId,String areaId,String userId)
      throws SQLException{
    Connection conn=null;
    Statement stm=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql="";
      if(this.dataBaseType.equals("MSSQLSERVER")){
        sql=
            "select *,dbo.FUNC_QUERY_ORG_HAS_CHILDREN(org_id) AS HAS_CHILDREN, "+
            " dbo.FUNC_QUERY_ORG_HAS_USER(org_id) AS HAS_USER from "+
            " wf_organization a,wf_area b where a.area_id=b.area_id and "+
            " a.org_state='1' and up_org='"+upOrgId+"' and a.org_id not in "+
            " (select tree_id from FUNC_GET_ORG_INFO_INCLUDE_SUB('"+orgId+
            "')) and a.area_id='"+areaId+"' "+
            " and (a.ORG_ID  in (SELECT dbo.WF_ROLE_MANAGE_ORG.ORG_ID "+
            "              FROM dbo.WF_ORG_USER_ROLE INNER JOIN dbo.WF_ROLE_MANAGE_ORG "+
            "              ON  dbo.WF_ORG_USER_ROLE.ROLE_ID = dbo.WF_ROLE_MANAGE_ORG.ROLE_ID"+
            "             WHERE dbo.WF_ORG_USER_ROLE.USER_ID = '"+userId+"') or a.CREATOR='"+userId+"')"+
            " order by org_order ";
      }
      else if(this.dataBaseType.equals("ORACLE")){
        sql="select a.*,FUNC_QUERY_ORG_HAS_CHILDREN(org_id) AS HAS_CHILDREN, "+
            " FUNC_QUERY_ORG_HAS_USER(org_id) AS HAS_USER from "+
            " wf_organization a,wf_area b where a.area_id=b.area_id and "+
            " a.org_state='1' and up_org='"+upOrgId+"' and a.org_id not in "+
            " (select c.org_id from wf_organization c start with c.org_id='"+
            orgId+"' connect by prior c.up_org=prior c.org_id) and a.area_id='"+
            areaId+"' "+" order by org_order ";
      }
      logger.info("......query sql:"+sql);
      rs=stm.executeQuery(sql);
      List list=resultsetToVO(rs);
      return list;
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(stm,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
  }

  /**
   * 更新wf_organization信息
   * @param info WfOrganization
   * @throws SQLException
   * @return List
   */
  public void update(WfOrganization org,String orgId)
      throws Exception{
    Connection conn=null;
    Statement stm=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      stm=conn.createStatement();
      String sql=" update wf_organization set ";
      StringBuffer sbWhere=new StringBuffer(" where org_id='"+orgId+"'");
      StringBuffer sbUpdata=getStrUpdateValues(org);