package cn.edu.lingnan.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import cn.edu.lingnan.model.Company;
import cn.edu.lingnan.model.Consumer;
import cn.edu.lingnan.model.PageBean;
import cn.edu.lingnan.util.StringUtil;

public class ConsumerDao {

	public ResultSet consumerList(Connection con,PageBean pageBean,Consumer consumer)throws Exception{
		StringBuffer sb=new StringBuffer("select * from t_consumer");
		if(consumer!=null && StringUtil.isNotEmpty(consumer.getConsumerName())){
			sb.append(" and consumerName like '%"+consumer.getConsumerName()+"%'");
		}
		if(pageBean!=null){
			sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		return pstmt.executeQuery();
	}
	
	public int consumerCount(Connection con,Consumer consumer)throws Exception{
		StringBuffer sb=new StringBuffer("select count(*) as total from t_consumer");
		if(StringUtil.isNotEmpty(consumer.getConsumerName())){
			sb.append(" and consumerName like '%"+consumer.getConsumerName()+"%'");
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			return rs.getInt("total");
		}else{
			return 0;
		}
	}
	
	/**
	 * 
	 * delete from tableName where field in (1,3,5)
	 * @param con
	 * @param delIds
	 * @return
	 * @throws Exception
	 */
	public int consumerDelete(Connection con,String delIds)throws Exception{
		String sql="delete from t_consumer where id in("+delIds+")";
		PreparedStatement pstmt=con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}
	
	public int consumerAdd(Connection con,Consumer consumer)throws Exception{
		String sql="insert into t_consumer values(null,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, consumer.getConsumerName());
		pstmt.setString(2, consumer.getPassword());
		return pstmt.executeUpdate();
	}
	
	public int consumerModify(Connection con,Consumer consumer)throws Exception{
		String sql="update t_consumer set consumerName=?,password=? where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, consumer.getConsumerName());
		pstmt.setString(2, consumer.getPassword());
		pstmt.setInt(3, consumer.getId());
		return pstmt.executeUpdate();
	}

	
}