/**
* Retrieves attachments using a condition passed as parameter
*
* @return List of attachments
*/
public ArrayList<Attachment> getAttachments(String whereCondition) {
ArrayList<Attachment> attachmentsList = new ArrayList<>();
String sql =
"SELECT "
+ KEY_ATTACHMENT_ID
+ ","
+ KEY_ATTACHMENT_URI
+ ","
+ KEY_ATTACHMENT_NAME
+ ","
+ KEY_ATTACHMENT_SIZE
+ ","
+ KEY_ATTACHMENT_LENGTH
+ ","
+ KEY_ATTACHMENT_MIME_TYPE
+ " FROM "
+ TABLE_ATTACHMENTS
+ whereCondition;
SQLiteDatabase db;
Cursor cursor = null;
try {
cursor = getDatabase().rawQuery(sql, null);
// Looping through all rows and adding to list
if (cursor.moveToFirst()) {
Attachment mAttachment;
do {
mAttachment =
new Attachment(
cursor.getLong(0),
Uri.parse(cursor.getString(1)),
cursor.getString(2),
cursor.getInt(3),
(long) cursor.getInt(4),
cursor.getString(5));
attachmentsList.add(mAttachment);
} while (cursor.moveToNext());
}
} finally {
if (cursor != null) cursor.close();
}
return attachmentsList;
}
/**
* Retrieves categories list from database
*
* @return List of categories
*/
public ArrayList<Category> getCategories() {
ArrayList<Category> categoriesList = new ArrayList<>();
String sql =
"SELECT "
+ KEY_CATEGORY_ID
+ ","
+ KEY_CATEGORY_NAME
+ ","
+ KEY_CATEGORY_DESCRIPTION
+ ","
+ KEY_CATEGORY_COLOR
+ ","
+ " COUNT("
+ KEY_ID
+ ") count"
+ " FROM "
+ TABLE_CATEGORY
+ " LEFT JOIN ("
+ " SELECT "
+ KEY_ID
+ ", "
+ KEY_CATEGORY
+ " FROM "
+ TABLE_NOTES
+ " WHERE "
+ KEY_TRASHED
+ " IS NOT 1"
+ ") USING( "
+ KEY_CATEGORY
+ ") "
+ " GROUP BY "
+ KEY_CATEGORY_ID
+ ","
+ KEY_CATEGORY_NAME
+ ","
+ KEY_CATEGORY_DESCRIPTION
+ ","
+ KEY_CATEGORY_COLOR
+ " ORDER BY IFNULL(NULLIF("
+ KEY_CATEGORY_NAME
+ ", ''),'zzzzzzzz') ";
Cursor cursor = null;
try {
cursor = getDatabase().rawQuery(sql, null);
// Looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
categoriesList.add(
new Category(
cursor.getLong(0),
cursor.getString(1),
cursor.getString(2),
cursor.getString(3),
cursor.getInt(4)));
} while (cursor.moveToNext());
}
} finally {
if (cursor != null) cursor.close();
}
return categoriesList;
}
