public Order getOrderById(int id) {
// Select All Query
Order order = new Order();
String selectQuery =
"SELECT *, "
+ TABLE_ORDERS
+ ".ID as "
+ KEY_TABLE_ID
+ " FROM "
+ TABLE_ORDERS
+ " JOIN "
+ TABLE_TABLES
+ " ON "
+ TABLE_ORDERS
+ "."
+ KEY_TABLE_ID
+ "="
+ TABLE_TABLES
+ "."
+ KEY_ID
+ " WHERE "
+ TABLE_ORDERS
+ "."
+ KEY_ID
+ "="
+ id;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
order.setId(cursor.getInt(0));
order.setTotal(cursor.getFloat(cursor.getColumnIndex(KEY_TOTAL)));
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 * 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(0));
item.setName(itemCursor.getString(1));
item.setDescription(itemCursor.getString(2));
item.setPrice(itemCursor.getFloat(3));
item.setCategory(new Category());
items.add(item);
} while (itemCursor.moveToNext());
}
order.setItems(items);
}
db.close();
// return contact list
return order;
}
// 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;
}