formprocess.java~

this is project for java lib system

												userID = myResultSet.getString("userID");
      											userTotQuota = Integer.parseInt(myResultSet.getString("userTotQuota"));
												userQuotaAvailable = Integer.parseInt(myResultSet.getString("userQuotaAvailable"));	
												userTotReservation = Integer.parseInt(myResultSet.getString("userTotReservation"));
												userReserveAvailable= Integer.parseInt(myResultSet.getString("userReserveAvailable"));
											}
      									} catch(SQLException sqle) {
											System.err.println("Error connecting: " +sqle);		
										}
									}
									itemBorrow = userTotQuota - userQuotaAvailable;
									itemReserve = userTotReservation - userReserveAvailable;
									
							
      								if (itemBorrow>0) {
      									totalRecDelErr = totalRecDelErr + 1;
      									RecDelErrReason.append("<li><font color='RED'>User ID: <i>" +userID +
      										"</i> still have <i>" +itemBorrow +"</i> item(s) no yet return</font></li>");
									} else if (itemReserve > 0) {									
      									totalRecDelErr = totalRecDelErr + 1;
      									RecDelErrReason.append("<li><font color='RED'>User ID: <i>" +userID +
      										"</i> still have <i>" +itemReserve +"</i> item(s) in reserve list</font></li>");										
      								}else{
      								totalRecDel = totalRecDel + 1;  	
      								query = "DELETE FROM user WHERE userRecNumber=" +delRecNumber;		
      								myResultSet = formDatabaseOperation(query);
      								}
      							}
      						}
      						dbprocess.setDBName("jsp_library");
      						dbprocess.setTblName("user");
      						dbprocess.setSQLQuery(query);
      						dbprocess.setProcessResult("<B>Delete Record(s)</B>");
      						// If delete operation having error, then display the error messager
      						if(totalRecDelErr>0){
      							dbprocess.setProcessMsg(totalRecDel +" record(s) success delete" +
      								"<br><br><font color='RED'>" +totalRecDelErr + "</font> record(s) having problem" +
      								"<ul>" +RecDelErrReason +"</ul>");
      						}else{
      							dbprocess.setProcessMsg(totalRecDel +" record(s) success delete");      				
      						}
      						dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> " +
      							"<a href='/JavaLibrary/admin/user_list.jsp'>[Back to User List]</a>");
							dbprocess.setProcessComplete(true); 
      					}else{
      					    dbprocess.setDBName("jsp_library");
      						dbprocess.setTblName("user");
      						dbprocess.setSQLQuery(query);
      						dbprocess.setProcessResult("<font color='RED'><B>Record Delete Error</B></font>");
      						dbprocess.setProcessMsg("<font color='RED'>Insufficiency Security Level</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);
      					}
      				}            				     				
				}else{
					dbprocess.setDBName("jsp_library");
      				dbprocess.setTblName("user");
      				dbprocess.setSQLQuery(query);
      				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("libraryitem")){
			// Do something for library item form	
			switch(formOperationCode) {
      		case 'a':
     			if(debug) out.println("item+a");
     			libraryitem.setPropertyFromRequestParameter(request);
      			query = "INSERT INTO libraryitem (" +
      				"itemCallNumber, itemCategory, itemSubject, itemMediaType, itemLanguage, itemTitle, itemBriefDesc, " + 
      				"itemAuthor, itemKeyword, itemPublisherName, itemISBN, itemLength, itemYear, " +
      				"itemTotQuantity, itemTotReserve, itemTotAvailable, itemTotBorrow) VALUES (" +
      				"'" +libraryitem.getItemCallNumber() +"', '" +libraryitem.getItemCategory() +
      				"', '" +libraryitem.getItemSubject() +"', '" +libraryitem.getItemMediaType() +
      				"', '" +libraryitem.getItemLanguage() +"', '" +libraryitem.getItemTitle() +
      				"', '" +libraryitem.getItemBriefDesc() +"', '" +libraryitem.getItemAuthor() +
      				"', '" +libraryitem.getItemKeyword() +"', '" +libraryitem.getItemPublisherName() +
      				"', '" +libraryitem.getItemISBN() +"', '" +libraryitem.getItemLength() +
      				"', '" +libraryitem.getItemYear() +"', " +libraryitem.getItemTotQuantity() +
      				", " +libraryitem.getItemTotReserve() +", " +libraryitem.getItemTotAvailable() +
      				", " +libraryitem.getItemTotBorrow() +")";
	   			myResultSet = formDatabaseOperation(query);	 
				libraryitem.resetVariable();
				session.setAttribute("libraryitem", libraryitem);				
				dbprocess.setDBName("jsp_library");
      			dbprocess.setTblName("libraryitem");
      			dbprocess.setSQLQuery(query);
      			dbprocess.setProcessResult("<B>Item Record success added</B>");
      			dbprocess.setProcessMsg("");
				dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> " +
      						"<a href='/JavaLibrary/libraryitem/libraryitem_add.jsp'>[Add another item record]</a>");    	
      			dbprocess.setProcessComplete(true);
      			break;
      		case 'e':
      			if(debug) out.println("item+e");   
      			libraryitem.setPropertyFromRequestParameter(request);      	
      			query = "UPDATE libraryitem SET itemCategory='" +libraryitem.getItemCategory() +
      				"', itemSubject='" +libraryitem.getItemSubject() + 
      				"', itemMediaType='" +libraryitem.getItemMediaType() +"', itemLanguage='" +libraryitem.getItemLanguage() +
      				"', itemTitle='" +libraryitem.getItemTitle() +"', itemBriefDesc='" +libraryitem.getItemBriefDesc() +
      				"', itemAuthor='" +libraryitem.getItemAuthor() +"', itemKeyword='" +libraryitem.getItemKeyword() +
      				"', itemPublisherName='" +libraryitem.getItemPublisherName() +"', itemISBN='" +libraryitem.getItemISBN() +
      				"', itemLength='" +libraryitem.getItemLength() +"', itemYear='" +libraryitem.getItemYear() +
      				"', itemTotQuantity=" +libraryitem.getItemTotQuantity() +", itemTotReserve=" +libraryitem.getItemTotReserve() +
      				", itemTotAvailable=" +libraryitem.getItemTotAvailable() +", itemTotBorrow=" +libraryitem.getItemTotBorrow() +  				
      				" WHERE itemRecNumber = '" +libraryitem.getItemRecNumber() +"'";
      				myResultSet = formDatabaseOperation(query);	
      				
					// Display the procesing messager
      				dbprocess.setDBName("jsp_library");
      				dbprocess.setTblName("libraryitem");
      				dbprocess.setSQLQuery(query);
      				dbprocess.setProcessResult("<B>Record Update Success</B>");
      				dbprocess.setProcessMsg("");
   					dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> " +
      					"<a href='/JavaLibrary/admin/libraryitem_list.jsp'>[Back to Item List]</a>");
					dbprocess.setProcessComplete(true); 						
      				//session.setAttribute("libraryitem", libraryitem);	  		
      			break;
      		case 'd':
      			if(debug) out.println("item+d");
      			int totalRecDel = 0;
      			int totalRecDelErr=0;
      			int delRecNumber;
      			String itemCallNumber = "";
      			String userType = login.getUserType();
      			StringBuffer RecDelErrReason= new StringBuffer("");
				int itemTotQuantity = 0;
				int itemTotAvailable = 0; 
				int itemTotBorrow = 0; 
				int itemTotReserve = 0;
				if(request.getParameterValues("del_rec") != null){
      				String[] paramValues = request.getParameterValues("del_rec");
   					// Only admin right can perform deleteion
   					if (userType.equals("admin")) {
      					for (int i=0; i<paramValues.length; i++){ 	
      						delRecNumber = Integer.parseInt(paramValues[i]);      											 		
      						query = "SELECT * FROM libraryitem WHERE itemRecNumber =" +delRecNumber;
      						myResultSet = formDatabaseOperation(query);      	
      						if (myResultSet != null){
								try{	
									while(myResultSet.next()) {	
										itemCallNumber = myResultSet.getString("itemCallNumber");
										itemTotQuantity = Integer.parseInt(myResultSet.getString("itemTotQuantity"));
										itemTotReserve = Integer.parseInt(myResultSet.getString("itemTotReserve"));
										itemTotAvailable = Integer.parseInt(myResultSet.getString("itemTotAvailable"));
										itemTotBorrow = Integer.parseInt(myResultSet.getString("itemTotBorrow"));
									}
      							} catch(SQLException sqle) {
									System.err.println("Error connecting: " +sqle);		
								}
							}
      							
      						if (itemTotBorrow > 0) {
      							totalRecDelErr=totalRecDelErr+1;
   								RecDelErrReason.append("<li><font color='RED'>Item Call Number: " +itemCallNumber +
      								" still have " +itemTotBorrow +" in quantity not yet return</font></li>");
      						} else if (itemTotReserve > 0) {
      							totalRecDelErr = totalRecDelErr + 1;
      							RecDelErrReason.append("<li><font color='RED'>Item Call Number: " +itemCallNumber +
      								" still have " +itemTotReserve +" in quantity under reserve list</font></li>");
      						}else {
      							totalRecDel = totalRecDel + 1;  	
      							query = "DELETE FROM libraryitem WHERE itemRecNumber=" +delRecNumber;		
      							myResultSet = formDatabaseOperation(query);
      						}
      					}      				
      					dbprocess.setDBName("jsp_library");
      					dbprocess.setTblName("libraryitem");
      					dbprocess.setSQLQuery(query);
      					dbprocess.setProcessResult("<B>Delete Record(s)</B>");
      					// If delete operation having error, then display the error messager
      					if(totalRecDelErr>0){
      						dbprocess.setProcessMsg(totalRecDel +" record(s) success delete" +
      							"<br><br><font color='RED'>" +totalRecDelErr + "</font> record(s) having problem" +
      							"<ul>" +RecDelErrReason +"</ul>");
      					}else{
      						dbprocess.setProcessMsg(totalRecDel +" record(s) success delete");      	
      					}			
      					dbprocess.setProcessAction("<a href='/JavaLibrary/mainmenu/mainmenu.jsp'>[Main Menu]</a> " +
      						"<a href='/JavaLibrary/admin/libraryitem_list.jsp'>[Back to Item List]</a>");
						dbprocess.setProcessComplete(true); 						
      				}else{
      				    dbprocess.setDBName("jsp_library");
      					dbprocess.setTblName("libraryitem");
      					dbprocess.setSQLQuery(query);
      					dbprocess.setProcessResult("<font color='RED'><B>Record Delete Error</B></font>");
      					dbprocess.setProcessMsg("<font color='RED'>Insufficiency Security Level</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);
      				}       				     				
				}else{
					dbprocess.setDBName("jsp_library");
      				dbprocess.setTblName("libraryitem");
      				dbprocess.setSQLQuery(query);
      				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("borrowrec")){
			// Do something for borrow record form
			switch(formOperationCode) {
      		case 'b':
      		    StringBuffer recErrReason= new StringBuffer("");
      			int totalRecErr=0;
      			int numSameRecInBorrowRec = 0;
      			int numSameRecInReserveRec = 0;
      			int totalBorrow = 0;
      			String itemCallNumber="";
				// Get how many items borrow from parameter pass by http request
      			String[] paramValues = request.getParameterValues("itemCallNumber");
      			String borrowID = request.getParameter("borrowerID");
      			
      			if (paramValues.length > 0 ) {
      				for (int i=0; i<paramValues.length; i++) {
      					itemCallNumber = paramValues[i];  
      					
      					//Check eitehr user had make the reservation for the same item or not
      					query = "SELECT count(*) FROM reserverec WHERE reserverID = '" +borrowID +
      						"' 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 borrower already borrow out the same item in pervious or not and not yet return
      					query = "SELECT count(*) FROM borrowrec WHERE borrowerID = '" +borrowID +
      						"' 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 +
      							" 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 borrowrec table
      						borrowrec.setPropertyFromRequestParameter(request, itemCallNumber);
      						query = "INSERT INTO borrowrec (" +
      							"itemCallNumber, staffIDBorrow, staffIDReturn, borrowerID, borrowerEmail, " +
      							" borrowDate, dueDate, returnDate, borrowStatus) VALUES (" +