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.Admin;
import com.nitpro.school.bean.Emp;
import com.nitpro.school.bean.User;
import com.nitpro.school.util.ConnectionPoolHelper;

public class EmpDao {
    public List<Emp> getEmp(){
    	List<Emp> list = new ArrayList<Emp>();
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT empid, name, sex, birthday, job, loginName, pwd, isLock FROM emp";
			Statement state = conn.createStatement();
			ResultSet rs = state.executeQuery(sql);
			while(rs.next()){
				Emp emp = new Emp();
				emp.setEmpId(rs.getInt("empid"));
				emp.setName(rs.getString("name"));
				emp.setSex(rs.getString("sex"));
				emp.setBirthday(rs.getDate("birthday"));
				emp.setJob(rs.getString("job"));
				emp.setLoginName(rs.getString("loginName"));
				emp.setPwd(rs.getString("pwd"));
				emp.setIsLock(rs.getString("isLock"));
				list.add(emp);
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return list;
    }
    
    public Emp getEmpById(int empId){
    	Emp emp = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT empid, name, sex, birthday, job, loginName, pwd, isLock FROM emp WHERE empid = ?";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setInt(1, empId);
			ResultSet rs = state.executeQuery();
			while(rs.next()){
				emp = new Emp();
				emp.setEmpId(rs.getInt("empid"));
				emp.setName(rs.getString("name"));
				emp.setSex(rs.getString("sex"));
				emp.setBirthday(rs.getDate("birthday"));
				emp.setJob(rs.getString("job"));
				emp.setLoginName(rs.getString("loginName"));
				emp.setPwd(rs.getString("pwd"));
				emp.setIsLock(rs.getString("isLock"));
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return emp;
    }

    public Emp getEmp(User user){
    	Emp emp = null;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "SELECT empid, name, sex, birthday, job, loginName, pwd, isLock FROM emp 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()){
				emp = new Emp();
				emp.setEmpId(rs.getInt("empid"));
				emp.setName(rs.getString("name"));
				emp.setSex(rs.getString("sex"));
				emp.setBirthday(rs.getDate("birthday"));
				emp.setJob(rs.getString("job"));
				emp.setLoginName(rs.getString("loginName"));
				emp.setPwd(rs.getString("pwd"));
				emp.setIsLock(rs.getString("isLock"));
			}
			ConnectionPoolHelper.closeResultSet(rs);
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return emp;
    }
    
    
    public int insertEmp(Emp emp){
    	int result = 0;
    	try {
			Connection conn = ConnectionPoolHelper.getConnection();
			String sql = "INSERT INTO emp(name, sex, birthday, job, loginName, pwd, isLock) VALUES(?, ?, ?, ?, ?, ?, ?)";
			PreparedStatement state = conn.prepareStatement(sql);
			state.setString(1, emp.getName());
			state.setString(2, emp.getSex());
			state.setDate(3, emp.getBirthday()!=null ? new java.sql.Date(emp.getBirthday().getTime()) : null);
			state.setString(4, emp.getJob());
			state.setString(5, emp.getLoginName());
			state.setString(6, emp.getPwd());
			state.setString(7, emp.getIsLock());
			result = state.executeUpdate();
			ConnectionPoolHelper.closeStatement(state);
			ConnectionPoolHelper.closeConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
    	return result;
    }

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

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