Пример #1
0
  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;
  }
Пример #2
0
  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;
  }
Пример #3
0
  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;
  }