roomoperationdal.cs

酒店管理系统

        /// </summary>
        /// <param name="number">房间编号</param>
        /// <returns>实体</returns>
        public List<ExitRoomInfo> SelectExitRoomInfo(string number)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectExitRoomInfo",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Number", SqlDbType.NVarChar, 18).Value = number;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<ExitRoomInfo> list = new List<ExitRoomInfo>();
                    while (dr.Read())
                    {
                        ExitRoomInfo e = new ExitRoomInfo();
                        e.Id = Convert.ToInt32(dr[0]);
                        e.Number = dr[1].ToString();
                        e.BeginTime = Convert.ToDateTime(dr[2]);
                        e.CDiscount = Convert.ToSingle(dr[3]);
                        e.Price = Convert.ToSingle(dr[4]);
                        e.Name = dr[5].ToString();
                        e.CID = dr[6].ToString();
                        e.CustomersType = dr[7].ToString();
                        list.Add(e);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 取出所有的业务信息
        /// <summary>
        /// 取出所有的业务信息
        /// </summary>
        /// <returns>泛型集合</returns>
        public List<RoomOfExit> SelectRoomOperationAll()
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectRoomOperationAll", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                List<RoomOfExit> list = new List<RoomOfExit>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        RoomOfExit ro = new RoomOfExit();
                        ro.Number = dr[0].ToString();
                        ro.BeginTime = Convert.ToDateTime(dr[1]);
                        ro.TotalPrice = Convert.ToSingle(dr[2]);
                        ro.CName = dr[3].ToString();
                        ro.CID = dr[4].ToString();
                        list.Add(ro);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion 

        #region 根据房间号码查询房间的价钱
        /// <summary>
        /// 根据房间号码查询房间的价钱
        /// </summary>
        /// <param name="roomNumber">房间编号</param>
        /// <returns>房间价格</returns>
        public float SelectPriceByRoomNumber(string roomNumber)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectPriceByRoomNumber", conn);
                cmd.CommandType=CommandType.StoredProcedure;
                cmd.Parameters.Add("@RoomNumber", SqlDbType.NVarChar, 50).Value = roomNumber;
                List<RoomType> list = new List<RoomType>();
                try
                {
                    conn.Open();
                    float price = Convert.ToSingle(cmd.ExecuteScalar());
                    
                    return price;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }

            }
        }
        #endregion

        #region 添加入住业务
        /// <summary>
        /// 添加入住业务
        /// </summary>
        /// <param name="ro">实体对象</param>
        public void InsertRoomOperation(RoomOperation ro)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_InsertRoomOperation",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = new SqlParameter[] { 
                    new SqlParameter("@BeginTime",SqlDbType.DateTime),
                    new SqlParameter("@EndTime",SqlDbType.DateTime),
                    new SqlParameter("@RoomId",SqlDbType.Int),
                    new SqlParameter("@TotalPrice",SqlDbType.Money),
                    new SqlParameter("@CIdentityId",SqlDbType.NVarChar,18),
                    new SqlParameter("@Remarks",SqlDbType.NVarChar,50)
                };
                pars[0].Value = ro.BeginTime;
                pars[1].Value = DateTime.Now;
                pars[2].Value=ro.RoomId;
                pars[3].Value = 0;
                pars[4].Value=ro.CIdentityId;
                pars[5].Value = ro.Remarks;
                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 根据ID修改退房时间
        /// <summary>
        /// 根据ID修改退房时间
        /// </summary>
        /// <param name="ro">实体对象</param>
        public void UpdateRoomOperation(RoomOperation ro)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_UpdateRoomOperation", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ro.ID;
                cmd.Parameters.Add("@EndTime", SqlDbType.DateTime).Value = ro.EndTime;
                cmd.Parameters.Add("@TotalPrice", SqlDbType.Float).Value = ro.TotalPrice;
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 详细业务信息(生成报表)
        /// <summary>
        /// 详细业务信息,生成报表
        /// </summary>
        /// <returns>DataTable</returns>
        public DataTable SelectRoomInfoAnnal()
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                DataTable dt = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter("usp_SelectRoomInfoAnnal", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                try
                {
                    da.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        #endregion
    }
}