database.java

Java语言编写的员工信息管理系统

			CallableStatement proc = connector
					.prepareCall("{ call sp_insertIntoEnrolGuest(?,?,?,?,?  ,?,?,?,?,? , ?) }");
			proc.setString(1, enrolInfo.enrolGuest.name);
			proc.setInt(2, enrolInfo.enrolGuest.sex);
			proc.setString(3, enrolInfo.enrolGuest.certificateId);
			proc.setString(4, enrolInfo.enrolGuest.nationality);
			proc.setInt(5, enrolInfo.enrolGuest.entourageNum);

			proc.setString(6, enrolInfo.enrolGuest.phone);
			proc.setString(7, enrolInfo.enrolGuest.address);
			proc.setString(8, enrolInfo.enrolGuest.postalcode);
			proc.setString(9, enrolInfo.enrolGuest.ArrivalDate);
			proc.setInt(10, enrolInfo.enrolGuest.desposit);
			proc.setString(11, enrolInfo.enrolGuest.StaffId);
			
			proc.execute();
			ResultSet resultset = proc.getResultSet();
			int guestId = 0;
			//获得入住客人标号(自动产生)
			while (resultset.next()) {
				guestId = resultset.getInt("enrolGuestNo");
				System.out.println("入住客户号码：" + guestId);
			}

			//更新客人的入住的房间信息    
			Room room = new Room();
			String roomId = "";
			for (Enumeration ee = enrolInfo.roomList.elements(); ee
					.hasMoreElements();) {
				room = (Room) ee.nextElement();
				roomId = room.id;

				//store produce: sp_insertToReservationModifyRoomState
				CallableStatement proc2 = connector
						.prepareCall("{ call sp_insertToEnrol(?,?) }");
				proc2.setInt(1, guestId);
				proc2.setString(2, roomId);
				proc2.execute();			
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	//获得数据库中的服务项表  
	public Vector getServiceInfoList(){		
		Vector serviceInfoList=new Vector();		
		try{
			//store procedure : sp_getAllRoomList
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_getServiceList}");        	
        	proc.execute();        	
        	ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {				
				Service service = new Service();	
				service = getServiceFromResultSet(resultset);
            	serviceInfoList.addElement(service);		//加入房间列表
            }            
			System.out.println ("=========serviceInfoList.size():===="+serviceInfoList.size());
			
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
		return serviceInfoList;  		
  	}
	

	// 获得登记客人信息,从数据库视图中读取出来
	public Vector getEnrolInfoForWaiterList() {
		Vector enrolInfoList = new Vector();
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_getEnrolGuestForWaiter }");
			proc.execute();
			ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {
				Enrol enrolInfoForWaiter = new Enrol();
				enrolInfoForWaiter.enrolGuest = getEnrolGuestForWaiterFromResultSet(resultset);
				enrolInfoList.addElement(enrolInfoForWaiter);
			}
			resultset.close();
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
		}
		// ////////////////////////////////
		try {
			for (int i = 0; i < enrolInfoList.size(); i++) {
				Enrol enrolInfo = new Enrol();
				enrolInfo = (Enrol) enrolInfoList.elementAt(i);
				int rgId = enrolInfo.enrolGuest.id;
				CallableStatement proc = connector
						.prepareCall("{ call sp_getEnrolRoomListByGId(?) }");
				proc.setInt(1, rgId);
				proc.execute();
				ResultSet resultset = proc.getResultSet();
				Vector RoomList = new Vector();
				while (resultset.next()) {
					Room room = new Room();
					room = getRoomFromResultSet(resultset);
					RoomList.addElement(room);
				}
				enrolInfo.roomList = RoomList;
				enrolInfoList.setElementAt(enrolInfo, i);
				resultset.close();
			}
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
		}
		return enrolInfoList;
	}
	
	public void newTransaction(Transaction transaction){
		transaction.printTransactionInfo();
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_inserIntoTransaction(?,?,?,?,?) }");
       		proc.setString(1,transaction.date);
       		proc.setInt(2,transaction.guestId);
       		proc.setString(3,transaction.serviceId);
       		proc.setInt(4,transaction.serviceFee);
       		proc.setString(5,transaction.staffId);       		
        	proc.execute();    	
        	proc.close();
        	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }		
	}
	
	//	获取交易信息到数据库
	public Vector getTransactions(){
		Vector transactionInfoList=new Vector();		
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_getTransactionList }");        	
        	proc.execute();        	
        	ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {				
				Transaction transaction = new Transaction();	
				transaction = getTransactionFromResultSet(resultset);
				transactionInfoList.addElement(transaction);		//加入房间列表
            } 			
			return transactionInfoList;			
        }catch (Exception e) {
        	e.printStackTrace();
        	return null;           
        }
	}
	//删除交易信息,撤销
	public void deleteTransactions(Transaction transaction){	
		//fee为应该减少的费用
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_deleteTransactions(?,?,?) }");
       		proc.setInt(1, transaction.id);
       		proc.setInt(2,transaction.serviceFee);
       		proc.setInt(3,transaction.guestId);
       		
        	proc.execute();    
        	proc.close();        	
        }catch (Exception e) {
            e.printStackTrace();
        }
	}	
//	 获得登记客人信息,从数据库视图中读取出来
	public Vector getEnrolInfoList() {
		Vector enrolInfoList = new Vector();
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_getEnrolGuest }");
			proc.execute();
			ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {
				Enrol enrolInfoForWaiter = new Enrol();
				enrolInfoForWaiter.enrolGuest = getEnrolGuestFromResultSet(resultset);
				enrolInfoList.addElement(enrolInfoForWaiter);
			}
			resultset.close();
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
		}
		// ////////////////////////////////
		try {
			for (int i = 0; i < enrolInfoList.size(); i++) {
				Enrol enrolInfo = new Enrol();
				enrolInfo = (Enrol) enrolInfoList.elementAt(i);
				int rgId = enrolInfo.enrolGuest.id;
				CallableStatement proc = connector
						.prepareCall("{ call sp_getEnrolRoomListByGId(?) }");
				proc.setInt(1, rgId);
				proc.execute();
				ResultSet resultset = proc.getResultSet();
				Vector RoomList = new Vector();
				while (resultset.next()) {
					Room room = new Room();
					room = getRoomFromResultSet(resultset);
					RoomList.addElement(room);
				}
				enrolInfo.roomList = RoomList;
				enrolInfoList.setElementAt(enrolInfo, i);
				resultset.close();
			}
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
		}
		return enrolInfoList;
	}
//	 将退房的客人信息传给数据库，便于修改下列信息：
	// 客人入住信息，个人信息，所住房间信息，
	// 历史客人信息，包括操作人员信息
	public void saveReturnRoomInfo(Enrol enrol) {
		//删除入住客人的信息, 级联删除客人房间信息, 触发器更新房间状态,
        //又会删除该客人的交易信息
		//一次可以处理四张表的更新
		deleteEnrolInfo(enrol.enrolGuest.id,
				enrol.enrolGuest.ServiceExpenses+enrol.enrolGuest.RoomExpenses,
				enrol.enrolGuest.CheckOutDate);
	}
//	删除入住信息
	public void deleteEnrolInfo(int guestId,int income, String date){
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_deleteEnrolInfo(?,?,?) }");
       		proc.setInt(1, guestId);
       		//更新历史收入表
       		proc.setInt(2, income);
       		proc.setString(3, date);
        	proc.execute();    
        	proc.close();        	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }
  	}
}