package com.cms.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.List;

import com.cms.dao.StaffDao;
import com.cms.model.Page;
import com.cms.model.Staff;

import dbutil.DBUtil;

public class StaffDaoImpl implements StaffDao {
private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
	@Override
	public boolean addStaff(Staff staff) {
		   try {
			conn=DBUtil.getConnection();
			String sql="insert into s_staff(id,jobnum,s_name,s_gender,s_birthday,s_place,s_nation,s_marriage,s_blood) values (?,?,?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,staff.getId());
			ps.setString(2,staff.getJobnum());
			ps.setString(3,staff.getS_name());
			ps.setInt(4,staff.getS_gender());
			ps.setString(5,staff.getS_birthday());
			ps.setString(6,staff.getS_place());
			ps.setString(7,staff.getS_nation());
			ps.setString(8,staff.getS_marriage());
			ps.setString(9,staff.getS_blood());
			int result=ps.executeUpdate();
			return result>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean updateStaff(Staff staff) {
		try {
			conn=DBUtil.getConnection();
			String sql="update s_staff set id=?,jobnum=?,s_name=?,s_gender=?,s_birthday=?,s_place=?,s_nation=?,s_marriage=?,s_blood=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,staff.getId());
			ps.setString(2,staff.getJobnum());
			ps.setString(3,staff.getS_name());
			ps.setInt(4,staff.getS_gender());
			ps.setString(5,staff.getS_birthday());
			ps.setString(6,staff.getS_place());
			ps.setString(7,staff.getS_nation());
			ps.setString(8,staff.getS_marriage());
			ps.setString(9,staff.getS_blood());
			int result=ps.executeUpdate();
			return result>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean deleteStaff(int id) {
		try {
			conn=DBUtil.getConnection();
			String sql="delete from s_staff where id=?";
			ps=conn.prepareStatement(sql);
			int result=ps.executeUpdate();
			return result>0?true:false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public List<Staff> selectAllStaff(Page page) {
		List<Staff> staffs=new ArrayList<Staff>();
		try {
			conn=DBUtil.getConnection();
			String sql="select * from s_staff limit ?,?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,page.getStartIndex());
			ps.setInt(2,page.getCurrentPage());
			rs=ps.executeQuery();
			while (rs.next()) {
				Staff staff=new Staff();
				staff.setId(rs.getInt(1));
				staff.setJobnum(rs.getString(2));
			staff.setS_name(rs.getString(3));
				staff.setS_gender(rs.getInt(4));
				staff.setS_birthday(rs.getString(5));
				staff.setS_place(rs.getString(6));
				staff.setS_nation(rs.getString(7));
				staff.setS_marriage(rs.getString(8));
				staff.setS_blood(rs.getString(9));
				staffs.add(staff);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return staffs;
	}

	@Override
	public Staff getStaff(int id) {
		Staff staff=new Staff();
		try {
			conn=DBUtil.getConnection();
			String sql="select * from s_staff where id =?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			if (rs.next()) {
				staff.setId(rs.getInt(1));
				staff.setJobnum(rs.getString(2));
			staff.setS_name(rs.getString(3));
				staff.setS_gender(rs.getInt(4));
				staff.setS_birthday(rs.getString(5));
				staff.setS_place(rs.getString(6));
				staff.setS_nation(rs.getString(7));
				staff.setS_marriage(rs.getString(8));
				staff.setS_blood(rs.getString(9));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return staff;
	}

	@Override
	public int count() {
		int count=0;
		try {
			conn=DBUtil.getConnection();
			String sql="select count(1) from s_staff";
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

}