package com.mitsui.dao;

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.mitsui.domain.Alter;
import com.mitsui.servlet.AlterInputServlet;
import com.mitsui.util.ConnectionUtil;

public class AlterDao {
	public boolean addAlter(Alter alter){
		String sql = "insert into staffalter(alter_id,p_id,p_name,alter_time,alter_bstate,alter_estate,alter_type,alter_content) values(?,?,?,?,?,?,?,?)";
		Object[] params = {
				alter.getAlter_id(),
				alter.getP_id(),
				alter.getP_name(),
				alter.getAlter_time(),
				alter.getAlter_bstate(),
				alter.getAlter_estate(),
				alter.getAlter_type(),
				alter.getAlter_content()
				
		};
		return ConnectionUtil.executeUpdate(sql, params);
	}
	public Alter queryAlter(Alter alter){
		String sql = "select * from alter where alter_id = ?";
		Connection conn = ConnectionUtil.getConection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, alter.getAlter_id());
			ResultSet rs = ps.executeQuery();
			Alter u = null;
			if(rs.next()){
				
				Integer alter_id = rs.getInt("alter_id");
				Integer p_id = rs.getInt("p_id");
				String p_name = rs.getString("p_name");
				String alter_time = rs.getString("alter_time");
				String alter_bstate = rs.getString("alter_bstate");
				String alter_estate = rs.getString("alter_estate");
				String alter_type = rs.getString("alter_type");
				String alter_content = rs.getString("alter_content");
				
				u = new Alter(alter_id, p_id, p_name, alter_time, alter_bstate, alter_estate, alter_type,alter_content);
			}
			if(u.getAlter_id().equals(alter.getAlter_id())){
				return u;
			}else{
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			
			return null;
		}
	}
	public List<Alter> queryAllAlter(){
		String sql = "select * from staffalter";
		Connection conn = ConnectionUtil.getConection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			Alter u = null;
			List<Alter> Alters = new ArrayList<Alter>();
			while(rs.next()){
				Integer alter_id = rs.getInt("alter_id");
				Integer p_id = rs.getInt("p_id");
				String p_name = rs.getString("p_name");
				String alter_time = rs.getString("alter_time");
				String alter_bstate = rs.getString("alter_bstate");
				String alter_estate = rs.getString("alter_estate");
				String alter_type = rs.getString("alter_type");
				String alter_content = rs.getString("alter_content");
				u = new Alter(alter_id, p_id, p_name, alter_time, alter_bstate, alter_estate, alter_type, alter_content);
				Alters.add(u);
			}
			return Alters;
		} catch (SQLException e) {
			

			
			e.printStackTrace();
			return null;
		}
	}
	public boolean deleteAlter(Integer alter_id){
		String sql = "delete from staffalter where alter_id = ?";
		Object[] params = {
				alter_id
		};
		return ConnectionUtil.executeUpdate(sql, params);
	}
	public Alter queryAlter(Integer alter_id){
		
		String sql = "select * from staffalter where alter_id = ?";
		Connection conn = ConnectionUtil.getConection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, alter_id);
			ResultSet rs = ps.executeQuery();
			Alter u = null;
			if(rs.next()){
				Integer alter_id1 = rs.getInt("alter_id");
				Integer p_id = rs.getInt("p_id");
				String p_name = rs.getString("p_name");
				String alter_time = rs.getString("alter_time");
				String alter_bstate = rs.getString("alter_bstate");
				String alter_estate = rs.getString("alter_estate");
				String alter_type = rs.getString("alter_type");
				String alter_content = rs.getString("alter_content");
				u = new Alter(alter_id1, p_id, p_name, alter_time, alter_bstate, alter_estate, alter_type, alter_content);;
			}
			return u;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean updateAlter(Alter alter){
		try{
		String sql = "update staffalter set p_id = ?,p_name = ?,alter_time = ?,alter_bstate = ?,alter_estate = ? ,alter_type = ?,alter_content = ?where  alter_id = ?";
		Object[] params = {
				
				alter.getP_id(),
				alter.getP_name(),
				alter.getAlter_time(),
				alter.getAlter_bstate(),
				alter.getAlter_estate(),
				alter.getAlter_type(),
				alter.getAlter_content(),
				alter.getAlter_id()
		};
		return ConnectionUtil.executeUpdate(sql, params);
		}catch(Exception e){

			
			
			e.printStackTrace();
			
		}
		return false;
	}
	public List<Alter> queryOneAlter(int id) {
		String sql = "select * from staffalter where p_id = "+ id +"";
		Connection conn = ConnectionUtil.getConection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			Alter u = null;
			List<Alter> Alters = new ArrayList<Alter>();
			while(rs.next()){
				Integer alter_id = rs.getInt("alter_id");
				Integer p_id = rs.getInt("p_id");
				String p_name = rs.getString("p_name");
				String alter_time = rs.getString("alter_time");
				String alter_bstate = rs.getString("alter_bstate");
				String alter_estate = rs.getString("alter_estate");
				String alter_type = rs.getString("alter_type");
				String alter_content = rs.getString("alter_content");
				u = new Alter(alter_id, p_id, p_name, alter_time, alter_bstate, alter_estate, alter_type, alter_content);
				Alters.add(u);
			}
			return Alters;
		} catch (SQLException e) {
			

			
			e.printStackTrace();
			return null;
		}
	}
	
}