public void updateJournalNote(DocumentNote documentNote) throws Exception {
kLogger.fine("Updating journal notes id=" + documentNote.getNoteId());
Connection connection = ConnectionPoolManager.getInstance().getConnection();
PreparedStatement psUpdate = connection.prepareStatement(SQL_UPDATE_JOURNALNOTE);
int i = 1;
psUpdate.setString(i++, documentNote.getNoteData());
psUpdate.setString(i++, documentNote.getUserName().toUpperCase());
psUpdate.setString(i++, documentNote.getCreated());
if (documentNote.isActive()) {
psUpdate.setString(i++, "Y");
} else {
psUpdate.setString(i++, "N");
}
psUpdate.setInt(i++, documentNote.getDocumentId());
psUpdate.setString(i++, documentNote.getNoteType());
psUpdate.setInt(i++, documentNote.getNoteId());
int recCount = psUpdate.executeUpdate();
psUpdate.close();
connection.commit();
connection.close();
if (recCount == 0) {
kLogger.warning("Unable to update journal notes id=" + documentNote.getNoteId());
throw new Exception("Unable to update journal notes id=" + documentNote.getNoteId());
}
}
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 void addJournalNote(DocumentNote documentNote) throws Exception {
kLogger.fine("Adding journal note for document: id=" + documentNote.getDocumentId());
Connection connection = ConnectionPoolManager.getInstance().getConnection();
PreparedStatement psInsert = connection.prepareStatement(SQL_INSERT_JOURNALNOTE);
int i = 1;
psInsert.setString(i++, documentNote.getNoteData());
psInsert.setString(i++, documentNote.getUserName().toUpperCase());
psInsert.setInt(i++, documentNote.getDocumentId());
psInsert.setString(i++, documentNote.getNoteType());
int recCount = psInsert.executeUpdate();
psInsert.close();
connection.commit();
connection.close();
if (recCount == 0) {
kLogger.warning(
"Unable to add journal note for document: id=" + documentNote.getDocumentId());
throw new Exception(
"Unable to add journal note for document: id=" + documentNote.getDocumentId());
}
}
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;
}