package service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;


import link.DAO;


import entities.Room;

import exception.RoomException;


public class BusinessRoom {
	private static RoomException exceptions;

	/*
	 * 向數據庫中添加客房信息
	 */
	public void addRoom(Object obj) throws Exception {

		Room room = (Room) obj;
		DAO dao = new DAO();
		// 向數據庫中添加對象
		try {
			try {
				// 連接數據庫
				dao.connection();

				// 將該用戶信息寫入數據庫中
				PreparedStatement pstmt = dao
						.getConn()
						.prepareStatement(
								"insert jiudian..room(roomnum,station,khname,khsex,telnum,fax,dengjitime,ruzhutime,beizhu) values(?,?,?,?,?,?,?,?,?)");

				pstmt.setString(1, room.getRoomnum());
				pstmt.setString(2, room.getStation());
				pstmt.setString(3, room.getKhname());
				pstmt.setString(4, room.getKhsex());
				pstmt.setString(5, room.getTelnum());
				pstmt.setString(6, room.getFax());
				pstmt.setString(7, room.getDengjitime());
				pstmt.setString(8, room.getRuzhutime());
				pstmt.setString(9, room.getBeizhu());

				pstmt.execute();
			} finally {
				dao.close();
			}
		} catch (SQLWarning e) {

			throw exceptions;
		} catch (SQLException e) {
			e.getErrorCode();
			e.printStackTrace();

			throw new RoomException(exceptions.getAddNewAccountError());
		}

	}

