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[]
*/

}