/**
* A method to build a list of Collaboration objects representing a list of collaborations
* associated with a collaborator
*
* @param id the unique identifier of a collaborator
* @return a CollaborationList object containing a list of Collaboration objects
*/
public CollaborationList getCollaborationList_org(String org_id, String id) {
// check on the input parameters
if (InputUtils.isValidInt(id) == false) {
throw new IllegalArgumentException("The id parameter cannot be null");
}
// declare helper variables
CollaborationList list = new CollaborationList(id);
// define other helper variables
Collaboration collaboration = null;
// define other helper variables
String partner = null;
Integer count = null;
String firstDate = null;
String lastDate = null;
// define the base sql query
String sqlQuery =
"SELECT distinct count(*), con.contributorid, min(e.first_date), max(e.first_date) "
+ "FROM contributor con, conevlink c , conevlink c2, orgevlink o, events e "
+ "WHERE o.organisationid = ? " // + org_id + " "
+ "AND c.eventid = O.EVENTID "
+ "AND e.eventid = O.EVENTID "
+ "AND e.eventid = c.EVENTID "
+ "AND c.contributorid != ?" // + id + " "
+ "AND con.contributorid = c.contributorid "
+ "AND c2.contributorid = ? " // + id + " "
+ "AND c2.eventid = c.eventid "
+ "GROUP BY con.contributorid, con.first_name ";
int[] param = {Integer.parseInt(org_id), Integer.parseInt(id), Integer.parseInt(id)};
// execute the query
java.sql.ResultSet resultSet = db.exePreparedStatement(sqlQuery, param);
try {
// check to see that data was returned
if (!resultSet.last()) {
db.tidyup();
return null;
} else resultSet.beforeFirst();
// loop though the resulset
while (resultSet.next()) {
// get the data
partner = resultSet.getString(2);
count = resultSet.getInt(1);
firstDate = resultSet.getDate(3).toString();
lastDate = resultSet.getDate(4).toString();
// create the collaboration object
collaboration = new Collaboration(id, partner, count, firstDate, lastDate);
// add the collaboration to the list
list.addCollaboration(collaboration);
}
} catch (java.sql.SQLException ex) {
System.out.println("Exception: " + ex.getMessage());
resultSet = null;
}
db.tidyup();
// return the list of collaborations
return list;
} // end the CollaborationList method
