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

public class AwardPC {

  private String stu_id;
  private String levels;
  private String record_time;
  private String info;
  private String validate;
  
  public void setStu_id(String s){stu_id=s;}
  public String getStu_id(){return stu_id;}
  public void setLevels(String s){levels=s;}
  public String getLevels(){return levels;}
  public void setRecord_time(String date){record_time=date;}
  public String getRecord_time(){return record_time;} 
  public void setInfo(String info){this.info = info;}
  public String getInfo(){return info;}
  public void setValidate(String validate){this.validate=validate;}
  public String getValidate(){return validate;}
  
  public String findTable(String code){
    if(code.startsWith("A"))
       return "award";
    else if(code.startsWith("P"))
       return "punishment";
    else
       return "changes";
  }
                                                                    
  public boolean hasLogin(String stu_id,String record_time,String levels){   //檢查該記錄是否已經(jīng)存在
  	boolean f=true;
  	String table = findTable(levels);
    String sql="select ID from "+table+" where STUID ='"+stu_id+"' and RECORD_TIME='"+record_time+"'";
  	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  getAwardPC(String dep,String s){
  	String sql = "";
  	String table1 = "",table2="";    if(s.equals("A")){    	table1 = "award";
    	table2 = "award_levels";    }else if(s.equals("P")){    	table1 = "punishment";
    	table2 = "punish_levels";    }else{      table1 = "changes";
      table2 = "change_code";    }
  	if(dep.equals("0000")){
  	   sql="select STUID,student.NAME NAME,SEX,DESCRIPTION,RECORD_TIME,CLASS_ID,department.NAME DEPARTMENT,INFOS,VALIDATE,CODE from "+table1+" apc,student,"+table2+" apc_levels,department where student.ID=apc.STUID and apc.LEVELS=apc_levels.CODE and student.DEPARTMENT=department.ID";
  	   }
  	else{
		   sql="select STUID,student.NAME NAME,SEX,DESCRIPTION,RECORD_TIME,CLASS_ID,department.NAME DEPARTMENT,INFOS,VALIDATE,CODE from "+table1+" apc,student,"+table2+" apc_levels,department where student.ID=apc.STUID and apc.LEVELS=apc_levels.CODE and student.DEPARTMENT=department.ID and student.DEPARTMENT='"+dep+"'";
		   }
		SQLBean db= new SQLBean();
		ResultSet rs = db.executeQuery(sql);
		 return rs;
	} 
		                         
		                         
	public void updateAwardPC(){
    	String table = findTable(levels);
      String sql="update "+table+
                 " set LEVELS='"+levels+"',INFOS='"+info+"',VALIDATE='"+validate+"' where STUID='"+stu_id+"' and RECORD_TIME='"+record_time+"'";
      SQLBean db= new SQLBean();
      db.executeInsert(sql);
  }	      			                         		                                                 	                       	                               
  	       
  public int deleteAwardPC(String stu_id,String record_time,String levels){
    	int num=0;
    	String table = findTable(levels);
    	SQLBean db = new SQLBean();
    	String sql="delete from "+table+" where STUID='"+stu_id+"' and RECORD_TIME='"+record_time+"'";
    	num=db.executeDelete(sql);
    	return num;
    	}  
    	                      	                       
  public int addAwardPC(){
  	int num = 0;
  	String table = findTable(levels);
    String sql="insert into "+table+"(STUID,LEVELS,RECORD_TIME,INFOS,VALIDATE)  "+	
            "values('"+stu_id+"','"+levels+"','"+record_time+"','"+info+"','"+validate+"') ";
    SQLBean db =new SQLBean();
    num=db.executeInsert(sql);
    if(validate.trim().equals("是")){
       if(levels.startsWith("C")){
    	     sql="select DESCRIPTION from change_code where CODE='"+levels+"'";
    	 try{
    	     ResultSet rs = db.executeQuery(sql);
    	     if(rs.next()){
    	     	 String description = rs.getString("DESCRIPTION");
    	       if(description.trim().equals("復(fù)學(xué)")){
    		        sql="update student set FLAG=1 where ID='"+stu_id+"'";
    	       }else{
    	         sql="update student set FLAG=0 where ID='"+stu_id+"'";
    	      }
    	      db.executeInsert(sql);
    	    }
    	  }catch(SQLException e){e.getMessage();}
    	}
    }
    return num;
 }
                                                   
}