formprocess.java~

this is project for java lib system

      							" already exist in user reserve list</font></li>");
      					// If that are same record exist in user existing borrow list
						} else if(numSameRecInBorrowRec >= 1) {
							totalRecErr = totalRecErr + 1;
							recErrReason.append("<li><font color='RED'>Item Call Number: " +itemCallNumber +
      							" already exist in user borrow record</font></li>");					
						} else {    					
     						// Insert into the reserverec table
      						reserverec.setPropertyFromRequestParameter(request, itemCallNumber);
      						query = "INSERT INTO reserverec (" +
      							"itemCallNumber, reserverID, reserverEmail, reserveDate, reserveCancelDate" +
      							", reserveInformDate, reserveStatus) VALUES (" +
      							"'" +reserverec.getItemCallNumber() +"', '" +reserverec.getReserverID() +
      							"', '" +reserverec.getReserverEmail() +"', '" +reserverec.getReserveDate() +
      							"', '" +reserverec.getReserveCancelDate() +"', '" +reserverec.getReserveInformDate() +
      							"', '" +reserverec.getReserveStatus() +"') ";
      						if (debug) out.println("<br>Query for reserverec: " +query);

      						myResultSet = formDatabaseOperation(query);
      						reserverec.resetVariable();
      						session.setAttribute("reserverec", reserverec); 
      					        					
      						totalReserve = totalReserve + 1;         					
      					
      						//Update the libraryitem database
      						query = "UPDATE libraryitem SET itemTotReserve = itemTotReserve + 1" +
      							" WHERE itemCallNumber ='" +itemCallNumber +"'";
      						myResultSet = formDatabaseOperation(query);
      						if (debug) out.println("<br>Query for libraryitem: " +query);
						}
					}			
					// Update the user database
					if (totalReserve > 0){
       					query = "UPDATE user SET userReserveAvailable = userReserveAvailable - " +totalReserve +
      						" WHERE  userID ='" +reserverID +"'";
      					myResultSet = formDatabaseOperation(query);	       		
       					if (debug) out.println("<br>Query for user database: " +query);   
       				}
       			
       				// Update the user bean
					user.setUserReserveAvailable(user.getUserReserveAvailable()-totalReserve);
					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);
      				}		
      			}
				
				dbprocess.setDBName("jsp_library");
      			dbprocess.setTblName("reserverec");
      			dbprocess.setSQLQuery("Query: " +query);
      			dbprocess.setProcessResult("<B>Reserve Record success added</B>");
      			// If delete operation having error, then display the error messager
      			if(totalRecErr>0){
      			dbprocess.setProcessMsg(totalReserve +" item(s) success added" +
      				"<br><br><font color='RED'>" +totalRecErr + "</font> record(s) having problem" +
      				"<ul>" +recErrReason +"</ul>");
      			}else{
      				dbprocess.setProcessMsg(totalReserve +" item(s) success added");      	
      			}
				dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> ");   	
      			dbprocess.setProcessComplete(true);   
      			break;
      		case 'd':
      			if(debug) out.println("reserve+d");
				if(request.getParameterValues("reserveRecNumber") != null){      			
      				String errorMsg;
      				int reserveRecNumber=0;
      				reserverID = "";
					itemCallNumber = "";
					recErrReason= new StringBuffer("");
      				totalRecErr=0;
      				int totalReserveCancel = 0;
      				int totalReserveUpdate = 0;
      				StringBuffer recUpdateInfo = new StringBuffer("");
      				paramValues = request.getParameterValues("reserveRecNumber");
      				String reserveStatus = "";
      				Date todayDate = new Date();
					String todayDateStr = ServletUtilities.dateToString(todayDate);
					Date reserveCancelDateAfterInform = ServletUtilities.diffrenceDay(todayDate, 7);
					String reserveCancelDateAfterInformStr = ServletUtilities.dateToString(reserveCancelDateAfterInform);
       				if (paramValues.length > 0 ) {
      					for (int i=0; i<paramValues.length; i++) {
      						// Get the borrowerID and itemCallNumber from the parameter
      						reserveRecNumber = Integer.parseInt(paramValues[i]);      					
      						if (debug) out.println("<br><br>reserveRecNumber: " +reserveRecNumber);      					
    						try {
      							query = "SELECT * FROM reserverec where reserveRecNumber = " +reserveRecNumber;      
      							Connection connection = connectionPool.getConnection();
								myResultSet = DatabaseUtilities.getQueryResultSet(connection, query, false);                                            
      							connectionPool.free(connection);
				    		} catch(Exception e) {
      							errorMsg = "Error: " + e;    		
    						}    
    					
    						if (myResultSet != null){
								try{
									while(myResultSet.next()) {
										reserverID = myResultSet.getString("reserverID");
										itemCallNumber = myResultSet.getString("itemCallNumber");
										reserveStatus = myResultSet.getString("reserveStatus");
									}
								} catch(SQLException sqle) {
									System.err.println("Error connecting: " +sqle);		
								}
							}	
							
							if (debug) out.println("<BR>reserverID: " +reserverID);
							if (debug) out.println("<br>itemCallNumber: " +itemCallNumber);
							if (debug) out.println("<br>reserveStatus: " +reserveStatus);										
							
							// Update the libraryitem record
							if(debug) out.println("<br>Update the libraryitem record");
							query = "UPDATE libraryitem SET itemTotReserve= itemTotReserve - 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 userReserveAvailable = userReserveAvailable +1" +
								" WHERE userID ='" +reserverID +"'";
							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.setUserReserveAvailable(user.getUserReserveAvailable()+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 = "DELETE FROM reserverec WHERE reserveRecNumber=" +reserveRecNumber;	
							if(debug) out.println("<br>Query(reserverec): " +query);	
							myResultSet = formDatabaseOperation(query);
							reserverID = "";
							
							// If the reservestatus is available then transfer the available to next first person in queue
							if(reserveStatus.equals("available")) {
								// Select the first person in next queue for the same item
								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;    		
    							}    
    					
    							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>Availability of Item Call Number: " +itemCallNumber +
      									" had update in queue to reserver ID: " +reserverID +"</li>");							
									totalReserveUpdate = totalReserveUpdate +1;
								}
							}
							totalReserveCancel = totalReserveCancel +1;							
    					}
    				}   			
					dbprocess.setDBName("jsp_library");
      				dbprocess.setTblName("reserverec");
      				dbprocess.setSQLQuery("Query: " +query);
      				dbprocess.setProcessResult("<B>Reserve Record success cancel</B>");
      				// If cancel reservation have involve next queue, then display the queue update messager
      				if(totalReserveUpdate>0){
      				dbprocess.setProcessMsg(totalReserveCancel +" reserve records success cancel" +
      					"<br><br>" +totalReserveUpdate + " record(s) in queue had update" +
      					"<ul>" +recUpdateInfo +"</ul>");
      				}else{
      					dbprocess.setProcessMsg(totalReserveCancel +" reserve records success cancel");      	
      				}
					dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> ");   	
      				dbprocess.setProcessComplete(true);        			      			

				}else{
				dbprocess.setDBName("jsp_library");
      			dbprocess.setTblName("lreserverec");
      			dbprocess.setSQLQuery("");
      			dbprocess.setProcessResult("<font color='RED'><B>Reservation cancel 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 {
			// ??  Should be some form that I miss.. hahaha.
			// Should report it...  :)
		}
		
		if(debug){
			out.println("<BR>Query: <b>" +query +"</b>");
	        out.println("</CENTER>\n</BODY></HTML>");
  		}
  		else{
      		session.setAttribute("dbprocess", dbprocess); 
      		gotoPage("/common/dbprocess_result.jsp", request, response);  
  		}  			 
  	}  	

	private ResultSet formDatabaseOperation(String query){  	
  	   	String errorMsg;
  	   	ResultSet myResultSet=null;
    	try {
      		Connection connection = connectionPool.getConnection();
			myResultSet = DatabaseUtilities.getQueryResultSet(connection, query, false);                                            
      		connectionPool.free(connection);
    	} catch(Exception e) {
      		errorMsg = "Error: " + e; 
      		connectionPool = null;   		
    	}    	   	
    	return(myResultSet); 
  	}
  	
  	private boolean checkDuplicateUserID(String userID){
  		int totUserRec = 0; 
  	   	String errorMsg;
  	   	ResultSet myResultSet=null;
    	try {
    		String query = "SELECT count(*) totUserRec FROM user WHERE userID='" +userID +"'"; 
      		Connection connection = connectionPool.getConnection();
			myResultSet = DatabaseUtilities.getQueryResultSet(connection, query, false);                                            
      		connectionPool.free(connection);
    	} catch(Exception e) {
      		errorMsg = "Error: " + e;    	
      		connectionPool = null;	
    	}
    	                          	
    	if (myResultSet != null){
			try{
				while(myResultSet.next()) {
					totUserRec = Integer.parseInt(myResultSet.getString("totUserRec"));						
				}
			} catch(SQLException sqle) {
				System.err.println("Error connecting: " +sqle);		
			}
		} 
    		
    	if (totUserRec > 0){
    		return(true);
    	}else{
    		return(false);
    	}  	
    }
}