?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 RoomOperationDAL
    {
        string strConn = ConfigurationManager.ConnectionStrings["HotelConn"].ToString();

        #region 根據房間類型,查詢空房間的床位
        /// <summary>
        /// 根據房間類型,查詢空房間的床位
        /// </summary>
        /// <param name="typename">房間類型</param>
        /// <returns>房間的床位</returns>
        public List<int> SelectBedNumberByTypeName(string typename)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectBedNumberByTypeName",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TypeName", SqlDbType.NVarChar, 50).Value = typename;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<int> list = new List<int>();
                    while (dr.Read())
                    {
                        list.Add(Convert.ToInt32(dr[0]));
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 查詢空房信息
        /// <summary>
        /// 查詢空房信息
        /// </summary>
        /// <returns>空房實體</returns>
        public List<RoomInfo> SelectRoomInfoAll()
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomInfoAll", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                List<RoomInfo> list = new List<RoomInfo>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomInfo r = new RoomInfo();
                        r.Number = dr[0].ToString();
                        r.Typename = dr[1].ToString();
                        r.Bednumber = Convert.ToInt32(dr[2].ToString());
                        r.Typeprice = Convert.ToSingle(dr[3]);
                        r.Addbedprice = Convert.ToSingle(dr[4]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 查詢已入住房間
        /// <summary>
        /// 查詢已入住房間
        /// </summary>
        /// <returns>業務實體</returns>
        public List<RoomOperation> SelectRoomOperationOfEndTime()
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomOperationOfEndTime", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                List<RoomOperation> list = new List<RoomOperation>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomOperation ro = new RoomOperation();
                        ro.ID = Convert.ToInt32(dr[0]);
                        ro.BeginTime = Convert.ToDateTime(dr[1]);
                        ro.EndTime = Convert.ToDateTime(dr[2]);
                        ro.RoomId = Convert.ToInt32(dr[3]);
                        ro.TotalPrice = Convert.ToSingle(dr[4]);
                        ro.CIdentityId = dr[5].ToString();
                        ro.Remarks = dr[6].ToString();
                        list.Add(ro);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根據房間類型查詢空房信息
        /// <summary>
        /// 根據房間類型查詢空房信息
        /// </summary>
        /// <param name="typename">房間類型</param>
        /// <returns>空房實體</returns>
        public List<RoomInfo> SelectRoomInfoByRoomType(string typename)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomInfoByRoomType", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TypeName",SqlDbType.NVarChar,50).Value=typename;
                List<RoomInfo> list = new List<RoomInfo>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomInfo r = new RoomInfo();
                        r.Number = dr[0].ToString();
                        r.Typename = dr[1].ToString();
                        r.Bednumber = Convert.ToInt32(dr[2].ToString());
                        r.Typeprice = Convert.ToSingle(dr[3]);
                        r.Addbedprice = Convert.ToSingle(dr[4]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根據類型名稱,查詢空房信息
        /// <summary>
        /// 根據類型名稱,查詢空房信息
        /// </summary>
        /// <param name="typename">房間類型名稱</param>
        /// <param name="bednumber">床位數</param>
        /// <returns></returns>
        public List<RoomInfo> SelectRoomInfoByBedNumber(string typename,int bednumber)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomInfoByBedNumber", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TypeName", SqlDbType.NVarChar, 50).Value = typename;
                cmd.Parameters.Add("@BedNumber", SqlDbType.Int).Value = bednumber;
                List<RoomInfo> list = new List<RoomInfo>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomInfo r = new RoomInfo();
                        r.Number = dr[0].ToString();
                        r.Typename = dr[1].ToString();
                        r.Bednumber = Convert.ToInt32(dr[2].ToString());
                        r.Typeprice = Convert.ToSingle(dr[3]);
                        r.Addbedprice = Convert.ToSingle(dr[4]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根據房間編號查詢退房的信息(價格,等詳細信息)
        /// <summary>
        /// 根據房間編號查詢退房的信息(價格,等詳細信息)