package stu;
 
 import java.sql.*;
import java.io.*; 

public class marking  {
	String tea_id;
	String coursename;
	String score;
	String stu_id;
	public void setStu_id(String id){this.stu_id=id;}
	public String getStu_id(){return stu_id;}
	public void setScore(String sc){this.score=sc;}
	public String getScore(){return score;}
	public void setTea_id(String id){this.tea_id=id;}
    public String getTea_id(){return tea_id;}
    public void setCoursename(String c){this.coursename=c;}
    public String getCoursename(){return coursename;}
    
    public ResultSet getStudents(){   	
    String sql=" select stu_id "+
		"from enrol "+
		"where accept=1 "+
		"and class_id IN( "+
		"select classes.id "+
		"from classes ,course "+
		"where course.id=classes.cour_id "+
		"and course.name='"+coursename+"' "+
		"and classes.tea_id='"+tea_id+"') ";		
         sqlBean db = new sqlBean();
        ResultSet rs=db.executeQuery(sql);   
        return rs; 
    	                           }
    	                           
    public void addmark(){
    	int mark=0;
    	int mark1=0;
    	String sql="select mark from student  where id='"+stu_id+"' ";
    	sqlBean db = new sqlBean();
    	 try{
    	 	
    	 ResultSet rs = db.executeQuery(sql);
    	 if(rs.next()) {  mark = rs.getInt("mark");}
        	
            } 
         catch(SQLException ex){ 
             System.err.println("學生成績查詢有錯誤:"+ex.getMessage() ); 
              System.out.print("學生成績查詢有錯誤:"+ex.getMessage());//輸出到客戶端 
                               }
                               
    	 sql="select mark from course where name='"+coursename+"'  ";
    	 try{
    	 	
    	 ResultSet rs=db.executeQuery(sql);
    	 if(rs.next()) 
        	{  mark1=rs.getInt("mark");   }
        	
            }
          catch(SQLException ex){ 
             System.err.println("成績查詢有錯誤:"+ex.getMessage() ); 
              System.out.print("成績查詢有錯誤:"+ex.getMessage());//輸出到客戶端 
                               } 
    	 mark = mark+mark1;
    	 sql="update student set mark='"+mark+"' where id='"+stu_id+"'  ";
    	 db.executeInsert(sql);
    	 
    	         
    	                 }    	                           

    public void updatemark(){
    	 int temp=0;
    	 try{
	 	 temp = Integer.parseInt(score)  ;    
	 	   }
	 	 catch(NumberFormatException e){
	 	 	System.err.println("成績輸入有錯誤:"+e.getMessage() ); 
            System.out.print("成績輸入有錯誤:"+e.getMessage());//輸出到客戶端 
	 	 	                           }
	 	  if(temp>60) 
	 	  {addmark();}	 
        String sql="update enrol set score='"+temp+"'  "+
		"where stu_id='"+stu_id+"' "+
		"and class_id IN( "+
		"select classes.id "+
		"from classes,teacher,course "+
		"where classes.tea_id='"+tea_id+"' "+
		"and course.id=classes.cour_id "+
		"and course.name='"+coursename+"' )  ";
		sqlBean db = new sqlBean();
	 	 db.executeInsert(sql); 
	 	 	                           
         	 	 	                           
	 	             
    	                    }    	                           
    
    
}