?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 RoomTypeDAL
    {
        //從配置文件中取出數據庫連接的字符串
        string strConn = ConfigurationManager.ConnectionStrings["HotelConn"].ToString();

        #region 查詢所有房間類型信息
        /// <summary>
        /// 查詢所有房間類型信息
        /// </summary>
        /// <returns>房間類型泛型集合</returns>
        public List<RoomType> SelectRoomType()
        {
            using (SqlConnection conn = new SqlConnection(strConn)) 
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomTypesAll", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                List<RoomType> list = new List<RoomType>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomType t = new RoomType();
                        t.TypeID = Convert.ToInt32(dr["TypeID"]);
                        t.TypeName = dr["TypeName"].ToString();
                        t.TypePrice = Convert.ToSingle(dr["TypePrice"]);
                        t.AddBedPrice = Convert.ToSingle(dr["AddBedPrice"]);
                        t.IsAddBed = dr["IsAddBed"].ToString();
                        t.Remark = dr["Remark"].ToString();
                        list.Add(t);
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
                return list;
            }
        }
        #endregion

        #region 根據房間名稱查詢房間類型信息
        /// <summary>
        /// 根據房間名稱查詢房間類型信息
        /// </summary>
        /// <param name="typeName">房間編號</param>
        /// <returns>房間類型泛型集合</returns>
        public List<RoomType> SelectRoomTypeByName(string typeName)
        {
            using(SqlConnection conn=new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomTypeListByTypeName",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TypeName", SqlDbType.VarChar, 50).Value=typeName;
                List<RoomType> list = new List<RoomType>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomType t = new RoomType();
                        t.TypeID = Convert.ToInt32(dr[0]);
                        t.TypeName = dr[1].ToString();
                        t.TypePrice = Convert.ToSingle(dr[2]);
                        t.AddBedPrice = Convert.ToSingle(dr[3]);
                        t.IsAddBed = dr[4].ToString();
                        t.Remark = dr[5].ToString();
                        list.Add(t);
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
                return list;
            }
        }
        #endregion

        #region 根據房間編號查詢房間類型
        /// <summary>
        /// 根據房間編號查詢房間類型
        /// </summary>
        /// <param name="typeID">房間編號</param>
        /// <returns>房間類型泛型集合</returns>
        public List<RoomType> SelectRoomTypeByTypeID(int typeID)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomType", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter par = new SqlParameter("@TypeID", SqlDbType.Int);
                par.Value = typeID;
                cmd.Parameters.Add(par);
                List<RoomType> list = new List<RoomType>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomType t = new RoomType();
                        t.TypeID = Convert.ToInt32(dr[0]);
                        t.TypeName = dr[1].ToString();
                        t.TypePrice = Convert.ToSingle(dr[2]);
                        t.AddBedPrice = Convert.ToSingle(dr[3]);
                        t.IsAddBed = dr[4].ToString();
                        t.Remark = dr[5].ToString();
                        list.Add(t);
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
                return list;
            }
        }
        #endregion

        #region 添加房間類型
        /// <summary>
        /// 添加房間類型
        /// </summary>
        /// <param name="t">房間類型實體對象</param>
        public void InsertRoomType(RoomType t)
        { 
            using(SqlConnection conn=new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_InsertRoomType", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = new SqlParameter[] 
                        { new SqlParameter("@TypeName",SqlDbType.VarChar,50),
                        new SqlParameter("@TypePrice",SqlDbType.Money),
                        new SqlParameter("@AddBedPrice",SqlDbType.Money),
                        new SqlParameter("@IsAddBed",SqlDbType.NChar,10),
                        new SqlParameter("@Remark",SqlDbType.NVarChar,50)
                };
                pars[0].Value = t.TypeName;
                pars[1].Value = t.TypePrice;
                pars[2].Value= t.AddBedPrice;
                pars[3].Value = t.IsAddBed;
                pars[4].Value = t.Remark;
                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="t">實體對象</param>
        /// <returns>bool</returns>
        public bool UpdateRoomTypeByID(RoomType t)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_UpdateRoomType",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = new SqlParameter[] 
                        { new SqlParameter("@TypeName",SqlDbType.VarChar,50),
                        new SqlParameter("@TypePrice",SqlDbType.Money),
                        new SqlParameter("@AddBedPrice",SqlDbType.Money),
                        new SqlParameter("@IsAddBed",SqlDbType.NChar,10),
                        new SqlParameter("@Remark",SqlDbType.NVarChar,50),
                        new SqlParameter("@TypeID",SqlDbType.Int)
                };
                pars[0].Value = t.TypeName;
                pars[1].Value = t.TypePrice;
                pars[2].Value = t.AddBedPrice;
                pars[3].Value = t.IsAddBed;
                pars[4].Value = t.Remark;
                pars[5].Value = t.TypeID;
                foreach (SqlParameter par in pars)
                {
                    cmd.Parameters.Add(par);
                }
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                
            }
        }
        #endregion

        #region 根據編號刪除房間類型信息
        /// <summary>
        /// 根據編號刪除房間類型信息
        /// </summary>
        /// <param name="typeID">房間的ID</param>
        /// <returns>bool</returns>
        public bool DeleteRoomTypeByID(int typeID)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_DeleteRoomType", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TypeID", SqlDbType.Int).Value = typeID;
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根據類型名稱查詢房價
        /// <summary>
        /// 根據類型名稱查詢房價
        /// </summary>
        /// <param name="typeName">房間名稱</param>
        /// <returns>價格</returns>
        public float SelectTypePriceByTypeName(string typeName)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectTypePriceByTypeName", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TypeName", SqlDbType.VarChar, 50).Value = typeName;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        return Convert.ToSingle(dr[0]);
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
                return 0;
            }
        }
        #endregion
    }
}