package dengjiKaoShi;
import conn.DataBaseConnection;
import java.sql.Date;
import java.sql.*;
import java.util.*;
import java.io.*;
public class DengjiKaoShiBean
{
	private Connection con=null;
	public DengjiKaoShiBean()
	{
		this.con=DataBaseConnection.getConnection();
	}
	
	//是否存在學生
	public boolean existXuesheng(DengjiKaoShi dengjiKaoShi) {
        boolean bool=false;
        try {
            String studentId = dengjiKaoShi.getStudentId();
            PreparedStatement pstmt = con.prepareStatement(
                    "select studentId from xuesheng where studentId='" +
                    studentId + "' and flag='"+1+"'");
            ResultSet rst = pstmt.executeQuery();
            if (rst.next()) {
                bool=true;
            }
            if(rst!=null){
        	rst.close();
        	}
        	if(pstmt!=null){
        		pstmt.close();
        	}
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        
        return bool;
    }
    
    //記錄是否存在
    public boolean existDengjiKaoShi(DengjiKaoShi dengjiKaoShi) {
        try {
            String studentId = dengjiKaoShi.getStudentId();
            String examTime = dengjiKaoShi.getExamTime();
            PreparedStatement pstmt = con.prepareStatement(
                    "select * from dengjiKaoShi where studentId='" +
                    studentId + "' and examTime='"+examTime+"'");
            ResultSet rst = pstmt.executeQuery();
            if (rst.next()) {
                return true;
            }
        } catch (Exception ex) {
            return false;
        }
        return false;
    }
    
	//插入
	public String addDengjiKaoShi(DengjiKaoShi dengjiKaoShi)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			if (!existXuesheng(dengjiKaoShi)) {
                return "notExist"; //不存在此學生
            }
            if (existDengjiKaoShi(dengjiKaoShi)) {
                return "exist"; //此學生計算機等級記錄已存在
            }
			pstmt=con.prepareStatement("insert into dengjiKaoShi values(?,?,?,?,?)");
			pstmt.setString(1,dengjiKaoShi.getStudentId());
			pstmt.setString(2,dengjiKaoShi.getRankType());
			pstmt.setString(3,dengjiKaoShi.getExamTime());
			pstmt.setString(4,dengjiKaoShi.getExamAddress());
			pstmt.setString(5,dengjiKaoShi.getScore());
			pstmt.execute();
			str="sucess";
		}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}

  	    	}
  	    return str;	
	}
	//更新
	public String updateDengjiKaoShi(DengjiKaoShi dengjiKaoShi)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("update dengjiKaoShi set rankType=?,examAddress=?,score=? where studentId=? and examTime=?");
			pstmt.setString(1,dengjiKaoShi.getRankType());
			pstmt.setString(2,dengjiKaoShi.getExamAddress());
			pstmt.setString(3,dengjiKaoShi.getScore());
			pstmt.setString(4,dengjiKaoShi.getStudentId());
			pstmt.setString(5,dengjiKaoShi.getExamTime());
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		 
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}

  	    	}
  	    return str;	
	}

	//刪除
	public String deleteDengjiKaoShi(String studentId,String examTime)
	{
		PreparedStatement pstmt=null;
		String str="";
		try
		{
			pstmt=con.prepareStatement("delete from dengjiKaoShi where studentId=? and examTime=?");
			pstmt.setString(1,studentId);
			pstmt.setString(2,examTime);
			pstmt.execute();
			str="sucess";
		}
		catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    		str="failer";
  	    	}
  	    	finally
  	    	{
  	    		try{
  	    		 if(pstmt!=null){
  	    		 	pstmt.close();
  	    		 }
  	    		 if(con!=null){
  	    		 	con.close();
  	    		 }
  	    		}catch(Exception e)
  	    		{
  	    			e.printStackTrace();
  	    		}

  	    	}
  	    return str;	
	}
	//查詢
	public ResultSet executeQuery(String sql)
  	{
  	     Statement stmt=null;
  	     ResultSet rst=null;
  	     try{
	  	      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  	      rst=stmt.executeQuery(sql);
  	    	}catch(SQLException ex)
  	    	{
  	    		System.err.println(ex.getMessage());
  	    	}
  	    	return rst;
  	 }
  	 //關閉
  	 public void close()
        {
        	try{
        		con.close();
        	}
        	catch(SQLException e)
        	{
        	   e.printStackTrace();
        	}

        }
        
     //所有班級
     public String getAllClass()
  {
  	Statement stmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  rst=stmt.executeQuery("select distinct classId,className from banji");
      while(rst.next()) { 
        String classId= rst.getString("classId");
        String className= rst.getString("className");
        sBuf.append("<option value='"+classId+"'>"+className+"</option>\n");
        }
    }
    catch (SQLException ex) {
      return "";
    }
    return sBuf.toString();
  }
  
  //所有等級考試
  public String getAllRankType()
  {
  	Statement stmt = null;
    ResultSet rst = null;
    StringBuffer sBuf=new StringBuffer();
    try {
      stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	  rst=stmt.executeQuery("select distinct rankType from dengjiKaoShi");
      while(rst.next()) { 
        String rankType= rst.getString("rankType");
        sBuf.append("<option value='"+rankType+"'>"+rankType+"</option>\n");
        }
    }
    catch (SQLException ex) {
      return "";
    }
    return sBuf.toString();
  }
  
}