package com.nitpro.school.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.nitpro.school.bean.Teacher;
import com.nitpro.school.bean.User;
import com.nitpro.school.util.ConnectionPoolHelper;

public class TeacherDao {
    public List<Teacher> getTeacher(){
    	List<Teacher> list = new ArrayList<Teacher>();
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT teacherid, name, sex, birthday, job ,loginname, pwd, islock FROM teacher";
			Statement state = conn.createStatement();
			ResultSet rs = state.executeQuery(sql);
			while(rs.next()){
				Teacher teacher = new Teacher();
				teacher.setTeacherId(rs.getInt("teacherid"));
				teacher.setName(rs.getString("name"));
				teacher.setSex(rs.getString("sex"));
				teacher.setBirthday(rs.getDate("birthday"));
				teacher.setJob(rs.getString("job"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setPwd(rs.getString("pwd"));
				teacher.setIsLock(rs.getString("isLock"));
				list.add(teacher);
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return list;
    }
    
    public Teacher getTeacherById(int teacherId){
    	Teacher teacher = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT teacherid, name, sex, birthday, job ,loginname, pwd, islock FROM teacher WHERE teacherid = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, teacherId);
			ResultSet rs = state.executeQuery();
			while(rs.next()){
				teacher = new Teacher();
				teacher.setTeacherId(rs.getInt("teacherid"));
				teacher.setName(rs.getString("name"));
				teacher.setSex(rs.getString("sex"));
				teacher.setBirthday(rs.getDate("birthday"));
				teacher.setJob(rs.getString("job"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setPwd(rs.getString("pwd"));
				teacher.setIsLock(rs.getString("isLock"));
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return teacher;
    }

    public Teacher getTeacher(User user){
    	Teacher teacher = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT teacherid, name, sex, birthday, job ,loginname, pwd, islock FROM teacher WHERE loginname = ? AND pwd = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setString(1, user.getUserName());
			state.setString(2, user.getPwd());
			ResultSet rs = state.executeQuery();
			while(rs.next()){
				teacher = new Teacher();
				teacher.setTeacherId(rs.getInt("teacherid"));
				teacher.setName(rs.getString("name"));
				teacher.setSex(rs.getString("sex"));
				teacher.setBirthday(rs.getDate("birthday"));
				teacher.setJob(rs.getString("job"));
				teacher.setLoginName(rs.getString("loginname"));
				teacher.setPwd(rs.getString("pwd"));
				teacher.setIsLock(rs.getString("isLock"));
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return teacher;
    }
    
    
    public int insertTeacher(Teacher teacher){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "INSERT INTO teacher(name, sex, birthday, job ,loginname, pwd, islock) VALUES(?, ?, ?, ?, ?, ?, ?)";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setString(1, teacher.getName());
			state.setString(2, teacher.getSex());
			state.setDate(3, teacher.getBirthday()!=null ? new java.sql.Date(teacher.getBirthday().getTime()) : null);
			state.setString(4, teacher.getJob());
			state.setString(5, teacher.getLoginName());
			state.setString(6, teacher.getPwd());
			state.setString(7, teacher.getIsLock());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

    public int updateTeacher(Teacher teacher){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "UPDATE teacher SET name = ?, sex = ?, birthday = ?, job = ?, loginname = ?, pwd = ?, isLock = ? WHERE teacherid = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setString(1, teacher.getName());
			state.setString(2, teacher.getSex());
			state.setDate(3, teacher.getBirthday()!=null ? new java.sql.Date(teacher.getBirthday().getTime()) : null);
			state.setString(4, teacher.getJob());
			state.setString(5, teacher.getLoginName());
			state.setString(6, teacher.getPwd());
			state.setString(7, teacher.getIsLock());
			state.setInt(8, teacher.getTeacherId());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

    public int changePwd(Teacher teacher){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "UPDATE teacher SET pwd = ? WHERE teacherid = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setString(1, teacher.getPwd());
			state.setInt(2, teacher.getTeacherId());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }
    
}