package org.mmxbb.exam.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.mmxbb.exam.bean.ExaminationPaper;
import org.mmxbb.exam.business.program.RandomDistributeExamPaperForm;
import org.mmxbb.exam.business.program.SearchActionForm;
import org.mmxbb.exam.util.DBConn;
import org.mmxbb.exam.util.GetQuestionCount;


public class ExaminationPaperDAO {
  private final static String GETALL_EXAMINATIONPAPER =
      "select * from EX_EXAMINATIONPAPER";
  private final static String GET_SHARCH_RESULT =
      "select e.e_id as e_id,e.e_name as e_name,e.e_type as e_type,b.b_value as e_typename,date_format(e.e_begin,'%Y-%m-%d') as e_begin,date_format(e.e_end,'%Y-%m-%d') as e_end,e.e_total as e_total,e.e_passvalue as e_passvalue,e.e_timer as e_timer,t.t_state as t_state from ex_examinationpaper e,ex_baseinfo b,ex_testpaper t where b.b_id=e.e_type and e.e_id=t.e_id and t.examinee_id=? and (now())<e.e_end and (now())>e.e_begin and t_state='096' ORDER BY E_ID DESC";
  private final static String UPDATE_PAPER =
      "UPDATE EX_EXAMINATIONPAPER SET E_NAME=?,E_TYPE=?,E_TIMER=?,E_IDLIST=?,E_BEGIN=date_format(?,'%Y-%m-%d'),E_END=date_format(?,'%Y-%m-%d'),E_EXAMINEELIST=?,E_PASSVALUE=?,E_TOTAL=?,E_STATE=? WHERE E_ID=?";
  private final static String UPDATE_PAPER2 =
      "UPDATE EX_EXAMINATIONPAPER SET E_NAME=?,E_TYPE=?,E_TIMER=?,E_IDLIST=?,E_BEGIN=(now()),E_END=(DATE_ADD(now(),INTERVAL 10 DAY )),E_EXAMINEELIST=?,E_PASSVALUE=?,E_TOTAL=?,E_STATE=? WHERE E_ID=?";
  private final static String UPDATE_PAPER_BASEINFO =
      "UPDATE EX_EXAMINATIONPAPER SET E_NAME=?,E_TYPE=?,E_TIMER=?,E_BEGIN=date_format(?,'%Y-%m-%d'),E_END=date_format(?,'%Y-%m-%d'),,E_PASSVALUE=?,E_TOTAL=?,WHERE E_ID=?";
  private final static String ADDPAPER =
      "INSERT INTO EX_EXAMINATIONPAPER (E_ID,E_IDLIST,E_BEGIN,E_END) VALUES ('','@@0@@0@@0@@0',(now()),(DATE_ADD(now(),INTERVAL 1 DAY )))";
  private final static String REMOVEEXAMINATIONPAPERBYKEY =
      "delete from EX_EXAMINATIONPAPER WHERE E_ID=?";
  private final static String UPDATE_E_STATE =
      "update ex_examinationpaper set e_state=? where e_id=?";
  private final static String GETBYKEY =
      "SELECT exam.e_id as e_id,exam.e_name as e_name,exam.e_type as e_type,exam.e_timer as e_timer,exam.e_idlist as e_idlist,date_format(exam.e_begin,'%Y-%m-%d') as e_begin,date_format(exam.e_end,'%Y-%m-%d') as e_end,exam.e_examineeList as e_examineeList,exam.e_passvalue as e_passvalue,exam.e_total as e_total,b2.b_value as e_state FROM EX_EXAMINATIONPAPER exam,EX_BASEINFO b2  WHERE exam.e_state=b2.b_id and exam.e_id=?";
  private final static String FINDBYE_STATE =
      "SELECT E_ID,E_NAME,E_TYPE,E_TIMER,E_IDLIST,date_format(E_BEGIN,'%Y-%m-%d') as E_BEGIN,date_format(E_END,'%Y-%m-%d') as E_END,E_EXAMINEELIST,E_PASSVALUE,E_TOTAL,E_STATE FROM EX_EXAMINATIONPAPER where e_state IS NULL";
  private final static String GET_BY_KEY_WHEN_ESTATE_NULL =
      "select e_id,e_name,e_type,e_timer,date_format(e_begin,'%Y-%m-%d') as e_begin,date_format(e_end,'%Y-%m-%d') as e_end,e_idlist,e_examineelist,e_passvalue,e_total,e_state from ex_examinationpaper where e_id=?";
  private final static String GET_IDLIST =
      "SELECT E_IDLIST FROM EX_EXAMINATIONPAPER WHERE E_ID = ?";
  private final static String CLEAR_IDLIST =
      "UPDATE EX_EXAMINATIONPAPER SET E_IDLIST = ? WHERE E_ID = ?";
  private final static String REMOVEEXAMINEE_LIST =
      "UPDATE EX_EXAMINATIONPAPER SET E_EXAMINEELIST='' WHERE E_ID=?";
  private final static String UPDATE_E_STATE_UNVALIDATE_EXAMPAPER =
      "UPDATE EX_EXAMINATIONPAPER SET e_state='040' where e_end<(now())";

