package stumng;

import java.sql.*;
import java.util.*;
import java.sql.Date;
import java.util.regex.*;

/*連接池管理類,可以管理多個數據庫連接池*/
public class DBConnManager {
  //連接池名列表
  private Vector poolnames = new Vector();

  //驅動程序名列表
  private Vector drivernames = new Vector();

  //數據庫標識列表
  private Vector dbids = new Vector();

  //用戶名列表
  private Vector usernames = new Vector();

  //密碼列表
  private Vector passwds = new Vector();

  //最大連接數列表
  private Vector maxconns = new Vector();

  //連接池隊列
  private Hashtable connPools = new Hashtable();

  public DBConnManager() {
    //添加Access數據庫的連接信息
    poolnames.addElement("Access");
    drivernames.addElement("sun.jdbc.odbc.JdbcOdbcDriver");
    dbids.addElement("jdbc:odbc:stuMngs");
    usernames.addElement("wangqiaoyun");
    passwds.addElement("wangqiaoyun");
    maxconns.addElement("6");

    //創建連接池
    createPools();
  }

  /*將連接返回給由指定的連接池*/
  public void releaseConnection(String name, Connection con) {
    DBConnPool pool = (DBConnPool) connPools.get(name);
    if (pool != null)
      pool.releaseConnection(con);
  }

  /*得到一個指定連接池中的連接*/
  public Connection getConnection(String name) {
    DBConnPool pool = (DBConnPool) connPools.get(name);
    if (pool != null)
      return pool.getConnection();
    else return null;
  }

  /*往Access數據庫中插入數據時，進行編碼轉換*
           public String ISOtoGBK(String iso) {
    String gb;
    try {
      if (iso.equals("")) {
        return "";
      }
      else {
        gb = new String(iso.getBytes("ISO-8859-1"),"GBK");
        return gb;
      }
    }
    catch (Exception e) {
      System.err.print("編碼轉換錯誤：" + e.getMessage());
      return "";
    }
           }

   *往Access數據庫中取數據時，進行編碼轉換*
           public String GBKtoISO(String gb) {
    String iso;
    try {
      if (gb.equals("")) {
        return "";
      }
      else {
        iso = new String(gb.getBytes("GBK"), "ISO-8859-1");
        return iso;
      }
    }
    catch (Exception e) {
      System.err.print("編碼轉換錯誤：" + e.getMessage());
      return "";
    }
           }*/



  //檢查該班是否存在此學生
  public boolean userExist(int userId, String aclass) {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    boolean occupied = true;
    String sqlquery ="select * from stuinf_A2241 where stu_id=? and class ='"+aclass+"'";
    try {
      if (aclass.equals("A2241")) {
        sqlquery = "select * from stuinf_A2241 where stu_id=?";
      }
      else if (aclass.equals("A2242")) {
        sqlquery = "select * from stuinf_A2242 where stu_id=?";
      }
      else if (aclass.equals("A2243")) sqlquery = "select * from stuinf_A2243 where stu_id=?";
      else occupied = false;
      con = getConnection("Access");
      ps = con.prepareStatement(sqlquery);
      ps.setInt(1, userId);
      rs = ps.executeQuery();
      if (rs.next() == false )
        occupied = false;
      else occupied = true;
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (rs != null)try {
        rs.close();
      }
      catch (SQLException ignore) {}
      if (ps != null)try {
        ps.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }
    }
    return occupied;
  }

  //檢查是否有該學生的成績記錄
  public boolean gradeExist(int userId, String aclass) {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    boolean occupied = true;
    String sqlquery = null;
    try {
      if (aclass.equals("A2241")) {
        sqlquery = "select * from stugrade_A2241 where stu_id=?";
      }
      else if (aclass.equals("A2242")) {
        sqlquery = "select * from stugrade_A2242 where stu_id=?";
      }
      else sqlquery = "select * from stugrade_A2243 where stu_id=?";
      con = getConnection("Access");
      ps = con.prepareStatement(sqlquery);
      ps.setInt(1, userId);
      rs = ps.executeQuery();
      if (!rs.next())
        occupied = false;
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (rs != null)try {
        rs.close();
      }
      catch (SQLException ignore) {}
      if (ps != null)try {
        ps.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }
    }
    return occupied;
  }

