package com.gc.dao;

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

import com.gc.action.Category;
import com.gc.impl.ICategory;

public class DCategory implements ICategory{
	
	private PreparedStatement pre = null;
	private Connection con = null;
	private ResultSet rs = null;

	public List<Category> selectCategoryByCID(int cid) {
		List<Category> list = new ArrayList<Category>();
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement("select * from Category where CID="+cid+" and CID!=0");
				rs = pre.executeQuery();
				while(rs.next())
				{
					Category ca = new Category();
					ca.setCid(rs.getInt(1));
					ca.setCname(rs.getString(2));
					list.add(ca);
				}
			}
			rs.close();
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public boolean OperatorCategory(String sql) {
		int count = 0;
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement(sql);
				count = pre.executeUpdate();
			}
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		if(count>0)
		{
			return true;
		}else
		{
			return false;
		}
	}

	public List<Category> selectAllCategory() {
		List<Category> list = new ArrayList<Category>();
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement("select * from Category where CID!=0");
				rs = pre.executeQuery();
				while(rs.next())
				{
					Category ca = new Category();
					ca.setCid(rs.getInt(1));
					ca.setCname(rs.getString(2));
					list.add(ca);
				}
			}
			rs.close();
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public List<Category> selectCategoryByCName(String cname) {
		List<Category> list = new ArrayList<Category>();
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement("select * from Category where CName='"+cname+"' and CID!=0");
				rs = pre.executeQuery();
				while(rs.next())
				{
					Category ca = new Category();
					ca.setCid(rs.getInt(1));
					ca.setCname(rs.getString(2));
					list.add(ca);
				}
			}
			rs.close();
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public boolean DeleteCategoryByCID(int cid) {
		int count = 0;
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareCall("{call proc_delCategory(?)}");
				pre.setInt(1,cid);
				count = pre.executeUpdate();
			}
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		if(count==0)
		{
			return true;
		}else
		{
			return false;
		}
	}

	public int selectCountOfCategory() {
		int count = 0;
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement("select count(CID) from Category");
				rs = pre.executeQuery();
				if(rs.next())
				{
					count = rs.getInt(1);
				}
			}
			rs.close();
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return count;
	}
}