API tools faq
paste
Guest User

Untitled

a guest
May 6th, 2018
97
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.56 KB | None | 0 0
raw download clone embed print report
  1. public 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.  
  94. public class SystemDataBase {
  95.  
  96. public static final String DB_NAME = "library.db";
  97. public static final int DB_VERSION = 1;
  98.  
  99. //User Table
  100. public static final String USER_TABLE = "user";
  101.  
  102. public static final String USER_ID = "_id";
  103. public static final int USER_ID_COL = 0;
  104.  
  105. public static final String USER_NAME = "user_name";
  106. public static final int USER_NAME_COL = 1;
  107.  
  108. public static final String USER_PASSWORD = "user_password";
  109. public static final int USER_PASSWORD_COL = 2;
  110.  
  111. public static final String USER_ADMIN = "admin";
  112. public static final int USER_ADMIN_COL = 3;
  113.  
  114. //Book Table
  115. public static final String BOOK_TABLE = "book";
  116.  
  117. public static final String BOOK_ID = "_id";
  118. public static final int BOOK_ID_COL = 0;
  119.  
  120. public static final String BOOK_TITLE = "book_title";
  121. public static final int BOOK_TITLE_COL = 1;
  122.  
  123. public static final String BOOK_AUTHOR = "book_author";
  124. public static final int BOOK_AUTHOR_COL = 2;
  125.  
  126. public static final String BOOK_ISBN = "book_isbn";
  127. public static final int BOOK_ISBN_COL = 3;
  128.  
  129. public static final String BOOK_FEE = "book_fee";
  130. public static final int BOOK_FEE_COL = 4;
  131.  
  132. //Transaction Table
  133. public static final String TRANSACTION_TABLE = "main_transaction";
  134.  
  135. public static final String TRANSACTION_ID = "_id";
  136. public static final int TRANSACTION_ID_COL = 0;
  137.  
  138. public static final String TRANSACTION_TYPE = "transaction_type";
  139. public static final int TRANSACTION_TYPE_COL = 1;
  140.  
  141. public static final String TRANSACTION_USER = "user_name";
  142. public static final int TRANSACTION_USER_COL = 2;
  143.  
  144. public static final String TRANSACTION_DATE = "current_date";
  145. public static final int TRANSACTION_DATE_COL = 3;
  146.  
  147. //Cancel Transaction Table
  148. public static final String CANCEL_TABLE = "cancel_transaction";
  149.  
  150. public static final String CANCEL_ID = "_id";
  151. public static final int CANCEL_ID_COL = 0;
  152.  
  153. public static final String CANCEL_HOLD_ID = "hold_id";
  154. public static final int CANCEL_HOLD_ID_COL = 1;
  155.  
  156. public static final String CANCEL_TITLE = "book_title";
  157. public static final int CANCEL_TITLE_COL = 2;
  158.  
  159. public static final String CANCEL_PICKUP = "pickup_date";
  160. public static final int CANCEL_PICKUP_COL = 3;
  161.  
  162. public static final String CANCEL_RETURN = "return_date";
  163. public static final int CANCEL_RETURN_COL = 4;
  164.  
  165. //Hold Table
  166. public static final String HOLD_TABLE = "hold_transaction";
  167.  
  168. public static final String HOLD_ID = "_id";
  169. public static final int HOLD_ID_COL = 0;
  170.  
  171. public static final String HOLD_RESERVATION = "reservation_number";
  172. public static final int HOLD_RESERVATION_COL = 1;
  173.  
  174. public static final String HOLD_TOTAL = "transaction_total";
  175. public static final int HOLD_TOTAL_COL = 2;
  176.  
  177. public static final String HOLD_TITLE = "book_title";
  178. public static final int HOLD_TITLE_COL = 3;
  179.  
  180. public static final String HOLD_PICKUP = "pickup_date";
  181. public static final int HOLD_PICKUP_COL = 4;
  182.  
  183. public static final String HOLD_RETURN = "return_date";
  184. public static final int HOLD_RETURN_COL = 5;
  185.  
  186. //Create and drop table statements
  187. public 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.  
  194. public 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.  
  202. public 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.  
  209. public 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.  
  219. public 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.  
  229. public static final String DROP_USER_TABLE =
  230. "DROP TABLE IF EXISTS " + USER_TABLE;
  231.  
  232. public static final String DROP_BOOK_TABLE =
  233. "DROP TABLE IF EXISTS " + BOOK_TABLE;
  234.  
  235. public static final String DROP_TRANSACTION_TABLE =
  236. "DROP TABLE IF EXISTS " + TRANSACTION_TABLE;
  237.  
  238. public static final String DROP_CANCEL_TABLE =
  239. "DROP TABLE IF EXISTS " + CANCEL_TABLE;
  240.  
  241. public static final String DROP_HOLD_TABLE =
  242. "DROP TABLE IF EXISTS " + HOLD_TABLE;
  243.  
  244. //DB and DB helper
  245. private SQLiteDatabase db;
  246. private DBHelper dbHelper;
  247.  
  248. //Constructor
  249. public SystemDataBase(Context context) {
  250. dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
  251. }
  252.  
  253. //Private Methods
  254. private void openReadableDB() {
  255. db = dbHelper.getReadableDatabase();
  256. }
  257.  
  258. private void openWriteableDB() {
  259. db = dbHelper.getWritableDatabase();
  260. }
  261.  
  262. private void closeDB() {
  263. if (db != null)
  264. db.close();
  265. }
  266.  
  267. private 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.  
  286. private 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.  
  320. private 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
  351. public 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.  
  370. public 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.  
  386. public 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.  
  406. public 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.  
  448. public 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. }
  479. public 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.  
  507. public 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.  
  518. public 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.  
  529. public 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
  569. private 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. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!