package com.jojo.dao;

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

import com.jojo.model.User;

/**
 * ¶ÔUserÀàÊý¾Ý½øÐÐ²Ù×÷
 * 
 * @author flash.J
 *
 */
public class UserDao {

	private Connection conn = null;

	/**
	 * ¹¹Ôìº¯Êý»ñÈ¡Á¬½Ó£¬¸ÃÀà²»¸ºÔð¹Ø±Õ
	 * 
	 * @param conn
	 */
	public UserDao(Connection conn) {
		this.conn = conn;
	}

	/**
	 * ÓÃ»§×¢²á
	 * 
	 * Íùuser±íÖÐÌí¼ÓÒ»ÐÐÊý¾Ý¡£³É¹¦·µ»Ø1
	 * 
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public int addUser(User user) throws SQLException {
		String sql = "insert into t_user values(null,?,?,?,?,?,?,?,?,?,?,?,null)";
		PreparedStatement pstmt = conn.prepareStatement(sql);

		// ÎÒÏÖÔÚ¾õµÃÐ´jdbc¶ñÐÄÁË
		pstmt.setString(1, user.getUserName());
		pstmt.setString(2, user.getPassword());
		pstmt.setInt(3, user.getUserIdCard());
		pstmt.setString(4, user.getUserRealName());
		
		//Èç¹ûsexÎª¿ÕÔòÉèÖÃÄ¬ÈÏÐÔ±ðÎªÅ®
		if(user.getSex() == null || user.getSex().length() == 0){
			user.setSex("Å®");
		}
		pstmt.setString(5, user.getSex());
		
		pstmt.setInt(6, user.getAge());
		pstmt.setString(7, user.getUserTel());
		pstmt.setString(8, user.getAddress());
		pstmt.setInt(9, user.getGroup());

		// ²åÈë´øÊ±¼äµÄÈÕÆÚ
		user.setAddTime(new java.util.Date(System.currentTimeMillis()));
		Timestamp ts = new Timestamp(user.getAddTime().getTime());
		pstmt.setTimestamp(10, ts);

		pstmt.setDouble(11, user.getSum());

		return pstmt.executeUpdate();
	}

	/**
	 * ¸ù¾ÝuserIdÉ¾³ýÓÃ»§£¬³É¹¦·µ»Ø1
	 * 
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public int deleteUser(int userId) throws SQLException {
		// Ö»É¾Ò»¸ö¾ÍÖ±½Ó¼ÓÔÚºóÍ·£¬Ê¡µÄ·³
		String sql = "delete from t_user where userId = " + userId;
		PreparedStatement pstmt = conn.prepareStatement(sql);
		int row = pstmt.executeUpdate();
		return row;
	}

	/**
	 * ¸ù¾ÝÓÃ»§ÃûÓëÃÜÂë²éÑ¯ÓÃ»§£¬³É¹¦·µ»ØUser¶ÔÏó£¬²»³É¹¦·µ»Ønull
	 * 
	 * @return
	 * @throws SQLException
	 */
	public User selectUser(String userName, String password) throws SQLException {
		User resultUser = null;

		// Òª²»Òª¸øuserNameºÍpassword½¨¸öË÷Òý ?
		String sql = "select * from t_user where userName = ? and password = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, userName);
		pstmt.setString(2, password);
		ResultSet rs = pstmt.executeQuery();

		if (rs.next()) {
			resultUser = new User();

			// ¡£¡£¡£²»ÊÇÈËÐ´µÄ
			resultUser.setUserId(rs.getInt("userId"));
			resultUser.setUserName(rs.getString("userName"));
			resultUser.setPassword(rs.getString("password"));
			resultUser.setUserIdCard(rs.getInt("userIdCard"));
			resultUser.setUserRealName(rs.getString("userRealName"));
			resultUser.setSex(rs.getString("sex"));
			resultUser.setAge(rs.getInt("age"));
			resultUser.setUserTel(rs.getString("userTel"));
			resultUser.setAddress(rs.getString("address"));
			resultUser.setGroup(rs.getInt("group"));

			// ÉèÖÃÈÕÆÚ
			Timestamp ts = rs.getTimestamp("addTime");
			if (ts != null) {
				java.util.Date date = new java.util.Date(ts.getTime());
				resultUser.setAddTime(date);
				resultUser.setAddTime_();
			}

			resultUser.setSum(rs.getDouble("sum"));
		}

		return resultUser;
	}
	
	/**
	 * ¸ù¾ÝuserId²éÑ¯user
	 * @param userId
	 * @return
	 * @throws SQLException 
	 */
	public User selectUser(int userId) throws SQLException{
		User resultUser = null;
		
		String sql = "select * from t_user where userId = "+ userId;
		PreparedStatement pstmt = conn.prepareStatement(sql,
				ResultSet.TYPE_FORWARD_ONLY, 
				ResultSet.CONCUR_READ_ONLY,
				ResultSet.CLOSE_CURSORS_AT_COMMIT);
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.next()){
			resultUser = new User();
			resultUser.setUserId(rs.getInt("userId"));
			resultUser.setUserName(rs.getString("userName"));
			resultUser.setPassword(rs.getString("password"));
			resultUser.setUserIdCard(rs.getInt("userIdCard"));
			resultUser.setUserRealName(rs.getString("userRealName"));
			resultUser.setSex(rs.getString("sex"));
			resultUser.setAge(rs.getInt("age"));
			resultUser.setUserTel(rs.getString("userTel"));
			resultUser.setAddress(rs.getString("address"));
			resultUser.setGroup(rs.getInt("group"));
			// ÉèÖÃÈÕÆÚ
			Timestamp ts = rs.getTimestamp("addTime");
			if (ts != null) {
				java.util.Date date = new java.util.Date(ts.getTime());
				resultUser.setAddTime(date);
				resultUser.setAddTime_();
			}
			resultUser.setSum(rs.getDouble("sum"));
		}
		
