monthrecord.java

简单的餐厅管理系统

package beans;

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

import util.BaseSQL;

public class MonthRecord {
	
	private int year;
	private int month;
	private String beginTime;
	private String endTime;
	private final static int checkedBill = 2;
	
	private Hashtable h = new Hashtable(); 
	
	private void getDate(){
		beginTime = year +"-"+month+"-"+1;
		endTime = year + "-" +(month+1)+"-"+"1";
	}
	
	public String getMonthMaterial(){
		
		getDate();

		BaseSQL b = new BaseSQL();
		try {
			b.connect("eatery", "root", "123456");
			Vector materials = new Vector();
			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 >= '"
							+ beginTime
							+ "' and endTime < '"
							+ endTime
							+ "') "
							+ "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);
			
			String sql = "select price,beginTime,endTime,numberOfMan,tableNum,billID,waiterID "
				+ " from bill where type = " + checkedBill +" and endTime >= '"
				+ beginTime
				+ "' and endTime < '" + endTime + "'";
			rs = b.statement
					.executeQuery(sql);
			float money = 0;
			int numberOfMan = 0;
			while (rs.next()) {

				money += rs.getFloat(1);
				numberOfMan += rs.getInt(4);
			}
			h.put("money", money);
			h.put("numberOfMan", numberOfMan);


		} 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 getOneMonthMaterial(String materialName){
		
		getDate();
		
		String bill_material = "select bill_dish.billID,dish_material.materialID," +
				"bill_dish.amount*dish_material.weight as totalused "+
				"from bill_dish,dish_material "+
				"where bill_dish.dishID = dish_material.dishID ";
		
		String sql ="select dayofmonth(bill.endTime),sum(bill_material.totalused) as weight "+
						"from bill,material,("+bill_material+") as bill_material "+
						"where bill.billID = bill_material.billID and material.materialID = bill_material.materialID and bill.billID in "+
						"(select billID from bill where endTime >= '"+beginTime+"' and endTime < '"+endTime+"' and type = 2) "+
						"and material.materialName = '"+materialName+"' "+
						"group by dayofmonth(bill.endTime),material.materialID order by dayofmonth(bill.endTime) asc ";
		
		BaseSQL b = new BaseSQL();

			try {
				b.connect("eatery","root","123456");
				ResultSet rs = b.statement.executeQuery(sql);
				Vector materialRecord = new Vector();
				while(rs.next()){
					int day = rs.getInt(1);
					float weight = rs.getFloat(2);
					
					MonthusedRecord record = new MonthusedRecord();
					record.setAmountFloat(weight);
					record.setDay(day);
					materialRecord.add(record);
				}
				h.put("materialName", materialName);
				h.put("materialRecord", materialRecord);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return "连接数据库出错";
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return "出现异常情况";
			}

			

		
		return null;
	}

	
	public String getOneMonthMoney(){
		getDate();
		String sql = "select dayofmonth(bill.endTime),sum(bill.price)"	+
				"from bill where bill.endTime>='"+beginTime+"' and bill.endTime<'"+endTime+"' and bill.type= "+checkedBill+
				"group by dayofmonth(bill.endTime)" + "order by dayofmonth(bill.endTime)";
		
		
		BaseSQL b = new BaseSQL();

		try {
			b.connect("eatery","root","123456");
			ResultSet rs = b.statement.executeQuery(sql);
			Vector moneyRecord = new Vector();
			while(rs.next()){
				int day = rs.getInt(1);
				float price = rs.getFloat(2);
				
				MonthusedRecord record = new MonthusedRecord();
				record.setAmountFloat(price);
				record.setDay(day);
				moneyRecord.add(record);
			}
			h.put("materialRecord", moneyRecord);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "连接数据库出错";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "出现异常情况";
		}
	
	return null;
	}
	
	public String getOneMonthPeople(){
		getDate();
		String sql = "select dayofmonth(bill.endTime),sum(bill.numberOfMan)"	+
				"from bill where bill.endTime>='"+beginTime+"' and bill.endTime<'"+endTime+"' and bill.type= "+checkedBill+
				"group by dayofmonth(bill.endTime)" + "order by dayofmonth(bill.endTime)";
		
		
		BaseSQL b = new BaseSQL();

		try {
			b.connect("eatery","root","123456");
			ResultSet rs = b.statement.executeQuery(sql);
			Vector peopleRecord = new Vector();
			while(rs.next()){
				int day = rs.getInt(1);
				int numberOfMan = rs.getInt(2);
				
				MonthusedRecord record = new MonthusedRecord();
				record.setAmountInt(numberOfMan);
				record.setDay(day);
				peopleRecord.add(record);
			}
			h.put("materialRecord", peopleRecord);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "连接数据库出错";
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "出现异常情况";
		}
	
	return null;
	}
	
	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 String getBeginTime() {
		return beginTime;
	}

	public void setBeginTime(String beginTime) {
		this.beginTime = beginTime;
	}

	public String getEndTime() {
		return endTime;
	}

	public void setEndTime(String endTime) {
		this.endTime = endTime;
	}

	public Hashtable getH() {
		return h;
	}

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