  private Connection conn;
  private DBConn dbconn;
  private int length = 3;
  private int rowCount;
  private int pageCount;
  private String pagestr;
  private String conditionStr = "";

  //Connect to db!!
  public ExaminationPaperDAO() {
    dbconn = new DBConn();
    conn = dbconn.getConnection();
  }

  public void setLength(int length) {
    this.length = length;
  }

  public int getLength() {
    return length;
  }

  public String getConditionStr() {
    return (this.conditionStr);
  }

  public void setConditionStr(String conditionStr) {
    this.conditionStr = conditionStr;
  }

  //Add ExaminationPaper!
  public void addExamintionPaper(ExaminationPaper examinationPaper) {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(ADDPAPER);
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  public Collection getCanGrade(SearchActionForm searchForm, int ipage) throws
      SQLException {

    ArrayList src = (ArrayList)this.getSearch(searchForm, ipage);

    Collection res = new ArrayList();
    ExaminationPaper ePaper = new ExaminationPaper();
    for (int i = 0; i < src.size(); i++) {
      ePaper = (ExaminationPaper) src.get(i);

      if (ePaper.getE_state().equals("進行中") || ePaper.getE_state().equals("已結束")) {
        res.add(src.get(i));
      }
    }

    this.rowCount = res.size();
    int pagesize = getLength();
    if (getLength() < 1) {
      pagesize = rowCount;
      this.pageCount = 1;
    } else {
      this.pageCount = rowCount / getLength() +
          ( (rowCount % getLength()) > 0 ? 1 : 0);
    }
    return res;
  }

  public String getPagestr(int ipage) {
    String strPage = "";
    if (getLength() > 0) {
      strPage += "共";
      strPage += String.valueOf(rowCount);
      strPage += "條記錄，共";
      strPage += String.valueOf(pageCount);
      strPage += "頁，當前是第";
      strPage += String.valueOf(ipage);
      strPage += "頁，      ";
      int istart, iend;
      istart = ipage - 5;
      if (istart < 0) {
        istart = 0;
      }
      iend = istart + 10;
      if (iend > pageCount) {
        iend = pageCount;
      }
      istart = iend - 10;
      if (istart < 0) {
        istart = 0;
      }
      for (int i = istart; i < iend; i++) {
        strPage +=
            "<a href='searchAction.do?page=";
        strPage += String.valueOf(i + 1);
        strPage += conditionStr;
        strPage += "'>";
        strPage += String.valueOf(i + 1);
        strPage += "</a>";
        strPage += "  ";
      }
    }
    this.pagestr = strPage;
    return strPage;
  }

  public String getPagestr_(int ipage) {
    String strPage = "";
    if (getLength() > 0) {
      strPage += "共";
      strPage += String.valueOf(rowCount);
      strPage += "條記錄，共";
      strPage += String.valueOf(pageCount);
      strPage += "頁，當前是第";
      strPage += String.valueOf(ipage);
      strPage += "頁，      ";
      int istart, iend;
      istart = ipage - 5;
      if (istart < 0) {
        istart = 0;
      }
      iend = istart + 10;
      if (iend > pageCount) {
        iend = pageCount;
      }
      istart = iend - 10;
      if (istart < 0) {
        istart = 0;
      }
      for (int i = istart; i < iend; i++) {
        strPage +=
            "<a href='examinationPaperListAction.do?page=";
        strPage += String.valueOf(i + 1);
        strPage += "'>";
        strPage += String.valueOf(i + 1);
        strPage += "</a>";
        strPage += "  ";
      }
    }
    this.pagestr = strPage;
    return strPage;
  }

  //Remove Examinationpaper!!
  public void removeExaminationPaper(ExaminationPaper examinationPaper) {
    removeExaminationPaper(examinationPaper.getE_id());
  }

  //Remove by key!!
  public void removeExaminationPaper(long e_id) {
    PreparedStatement pstmt = null;

    try {
      pstmt = conn.prepareStatement(REMOVEEXAMINATIONPAPERBYKEY);
      pstmt.setLong(1, e_id);
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

//Update E_state!!
  public void updateE_state(long e_id, String e_state) {
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(UPDATE_E_STATE);
      pstmt.setString(1, e_state);
      pstmt.setLong(2, e_id);
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.getStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  public void removeExaminee_list(long e_id){
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(REMOVEEXAMINEE_LIST);
      pstmt.setLong(1, e_id);
      pstmt.executeUpdate();
    } catch (SQLException ex) {
      ex.getStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }

  }

  //Update ExaminationPaper!!
  public void updateExaminationPaper(ExaminationPaper examinationPaper) {
    PreparedStatement pstmt = null;
    try {
      if (null == examinationPaper.getE_begin() ||
          "".equals(examinationPaper.getE_begin())) {
        pstmt = conn.prepareStatement(UPDATE_PAPER2);
        pstmt.setString(1, examinationPaper.getE_name());
        pstmt.setString(2, examinationPaper.getE_type());
        pstmt.setInt(3, examinationPaper.getE_timer());
        pstmt.setString(4, examinationPaper.getE_idlist());
        pstmt.setString(5, examinationPaper.getE_examineeList());
        pstmt.setFloat(6, examinationPaper.getE_passvalue());
        pstmt.setFloat(7, examinationPaper.getE_total());
        pstmt.setString(8, examinationPaper.getE_state());
        pstmt.setLong(9, examinationPaper.getE_id());
        pstmt.executeUpdate();
      } else {
        pstmt = conn.prepareStatement(UPDATE_PAPER);
        pstmt.setString(1, examinationPaper.getE_name());
        pstmt.setString(2, examinationPaper.getE_type());
        pstmt.setInt(3, examinationPaper.getE_timer());
        pstmt.setString(4, examinationPaper.getE_idlist());
        pstmt.setString(5, examinationPaper.getE_begin());
        pstmt.setString(6, examinationPaper.getE_end());
        pstmt.setString(7, examinationPaper.getE_examineeList());
        pstmt.setFloat(8, examinationPaper.getE_passvalue());
        pstmt.setFloat(9, examinationPaper.getE_total());
        pstmt.setString(10, examinationPaper.getE_state());
        pstmt.setLong(11, examinationPaper.getE_id());
        pstmt.executeUpdate();
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    } finally {
      try {
        pstmt.close();
        pstmt = null;
        conn.close();
        conn = null;
      } catch (SQLException ex1) {
        ex1.printStackTrace();
      }
    }
  }

  public void updateE_stateUnvalidateExamPaper(){
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(UPDATE_E_STATE_UNVALIDATE_EXAMPAPER);
    pstmt.executeUpdate();
    } catch (SQLException ex) {