		return resultUser;
	}
	/**
	 * ²éÃû×Ö
	 * @param userId
	 * @return
	 * @throws SQLException 
	 */
	public String selectUserName(int userId) throws SQLException {
		String sql = "select userName from t_user where userId="+userId;
		PreparedStatement pstmt = conn.prepareStatement(sql,
				ResultSet.TYPE_FORWARD_ONLY, 
				ResultSet.CONCUR_READ_ONLY,
				ResultSet.CLOSE_CURSORS_AT_COMMIT);
		ResultSet rs = pstmt.executeQuery();
		String userName = null;
		if(rs.next()){
			userName = rs.getString(1);
		}
		return userName;
	}
	
	/**
	 * ×¢²áºó·µ»ØuserId,Ê§°Ü·µ»Ø0
	 * @param user
	 * @return
	 * @throws SQLException 
	 */
	public int selectUserId(User user) throws SQLException{
		String sql = "select userId from t_user where userName = ? and password = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql,
				ResultSet.TYPE_FORWARD_ONLY, 
				ResultSet.CONCUR_READ_ONLY,
				ResultSet.CLOSE_CURSORS_AT_COMMIT);
		pstmt.setString(1, user.getUserName());
		pstmt.setString(2, user.getPassword());
		
		ResultSet rs = pstmt.executeQuery();
		
		int userId = 0;
		if(rs.next()){
			userId = rs.getInt("userId");
		}
		return userId;
	}
	/**
	 * ²éÑ¯Ñ§ºÅ
	 * @param userId
	 * @return
	 * @throws SQLException
	 */
	public int selectUserIdCard(int userId) throws SQLException{
		String sql = "select userIdCard from t_user where userId = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql, 
				ResultSet.TYPE_FORWARD_ONLY, 
				ResultSet.CONCUR_READ_ONLY, 
				ResultSet.CLOSE_CURSORS_AT_COMMIT);
		pstmt.setInt(1, userId);
		
		ResultSet rs = pstmt.executeQuery();
		
		int userIdCard = 0;
		if(rs.next()){
			userIdCard = rs.getInt(1);
		}
		return userIdCard;
	}
	
	
	
	
	
	
	
	
	/**
	 * ¸üÐÂÓÃ»§£¬³É¹¦·µ»Ø1
	 * 
	 * ÕâÀïÓÐ¸öÎÊÌâ£¬¸Ã·½·¨ÊÇÓÉË­µ÷ÓÃµÄ¡£ÒòÎªÓÐÐ©²ÎÊýÊÇÓÃ»§²»¿É¼ûµÄ¡£ ÄÇÃ´£¬Õâ¸ö·½·¨ÓÉÓÃ»§µ÷ÓÃ£¬ÐÞ¸ÄÓÃ»§¿É¼ûÊôÐÔ¡£
	 * 
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public int updateUser(User user) throws SQLException {
		String sql = "update t_user set userName = ?, password = ?, userIdCard = ?, "
				+ "userRealName = ?, sex = ?, age = ?, userTel = ?, address = ? " + "where userId = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, user.getUserName());
		pstmt.setString(2, user.getPassword());
		pstmt.setInt(3, user.getUserIdCard());
		pstmt.setString(4, user.getUserRealName());
		pstmt.setString(5, user.getSex());
		pstmt.setInt(6, user.getAge());
		pstmt.setString(7, user.getUserTel());
		pstmt.setString(8, user.getAddress());
		pstmt.setInt(9, user.getUserId());

		int row = pstmt.executeUpdate();
		return row;
	}

	/**
	 * ¸üÐÂÓÃ»§×é£¬³É¹¦·µ»Ø1
	 * 
	 * ¸Ã·½·¨ÓÉÉÌ¼Òµ÷ÓÃ¡£
	 * 
	 * @param group
	 * @return
	 * @throws SQLException
	 */
	public int updateUser(User user, int group) throws SQLException {
		String sql = "update t_user set group = ? where userId = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, group);
		pstmt.setInt(2, user.getUserId());

		int row = pstmt.executeUpdate();
		return row;
	}

	/**
	 * ¸üÐÂÓÃ»§×ÜÏû·Ñ£¬¶©µ¥ÊÕ»õÊ±×Ô¶¯µ÷ÓÃ£¬³É¹¦·µ»Ø1
	 * 
	 * @param user
	 * @param sum
	 *            ¸Ã²ÎÊýÊÇ¶©µ¥µÄ×Ü¼Û¸ñ
	 * @return
	 * @throws SQLException
	 */
	public int updateUser(int userId, double sum) throws SQLException {
		String sql = "update t_user set sum=sum+?  where userId = ?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setDouble(1, sum);
		pstmt.setInt(2, userId);

		return pstmt.executeUpdate();
	}
}
