package com.qrsx.buyflowers.beans;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

public class GoodsBean extends DBConnection {
	private PreparedStatement ptst = null;

	private Collection collection = null;

	private GoodsClass goodsClass;

	private ResultSet rs = null;

	public GoodsBean() throws ClassNotFoundException, SQLException {
		super();
	}

	public void setGoodsInfo(GoodsClass goodsClass) {
		this.goodsClass = goodsClass;
	}
	
	
	/*public GoodsClass getGoodsInfo(String goodsId) {
		String sql = "select * from goods where goodsId=?";
		try {
			ptst = con.prepareStatement(sql);
			ptst.setString(1, goodsId);
			rs = ptst.executeQuery();
			//rs.next();
			goodsClass = new GoodsClass();
			if (rs.next()) {
				
				goodsClass.setGoodsId(rs.getString("goodsId"));
				goodsClass.setGoodsName(rs.getString("goodsName"));
				goodsClass.setGoodsmaterial(rs.getString("goodsmaterial"));
				goodsClass.setGoodsPackage(rs.getString("goodsPackage"));
				goodsClass.setGoodsLanguage(rs.getString("goodsLanguage"));
				goodsClass.setGoodsPrice(rs.getFloat("goodsPrice"));
				goodsClass.setGoodsCent(rs.getString("goodsCent"));
				goodsClass.setGoodsScope(rs.getString("goodsScope"));
				goodsClass.setGoodsPlace(rs.getString("goodsPlace"));
				goodsClass.setGoodsHabitus(rs.getString("goodsHabitus"));
				goodsClass.setSortName(rs.getString("sortName"));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return goodsClass;

	}*/