	/*
	 * 按照客房號從數據庫中查詢客房信息 queryRoom()
	 */
	public ArrayList queryRoomBynum(String num) throws Exception {
		DAO dao = new DAO();
		ArrayList arrary = new ArrayList();
		try {
			try {
				dao.connection();
				String sql = "select * from jiudian..room where roomnum = '"
						+ num + "'";
				Statement stmt = dao.getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);

				while (rs.next()) {
					Room room = new Room();
					room.setRoomnum(rs.getString(1));
					room.setStation(rs.getString(2));
					room.setKhname(rs.getString(3));
					room.setKhsex(rs.getString(4));
					room.setTelnum(rs.getString(5));
					room.setFax(rs.getString(6));
					room.setDengjitime(rs.getString(7));
					room.setRuzhutime(rs.getString(8));
					room.setBeizhu(rs.getString(9));
					arrary.add(room);
				}
				return arrary;
			} finally {
				dao.close();
			}
		} catch (Exception e) {
			throw new RoomException(exceptions.isNotExist());
		}
	}

	/*
	 * 按照客房的狀態從數據庫中查尋客房的信息
	 */
	public ArrayList queryRoomBystation(String station) throws Exception {
		
		DAO dao = new DAO();
		ArrayList arrary = new ArrayList();
		try {
			try {
				dao.connection();
				String sql = "select * from jiudian..room where station = '"
						+ station + "'";
				Statement stmt = dao.getConn().createStatement();
				ResultSet rs = stmt.executeQuery(sql);

				while (rs.next()) {
					Room room = new Room();
					room.setRoomnum(rs.getString(1));
					room.setStation(rs.getString(2));
					room.setKhname(rs.getString(3));
					room.setKhsex(rs.getString(4));
					room.setTelnum(rs.getString(5));
					room.setFax(rs.getString(6));
					room.setDengjitime(rs.getString(7));
					room.setRuzhutime(rs.getString(8));
					room.setBeizhu(rs.getString(9));
					arrary.add(room);
				}
				return arrary;
			} finally {
				dao.close();
			}
		} catch (Exception e) {
			throw new RoomException(exceptions.isNotExist());
		}
	}
	/*
	 * 查詢所有客房信息
	 */
	public ArrayList queryAllroom() throws RoomException{
		DAO dao = new DAO();
		   ArrayList arrary = new ArrayList();		
			try{
				try{				
					dao.connection();
					String sql = "select * from jiudian..room " ;
					Statement stmt = dao.getConn().createStatement();
					ResultSet rs = stmt.executeQuery(sql);
					
					while(rs.next()){
						Room room = new Room();
						room.setRoomnum(rs.getString(1));
						room.setStation(rs.getString(2));
						room.setKhname(rs.getString(3));
						room.setKhsex(rs.getString(4));
						room.setTelnum(rs.getString(5));
						room.setFax(rs.getString(6));
						room.setDengjitime(rs.getString(7));
						room.setRuzhutime(rs.getString(8));
						room.setBeizhu(rs.getString(9));
						arrary.add(room);
						
					}
					return arrary;
				}
				finally{
					dao.close();
				}
			}
			catch(Exception e){
				throw new RoomException(exceptions.isNotExist());
			}
		
	}

	/*
	 * 按照 roomnum查詢客房的唯一id號
	 */
	public String queryId(String aaa) throws Exception {
		DAO dao = new DAO();
	   	String id = null;
	   	try{
	   		try{
	   			dao.connection();
	   			String sql = "select * from jiudian..room where roomnum = '" + aaa + "'";
	   			Statement stmt = dao.getConn().createStatement();
	   			ResultSet rs = stmt.executeQuery( sql );
	   			
	   			if(rs.next()){
	   				id = rs.getString("id");
	   			}
	   			else{
	   				throw new RoomException(exceptions.isNotExist());
	   			}				
	   		}
	   		finally{
	   			dao.close();
	   		}
	   	}
	   	catch(SQLException e){
	   		throw new RoomException(exceptions.getAccountInfo());
	   	}
	   	return id;

	}

	/*
	 * 修改客房信息
	 */
	public void updateRoom(Object obj, String roomnum) throws Exception {
		BusinessRoom bus = new BusinessRoom();
		String id =(String) bus.queryId(roomnum);
		Room room = (Room)obj;
		DAO dao = new DAO();
		try{
			try{                                                          
				dao.connection();                                       
				PreparedStatement pstmt = dao.getConn().prepareStatement("update jiudian..room set roomnum=(?), station=(?),khname=(?),khsex=(?),telnum=(?),fax=(?),dengjitime=(?),ruzhutime=(?) where id='"+id+"'");
				pstmt.setString(1, room.getRoomnum());
				pstmt.setString(2, room.getStation());
				pstmt.setString(3, room.getKhname());
				pstmt.setString(4, room.getKhsex());
				pstmt.setString(5, room.getTelnum());
				pstmt.setString(6, room.getFax());
				pstmt.setString(7, room.getDengjitime());
				pstmt.setString(8, room.getRuzhutime());
				pstmt.setString(9, room.getBeizhu());
				
				
				pstmt.executeUpdate();
			}
			finally{
				dao.close();
			}
		}
		catch( SQLException e){
			throw new RoomException( exceptions.getUpdateAccountError());
		}
		

	}

	/*
	 * 刪除客房信息
	 */
	public void deleteRoom(String num) throws Exception {
		DAO dao =new DAO();
		try{
			try{
				dao.connection();
				String sql = "delete from jiudian..room where roomnum = '" + num + "'";
				Statement stmt = dao.getConn().createStatement();
				
				stmt.executeUpdate( sql );
				System.out.print("刪除成功！");
			}finally{
				dao.close();
				
			}
		}catch(SQLException e){
			throw new RoomException( exceptions.getUpdateAccountError());
		}

	}

	public static void main(String[] arges) throws Exception {
		BusinessRoom bs = new BusinessRoom();
		Room room = new Room();
		room.setDengjitime("123");
		room.setFax("213");
		room.setKhname("erwe");
		room.setKhsex("ban");
		room.setRoomnum("13213");
		room.setRuzhutime("ewrwerw");
		room.setStation("werew");
		room.setTelnum("123213213");
		bs.addRoom(room);
		//ArrayList list = new ArrayList();
		//list=bs.queryAllroom();
		//room = (Room)list.get(0);
		//System.out.println(room.getFax());
      // bs.updateRoom(room, "13213");
    //   bs.deleteRoom("13213");
		//String id = bs.queryId("13213");
		//System.out.print(id);
	}

}