public List<Agent> getClientsPerAgent() {
List<Agent> agent_list = null;
try {
conn = JDBC_Conn.getConnection();
String sql =
"select agent_id, first_nm, last_nm, phone_number, bb.num from agents natural join (select agent_id, count(booking_id) num from bookings group by agent_id) bb order by bb.num desc";
stmt =
conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery();
while (rs.next()) {
if (agent_list == null) agent_list = new ArrayList<Agent>();
Agent agent = new Agent();
agent.setAgent_id(rs.getString("agent_id"));
agent.setPhone_number(rs.getString("phone_number"));
agent.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
agent.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));
agent.setNbr_clients(rs.getInt("num"));
agent_list.add(agent);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
return agent_list;
}
public List<Agent> queryAllAgents(Page page) {
List<Agent> agent_list = null;
try {
conn = JDBC_Conn.getConnection();
String sql = "select * from agents natural join position";
stmt =
conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.setMaxRows(page.getEndIndex());
rs = stmt.executeQuery();
if (page.getBeginIndex() > 0) {
rs.absolute(page.getBeginIndex());
}
while (rs.next()) {
if (agent_list == null) agent_list = new ArrayList<Agent>();
Agent agent = new Agent();
agent.setAgent_id(rs.getString("agent_id"));
agent.setPhone_number(rs.getString("phone_number"));
agent.setEmail(rs.getString("email"));
agent.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
agent.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date dob = sdf.parse(rs.getString("dob"));
agent.setDob(dob);
agent.setZip(rs.getString("zip"));
agent.setGender(rs.getString("gender"));
agent.setPosition(rs.getString("position"));
agent_list.add(agent);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
return agent_list;
}
public Agent queryAgentById(String agent_id) {
Agent agent = null;
try {
conn = JDBC_Conn.getConnection();
String sql = "select * from agents natural join position where agent_id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, agent_id);
rs = stmt.executeQuery();
while (rs.next()) {
agent = new Agent();
agent.setAgent_id(rs.getString("agent_id"));
agent.setPhone_number(rs.getString("phone_number"));
agent.setEmail(rs.getString("email"));
agent.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
agent.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date dob = sdf.parse(rs.getString("dob"));
agent.setDob(dob);
agent.setZip(rs.getString("zip"));
agent.setGender(rs.getString("gender"));
agent.setPosition(rs.getString("position"));
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
return agent;
}
public List<Agent> queryAgentByCondition(Agent agent, Page page) {
List<Agent> agent_list = null;
int count = 0;
try {
conn = JDBC_Conn.getConnection();
String sql = "select * from agents natural join position where ";
if (agent.getFirst_nm() != null && !"".equals(agent.getFirst_nm())) {
sql += " first_nm='" + agent.getFirst_nm() + "'" + " and ";
count++;
}
if (agent.getLast_nm() != null && !"".equals(agent.getLast_nm())) {
sql += " last_nm='" + agent.getLast_nm() + "'" + " and ";
count++;
}
if (agent.getGender() != null && !"".equals(agent.getGender())) {
sql += " gender='" + agent.getGender() + "'" + " and ";
count++;
}
if (agent.getZip() != null && !"".equals(agent.getZip())) {
sql += " zip='" + agent.getZip() + "'" + " and ";
count++;
}
if (agent.getEmail() != null && !"".equals(agent.getEmail())) {
sql += " email='" + agent.getEmail() + "'" + " and ";
count++;
}
if (agent.getPhone_number() != null && !"".equals(agent.getPhone_number())) {
sql += " phone_number='" + agent.getPhone_number() + "'" + " and ";
count++;
}
if (agent.getPosition() != null && !"".equals(agent.getPosition())) {
sql += " position='" + agent.getPosition() + "'" + " and ";
count++;
}
if (agent.getDob() != null) {
SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
String dob_str = sdf.format(agent.getDob());
sql += " dob= " + "to_date('" + dob_str + "'," + "'MM/dd/yyyy')" + " and ";
count++;
}
if (count == 0) return queryAllAgents(page);
int last_index = sql.lastIndexOf("and");
sql = sql.substring(0, last_index);
stmt =
conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.setMaxRows(page.getEndIndex());
rs = stmt.executeQuery();
if (page.getBeginIndex() > 0) {
rs.absolute(page.getBeginIndex());
}
while (rs.next()) {
if (agent_list == null) agent_list = new ArrayList<Agent>();
Agent agent1 = new Agent();
agent1.setAgent_id(rs.getString("agent_id"));
agent1.setPhone_number(rs.getString("phone_number"));
agent1.setEmail(rs.getString("email"));
agent1.setFirst_nm(CommUtils.initCap(rs.getString("first_nm")));
agent1.setLast_nm(CommUtils.initCap(rs.getString("last_nm")));
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date dob = sdf.parse(rs.getString("dob"));
agent1.setDob(dob);
agent1.setZip(rs.getString("zip"));
agent1.setGender(rs.getString("gender"));
agent1.setPosition(rs.getString("position"));
agent_list.add(agent1);
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException();
} finally {
JDBC_Conn.releaseConnection(conn, stmt, rs);
}
return agent_list;
}