package cn.hmsoft.ltc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import cn.hmsoft.ltc.common.util.DateUtil;
import cn.hmsoft.ltc.common.util.DbUtil;
import cn.hmsoft.ltc.model.LoginRequest;
import cn.hmsoft.ltc.model.Users;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

/**
 * 用户信息
 * @author Administrator
 *
 */
public class UsersDao {

	/**
	 * 用户信息注册
	 * @param user
	 */
	public void insertUser(Users user) {
		DbUtil util = new DbUtil();
		util.insert("insert into users(schoolName,courseId,teacherName,phone,userName,passwd,titleName,userRole,createDate,certNo) "
				+ "values('" + user.getSchoolName() + "', "
					   + "'" + user.getCourseId() + "', "
					   + "'" + user.getTeacherName() + "', "
					   + "'" + user.getPhone() + "', "
					   + "'" + user.getUserName() + "', "
				       + "'" + user.getPasswd() + "', "
				       + "'" + user.getTitleName() + "', "
				       + user.getUserRole() + ", "
				       +  "'" + DateUtil.formatToString(new Date()) + "', "
				       + "'" + user.getCertNo() + "')");
	}
	
	/**
	 * 校验用户名和密码码
	 * @param userName
	 * @param passwd
	 * @return
	 * @throws SQLException
	 */
	public LoginRequest checkUserName(String userName,String passwd) throws SQLException {
		LoginRequest loginResp = new LoginRequest();
		loginResp.setStatus(-1);
		DbUtil util = new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select userName,schoolName,userId,userRole,courseId,firstLogin from users where userName = '" + userName + "' and passwd= '" + passwd + "'").toString();
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			loginResp.setStatus(0); //成功
			loginResp.setUserName(rs.getString(1));
			loginResp.setSchoolName(rs.getString(2));
			loginResp.setUserId(rs.getInt(3));
			loginResp.setUserRole(rs.getInt(4));
			loginResp.setCourseId(rs.getInt(5));
			loginResp.setFirstLogin(rs.getInt(6));
			return loginResp;
		}
		return loginResp;
	}
	
	/**
	 *  注册时校验用户名是否存在
	 * @param userName
	 * @return true 则说明该用户不存在，false则说明该用户存在
	 * @throws SQLException
	 */
	public boolean checkName(String userName) throws SQLException {
		boolean flag = true;
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select count(*) from users where userName = '"+userName+"' ").toString();
		ResultSet rs = util.query(strSql.toString());
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
			System.out.println("count:" + rs.getInt(1));
		}
		if(count>0) {
			flag = false;
		}
		return flag;
	}
	
	/**
	 *   通过用户名修改用户密码密码
	 * @param userName  用户名
	 * @param userPwd    密码、
	 * @return true 成功，false 失败
	 */
	public boolean updateUserPwd(String userName,String userPwd) {
		boolean flag = false;
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("update users set passwd='"+userPwd+"'  where userName = '"+userName+"' ").toString();
		int count = util.update(strSql.toString());
		if(count>0) {
			flag = true; //成功
		}
		return flag;
	}
	
	/**
	 *  
	 * @param userId  用户ID
	 */
	public boolean updateUserFirstLogin(Integer userId) {
		boolean flag = false;
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("update users set firstLogin=1  where userId = " + userId + " ").toString();
		int count = util.update(strSql.toString());
		if(count>0) {
			flag = true; //成功
		}
		return flag;
	}
	
	/**
	 * 查询用户id by userName
	 * 
	 * @return
	 * @throws SQLException
	 */
	public Integer selectUsersByUserName(String userName) throws SQLException {
		DbUtil util = new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select userId from users where userName = '"+userName+"' ").toString();
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			int id  = rs.getInt(1);   //用户主健
			return id;
		}
		return null;
	}
	
	/**
	 * 查询用户信息 by Id
	 * @return
	 * @throws SQLException
	 */
	public Users selectUserById(Integer id) throws SQLException {
		DbUtil util = new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select userId,schoolName,teacherName,phone,userName,titleName,certNo,courseId,userRole from users where userid = " + id).toString();
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			Users user = new Users();
			user.setUserId(rs.getInt(1));
			user.setSchoolName(rs.getString(2));
			user.setTeacherName(rs.getString(3));
			user.setPhone(rs.getString(4));
			user.setUserName(rs.getString(5));
			user.setTitleName(rs.getString(6));
			user.setCertNo(rs.getString(7));
			user.setCourseId(rs.getInt(8));
			user.setUserRole(rs.getInt(9));
			return user;
		}
		return null;
	}
	
	public ObservableList<Users> listUsers() throws SQLException {
		ObservableList<Users> userList = FXCollections.observableArrayList();
		String sql = "select userName,passwd,userId,userRole,phone from users";
		DbUtil util = new DbUtil();
		ResultSet rs = util.query(sql);
		while (rs.next()) {
			Users u = new Users();
			u.setUserName(rs.getString(1));
			u.setPasswd(rs.getString(2));
			u.setUserId(rs.getInt(3));
			u.setUserRole(rs.getInt(4));
			u.setPhone(rs.getString(5));
			userList.add(u);
		}
		return userList;
	}
	
	/**
	 * 查询用户信息 by courseCode
	 * @return
	 * @throws SQLException
	 */
	public Users selectUserByCourseCode(String courseCode) throws SQLException {
		DbUtil util = new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select users.userId as userId,users.schoolName,users.teacherName,"
				+ "users.phone,users.userName,users.titleName,users.certNo,"
				+ "users.courseId,users.userRole,users.createDate,"
				+ "course.teachBook from users ,course where users.courseId = course.id and course.code =  '" + courseCode+"'").toString();
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			Users user = new Users();
			user.setUserId(rs.getInt(1));
			user.setSchoolName(rs.getString(2));
			user.setTeacherName(rs.getString(3));
			user.setPhone(rs.getString(4));
			user.setUserName(rs.getString(5));
			user.setTitleName(rs.getString(6));
			user.setCertNo(rs.getString(7));
			user.setCourseId(rs.getInt(8));
			user.setUserRole(rs.getInt(9));
			user.setCreateDate(rs.getString(10));
			user.setTeachBook(rs.getString(11));
			return user;
		}
		return null;
	}
}
