guestconsumeddao.java

一个KTV管理系统

package dao.consumeddao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
import javax.swing.JOptionPane;
import vo.GuestConsumedInfo;
import vo.WaiterInfo;
import common.dbconnection.DbConnection;

//import common.DbConnection;
public class GuestConsumedDao {

	private Connection con = null;

	private Statement sta = null;

	private ResultSet res = null;

	private Vector v = null;

	// 通过消费流水号取得相应的的宾客开房时间
	public String getConsumedTime(String value) {
		String time = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select * from KTV_GUEST_CONSUMED_INFO where CONSUMED_ID = "
				+ value.trim() + "";
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			while (set.next()) {
				time = set.getString("GUEST_CHECK_IN_TIME").substring(0, 19);
			}
			System.out.println("取得宾客开房时间成功");
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return time;
	}

	// 通过消费流水号取得相应的宾客消费信息记录
	public GuestConsumedInfo getAGuestConsumedInfo(String value) {
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		GuestConsumedInfo gci = null;
		String sql = "select * from KTV_GUEST_CONSUMED_INFO where CONSUMED_ID = "
				+ value.trim() + "";
		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);
				System.out.println("取得宾客消费信息记录成功");
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return gci;
	}

	// 更新一条宾客消费信息记录
	public boolean updateGuestConsumedInfo(GuestConsumedInfo demo) {
		boolean flag = false;
		String sql = "update KTV_GUEST_CONSUMED_INFO set GUEST_NAME = '"
				+ demo.getGuestName() + "'," + "GUEST_ID = "
				+ demo.getGuestId() + ",GUEST_CHECK_OUT_TIME = to_date('"
				+ demo.getGuestCheckOutTime().substring(0, 19)
				+ "','YYYY-MM-DD HH24:MI:SS')," + "CONSUMED_TIME = '"
				+ demo.getConsumedTime() + "',GOODS_CONSUMED_MONEY = "
				+ String.valueOf(demo.getGoodsConsumedMoney()) + ","
				+ "ROOM_MONEY = " + String.valueOf(demo.getRoomMoney())
				+ ",CONSUMED_TYPE_NAME = '" + demo.getConsumedTypeName() + "',"
				+ "DEPOSIT = " + String.valueOf(demo.getDeposit()) + ",FEE = "
				+ String.valueOf(demo.getFee()) + ",REBATE = "
				+ String.valueOf(demo.getRebate()) + " where CONSUMED_ID = "
				+ demo.getConsumedId() + "";
		System.out.println(sql);
		try {
			// con = new JdbcDemo().getConnection();
			con = new DbConnection().getConnection();
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			flag = true;
			System.out.println("更新一条宾客消费信息记录成功");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e1) {
				System.out.println(e1.getMessage());
			}
		}
		return flag;
	}

	// 新建一条宾客消费信息记录
	public boolean insertGuestConsumedInfo(GuestConsumedInfo demo) {
		boolean flag = false;
		String sql = "insert into KTV_GUEST_CONSUMED_INFO(CONSUMED_ID,GUEST_NAME,ROOM_NUMBER,GUEST_ID,GUEST_CHECK_IN_TIME,GUEST_CHECK_OUT_TIME,CONSUMED_TIME,GOODS_CONSUMED_MONEY,ROOM_MONEY,CONSUMED_TYPE_NAME,DEPOSIT,FEE,REBATE)"
				+ " values("
				+ demo.getConsumedId()
				+ ",'"
				+ demo.getGuestName()
				+ "','"
				+ demo.getRoomNumber()
				+ "',"
				+ demo.getGuestId()
				+ ",to_date('"
				+ demo.getGuestCheckInTime()
				+ "','YYYY-MM-DD HH24:MI:SS')"
				+ ",to_date('"
				+ demo.getGuestCheckOutTime()
				+ "','YYYY-MM-DD HH24:MI:SS'),'"
				+ demo.getConsumedTime()
				+ "',"
				+ String.valueOf(demo.getGoodsConsumedMoney())
				+ ","
				+ String.valueOf(demo.getRoomMoney())
				+ ",'"
				+ demo.getConsumedTypeName()
				+ "',"
				+ String.valueOf(demo.getDeposit())
				+ ","
				+ String.valueOf(demo.getFee())
				+ ","
				+ String.valueOf(demo.getRebate()) + ")";
		System.out.println(sql);
		try {
			// con = new JdbcDemo().getConnection();
			con = new DbConnection().getConnection();
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			flag = true;
			System.out.println("新建一条宾客消费信息记录成功");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e1) {
				System.out.println(e1.getMessage());
			}
		}
		return flag;
	}

	public boolean updateGuestGoodsConsumedInfo(String demo, double money) {
		boolean flag = false;
		String sql = "update KTV_GUEST_CONSUMED_INFO set GOODS_CONSUMED_MONEY = GOODS_CONSUMED_MONEY + "
				+ money + " where CONSUMED_ID = " + demo + "";
		System.out.println(sql);
		try {
			// con = new JdbcDemo().getConnection();
			con = new DbConnection().getConnection();
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			flag = true;
			System.out.println("更新一条宾客消费信息记录成功");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e1) {
				System.out.println(e1.getMessage());
			}
		}
		return flag;
	}

	public boolean deleteGuestGoodsConsumedInfo(String demo, double money) {
		boolean flag = false;
		String sql = "update KTV_GUEST_CONSUMED_INFO set GOODS_CONSUMED_MONEY = GOODS_CONSUMED_MONEY - "
				+ money + " where CONSUMED_ID = " + demo + "";
		System.out.println(sql);
		try {
			// con = new JdbcDemo().getConnection();
			con = new DbConnection().getConnection();
			sta = con.createStatement();
			res = sta.executeQuery(sql);
			flag = true;
			System.out.println("更新一条宾客消费信息记录成功");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				con.close();
			} catch (Exception e1) {
				System.out.println(e1.getMessage());
			}
		}
		return flag;
	}

	// 取得相应的宾客消费信息记录
	public Vector getGuestConsumedInfo(String value, String value2,
			String start, String end) {
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		GuestConsumedInfo gci = null;
		String sql = null;
		Vector v = new Vector();
		if (value.trim().equals("0")) {
			if (value2.trim().equals("0")) {
			} else {
				sql = "select * from KTV_GUEST_CONSUMED_INFO where GUEST_NAME = '"
						+ value2.trim() + "'";
			}
		} else {
			if (value2.trim().equals("0")) {
				sql = "select * from KTV_GUEST_CONSUMED_INFO where ROOM_NUMBER = '"
						+ value.trim() + "'";