package server.pool;
import java.sql.SQLException;
import java.util.Date;
import java.io.BufferedReader;
import java.io.PrintWriter;
import java.sql.Connection;
import java.net.Socket;
import java.io.IOException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.io.InputStreamReader;
import java.io.FileReader;
import java.sql.PreparedStatement;
import java.util.Vector;
import java.io.FileOutputStream;

/**
 * 用來創建工作線程
 * @author not attributable
 * @version 1.0
 */
class WorkThread implements Runnable{
Socket socket=null;
BufferedReader bufin;
PrintWriter prout;
DataBaseConnection DBPool;
Connection conn;
String ID;
private Vector userContainer;
CloseBean closed;
/**
 * 構造函數
 * @param socket Socket
 */
public WorkThread(Socket socket,Vector userContainer,CloseBean closed) {
    this.socket=socket;
    this.closed=closed;
    this.userContainer=userContainer;
try {
    bufin = new BufferedReader(new InputStreamReader(socket.getInputStream()));
    prout = new PrintWriter(socket.getOutputStream());
} catch (IOException ex) {
    System.out.println("讀取流失敗");
}
  DBPool=DataBaseConnection.getInstance();
  conn=DBPool.getConnection();
}
/**
 * 實現Runnable接口，服務器的主要工作線程
 */
public void run(){
    String cmd="";
 while(!closed.isClosed()){       //服務器未關閉
        try {
          cmd=bufin.readLine();    //接收客戶端命令
        } catch (IOException ex) {
          cmd="";
          System.err.println(ex.getMessage());
          break;
        }
        if(cmd.equals("close")){      //關閉連接命令
         System.out.println(cmd);     //測試用
          break;
        }
        else if(cmd.equals("read")){    //讀取習題命令
           System.out.println(cmd);    //測試用
             send();
        }
        else if(cmd.equals("write")){    //修改、添加命令
            System.out.println(cmd);     //測試用
                receive();
            }
        else if(cmd.equals("login")){    //登陸命令
               System.out.println(cmd);    //測試用
               login();
            }
        else if(cmd.equals("load")){    //下載試卷命令
             System.out.println(cmd);    //測試用
                load();
            }
            else if(cmd.equals("transfer")||cmd.equals("savetest")){     //上傳試卷命令
                System.out.println(cmd);     //測試用
                transfer();
            }
            else if(cmd.equals("readCourse")){
                System.out.println(cmd);     //測試用
                sendCourse();
            }
            else System.out.println("The error cmd:"+cmd); 
            try {
                 Thread.sleep(5000);
                 System.out.println("sleep");   //測試用
            } catch (InterruptedException ex2) {
              System.err.println(ex2.getMessage());
            }
  }
    try{
        userContainer.remove(ID);    //從userContainer中移除該用戶ID
        bufin.close();      //關閉輸入流
        prout.close();      //關閉輸出流
        DBPool.freeConnection(conn);   //釋放數據庫連接
        socket.close();     //關閉socket連接
  }catch(IOException ex){
      System.err.println(ex.getMessage());
    }
}
/**
 * 響應客戶端的讀取習題命令
 */
public void send(){
    String sql=null;
    ResultSet rt1;
    try {
        sql = bufin.readLine();       //接收客戶端的SQL語句
        Statement st = conn.createStatement();
        rt1 = st.executeQuery(sql);    //查詢數據庫
        while (rt1.next()) {
            prout.println(rt1.getString("id"));
            prout.println(rt1.getString("course_name"));
            prout.println(rt1.getString("type"));
            prout.println(rt1.getString("text"));
            prout.println("over");      //提示客戶端習題內容發送完畢
            prout.println(rt1.getString("answer"));
            prout.println("over");       //提示客戶端習題答案發送完畢
            prout.println(rt1.getString("chapter"));
            prout.println(rt1.getString("section"));
            prout.println(rt1.getString("difficulty"));
            prout.flush();
        }
        rt1.close();    //關閉結果集
        st.close();     //關閉 Statement
    } catch (IOException ex) {
        System.err.println(ex.toString());
    } catch (SQLException ex1) {
        System.err.println(ex1.toString());
    }finally{
        prout.println("End");        //提示客戶端習題發送完畢
        prout.flush();
    }
}
public void sendCourse(){
     String  sql="select * from course;";
      try {
          Statement st = conn.createStatement();
          ResultSet rt1 = st.executeQuery(sql);
         while(rt1.next()){
             prout.println(rt1.getString(1));
             prout.println(rt1.getString(2));
              prout.println(rt1.getString(3));
               prout.println(rt1.getString(4));
                prout.println(rt1.getString(5));
                 prout.println(rt1.getString(6));
             prout.flush();
         }
        prout.println("End");        //提示客戶端習題發送完畢
        prout.flush();
         rt1.close();
         st.close();
    } catch (SQLException ex1) {
        System.err.println(ex1.toString());
    }
}
/**
 * 響應客戶端的修改、添加命令
 */
public void receive(){
    Vector SQLbean=new Vector();
    String sql;
    try {
        while(true){
            sql=bufin.readLine();     //接收客戶端的SQL語句
            if(sql.equals("over")) break;
            SQLbean.addElement(sql);
        }
      Statement  st = conn.createStatement();
      for(int i=0;i<SQLbean.size();i++)
      {
        st.executeUpdate(SQLbean.elementAt(i).toString());  //執行SQL語句
        }
       st.close();
   }catch(SQLException ed){
       System.err.println(ed.toString());
   }catch(IOException ed){
        System.err.println(ed.toString());
   }
}
/**
 * 響應客戶端的登錄命令
 */
public void login(){
     String password;
     String sql;
     ResultSet rt1,rt2;
    try {
        ID = bufin.readLine();         //接收客戶端的登錄ID
        password=bufin.readLine();     //接收客戶端的密碼
    } catch (IOException ex) {
        prout.println("NET error");
        prout.flush();
        System.err.println(ex.toString());
        return;
    }
    for(int i=0;i<userContainer.size();i++){      //檢查該用戶ID是否已登錄
        if(ID.equals(userContainer.elementAt(i))){
            prout.println("該用戶已在異地登錄");
            prout.flush();
            return;
        }
    }
  sql="select * from user where user_id='"+ID+"' and password='"+password+"';";
    try {
        Statement  st = conn.createStatement();
         rt1=st.executeQuery(sql);   //查詢數據庫
        if (rt1.next()) {            // 用戶ID、密碼正確
            prout.println("OK");
            prout.flush();
            userContainer.addElement(ID);   //將用戶ID添加到userContainer中
            prout.println(rt1.getString(2));
            prout.println(rt1.getString(4));
            prout.println(rt1.getBoolean(5));
            prout.println(rt1.getString(6));
            prout.println(rt1.getString(7));
            rt2=st.executeQuery("select course_name from user_course where user_id='"+ID+"';");
            while(rt2.next()){
                 prout.println(rt2.getString(1));   //發送用戶所教課程名給客戶端
            }
            prout.println("over");
            prout.flush();
            rt2.close();
        }
        else {     // 用戶ID、密碼不正確
          prout.println("用戶ID或密碼出錯");
          prout.flush();
        }
      rt1.close();
      st.close();
    } catch (SQLException ex2) {
     prout.println("數據庫讀取失敗");
     prout.flush();
     System.err.println(ex2.toString());
    }
}
/**
 * 下載試卷到客戶端
 */
public void load(){
    String readin, filechar;
    String filepath,answerpath;
    Statement st,st2;
    ResultSet rt;
    int i;
    try {
        readin = bufin.readLine();    //接收客戶端的查詢語句
        st = conn.createStatement();
        rt=st.executeQuery(readin);
        BufferedReader fileread=null;
        BufferedReader answerread=null;
        while(rt.next()){
            try{
            filepath=rt.getString("path");
            fileread=new BufferedReader(new FileReader(filepath));
            answerpath=filepath.substring(0,filepath.length()-8)+"answer.txt";
            answerread=new BufferedReader(new FileReader(answerpath));
          }catch (IOException ex) {
                i=rt.getInt(1);
            //    System.err.println(ex.toString()+i);
                try{
                   st2= conn.createStatement();
                   st2.executeUpdate("delete from test where test_id=" + i +";");
                   st2.close();
              }catch (SQLException  exl) {
                  System.err.println(exl.toString()+i);
              }
                continue;
            }
            prout.println(rt.getString("course_name"));
            prout.println(rt.getString("user_name"));
            prout.println(rt.getString("test_type"));
            prout.println(rt.getString("date"));
            prout.flush();
            while((filechar=fileread.readLine())!=null){
               prout.println(filechar);
               prout.flush();
            }
            prout.println("fileover");
            prout.flush();
            fileread.close();
            fileread=null;
            while((filechar=answerread.readLine())!=null){
                prout.println(filechar);
                prout.flush();
            }
            prout.println("answerover");
            prout.flush();
            answerread.close();
            answerread=null;
        }
        rt.close();
        st.close();
      } catch (SQLException ex1) {
          System.err.println(ex1.toString());
      }catch (IOException ex) {
           System.err.println(ex.toString());
     }finally{
        prout.println("End");
        prout.flush();
    }
}
/**
 * 響應客戶端上傳試卷
 */
public void transfer(){
    String readin,course_name,user_name,type,path;
    String sql="select test_id from test;";
    int test_id;
    String filename;
   PrintWriter fileout,answerout;
    Date datetime=new Date();
    java.text.SimpleDateFormat formatter = new java.text.SimpleDateFormat("yyyy-MM-dd");
    String date=formatter.format(datetime);           //獲得當前系統時間
    try {
        Statement st = conn.createStatement();
        ResultSet rt=st.executeQuery(sql);
        if(rt.next()){
            rt.last();        //取最后一條記錄
            test_id = rt.getInt(1);
        }else test_id=0;
        rt.close();
        st.close();
        sql="insert into test(course_name,user_name,test_type,path,date) values(?,?,?,?,'"+date+"');";
        PreparedStatement pst=conn.prepareStatement(sql);    //將上傳試卷屬性插入到數據庫中去
            course_name= bufin.readLine();    //獲得課程名屬性
            test_id++;
            pst.setString(1,course_name);
            user_name=bufin.readLine();      //獲得創建者屬性
            pst.setString(2,user_name);
            type=bufin.readLine();        //獲得試卷類型屬性
            pst.setString(3,type);
            filename=test_id+"test.txt";
            path="test/"+filename;
            pst.setString(4,path);     //設計試卷路徑屬性
            pst.executeUpdate();   //執行插入語句
            pst.close();
           fileout=new PrintWriter(new FileOutputStream(path));
           while(true){
                readin=bufin.readLine();
                if(readin.equals("over"))break;
                fileout.println(readin);
                fileout.flush();
           }
      fileout.close();
      path=path.substring(0,path.length()-8)+"answer.txt";
      answerout=new PrintWriter(new FileOutputStream(path));
                     while(true){
                          readin=bufin.readLine();
                          if(readin.equals("over"))break;
                          answerout.println(readin);
                          answerout.flush();
                     }
                answerout.close();
    } catch (IOException ex) {
         System.err.println(ex.toString());
    }catch (SQLException ex1) {
         System.err.println(ex1.toString());
    }
}
}