· 8 years ago · May 07, 2018, 03:14 AM
1public class LibraryBook implements Parcelable {
2 String title, author, isbn;
3 double fee;
4
5 @Override
6 public int describeContents() {
7 return 0;
8 }
9
10 @Override
11 public void writeToParcel(Parcel out, int flags) {
12 out.writeString(title);
13 out.writeString(author);
14 out.writeString(isbn);
15 out.writeDouble(fee);
16 }
17
18 public static final Parcelable.Creator<LibraryBook> CREATOR = new Parcelable.Creator<LibraryBook>() {
19 public LibraryBook createFromParcel(Parcel in) {
20 return new LibraryBook(in);
21 }
22
23 public LibraryBook[] newArray(int size) {
24 return new LibraryBook[size];
25 }
26 };
27
28 private LibraryBook(Parcel in) {
29 title = in.readString();
30 author = in.readString();
31 isbn = in.readString();
32 fee = in.readDouble();
33 }
34
35 public LibraryBook(String title, String author, String isbn, double fee ) {
36 this.title = title;
37 this.author = author;
38 this.isbn = isbn;
39 this.fee = fee;
40 }
41
42 public LibraryBook(String bookInfo){
43 String[] array = bookInfo.split("\n");
44 for(int i = 0; i < 4; i++){
45 String[] tempArray = array[i].split("\t");
46 array[i] = tempArray[1];
47 }
48 title = array[0];
49 author = array[1];
50 isbn = array[2];
51 fee = Double.parseDouble(array[3]);
52 }
53
54 public String getTitle() {
55 return title;
56 }
57
58 public void setTitle(String title) {
59 this.title = title;
60 }
61
62 public String getAuthor() {
63 return author;
64 }
65
66 public void setAuthor(String author) {
67 this.author = author;
68 }
69
70 public String getIsbn() {
71 return isbn;
72 }
73
74 public void setIsbn(String isbn) {
75 this.isbn = isbn;
76 }
77
78 public double getFee() {
79 return fee;
80 }
81
82 public void setFee(double fee) {
83 this.fee = fee;
84 }
85
86 public String toString(){
87 return "Book Title: t" + title +
88 "nAuthor: t" + author +
89 "nISBN: t" + isbn +
90 "nFee/hr: t" + fee;
91 }
92}
93
94public class SystemDataBase {
95
96public static final String DB_NAME = "library.db";
97public static final int DB_VERSION = 1;
98
99//User Table
100public static final String USER_TABLE = "user";
101
102public static final String USER_ID = "_id";
103public static final int USER_ID_COL = 0;
104
105public static final String USER_NAME = "user_name";
106public static final int USER_NAME_COL = 1;
107
108public static final String USER_PASSWORD = "user_password";
109public static final int USER_PASSWORD_COL = 2;
110
111public static final String USER_ADMIN = "admin";
112public static final int USER_ADMIN_COL = 3;
113
114//Book Table
115public static final String BOOK_TABLE = "book";
116
117public static final String BOOK_ID = "_id";
118public static final int BOOK_ID_COL = 0;
119
120public static final String BOOK_TITLE = "book_title";
121public static final int BOOK_TITLE_COL = 1;
122
123public static final String BOOK_AUTHOR = "book_author";
124public static final int BOOK_AUTHOR_COL = 2;
125
126public static final String BOOK_ISBN = "book_isbn";
127public static final int BOOK_ISBN_COL = 3;
128
129public static final String BOOK_FEE = "book_fee";
130public static final int BOOK_FEE_COL = 4;
131
132//Transaction Table
133public static final String TRANSACTION_TABLE = "main_transaction";
134
135public static final String TRANSACTION_ID = "_id";
136public static final int TRANSACTION_ID_COL = 0;
137
138public static final String TRANSACTION_TYPE = "transaction_type";
139public static final int TRANSACTION_TYPE_COL = 1;
140
141public static final String TRANSACTION_USER = "user_name";
142public static final int TRANSACTION_USER_COL = 2;
143
144public static final String TRANSACTION_DATE = "current_date";
145public static final int TRANSACTION_DATE_COL = 3;
146
147//Cancel Transaction Table
148public static final String CANCEL_TABLE = "cancel_transaction";
149
150public static final String CANCEL_ID = "_id";
151public static final int CANCEL_ID_COL = 0;
152
153public static final String CANCEL_HOLD_ID = "hold_id";
154public static final int CANCEL_HOLD_ID_COL = 1;
155
156public static final String CANCEL_TITLE = "book_title";
157public static final int CANCEL_TITLE_COL = 2;
158
159public static final String CANCEL_PICKUP = "pickup_date";
160public static final int CANCEL_PICKUP_COL = 3;
161
162public static final String CANCEL_RETURN = "return_date";
163public static final int CANCEL_RETURN_COL = 4;
164
165//Hold Table
166public static final String HOLD_TABLE = "hold_transaction";
167
168public static final String HOLD_ID = "_id";
169public static final int HOLD_ID_COL = 0;
170
171public static final String HOLD_RESERVATION = "reservation_number";
172public static final int HOLD_RESERVATION_COL = 1;
173
174public static final String HOLD_TOTAL = "transaction_total";
175public static final int HOLD_TOTAL_COL = 2;
176
177public static final String HOLD_TITLE = "book_title";
178public static final int HOLD_TITLE_COL = 3;
179
180public static final String HOLD_PICKUP = "pickup_date";
181public static final int HOLD_PICKUP_COL = 4;
182
183public static final String HOLD_RETURN = "return_date";
184public static final int HOLD_RETURN_COL = 5;
185
186//Create and drop table statements
187public static final String CREATE_USER_TABLE =
188 "CREATE TABLE " + USER_TABLE + " (" +
189 USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
190 USER_NAME + " TEXT NOT NULL UNIQUE, " +
191 USER_PASSWORD + " TEXT NOT NULL, " +
192 USER_ADMIN + " INTEGER NOT NULL);";
193
194public static final String CREATE_BOOK_TABLE =
195 "CREATE TABLE " + BOOK_TABLE + " (" +
196 BOOK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
197 BOOK_TITLE + " TEXT NOT NULL UNIQUE, " +
198 BOOK_AUTHOR + " TEXT NOT NULL, " +
199 BOOK_ISBN + " TEXT NOT NULL UNIQUE, " +
200 BOOK_FEE + " REAL NOT NULL);";
201
202public static final String CREATE_TRANSACTION_TABLE =
203 "CREATE TABLE " + TRANSACTION_TABLE + " (" +
204 TRANSACTION_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
205 TRANSACTION_TYPE + " TEXT NOT NULL, " +
206 TRANSACTION_USER + " TEXT NOT NULL, " +
207 TRANSACTION_DATE + " TEXT NOT NULL);";
208
209public static final String CREATE_CANCEL_TABLE =
210 "CREATE TABLE " + CANCEL_TABLE + " (" +
211 CANCEL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
212 CANCEL_HOLD_ID + " INTEGER NOT NULL, " +
213 CANCEL_TITLE + " TEXT NOT NULL, " +
214 CANCEL_PICKUP + " TEXT NOT NULL, " +
215 CANCEL_RETURN + " TEXT NOT NULL, " +
216 "FOREIGN KEY (" + CANCEL_ID + ") REFERENCES " + TRANSACTION_TABLE + "(" + TRANSACTION_ID + "), " +
217"FOREIGN KEY (" + CANCEL_HOLD_ID + ") REFERENCES " + HOLD_TABLE + "(" + HOLD_ID + "));";
218
219public static final String CREATE_HOLD_TABLE =
220 "CREATE TABLE " + HOLD_TABLE + " (" +
221 HOLD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
222 HOLD_RESERVATION + " INTEGER NOT NULL, " +
223 HOLD_TOTAL + " TEXT NOT NULL, " +
224 HOLD_TITLE + " TEXT NOT NULL, " +
225 HOLD_PICKUP + " TEXT NOT NULL, " +
226 HOLD_RETURN + " TEXT NOT NULL, " +
227 "FOREIGN KEY (" + HOLD_ID + ") REFERENCES " + TRANSACTION_TABLE + "(" + TRANSACTION_ID + "));";
228
229public static final String DROP_USER_TABLE =
230 "DROP TABLE IF EXISTS " + USER_TABLE;
231
232public static final String DROP_BOOK_TABLE =
233 "DROP TABLE IF EXISTS " + BOOK_TABLE;
234
235public static final String DROP_TRANSACTION_TABLE =
236 "DROP TABLE IF EXISTS " + TRANSACTION_TABLE;
237
238public static final String DROP_CANCEL_TABLE =
239 "DROP TABLE IF EXISTS " + CANCEL_TABLE;
240
241public static final String DROP_HOLD_TABLE =
242 "DROP TABLE IF EXISTS " + HOLD_TABLE;
243
244//DB and DB helper
245private SQLiteDatabase db;
246private DBHelper dbHelper;
247
248//Constructor
249public SystemDataBase(Context context) {
250 dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
251}
252
253//Private Methods
254private void openReadableDB() {
255 db = dbHelper.getReadableDatabase();
256}
257
258private void openWriteableDB() {
259 db = dbHelper.getWritableDatabase();
260}
261
262private void closeDB() {
263 if (db != null)
264 db.close();
265}
266
267private static LibraryUser getUserFromCursor(Cursor cursor) {
268 if (cursor == null || cursor.getCount() == 0){
269 return null;
270 }
271 else {
272 try {
273 LibraryUser user = new LibraryUser(
274 cursor.getString(USER_NAME_COL),
275 cursor.getString(USER_PASSWORD_COL),
276 cursor.getInt(USER_ADMIN_COL)
277 );
278 return user;
279 }
280 catch(Exception e) {
281 return null;
282 }
283 }
284}
285
286private static LogConverter getHoldLogFromCursor(Cursor cursor) {
287 if (cursor == null || cursor.getCount() == 0){
288 return null;
289 }
290 else {
291 try {
292 Log.e("Test1", cursor.getString(1));
293 Log.e("Test2", cursor.getString(2));
294 Log.e("Test3", cursor.getString(3));
295 Log.e("Test4", cursor.getString(4));
296 Log.e("Test5", cursor.getString(5));
297 Log.e("Test6", cursor.getString(6));
298 Log.e("Test7", cursor.getString(7));
299 Log.e("Test8", cursor.getString(8));
300
301 return new LogConverter(
302 cursor.getString(1),
303 cursor.getString(2),
304 cursor.getString(3),
305 cursor.getString(6),
306 cursor.getString(7),
307 cursor.getString(8),
308 cursor.getInt(4),
309 cursor.getDouble(5)
310
311 );
312
313 }
314 catch(Exception e) {
315 return null;
316 }
317 }
318}
319
320private static LogConverter getCancelLogFromCursor(Cursor cursor) {
321 if (cursor == null || cursor.getCount() == 0){
322 return null;
323 }
324 else {
325 try {
326 Log.e("Test1", cursor.getString(1));
327 Log.e("Test2", cursor.getString(2));
328 Log.e("Test3", cursor.getString(3));
329 Log.e("Test4", cursor.getString(4));
330 Log.e("Test5", cursor.getString(5));
331 Log.e("Test6", cursor.getString(6));
332 Log.e("Test7", cursor.getString(7));
333
334 return new LogConverter(
335 cursor.getString(1),
336 cursor.getString(2),
337 cursor.getString(3),
338 cursor.getString(5),
339 cursor.getString(6),
340 cursor.getString(7)
341 );
342
343 }
344 catch(Exception e) {
345 return null;
346 }
347 }
348}
349
350//Public Methods
351public long insertUser(LibraryUser user) throws SQLException{
352 ContentValues cv = new ContentValues();
353 cv.put(USER_NAME, user.getUserName());
354 cv.put(USER_PASSWORD, user.getUserPassword());
355 cv.put(USER_ADMIN, user.getIsAdmin());
356
357 long rowID;
358 this.openWriteableDB();
359 try {
360 rowID = db.insertOrThrow(USER_TABLE, null, cv);
361 } catch (SQLException e) {
362 throw e;
363 }
364
365 this.closeDB();
366
367 return rowID;
368}
369
370public LibraryUser getUser(String userName, String userPassword) {
371 String where = USER_NAME + "= ? AND " + USER_PASSWORD + "= ?";
372 String[] whereArgs = { userName, userPassword };
373
374 this.openReadableDB();
375 Cursor cursor = db.query(USER_TABLE,
376 null, where, whereArgs, null, null, null);
377 cursor.moveToFirst();
378 LibraryUser user = getUserFromCursor(cursor);
379 if (cursor != null)
380 cursor.close();
381 this.closeDB();
382
383 return user;
384}
385
386public long insertBook(LibraryBook book) throws SQLException{
387 ContentValues cv = new ContentValues();
388 cv.put(BOOK_TITLE, book.getTitle());
389 cv.put(BOOK_AUTHOR, book.getAuthor());
390 cv.put(BOOK_ISBN, book.getIsbn());
391 cv.put(BOOK_FEE, book.getFee());
392
393 long rowID;
394 this.openWriteableDB();
395 try {
396 rowID = db.insertOrThrow(BOOK_TABLE, null, cv);
397 } catch (SQLException e) {
398 throw e;
399 }
400
401 this.closeDB();
402
403 return rowID;
404}
405
406public long insertLog(LogConverter log)throws SQLException{
407 ContentValues cv = new ContentValues();
408 ContentValues cv2 = null;
409 if(log.getTransactionType().equalsIgnoreCase("new account"))
410 log.putCV(cv);
411 else{
412 cv2 = new ContentValues();
413 log.putCV(cv, cv2);
414 }
415
416
417 long rowID;
418 this.openWriteableDB();
419 try {
420 rowID = db.insertOrThrow(TRANSACTION_TABLE, null, cv);
421 } catch (SQLException e) {
422 throw e;
423 }
424
425 if(log.getTransactionType().equalsIgnoreCase("Place Hold")){
426 cv2.put(HOLD_ID, rowID);
427 try {
428 db.insertOrThrow(HOLD_TABLE, null, cv2);
429 } catch (SQLException e) {
430 throw e;
431 }
432 }
433
434 if(log.getTransactionType().equalsIgnoreCase("Cancel Hold")){
435 cv2.put(CANCEL_ID, rowID);
436 try {
437 db.insertOrThrow(CANCEL_TABLE, null, cv2);
438 } catch (SQLException e) {
439 throw e;
440 }
441 }
442
443 this.closeDB();
444
445 return rowID;
446}
447
448public ArrayList<String> getHoldLogs(String userName) {
449 ArrayList<String> list = new ArrayList<>();
450 String where = TRANSACTION_USER + "= ?";
451 String[] whereArgs = {userName};
452
453 this.openReadableDB();
454
455 String tableQuery = "(SELECT * " +
456 "FROM main_transaction " +
457 "NATURAL JOIN hold_transaction t1 " +
458 "LEFT JOIN cancel_transaction t2 ON t1._id = t2.hold_id " +
459 "WHERE t2.hold_id IS NULL)";
460
461
462 Cursor cursor = db.query(tableQuery,
463 null, where, whereArgs, null, null, null);
464
465 while(cursor.moveToNext()){
466 list.add(cursor.getInt(0) + "t" + getHoldLogFromCursor(cursor).toString());
467 }
468
469 if(cursor != null)
470 cursor.close();
471 closeDB();
472
473 if(list.isEmpty())
474 list.add("No reservations found!");
475
476 return list;
477
478}
479public ArrayList<String> getLogs() {
480 ArrayList<String> list = new ArrayList<>();
481 openReadableDB();
482 Cursor cursor = db.query(TRANSACTION_TABLE,
483 null, null, null, null, null, null);
484
485 while(cursor.moveToNext()) {
486 if(cursor.getString(TRANSACTION_TYPE_COL).equalsIgnoreCase("New Account")) {
487 LogConverter temp = new LogConverter(
488 cursor.getString(TRANSACTION_TYPE_COL),
489 cursor.getString(TRANSACTION_USER_COL),
490 cursor.getString(TRANSACTION_DATE_COL));
491 list.add(temp.toString());
492 } else if(cursor.getString(TRANSACTION_TYPE_COL).equalsIgnoreCase("Place Hold")){
493 LogConverter temp = getLog(cursor.getString(TRANSACTION_ID_COL));
494 list.add(temp.toString());
495 } else {
496 LogConverter temp = getCLog(cursor.getString(TRANSACTION_ID_COL));
497 list.add(temp.toString());
498 }
499 }
500 if(cursor != null)
501 cursor.close();
502 closeDB();
503
504 return list;
505}
506
507public LogConverter getLog(String iD){
508 if(db == null)
509 openReadableDB();
510 Cursor cursor = db.rawQuery("SELECT * " +
511 "FROM main_transaction " +
512 "NATURAL JOIN hold_transaction " +
513 "WHERE _id = " + iD, null);
514 cursor.moveToFirst();
515 return getHoldLogFromCursor(cursor);
516}
517
518public LogConverter getCLog(String iD){
519 if(db == null)
520 openReadableDB();
521 Cursor cursor = db.rawQuery("SELECT * " +
522 "FROM main_transaction " +
523 "NATURAL JOIN cancel_transaction " +
524 "WHERE _id = " + iD, null);
525 cursor.moveToFirst();
526 return getCancelLogFromCursor(cursor);
527}
528
529public ArrayList<String> getBooks(String pickupTime, String returnTime) {
530 ArrayList<String> list = new ArrayList<>();
531 openReadableDB();
532
533 String holdTransactions = "SELECT * " +
534 "FROM hold_transaction t1 " +
535 "LEFT JOIN cancel_transaction t2 ON t1._id = t2.hold_id " +
536 "WHERE t2.hold_id IS NULL";
537
538 String invalidBooks = "SELECT * " +
539 "FROM (" + holdTransactions + ") " +
540 "WHERE pickup_date BETWEEN '" + pickupTime + "' AND '" + returnTime+ "' OR " +
541 "return_date BETWEEN '" + pickupTime + "' AND '" + returnTime + "'";
542
543 String validBooks = "SELECT t3.book_title, t3.book_author, t3.book_isbn, t3.book_fee " +
544 "FROM " + BOOK_TABLE + " as t3 " +
545 "LEFT JOIN (" + invalidBooks + ") as t4 ON t3.book_title = t4.book_title " +
546 "WHERE t4.book_title IS NULL";
547
548 Cursor cursor = db.rawQuery(validBooks, null);
549
550 while(cursor.moveToNext()) {
551 LibraryBook temp = new LibraryBook(
552 cursor.getString(0),
553 cursor.getString(1),
554 cursor.getString(2),
555 cursor.getDouble(3));
556 list.add(temp.toString());
557 }
558
559 if(cursor != null)
560 cursor.close();
561 closeDB();
562
563 if(list.isEmpty())
564 list.add("No books available for this time range!");
565 return list;
566}
567
568//DB Helper Class
569private static class DBHelper extends SQLiteOpenHelper {
570
571 public DBHelper(Context context, String name,
572 SQLiteDatabase.CursorFactory factory, int version) {
573 super(context, name, factory, version);
574 }
575
576 @Override
577 public void onCreate(SQLiteDatabase db) {
578 // create tables
579 db.execSQL(CREATE_USER_TABLE);
580 db.execSQL(CREATE_BOOK_TABLE);
581 db.execSQL(CREATE_TRANSACTION_TABLE);
582 db.execSQL(CREATE_CANCEL_TABLE);
583 db.execSQL(CREATE_HOLD_TABLE);
584
585 // insert default lists
586 db.execSQL("INSERT INTO user(user_name, user_password, admin) VALUES('a@lice5', '@csit100', 0)");
587 db.execSQL("INSERT INTO user(user_name, user_password, admin) VALUES('$brian7', '123abc##', 0)");
588 db.execSQL("INSERT INTO user(user_name, user_password, admin) VALUES('!chris12!', 'CHRIS12!!', 0)");
589 db.execSQL("INSERT INTO user(user_name, user_password, admin) VALUES('!admin2', '!admin2', 1)");
590
591 db.execSQL("INSERT INTO book(book_title, book_author, book_isbn, book_fee) VALUES('Hot Java', 'S. Narayanan', '123-ABC-101', 0.05)");
592 db.execSQL("INSERT INTO book(book_title, book_author, book_isbn, book_fee) VALUES('Fun Java', 'Y. Byun', 'ABCDEF-09', 1.00)");
593 db.execSQL("INSERT INTO book(book_title, book_author, book_isbn, book_fee) VALUES('Algorithm for Java', 'K. Alice', 'CDE-777-123', 0.25)");
594 }
595
596 @Override
597 public void onUpgrade(SQLiteDatabase db,
598 int oldVersion, int newVersion) {
599
600 Log.d("TRANSACTION LOGS", "Upgrading db from version "
601 + oldVersion + " to " + newVersion);
602
603 db.execSQL(SystemDataBase.DROP_BOOK_TABLE);
604 db.execSQL(SystemDataBase.DROP_USER_TABLE);
605 db.execSQL(SystemDataBase.DROP_TRANSACTION_TABLE);
606 db.execSQL(SystemDataBase.DROP_CANCEL_TABLE);
607 db.execSQL(SystemDataBase.DROP_HOLD_TABLE);
608 onCreate(db);
609 }
610}
611
612
613}