clientdbconn.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>
/// ClientDBConn 的摘要说明
/// </summary>
public class ClientDBConn:DBConn 
{
    private string sql = null;
    private SqlCommand comm = null;
    private SqlDataReader reader = null;

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

    public ArrayList getClientInfoPresent()
    {
   //     System.DateTime currentTime = new System.DateTime();
   //    currentTime = System.DateTime.Now;

        this.Open();
        sql = "SELECT ClientInfo.ClientName, StayInfo.room, HotelInfo.roomType, HotelInfo.price, StayInfo.startTime,StayInfo.endTime, HotelInfo.discount, ClientInfo.identityNo, HotelInfo.telephone FROM StayInfo INNER JOIN ClientInfo ON StayInfo.IDPerson = ClientInfo.ID INNER JOIN HotelInfo ON StayInfo.room = HotelInfo.room WHERE (StayInfo.IsIn = 1) AND (StayInfo.IDGroup IS NULL)";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Client clientInfo = null;

        while (reader.Read())
        {
            clientInfo = new Client();
            clientInfo.Name = reader.GetString(0);
            clientInfo.Room = reader.GetInt32(1);
            clientInfo.Type = reader.GetString(2);
            clientInfo.Price = reader.GetSqlMoney(3).ToDouble();
            clientInfo.StartTime = reader.GetSqlDateTime(4).ToString();
            clientInfo.EndTime = reader.GetSqlDateTime(5).ToString();
            clientInfo.Discount = reader.GetDouble(6);
            clientInfo.IdentityNO = reader.GetString(7);
            clientInfo.TelephoneNO = reader.GetInt32(8).ToString();
            reList.Add(clientInfo);
        }

        this.Close();
        return reList;
    }

    public ArrayList getClientInfoHistory()
    {
        this.Open();
        sql = "SELECT ClientInfo.ClientName, StayInfo.room, HotelInfo.roomType, HotelInfo.price, StayInfo.startTime,StayInfo.endTime, HotelInfo.discount, ClientInfo.identityNo, HotelInfo.telephone FROM StayInfo INNER JOIN ClientInfo ON StayInfo.IDPerson = ClientInfo.ID INNER JOIN HotelInfo ON StayInfo.room = HotelInfo.room WHERE (StayInfo.IsIn = 0) AND (StayInfo.IDGroup IS NULL)";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Client clientInfo = null;

        while (reader.Read())
        {
            clientInfo = new Client();
            clientInfo.Name = reader.GetString(0);
            clientInfo.Room = reader.GetInt32(1);
            clientInfo.Type = reader.GetString(2);
            clientInfo.Price = reader.GetSqlMoney(3).ToDouble();
            clientInfo.StartTime = reader.GetSqlDateTime(4).ToString();
            clientInfo.EndTime = reader.GetSqlDateTime(5).ToString();
            clientInfo.Discount = reader.GetDouble(6);
            clientInfo.IdentityNO = reader.GetString(7);
            clientInfo.TelephoneNO = reader.GetInt32(8).ToString();
            reList.Add(clientInfo);
        }

        this.Close();
        return reList;
    }

    public ArrayList getClientInfoReservation()
    {
        this.Open();
        sql = "SELECT engageInfo.name, RoomOrdered.roomNo, HotelInfo.roomType, HotelInfo.price, engageInfo.startTime, engageInfo.endTime, HotelInfo.discount, engageInfo.memo, engageInfo.telePhoneNo FROM engageInfo INNER JOIN RoomOrdered ON engageInfo.ID = RoomOrdered.orderID INNER JOIN HotelInfo ON RoomOrdered.roomNo = HotelInfo.room WHERE (engageInfo.isGroup = 0) ORDER BY engageInfo.startTime";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Client clientInfo = null;

        while (reader.Read())
        {
            clientInfo = new Client();
            clientInfo.Name = reader.GetString(0);
            clientInfo.Room = reader.GetInt32(1);
            clientInfo.Type = reader.GetString(2);
            clientInfo.Price = reader.GetSqlMoney(3).ToDouble();
            clientInfo.StartTime = reader.GetSqlDateTime(4).ToString();
            clientInfo.EndTime = reader.GetSqlDateTime(5).ToString();
            clientInfo.Discount = reader.GetDouble(6);
            clientInfo.Memo = reader.GetString(7);
            clientInfo.TelephoneNO = reader.GetString(8);
            reList.Add(clientInfo);
        }

        this.Close();
        return reList;
    }

