guestconsumeddao.java

一个KTV管理系统

			} else {
				sql = "select * from KTV_GUEST_CONSUMED_INFO where ROOM_NUMBER = '"
						+ value.trim()
						+ "' and GUEST_NAME = '"
						+ value2.trim()
						+ "'";
			}
		}
		if (start.equals("0")) {
		} else {
			sql = sql + "and GUEST_CHECK_OUT_TIME > to_date('" + start
					+ "','YYYY-MM-DD HH24:MI:SS')";
		}
		if (end.equals("0")) {
		} else {
			sql = sql + "and GUEST_CHECK_OUT_TIME < to_date('" + end
					+ "','YYYY-MM-DD HH24:MI:SS')";
		}
		if (value.trim().equals("0") && value2.trim().equals("0")) {
			if (start.equals("0") && end.equals("0")) {
				JOptionPane.showMessageDialog(null, "请输入正确的查询条件", "提示信息",
						JOptionPane.YES_OPTION);
			} else {
				if (start.equals("0")) {
					sql = "select * from KTV_GUEST_CONSUMED_INFO where GUEST_CHECK_OUT_TIME < "
							+ "to_date('" + end + "','YYYY-MM-DD HH24:MI:SS')";
				} else {
					if (end.equals("0")) {
						sql = "select * from KTV_GUEST_CONSUMED_INFO where GUEST_CHECK_OUT_TIME > "
								+ "to_date('"
								+ start
								+ "','YYYY-MM-DD HH24:MI:SS')";
					} else {
						sql = "select * from KTV_GUEST_CONSUMED_INFO where GUEST_CHECK_OUT_TIME > "
								+ "to_date('"
								+ start
								+ "','YYYY-MM-DD HH24:MI:SS')"
								+ "and GUEST_CHECK_OUT_TIME < to_date('"
								+ end
								+ "','YYYY-MM-DD HH24:MI:SS')";
					}
				}
			}
		}
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			System.out.println(sql);
			while (set.next()) {
				long consumedId = set.getLong("CONSUMED_ID");
				String guestName = set.getString("GUEST_NAME");
				String roomNumber = set.getString("ROOM_NUMBER");
				long guestId = set.getLong("GUEST_ID");
				String gcit = set.getString("GUEST_CHECK_IN_TIME").substring(0,
						19);
				String gcot = set.getString("GUEST_CHECK_OUT_TIME").substring(
						0, 19);
				String consumedTime = set.getString("CONSUMED_TIME");
				double goodsConsumedMoney = set
						.getDouble("GOODS_CONSUMED_MONEY");
				int roomMoney = set.getInt("ROOM_MONEY");
				String consumedTypedName = set.getString("CONSUMED_TYPE_NAME");
				int deposit = set.getInt("DEPOSIT");
				double fee = set.getDouble("FEE");
				double rebate = set.getDouble("REBATE");
				gci = new GuestConsumedInfo(consumedId, guestName, roomNumber,
						guestId, gcit, gcot, consumedTime, goodsConsumedMoney,
						roomMoney, consumedTypedName, deposit, rebate, fee);
				v.addElement(gci);
				// System.out.println("取得宾客消费信息记录成功");
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	// 取得所有相应的宾客消费信息记录
	public Vector getWaiterConsumedInfo(String value) {
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		WaiterInfo gci = null;
		String sql = null;
		Vector v = new Vector();
		if (value.trim().equals("0")) {
		} else {
			sql = "select * from KTV_waiter where waiter_id = '" + value.trim()
					+ "'";
		}
		// } else {
		// if (value2.trim().equals("0")) {
		// sql = "select * from KTV_GUEST_CONSUMED_INFO where ROOM_NUMBER = '"
		// + value.trim() + "'";
		// } else {
		// sql = "select * from KTV_GUEST_CONSUMED_INFO where ROOM_NUMBER = '"
		// + value.trim()
		// + "' and GUEST_NAME = '"
		// + value2.trim()
		// + "'";
		// }
		// }
		// if (start.equals("0")) {
		// } else {
		// sql = sql + "and GUEST_CHECK_OUT_TIME > to_date('" + start
		// + "','YYYY-MM-DD HH24:MI:SS')";
		// }
		// if (end.equals("0")) {
		// } else {
		// sql = sql + "and GUEST_CHECK_OUT_TIME < to_date('" + end
		// + "','YYYY-MM-DD HH24:MI:SS')";
		// }
		// if (value.trim().equals("0") && value2.trim().equals("0")) {
		// if (start.equals("0") && end.equals("0")) {
		// JOptionPane.showMessageDialog(null, "请输入正确的查询条件", "提示信息",
		// JOptionPane.YES_OPTION);
		// } else {
		// if (start.equals("0")) {
		// sql = "select * from KTV_GUEST_CONSUMED_INFO where
		// GUEST_CHECK_OUT_TIME < "
		// + "to_date('" + end + "','YYYY-MM-DD HH24:MI:SS')";
		// } else {
		// if (end.equals("0")) {
		// sql = "select * from KTV_GUEST_CONSUMED_INFO where
		// GUEST_CHECK_OUT_TIME > "
		// + "to_date('"
		// + start
		// + "','YYYY-MM-DD HH24:MI:SS')";
		// } else {
		// sql = "select * from KTV_GUEST_CONSUMED_INFO where
		// GUEST_CHECK_OUT_TIME > "
		// + "to_date('"
		// + start
		// + "','YYYY-MM-DD HH24:MI:SS')"
		// + "and GUEST_CHECK_OUT_TIME < to_date('"
		// + end
		// + "','YYYY-MM-DD HH24:MI:SS')";
		// }
		// }

		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			while (set.next()) {
				int waiter_id = set.getInt("waiter_id");
				String waiter_name = set.getString("waiter_name");
				String waiter_sex = set.getString("waiter_sex");
				String waiter_tel = set.getString("waiter_tel");
				String waiter_area = set.getString("waiter_area");
				String leveling = set.getString("leveling");

				gci = new WaiterInfo(waiter_id, waiter_name, waiter_sex,
						waiter_tel, waiter_area, leveling);
				v.addElement(gci);
				System.out.println("zhuobin");
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	public Vector getsWaiterConsumedInfo() {
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		WaiterInfo gci = null;
		Vector v = new Vector();
		String sql = "select * from KTV_waiter";

		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			while (set.next()) {
				int waiter_id = set.getInt("waiter_id");
				String waiter_name = set.getString("waiter_name");
				String waiter_sex = set.getString("waiter_sex");
				String waiter_tel = set.getString("waiter_tel");
				String waiter_area = set.getString("waiter_area");
				String leveling = set.getString("leveling");

				gci = new WaiterInfo(waiter_id, waiter_name, waiter_sex,
						waiter_tel, waiter_area, leveling);
				v.addElement(gci);
				System.out.println("zhuobin");
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}

	public Vector getsGuestConsumedInfo() {
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		GuestConsumedInfo gci = null;
		Vector v = new Vector();
		String sql = "select * from KTV_GUEST_CONSUMED_INFO ";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			while (set.next()) {
				long consumedId = set.getLong("CONSUMED_ID");
				String guestName = set.getString("GUEST_NAME");
				String roomNumber = set.getString("ROOM_NUMBER");
				long guestId = set.getLong("GUEST_ID");
				String gcit = set.getString("GUEST_CHECK_IN_TIME").substring(0,
						19);
				String gcot = set.getString("GUEST_CHECK_OUT_TIME").substring(
						0, 19);
				String consumedTime = set.getString("CONSUMED_TIME");
				double goodsConsumedMoney = set
						.getDouble("GOODS_CONSUMED_MONEY");
				int roomMoney = set.getInt("ROOM_MONEY");
				String consumedTypedName = set.getString("CONSUMED_TYPE_NAME");
				int deposit = set.getInt("DEPOSIT");
				double fee = set.getDouble("FEE");
				double rebate = set.getDouble("REBATE");
				gci = new GuestConsumedInfo(consumedId, guestName, roomNumber,
						guestId, gcit, gcot, consumedTime, goodsConsumedMoney,
						roomMoney, consumedTypedName, deposit, rebate, fee);
				v.addElement(gci);
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}
}