package com.mwq.dao;

import java.util.Vector;

public class Dao extends BaseDao {

	private static Dao dao;

	static {
		dao = new Dao();
	}

	public static Dao getInstance() {
		return dao;
	}

	// tb_user
	public Vector sUser() {
		return selectSomeNote("select name,sex,birthday,id_card,freeze from tb_user where freeze='正常'");
	}

	public Vector sUserNameOfNotFreeze() {
		return selectSomeValue("select name from tb_user where freeze='正常'");
	}

	public Vector sUserByName(String name) {
		return selectOnlyNote("select * from tb_user where name='" + name + "'");
	}

	public boolean uPasswordByName(String name, String password) {
		return super.longHaul("update tb_user set password='" + password
				+ "' where name='" + name + "'");
	}

	public boolean uFreezeByName(String name, String freeze) {
		return super.longHaul("update tb_user set freeze='" + freeze
				+ "' where name='" + name + "'");
	}

	public boolean iUser(String values[]) {
		String sql = "insert into tb_user(name,sex,birthday,id_card,password,freeze) values('"
				+ values[0]
				+ "','"
				+ values[1]
				+ "','"
				+ values[2]
				+ "','"
				+ values[3] + "','" + values[4] + "','" + values[5] + "')";
		System.out.println(sql);
		return longHaul(sql);
	}

	// tb_order_form
	public Vector sOrderFormOfDay(String date) {
		return selectSomeNote("select * from tb_order_form where datetime between '"
				+ date + " 00:00:00' and '" + date + " 23:59:59'");
	}

	public String sOrderFormOfMaxId() {
		Object object = selectOnlyValue("select max(num) from tb_order_form");
		if (object == null) {
			return null;
		} else {
			return object.toString();
		}
	}

	public String sOrderFormOfMinDatetime() {
		Object object = selectOnlyValue("select min(datetime) from tb_order_form");
		if (object == null) {
			return null;
		} else {
			return object.toString();
		}
	}

	public String[] monthCheckOut(String num) {
		String values[] = { "——", "——", "——", "——", "——" };
		String sqls[] = {
				"select count(*) from tb_order_form where num like '" + num
						+ "%'",
				"select sum(money) from tb_order_form where num like '" + num
						+ "%'",
				"select avg(money) from tb_order_form where num like '" + num
						+ "%'",
				"select max(money) from tb_order_form where num like '" + num
						+ "%'",
				"select min(money) from tb_order_form where num like '" + num
						+ "%'" };
		for (int i = 0; i < sqls.length; i++) {
			Object value = super.selectOnlyValue(sqls[i]);
			if (value != null) {
				String v = value.toString();
				if (!v.equals("0"))
					values[i] = v;
			}
		}
		return values;
	}

	public Vector yearCheckOut(int year) {
		Vector<Vector> valueV = new Vector<Vector>();
		String sql = "";
		String formatMonth = "";
		String formatDay = "";
		for (int day = 1; day <= 31; day++) {
			Vector rowV = new Vector();// 统计行
			rowV.add(day);// 加入日期
			formatDay = (day < 10 ? "0" + day : "" + day);
			for (int month = 1; month <= 12; month++) {// 做统计
				formatMonth = (month < 10 ? "0" + month : "" + month);
				sql = "select sum(money) from tb_order_form where num like '"
						+ year + formatMonth + formatDay + "%'";
				yearCheckOut(rowV, sql);
			}
			sql = "select sum(money) from tb_order_form where num like '"
					+ year + "__" + formatDay + "%'";// 做列总计
			yearCheckOut(rowV, sql);
			valueV.add(rowV);
		}
		Vector rowV = new Vector();// 总计行
		rowV.add("总计");
		for (int month = 1; month <= 12; month++) {// 做月总计
			formatMonth = (month < 10 ? "0" + month : "" + month);
			sql = "select sum(money) from tb_order_form where num like '"
					+ year + formatMonth + "%'";
			yearCheckOut(rowV, sql);
		}
		sql = "select sum(money) from tb_order_form where num like '" + year
				+ "%'";// 做年总计
		yearCheckOut(rowV, sql);
		valueV.add(rowV);

		return valueV;
	}

	public void yearCheckOut(Vector rowV, String sql) {
		Object value = super.selectOnlyValue(sql