package bean;

import java.sql.*;
import java.util.*;

public class HRM {

	private Connection conn;

	private Statement stmt;

	public HRM() throws Exception {
		conn = DriverManager.getConnection(
				"jdbc:sqlserver://localhost;database=hospital", "sa", "1");
		conn.setAutoCommit(false);
		stmt = conn.createStatement();
		System.out.println("建立連接成功!");
	}

	public Collection getStaff() throws Exception {
		ArrayList ret = new ArrayList();
		ResultSet rst = stmt.executeQuery("SELECT* FROM Staff");
		while (rst.next()) {
			Staff staff = new Staff(rst.getString(2), rst.getString(3), rst
					.getString(4), rst.getString(5), rst.getString(6), rst
					.getString(7), rst.getString(8), rst.getString(9), rst
					.getString(10), rst.getString(11), rst.getString(12), rst
					.getInt(13), rst.getString(14),rst.getFloat(15),rst.getString(16));
			staff.setStaffId((new Integer(rst.getInt(1)).toString()));
			ret.add(staff);
		}
		rst.close();
		conn.commit();
		return ret;
	}

	// 使用了
	public ArrayList getStaffInWard(String wardid) throws SQLException {
		ArrayList ret = new ArrayList();
		ResultSet rst = stmt.executeQuery("SELECT* FROM Staff where wardid='"+wardid+"'");
		while (rst.next()) {
			Staff staff = new Staff(rst.getString(2), rst.getString(3), rst
					.getString(4), rst.getString(5), rst.getString(6), rst
					.getString(7), rst.getString(8), rst.getString(9), rst
					.getString(10), rst.getString(11), rst.getString(12), rst
					.getInt(13), rst.getString(14),rst.getFloat(15),rst.getString(16));
			staff.setStaffId((new Integer(rst.getInt(1)).toString()));
			ret.add(staff);
		}
		rst.close();
		conn.commit();
		return ret;
	}

	public Staff getStaffInfo(String staffId) throws SQLException {
		ResultSet ret = stmt
				.executeQuery("SELECT* FROM Staff where staffId = '" + staffId
						+ "'");
		Staff staff = null;
		if (ret.next()) {
			staff = new Staff(ret.getString(2), ret.getString(3), ret
					.getString(4), ret.getString(5), ret.getString(6), ret
					.getString(7), ret.getString(8), ret.getString(9), ret
					.getString(10), ret.getString(11), ret.getString(12), ret
					.getInt(13), ret.getString(14),ret.getFloat(15),ret.getString(16));
			staff.setStaffId((new Integer(ret.getInt(1)).toString()));
		}
		conn.commit();
		return staff;
	}

	public boolean addStaff(Staff staff) throws SQLException {
		
		System.out.print(staff.getWardid());

			stmt.execute("INSERT INTO Staff VALUES('" + staff.getPassword()
					+ "','" + staff.getF_name() + "','" + staff.getL_name()
					+ "','" + staff.getAddress() + "','" + staff.getTele()
					+ "','" + staff.getBirth() + "','" + staff.getSex() + "','"
					+ staff.getMIN() + "','" + staff.getPosition() + "','"
					+ staff.getSalary_type() + "','" + staff.getContract_type()
					+ "'," + staff.getHour() + ",'000'," + staff.getSalary() + ",'" + staff.getPaidType()+ "')");
			stmt.close();
		conn.commit();
		return true;

	}

	public boolean deleteStaff(int staffId) throws SQLException {

		try {

			stmt.execute("DELETE FROM staff WHERE staffId = " + staffId + "");
		} catch (SQLException e) {
			e.printStackTrace();
			conn.rollback();
			return false;
		}
		conn.commit();
		return true;

	}

