package com.lovo.cq.candy.daoimpl;

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

import com.lovo.cq.candy.common.DbUtil;
import com.lovo.cq.candy.dao.AdminCandyDao;
import com.lovo.cq.candy.po.Candy;
import com.lovo.cq.candy.po.CandyPager;
import com.lovo.cq.candy.po.Inform;
import com.lovo.cq.candy.po.InformPager;

public class AdminCandyDaoImpl implements AdminCandyDao{

	public List getAllCandys() {
		List candys = new ArrayList();
		DbUtil daoUtil = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		try {
			daoUtil = new DbUtil();
			sql = "select * from tb_candy";
			ps = daoUtil.getCon().prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Candy candy = new Candy();
				candy.setCandyId(rs.getInt("candyId"));
				candy.setSuperTypeId(rs.getInt("superTypeId"));
				candy.setSubTypeId(rs.getInt("subTypeId"));
				candy.setCandyName(rs.getString("candyName"));
				candy.setIntroduce(rs.getString("introduce"));
				candy.setPrice(rs.getFloat("price"));
				candy.setNowPrice(rs.getFloat("nowPrice"));
				candy.setPicture(rs.getString("picture"));
				candy.setInTime(rs.getString("inTime"));
				candy.setNewCandys(rs.getInt("newCandys"));
				candy.setSaleCandys(rs.getInt("saleCandys"));
				candy.setHostCandys(rs.getInt("hostCandys"));
				candy.setCandyNum(rs.getInt("candyNum"));
				candys.add(candy);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				daoUtil.close();
			} catch(SQLException s) {
				s.printStackTrace();
			}
		}
		return candys;
	}

	public boolean addCandy(Candy candy) {
		DbUtil dao = null;
		PreparedStatement ps = null;
		String sql = "";
		try {
			dao = new DbUtil();
			sql = "insert into tb_candy values(null,?,?,?,?,?,?,?,?,?,?,?,null,?,?,?,?,?)";
			ps = dao.getCon().prepareStatement(sql);
			ps.setInt(1, candy.getSuperTypeId());
			ps.setInt(2, candy.getSubTypeId());
			ps.setString(3, candy.getCandyName());
			ps.setString(4, candy.getISBN());
			ps.setString(5, candy.getIntroduce());
			ps.setFloat(6, candy.getPrice());
			ps.setFloat(7, candy.getNowPrice());
			ps.setString(8, candy.getPicture());
			ps.setInt(9, candy.getPages());
			ps.setString(10, candy.getAdress());
			ps.setString(11, candy.getCooker());
			ps.setInt(12, candy.getNewCandys());
			ps.setInt(13, candy.getSaleCandys());
			ps.setInt(14, candy.getHostCandys());
			ps.setInt(15, candy.getSpecialCandys());
			ps.setInt(16, candy.getCandyNum());
			int i = ps.executeUpdate();
			if(i != 0) {
				return true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				dao.close();
			} catch(SQLException s) {
				s.printStackTrace();
			}
		}
		return false;
	}

	public boolean checkCandyNameIsExist(String candyName) {
		DbUtil daoUtil = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		try {
			daoUtil = new DbUtil();
			sql = "select * from tb_candy where candyName = ?";
			ps = daoUtil.getCon().prepareStatement(sql);
			ps.setString(1, candyName);
			rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				daoUtil.close();
			} catch(SQLException s) {
				s.printStackTrace();
			}
		}
		return false;
	}

	public boolean checkISBNIsExist(String ISBN) {
		DbUtil daoUtil = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		try {
			daoUtil = new DbUtil();
			sql = "select * from tb_candy where ISBN = ?";
			ps = daoUtil.getCon().prepareStatement(sql);
			ps.setString(1, ISBN);
			rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				daoUtil.close();
			} catch(SQLException s) {
				s.printStackTrace();
			}
		}
		return false;
	}

	public CandyPager searchCandy(String candyName) {
		Map candyMap = new HashMap();
		DbUtil daoUtil = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		try {
			daoUtil = new DbUtil();
			sql = "select * from tb_candy where candyName like ?";
			ps = daoUtil.getCon().prepareStatement(sql);
			ps.setString(1, candyName+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Candy candy = new Candy();
				candy.setCandyId(rs.getInt("candyId"));
				candy.setSuperTypeId(rs.getInt("superTypeId"));
				candy.setSubTypeId(rs.getInt("subTypeId"));
				candy.setCandyName(rs.getString("candyName"));
				candy.setIntroduce(rs.getString("introduce"));
				candy.setPrice(rs.getFloat("price"));
				candy.setNowPrice(rs.getFloat("nowPrice"));
				candy.setPicture(rs.getString("picture"));
				candy.setInTime(rs.getString("inTime"));
				candy.setNewCandys(rs.getInt("newCandys"));
				candy.setSaleCandys(rs.getInt("saleCandys"));
				candy.setHostCandys(rs.getInt("hostCandys"));
				candy.setCandyNum(rs.getInt("candyNum"));
				candyMap.put(candy.getCandyId(),candy);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				daoUtil.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		CandyPager bp = new CandyPager();
		bp.setCandyMap(candyMap);
		bp.setTotalNum(candyMap.size());
		return bp;
	}

	public List getAllCandyName() {
		List candyNameList = new ArrayList();
		DbUtil daoUtil = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = null;
		try {
			daoUtil = new DbUtil();
			sql = "select candyName from tb_candy";
			ps = daoUtil.getCon().prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				candyNameList.add(rs.getString("candyName"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				daoUtil.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return candyNameList;
	}
	public CandyPager getCandyPager(int index,int pageSize) {
		Map candyMap = new HashMap();
		DbUtil db = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			db = new DbUtil();
			String sql = "select * from tb_candy limit ?,?";
			ps = db.getCon().prepareStatement(sql);
			ps.setInt(1, index);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while(rs.next()) {
				Candy candy = new Candy();
				candy.setCandyId(rs.getInt("candyId"));
				candy.setSuperTypeId(rs.getInt("superTypeId"));
				candy.setSubTypeId(rs.getInt("subTypeId"));
				candy.setCandyName(rs.getString("candyName"));
				candy.setIntroduce(rs.getString("introduce"));
				candy.setPrice(rs.getFloat("price"));
				candy.setNowPrice(rs.getFloat("nowPrice"));
				candy.setPicture(rs.getString("picture"));
				candy.setInTime(rs.getString("inTime"));
				candy.setNewCandys(rs.getInt("newCandys"));
				candy.setSaleCandys(rs.getInt("saleCandys"));
				candy.setHostCandys(rs.getInt("hostCandys"));
				candy.setCandyNum(rs.getInt("candyNum"));
				candyMap.put(candy.getCandyId(), candy);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				db.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		CandyPager bp = new CandyPager();
		bp.setCandyMap(candyMap);
		bp.setPageSize(pageSize);
		bp.setTotalNum(getAllCandys().size());
		return bp;
	}
	public boolean deleteCandy(int[] candyIds) {
		DbUtil daoUtil = null;
		PreparedStatement ps = null;
		Connection conn = null;
		String sql = "delete from tb_candy where candyId=?";
		try {
			daoUtil = new DbUtil();
			conn = daoUtil.getCon();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for(int j=0;j<candyIds.length;j++) {
				ps.setInt(1, candyIds[j]);
				ps.addBatch();
			}
			int[] k = ps.executeBatch();
			conn.commit();
			if(k.length == candyIds.length) {
				return true;
			}
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		} finally {
			try {
				ps.close();
				daoUtil.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}
}