package myBean;
import java.sql.*;
import myBeans.SQLBean;

public class Student {
  private String id;
  private String password;
  private String name;
  private String sex;
  private String jiguan;
  private String classid;
  private String dep;
  private String assitant;
  
  private int age;
  private int entry;
  
  public void setAssitant(String s){assitant=s;}
  public String getAssitant(){return assitant;}
  public void setClassid(String s){classid=s;}
  public String getClassid(){return classid;}
  public void setAge(int age){this.age=age;}
  public int getAge(){return age;}
  public void setDep(String s){dep=s;}
  public String getDep(){return dep;}
  public void setSex(String s){sex=s;}
  public String getSex(){return sex;}	 
  public void setEntry(int year){entry=year;}
  public int getEntry(){return entry;} 
                                                                    
  public String getId() {
    return id;
  }
  public void setId(String id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public String getPassword() {
    return password;
  }
  public void setPassword(String password) {
    this.password = password;
  }

  public String getJiguan() {
    return jiguan;
  }
  public void setJiguan(String jiguan) {
    this.jiguan = jiguan;
  }
  
  
  public boolean hasLogin(String id){   //檢查該學(xué)生是否已經(jīng)注冊
  	boolean f=true;
   String sql="select * from student where ID ='"+id+"'";
  	SQLBean db =new SQLBean();
  	try{
  	ResultSet rs=db.executeQuery(sql);
  	if(rs.next()){ f=false;}
  	else{ f=true;}
  	}catch(Exception e){ e.getMessage();}
  	return f;
  }
  
  public ResultSet getInformation(String id){
    String sql = "select student.*,department.NAME from student,department where student.ID='"+id+"' and student.DEPARTMENT=department.ID";
    SQLBean db= new SQLBean();
		ResultSet rs = db.executeQuery(sql);
		return rs;
  }
  	                       
  public ResultSet  getStudent(String dep){
  	String sql = "";
  	if(dep.equals("0000")){
  	   sql="select student.*,department.NAME from student,department  where student.DEPARTMENT=department.ID";
  	   }
  	else{
		   sql="select student.*,department.NAME from student,department where student.DEPARTMENT=department.ID and DEPARTMENT='"+dep+"'";
		   }
		SQLBean db= new SQLBean();
		ResultSet rs = db.executeQuery(sql);
		 return rs;
	} 
	
	public ResultSet getStu_id(String class_id){
		String sql = "select ID,NAME from student where student.CLASS_ID='"+class_id+"' and FLAG=1";
		SQLBean db = new SQLBean();
		ResultSet rs = db.executeQuery(sql);
		return rs;
	}	 
	
	public ResultSet getCour_Stu(String cour_id){
		String sql = "select student.*,department.NAME from student,department,teach where student.DEPARTMENT=department.ID and teach.CLASS_ID=student.CLASS_ID and teach.COUR_ID='"+cour_id+"'";
	  SQLBean db = new SQLBean();
	  ResultSet rs = db.executeQuery(sql);
	  return rs;
	}                        
		  
	public ResultSet getMark(String id, String sem){
		String sql = "select NAME,CREDIT,GRADE from course,stu_course where course.ID=stu_course.COURSEID and course.SEMESTER='"+sem+"' and stu_course.STUID='"+id+"'";
	  SQLBean db = new SQLBean();
	  ResultSet rs = db.executeQuery(sql);
	  return rs;
	}
	                       
	public String updateStudent(){
    	
      String sql="update student "+
                 " set NAME='"+name+"',SEX='"+sex+"',DEPARTMENT='"+dep+"',	 "+
                 "PASSWORD='"+password+"',PROVINCE='"+jiguan+"',CLASS_ID='"+classid+"',ENTRY_TIME='"+entry+"',AGE='"+age+"'  "+
                 " where ID='"+id+"' ";
/*      try{ 
        Class.forName("com.mysql.jdbc.Driver"); 
       } 
      catch(java.lang.ClassNotFoundException e){ 
        System.err.println("加載驅(qū)動器有錯誤:"+e.getMessage( )); 
        System.out.print("執(zhí)行插入有錯誤:"+e.getMessage());//輸出到客戶端 
      }
      try{ 
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/studb","root","125125");
Statement stmt=conn.createStatement( ); 
stmt.executeUpdate(sql); 
} 
catch(SQLException ex){ 
System.err.println("執(zhí)行插入有錯誤:"+ex.getMessage() ); 
System.out.print("執(zhí)行插入有錯誤:"+ex.getMessage());//輸出到客戶端 
}*/
      SQLBean db= new SQLBean();
      db.executeInsert(sql);
      return sql;
}	    
  			                         		                                                 	                       	                       
   public void deleteStudent(){    
   	  String class_id=id.substring(0,10);
    	String sql="delete  from student where ID ='"+id+"' ";
    	SQLBean db= new SQLBean();
    	db.executeDelete(sql);
    	sql="update class set LEAVECOUTN=LEAVECOUNT+1 where ID='"+class_id+"'";
    	db.executeInsert(sql);
    	sql="update class set COUNT=COUNT-1 where ID='"+class_id+"'";
    	db.executeInsert(sql);
    	
   }                       
  	       
  public int deleteStudent(String id){
    	int num=0;
    	String class_id=id.substring(0,10);
    	String sql="delete from student where ID = '"+id+"'";
    	SQLBean db = new SQLBean();
    	num=db.executeDelete(sql);
    	sql="update class set LEAVECOUNT=LEAVECOUNT+1 where ID='"+class_id+"'";
    	db.executeInsert(sql);
    	sql="update class set COUNT=COUNT-1 where ID='"+class_id+"'";
    	db.executeInsert(sql);
    	return num;
  }    
                      	                       
  public String addStudent()  {
    String sql="insert into student(NAME,PASSWORD,ID,SEX,DEPARTMENT,PROVINCE,CLASS_ID,ENTRY_TIME,AGE)  "+	
            "VALUES('"+name+"','"+password+"','"+id+"','"+sex+"','"+dep+"','"+jiguan+"','"+classid+"','"+entry+"','"+age+"') ";
            SQLBean db =new SQLBean();
           db.executeInsert(sql);
           sql="update class set COUNT=COUNT+1 where ID='"+classid+"'";
           db.executeInsert(sql);
         return sql;
 }
                                                   	
}