?using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using HotelManagerModels;

namespace HotelManagerDAL
{
    public class RoomDAL
    {
        //從配置文件中取出數據庫連接的字符串
        private string strConn = ConfigurationManager.ConnectionStrings["HotelConn"].ToString();

        #region 查詢所有房間信息
        /// <summary>
        /// 查詢所有房間信息
        /// </summary>
        /// <returns>房間集合</returns>
        public List<Room> SelectRoomsAll()
        { 
            using(SqlConnection conn = new SqlConnection(strConn))
            {
                List<Room> list = new List<Room>();
                SqlCommand cmd = new SqlCommand("usp_SelectRoomsAll", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Room r = new Room();
                        RoomStatus rs=new RoomStatus();
                        r.RoomID = Convert.ToInt32(dr["RoomID"]);
                        r.Number = dr["Number"].ToString();
                        r.TypeID = Convert.ToInt32(dr["TypeID"]);
                        r.BedNumber = Convert.ToInt32(dr["BedNumber"]);
                        r.Description = dr["Description"].ToString();
                        rs.ID = Convert.ToInt32(dr["StatusID"]);
                        rs.Status = dr["Status"].ToString();
                        r.State = rs;
                        r.GuestNumber = Convert.ToInt32(dr["GuestNumber"]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根據房間號查詢房間信息
        /// <summary>
        /// 根據房間號查詢房間信息
        /// </summary>
        /// <param name="number">房間號</param>
        /// <returns>房間集合</returns>
        public List<Room> SelectRoomsByRoomNumber(string number)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                List<Room> list = new List<Room>();
                SqlCommand cmd = new SqlCommand("usp_SelectRoomsByRoomNumber", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Number", SqlDbType.NVarChar, 50).Value = number;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Room r = new Room();
                        RoomStatus rs = new RoomStatus();
                        r.RoomID = Convert.ToInt32(dr["RoomID"]);
                        r.Number = dr["Number"].ToString();
                        r.TypeID = Convert.ToInt32(dr["TypeID"]);
                        r.BedNumber = Convert.ToInt32(dr["BedNumber"]);
                        r.Description = dr["Description"].ToString();
                        rs.Status = dr["Status"].ToString();
                        r.State = rs;
                        r.GuestNumber = Convert.ToInt32(dr["GuestNumber"]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根據房間編號查詢房間信息
        /// <summary>
        /// 根據房間編號查詢房間信息
        /// </summary>
        /// <param name="roomID">房間編號</param>
        /// <returns>泛型集合</returns>
        public List<Room> SelectRoomByRoomID(int roomID)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                List<Room> list = new List<Room>();
                SqlCommand cmd = new SqlCommand("usp_SelectRoom", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@RoomID", SqlDbType.Int).Value = roomID;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Room r = new Room();
                        RoomStatus rs = new RoomStatus();
                        r.RoomID = Convert.ToInt32(dr["RoomID"]);
                        r.Number = dr["Number"].ToString();
                        r.TypeID = Convert.ToInt32(dr["TypeID"]);
                        r.BedNumber = Convert.ToInt32(dr["BedNumber"]);
                        r.Description = dr["Description"].ToString();
                        rs.ID = Convert.ToInt32(dr["StatusID"]);
                        rs.Status = dr["Status"].ToString();
                        r.State = rs;
                        r.GuestNumber = Convert.ToInt32(dr["GuestNumber"]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 增加房間信息
        /// <summary>
        /// 增加房間信息
        /// </summary>
        /// <param name="room">房間對象</param>
        /// <returns>bool</returns>
        public void InsertRomm(Room room)
        {
            using(SqlConnection conn=new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_InsertRoom", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@Number",SqlDbType.NVarChar,50),
                    new SqlParameter("@TypeID",SqlDbType.Int),
                    new SqlParameter("@BedNumber",SqlDbType.Int),
                    new SqlParameter("@Description",SqlDbType.NVarChar,50),
                    new SqlParameter("@StatusID",SqlDbType.Int),
                    new SqlParameter("@GuestNumber",SqlDbType.Int)
                };
                pars[0].Value = room.Number;
                pars[1].Value = room.TypeID;
                pars[2].Value = room.BedNumber;
                pars[3].Value = room.Description;
                pars[4].Value = room.State.ID;
                pars[5].Value = room.GuestNumber;
                foreach(SqlParameter par in pars)
                {
                    cmd.Parameters.Add(par);
                }
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion
        
        #region 修改房間信息
        /// <summary>
        /// 修改房間信息
        /// </summary>
        /// <param name="room">room實體對象</param>