  //刪除學生
  //userId為要刪除的用戶ID
  public boolean removeUser(int userId, String aname, String aclass) {
    Connection con = null;
    PreparedStatement ps = null;
    boolean delflag = false;
    boolean exitflag = false;
    String sqlupdate = null;
    String sqldel = null;
    try {
      if (aclass.equals("A2241")) {
        sqlupdate = "delete * from stuinf_A2241 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_A2241 where stu_id= ?";
        }
      }
      else if (aclass.equals("A2242")) {
        sqlupdate = "delete * from stuinf_A2242 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_A2242 where stu_id= ?";
        }
      }
      else {
        sqlupdate = "delete * from stuinf_A2243 where stu_id=? and name = ?";
        exitflag = gradeExist(userId, aclass);
        if (exitflag = true) {
          sqldel = "delete * from stugrade_A2243 where stu_id= ?";
        }
      }
      con = getConnection("Access");
      ps = con.prepareStatement(sqlupdate);
      ps.setInt(1, userId);
      ps.setString(2, aname);
      ps.executeUpdate();
      exitflag = gradeExist(userId, aclass);
      if (exitflag = true) {
        ps = con.prepareStatement(sqldel);
        ps.setInt(1, userId);
        ps.executeUpdate();
      }
      delflag = true;
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (ps != null)try {
        ps.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }

    }

    return delflag;
  }

  //添加基本信息
  //userId為添加的用戶ID
  public boolean addUser(String aname, int astu_id, String aclass,
                         Date abirthday, String asex,
                         String anativeplace, String aethnic) {
    Connection con = null;
    PreparedStatement ps = null;
    boolean addflag = false;
    String sqlInsert = null;
    try {
      if (aclass.equals("A2241")) {
        sqlInsert = "insert into stuinf_A2241(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("A2242")) {
        sqlInsert = "insert into stuinf_A2242(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      }
      else sqlInsert = "insert into stuinf_A2243(name, stu_id, class, birthday, sex, nativeplace, ethnic ) values(?,?,?,?,?,?,?)";
      con = getConnection("Access");
      ps = con.prepareStatement(sqlInsert);
      ps.setString(1, aname);
      ps.setInt(2, astu_id);
      ps.setString(3, aclass);
      ps.setDate(4, abirthday);
      ps.setString(5, asex);
      ps.setString(6, anativeplace);
      ps.setString(7, aethnic);
      ps.executeUpdate();
      addflag = true;
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (ps != null)try {
        ps.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }
    }
    return addflag;
  }

  //添加學生成績
  public boolean addGrade(String aclass, int astu_id, float atotal, float amath,
                          float achinese,
                          float aenglish, float acomposite) {
    Connection con = null;
    PreparedStatement ps = null;
    boolean addflag = false;
    String sqlInsert = null;
    try {
      if (aclass.equals("A2241")) {
        sqlInsert = "insert into stugrade_A2241(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else if (aclass.equals("A2242")) {
        sqlInsert = "insert into stugrade_A2242(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      }
      else sqlInsert = "insert into stugrade_A2243(stu_id, total, chinese, math, english, composite, class) values(?,?,?,?,?,?,?)";
      con = getConnection("Access");
      ps = con.prepareStatement(sqlInsert);
      ps.setInt(1, astu_id);
      ps.setString(7, aclass);
      ps.setFloat(2, atotal);
      ps.setFloat(3, achinese);
      ps.setFloat(4, amath);
      ps.setFloat(5, aenglish);
      ps.setFloat(6, acomposite);
      ps.executeUpdate();
      addflag = true;
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (ps != null)try {
        ps.close();
      }
      catch (SQLException ignore) {}
      if (con != null)try {
        con.close();
      }
      catch (SQLException ignore) {}
      finally {
        releaseConnection("Access", con);
      }
    }
    return addflag;
  }

  //修改學生基本信息
  public boolean updateUser(String aname, int astuid, String aclass,
                            String asex, Date abirthday, String anativeplace,
                            String aethnic) {
    Connection conn = null;
    boolean updflag = false;
    ResultSet rs = null;
    try {
      conn = getConnection("Access");
      //聲明可更新的結果集
      Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_UPDATABLE);
      if (aclass.equals("A2241")) {
        rs = stat.executeQuery("select * from stuinf_A2241 where stu_id = " +
                               astuid);
      }
      else if (aclass.equals("A2242")) {
        rs = stat.executeQuery("select * from stuinf_A2242 where stu_id = " +
                               astuid);
      }
      else rs = stat.executeQuery("select * from stuinf_A2243 where stu_id = " +
                                  astuid);

      while (rs.next()) {
        if (!aname.equals("")) {
          rs.updateString("name", aname);
          rs.updateRow();
        }
        if (!aclass.equals("")) {
          rs.updateString("class", aclass);
          rs.updateRow();
        }
        if (!asex.equals("")) {
          rs.updateString("sex", asex);
          rs.updateRow();
        }
        if (!anativeplace.equals("")) {
          rs.updateString("nativeplace", anativeplace);
          rs.updateRow();
        }
        if (!aethnic.equals("")) {
          rs.updateString("ethnic", aethnic);
          rs.updateRow();
        }
        if (abirthday != null) {
          rs.updateDate("birthday", abirthday);
          rs.updateRow();
        }

      }
      updflag = true;

    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    finally {
      if (rs != null)try {
        rs.close();
      }
      catch (SQLException ignore) {}