· 7 years ago · Feb 01, 2019, 11:58 AM
1package controllers;
2
3import java.sql.Timestamp;
4import java.text.DateFormat;
5import java.text.ParseException;
6import java.text.SimpleDateFormat;
7import java.time.LocalDate;
8import java.time.format.DateTimeFormatter;
9import java.util.ArrayList;
10import java.util.Date;
11
12import client.ClientConnection;
13import entities.*;
14import entities.Account.UserType;
15import entities.Book.bookType;
16import entities.UserAccount.accountStatus;
17
18public class DatabaseController {
19
20 private static ClientConnection clientConnection;
21
22 static Account loggedAccount;
23
24 /**
25 * create new account
26 *
27 * @param arr
28 */
29 public static void addAccount(UserAccount newAccount) {
30 ArrayList<String> arr = new ArrayList<String>();
31 String query = "INSERT INTO account(ID, firstName, lastName, eMail, mobileNum, userID, userName, password, userType, status, delays,logged)VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
32 arr.add(String.valueOf(newAccount.getID()));
33 arr.add(newAccount.getFirstName());
34 arr.add(newAccount.getLastName());
35 arr.add(newAccount.getEmail());
36 arr.add(String.valueOf(newAccount.getMobileNum()));
37 arr.add(String.valueOf(newAccount.getAccountID()));
38 arr.add(newAccount.getUserName());
39 arr.add(newAccount.getPassword());
40 arr.add(newAccount.getUserType().toString());
41 arr.add(newAccount.getStatus().toString());
42 arr.add(String.valueOf(newAccount.getDelays()));
43 arr.add(String.valueOf(0));
44 arr.add(query);
45 clientConnection.executeQuery(arr);
46 clientConnection.executeQuery(
47 "INSERT INTO archive(userID, ID, userName, password, firstName, lastName, mobileNum, eMail)VALUES ('"
48 + newAccount.getAccountID() + "','" + newAccount.getID() + "','" + newAccount.getUserName()
49 + "','" + newAccount.getPassword() + "','" + newAccount.getFirstName() + "','"
50 + newAccount.getLastName() + "','" + newAccount.getMobileNum() + "','" + newAccount.getEmail()
51 + "')");
52
53 }
54
55 /**
56 * Generate a new account ID for the new user
57 *
58 * @return account ID for the new user
59 */
60 public static int generateAccountID() {
61 return ((getTableRowsNumber("account", null) + 1) * 264 + 759);
62 }
63
64 /**
65 * <<<<<<< HEAD returns rows number in table according to type if type =null ,
66 * return all the rows number ======= returns rows number in table >>>>>>>
67 * branch 'master' of https://github.com/Athl1n3/OBL-Project.git
68 *
69 * @param tableName
70 * @return int table rows number
71 */
72 public static int getTableRowsNumber(String tableName, String type) {
73 if (type != null)
74 clientConnection.executeQuery("SELECT COUNT(*) FROM " + tableName + " WHERE userType = '" + type + "';");
75 else
76 clientConnection.executeQuery("SELECT COUNT(*) FROM " + tableName + ";");
77 return Integer.parseInt(clientConnection.getList().get(0));
78 }
79
80 public static int getCount(String table, String field, String fieldVal) {
81 clientConnection.executeQuery("SELECT COUNT(*) FROM " + table + " WHERE " + field + "= '" + fieldVal + "';");
82 return Integer.parseInt(clientConnection.getList().get(0));
83 }
84
85 /**
86 * update account details including logged field
87 *
88 * @param account
89 */
90 public static void updateAccount(Account account) {
91 /*
92 * String query = "UPDATE account SET firstName = '" + account.getFirstName() +
93 * "', lastName = '" + account.getLastName() + "', eMail = '" +
94 * account.getEmail() + "', mobileNum = '" + account.getMobileNum() +
95 * "', userName = '" + account.getUserName() + "', password = '" +
96 * account.getPassword() + "', logged = '" + (account.isLogged() == true ? 1:0)
97 * + "' WHERE userID = '" + account.getAccountID() + "';";
98 */
99 String query = "UPDATE account SET firstName = '" + account.getFirstName() + "', lastName = '"
100 + account.getLastName() + "', eMail = '" + account.getEmail() + "', mobileNum = '"
101 + account.getMobileNum() + "', userName = '" + account.getUserName() + "', password = '"
102 + account.getPassword() + "' WHERE userID = '" + account.getAccountID() + "';";
103 clientConnection.executeQuery(query);
104 }
105
106 public static void logAccount(Account account) {
107 clientConnection.executeQuery("UPDATE account SET logged = '" + (account.isLogged() == true ? 1 : 0)
108 + "' WHERE userID = '" + account.getAccountID() + "';");
109 }
110
111 /**
112 * update user's status
113 *
114 * @param userAccount
115 */
116 public static void updateUserStatus(UserAccount userAccount, boolean resetDelays) {
117 String query;
118 if (resetDelays)
119 query = "UPDATE account SET status = '" + userAccount.getStatus() + "', delays = '0' WHERE userID = '"
120 + userAccount.getAccountID() + "';";
121 else
122 query = "UPDATE account SET status = '" + userAccount.getStatus() + "' WHERE userID = '"
123 + userAccount.getAccountID() + "';";
124 clientConnection.executeQuery(query);
125 }
126
127 /**
128 * Lock user account
129 *
130 * @param userAccount
131 */
132 public static boolean lockAccount(int accountID) {
133 String query = "UPDATE account SET status = 'Locked' WHERE userID = '" + accountID + "';";
134 clientConnection.executeQuery(query);
135 clientConnection.getObject();
136 return (Boolean) clientConnection.getObject();
137 }
138
139 /**
140 * update user's delays
141 *
142 * @param userAccount
143 */
144 public static void updateUserDelays(UserAccount userAccount) {
145 String query = "UPDATE account SET delays = '" + userAccount.getDelays() + "' WHERE userID = '"
146 + userAccount.getAccountID() + "';";
147 clientConnection.executeQuery(query);
148 }
149
150 /**
151 * finds the account in DB according to user id and returned it, if the account
152 * doesn't exists then return null
153 *
154 * @param ID
155 * @return Account
156 */
157 public static Account getAccount(int ID) {
158 clientConnection.executeQuery("SELECT * FROM Account WHERE ID = " + ID + ";");
159 ArrayList<String> res = clientConnection.getList();
160 if (res.size() != 0) {
161 Account userAccount;
162 if (res.get(8).equals("User")) {
163 userAccount = new UserAccount();
164 ((UserAccount) userAccount).parseArrayIntoAccount(res);
165 } else {
166 if (res.get(8).equals("Librarian"))
167 userAccount = new LibrarianAccount();
168 else
169 userAccount = new ManagerAccount();
170 ((LibrarianAccount) userAccount).parseArrayIntoAccount(res);
171 }
172 return userAccount;
173 } else
174 return null;
175 }
176
177 /**
178 * finds the account in DB according to user id and returned it, if the account
179 * doesn't exists then return null
180 *
181 * @param ID
182 * @return Account
183 */
184 public static Account getAccountByAccountID(int accountID) {
185 clientConnection.executeQuery("SELECT * FROM Account WHERE userID = " + accountID + ";");
186 ArrayList<String> res = clientConnection.getList();
187 if (res.size() != 0) {
188 Account userAccount;
189 if (res.get(8).equals("User")) {
190 userAccount = new UserAccount();
191 ((UserAccount) userAccount).parseArrayIntoAccount(res);
192 } else {
193 if (res.get(8).equals("Librarian"))
194 userAccount = new LibrarianAccount();
195 else
196 userAccount = new ManagerAccount();
197 ((LibrarianAccount) userAccount).parseArrayIntoAccount(res);
198 }
199 return userAccount;
200 } else
201 return null;
202 }
203
204 public static boolean ifExists(String table, String field, String fieldVal) {
205 clientConnection
206 .executeQuery("SELECT EXISTS(SELECT * FROM " + table + " WHERE " + field + " = '" + fieldVal + "');");
207 if (clientConnection.getList().get(0).equals("0"))
208 return false;// Field value doesn't exist
209 return true;// Field value already exists
210 }
211
212 /**
213 * finds the account in DB according to (username && password) and returned it,
214 * if the account doesn't exists then return null
215 *
216 * @param username
217 * @param password
218 * @return Account
219 */
220 public static Account getAccount(String username, String password) {
221 String query = "SELECT * FROM Account WHERE username = '" + username + "' AND password = '" + password + "';";
222 clientConnection.executeQuery(query);
223 ArrayList<String> res = clientConnection.getList();
224 if (res.size() != 0) {
225 Account loggedAccount;
226 if (res.get(8).equals("User")) {
227 loggedAccount = new UserAccount();
228 ((UserAccount) loggedAccount).parseArrayIntoAccount(res);
229 } else {
230 if (res.get(8).equals("Librarian"))
231 loggedAccount = new LibrarianAccount();
232 else
233 loggedAccount = new ManagerAccount();
234 ((LibrarianAccount) loggedAccount).parseArrayIntoAccount(res);
235 }
236 return loggedAccount;
237 } else
238 return null;
239 }
240
241 /**
242 * return user accounts list according to account status
243 *
244 * @param status
245 * @return arrayList of user account
246 */
247 public static ArrayList<UserAccount> getUserAccounts(accountStatus status) {
248 clientConnection.executeQuery("SELECT * FROM Account WHERE userType = '" + UserType.User.toString()
249 + "' AND status = " + status + "';");
250 try {
251 ArrayList<String> res = clientConnection.getList();
252 ArrayList<UserAccount> arr = new ArrayList<UserAccount>();
253 while (res.size() != 0) {
254 UserAccount userAccount = new UserAccount();
255 userAccount.parseArrayIntoAccount(res);
256 arr.add(userAccount);
257 res.subList(0, 13).clear();
258 }
259 return arr;
260 } catch (NullPointerException e) {
261 return null;
262 }
263 }
264
265 /**
266 * adds new book to the library book list
267 *
268 * @param newBook
269 */
270 public static void addBook(Book newBook) {
271 ArrayList<String> arr = new ArrayList<String>();
272 String query = "INSERT INTO BOOk(bookID, name, author, edition, printYear, subject, description, catalog,"
273 + " tableOfContents, shelf, copiesNumber, Type, availableCopies) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
274 arr.add(String.valueOf(newBook.getBookID()));
275 arr.add(newBook.getName());
276 arr.add(newBook.getAuthor());
277 arr.add(newBook.getEdition());
278 arr.add(String.valueOf(newBook.getPrintYear()));
279 arr.add(newBook.getSubject());
280 arr.add(newBook.getDescription());
281 arr.add(String.valueOf(newBook.getCatalog()));
282 arr.add(newBook.getTableOfContents());
283 arr.add(newBook.getShelf());
284 arr.add(String.valueOf(newBook.getCopiesNumber()));
285 arr.add(String.valueOf(newBook.getBookType()));
286 arr.add(String.valueOf(newBook.getAvailableCopies()));
287 arr.add(query);
288 clientConnection.executeQuery(arr);
289 }
290
291 /**
292 * this function updates existed book data according to book id
293 *
294 * @param existingBook
295 */
296 public static void editBook(Book existingBook) {
297 clientConnection.executeQuery("UPDATE book SET copiesNumber = '" + existingBook.getCopiesNumber()
298 + "', shelf = '" + existingBook.getShelf() + "', description = '" + existingBook.getDescription()
299 + "', author = '" + existingBook.getAuthor() + "', edition = '" + existingBook.getEdition()
300 + "', printYear = '" + existingBook.getPrintYear() + "', catalog = '" + existingBook.getCatalog()
301 + "', subject = '" + existingBook.getSubject() + "', tableOfContents = '"
302 + existingBook.getTableOfContents() + "', type = '" + existingBook.getBookType().name() + "', name = '"
303 + existingBook.getName() + "' WHERE bookID = '" + existingBook.getBookID() + "' ;");
304 // need to update the pdf contents file path
305 }
306
307 /**
308 * deletes book from library books list
309 *
310 * @param bookToDelete
311 */
312 public static void deleteBook(int bookID) {
313 clientConnection.executeQuery("DELETE FROM book WHERE bookID = '" + bookID + "';");
314 }
315
316 /**
317 * search for a specific book according to its id and if its founded, return it,
318 * else return null
319 *
320 * @param id
321 * @return Book
322 */
323 public static Book getBook(int id) {
324 clientConnection.executeQuery("SELECT * FROM book WHERE bookID= '" + id + "' ;");
325 ArrayList<String> res = clientConnection.getList();
326 if (res.size() != 0) {
327 Book book = new Book(Integer.parseInt(res.get(0)), res.get(1), res.get(2), res.get(3),
328 Integer.parseInt(res.get(4)), res.get(5), res.get(6), Integer.parseInt(res.get(7)), res.get(8),
329 res.get(9), Integer.parseInt(res.get(10)),
330 res.get(11).equals("Regular") ? bookType.Regular : bookType.Wanted, Integer.parseInt(res.get(12)));
331
332 return book;
333 }
334
335 return null;
336 }
337
338 /**
339 * return list of books from DB
340 *
341 * @return ArrayList<Book>
342 */
343 public static ArrayList<Book> getAllBooks() {
344
345 clientConnection.executeQuery("SELECT * FROM book");
346
347 ArrayList<String> res = clientConnection.getList();
348 ArrayList<Book> bookList = new ArrayList<Book>();
349 while (res.size() != 0) {
350 Book book = new Book(Integer.parseInt(res.get(0)), res.get(1), res.get(2), res.get(3),
351 Integer.parseInt(res.get(4)), res.get(5), res.get(6), Integer.parseInt(res.get(7)), res.get(8),
352 res.get(9), Integer.parseInt(res.get(10)),
353 res.get(11).equals("Regular") ? bookType.Regular : bookType.Wanted, Integer.parseInt(res.get(12)));
354 res.subList(0, 13).clear();
355 bookList.add(book);
356 }
357
358 return bookList;
359 }
360
361 /**
362 * search for specific book according to its name,author, subject or description
363 *
364 * @param str search for book
365 * @param searchBy it could be name, author, subject or description
366 * @return ArrayList<Book>
367 */
368 public static ArrayList<Book> bookSearch(String str, String searchBy) throws NumberFormatException {
369 switch (searchBy.toLowerCase()) {
370 case "book id":
371 clientConnection.executeQuery("SELECT * FROM book WHERE bookID = '" + Integer.parseInt(str) + "' ;");
372 break;
373 case "name":
374 clientConnection.executeQuery("SELECT * FROM book WHERE name LIKE '%" + str.toLowerCase() + "%' ;");
375 break;
376 case "author":
377 clientConnection.executeQuery("SELECT * FROM book WHERE author LIKE '%" + str.toLowerCase() + "%' ;");
378 break;
379 case "subject":
380 clientConnection.executeQuery("SELECT * FROM book WHERE subject LIKE '%" + str.toLowerCase() + "%' ;");
381 break;
382 case "description":
383 clientConnection.executeQuery("SELECT * FROM book WHERE description LIKE '%" + str.toLowerCase() + "%' ;");
384 break;
385 default:
386 return null;
387 }
388 ArrayList<String> res = clientConnection.getList();
389 if (res.isEmpty())
390 return null;
391 ArrayList<Book> bookList = new ArrayList<Book>();
392 while (res.size() != 0) {
393 Book book = new Book(Integer.parseInt(res.get(0)), res.get(1), res.get(2), res.get(3),
394 Integer.parseInt(res.get(4)), res.get(5), res.get(6), Integer.parseInt(res.get(7)), res.get(8),
395 res.get(9), Integer.parseInt(res.get(10)),
396 res.get(11).equals("Regular") ? bookType.Regular : bookType.Wanted, Integer.parseInt(res.get(12)));
397 res.subList(0, 13).clear();
398 bookList.add(book);
399 }
400 return bookList;
401 }
402
403 /**
404 * update the Book availableCopies -=1
405 *
406 * @param book
407 */
408 public static void updateBookAvailableCopies(Book book, int val) {
409 clientConnection.executeQuery("UPDATE Book SET availableCopies = '" + (book.getAvailableCopies() + val)
410 + "' WHERE BookID = '" + book.getBookID() + "';");
411 }
412
413 /**
414 * add new lentBook to the user lentBook list
415 *
416 * @param newLentBook
417 */
418 public static void addLentBook(LentBook newLentBook) {
419 ArrayList<String> arr = new ArrayList<String>();
420 String query = "INSERT INTO LentBook(userID, bookID,copySerialNumber, issueDate, dueDate, late) VALUES(?,?,?,?,?,?)";
421 arr.add(String.valueOf(newLentBook.getUserID()));
422 arr.add(String.valueOf(newLentBook.getBook().getBookID()));
423 arr.add(String.valueOf(newLentBook.getBookCopy().getSerialNumber()));
424 arr.add(String.valueOf(newLentBook.getIssueDate()));
425 arr.add(String.valueOf(newLentBook.getDueDate()));
426 arr.add(String.valueOf("0"));
427 arr.add(query);
428 clientConnection.executeQuery(arr);
429 }
430
431 public static void deleteLendBook(int accountID, int bookID) {
432 clientConnection
433 .executeQuery("DELETE FROM LentBook WHERE userID = '" + accountID + "' AND bookID = '" + bookID + "';");
434 }
435
436 /**
437 * return the user lent books list from DB if(userID>0) return only the user
438 * lent Book list, if(userID<0) return the whole lent Book list, if userID = 0
439 * return only the late users
440 *
441 * @return ArrayList
442 */
443 public static ArrayList<LentBook> getLentBookList(int userID) {
444 String query;
445 if (userID > 0) // only for specific user according to userID
446 query = "SELECT userID, bookID, copySerialNumber, issueDate, dueDate, returnDate, late FROM LentBook WHERE userID = '"
447 + userID + "' AND returned = '0';";
448 else if (userID < 0)// all the list
449 query = "SELECT userID, bookID, copySerialNumber, issueDate, dueDate, returnDate, late FROM LentBook AND returned = '0';";
450 else // only the late one [userID = 0]
451 query = "SELECT userID,bookID, copySerialNumber, issueDate ,dueDate, returnDate, late FROM LentBook WHERE late = '1' AND returned = '0';";
452
453 clientConnection.executeQuery(query);
454 try {
455 ArrayList<String> res = clientConnection.getList();
456 ArrayList<LentBook> lentBookList = new ArrayList<LentBook>();
457 while (res.size() != 0) {
458 LentBook lentBook = new LentBook(Integer.parseInt(res.get(0)), getBook(Integer.parseInt(res.get(1))),
459 getBookCopy(res.get(1), res.get(2)), LocalDate.parse(res.get(3)), LocalDate.parse(res.get(4)),
460 LocalDate.parse(res.get(5)), res.get(6).equals("1") ? true : false);
461 res.subList(0, 7).clear();
462 lentBookList.add(lentBook);
463 }
464 return lentBookList;
465 } catch (NullPointerException e) {
466 return null;
467 }
468 }
469
470 public static LentBook getLentBook(int userID, int bookID, String serialNumber) {
471 clientConnection.executeQuery(
472 "SELECT userID,bookID, copySerialNumber, issueDate,dueDate,returnDate,late FROM LentBook WHERE userID = '"
473 + userID + "' AND bookID = '" + bookID + "' AND copySerialNumber = '" + serialNumber
474 + "' AND returned = '0';");
475 ArrayList<String> res = clientConnection.getList();
476 LentBook lentBook;
477 if (!res.isEmpty()) {
478 lentBook = new LentBook(Integer.parseInt(res.get(0)), getBook(Integer.parseInt(res.get(1))),
479 getBookCopy(res.get(1), res.get(2)), LocalDate.parse(res.get(3)), LocalDate.parse(res.get(4)),
480 LocalDate.parse(res.get(5)), res.get(6).equals("1") ? true : false);
481 return lentBook;
482 }
483 return null;
484 }
485
486 /**
487 * get book copy
488 *
489 * @param bookID
490 * @param serialNumber
491 * @return
492 */
493 public static BookCopy getBookCopy(String bookID, String serialNumber) {
494 clientConnection.executeQuery(
495 "SELECT * FROM BookCopy WHERE serialNumber= '" + serialNumber + "' AND bookID = '" + bookID + "' ;");
496 ArrayList<String> res = clientConnection.getList();
497 if (res.size() != 0) {
498 BookCopy bookCopy = new BookCopy(Integer.parseInt(res.get(0)), res.get(1), LocalDate.parse(res.get(2)),
499 res.get(3).equals("1") ? true : false);
500 return bookCopy;
501 }
502 return null;
503 }
504
505 /**
506 * return the book copies list from DB
507 *
508 * @return ArrayList<BookCopy>
509 */
510 public static ArrayList<BookCopy> getbookCopyList(int bookID) {
511
512 clientConnection.executeQuery("SELECT * FROM BookCopy WHERE bookID = '" + bookID + "';");
513 ArrayList<String> res = clientConnection.getList();
514 ArrayList<BookCopy> bookCopyList = new ArrayList<BookCopy>();
515 while (res.size() != 0) {
516 BookCopy bookCopy = new BookCopy(Integer.parseInt(res.get(0)), res.get(1), LocalDate.parse(res.get(2)),
517 res.get(2).equals("1") ? true : false);
518 res.subList(0, 4).clear();
519 bookCopyList.add(bookCopy);
520 }
521
522 return bookCopyList;
523 }
524
525 /**
526 * get all the copies
527 *
528 * @return bookCopy list
529 */
530 public static ArrayList<BookCopy> getAllCopies() {
531 clientConnection.executeQuery("SELECT * FROM BookCopy;");
532 ArrayList<String> res = clientConnection.getList();
533 ArrayList<BookCopy> bookCopyList = new ArrayList<BookCopy>();
534 while (res.size() != 0) {
535 BookCopy bookCopy = new BookCopy(Integer.parseInt(res.get(0)), res.get(1), LocalDate.parse(res.get(2)),
536 res.get(2).equals("1") ? true : false);
537 res.subList(0, 4).clear();
538 bookCopyList.add(bookCopy);
539 }
540
541 return bookCopyList;
542 }
543
544 /**
545 * deletes specific copy from booCopy table in DB
546 *
547 * @param bookID
548 * @param serialNumber
549 */
550 public static void deleteBookCopy(int bookID, String serialNumber) {
551
552 clientConnection.executeQuery(
553 "DELETE FROM BookCopy WHERE bookID = '" + bookID + "' AND copySerialNumber = '" + serialNumber + "';");
554 }
555
556 /**
557 * add new book Copy to BookCopy table in DB
558 *
559 * @param copy
560 */
561 public static void addBookCopy(BookCopy copy) {
562 ArrayList<String> arr = new ArrayList<String>();
563 String query = "INSERT INTO BookCopy(bookID,serialNumber, purchaseDate) VALUES(?,?,?)";
564 arr.add(String.valueOf(copy.getBookID()));
565 arr.add(copy.getSerialNumber());
566 // we don't need to set the lent field, because its given a default value = 0
567 arr.add(String.valueOf(copy.getPurchaseDate()));
568 arr.add(query);
569 clientConnection.executeQuery(arr);
570 }
571
572 /**
573 * update the BookCopy isLent field
574 *
575 * @param bookCopy
576 */
577 public static void updateBookCopy(BookCopy bookCopy) {
578 clientConnection
579 .executeQuery("UPDATE bookCopy SET isLent = '" + (bookCopy.isLent() ? 1 : 0) + "' WHERE bookID = '"
580 + bookCopy.getBookID() + "' AND serialNumber = '" + bookCopy.getSerialNumber() + "';");
581 // return ((Boolean) clientConnection.getObject()) == true ? true : false;
582 }
583
584 /**
585 * update return date or due date in lent book table
586 *
587 * @param LentBook
588 */
589 public static void updateBookReturnDate(LentBook lentBook) {
590 clientConnection.executeQuery("UPDATE LentBook SET dueDate = '" + lentBook.getDueDate() + "' WHERE userID = '"
591 + lentBook.getUserID() + "' AND bookID = '" + lentBook.getBook().getBookID()
592 + "' AND copySerialNumber = '" + lentBook.getBookCopy().getSerialNumber() + "';");
593 }
594
595 /**
596 * update returned and return date fields in lentBook table
597 *
598 * @param lentBook
599 */
600 public static void returnBook(LentBook lentBook) {
601 ArrayList<String> arr = new ArrayList<>();
602 String query = "UPDATE LentBook SET returnDate = '" + lentBook.getReturnDate() + "', returned = '"
603 + (lentBook.isReturned() ? 1 : 0) + "' WHERE userID = '" + lentBook.getUserID() + "' AND bookID = '"
604 + lentBook.getBook().getBookID() + "' AND copySerialNumber = '"
605 + lentBook.getBookCopy().getSerialNumber() + "';";
606 arr.add("#");
607 arr.add(query);
608 clientConnection.executeQuery(query);
609 }
610
611 public static boolean isLate(LentBook lentBook) {
612 clientConnection.executeQuery("SELECT late FROM LentBook WHERE userID = '" + lentBook.getUserID()
613 + "' AND bookID = '" + lentBook.getBook().getBookID() + "' AND copySerialNumber = '"
614 + lentBook.getBookCopy().getSerialNumber() + "';");
615 if (Integer.parseInt(clientConnection.getList().get(0)) == 1)
616 return true;
617 return false;
618 }
619
620 /**
621 * place an Book Order in DB
622 *
623 * @param order
624 */
625 public static void placeOrder(BookOrder order) {
626 ArrayList<String> arr = new ArrayList<String>();
627 String query = "INSERT INTO BookOrder(orderID, userID, bookID, orderDate) VALUES(?,?,?,?)";
628 arr.add(String.valueOf(order.getOrderID()));
629 arr.add(String.valueOf(order.getUserID()));
630 arr.add(String.valueOf(order.getBookID()));
631 arr.add(String.valueOf(order.getOrderDate()));
632 arr.add(query);
633 clientConnection.executeQuery(arr);
634 // clientConnection.executeQuery("Select * FROM BookOrder Order By orderDate ASC
635 // LIMIT 1");
636 // System.out.println(clientConnection.getList());
637
638 }
639
640 /**
641 * returns the last order id
642 *
643 * @return int
644 */
645 public static int getLatestOrderID() {
646 clientConnection.executeQuery("SELECT orderID FROM BookOrder ORDER BY orderID DESC");
647 if (clientConnection.getList().size() != 0)
648 return Integer.parseInt(clientConnection.getList().get(0));
649 else
650 return 0;
651 }
652
653 /**
654 * check existence of specific order
655 *
656 * @param userID
657 * @param bookID
658 * @return Boolean
659 */
660 public static boolean checkExistingOrder(int userID, int bookID) {
661 clientConnection
662 .executeQuery("SELECT * FROM BookOrder WHERE userID = '" + userID + "' AND bookID = '" + bookID + "';");
663 ArrayList<String> res = clientConnection.getList();
664 if (res.size() != 0) {
665 return true;
666 }
667 return false;
668 }
669
670 /**
671 * check if there is any orders for specific book
672 *
673 * @param bookID
674 * @return Boolean
675 */
676 public static boolean checkExistngBookOrder(int bookID) {
677 clientConnection.executeQuery("SELECT * FROM BookOrder WHERE bookID = '" + bookID + "';");
678 ArrayList<String> res = clientConnection.getList();
679 if (res.size() != 0) {
680 return true;
681 }
682 return false;
683 }
684
685 /**
686 * this function returns the user's original data from DB according to its id,
687 * if user not founded,return null
688 *
689 * @param id
690 * @return Archive
691 */
692 public static Archive getArchiveData(int id) {
693 clientConnection.executeQuery("SELECT * FROM archive WHERE ID= '" + id + "' ;");
694 ArrayList<String> res = clientConnection.getList();
695 if (res.size() != 0) {
696 Archive archive = new Archive(Integer.parseInt(res.get(0)), Integer.parseInt(res.get(1)), res.get(2),
697 res.get(3), res.get(4), res.get(5), res.get(6), res.get(7));
698 return archive;
699 }
700
701 return null;
702
703 }
704
705 /**
706 * return user activity list from DB
707 *
708 * @param AccountID
709 * @return ArrayList<UserActivity>
710 */
711 public static ArrayList<UserActivity> getUserActivity(int AccountID) {
712 clientConnection.executeQuery(
713 "SELECT userid, activityName, date FROM useractivity WHERE userID = '" + AccountID + "';");
714 ArrayList<String> res = clientConnection.getList();
715 ArrayList<UserActivity> activityList = new ArrayList<UserActivity>();
716 DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
717 while (res.size() != 0) {
718 try {
719 Date parsedDate = dateFormat.parse(res.get(2));
720 UserActivity activity = new UserActivity(Integer.parseInt(res.get(0)), res.get(1),
721 new Timestamp(parsedDate.getTime()));
722 res.subList(0, 3).clear();
723 activityList.add(activity);
724 } catch (ParseException e) {
725 // TODO Auto-generated catch block
726 e.printStackTrace();
727 }
728 }
729 return activityList;
730 }
731
732 /**
733 * adding user activity to userActivity table in DB
734 *
735 * @param ID
736 * @param activity
737 */
738 public static void addActivity(int ID, String activity) {
739 ArrayList<String> arr = new ArrayList<String>();
740 String query = "INSERT INTO userActivity(userID, activityName, date) VALUES(?,?,?);";
741 clientConnection.executeQuery("SELECT COUNT(*) FROM account;");
742 // arr.add(String.valueOf(getTableRowsNumber("userActivity", null) + 1));
743 arr.add(String.valueOf(ID));
744 arr.add(activity);
745 // get the current date and time to be saved in DB
746 Timestamp now = new Timestamp(new Date().getTime());
747 arr.add(String.valueOf(now));
748 arr.add(query);
749 clientConnection.executeQuery(arr);
750 }
751
752 /**
753 * return user notifications list from DB
754 *
755 * @param AccountID
756 * @return ArrayList<Notification>
757 */
758 public static ArrayList<Notification> getNotifications(int AccountID) {
759 if (AccountID != 1 && AccountID != 2)
760 clientConnection.executeQuery(
761 "SELECT notificationNum, userID, date, message, messageType FROM notification WHERE userID = '"
762 + AccountID + "';");
763 else if (AccountID == 1)
764 clientConnection.executeQuery(
765 "SELECT notificationNum, userID, date, message, messageType FROM notification WHERE userType = 'Manager' OR userType='Librarian';");
766 else
767 clientConnection.executeQuery(
768 "SELECT notificationNum, userID, date, message, messageType FROM notification WHERE userType = 'Librarian';");
769 ArrayList<String> res = clientConnection.getList();
770 ArrayList<Notification> notificationsList = new ArrayList<Notification>();
771 DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
772 while (res.size() != 0) {
773 try {
774 Date parsedDate = dateFormat.parse(res.get(2));
775 Notification notification = new Notification(Integer.parseInt(res.get(0)), Integer.parseInt(res.get(1)),
776 new Timestamp(parsedDate.getTime()), res.get(3), res.get(4));
777 res.subList(0, 5).clear();
778 notificationsList.add(notification);
779 } catch (ParseException e) {
780 // TODO Auto-generated catch block
781 e.printStackTrace();
782 }
783 }
784 return notificationsList;
785 }
786
787 /**
788 * get the closest return date from DB according to BookID
789 *
790 * @param bookID
791 * @return LocalDate
792 */
793 public static LocalDate getClosestReturnDate(int bookID) {
794 clientConnection
795 .executeQuery("SELECT dueDate From LentBook WHERE bookID = '" + bookID + "' ORDER BY dueDate LIMIT 1");
796 ArrayList<String> res = clientConnection.getList();
797 if (!res.isEmpty()) {
798 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-d");
799 return LocalDate.parse(res.get(0), formatter);
800 } else
801 return null;
802 }
803
804 /**
805 * Delete notification from database
806 *
807 * @param delNotf
808 */
809 public static void deleteNotfication(Notification delNotf) {
810 clientConnection.executeQuery(
811 "DELETE FROM notification WHERE notificationNum = '" + delNotf.getNotificationNum() + "';");
812 }
813
814 /**
815 * fills activities report from DB and return it
816 *
817 * @return ActivitiesReport
818 */
819 public static ActivitiesReport getActivityReport() {
820 ActivitiesReport report = new ActivitiesReport();
821 report.setTotalUsers(getTableRowsNumber("account", UserType.User.toString()));
822 report.setActiveUsersNumber(getUserTypeNumberAccordingToStatus(accountStatus.Active.toString()));
823 report.setFrozenUsersNumber(getUserTypeNumberAccordingToStatus(accountStatus.Suspended.toString()));
824 report.setLockedUsersNumber(getUserTypeNumberAccordingToStatus(accountStatus.Locked.toString()));
825
826 // get book list from DB
827 report.setBooks(getAllBooks());
828 report.setAccounts(getAllLateUsers());
829 return report;
830 }
831
832 /**
833 * get the users list witch they where late in return book at least once
834 *
835 * @return users accounts
836 */
837 public static ArrayList<UserAccount> getAllLateUsers() {
838 String query = "SELECT DISTINCT userID FROM LentBook WHERE late = '1' AND returned = '1';";
839 clientConnection.executeQuery(query);
840 try {
841 ArrayList<UserAccount> accounts = new ArrayList<UserAccount>();
842 ArrayList<String> res = clientConnection.getList();
843 while (res.size() != 0) {
844 accounts.add((UserAccount) getAccount(Integer.parseInt(res.get(0))));
845 res.remove(0);
846 }
847 return accounts;
848
849 } catch (NullPointerException e) {
850 return null;
851 }
852 }
853
854 /**
855 * get the late book list from lentBook table
856 *
857 * @return book list
858 */
859 public static ArrayList<Book> getAllLateBooks() {
860 String query = "SELECT DISTINCT bookID FROM LentBook WHERE late = '1' AND returned = '1';";
861 clientConnection.executeQuery(query);
862 try {
863 ArrayList<Book> bookList = new ArrayList<Book>();
864 ArrayList<String> res = clientConnection.getList();
865 while (res.size() != 0) {
866 Book book = getBook(Integer.parseInt(res.get(0)));
867 if (book != null)
868 bookList.add(book);
869 res.remove(0);
870 }
871 return bookList;
872
873 } catch (NullPointerException e) {
874 return null;
875 }
876 }
877
878 /**
879 * get all the late copies for specific Book as list of lentBooks
880 *
881 * @param bookID
882 * @return
883 */
884 public static ArrayList<LentBook> getLateCopiesForSpecificBook(int bookID) {
885 String query = "SELECT DISTINCT copySerialNumber FROM LentBook WHERE bookID= '" + bookID
886 + "' AND late = '1' AND returned = '1' ;";
887 clientConnection.executeQuery(query);
888 try {
889 ArrayList<LentBook> arr = new ArrayList<LentBook>();
890 ArrayList<String> res = clientConnection.getList();
891 while (res.size() != 0) {
892 LentBook lentBook = getlentBook(bookID, res.get(0));
893 if (lentBook != null)
894 arr.add(lentBook);
895 res.remove(0);
896 }
897 return arr;
898
899 } catch (NullPointerException e) {
900 return null;
901 }
902 }
903
904 /**
905 * get late lentBook according to bookID and copy SerialNumber
906 *
907 * @param bookID
908 * @param serialNumber
909 * @return lentBook
910 */
911 private static LentBook getlentBook(int bookID, String serialNumber) {
912 clientConnection.executeQuery("SELECT userID, bookID, copySerialNumber, issueDate, dueDate, returnDate, late"
913 + " FROM LentBook WHERE bookID= '" + bookID + "' AND copySerialNumber = '" + serialNumber
914 + "' AND late = '1' AND returned = '1' ;");
915 ArrayList<String> res = clientConnection.getList();
916 LentBook lentBook;
917 if (!res.isEmpty()) {
918 lentBook = new LentBook(Integer.parseInt(res.get(0)), getBook(Integer.parseInt(res.get(1))),
919 getBookCopy(res.get(1), res.get(2)), LocalDate.parse(res.get(3)), LocalDate.parse(res.get(4)),
920 LocalDate.parse(res.get(5)), res.get(6).equals("1") ? true : false);
921 return lentBook;
922 }
923 return null;
924
925 }
926
927 /**
928 *
929 * @param status
930 * @return int number of users according to there status
931 */
932 public static int getUserTypeNumberAccordingToStatus(String status) {
933 clientConnection
934 .executeQuery("SELECT COUNT(*) FROM account WHERE userType = 'User' AND status = '" + status + "';");
935 ArrayList<String> arr;
936 arr = clientConnection.getList();
937 if (arr.size() == 0) {
938 return 0;
939 }
940 return Integer.parseInt(arr.get(0));
941 }
942
943 public static boolean updateLentBook(LentBook lentBook) {
944 clientConnection.executeQuery(
945 "UPDATE lentbook SET dueDate = '" + lentBook.getDueDate() + "', late = '0' WHERE userID = '"
946 + lentBook.getUserID() + "' AND bookID = '" + lentBook.getBook().getBookID() + "';");
947 return ((Boolean) clientConnection.getObject()) == true ? true : false;
948 }
949
950 public static boolean addManualExtend(ManualExtend extendLog) {
951 String query = "INSERT INTO ManualExtend(bookID, userID, workerName, extendDate, dueDate) VALUES(?,?,?,?,?)";
952 ArrayList<String> arr = new ArrayList<String>();
953 arr.add(String.valueOf(extendLog.getBookID()));
954 arr.add(String.valueOf(extendLog.getUserID()));
955 arr.add(String.valueOf(extendLog.getWorkerName()));
956 arr.add(String.valueOf(extendLog.getExtendDate()));
957 arr.add(String.valueOf(extendLog.getDueDate()));
958 arr.add(query);
959 clientConnection.executeQuery(arr);
960
961 return ((Boolean) clientConnection.getObject()) == true ? true : false;
962 }
963
964 /**
965 * Get user scheduled suspension if it exists
966 *
967 * @param userID to get suspension scheduled
968 * @return scheduled suspension data / Null if it doesn't exist
969 */
970 public static LocalDate getSchedueledSuspension(int userID) {
971 if (ifExists("scheduledSuspension", "userID", String.valueOf(userID))) {
972 clientConnection.executeQuery("SELECT untilDate FROM scheduledSuspension WHERE userID = '" + userID + "'");
973 return LocalDate.parse(clientConnection.getList().get(0));
974 }
975 return null;
976 }
977
978 /**
979 * Add a scheduled suspension
980 *
981 * @param userID to suspend
982 * @param untilDate until what date
983 * @return if executed successfully
984 */
985 public static boolean addScheduledSuspension(int userID, LocalDate untilDate) {
986 if (ifExists("scheduledSuspension", "userID", String.valueOf(userID))) {
987 clientConnection.executeQuery(
988 "UPDATE scheduledSuspension SET untilDate ='" + untilDate + "' WHERE userID = '" + userID + "'");
989 } else
990 clientConnection.executeQuery(
991 "INSERT INTO scheduledSuspension(userID, untilDate)VALUES ('" + userID + "','" + untilDate + "')");
992 return ((Boolean) clientConnection.getObject()) == true ? true : false;
993 }
994
995 /**
996 * Delete scheduled account suspension
997 *
998 * @param userID
999 * @return if executed successfully
1000 */
1001 public static boolean deleteScheduledSuspension(int userID) {
1002 if (ifExists("scheduledSuspension", "userID", String.valueOf(userID))) {
1003 clientConnection.executeQuery("DELETE FROM scheduledSuspension WHERE userID = '" + userID + "'");
1004 return true;
1005 }
1006 return false;
1007 }
1008
1009 /**
1010 * Initiate a new client connection to the server
1011 *
1012 * @param newClientConnection
1013 */
1014 public static void InitiateClient(ClientConnection newClientConnection) {
1015 clientConnection = newClientConnection;
1016 }
1017
1018 /**
1019 * Shutdown client server connection when primary stage closes and logout logged
1020 * in account
1021 */
1022 public static void terminateClient() {
1023 if (loggedAccount != null) {
1024 loggedAccount.setLogged(false);
1025 logAccount(loggedAccount);
1026 System.out.println("Logging user out");
1027 }
1028 clientConnection.terminate();
1029 }
1030
1031 /**
1032 * This method was built only for testing purposes (External system sends a
1033 * graduation note with graduated student ID to OBL server)
1034 *
1035 * @param accountID
1036 */
1037 public static void graduateStudent(Integer studentID) {
1038 clientConnection.graduateStudent(studentID);
1039 }
1040
1041 public static void saveFile(String bookName, String filePath, int bookID) {
1042 ArrayList<String> arr = new ArrayList<String>();
1043 arr.add(bookName + ".pdf");
1044 arr.add(filePath);
1045 arr.add(String.valueOf(bookID));
1046 arr.add("&");
1047 clientConnection.saveFile(arr);
1048 }
1049
1050}