Esempio n. 1
0
  /**
   * 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;
  }
Esempio n. 2
0
  /**
   * 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;
  }