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.SmallCategory;
import com.gc.impl.ISmallCategory;

public class DSmallCategory implements ISmallCategory{
	private PreparedStatement pre = null;
	private Connection con = null;
	private ResultSet rs = null;
	
	public List<SmallCategory> selectSmallCategoryBySCID(int scid) {
		List<SmallCategory> list = new ArrayList<SmallCategory>();
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement("select * from SmallCategory where SCID="+scid+" and SCID!=0");
				rs = pre.executeQuery();
				while(rs.next())
				{
					SmallCategory sa = new SmallCategory();
					sa.setScid(rs.getInt(1));
					sa.setSname(rs.getString(2));
					sa.setCid(rs.getInt(3));
					list.add(sa);
				}
			}
			rs.close();
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

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

	public boolean OperatorSmallCategory(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<SmallCategory> selectAllSmallCategory() {
		List<SmallCategory> list = new ArrayList<SmallCategory>();
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement("select * from SmallCategory");
				rs = pre.executeQuery();
				while(rs.next())
				{
					SmallCategory sa = new SmallCategory();
					sa.setScid(rs.getInt(1));
					sa.setSname(rs.getString(2));
					sa.setCid(rs.getInt(3));
					list.add(sa);
				}
			}
			rs.close();
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

	public List<SmallCategory> selectSmallCategoryBySql(String sql) {
		List<SmallCategory> list = new ArrayList<SmallCategory>();
		try
		{
			dao d = new dao();
			con = d.getCon();
			if(con!=null)
			{
				pre = con.prepareStatement(sql);
				rs = pre.executeQuery();
				while(rs.next())
				{
					SmallCategory sa = new SmallCategory();
					sa.setScid(rs.getInt(1));
					sa.setSname(rs.getString(2));
					sa.setCid(rs.getInt(3));
					list.add(sa);
				}
			}
			rs.close();
			pre.close();
			con.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}

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

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