/**
* Method to get the workgroups to which the user with given username has at least colony manage
* access.
*
* @param userName - the requesters username
* @return - list of workgroup names
*/
public List<String> getColonyManageWorkgroups(String userName) {
List<String> owners = new ArrayList<String>();
String queryString =
"SELECT DISTINCT w.WorkgroupName "
+ "FROM Workgroup AS w "
+ "JOIN WorkgroupUser AS wu "
+ "ON wu._Workgroup_key = w._Workgroup_key "
+ "JOIN User AS u "
+ "ON u._User_key = wu._User_key "
+ "JOIN WorkgroupUserFunctionalArea AS wufa "
+ "ON wu._WorkgroupUser_key = wufa._WorkgroupUser_key "
+ "JOIN FunctionalArea AS fa "
+ "ON wufa._FunctionalArea_key = fa._FunctionalArea_key "
+ "WHERE u.UserName = ? "
+ "AND (u.IsActive = 1 OR u.IsActive = -1) "
+ "AND (fa.FunctionalArea = 'ColonyManagement'"
+ "OR fa.FunctionalArea = 'Administration')";
PreparedStatement query;
ResultSet rs;
Result resultData = null;
Connection con = this.getConnection();
try {
// establish connection and initialize prepared statement
query = con.prepareStatement(queryString);
// set ? in queryString to userName
query.setString(1, userName);
rs = query.executeQuery();
rs.beforeFirst();
// Very Very Very Important. Doesn't convert to Result with out the
// following line -> myResult.beforeFirst();
if (rs != null) {
resultData = ResultSupport.toResult(rs);
for (SortedMap workgroup : resultData.getRows()) {
owners.add(this.myGet("WorkgroupName", workgroup));
}
}
} catch (Exception e) {
System.out.println("ERROR: " + e);
}
return owners;
}
public static List<Book> searchBook(String key) {
String sql = "exec searchBook '" + key + "'";
Result result = DBHelper.execSql(sql);
SortedMap[] rows = result.getRows();
List<Book> books = new ArrayList<Book>();
for (SortedMap row : rows) {
Book book = new Book();
book.setId(Integer.parseInt(row.get("id").toString().trim()));
book.setName(row.get("name").toString().trim());
book.setISBN(row.get("ISBN").toString().trim());
book.setAuthor(row.get("author").toString().trim());
book.setDescription(row.get("description").toString().trim());
book.setPublishDate(row.get("publishDate").toString().trim());
book.setQuantity(Integer.parseInt(row.get("quantity").toString().trim()));
System.out.println(book);
books.add(book);
}
return books;
}