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;
}
