package tester.business.maitain;



import tclcc.tester.business.maitain.Trainplan;
import tclcc.tester.util.DBConn;
import java.sql.*;
import java.util.*;

public class TrainPlanDAO {
 private DBConn dbconn = null;
 private Connection conn = null;

 private static final String ADD_TRAINPLAN=
     "INSERT INTO KS_TRAINPLAN (p_id,p_title,issue_time,issuer,p_content,p_accessory) VALUES (lpad(SEQ_P_ID.nextVal,10,′0′),?,?,to_date(?,′yyyy-mm-dd′),?,?,?)";
 private final static String UPDATE_TRAINPLAN=
     "UPDATE KS_TRAINPLAN set p_id=?,p_title=?,post_index=?,issue_time=to_date(?,′yyyy-mm-dd′),issuer=?,p_content=?,p_accessory=? where p_id=?";

 /**
  * get a connection from a DB pool
  * @return Connection
  */
 public TrainPlanDAO() {
   try {
     dbconn = new DBConn();
     conn = dbconn.getConnection();
   } catch (Exception ex) {
     ex.printStackTrace();
   }
 }

 /**
  * add a row into DB
  * @param examinee Examinee
  * @throws SQLException
  */
 public void addTrainPlan(Trainplan trainPlan) throws SQLException {
   PreparedStatement pstmt = null;

   try {
     pstmt = conn.prepareStatement(ADD_TRAINPLAN);
     pstmt.setString(1, trainPlan.getP_title());
     pstmt.setString(2, trainPlan.getIssue_time());
     pstmt.setString(3, trainPlan.getIssuer());
     pstmt.setString(4, trainPlan.getP_content());
     pstmt.setString(5, trainPlan.getP_accessory());

     pstmt.executeUpdate();
   } catch (SQLException ex) {
     ex.printStackTrace();
   } finally {
     try {
       pstmt.close();
       //conn.close();
     } catch (SQLException ex1) {
     }
   }
 }

 /**
  * remove a row from DB
  * @param trainPlan TrainPlan
  * @throws SQLException
  */
 public void removeTrainPlan(Trainplan trainPlan) throws SQLException {
   this.removeTrainPlan(trainPlan.getP_id());
 }

 /**
  * remove a row from DB
  * @param p_id int
  * @throws SQLException
  */
 public void removeTrainPlan(int p_id) throws SQLException {
   Statement stmt = null;
   try {
     stmt = conn.createStatement();
     stmt.execute("DELETE FROM ks_trainplan WHERE p_id=" + p_id);
   } catch (SQLException ex) {
     ex.printStackTrace();
     throw new SQLException("SQLExction on : TrainPlanDAO.removeExaminee()");
   } finally {
     try {
       conn.close();
     } catch (SQLException ex1) {
       ex1.printStackTrace();
     }
   }
 }

 /**
  * update a row
  * @param examinee Examinee
  * @throws SQLException
  */
 public void updateTrainPlan(Trainplan trainPlan) throws SQLException {
   PreparedStatement pstmt = null;
   try {
     // UPDATE_TRAINPLAN = UPDATE_TRAINPLAN + "where p_id =" + trainPlan.getP_id();      //the condition need modification
     pstmt = conn.prepareStatement(UPDATE_TRAINPLAN);
     pstmt.setInt(1, trainPlan.getP_id());
     pstmt.setString(2, trainPlan.getP_title());
     pstmt.setInt(3, trainPlan.getPost_index());
     pstmt.setString(4, trainPlan.getIssue_time());
     pstmt.setString(5, trainPlan.getIssuer());
     pstmt.setString(6, trainPlan.getP_content());
     pstmt.setString(7, trainPlan.getP_accessory());
     pstmt.setInt(8,trainPlan.getP_id());
     pstmt.executeUpdate();
   } /*catch (SQLException ex) {
     ex.getStackTrace();
     throw new SQLException("SQLExction on : TrainPlanDAO.updateExaminee()");
   } */
   catch(SQLException sqle){
     do
     {
       System.err.println("Exception occoured:Message:"+sqle.getMessage());
       System.err.println("SQL state:"+sqle.getSQLState());
       System.err.println("Vendor code:"+sqle.getErrorCode()+"---------------");
      }  while((sqle=sqle.getNextException())!=null);
   }finally {
     try {
       conn.close();
     } catch (SQLException ex1) {
       ex1.printStackTrace();
     }
   }
 }

 /**
  * get all record from DB
  * @throws SQLException
  * @return Collection
  */
 public Collection getAll() throws SQLException {
   Statement stmt = null;
   ResultSet rs = null;
   Trainplan trainPlan = null;
   Collection list = null;

   try {
     stmt = conn.createStatement();
     rs = stmt.executeQuery("SELECT * FROM ks_trainplan");
     list = new ArrayList();
     while (rs.next()) {
       trainPlan = new Trainplan();
       trainPlan.setP_id(rs.getInt("P_ID"));
       trainPlan.setP_title(rs.getString("P_TITLE"));
       trainPlan.setPost_index(rs.getInt("POST_INDEX"));
       trainPlan.setIssue_time(rs.getString("ISSUE_TIME"));
       trainPlan.setIssuer(rs.getString("ISSUER"));
       trainPlan.setP_content(rs.getString("P_CONTENT"));
       trainPlan.setP_accessory(rs.getString("P_ACCESSORY"));
       list.add(trainPlan);
     }
   } catch (SQLException ex) {
   } finally {
     try {
       conn.close();
     } catch (SQLException ex1) {
       ex1.printStackTrace();
     }
     return list;
   }
 }

 public static String toString(Trainplan trainPlan){
     return trainPlan.getP_title();
   }

 /**
  * for test
  * @param args String[]
  */


}