hoteldbconn.cs

酒店管理系统

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;

/// <summary>
/// HotelDBConn 的摘要说明
/// </summary>
public class HotelDBConn:DBConn
{
    private string sql = null;
    private SqlCommand comm = null;
    private SqlDataReader reader = null;

    private string RangeMode;         //排列的方式有三种：按房间号，房型和价格排列

    public HotelDBConn()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    /// <summary>
    /// 提取房间信息
    /// </summary>
    /// <param name="rangeMode"></param>
    /// <returns></returns>
    public ArrayList getHotelInfo()
    {
        this.Open();
        sql = "SELECT * FROM [HotelInfo]";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Hotel hotelInfo = null;

        while (reader.Read())
        {
            hotelInfo = new Hotel();
            hotelInfo.Room = reader.GetInt32(0);
            hotelInfo.Price = reader.GetSqlMoney(1).ToDouble();
            hotelInfo.Type = reader.GetString(2);
            hotelInfo.Telephone = reader.GetInt32(3);
            try
            {
                hotelInfo.Memo = reader.GetString(4);
            }
            catch
            {
                hotelInfo.Memo = "";
            }
            hotelInfo.Discount = reader.GetDouble(5);
            reList.Add(hotelInfo);
        }

        this.Close();
        return reList;
        
        throw new Exception("The method or operation is not implemented.");
    }

    public int getRoomNum()
    {
        this.Open();
        sql = "SELECT COUNT(room) AS roomNum FROM HotelInfo";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();
        Hotel hotleInfo = null;
        hotleInfo = new Hotel();
        while (reader.Read())
        {
            hotleInfo.RoomNum = reader.GetInt32(0);
        }

        return hotleInfo.RoomNum;
    }

    /// <summary>
    /// 空房间查询
    /// </summary>
    /// <returns></returns>
    public ArrayList getEmptyRoom()
    {
        this.Open();
        sql = "SELECT DISTINCT HotelInfo.room, HotelInfo.roomType, HotelInfo.price FROM HotelInfo LEFT OUTER JOIN StayInfo ON HotelInfo.room = StayInfo.room LEFT OUTER JOIN RoomOrdered ON HotelInfo.room = RoomOrdered.roomNo LEFT OUTER JOIN engageInfo ON RoomOrdered.orderID = engageInfo.ID WHERE (HotelInfo.room NOT IN (SELECT  roomNo FROM RoomOrdered AS RoomOrdered_1 WHERE (engageInfo.startTime > GETDATE()))) AND (HotelInfo.room NOT IN (SELECT room FROM StayInfo AS StayInfo_1 WHERE(stayinfo.isin = 1))) ORDER BY HotelInfo.room";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Hotel hotelInfo = null;

        while (reader.Read())
        {
            hotelInfo = new Hotel();
            hotelInfo.Room = reader.GetInt32(0);
            hotelInfo.Type = reader.GetString(1);
            hotelInfo.Price = reader.GetSqlMoney(2).ToDouble();
            hotelInfo.Status = "空房";
            reList.Add(hotelInfo);
        }

        this.Close();
        return reList;
    }

    /// <summary>
    /// 有人住的房间查询
    /// </summary>
    /// <returns></returns>
    public ArrayList getHoldRoom()
    {
        this.Open();
        sql = "SELECT DISTINCT HotelInfo.room, HotelInfo.roomType, HotelInfo.price FROM HotelInfo INNER JOIN  StayInfo ON HotelInfo.room = StayInfo.room WHERE (StayInfo.IsIn = 1)";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Hotel hotelInfo = null;

        while (reader.Read())
        {
            hotelInfo = new Hotel();
            hotelInfo.Room = reader.GetInt32(0);
            hotelInfo.Type = reader.GetString(1);
            hotelInfo.Price = reader.GetSqlMoney(2).ToDouble();
            hotelInfo.Status = "有人住";
            reList.Add(hotelInfo);
        }

        this.Close();
        return reList;
    }

    /// <summary>
    /// 今日被预订的房间查询
    /// </summary>
    /// <returns></returns>
    public ArrayList getReservationRoom()
    {
        this.Open();
        sql = "SELECT DISTINCT HotelInfo.room, HotelInfo.roomType, HotelInfo.price FROM RoomOrdered LEFT OUTER JOIN HotelInfo ON RoomOrdered.roomNo = HotelInfo.room LEFT OUTER JOIN engageInfo ON engageInfo.ID = RoomOrdered.orderID WHERE (HotelInfo.room IN (SELECT roomNo FROM RoomOrdered AS RoomOrdered_1)) AND (engageInfo.startTime = CONVERT(datetime, CONVERT(char(10), GETDATE(), 120)))";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Hotel hotelInfo = null;

        while (reader.Read())
        {
            hotelInfo = new Hotel();
            hotelInfo.Room = reader.GetInt32(0);
            hotelInfo.Type = reader.GetString(1);
            hotelInfo.Price = reader.GetSqlMoney(2).ToDouble();
            hotelInfo.Status = "今日被预订";
            reList.Add(hotelInfo);
        }

        this.Close();
        return reList;
    }

    /// <summary>
    /// 插入房间信息
    /// </summary>
    /// <param name="hotel"></param>
    public void insertRoom(Hotel hotel)
    {
        sql = "INSERT INTO [HotelInfo] (room, price, roomType, telephone, memo, discount) VALUES('" + hotel.Room.ToString () +"', '" + hotel.Price.ToString() + "', '" + hotel.Type .ToString () + "', '" + hotel.Telephone.ToString() +"', '" + hotel.Memo.ToString() + "', '"+ hotel.Discount.ToString() +"')";
        this.Open();
        comm = new SqlCommand(sql, conn);
        comm.ExecuteNonQuery();
        this.Close();

     //   throw new Exception("The method or operation is not implemented.");
    }

    /// <summary>
    /// 删除房间信息
    /// </summary>
    /// <param name="hotel"></param>
    public void delRoom(int room)
    {
        sql = "DELETE FROM [HotelInfo] WHERE [ROOM] = " + room.ToString();
        this.Open();
        comm = new SqlCommand(sql, conn);
        comm.ExecuteNonQuery();
        this.Close();
    }

    /// <summary>
    /// 修改房间信息
    /// </summary>
    /// <param name="hotel"></param>
    public void updateRoom(Hotel hotel)
    {
        sql = "UPDATE [HotelInfo] SET [room] = '" + hotel.Room.ToString () + "', [roomType] = '" + hotel.Type.ToString () + "', [price] = '" + hotel.Price.ToString() + "', [telephone] = '" + hotel.Telephone.ToString() + "', [memo] = '" + hotel.Memo.ToString () + "',  [discount] = " + hotel.Discount.ToString() + " WHERE [room] = " + hotel.Room.ToString();
        this.Open();
        comm = new SqlCommand(sql, conn);
        comm.ExecuteNonQuery();
        this.Close();
    }

}