	public boolean editStaff(String staffId, Staff staff) throws SQLException {

		try {

			stmt.execute("UPDATE staff SET " + "f_name ='" + staff.getF_name()
					+ "',l_name ='" + staff.getL_name() + "',address ='"
					+ staff.getAddress() + "',tele ='" + staff.getTele()
					+ "',birth ='" + staff.getBirth() + "',sex ='"
					+ staff.getSex() + "',NIN ='" + staff.getMIN()
					+ "',position ='" + staff.getPosition()
					+ "',salary_type ='" + staff.getSalary_type()
					+ "',contract_type ='" + staff.getContract_type()
					+ "',hours =" + staff.getHour() + ",position ="
					+ staff.getWardid() + "");

		} catch (SQLException e) {
			conn.rollback();
			return false;
		}
		conn.commit();
		return true;

	}

	public Collection getExperience(String staffId) throws Exception {
		Collection ret = new ArrayList();

		ResultSet rst = stmt
				.executeQuery("SELECT* FROM experience where staffId='"
						+ staffId + "'");
		while (rst.next()) {
			ret.add(new Experience(rst.getInt(1), rst.getInt(2), rst
					.getString(1), rst.getString(2), rst.getString(3)));
		}
		rst.close();
		conn.commit();
		return ret;
	}

	public boolean addExperience(Experience experience) throws SQLException {

		try {

			stmt.execute("INSERT INTO experience VALUES('"
					+ experience.getStaffid() + "','"
					+ experience.getExperNum() + "','"
					+ experience.getOrganization() + "','"
					+ experience.getStart_date() + "','"
					+ experience.getFinish_date() + "'");

		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
			conn.rollback();
			return false;
		}
		conn.commit();
		return true;

	}

	public Collection getQualification(String staffId) throws Exception {
		Collection ret = new ArrayList();

		ResultSet rst = stmt
				.executeQuery("SELECT* FROM qualification where staffId= '"
						+ staffId + "'");
		while (rst.next()) {
			ret.add(new Experience(rst.getInt(1), rst.getInt(2), rst
					.getString(1), rst.getString(2), rst.getString(3)));
		}
		rst.close();
		conn.commit();
		return ret;
	}

	public boolean addQualification(Qualification qualification)
			throws SQLException {

		try {

			stmt.execute("INSERT INTO qualification VALUES('"
					+ qualification.getStaffId() + "','"
					+ qualification.getType() + "','" + qualification.getDate()
					+ "','" + qualification.getInstitution() + "'");
		} catch (SQLException e) {
			// TODO 自動生成 catch 塊
			e.printStackTrace();
			conn.rollback();
			return false;
		}
		conn.commit();
		return true;

	}
	
	
	public ArrayList getStaffofEx(String ex) throws SQLException{
		ResultSet rst = stmt.executeQuery("select * from staff,experience where organization='"+ex+"' and staff.staffid = experience.staffid");
		ArrayList al = new ArrayList();
		while(rst.next()){
			Staff staff = new Staff(rst.getString(2), rst.getString(3), rst
					.getString(4), rst.getString(5), rst.getString(6), rst
					.getString(7), rst.getString(8), rst.getString(9), rst
					.getString(10), rst.getString(11), rst.getString(12), rst
					.getInt(13), rst.getString(14),rst.getFloat(15),rst.getString(16));
			staff.setStaffId((new Integer(rst.getInt(1)).toString()));
			al.add(staff);
			
		}
		rst.close();
		conn.commit();
		return al;	
	}
	
	
	public ArrayList getStaffofQu(String type,String institu) throws SQLException{
		ResultSet rst = stmt.executeQuery("select * from staff,qualification where type='"+type+"' and institution='"+institu+"' and staff.staffid = qualification.staffid");
		ArrayList al = new ArrayList();
		while(rst.next()){
			Staff staff = new Staff(rst.getString(2), rst.getString(3), rst
					.getString(4), rst.getString(5), rst.getString(6), rst
					.getString(7), rst.getString(8), rst.getString(9), rst
					.getString(10), rst.getString(11), rst.getString(12), rst
					.getInt(13), rst.getString(14),rst.getFloat(15),rst.getString(16));
			staff.setStaffId((new Integer(rst.getInt(1)).toString()));
			al.add(staff);
			
		}
		rst.close();
		conn.commit();
		return al;
		
		
	}

	public void destroy() throws SQLException {
		stmt.close();
		conn.close();
	}
}