?? formprocess.java
字號:
"'" +borrowrec.getItemCallNumber() +"', '" +borrowrec.getStaffIDBorrow() +
"', '" +borrowrec.getStaffIDReturn() +"', '" +borrowrec.getBorrowerID() +
"', '" +borrowrec.getBorrowerEmail() +"', '" +borrowrec.getBorrowDate() +
"', '" +borrowrec.getDueDate() +"', '" +borrowrec.getReturnDate() +
"', '" +borrowrec.getBorrowStatus() +"' )";
if (debug) out.println("<br>Query for borrowrec: " +query);
myResultSet = formDatabaseOperation(query);
borrowrec.resetVariable();
session.setAttribute("borrowrec", borrowrec);
totalBorrow = totalBorrow + 1;
//Update the libraryitem database
query = "UPDATE libraryitem SET itemTotAvailable = itemTotAvailable - 1" +
" , itemTotBorrow = itemTotBorrow + 1 " +
" WHERE itemCallNumber ='" +itemCallNumber +"'";
myResultSet = formDatabaseOperation(query);
if (debug) out.println("<br>Query for libraryitem: " +query);
}
}
// Update the user database
if (totalBorrow > 0){
query = "UPDATE user SET userQuotaAvailable = userQuotaAvailable - " +totalBorrow +
" WHERE userID ='" +borrowID +"'";
myResultSet = formDatabaseOperation(query);
if (debug) out.println("<br>Query for user database: " +query);
}
}
dbprocess.setDBName("jsp_library");
dbprocess.setTblName("libraryitem");
dbprocess.setSQLQuery("Query: " +query);
dbprocess.setProcessResult("<B>Borrow Record success added</B>");
// If delete operation having error, then display the error messager
if(totalRecErr>0){
dbprocess.setProcessMsg(totalBorrow +" item(s) success added" +
"<br><br><font color='RED'>" +totalRecErr + "</font> record(s) having problem" +
"<ul>" +recErrReason +"</ul>");
}else{
dbprocess.setProcessMsg(totalBorrow +" item(s) success added");
}
dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> " +
"<a href='/JavaLibrary/operation/borrow.jsp'>[Add another borrow record]</a>");
dbprocess.setProcessComplete(true);
break;
case 'r':
if(debug) out.println("return");
if(request.getParameterValues("borrowRecNumber") != null){
String errorMsg;
int borrowRecNumber=0;
String borrowerID = ""; String reserverID = ""; String borrowStatus = "";
itemCallNumber = "";
String dueDate = "";
Date todayDate = new Date();
String todayDateStr = ServletUtilities.dateToString(todayDate);
Date reserveCancelDateAfterInform = ServletUtilities.diffrenceDay(todayDate, 7);
String reserveCancelDateAfterInformStr = ServletUtilities.dateToString(reserveCancelDateAfterInform);
int overdueDay = 0;
float penaltyAmount = 0;
float totalPenaltyAmount = 0;
totalRecErr=0;
recErrReason= new StringBuffer("");
StringBuffer recUpdateInfo = new StringBuffer("");
StringBuffer penaltyInfo = new StringBuffer("");
int totalReserveUpdate=0;
int totalReturnNumber=0;
paramValues = request.getParameterValues("borrowRecNumber");
if (debug) out.println("<BR>borrowerID: " +borrowerID);
if (paramValues.length > 0 ) {
for (int i=0; i<paramValues.length; i++) {
// Get the itemCallNumber and calculate the total due day and penalty amount
borrowRecNumber = Integer.parseInt(paramValues[i]);
if (debug) out.println("<br><br>borrowRecNumber: " +borrowRecNumber);
query = "SELECT *, (TO_DAYS(NOW())-TO_DAYS(dueDate)) as overdueDay " +
" FROM borrowrec where borrowRecNumber = " +borrowRecNumber;
if(debug) out.println("<br>Query(Select): " +query);
try {
Connection connection = connectionPool.getConnection();
myResultSet = DatabaseUtilities.getQueryResultSet(connection, query, false);
connectionPool.free(connection);
} catch(Exception e) {
errorMsg = "Error: " + e;
}
overdueDay=0;
penaltyAmount=0;
if (myResultSet != null){
try{
while(myResultSet.next()) {
borrowerID = myResultSet.getString("borrowerID");
itemCallNumber = myResultSet.getString("itemCallNumber");
dueDate = myResultSet.getString("dueDate");
overdueDay = Integer.parseInt(myResultSet.getString("overdueDay"));
}
} catch(SQLException sqle) {
System.err.println("Error connecting: " +sqle);
}
}
if(debug) out.println("<br>itemCallNumber: " +itemCallNumber);
// Update the penaltyrec
if(overdueDay > 0) {
if(debug) out.println("<br>Update the penaltyrec");
penaltyAmount = (float) (overdueDay * 1.5);
query = "INSERT INTO penaltyrec (borrowRecNumber, borrowerID, overdueDay, penaltyAmount)" +
" VALUES (" +borrowRecNumber +", '" +borrowerID +"', " +overdueDay +", " +penaltyAmount +")";
myResultSet = formDatabaseOperation(query);
totalPenaltyAmount = totalPenaltyAmount +penaltyAmount;
penaltyInfo.append("<li>Item Call Number: " +itemCallNumber +
" had overdue for " +overdueDay +" days and panelty is: $" +penaltyAmount +"</li>");
if(debug) out.println("<br>Query(overdueDay): " +query);
}
// Update the borrowrec record
if(debug) out.println("<br>Update the borrow record");
query = "UPDATE borrowrec SET returnDate ='" +todayDateStr +
"', StaffIDReturn ='" +login.getUserID() +"', borrowStatus = 'return' " +
" WHERE borrowRecNumber =" +borrowRecNumber;
if(debug) out.println("<br>Query(borrowrec): " +query);
myResultSet = formDatabaseOperation(query);
// Update the libraryitem record
if(debug) out.println("<br>Update the libraryitem record");
query = "UPDATE libraryitem SET itemTotAvailable = itemTotAvailable + 1" +
", itemTotBorrow = itemTotBorrow -1" +
" WHERE itemCallNumber = '" +itemCallNumber +"'";
if(debug) out.println("<br>Query(libraryitem): " +query);
myResultSet = formDatabaseOperation(query);
// Update the user record
if(debug) out.println("<br>Update the user record");
query = "UPDATE user SET userQuotaAvailable = userQuotaAvailable +1" +
" WHERE userID ='" +borrowerID +"'";
if(debug) out.println("<br>Query(user): " +query);
myResultSet = formDatabaseOperation(query);
// Update the user bean if the reserver ID for the record that cancel same as login user.
if (reserverID.equals(login.getUserID())) {
user.setUserQuotaAvailable(user.getUserQuotaAvailable()+1);
session.setAttribute("user", user);
// if staff update their own record then set the staff bean
if( (login.getUserType().equals("admin")) || (login.getUserType().equals("librarian"))){
staff.setPropertyFromOtherUserBean(user);
session.setAttribute("staff", staff);
}
}
// Update the reserve record
if(debug) out.println("<br>Update the reserve record");
query = "SELECT * FROM reserverec WHERE itemCallNumber ='" +itemCallNumber +"'" +
" AND reserveStatus != 'available' ORDER BY reserveCancelDate ASC limit 1";
if(debug) out.println("<br>Query(reserverec - select): " +query);
try {
Connection connection = connectionPool.getConnection();
myResultSet = DatabaseUtilities.getQueryResultSet(connection, query, false);
connectionPool.free(connection);
} catch(Exception e) {
errorMsg = "Error: " + e;
}
reserverID = "";
if (myResultSet != null){
try{
while(myResultSet.next()) {
reserverID = myResultSet.getString("reserverID");
}
} catch(SQLException sqle) {
System.err.println("Error connecting: " +sqle);
}
}
// Update the status to available and the cancel date to one week after inform
if(!reserverID.equals("")){
query = "UPDATE reserveRec SET reserveStatus = 'available'" +
", reserveInformDate = '" +todayDateStr +"'" +
", reserveCancelDate = '" +reserveCancelDateAfterInformStr +"'" +
" WHERE reserverID = '" +reserverID +"'";
if(debug) out.println("<br>Query(reserverec - update): " +query);
myResultSet = formDatabaseOperation(query);
recUpdateInfo.append("<li>Item Call Number: " +itemCallNumber +
" had update as available in reserve queue for reserver ID: " +reserverID +"</li>");
totalReserveUpdate = totalReserveUpdate +1;
}
totalReturnNumber = totalReturnNumber + 1;
}
}
dbprocess.setDBName("jsp_library");
dbprocess.setTblName("borrowrec");
dbprocess.setSQLQuery("Query: " +query);
dbprocess.setProcessResult("<B>Return Record success added</B>");
// If delete return having update the reserver record and/or penalty invlove, display the messager
String ReserveUpdateMsg = "";
String PenaltyMsg = "";
if(totalReserveUpdate>0)
ReserveUpdateMsg = "<br><br>" + totalReserveUpdate + " record(s) in reserve queue had update" +
"<ul>" +recUpdateInfo +"</ul>";
if(totalPenaltyAmount>0)
PenaltyMsg = "<br><br>Total Penalty: $" +totalPenaltyAmount +" for the item that had overdue" +
"<ul>" +penaltyInfo + "</ul>";
dbprocess.setProcessMsg(totalReturnNumber +" Return records success added" +
ReserveUpdateMsg + PenaltyMsg);
dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> ");
dbprocess.setProcessComplete(true);
}else{
dbprocess.setDBName("jsp_library");
dbprocess.setTblName("borrowrec");
dbprocess.setSQLQuery("");
dbprocess.setProcessResult("<font color='RED'><B>Record Delete Error</B></font>");
dbprocess.setProcessMsg("<font color='RED'>No record(s) select</font>");
dbprocess.setProcessAction("<a href='" +form.getFormURL() +"'>" +
"<img src='/JavaLibrary/images/common/back_arrow.gif' width='38' height='21' border='0'>BACK</a>");
dbprocess.setProcessComplete(true);
}
break;
default:
break;
}
}else if( formName.equals("reserve")){
// Do something for reserve record form
switch(formOperationCode) {
case 'a':
if(debug) out.println("reserve+a");
StringBuffer recErrReason= new StringBuffer("");
int totalRecErr=0;
int numSameRecInReserveRec = 0;
int numSameRecInBorrowRec = 0;
int totalReserve = 0;
String itemCallNumber="";
// Get how many items borrow from parameter pass by http request
String[] paramValues = request.getParameterValues("itemCallNumber");
String reserverID = request.getParameter("reserverID");
if (paramValues.length > 0 ) {
for (int i=0; i<paramValues.length; i++) {
itemCallNumber = paramValues[i];
//Check either reserver had make the same item in current reservation or not
query = "SELECT count(*) FROM reserverec WHERE reserverID = '" +reserverID +
"' AND itemCallNumber = '" +itemCallNumber +"'";
myResultSet = formDatabaseOperation(query);
if (myResultSet != null){
try{
while(myResultSet.next()) {
numSameRecInReserveRec = Integer.parseInt(myResultSet.getString("count(*)"));
}
} catch(SQLException sqle) {
System.err.println("Error connecting: " +sqle);
}
}
//Check either reserver had same item in current borrow record or not
query = "SELECT count(*) FROM borrowrec WHERE borrowerID = '" +reserverID +
"' AND itemCallNumber = '" +itemCallNumber +"' AND borrowStatus != 'return'";
myResultSet = formDatabaseOperation(query);
if (myResultSet != null){
try{
while(myResultSet.next()) {
numSameRecInBorrowRec = Integer.parseInt(myResultSet.getString("count(*)"));
}
} catch(SQLException sqle) {
System.err.println("Error connecting: " +sqle);
}
}
// If that are same record exist in previous reserve list
if (numSameRecInReserveRec >= 1 ){
totalRecErr = totalRecErr + 1;
recErrReason.append("<li><font color='RED'>Item Call Number: " +itemCallNumber +
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -