package dao;

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

import entity.CommodityType;




/**
 * @author Administrator
 *商品类型的数据库处理类
 * 对应数据库COMMODITYTYPE表
 */
public class CommodityTypeDao {
	DBconn conn = new DBconn();
	//在数据库中添加商品类型
	public boolean add(CommodityType com){
		return conn.update("insert into CommodityType(PARENTTYPEID,TYPENAME) values(?,?)", 
				new String[]{String.valueOf(com.getParentTypeId()),com.getTypeName()})>0;
	} 
	//根据商品id来删除商品类型
	public boolean deleteById(int typeId){
		return conn.update("delete from CommodityType where typeId=?", 
				new String[]{String.valueOf(typeId)})>0;
	}
	//根据商品对象来更新商品类型
	public boolean updateByCommodityType(CommodityType com){
		return conn.update("update CommodityType set PARENTTYPEID=?,TYPENAME=? where typeId=?", 
				new String[]{String.valueOf(com.getParentTypeId()),
				             com.getTypeName(),
				             String.valueOf(com.getTypeId())})>0;
	}
	/**
	 * @param typeId
	 * @return根据ID获取商品类型的数据
	 */
	public CommodityType queryById(int typeId){
		CommodityType com=new CommodityType();
		ResultSet rs=conn.query("select * from CommodityType where typeId=?", new String[]{String.valueOf(typeId)});
		try {
			while(rs.next()){
				com.setTypeId(rs.getInt("typeId"));		//类别编号
				com.setTypeName(rs.getString("typeName"));	//类别名称
				com.setParentTypeId(rs.getInt("parentTypeId"));	//父类ID
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();		
		}
		return com;
	}/**
	 * @param typeId
	 * @return根据父类ID获取商品类型的数据
	 */
	public ArrayList<CommodityType> queryByPid(int parentTypeId){
		ArrayList<CommodityType> list=new ArrayList<CommodityType>();
		ResultSet rs=conn.query("select * from CommodityType where parentTypeId=? order by typeId ", new String[]{String.valueOf(parentTypeId)});
		try {
			while(rs.next()){
				CommodityType com=new CommodityType(rs.getInt("typeId"),
                        rs.getString("typeName"),
                        rs.getInt("parentTypeId"));
list.add(com);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();		
		}
		return list;
	}
	/**
	 * @return
	 * 返回所有的商品类型数据
	 */
	public ArrayList<CommodityType> queryAll(){
		ArrayList<CommodityType> list=new ArrayList<CommodityType>();
		ResultSet rs=conn.query("select * from CommodityType order by parentTypeId,typeId  ", null);
		try {
			while(rs.next()){
				CommodityType com=new CommodityType(rs.getInt("typeId"),
						                            rs.getString("typeName"),
						                            rs.getInt("parentTypeId"));
				list.add(com);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
	
	/**
	 * @param typeName类型名称
	 * @return根据类型名查询类型id
	 */
	public int queryTypeId(String typeName){
		int typeId=-1;
		String sql = "select typeid from commodityType where typename=?";
		ResultSet rs=conn.query(sql, new String[]{typeName});
		try {
			if(rs.next()){
				typeId = rs.getInt("typeid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return typeId;
	}
	
	/**
	 * @return所有子类型
	 */
	public ArrayList<CommodityType> querySubType(){
		ArrayList<CommodityType> list = new ArrayList<CommodityType>();
		String sql = "select * from commodityType where PARENTTYPEID<>0 order by typeId";
		ResultSet rs=conn.query(sql, null);
		try {
			while(rs.next()){
				CommodityType type = new CommodityType();
				type.setTypeId(rs.getInt(1));
				type.setParentTypeId(rs.getInt(2));
				type.setTypeName(rs.getString(3));
				list.add(type);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			conn.closeAll();
		}
		return list;
	}
}