public DocumentNote readByNoteId(final int noteId) throws Exception {
DocumentNote documentNote = null;
kLogger.fine("Reading journal note :id=" + noteId);
Connection connection = ConnectionPoolManager.getInstance().getConnection();
PreparedStatement psSelect = connection.prepareStatement(SQL_SELECT_JOURNALNOTEBYID);
psSelect.setInt(1, noteId);
ResultSet rs = psSelect.executeQuery();
if (rs.next()) {
documentNote = new DocumentNote();
documentNote.setNoteId(rs.getInt("ID"));
documentNote.setNoteData(rs.getString("NOTEDATA"));
documentNote.setUserName(rs.getString("USERNAME"));
documentNote.setCreated(rs.getString("CREATED"));
if ("Y".equalsIgnoreCase(rs.getString("ACTIVE"))) {
documentNote.setActive(true);
} else {
documentNote.setActive(false);
}
documentNote.setDocumentId(rs.getInt("DOCUMENTID"));
documentNote.setNoteType(rs.getString("NOTETYPE"));
}
rs.close();
psSelect.close();
connection.close();
return documentNote;
}
public ArrayList<DocumentNote> readJournalNotes(final String criteria) {
ArrayList<DocumentNote> documentNotes = new ArrayList<DocumentNote>();
kLogger.fine("Reading journal note for criteria=" + criteria);
try {
Connection connection = ConnectionPoolManager.getInstance().getConnection();
StringBuffer sbSelect = new StringBuffer();
sbSelect.append(SQL_SELECT_JOURNALNOTES);
if (criteria.length() > 1) {
sbSelect.append(" WHERE " + criteria);
}
PreparedStatement psSelect = connection.prepareStatement(sbSelect.toString());
ResultSet rs = psSelect.executeQuery();
while (rs.next()) {
DocumentNote documentNote = new DocumentNote();
documentNote.setNoteId(rs.getInt("ID"));
documentNote.setNoteData(rs.getString("NOTEDATA"));
documentNote.setUserName(rs.getString("USERNAME"));
documentNote.setCreated(rs.getString("CREATED"));
if ("Y".equalsIgnoreCase(rs.getString("ACTIVE"))) {
documentNote.setActive(true);
} else {
documentNote.setActive(false);
}
documentNote.setDocumentId(rs.getInt("DOCUMENTID"));
documentNote.setNoteType(rs.getString("NOTETYPE"));
documentNotes.add(documentNote);
}
rs.close();
psSelect.close();
connection.close();
} catch (Exception e) {
e.printStackTrace();
kLogger.warning("Unable to read journal note criteria:" + criteria);
}
return documentNotes;
}
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;
}