	public int getAllPageByPrice(String goodsPrice) {
		int count = 0;
		int page = 0;
		int i = goodsPrice.indexOf('-');
		String s = goodsPrice.substring(0, i);
		String d = goodsPrice.substring(i + 1, goodsPrice.length()-1);
		float price1 = Float.parseFloat(s);
		float price2 = Float.parseFloat(d);
		String sql = "select goodsPrice from goods where goodsPrice between ? and ?";
		try {
			ptst = con.prepareStatement(sql);
			ptst.setFloat(1, price1);
			ptst.setFloat(2, price2);
			rs = ptst.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		if (count % 16 != 0) {
			page = (count - count % 16) / 16 + 1;
		} else {
			page = count / 16;
		}
		return page;
	}
	
	public Collection getGoods(String id){
		String sql="select * from goods where goodsId=?";
		try {
			ptst=con.prepareStatement(sql);
			ptst.setString(1, id);
			rs=ptst.executeQuery();
		   collection=new ArrayList();
		   while(rs.next()){
			   goodsClass=new GoodsClass();
			   goodsClass.setGoodsId(rs.getString("goodsId"));
				goodsClass.setGoodsName(rs.getString("goodsName"));
				goodsClass.setGoodsmaterial(rs.getString("goodsmaterial"));
				goodsClass.setGoodsPackage(rs.getString("goodsPackage"));
				goodsClass.setGoodsLanguage(rs.getString("goodsLanguage"));
				goodsClass.setGoodsPrice(rs.getFloat("goodsPrice"));
				goodsClass.setGoodsCent(rs.getString("goodsCent"));
				goodsClass.setGoodsScope(rs.getString("goodsScope"));
				goodsClass.setGoodsPlace(rs.getString("goodsPlace"));
				goodsClass.setGoodsHabitus(rs.getString("goodsHabitus"));
				goodsClass.setSortName(rs.getString("sortName"));
				collection.add(goodsClass);
		   }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return collection;
	}

	public Collection getGoodsByPrice(String goodsPrice, int pageCount) {
		int i = goodsPrice.indexOf('-');
		String s = goodsPrice.substring(0, i);
		String d = goodsPrice.substring(i + 1, goodsPrice.length());
		float price1 = Float.parseFloat(s);
		float price2 = Float.parseFloat(d);
		String sql = "select * from goods where goodsprice between ? and ?";
		try {
			ptst = con.prepareStatement(sql);
			ptst.setFloat(1, price1);
			ptst.setFloat(2, price2);
			rs = ptst.executeQuery();
			collection = new ArrayList();
			rs.next();
			int n = pageCount * 16;
			for (int j = 0; j < n; j++)
				rs.next();
			for (int m = 0; m < 16; m++, rs.next()) {
				goodsClass = new GoodsClass();
				goodsClass.setGoodsId(rs.getString("goodsId"));
				goodsClass.setGoodsName(rs.getString("goodsName"));
				goodsClass.setGoodsmaterial(rs.getString("goodsmaterial"));
				goodsClass.setGoodsPackage(rs.getString("goodsPackage"));
				goodsClass.setGoodsLanguage(rs.getString("goodsLanguage"));
				goodsClass.setGoodsPrice(rs.getFloat("goodsPrice"));
				goodsClass.setGoodsCent(rs.getString("goodsCent"));
				goodsClass.setGoodsScope(rs.getString("goodsScope"));
				goodsClass.setGoodsPlace(rs.getString("goodsPlace"));
				goodsClass.setGoodsHabitus(rs.getString("goodsHabitus"));
				goodsClass.setSortName(rs.getString("sortName"));
				collection.add(goodsClass);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return collection;
	}

	public int getAllPageBySort(String sortName) {
		int count = 0;
		int page = 0;
		String sql = "select sortName from goods";
		try {
			ptst = con.prepareStatement(sql);
			rs = ptst.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}

		if (count % 16 != 0) {
			page = (count - count % 16) / 16 + 1;
		} else {
			page = count / 16;
		}
		return page;

	}

	public Collection getGoodsBySort(String sortName, int pageCount) {
		String sql = "select * from goods where sortName=? ";

		try {
			ptst = con.prepareStatement(sql);
			ptst.setString(1, sortName);
			rs = ptst.executeQuery();
			collection = new ArrayList();
			rs.next();
			int n = pageCount * 16;
			for (int j = 0; j < n; j++)
				rs.next();
			for (int m = 0; m < 16; m++, rs.next()) {
				goodsClass = new GoodsClass();
				goodsClass.setGoodsId(rs.getString("goodsId"));
				goodsClass.setGoodsName(rs.getString("goodsName"));
				goodsClass.setGoodsmaterial(rs.getString("goodsmaterial"));
				goodsClass.setGoodsPackage(rs.getString("goodsPackage"));
				goodsClass.setGoodsLanguage(rs.getString("goodsLanguage"));
				goodsClass.setGoodsPrice(rs.getFloat("goodsPrice"));
				goodsClass.setGoodsCent(rs.getString("goodsCent"));
				goodsClass.setGoodsScope(rs.getString("goodsScope"));
				goodsClass.setGoodsPlace(rs.getString("goodsPlace"));
				goodsClass.setGoodsHabitus(rs.getString("goodsHabitus"));
				goodsClass.setSortName(rs.getString("sortName"));
				collection.add(goodsClass);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return collection;

	}

	public int getRowCount() {
		int count = 0;
		String sql = "select * from goods";
		try {
			ptst = con.prepareStatement(sql);
			rs = ptst.executeQuery();
			while (rs.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	public Collection getGoodsByHeightSelect(String searchkey, String action,
			String jiage, String anclassid) {
		collection=new ArrayList();
		String s = "%" + searchkey + "%";
		if (action==null) action="%";
		if(jiage==null) jiage="5000";
		if (anclassid==null) anclassid="%";

		try {

			if (action.trim().equals("商品名稱")) {
				 String sql = "select * from goods where goodsName like ? and goodsPrice<=? and sortName= ?";
				ptst = con.prepareStatement(sql);
			} else if (action.trim().equals("品牌")) {
				String sql = "select * from goods where goodsLanguage  like ? and  goodsPrice<=? and sortName=?";
				ptst = con.prepareStatement(sql);
			} else {
				String sql = "select * from goods where goodsmaterial like ? and goodsPrice<=? and sortName=?";
				ptst = con.prepareStatement(sql);
			}
      
			ptst.setString(1, s);
			ptst.setFloat(2, Float.parseFloat(jiage));
			ptst.setString(3, anclassid);
			rs = ptst.executeQuery();
			while (rs.next()) {
				goodsClass = new GoodsClass();
				goodsClass.setGoodsId(rs.getString("goodsId"));
				goodsClass.setGoodsName(rs.getString("goodsName"));
				goodsClass.setGoodsmaterial(rs.getString("goodsmaterial"));
				goodsClass.setGoodsPackage(rs.getString("goodsPackage"));
				goodsClass.setGoodsLanguage(rs.getString("goodsLanguage"));
				goodsClass.setGoodsPrice(rs.getFloat("goodsPrice"));
				goodsClass.setGoodsCent(rs.getString("goodsCent"));
				goodsClass.setGoodsScope(rs.getString("goodsScope"));
				goodsClass.setGoodsPlace(rs.getString("goodsPlace"));
				goodsClass.setGoodsHabitus(rs.getString("goodsHabitus"));
				goodsClass.setSortName(rs.getString("sortName"));
				collection.add(goodsClass);