/**
 * 
 */
package com.permission.dao.impl;

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.permission.dao.IUserInfoDAO;
import com.permission.sys.BaseDAO;
import com.permission.sys.PageRoll;
import com.permission.utils.common.PermissionException;
import com.permission.utils.db.DBUtil;
import com.permission.vo.UserInfo;

/**
 * @author Jack
 * 
 */
public class UserInfoDAOImpl extends BaseDAO implements IUserInfoDAO {

	@Override
	public void add(UserInfo user) throws PermissionException {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "insert into user(Username,Password,email,sex,createDate,lastLoginDate,photo,telephone,birthDate,address,post,status) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getSex());
			ps.setString(5, user.getCreateDate());
			ps.setString(6, user.getLastLoginDate());
			ps.setString(7, user.getPhoto());
			ps.setString(8, user.getTelephone());
			ps.setString(9, user.getBirthDate());
			ps.setString(10, user.getAddress());
			ps.setString(11, user.getPost());
			ps.setString(12, user.getStatus());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PermissionException("添加用户失败");
		} finally {
			DBUtil.close(null, ps, conn);
		}
	}

	@Override
	public void delete(Integer userid) throws PermissionException {
		String sql = "delete from user where userid=" + userid;
		try {
			executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PermissionException("删除用户失败");
		}
	}

	@Override
	public UserInfo findByUserID(Integer userid) throws PermissionException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		UserInfo user = null;
		String sql = "select u.UserID,u.Username,u.Password,u.email,u.sex,u.createDate,u.lastLoginDate,u.photo,u.telephone,u.birthDate,u.address,u.post,u.status from user u where u.UserID=?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new UserInfo();
				user.setUserID(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setSex(rs.getString(5));
				user.setCreateDate(rs.getString(6));
				user.setLastLoginDate(rs.getString(7));
				user.setPhoto(rs.getString(8));
				user.setTelephone(rs.getString(9));
				user.setBirthDate(rs.getString(10));
				user.setAddress(rs.getString(11));
				user.setPost(rs.getString(12));
				user.setStatus(rs.getString(13));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PermissionException("查询用户信息失败");
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return user;
	}

	@Override
	public List<UserInfo> list(PageRoll pageRoll) throws PermissionException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<UserInfo> list = new ArrayList<UserInfo>();
		String sql = "select u.UserID,u.Username,u.Password,u.email,u.sex,u.createDate,u.lastLoginDate,u.photo,u.telephone,u.birthDate,u.address,u.post,u.status from user u limit ?,?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (pageRoll.getCurrPage() - 1) * pageRoll.getPageSize());
			ps.setInt(2, pageRoll.getPageSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				UserInfo user = new UserInfo();
				user.setUserID(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setSex(rs.getString(5));
				user.setCreateDate(rs.getString(6));
				user.setLastLoginDate(rs.getString(7));
				user.setPhoto(rs.getString(8));
				user.setTelephone(rs.getString(9));
				user.setBirthDate(rs.getString(10));
				user.setAddress(rs.getString(11));
				user.setPost(rs.getString(12));
				user.setStatus(rs.getString(13));
				list.add(user);
			}
			sql = "select count(*) from user";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();
			pageRoll.setCountSize(rs.getInt(1));
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PermissionException("查询用户列表失败");
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public UserInfo login(String username, String password)
			throws PermissionException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		UserInfo user = null;
		String sql = "select u.UserID,u.Username,u.Password,u.email,u.sex,u.createDate,u.lastLoginDate,u.photo,u.telephone,u.birthDate,u.address,u.post,u.status from user u where u.Username=? and u.Password=?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			rs = ps.executeQuery();
			if (rs.next()) {
				user = new UserInfo();
				user.setUserID(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setSex(rs.getString(5));
				user.setCreateDate(rs.getString(6));
				user.setLastLoginDate(rs.getString(7));
				user.setPhoto(rs.getString(8));
				user.setTelephone(rs.getString(9));
				user.setBirthDate(rs.getString(10));
				user.setAddress(rs.getString(11));
				user.setPost(rs.getString(12));
				user.setStatus(rs.getString(13));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PermissionException("登录失败");
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return user;
	}

	@Override
	public void update(UserInfo user) throws PermissionException {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "update user set Username=?,Password=?,email=?,sex=?,createDate=?,lastLoginDate=?,photo=?,telephone=?,birthDate=?,address=?,post=?,status=? where UserID=?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getEmail());
			ps.setString(4, user.getSex());
			ps.setString(5, user.getCreateDate());
			ps.setString(6, user.getLastLoginDate());
			ps.setString(7, user.getPhoto());
			ps.setString(8, user.getTelephone());
			ps.setString(9, user.getBirthDate());
			ps.setString(10, user.getAddress());
			ps.setString(11, user.getPost());
			ps.setString(12, user.getStatus());
			ps.setInt(13, user.getUserID());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PermissionException("修改用户失败");
		} finally {
			DBUtil.close(null, ps, conn);
		}
	}

	@Override
	public Map<String, String> findFunctionCode(Integer userid)
			throws PermissionException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Map<String, String> map = new HashMap<String, String>();
		String sql = "select functionCode,functionName from userandrole uar ,roleandfunction raf,userfunction uf where uar.RoleID=raf.RoleID and raf.FunctionID=uf.FunctionID and uar.UserID=?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userid);
			rs = ps.executeQuery();
			while (rs.next()) {
				map.put(rs.getString(1), rs.getString(2));
			}
		} catch (SQLException e) {
			throw new PermissionException("查询用户权限失败");
		} finally {
			DBUtil.close(rs, ps, conn);
		}
		return map;
	}

	@Override
	public void update(Integer userid, String status)
			throws PermissionException {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "update user set status=? where UserID=?";
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, status);
			ps.setInt(2, userid);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PermissionException("修改用户状态失败");
		} finally {
			DBUtil.close(null, ps, conn);
		}
	}
	

}