    public ArrayList getGroupClientPresent() 
    {
        this.Open();
        sql = "SELECT GroupInfo.name, ClientInfo.ClientName, StayInfo.room, HotelInfo.roomType, HotelInfo.price, StayInfo.startTime, StayInfo.endTime, GroupInfo.discount, GroupInfo.linkman, GroupInfo.telePhoneNo FROM StayInfo INNER JOIN GroupInfo ON StayInfo.IDGroup = GroupInfo.ID LEFT OUTER JOIN HotelInfo ON StayInfo.room = HotelInfo.room INNER JOIN ClientInfo ON StayInfo.IDPerson = ClientInfo.ID WHERE (StayInfo.IsIn = 0)";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Client groupClient = null;

        while (reader.Read())
        {
            groupClient = new Client();
            groupClient.Groupname = reader.GetString(0);
            groupClient.Name = reader.GetString(1);
            groupClient.Room = reader.GetInt32(2);
            groupClient.Type = reader.GetString(3);
            groupClient.Price = reader.GetSqlMoney(4).ToDouble();
            groupClient.StartTime = reader.GetSqlDateTime(5).ToString();
            groupClient.EndTime = reader.GetSqlDateTime(6).ToString();
            groupClient.Discount = reader.GetDouble(7);
            groupClient.Linkman = reader.GetString(8);
            groupClient.Linktelephone = reader.GetString(9);
            reList.Add(groupClient);
        }

        this.Close();
        return reList;
    }

    public ArrayList getGroupClientHistory()
    {
        this.Open();
        sql = "SELECT GroupInfo.name, ClientInfo.ClientName, StayInfo.room, HotelInfo.roomType, HotelInfo.price, StayInfo.startTime, StayInfo.endTime, GroupInfo.discount, GroupInfo.linkman, GroupInfo.telePhoneNo FROM StayInfo INNER JOIN GroupInfo ON StayInfo.IDGroup = GroupInfo.ID LEFT OUTER JOIN HotelInfo ON StayInfo.room = HotelInfo.room INNER JOIN ClientInfo ON StayInfo.IDPerson = ClientInfo.ID WHERE (StayInfo.IsIn = 1)";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Client groupClient = null;

        while (reader.Read())
        {
            groupClient = new Client();
            groupClient.Groupname = reader.GetString(0);
            groupClient.Name = reader.GetString(1);
            groupClient.Room = reader.GetInt32(2);
            groupClient.Type = reader.GetString(3);
            groupClient.Price = reader.GetSqlMoney(4).ToDouble();
            groupClient.StartTime = reader.GetSqlDateTime(5).ToString();
            groupClient.EndTime = reader.GetSqlDateTime(6).ToString();
            groupClient.Discount = reader.GetDouble(7);
            groupClient.Linkman = reader.GetString(8);
            groupClient.Linktelephone = reader.GetString(9);
            reList.Add(groupClient);
        }

        this.Close();
        return reList;
    }

    public ArrayList getGroupClientReservation()
    {
        this.Open();
        sql = "SELECT engageInfo.name, RoomOrdered.roomNo, HotelInfo.roomType, HotelInfo.price, engageInfo.startTime, engageInfo.endTime, HotelInfo.discount,engageInfo.telePhoneNo FROM engageInfo INNER JOIN RoomOrdered ON engageInfo.ID = RoomOrdered.orderID INNER JOIN HotelInfo ON RoomOrdered.roomNo = HotelInfo.room WHERE (engageInfo.isGroup = 1) ORDER BY engageInfo.startTime";
        comm = new SqlCommand(sql, conn);
        reader = comm.ExecuteReader();

        ArrayList reList = new ArrayList();
        Client groupClient = null;

        while (reader.Read())
        {
            groupClient = new Client();
            groupClient.Groupname = reader.GetString(0);
            groupClient.Room = reader.GetInt32(1);
            groupClient.Type = reader.GetString(2);
            groupClient.Price = reader.GetSqlMoney(3).ToDouble();
            groupClient.StartTime = reader.GetSqlDateTime(4).ToString();
            groupClient.EndTime = reader.GetSqlDateTime(5).ToString();
            groupClient.Discount = reader.GetDouble(6);
            groupClient.Linktelephone = reader.GetString(7);
            reList.Add(groupClient);
        }

        this.Close();
        return reList;
    }
}