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