/** Retrieves all notes with specified tags */
public List<Note> getNotesByTag(String[] tags) {
// Select All Query
StringBuilder whereCondition = new StringBuilder();
whereCondition.append(" WHERE ");
for (int i = 0; i < tags.length; i++) {
if (i != 0) {
whereCondition.append(" AND ");
}
whereCondition
.append("(" + KEY_CONTENT + " LIKE '%")
.append(tags[i])
.append("%' OR ")
.append(KEY_TITLE)
.append(" LIKE '%")
.append(tags[i])
.append("%')");
}
// Trashed notes must be included in search results only if search if performed from trash
whereCondition
.append(" AND " + KEY_TRASHED + " IS ")
.append(Navigation.checkNavigation(Navigation.TRASH) ? "" : "" + " NOT ")
.append(" 1");
return getNotes(whereCondition.toString(), true);
}
/**
* Gets notes matching pattern with title or content text
*
* @param pattern String to match with
* @return Notes list
*/
public List<Note> getNotesByPattern(String pattern) {
int navigation = Navigation.getNavigation();
String whereCondition =
" WHERE "
+ KEY_TRASHED
+ (navigation == Navigation.TRASH ? " IS 1" : " IS NOT 1")
+ (navigation == Navigation.ARCHIVE ? " AND " + KEY_ARCHIVED + " IS 1" : "")
+ (navigation == Navigation.CATEGORY
? " AND " + KEY_CATEGORY + " = " + Navigation.getCategory()
: "")
+ (navigation == Navigation.UNCATEGORIZED
? " AND (" + KEY_CATEGORY + " IS NULL OR " + KEY_CATEGORY_ID + " == 0) "
: "")
+ (Navigation.checkNavigation(Navigation.REMINDERS)
? " AND " + KEY_REMINDER + " IS NOT NULL"
: "")
+ " AND ("
+ " ( "
+ KEY_LOCKED
+ " IS NOT 1 AND ("
+ KEY_TITLE
+ " LIKE '%"
+ pattern
+ "%' "
+ " OR "
+ KEY_CONTENT
+ " LIKE '%"
+ pattern
+ "%' ))"
+ " OR ( "
+ KEY_LOCKED
+ " = 1 AND "
+ KEY_TITLE
+ " LIKE '%"
+ pattern
+ "%' )"
+ ")";
return getNotes(whereCondition, true);
}
/** Retrieves all tags of a specified note */
public List<Tag> getTags(Note note) {
List<Tag> tags = new ArrayList<>();
HashMap<String, Integer> tagsMap = new HashMap<>();
String whereCondition =
" WHERE "
+ (note != null ? KEY_ID + " = " + note.get_id() + " AND " : "")
+ "("
+ KEY_CONTENT
+ " LIKE '%#%' OR "
+ KEY_TITLE
+ " LIKE '%#%' "
+ ")"
+ " AND "
+ KEY_TRASHED
+ " IS "
+ (Navigation.checkNavigation(Navigation.TRASH) ? "" : " NOT ")
+ " 1";
List<Note> notesRetrieved = getNotes(whereCondition, true);
for (Note noteRetrieved : notesRetrieved) {
HashMap<String, Integer> tagsRetrieved = TagsHelper.retrieveTags(noteRetrieved);
for (String s : tagsRetrieved.keySet()) {
int count = tagsMap.get(s) == null ? 0 : tagsMap.get(s);
tagsMap.put(s, ++count);
}
}
for (String s : tagsMap.keySet()) {
Tag tag = new Tag(s, tagsMap.get(s));
tags.add(tag);
}
Collections.sort(tags, (tag1, tag2) -> tag1.getText().compareToIgnoreCase(tag2.getText()));
return tags;
}
/**
* Common method for notes retrieval. It accepts a query to perform and returns matching records.
*/
public List<Note> getNotes(String whereCondition, boolean order) {
List<Note> noteList = new ArrayList<>();
String sort_column, sort_order = "";
// Getting sorting criteria from preferences. Reminder screen forces sorting.
if (Navigation.checkNavigation(Navigation.REMINDERS)) {
sort_column = KEY_REMINDER;
} else {
sort_column = prefs.getString(Constants.PREF_SORTING_COLUMN, KEY_TITLE);
}
if (order) {
sort_order =
KEY_TITLE.equals(sort_column) || KEY_REMINDER.equals(sort_column) ? " ASC " : " DESC ";
}
// In case of title sorting criteria it must be handled empty title by concatenating content
sort_column = KEY_TITLE.equals(sort_column) ? KEY_TITLE + "||" + KEY_CONTENT : sort_column;
// In case of reminder sorting criteria the empty reminder notes must be moved on bottom of
// results
sort_column =
KEY_REMINDER.equals(sort_column)
? "IFNULL(" + KEY_REMINDER + ", " + "" + Constants.TIMESTAMP_UNIX_EPOCH + ")"
: sort_column;
// Generic query to be specialized with conditions passed as parameter
String query =
"SELECT "
+ KEY_CREATION
+ ","
+ KEY_LAST_MODIFICATION
+ ","
+ KEY_TITLE
+ ","
+ KEY_CONTENT
+ ","
+ KEY_ARCHIVED
+ ","
+ KEY_TRASHED
+ ","
+ KEY_REMINDER
+ ","
+ KEY_REMINDER_FIRED
+ ","
+ KEY_RECURRENCE_RULE
+ ","
+ KEY_LATITUDE
+ ","
+ KEY_LONGITUDE
+ ","
+ KEY_ADDRESS
+ ","
+ KEY_LOCKED
+ ","
+ KEY_CHECKLIST
+ ","
+ KEY_CATEGORY
+ ","
+ KEY_CATEGORY_NAME
+ ","
+ KEY_CATEGORY_DESCRIPTION
+ ","
+ KEY_CATEGORY_COLOR
+ " FROM "
+ TABLE_NOTES
+ " LEFT JOIN "
+ TABLE_CATEGORY
+ " USING( "
+ KEY_CATEGORY
+ ") "
+ whereCondition
+ (order ? " ORDER BY " + sort_column + sort_order : "");
Log.v(Constants.TAG, "Query: " + query);
Cursor cursor = null;
try {
cursor = getDatabase().rawQuery(query, null);
// Looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
int i = 0;
Note note = new Note();
note.setCreation(cursor.getLong(i++));
note.setLastModification(cursor.getLong(i++));
note.setTitle(cursor.getString(i++));
note.setContent(cursor.getString(i++));
note.setArchived("1".equals(cursor.getString(i++)));
note.setTrashed("1".equals(cursor.getString(i++)));
note.setAlarm(cursor.getString(i++));
note.setReminderFired(cursor.getInt(i++));
note.setRecurrenceRule(cursor.getString(i++));
note.setLatitude(cursor.getString(i++));
note.setLongitude(cursor.getString(i++));
note.setAddress(cursor.getString(i++));
note.setLocked("1".equals(cursor.getString(i++)));
note.setChecklist("1".equals(cursor.getString(i++)));
// Eventual decryption of content
if (note.isLocked()) {
note.setContent(
Security.decrypt(note.getContent(), prefs.getString(Constants.PREF_PASSWORD, "")));
}
// Set category
long categoryId = cursor.getLong(i++);
if (categoryId != 0) {
Category category =
new Category(
categoryId,
cursor.getString(i++),
cursor.getString(i++),
cursor.getString(i++));
note.setCategory(category);
}
// Add eventual attachments uri
note.setAttachmentsList(getNoteAttachments(note));
// Adding note to list
noteList.add(note);
} while (cursor.moveToNext());
}
} finally {
if (cursor != null) cursor.close();
}
Log.v(Constants.TAG, "Query: Retrieval finished!");
return noteList;
}