/** Getting single note */
public Note getNote(long id) {
String whereCondition = " WHERE " + KEY_ID + " = " + id;
List<Note> notes = getNotes(whereCondition, true);
Note note;
if (notes.size() > 0) {
note = notes.get(0);
} else {
note = null;
}
return note;
}
/** 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;
}
// Inserting or updating single note
public Note updateNote(Note note, boolean updateLastModification) {
SQLiteDatabase db = getDatabase(true);
String content;
if (note.isLocked()) {
content = Security.encrypt(note.getContent(), prefs.getString(Constants.PREF_PASSWORD, ""));
} else {
content = note.getContent();
}
// To ensure note and attachments insertions are atomical and boost performances transaction are
// used
db.beginTransaction();
ContentValues values = new ContentValues();
values.put(KEY_TITLE, note.getTitle());
values.put(KEY_CONTENT, content);
values.put(
KEY_CREATION,
note.getCreation() != null ? note.getCreation() : Calendar.getInstance().getTimeInMillis());
values.put(
KEY_LAST_MODIFICATION,
updateLastModification
? Calendar.getInstance().getTimeInMillis()
: (note.getLastModification() != null
? note.getLastModification()
: Calendar.getInstance().getTimeInMillis()));
values.put(KEY_ARCHIVED, note.isArchived());
values.put(KEY_TRASHED, note.isTrashed());
values.put(KEY_REMINDER, note.getAlarm());
values.put(KEY_REMINDER_FIRED, note.isReminderFired());
values.put(KEY_RECURRENCE_RULE, note.getRecurrenceRule());
values.put(KEY_LATITUDE, note.getLatitude());
values.put(KEY_LONGITUDE, note.getLongitude());
values.put(KEY_ADDRESS, note.getAddress());
values.put(KEY_CATEGORY, note.getCategory() != null ? note.getCategory().getId() : null);
boolean locked = note.isLocked() != null ? note.isLocked() : false;
values.put(KEY_LOCKED, locked);
boolean checklist = note.isChecklist() != null ? note.isChecklist() : false;
values.put(KEY_CHECKLIST, checklist);
db.insertWithOnConflict(TABLE_NOTES, KEY_ID, values, SQLiteDatabase.CONFLICT_REPLACE);
Log.d(Constants.TAG, "Updated note titled '" + note.getTitle() + "'");
// Updating attachments
List<Attachment> deletedAttachments = note.getAttachmentsListOld();
for (Attachment attachment : note.getAttachmentsList()) {
updateAttachment(
note.get_id() != null ? note.get_id() : values.getAsLong(KEY_CREATION), attachment, db);
deletedAttachments.remove(attachment);
}
// Remove from database deleted attachments
for (Attachment attachmentDeleted : deletedAttachments) {
db.delete(
TABLE_ATTACHMENTS,
KEY_ATTACHMENT_ID + " = ?",
new String[] {String.valueOf(attachmentDeleted.getId())});
}
db.setTransactionSuccessful();
db.endTransaction();
// Fill the note with correct data before returning it
note.setCreation(
note.getCreation() != null ? note.getCreation() : values.getAsLong(KEY_CREATION));
note.setLastModification(values.getAsLong(KEY_LAST_MODIFICATION));
return note;
}
/** Retrieves statistics data based on app usage */
public Stats getStats() {
Stats mStats = new Stats();
// Categories
mStats.setCategories(getCategories().size());
// Everything about notes and their text stats
int notesActive = 0,
notesArchived = 0,
notesTrashed = 0,
reminders = 0,
remindersFuture = 0,
checklists = 0,
notesMasked = 0,
tags = 0,
locations = 0;
int totalWords = 0, totalChars = 0, maxWords = 0, maxChars = 0, avgWords = 0, avgChars = 0;
int words, chars;
List<Note> notes = getAllNotes(false);
for (Note note : notes) {
if (note.isTrashed()) {
notesTrashed++;
} else if (note.isArchived()) {
notesArchived++;
} else {
notesActive++;
}
if (note.getAlarm() != null && Long.parseLong(note.getAlarm()) > 0) {
if (Long.parseLong(note.getAlarm()) > Calendar.getInstance().getTimeInMillis()) {
remindersFuture++;
} else {
reminders++;
}
}
if (note.isChecklist()) {
checklists++;
}
if (note.isLocked()) {
notesMasked++;
}
tags += TagsHelper.retrieveTags(note).size();
if (note.getLongitude() != null && note.getLongitude() != 0) {
locations++;
}
words = getWords(note);
chars = getChars(note);
if (words > maxWords) {
maxWords = words;
}
if (chars > maxChars) {
maxChars = chars;
}
totalWords += words;
totalChars += chars;
}
mStats.setNotesActive(notesActive);
mStats.setNotesArchived(notesArchived);
mStats.setNotesTrashed(notesTrashed);
mStats.setReminders(reminders);
mStats.setRemindersFutures(remindersFuture);
mStats.setNotesChecklist(checklists);
mStats.setNotesMasked(notesMasked);
mStats.setTags(tags);
mStats.setLocation(locations);
avgWords = totalWords / (notes.size() != 0 ? notes.size() : 1);
avgChars = totalChars / (notes.size() != 0 ? notes.size() : 1);
mStats.setWords(totalWords);
mStats.setWordsMax(maxWords);
mStats.setWordsAvg(avgWords);
mStats.setChars(totalChars);
mStats.setCharsMax(maxChars);
mStats.setCharsAvg(avgChars);
// Everything about attachments
int attachmentsAll = 0, images = 0, videos = 0, audioRecordings = 0, sketches = 0, files = 0;
List<Attachment> attachments = getAllAttachments();
for (Attachment attachment : attachments) {
if (Constants.MIME_TYPE_IMAGE.equals(attachment.getMime_type())) {
images++;
} else if (Constants.MIME_TYPE_VIDEO.equals(attachment.getMime_type())) {
videos++;
} else if (Constants.MIME_TYPE_AUDIO.equals(attachment.getMime_type())) {
audioRecordings++;
} else if (Constants.MIME_TYPE_SKETCH.equals(attachment.getMime_type())) {
sketches++;
} else if (Constants.MIME_TYPE_FILES.equals(attachment.getMime_type())) {
files++;
}
}
mStats.setAttachments(attachmentsAll);
mStats.setImages(images);
mStats.setVideos(videos);
mStats.setAudioRecordings(audioRecordings);
mStats.setSketches(sketches);
mStats.setFiles(files);
return mStats;
}
