formprocess.java~

this is project for java lib system

      							"'" +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 +