package scoreInfo;

import java.sql.*;

public class checkEnrol{
	
	public boolean hasPassPrepare(String prepare){   //是否完成预修课
	  boolean f=false;
	  int a=0;
	  String temp=null;
	  String sql="select score "+
				"from enrol,classes ,course "+
				"where   enrol.class_id=classes.id "+
				"and classes.cour_id=course.id "+
				"and course.id='"+prepare+"' ";
      sqlBean db=new sqlBean();
      try{
      	ResultSet rs = db.executeQuery(sql);
      	if(rs.next())//获取enrol表中的score,若score为null或空，则还没有注册
      	temp=rs.getString("score");
      	}catch(SQLException e){System.out.print(e.toString());}
      	if(temp==null || temp.equals(""))
      	return false;
      	else{
      	try{
      	 a=Integer.parseInt(temp);	
      		} catch(NumberFormatException e){System.out.print(e.toString());}
      	if(a>=60) f=true;}
      	//获取enrol表中的score,若score大于60分，则还注册
      	return f;	
		}
	

  	 	            
	public boolean hasLogin(String stu_id,String cour_id){  
		//该学生是否已经注册该课程
  	boolean f=true;
   String sql="select stu_id,class_id "+
			"from enrol,classes "+
			"where stu_id='"+stu_id+"' "+
			"and classes.id=enrol.class_id "+
			"and cour_id='"+cour_id+"' ";
  	sqlBean db =new sqlBean();
  	try{
  	ResultSet rs=db.executeQuery(sql);
  	if(rs.next()){ f=false;}///若该学生已经注册该课程，则为false
  	else{ f=true;}///若该学生还未注册该课程，则为true
  	}catch(Exception e){ e.getMessage();}
  	return f;
  	                       }
  	                       
  	      
   public ResultSet getCourse()
   //getCourse(String stu_id)
    { 
/*
String sql="select DISTINCT course.id,course.name,course.prepare,course.dep , "+
			"classes.id as class_id,classes.room_id,classes.cour_time,teacher.name as tea_name "+
			"from course,classes,teacher "+
			"where classes.cour_id=course.id  "+
			"and classes.tea_id=teacher.id "+
 			"and classes.id in "+
			"( select classes.id from classes,student,course  "+
			"where classes.id not in( select class_id from enrol where stu_id='"+stu_id+"') "+
			"and ( student.department=course.dep or course.dep ='public')  "+
			"and course.id=classes.cour_id and student.id='"+stu_id+"') ";
*/
	   String sql="select  *  from course ";
  	  sqlBean db = new sqlBean();
  	ResultSet rs=  db.executeQuery(sql);
  	return rs;
  	     } 
  	                          
  	   
   public ResultSet  getCourse(String stu_id) { 

      /* String sql="select DISTINCT course.id,course.name,course.prepare,course.dep , "+
			"classes.id as class_id,classes.room_id,classes.cour_time,teacher.name as tea_name "+
			"from course,classes,teacher "+
			"where classes.cour_id=course.id  "+
			"and classes.tea_id=teacher.id "+
 			"and classes.id in "+
			"( select classes.id from classes,student,course  "+
			"where classes.id not in( select class_id from enrol where stu_id='"+stu_id+"') "+
			"and ( student.department=course.dep or course.dep ='public')  "+
			"and course.id=classes.cour_id and student.id='"+stu_id+"') ";
  */
	   String sql="select DISTINCT course.id,course.name,course.prepare,course.dep , "+
		"classes.id as class_id,classes.room_id,classes.cour_time,teacher.name as tea_name "+
		"from course,classes,teacher "+
		"where classes.cour_id=course.id  "+
		"and classes.tea_id=teacher.id "+
		"and classes.id in "+
		"( select classes.id from classes,student,course  "+
		"where classes.id not in( select class_id from enrol where stu_id='"+stu_id+"') "+
		"and course.id=classes.cour_id ) ";////and student.id='"+stu_id+"'
  	  sqlBean db = new sqlBean();
  	ResultSet rs=  db.executeQuery(sql);
  	return rs;
  	     }  
   
    
	                             
  	           
   
   
  public String getTotalMark(String id){/////此处参数值id为学生学号
    	String temp="0";
  	  String sql="select mark from student where id='"+id+"'  ";
  	  sqlBean db = new sqlBean();
      ResultSet rs=  db.executeQuery(sql); 	
  	     try{
  	     	if(rs.next())
  	     	temp=rs.getString("mark");
  	     	} catch(Exception e){}
  	     	System.out.println(temp);
  	     	return temp;	
  	     	}
 	 
 	 public int updatestu(String pw,String id,String e_mail,String tel){
 	 	int num=0;
 	 
        String sql="update student set password='"+pw+"',e_mail='"+e_mail+"',tel='"+tel+"'  where name='"+id+"'  ";         
 	 	sqlBean db = new sqlBean();
 	 	num=db.executeInsert(sql);
 	 	return num;
 	 	
 	 	}
 	 	
 	 
 	 public int enrol(String class_id,String stu_id){
	        int num=0;    
         String sql="insert into enrol(stu_id,class_id) "+
         " VALUES('"+stu_id+"','"+class_id+"')   ";
           sqlBean db = new sqlBean();
          num= db.executeInsert(sql); 
          return num;
         }
 	 
 	 		                    
  	 public int enrol1(String class_id,String stu_id,String cour_id){
  	        int num=0;    
            String sql="insert into enrol(stu_id,class_id,courid) "+
            " VALUES('"+stu_id+"','"+class_id+"','"+cour_id+"')   ";
              sqlBean db = new sqlBean();
             num= db.executeInsert(sql); 
             return num;
            }
  	 
  	 
  	 
                  }