bizinfodbconn.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>
/// bizInfoDBConn 的摘要说明
/// </summary>
public class bizInfoDBConn:DBConn
{
    private string sql = null;
    private SqlCommand comm = null;
    private SqlDataReader reader = null;
    private int fullRoomNum = 0;
    private int totalRoomNum = 1;
	public bizInfoDBConn()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}

    public int getFullRoomNum()
    {
        return fullRoomNum;
    }

    public int getTotalRoomNum()
    {
        this.Open();
        sql = "select COUNT(*) from [HotelInfo]";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();
        while (reader.Read())
        {
            totalRoomNum = reader.GetInt32(0);
        }
        reader.Close();
        this.Close();
        return totalRoomNum;
    }

    public ArrayList getFullRoom(DateTime day)
    {
        ArrayList reList = new ArrayList();
        roomHistory room = null;

        this.Open();

        sql = "SELECT StayInfo.room, ClientInfo.ClientName, StayInfo.startTime, StayInfo.endTime FROM StayInfo INNER JOIN ClientInfo ON StayInfo.IDPerson = ClientInfo.ID WHERE (StayInfo.startTime >= '"+ day.ToString() +"') AND (StayInfo.startTime < '"+day.AddDays(1.0).ToString()+"') OR (StayInfo.startTime <= '"+day.ToString()+"') AND (StayInfo.endTime > '"+day.AddHours(18.0).ToString()+"') ORDER BY ClientInfo.ClientName";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();
        while (reader.Read())
        {
            fullRoomNum++;
            room = new roomHistory();
            room.RoomId = reader.GetInt32(0);
            room.CusName = reader.GetString(1);
            room.CheckInTime = reader.GetDateTime(2);
            room.CheckOutTime = reader.GetDateTime(3);
            reList.Add(room);
        }
        reader.Close();

        sql = "SELECT StayInfo.room, GroupInfo.name, StayInfo.startTime, StayInfo.endTime FROM StayInfo INNER JOIN GroupInfo ON StayInfo.IDGroup = GroupInfo.ID WHERE (StayInfo.startTime >= '" + day.ToString() + "') AND (StayInfo.startTime < '" + day.AddDays(1.0).ToString() + "') OR (StayInfo.startTime <= '" + day.ToString() + "') AND (StayInfo.endTime > '" + day.AddHours(18.0).ToString() + "') ORDER BY GroupInfo.name";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();
        while (reader.Read())
        {
            fullRoomNum++;
            room = new roomHistory();
            room.RoomId = reader.GetInt32(0);
            room.CusName = reader.GetString(1);
            room.CheckInTime = reader.GetDateTime(2);
            room.CheckOutTime = reader.GetDateTime(3);
            reList.Add(room);
        }
        reader.Close();

        this.Close();
        return reList;
    }

    public ArrayList getRoomIncome(DateTime begin, DateTime end)
    {
        ArrayList reList = new ArrayList();
        roomHistory room = null;

        this.Open();

        sql = "select StayInfo.room, ClientInfo.ClientName, StayInfo.startTime, StayInfo.endTime, StayInfo.totalPrice from StayInfo, ClientInfo where StayInfo.endTime >='" + begin.ToString() + "' and StayInfo.endTime < '" + end.ToString() + "' and StayInfo.IDPerson = ClientInfo.ID";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();
        while (reader.Read())
        {
            room = new roomHistory();
            room.RoomId = reader.GetInt32(0);
            room.CusName = reader.GetString(1);
            room.CheckInTime = reader.GetDateTime(2);
            room.CheckOutTime = reader.GetDateTime(3);
            room.Rent = reader.GetSqlMoney(4).ToDouble();
            reList.Add(room);
        }
        reader.Close();

        sql = "select StayInfo.room, GroupInfo.name, StayInfo.startTime, StayInfo.endTime, StayInfo.totalPrice from StayInfo, GroupInfo where StayInfo.endTime >='" + begin.ToString() + "' and StayInfo.endTime < '" + end.ToString() + "' and StayInfo.IDGroup = GroupInfo.ID";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();
        while (reader.Read())
        {
            room = new roomHistory();
            room.RoomId = reader.GetInt32(0);
            room.CusName = reader.GetString(1);
            room.CheckInTime = reader.GetDateTime(2);
            room.CheckOutTime = reader.GetDateTime(3);
            room.Rent = reader.GetSqlMoney(4).ToDouble();
            reList.Add(room);
        }
        reader.Close();

        this.Close();
        return reList;
    }
}