package cn.com.edu.gui.contral;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import cn.com.edu.gui.model.DBConnectionManager;
  /*
   * 成員注冊功能模塊
   * true :注冊成功;false:注冊失敗; 
   */
public class MemberManage extends SubClassMember{
  public boolean addMember(MemberValues mv){
    boolean flag = false;
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    PreparedStatement pstmt = null;
    try{
      pstmt = con.prepareStatement("insert into members values(?,?,?,?,?,?,?,?,to_date(?,'YYYY-MM-DD'),?,to_date(?,'YYYY-MM-DD'),?,?)");
      pstmt.setString(1,mv.getAccount());
      pstmt.setString(2,mv.getPassword());
      pstmt.setString(3,mv.getName());
      pstmt.setString(4,mv.getMajor());
      pstmt.setString(5,mv.getSex());
      pstmt.setInt(6,mv.getPhone());
      pstmt.setString(7,mv.getEmall());
      pstmt.setInt(8,mv.getQq());
      pstmt.setString(9,mv.getBirth());
      pstmt.setString(10,mv.getDormitory());
      pstmt.setString(11,mv.getJoinDate());
      pstmt.setString(12,mv.getDeptAssn());
      pstmt.setString(13,mv.getAdmin());
      int count = pstmt.executeUpdate();
      System.out.print(count);
      if(count!=0){                    //不為0時表示記錄已寫入數(shù)據(jù)庫中;
        flag=true;
        System.out.println(count);
      } 
    }catch(Exception e){
      e.getMessage();
    }finally{
      if(con != null){
        con = null;
      }
      if(pstmt != null){
        pstmt = null;
      }
    } 
    return flag;
    
   
    
  }
  /*
   * 檢測數(shù)據(jù)庫中是否存在相同的帳號;
   * true : 不存在相同帳號，可以注冊;false :存在相同帳號,不能注冊;
   */
  public boolean selectMember(String input){
    String str = "'"+input+"'";
    Vector v = new Vector();
    boolean flag = false;
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    try{
      Statement stmt = con.createStatement();
      ResultSet set = stmt.executeQuery("select * from members where maccount="+str);
      while(set.next()){
        String account = set.getString(1);
        v.addElement(new MemberValues(account));
      }
    }catch(Exception e){
      System.out.print(e.getMessage());
    }
    if(v.size() != 0){
      flag = true;
    }
    return flag;
  }
  public Vector getAssnName(){
    Vector v = new Vector();
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    Statement stmt = null;
    try{
      stmt = con.createStatement();
      ResultSet set = stmt.executeQuery("select mDeptAssn from members");
      while(set.next()){
        String mdeptAssn = set.getString("mdeptAssn");
        v.addElement(mdeptAssn);
      }
    }catch(Exception e){
      
    }
    return v;
  }
  public Vector getMadmin(){
    Vector v = new Vector();
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    Statement stmt = null;
    try{
      stmt = con.createStatement();
      ResultSet set = stmt.executeQuery("select mAdmin from members");
      while(set.next()){
        String mAdmin = set.getString("mAdmin");
        v.addElement(mAdmin);
      }
    }catch(Exception e){
      
    }
    return v;
  }
  public boolean checkAccount(String input1,String input2){
    String str = "'"+input1+"'";
    String str1 = "'"+input2+"'";
    Vector v = new Vector();
    boolean flag = false;
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    try{
      Statement stmt = con.createStatement();
      ResultSet set = stmt.executeQuery("select * from members where maccount="+str+"and mpassword ="+str1);
      while(set.next()){
        String account = set.getString(1);
        String password = set.getString(2);
        v.addElement(new MemberValues(account,password));
      }
    }catch(Exception e){
      System.out.print("數(shù)據(jù)操作異常");
    }
    if(v.size() != 0){
      flag = true;
    }
    return flag;
  }
  public boolean deleteMember(String input1,String input2){
    String str = "'"+input1+"'";
    String str1 = "'"+input2+"'";
    boolean flag = false;
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    try{
      PreparedStatement pstm = null;
      pstm = con.prepareStatement("delete members where maccount="+str+"and mpassword ="+str1);
      int count = pstm.executeUpdate();
      if(count != 0){
        flag = true;
      }
    }catch(Exception e){
      System.out.print(e.getMessage());
    }
    return flag;
  }
  public boolean updateMember(MemberValues value){
   
    boolean flag = false;
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    try{
      PreparedStatement pstm = null;
      pstm = con.prepareStatement("update members set mAdmin = ? where maccount = ?");
      try{
        pstm.setString(1,value.getAdmin());
        pstm.setString(2,value.getAccount());
        int count = pstm.executeUpdate();
        if(count != 0){
          flag = true;
        }
      }catch(Exception e){
        System.out.println(e.getMessage());
      }
    }catch(Exception e){
      System.out.print(e.getMessage());
    }
    return flag;
  }
  public boolean updateMemberPassword(MemberValuesOther value){
    boolean flag = false;
    DBConnectionManager db = DBConnectionManager.getInstance();
    Connection con = db.getConnection("oracle");
    try{
      PreparedStatement pstm = null;
      pstm = con.prepareStatement("update members set mPassword = ? where maccount = ?");
      try{
        pstm.setString(1,value.getPasswordNew());
        pstm.setString(2,value.getAccount());
        int count = pstm.executeUpdate();
        if(count != 0){
          flag = true;
        }
      }catch(Exception e){
        System.out.println(e.getMessage());
      }
    }catch(Exception e){
      System.out.print(e.getMessage());
    }
    return flag;
  }
}