dailyrecord.java

简单的餐厅管理系统

package beans;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.Vector;

import util.BaseSQL;

public class DailyRecord {
	private int year = 0;
	private int month = 0;
	private int day = 0;
	private Hashtable h = new Hashtable();
	private final static int checkedBill = 2;
	
	private float money = 0;
	private int numberOfMan = 0;
	private Vector<Bill> bills = new Vector<Bill>();
	private Vector<Material> materials = new Vector<Material>();

	String beginDate = "";
	String endDate = "";

	public String getDailyMoney() {//按金额统计
		String selectSQL = " order by price desc";
		return getDailyBill(selectSQL);
	}

	public String getDailyPeople() {//按人数统计
		String selectSQL = " order by numberOfMan desc";
		return getDailyBill(selectSQL);
	}

	private String getDailyBill(String selectSQL) {
		String returnString = getDailyInformation(selectSQL);
		if (returnString == null) {
			h.put("totalMoney", money);// 总金额
			h.put("totalNumberOfMan", numberOfMan);// 总人数
			h.put("bills", bills);// 详细订单
		}
		return returnString;
	}

	private void getDate() {//计算时间
		beginDate = year + "-" + month + "-" + day;
		endDate = year + "-" + month + "-" + (day + 1);
	}

	/**
	 * @param selectSQL
	 * @return
	 */
	private String getDailyInformation(String selectSQL) {//获得总金额和总人数及总账单

		getDate();

		BaseSQL b = new BaseSQL();
		try {
			b.connect("eatery", "root", "123456");
			String sql = "select price,beginTime,endTime,numberOfMan,tableNum,billID,waiterID "
				+ " from bill where type = " + checkedBill +" and endTime >= '"
				+ beginDate
				+ "' and endTime < '" + endDate + "'" + selectSQL;
			ResultSet rs = b.statement
					.executeQuery(sql);
			while (rs.next()) {
				Bill addBill = new Bill();
				addBill.setPrice(rs.getFloat(1));
				addBill.setBeginTime(rs.getTimestamp(2));
				addBill.setEndTime(rs.getTimestamp(3));
				addBill.setNumberOfMan(rs.getInt(4));
				addBill.setTableNum(rs.getInt(5));
				addBill.setBillID(rs.getString(6));
				User user = new User();
				user.getWaiterData(rs.getString(7));
				addBill.setWaiter(user);
				bills.add(addBill);
				System.out.println(rs.getFloat(1));
				money += rs.getFloat(1);
				numberOfMan += rs.getInt(4);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "数据库操作出错！";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "出现未知错误！";
		} finally {
			try {
				b.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}

	public String getDailyMaterialUsed() {//获得主料使用情况
		getDate();

		BaseSQL b = new BaseSQL();
		try {
			b.connect("eatery", "root", "123456");

			ResultSet rs = b.statement
					.executeQuery("select materialName, sum(totalNumber) "
							+ "from(select dishName,materialName,weight*dishNumber totalNumber "
							+ "from dish,dish_material,material, "
							+ "(select dishID ,sum(amount) dishNumber "
							+ "from bill_dish "
							+ "where billID in "
							+ "(select billID from bill where type = "+checkedBill+" and endTime >= '"
							+ beginDate
							+ "' and endTime < '"
							+ endDate
							+ "') "
							+ "group by dishID) as dailyDish "
							+ "where dish_material.dishID = dailyDish.dishID "
							+ "and dish_material.materialID = material.materialID "
							+ "and dish.dishID = dish_material.dishID "
							+ ") as final " + "group by materialName ");
			while (rs.next()) {
				Material material = new Material();
				material.setName(rs.getString(1));
				material.setWeight(rs.getFloat(2));
				materials.add(material);
			}
			h.put("materials", materials);

		} catch (SQLException e) {
			// TODO Auto-generated catch block

			return "数据库操作出错！";
		} catch (Exception e) {
			// TODO Auto-generated catch block

			return "出现未知错误！";
		} finally {
			try {
				b.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block

			}
		}
		return null;
	}

	public String getDetailMaterialUsed(String materialName) {//获得单个主料使用情况
		getDate();

		BaseSQL b = new BaseSQL();
		try {
			b.connect("eatery", "root", "123456");

			ResultSet rs = b.statement
					.executeQuery("select dishName,weight,dishNumber,weight*dishNumber totalNumber "
							+ "from dish,dish_material,material, "
							+ "(select dishID ,sum(amount) dishNumber "
							+ "from bill_dish "
							+ "where billID in "
							+ "(select billID from bill where type = "+checkedBill+" and endTime >= '"
							+ beginDate
							+ "' and endTime < '"
							+ endDate
							+ "') "
							+ "group by dishID) as dailyDish "
							+ "where dish_material.dishID = dailyDish.dishID "
							+ "and dish_material.materialID = material.materialID "
							+ "and material.materialName = '"
							+ materialName
							+ "' " + "and dish.dishID = dish_material.dishID; ");
			Vector<MaterialNumber> materialNumbers = new Vector<MaterialNumber>();
			int totalNumber = 0;
			int totalAmount = 0;
			while (rs.next()) {
				MaterialNumber materialNumber = new MaterialNumber();
				materialNumber.setDishName(rs.getString(1));
				materialNumber.setWeight(rs.getFloat(2));
				materialNumber.setNumber(rs.getInt(3));
				materialNumber.setAmount(rs.getFloat(4));
				materialNumbers.add(materialNumber);
				totalNumber += rs.getInt(3);
				totalAmount += rs.getFloat(4);
			}
			h.put("materialNumbers", materialNumbers);
			h.put("totalNumber", totalNumber);
			h.put("totalAmount", totalAmount);

		} catch (SQLException e) {
			// TODO Auto-generated catch block

			return "数据库操作出错！";
		} catch (Exception e) {
			// TODO Auto-generated catch block

			return "出现未知错误！";
		} finally {
			try {
				b.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block

			}
		}

		return null;
	}

	public String getDailyBillMain() {//获得日流水账单
		if (getDailyInformation("") == null && getDailyMaterialUsed() == null) {

			h.put("totalMoney", money);
			h.put("totalNumberOfMan", numberOfMan);
			h.put("materials", materials);
			return null;
		}
		return "出错！";
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}

	public int getMonth() {
		return month;
	}

	public void setMonth(int month) {
		this.month = month;
	}

	public int getDay() {
		return day;
	}

	public void setDay(int day) {
		this.day = day;
	}

	public Hashtable getH() {
		return h;
	}

	public void setH(Hashtable h) {
		this.h = h;
	}
}