package cn.edu.chu.category;

import java.sql.*;
import java.util.List;

import cn.edu.chu.user.*;
import cn.edu.chu.util.DB;

public class CategoryDAO {
	public static  void save(Category c) {
		Connection conn = null;
		PreparedStatement pstmt =null;
		try {
			conn=DB.getConn();
			String sql="";
			if(c.getId()==-1){
				sql="insert into category values(null,?,?,?,?,?)";
			}else{
				sql="insert into category values("+c.getId()+",?,?,?,?,?)";
			}
			pstmt=DB.getStmt(conn, sql);
			pstmt.setInt(1, c.getPid());
			pstmt.setString(2, c.getName());
			pstmt.setString(3, c.getDescr());			
			pstmt.setInt(4, c.getCno());
			pstmt.setInt(5, c.getGrade());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}

	}
	
	public static Category getCategory(int id){
		Category c=null;
		Connection conn = null;
		ResultSet rs=null;
		try {
			conn=DB.getConn();
			String sql="select * from category where id="+id;
//System.out.println(sql);
			rs=DB.executeQuery(conn, sql);
			while(rs.next()){
				c=new Category();
				c.setId(rs.getInt("id"));
				c.setPid(rs.getInt("pid"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setCno(rs.getInt("cno"));
				c.setGrade(rs.getInt("grade"));
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return c;
	}
	
	public static void getCategories(List<Category> list){
		Connection conn = null;
		ResultSet rs=null;
		try {
			conn=DB.getConn();
			String sql="select * from category";
			rs=DB.executeQuery(conn, sql);
			while(rs.next()){
				Category c=new Category();
				c.setId(rs.getInt("id"));
				c.setPid(rs.getInt("pid"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setCno(rs.getInt("cno"));
				c.setGrade(rs.getInt("grade"));
				list.add(c);				
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
	}

	public static void delete(int id) {
		Connection conn=null;
		Statement stmt=null;
		String sql="delete from category where 1=1 and (id="+id+" or pid="+id+")";
//System.out.println(sql);
		try {
			conn=DB.getConn();
			stmt=DB.getStmt(conn);
			stmt.executeUpdate(sql);			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DB.close(stmt);
			DB.close(conn);
		}		
		
	}

	public static void delete(int id, int pid) {
		
		
	}

	public static void getCategories(List<Category> list,int id){
		Connection conn = null;
		ResultSet rs=null;
		try {
			conn=DB.getConn();
			String sql="select * from category where pid="+id;
			rs=DB.executeQuery(conn, sql);
			while(rs.next()){
				Category c=new Category();
				c.setId(rs.getInt("id"));
				c.setPid(rs.getInt("pid"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setCno(rs.getInt("cno"));
				c.setGrade(rs.getInt("grade"));
				list.add(c);
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
	}
	
	public static void getCategoriesTree(List<Category> list,int id){
		Connection conn = null;
		ResultSet rs=null;
		try {
			conn=DB.getConn();
			String sql="select * from category where pid="+id;
			rs=DB.executeQuery(conn, sql);
			while(rs.next()){
				Category c=new Category();
				c.setId(rs.getInt("id"));
				c.setPid(rs.getInt("pid"));
				c.setName(rs.getString("name"));
				c.setDescr(rs.getString("descr"));
				c.setCno(rs.getInt("cno"));
				c.setGrade(rs.getInt("grade"));
				list.add(c);
//System.out.println("getCategoriesTree before if!");
				if(rs.getInt("grade")>=1){
//System.out.println("getCategoriesTree after! if");
					getCategoriesTree(list,rs.getInt("id"));
				}
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
	}

	/*public static void addChildCategory(int pid,String name,String descr) {
		Connection conn = null;
		ResultSet rs=null;
		try {
			conn=DB.getConn();
			Category c=new Category();
			
			}			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		
	}*/

}