/**
* String ["Fine ID", "Title", "Checked Out", "Returned", "Fine Amount"]
*
* @param loggedInUser
* @return
* @throws SQLException
*/
public String[][] getOutstandingFines(User loggedInUser) throws SQLException {
ArrayList<ArrayList<String>> temp = new ArrayList<ArrayList<String>>();
// AAA
String statement =
"Select fine.fid, book.title, "
+ "borrowing.outdate, borrowing.indate,"
+ " fine.amount from fine, book, borrowing "
+ "where fine.borid = borrowing.borid AND "
+ "borrowing.callnumber = book.callnumber "
+ "AND fine.paiddate is null AND"
+ " borrowing.bid = "
+ loggedInUser.getBID();
System.out.println(loggedInUser.getBID());
ResultSet rs = sql(statement, SQLType.query);
while (rs.next()) {
System.out.println("yep");
ArrayList<String> row = new ArrayList<String>();
row.add(rs.getString("FID"));
row.add(rs.getString("Title"));
row.add(rs.getString("outdate"));
row.add(rs.getString("indate"));
row.add(rs.getString("Amount"));
temp.add(row);
}
if (temp.size() == 0) {
return new String[0][5];
}
String[][] newData = new String[temp.size()][temp.get(0).size()];
for (int outerCount = 0; outerCount < temp.size(); outerCount++) {
for (int innerCount = 0; innerCount < temp.get(0).size(); innerCount++) {
newData[outerCount][innerCount] = temp.get(outerCount).get(innerCount);
}
}
return newData; /*
String[][] date = {
{"22", "Long Island Ice Tea",
"January 15, 2014", "April 30, 2014", "$25.03"},
{"34534", "Noah>Daniel, inc",
"March 25, 2014", "April 25, 2014", "$26.02"},
{"6234", "That time I saw a ghost",
"March 10, 2014", "March 17, 2014", "$1.03"},
{"43", "Smokin the While Elephant",
"March 12, 2014", "March 16, 2014", "$5.01"}
};
return date;*/
}
/**
* Need each row formatted like this :) String["Call Number", "Title"]
*
* @param loggedInUser
* @return
* @throws SQLException
*/
public String[][] getHoldRequests(User loggedInUser) throws SQLException {
ArrayList<ArrayList<String>> temp = new ArrayList<ArrayList<String>>();
String statement =
"Select holdrequest.callnumber,"
+ " book.title, book.mainAuthor from holdrequest, book, "
+ "borrower WHERE holdrequest.bid = borrower.bid AND "
+ "holdrequest.callnumber = book.callnumber AND "
+ "holdrequest.bid = "
+ loggedInUser.getBID();
ResultSet rs = sql(statement, SQLType.query);
while (rs.next()) {
System.out.println("yep");
ArrayList<String> row = new ArrayList<String>();
row.add(rs.getString("callnumber"));
row.add(rs.getString("Title"));
row.add(rs.getString("MainAuthor"));
temp.add(row);
}
if (temp.size() == 0) {
return new String[0][3];
}
String[][] newData = new String[temp.size()][temp.get(0).size()];
for (int outerCount = 0; outerCount < temp.size(); outerCount++) {
for (int innerCount = 0; innerCount < temp.get(0).size(); innerCount++) {
newData[outerCount][innerCount] = temp.get(outerCount).get(innerCount);
}
}
return newData; /*
String[][] data = {
{"A2NRBS2", "Long Island Ice Tea",
"January 15, 2014", "Checked Out", "April 30, 2014"},
{"34534q5w", "Noah>Daniel, inc",
"March 25, 2014", "On Hold", "April 25, 2014"},
{"A6 FGD 234", "That time I saw a ghost",
"March 10, 2014", "Checked Out", "March 17, 2014"},
{"43A dfgdfg", "Smokin the While Elephant",
"March 12, 2014", "On Hold", "March 16, 2014"}
};
return data;*/
}
/**
* String ["Call Number", "Title", "Checked Out", "Due", ""]
*
* @param loggedInUser
* @return
* @throws SQLException
*/
public String[][] getLoanedBooks(User loggedInUser) throws SQLException {
ArrayList<ArrayList<String>> temp = new ArrayList<ArrayList<String>>();
String statement =
"select borrowing.callnumber, borrowing.duedate,"
+ " book.title, borrowing.outdate from borrowing,"
+ " book where borrowing.callnumber = book.callnumber"
+ " AND borrowing.indate is null AND borrowing.bid = "
+ loggedInUser.getBID();
ResultSet rs = sql(statement, SQLType.query);
while (rs.next()) {
System.out.println("yep");
ArrayList<String> row = new ArrayList<String>();
row.add(rs.getString("Callnumber"));
row.add(rs.getString("Title"));
row.add(rs.getString("outdate"));
row.add(rs.getString("duedate")); // TODO calculate due date
row.add("");
temp.add(row);
}
if (temp.size() == 0) {
return new String[0][5];
}
String[][] newData = new String[temp.size()][temp.get(0).size()];
for (int outerCount = 0; outerCount < temp.size(); outerCount++) {
for (int innerCount = 0; innerCount < temp.get(0).size(); innerCount++) {
newData[outerCount][innerCount] = temp.get(outerCount).get(innerCount);
}
}
return newData; /*
String[][] data = {
{"A2NRBS2", "Long Island Ice Tea",
"January 15, 2014", "April 30, 2014", ""},
{"34534q5w", "Noah>Daniel, inc",
"March 25, 2014", "April 25, 2014", ""},
{"A6 FGD 234", "That time I saw a ghost",
"March 10, 2014", "March 17, 2014", ""},
{"43A dfgdfg", "Smokin the While Elephant",
"March 12, 2014", "March 16, 2014", ""}
};
return data;*/
}
// Checks out a book, when given a call number
public void checkOut(ArrayList<BookCopy> copies, int bid)
throws SQLException, NotCheckedInException, BadCallNumberException, BadUserIDException,
UserCreationException, ParseException {
String statement;
ResultSet rs;
User user = getUser(bid);
Date date = new Date(System.currentTimeMillis());
Date dueDate = new Date(System.currentTimeMillis() + 604800000);
// Checkout all items
for (BookCopy copy : copies) {
System.out.println(dueDate.toString());
statement =
"insert into borrowing values (borid_counter.nextVal, "
+ user.getBID()
+ ", '"
+ copy.getCallNo()
+ "', "
+ copy.getCopyNo()
+ ", '"
+ formatDate(date)
+ "', '"
+ formatDate(dueDate)
+ "', Null)";
System.out.println(statement);
sql(statement, SQLType.insert);
System.out.println("insertCopy");
// update bookcopy
// Check BookCopies. If one has status "on-hold" delete the
// Hold Request. (Book was cleared for sign out in "confirmOkToCheckout" Method.
statement =
"select * from BookCopy where callnumber='"
+ copy.getCallNo()
+ "' and copyNo ="
+ copy.getCopyNo();
rs = sql(statement, SQLType.query);
System.out.println(statement);
rs.next();
if (rs.getString("status").equalsIgnoreCase("on-hold")) {
System.out.println("Was on hold");
// Update status to out
statement =
"update BookCopy set status='out' where "
+ "callnumber='"
+ copy.getCallNo()
+ "' and copyNo ="
+ copy.getCopyNo();
sql(statement, SQLType.query);
// Delete Hold Request
statement =
"delete * from BookCopy set where "
+ "callnumber='"
+ copy.getCallNo()
+ "' and copyNo ="
+ copy.getCopyNo();
sql(statement, SQLType.delete);
} else {
// Update status to out
statement =
"update BookCopy set status='out' where "
+ "callnumber='"
+ copy.getCallNo()
+ "' and copyNo ="
+ copy.getCopyNo();
System.out.println("setting to out");
sql(statement, SQLType.query);
}
}
// SQLException from the db
// notcheckedinexception if it's not 'borrowable'
// badCallNumberException if it isn't in the db. (vs. db connection errors)
// BadUserIDException if the user is not in the DB
this.updateStatusBar("Item(s) checked out");
}