package net.shopxx.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import net.shopxx.dao.ProductCategoryDao;
import net.shopxx.entity.ProductCategory;
import util.DBPoolConnection;


public class ProductCategoryImpl implements ProductCategoryDao{
	Connection conn;
	PreparedStatement ps;
	PreparedStatement ps1;
	ResultSet rs;
	ResultSet rs1;
	@Override
	/*
	 * 获得所有大的商品分类（grade=0）
	 */
	public List<ProductCategory> getAllProductCategory() {
		List<ProductCategory> list=new ArrayList<>();
		try {
			String sql="select * from xx_product_category where grade=0";
			conn=DBPoolConnection.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				ProductCategory productCategory=new ProductCategory();
				productCategory.setName(rs.getString("name"));
				productCategory.setOrders(rs.getShort("orders"));
				productCategory.setId(rs.getInt("id"));
				list.add(productCategory);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBPoolConnection.close(conn, ps, rs);
		}
		
		return list;	
	}
	
	/*
	 * 获得所有小的商品分类
	 */
	public List<ProductCategory> getAllProductCategorySmall() {
		List<ProductCategory> list=new ArrayList<>();
		try {
			String sql="select * from xx_product_category where grade=0";
			int grade;
			conn=DBPoolConnection.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				ProductCategory productCategory=new ProductCategory();
				productCategory.setName(rs.getString("name"));
				productCategory.setOrders(rs.getShort("orders"));
				grade=rs.getShort("orders");
				String sql1="select * from xx_product_category where parent="+grade;
				ps1=conn.prepareStatement(sql1);
				rs1=ps1.executeQuery();
				while(rs1.next()){
					ProductCategory productCategory1=new ProductCategory();
					productCategory1.setName(rs1.getString("name"));
					productCategory1.setOrders(rs1.getShort("orders"));
					productCategory1.setParent(grade);
					productCategory1.setId(rs1.getInt("id"));
					list.add(productCategory1);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBPoolConnection.close(conn, ps, rs);
		}
		
		return list;	
	}
	
	
	@Override
	/*
	 * 添加商品分类
	 */
	public void addProductCategory(ProductCategory productCategory) {
		try {
			String sql;
			if(productCategory.getParent()==0){//他是顶级分类
				sql="insert into xx_product_category (create_date,modify_date,grade,name,tree_path,parent) values (now(),now(),0,'"+productCategory.getName()+"',',',null)";
			}else{//他不是顶级分类
				sql="insert into xx_product_category (create_date,modify_date,grade,name,tree_path,parent) values (now(),now(),1,'"+productCategory.getName()+"','"+productCategory.getParent()+"','"+productCategory.getParent()+"')";
			}
			conn=DBPoolConnection.getConnection();
			ps=conn.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBPoolConnection.close(conn, ps, rs);
		}
	}


}