public ArrayList<DocumentNote> searchDocumentNotes(
final String searchTerm,
final ArrayList<User> users,
final ArrayList<DocumentClass> documentClasses) {
ArrayList<DocumentNote> result = new ArrayList<DocumentNote>();
kLogger.fine("Searching journal notes for search term =" + searchTerm);
try {
Connection connection = ConnectionPoolManager.getInstance().getConnection();
String likeClause = "";
StringTokenizer strTok = new StringTokenizer(searchTerm, " ");
while (strTok.hasMoreTokens()) {
String value = strTok.nextElement().toString().toUpperCase();
value = value.replaceAll("'", "''");
likeClause += "UPPER(NOTEDATA) LIKE '%" + value + "%' OR ";
}
likeClause += " UPPER(NOTEDATA) = 'XXXXXXXXX'";
String documentClassCriteria = "0";
for (DocumentClass documentClass : documentClasses) {
documentClassCriteria += "," + documentClass.getClassId();
}
String userNameCriteria = "'PUBLIC'";
for (User user : users) {
userNameCriteria += ",'" + user.getUserName() + "'";
}
String query =
"SELECT ID, NOTEDATA, NOTES.USERNAME, NOTES.CREATED,DOCUMENTS.DOCUMENTID,DOCUMENTS.CLASSID,NOTETYPE FROM "
+ "NOTES,DOCUMENTCLASSES,DOCUMENTS WHERE ("
+ likeClause
+ ") AND "
+ "DOCUMENTCLASSES.CLASSID=DOCUMENTS.CLASSID "
+ "AND DOCUMENTS.DOCUMENTID=NOTES.DOCUMENTID AND DOCUMENTCLASSES.CLASSID IN"
+ "("
+ documentClassCriteria
+ ") AND NOTES.USERNAME IN ("
+ userNameCriteria
+ ") AND NOTES.ACTIVE='Y' ORDER BY DOCUMENTCLASSES.CLASSDESC,DOCUMENTS.DOCUMENTID";
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery(query);
while (rs.next()) {
DocumentNote jNote = new DocumentNote();
jNote.setNoteId(rs.getInt("ID"));
jNote.setNoteData(rs.getString("NOTEDATA"));
jNote.setUserName(rs.getString("USERNAME"));
jNote.setCreated(rs.getString("CREATED"));
jNote.setDocumentId(rs.getInt("DOCUMENTID"));
jNote.setActive(true);
jNote.setClassId(rs.getInt("CLASSID"));
jNote.setNoteType(rs.getString("NOTETYPE"));
result.add(jNote);
}
rs.close();
statement.close();
connection.close();
} catch (Exception e) {
kLogger.warning("Unable to search journal notes for term : " + searchTerm);
e.printStackTrace();
}
return result;
}
