package info;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import javax.sql.DataSource;

public class index extends HttpServlet {
  private static final String CONTENT_TYPE = "text/html; charset=GB2312";
  private DataSource dataSource=null;
  //Initialize global variables
  public void init() throws ServletException {
    Init init=new Init(getServletContext().getRealPath("/"));
    dataSource=LinkDB.getDB();
    if (dataSource==null) {
      LinkDB.setDB(init.getDriverName(),init.getDBURL(),init.getDBUser(),init.getDBPassword());
      dataSource=LinkDB.getDB();
    }
  }
  //Process the HTTP Get request
  public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);
    response.sendRedirect("index.jsp");
  }
  //Process the HTTP Post request
  public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    request.setCharacterEncoding("GB2312");
    response.setContentType(CONTENT_TYPE);
    PrintWriter out=response.getWriter();
    String id=request.getParameter("id").replaceAll("'","''");
    String kind=request.getParameter("search_kind");
    ReadDB readDB=new ReadDB();
    int num=readDB.getNum();
    Connection conn=null;
    Statement stmt=null;
    ResultSet rs=null;
    try{
      conn=dataSource.getConnection();
      stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
      if (kind.equals("1")) {
        rs=stmt.executeQuery("select * from info_table1,info_table2 where info_table1.id=info_table2.id and info_table1.s_name='"+id+"'");
      }
      else if (kind.equals("2")) {
        rs=stmt.executeQuery("select * from info_table1,info_table2 where info_table1.id=info_table2.id and info_table1.id='"+id+"'");
      }
      else {
        out.println("<script>alert('請指定搜索條件');document.location='javascript:history.go(-1);'</script>");
        return;
      }
      out.println("<table border=1>");
      out.println("<tr><td>學號</td><td>姓名</td><td>性別</td><td>年級</td><td>出生日期</td>");
      for(int i=1;i<=num-2;i++)
        out.println("<td>class"+i+"</td>");
      out.println("<td>時間</td><td>管理</td></tr>");
      while(rs.next()){
        out.println("<tr>");
        out.println("<td>"+rs.getString("id").trim()+"</td>");
        out.println("<td>"+rs.getString("s_name").trim()+"</td>");
        out.println("<td>"+rs.getString("s_sex").trim()+"</td>");
        out.println("<td>"+rs.getString("s_grade").trim()+"</td>");
        out.println("<td>"+rs.getString("s_birth").trim()+"</td>");
        for(int i=1;i<=num-2;i++)
          out.println("<td>"+rs.getFloat("class"+i)+"</td>");
        out.println("<td>"+rs.getString("s_time").trim()+"</td>");
        out.println("<td><a href=manage?action=change&id="+rs.getString("id").trim()+" target=_blank>修改</a>/<a href=manage?action=del&id="+rs.getString("id").trim()+" target=_blank>刪除</a></td>");
      }
      out.println("</table>");
      rs.close();
    }
    catch(SQLException e){
      out.println(e);
    }
    finally{
      try{
        stmt.close();
        conn.close();
        LinkDB.shutdownDataSource(dataSource);
      }
      catch(SQLException e){
        out.println(e);
      }
    }
  }
}