public List<Item> getItemsByName(CharSequence keyword) {
ArrayList<Item> items = new ArrayList<Item>();
String selectQuery =
"SELECT * FROM "
+ TABLE_ITEMS
+ " WHERE "
+ TABLE_ITEMS
+ "."
+ KEY_NAME
+ " LIKE '%"
+ keyword
+ "%'";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
Item item = new Item();
item.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
item.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
item.setDescription(cursor.getString(cursor.getColumnIndex(KEY_DESCRIPTION)));
item.setPrice(cursor.getFloat(cursor.getColumnIndex(KEY_PRICE)));
item.setCreated_at(cursor.getString(cursor.getColumnIndex(KEY_CREATED_AT)));
item.setUpdated_at(cursor.getString(cursor.getColumnIndex(KEY_UPDATED_AT)));
Category category = new Category();
String selectCategory =
"SELECT * FROM "
+ TABLE_CATEGORIES
+ " WHERE "
+ TABLE_CATEGORIES
+ "."
+ KEY_ID
+ " = "
+ cursor.getString(cursor.getColumnIndex(KEY_CATEGORY_ID));
Cursor categoryCursor = db.rawQuery(selectCategory, null);
if (categoryCursor.moveToFirst()) {
category.setId(categoryCursor.getInt(0));
category.setName(categoryCursor.getString(1));
}
item.setCategory(category);
items.add(item);
} while (cursor.moveToNext());
}
db.close();
// return contact list
return items;
}
public Item getItemById(int itemID) {
Item item = new Item();
String selectQuery =
"SELECT * FROM " + TABLE_ITEMS + " WHERE " + TABLE_ITEMS + "." + KEY_ID + "=" + itemID;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
item.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
item.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
item.setDescription(cursor.getString(cursor.getColumnIndex(KEY_DESCRIPTION)));
item.setPrice(cursor.getFloat(cursor.getColumnIndex(KEY_PRICE)));
item.setCreated_at(cursor.getString(cursor.getColumnIndex(KEY_CREATED_AT)));
item.setUpdated_at(cursor.getString(cursor.getColumnIndex(KEY_UPDATED_AT)));
Category category = new Category();
String selectCategory =
"SELECT * FROM "
+ TABLE_CATEGORIES
+ " WHERE "
+ TABLE_CATEGORIES
+ "."
+ KEY_ID
+ " = "
+ cursor.getInt(4);
Cursor categoryCursor = db.rawQuery(selectCategory, null);
if (categoryCursor.moveToFirst()) {
category.setId(categoryCursor.getInt(categoryCursor.getColumnIndex(KEY_ID)));
category.setName(categoryCursor.getString(categoryCursor.getColumnIndex(KEY_NAME)));
}
item.setCategory(category);
}
db.close();
return item;
}
public List<Category> getCategories() {
List<Category> categoryList = new ArrayList<Category>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_CATEGORIES;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Category category = new Category();
category.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
category.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
categoryList.add(category);
} while (cursor.moveToNext());
}
db.close();
// return contact list
return categoryList;
}
// Getting All Contacts
public List<Order> getAllOrders() {
List<Order> orderList = new ArrayList<Order>();
// Select All Query
String selectQuery =
"SELECT "
+ TABLE_ORDERS
+ ".id AS order_id, "
+ TABLE_TABLES
+ ".id AS table_id, * FROM "
+ TABLE_ORDERS
+ " JOIN "
+ TABLE_TABLES
+ " ON "
+ TABLE_ORDERS
+ "."
+ KEY_TABLE_ID
+ "="
+ TABLE_TABLES
+ "."
+ KEY_ID;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Order order = new Order();
order.setId(cursor.getInt(cursor.getColumnIndex(KEY_ORDER_ID)));
Table table = new Table();
table.setId(cursor.getInt(cursor.getColumnIndex(KEY_TABLE_ID)));
table.setNumber(cursor.getInt(cursor.getColumnIndex(KEY_NUMBER)));
table.setSeats(cursor.getInt(cursor.getColumnIndex(KEY_SEATS)));
table.setDescription(cursor.getString(cursor.getColumnIndex(KEY_DESCRIPTION)));
table.setTaken(cursor.getInt(cursor.getColumnIndex(KEY_TAKEN)) == 1);
order.setTable(table);
String selectItemsQuery =
"SELECT "
+ TABLE_ITEMS
+ ".id AS table_id, "
+ TABLE_ORDER_ITEM
+ ".id AS order_item_id, "
+ " * FROM "
+ TABLE_ITEMS
+ " JOIN "
+ TABLE_ORDER_ITEM
+ " ON "
+ TABLE_ITEMS
+ "."
+ KEY_ID
+ "="
+ TABLE_ORDER_ITEM
+ "."
+ KEY_ITEM_ID
+ " AND "
+ TABLE_ORDER_ITEM
+ "."
+ KEY_ORDER_ID
+ " = "
+ order.getId();
Cursor itemCursor = db.rawQuery(selectItemsQuery, null);
ArrayList<Item> items = new ArrayList<Item>();
if (itemCursor.moveToFirst()) {
do {
Item item = new Item();
item.setId(itemCursor.getInt(itemCursor.getColumnIndex("order_item_id")));
item.setName(itemCursor.getString(itemCursor.getColumnIndex(KEY_NAME)));
item.setDescription(itemCursor.getString(itemCursor.getColumnIndex(KEY_DESCRIPTION)));
item.setPrice(itemCursor.getFloat(itemCursor.getColumnIndex(KEY_PRICE)));
Category category = new Category();
String selectCategory =
"SELECT * FROM "
+ TABLE_CATEGORIES
+ " WHERE "
+ TABLE_CATEGORIES
+ "."
+ KEY_ID
+ " = "
+ itemCursor.getInt(4);
Cursor categoryCursor = db.rawQuery(selectCategory, null);
if (categoryCursor.moveToFirst()) {
category.setId(categoryCursor.getInt(categoryCursor.getColumnIndex(KEY_ID)));
category.setName(categoryCursor.getString(categoryCursor.getColumnIndex(KEY_NAME)));
}
item.setCategory(category);
items.add(item);
} while (itemCursor.moveToNext());
}
order.setItems(items);
orderList.add(order);
} while (cursor.moveToNext());
}
db.close();
// return order list
return orderList;
}