· 9 years ago · Nov 29, 2016, 01:52 AM
1package com.example.friskybutcher.foodorder;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.SQLException;
7import android.database.sqlite.SQLiteDatabase;
8import android.database.sqlite.SQLiteOpenHelper;
9import android.support.v7.app.AppCompatActivity;
10import android.util.Log;
11import android.view.View;
12import android.widget.Toast;
13
14import java.io.BufferedReader;
15import java.io.FileReader;
16import java.util.HashMap;
17
18/**
19 * Created by friskybutcher on 23/11/16.
20 */
21
22public class DBManager extends AppCompatActivity
23{
24
25 private static final String DATABASE_NAME = "Restaurant";
26 private static final String TABLE_NAME = "food_items";
27 private static final String ORDER_TABLE = "orders";
28 private static final int DATABASE_VERSION = 1;
29
30 public static final String COL_1 = "_id";
31 public static final String COL_2 = "NAME";
32 public static final String COL_3 = "CATAGORY";
33 public static final String COL_4 = "PRICE";
34 public static final String COL_5 = "DESCRIPTION";
35 public static final String COL_6 = "STOCK";
36
37 public static final String COL_1_O = "_id";
38 public static final String COL_2_O = "NAME";
39 public static final String COL_3_O = "PRICE";
40
41
42 private static final String TAG = "DbAdapter";
43
44 private static final String CREATE_TABLE =
45 "CREATE TABLE " + TABLE_NAME +
46 " (" + COL_1 + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
47 COL_2 + " TEXT, " +
48 COL_3 + " TEXT, " +
49 COL_4 + " REAL, " +
50 COL_5 + " TEXT, " +
51 COL_6 + " REAL);";
52
53 private static final String CREATE_ORDER_TABLE =
54 "CREATE TABLE " + ORDER_TABLE +
55 " (" + COL_1_O + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
56 COL_2_O + " TEXT, " +
57 COL_3_O + " REAL);";
58
59 private final Context context;
60 private DatabaseHelper DBHelper;
61 private SQLiteDatabase db;
62
63 public DBManager(Context ctx)
64 {
65 this.context = ctx;
66 DBHelper = new DatabaseHelper(context);
67 }
68
69 private static class DatabaseHelper extends SQLiteOpenHelper
70 {
71 private SQLiteOpenHelper _openHelper;
72 DatabaseHelper(Context context)
73 {
74 super(context, DATABASE_NAME, null, DATABASE_VERSION);
75 }
76
77
78 @Override
79 public void onCreate(SQLiteDatabase db)
80 {
81 //Log.w(TAG, CREATE_TABLE);
82 db.execSQL(CREATE_TABLE);
83
84 //db.execSQL("create table " + TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,CATAGORY TEXT,PRICE REAL,DESCRIPTION TEXT,STOCK INTEGER)");
85 //db.execSQL("create table " + ORDER_TABLE + "(_id INTEGER PRIMARY KEY AUTOINCREMENT,NAME TEXT,PRICE REAL");
86 //Log.w(TAG, CREATE_ORDER_TABLE);
87 db.execSQL(CREATE_ORDER_TABLE);
88
89 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Spicy Chicken Wangs', 'Starters', 7.99, 'Locally sourced Irish Chicken Wings with super spicy sauce!', 150)");
90 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Mexican Quesadillas', 'Starters', 6.99, 'Delicious crunchy quesadillas', 120)");
91 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Pizza Balls', 'Starters', 8, 'One of our new recipies, Time to deliver a pizza ball!', 25)");
92 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Caesar Salad', 'Starters', 7.99, 'Freshly made classic, Vegetarian', 70)");
93 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Red Pepper & Tomato Soup', 'Soup', 5.50, 'Homeade creamy tomato soup with Bread', 20)");
94 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Potato & Leek', 'Soup', 6.25, 'Creamy soup with chunky chicken & veg', 0)");
95 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Seafood Chowder', 'Soup', 9, 'Our most popular soup!', 200)");
96 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Sweet Potato & Butternut Squash', 'Soup', 8, 'For the vegans', 10)");
97 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Beef Ramen', 'Soup', 10, 'Japanese beef with noodles and soup', 30)");
98 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Ham & Cheese toastie', 'Sandwiches', 4.50, 'Hard to beat a classic toastie', 350)");
99 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Turkey Club Bagel', 'Sandwiches', 6, 'Turkey, tomato, lettuce, crispy bacon, melted cheese', 25)");
100 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Breakfast Roll', 'Sandwiches', 5.50, 'As many full irish ingrediants as we can fit!', 35)");
101 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Spaghetti Bolagnese', 'Pasta', 11, 'An italian classic, with parmesan cheese', 150)");
102 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Spaghetti Carbonara', 'Pasta', 11.50, 'Our take on another classic', 150)");
103 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Risotto Taleggio e Pera', 'Pasta', 13, 'Carnaroli rice with caramalised pear & taleggio cheese', 20)");
104 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Seafood Linguine', 'Pasta', 13, 'Linguine with seafood and a cream sauce', 150)");
105 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Homeade Ravioli', 'Pasta', 14, 'Homeade pasta ravioli with chicken and veg', 10)");
106 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Chitarra ai Frutti di mare', 'Pasta', 15, 'Homemade fresh spaghetti with mix of seafood in cherry tomato sauce and garlic', 24)");
107 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Fish & Chips', 'Fish', 11, 'Battered Cod with Chip-shop chips', 50)");
108 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Grilles salmon with mashed potato', 'Fish', 13, 'Fresh irish salmon with a creamy mash and veg', 30)");
109 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Roast Supreme of Corn-fed Chicken', 'Main Course', 15, 'Tomato, Red Onion, Courgette, Chorizo and Chickpea Cassoulet', 12)");
110 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Beef Burger', 'Main Course', 12, 'Irish homeade beef burgers with chip-shop chips', 150)");
111 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Wild Mushroom Panzarotti', 'Main Course', 15, 'Tiger Prawns, Cherry Tomatoes, Rocket, Cream Sauce, Parmesan', 1)");
112 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Rack of Free-range Kilmolin Lamb', 'Main Course', 16, 'Rosemary Roast Potatoes, Cherry Tomatoes & Kalamata Olives, Salsa Verde', 20)");
113 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('9oz Black Angus Sirloin Steak', 'Main Course', 21, 'Sauteed Onions and Shitake Mushrooms, Truffle and Parmesan Chips, Green Peppercorn Sauce', 30)");
114 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Vanilla Crème Brulée', 'Desert', 6, 'Lemon Sorbet', 75)");
115 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Chocolate Brownie', 'Desert', 6, 'Vanilla ice-cream', 150)");
116 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Fig and Frangipan Tart', 'Desert', 6, 'Lemon Marscapone Cream', 20)");
117 db.execSQL("insert into " + TABLE_NAME + "(NAME, CATAGORY, PRICE, DESCRIPTION, STOCK) VALUES ('Mixed ice-cream', 'Desert', 4.50, 'Homeade chocolate, vanilla and mango ice-cream', 50)");
118 }
119
120 @Override
121 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
122 {
123 Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
124 db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
125 db.execSQL("DROP TABLE IF EXISTS " + ORDER_TABLE);
126 onCreate(db);
127 }
128
129 }
130
131 public DBManager open() throws SQLException
132 {
133 DBHelper = new DatabaseHelper(context);//DbHelper now has the ability (through SQLiteOpenHelper) to manage our database (through context)
134 db = DBHelper.getWritableDatabase();//opens database to be read or written
135 return this;
136 }
137
138 public Cursor getCategories()
139 {
140 //true, TABLE_NAME, new String[] { COL_1 ,COL_3 }, null, null, COL_3, null, null, null
141 Cursor mCursor = null;
142 //String q = "SELECT DISTINCT("+ COL_3 + "), " + COL_1 + " FROM " + TABLE_NAME + "GROUP BY (NOT NULL " + COL_3 + ")";
143 String w = "SELECT DISTINCT " + COL_3 + " AS " + COL_1 + "," + COL_3 + " FROM " + TABLE_NAME;
144 try
145 {
146 mCursor = db.rawQuery(w, null);
147
148 if(mCursor != null)
149 {
150 mCursor.moveToFirst();
151 }
152 return mCursor;
153 }
154 catch(SQLException e)
155 {
156 Log.e("Error", "Failed to get item" + e);
157 }
158 return mCursor;
159 }
160
161 public Cursor getStarters()
162 {
163 Cursor mCursor = null;
164 String starter_cata = "Starters";
165 String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COL_3 + " = '" + starter_cata + "'";
166
167 try
168 {
169 mCursor = db.rawQuery(q, null);
170
171 if(mCursor != null)
172 {
173 mCursor.moveToFirst();
174 }
175 return mCursor;
176 }
177 catch(SQLException e)
178 {
179 Log.e("Error", "Failed to get item" + e);
180 }
181 return mCursor;
182 }
183
184 public Cursor getSoup()
185 {
186 Cursor mCursor = null;
187 String soup_cata = "Soup";
188 String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COL_3 + " = '" + soup_cata + "'";
189
190 try
191 {
192 mCursor = db.rawQuery(q, null);
193
194 if(mCursor != null)
195 {
196 mCursor.moveToFirst();
197 }
198 return mCursor;
199 }
200 catch(SQLException e)
201 {
202 Log.e("Error", "Failed to get item" + e);
203 }
204 return mCursor;
205 }
206
207 public Cursor getSandwiches()
208 {
209 Cursor mCursor = null;
210 String sandwich_cata = "Sandwiches";
211 String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COL_3 + " = '" + sandwich_cata + "'";
212
213 try
214 {
215 mCursor = db.rawQuery(q, null);
216
217 if(mCursor != null)
218 {
219 mCursor.moveToFirst();
220 }
221 return mCursor;
222 }
223 catch(SQLException e)
224 {
225 Log.e("Error", "Failed to get item" + e);
226 }
227 return mCursor;
228 }
229
230 public Cursor getPasta()
231 {
232 Cursor mCursor = null;
233 String pasta_cata = "Pasta";
234 String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COL_3 + " = '" + pasta_cata + "'";
235
236 try
237 {
238 mCursor = db.rawQuery(q, null);
239
240 if(mCursor != null)
241 {
242 mCursor.moveToFirst();
243 }
244 return mCursor;
245 }
246 catch(SQLException e)
247 {
248 Log.e("Error", "Failed to get item" + e);
249 }
250 return mCursor;
251 }
252
253 public Cursor getFish()
254 {
255 Cursor mCursor = null;
256 String fish_cata = "Fish";
257 String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COL_3 + " = '" + fish_cata + "'";
258
259 try
260 {
261 mCursor = db.rawQuery(q, null);
262
263 if(mCursor != null)
264 {
265 mCursor.moveToFirst();
266 }
267 return mCursor;
268 }
269 catch(SQLException e)
270 {
271 Log.e("Error", "Failed to get item" + e);
272 }
273 return mCursor;
274 }
275
276 public Cursor getMainCourse()
277 {
278 Cursor mCursor = null;
279 String main_cata = "Main Course";
280 String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COL_3 + " = '" + main_cata + "'";
281
282 try
283 {
284 mCursor = db.rawQuery(q, null);
285
286 if(mCursor != null)
287 {
288 mCursor.moveToFirst();
289 }
290 return mCursor;
291 }
292 catch(SQLException e)
293 {
294 Log.e("Error", "Failed to get item" + e);
295 }
296 return mCursor;
297 }
298 public Cursor getDesert()
299 {
300 Cursor mCursor = null;
301 String desert_cata = "Desert";
302 String q = "SELECT * FROM " + TABLE_NAME + " WHERE " + COL_3 + " = '" + desert_cata + "'";
303
304 try
305 {
306 mCursor = db.rawQuery(q, null);
307
308 if(mCursor != null)
309 {
310 mCursor.moveToFirst();
311 }
312 return mCursor;
313 }
314 catch(SQLException e)
315 {
316 Log.e("Error", "Failed to get item" + e);
317 }
318 return mCursor;
319 }
320
321
322 public Cursor addToOrder(String id)
323 {
324 Cursor mCursor = null;
325 String q = "INSERT INTO " + ORDER_TABLE +
326 " SELECT _id, " + COL_2 +
327 ", " + COL_4 +
328 " FROM " + TABLE_NAME +
329 " WHERE " + " _id " +
330 "= " + id + ";";
331 try
332 {
333 mCursor = db.rawQuery(q, null);
334
335 if (mCursor != null)
336 {
337 mCursor.moveToFirst();
338 }
339 return mCursor;
340 }
341 catch(SQLException e)
342 {
343 Log.e("Error", "Failed to get item" + e);
344 }
345 return mCursor;
346 }
347
348 public Cursor viewOrder()
349 {
350 Cursor mCursor = null;
351 String q = "SELECT * FROM " + ORDER_TABLE + ";";
352
353 try
354 {
355 mCursor = db.rawQuery(q, null);
356
357 if(mCursor != null)
358 {
359 mCursor.moveToFirst();
360 }
361 return mCursor;
362 }
363 catch(SQLException e)
364 {
365 Log.e("Error", "Failed to get item" + e);
366 }
367 return mCursor;
368 }
369
370 public double totalPrice()
371 {
372 Cursor c = db.rawQuery("SELECT Sum(" + COL_3_O +
373 ") FROM " + ORDER_TABLE, null);
374 if ( c.moveToFirst() )
375 {
376 return c.getDouble(0);
377 }
378 return c.getDouble(0);
379 }
380
381 public void remove(long id)
382 {
383 String string = String.valueOf(id);
384 db.execSQL("DELETE FROM " + ORDER_TABLE + " WHERE _id = '" + string + "'");
385 }
386
387}