/**
* @user admin
* @param CandidateAction object
* @return true if candidate information added in database else false Generates a unique candidate
* id for each candidate and fills the candidate information into the database
*/
public boolean addCandidate(CandidateAction ca) {
conn = Connect.createConn();
try {
int j = genCandidateid();
query = "insert into its_candidateinformation_tbl values(?,?,?,?,?,?,?,?,?,?,?, default)";
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, j);
pstmt.setString(2, ca.getFname());
pstmt.setString(3, ca.getLname());
pstmt.setString(4, ca.getDegree());
pstmt.setString(5, ca.getStream());
pstmt.setString(6, ca.getPskillset());
pstmt.setString(7, ca.getSskillset());
pstmt.setInt(8, ca.getExperience());
pstmt.setString(9, ca.getDesignation());
pstmt.setString(10, ca.getJoining());
pstmt.setString(11, ca.getLocation());
int i = pstmt.executeUpdate();
if (i == 1) {
message = "Candidate successfully added. Candidate ID is:" + j;
return true;
}
if (i == 0) {
message = "Candidate could not be added";
return false;
}
} catch (SQLException e) {
message = "exception occured ";
System.out.println("Cannot add data into candidate table " + e);
return false;
} finally {
closeConn();
}
return false;
}
/**
* @user admin
* @param candidateid
* @return true if candidate is registered, shared with Tech Panel and it is shared now with HR
* panel else false If candidate is already shared with Tech panel, then only it can be shared
* with HR panel.
*/
public boolean hshareCandidate(int candidateid) {
isreg = isRegistered(candidateid);
conn = Connect.createConn();
if (isreg == 0) { // candidate not present in database
message = "Candidate not present in database";
return false;
} else {
// fetch the data stored in shared column to check whether candidate is already shared or not
query = "select shared from its_candidateinformation_tbl where candidateid=?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, candidateid);
rset = pstmt.executeQuery();
rset.next();
String sharedwith = rset.getString(1);
if (sharedwith.equalsIgnoreCase("n")) { // shared with none
message =
"Candidate not shared with Technical Panel. First share it with Technical Panel";
return false;
}
if (sharedwith.equalsIgnoreCase("h")) { // shared with Tech and HR panel
message = "Candidate already shared with Technical & HR Panel";
return false;
}
if (sharedwith.equalsIgnoreCase("t")) { // shared with Tech panel only
query = "update its_candidateinformation_tbl set shared=? where candidateid=?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setInt(2, candidateid);
pstmt.setString(1, "h");
int j = pstmt.executeUpdate();
if (j == 1) {
message = "candidate successfully shared with Tech & HR panel.";
return true;
}
if (j == 0) {
message = "candidate could not be shared.";
return false;
}
} catch (SQLException e) {
message = "SQL Exception occured";
System.out.println("Candidate info not shared" + e);
}
}
} catch (SQLException e) {
System.out.println("Exception caught in share candidate with tech panel " + e);
} finally {
closeConn();
}
return false;
}
}
/**
* @user admin
* @param CandidateAction object
* @return LinkedList of CandidateAction type objects who match the search criteria TODO - provide
* search methods for individual queries SQL command like is used to search substrings within
* Primary Skillset and Secondary Skillset strings stored in database
*/
public LinkedList<CandidateAction> searchCandidate(CandidateAction ca) {
conn = Connect.createConn();
query1 = "SELECT * FROM its_candidateinformation_tbl WHERE ( ";
query =
query1
+ " upper(pskillset) like upper(?) AND upper(sskillset) like upper(?) AND experience=?)";
System.out.println(query);
lca = new LinkedList<CandidateAction>();
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(
1, "%" + ca.getPskillset() + "%"); // used %string% format to search within strings
pstmt.setString(
2, "%" + ca.getSskillset() + "%"); // used %string% format to search within strings
pstmt.setInt(3, ca.getExperience());
rset = pstmt.executeQuery();
while (rset.next()) {
System.out.println("within rset");
ca1 = new CandidateAction();
ca1.setCandidateid(rset.getInt(1));
ca1.setFname(rset.getString(2));
ca1.setLname(rset.getString(3));
ca1.setDegree(rset.getString(4));
ca1.setStream(rset.getString(5));
ca1.setPskillset(rset.getString(6));
ca1.setSskillset(rset.getString(7));
ca1.setExperience(rset.getInt(8));
ca1.setDesignation(rset.getString(9));
ca1.setJoining(rset.getString(10).substring(0, 10));
ca1.setLocation(rset.getString(11));
System.out.println("rset not null");
System.out.println(ca1.getCandidateid());
lca.add(ca1);
}
if (lca == null) {
message = "no records found with given search criteria";
System.out.println("lca is null");
} else {
message = "Candidates with given search criteria are given here";
System.out.println("lca not null");
}
return lca;
} catch (SQLException e) {
message = "Exception occured in search candidate";
System.out.println("Candidate info cannot be fetched " + e);
} finally {
closeConn();
}
return lca;
}
/**
* @user admin
* @return unique candidate id
* @throws SQLException selects the max. candidateid from database, adds 1 to it and returns if no
* candidate is registered the method returns 1001
*/
private int genCandidateid() {
conn = Connect.createConn();
try {
query1 = "select count(candidateid) from its_candidateinformation_tbl";
st = conn.createStatement();
rset = st.executeQuery(query1);
rset.next();
int num_rows = rset.getInt(1);
if (num_rows > 0) {
query1 = "select max(candidateid) from its_candidateinformation_tbl";
pstmt = conn.prepareStatement(query1);
rset = pstmt.executeQuery();
rset.next();
int candidateid = rset.getInt(1) + 1;
return candidateid;
} else {
return 1001;
}
} catch (SQLException e) {
System.out.println("Error occurred in generating candidate-id " + e);
return 1000;
}
}
/**
* @param candidateid
* @return 1 if candidate-id present in database, 0 if not present and -1 in case any exception
* occurs in searching the candidate in database
*/
public int isRegistered(int candidateid) {
conn = Connect.createConn();
query = "select count(*) from its_candidateinformation_tbl where candidateid=?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setInt(1, candidateid);
rset = pstmt.executeQuery();
rset.next();
int i = rset.getInt(1);
if (i == 0) { // candidate id nor present in database
message = "Candidate not present in database";
return 0;
}
if (i == 1) { // candidate id present in database
return 1;
}
} catch (SQLException e) {
System.out.println("Exception occured in is registered " + e);
return -1;
} finally {
closeConn();
}
return -1;
}