/// <summary>
/// 增加订单
/// </summary>
/// <param name="guest">客户记录对象</param>
public void AddOrder(GuestRecord guest)
{
string sql = string.Format(@"INSERT [dbo].[GuestRecord] ( [GuestID], [Deposit], [ArrivalDate], [RoomId])
VALUES ('{0}','{1}','{2}','{3}')
UPDATE [dbo].[Room]
SET [RoomStateId]=1
WHERE [RoomId]='{3}'", guest.GuestID, guest.Deposit, guest.ArrivalDateStr, guest.RoomId);
DBHerper.NonQuery(sql);
}
/// <summary>
/// 查询单条订单
/// </summary>
/// <param name="roomid">房间编号</param>
/// <returns>订单对象 </returns>
public GuestRecord SLoadOrder(string roomid)
{
GuestRecord record = null;
string sql = string.Format(@"SELECT [RoomId],[Name],[CardNo],[Phone],CASE WHEN [Sex]=0 THEN '男' WHEN [Sex]=1 THEN '女' END,[Deposit],[ArrivalDate],R.ID
FROM [dbo].[GuestInfo] G,[dbo].[GuestRecord] R
WHERE G.ID=R.GuestID AND [LeaveDate] IS NULL and [RoomId]={0}", roomid);
SqlDataReader r = DBHerper.Reader(sql);
if (r.Read())
{
record = new GuestRecord()
{
RoomId = Convert.ToInt32(r[0]), Name = r[1].ToString(), CardNo = r[2].ToString(), Phone = r[3].ToString(), SexStr = r[4].ToString(), Deposit = Convert.ToDouble(r[5]), ArrivalDate = Convert.ToDateTime(r[6]), ID = Convert.ToInt32(r[7])
};
}
r.Close();
return(record);
}
/// <summary>
/// 筛选订单-按房间编号筛选
/// </summary>
/// <param name="RoomID">房间编号</param>
/// <returns>客户记录对象集合</returns>
public List <GuestRecord> ScreenOrder(int RoomID)
{
List <GuestRecord> list = new List <GuestRecord>();
string sql = string.Format(@"SELECT [RoomId],[Name],[CardNo],[Phone],CASE WHEN [Sex]=0 THEN '男' WHEN [Sex]=1 THEN '女' END,[Deposit],[ArrivalDate],R.ID
FROM [dbo].[GuestInfo] G,[dbo].[GuestRecord] R
WHERE G.ID=R.GuestID AND [LeaveDate] IS NULL AND [RoomId] LIKE '%{0}%'", RoomID);
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
GuestRecord record = new GuestRecord()
{
RoomId = Convert.ToInt32(r[0]), Name = r[1].ToString(), CardNo = r[2].ToString(), Phone = r[3].ToString(), SexStr = r[4].ToString(), Deposit = Convert.ToDouble(r[5]), ArrivalDate = Convert.ToDateTime(r[6]), ID = Convert.ToInt32(r[7])
};
list.Add(record);
}
r.Close();
return(list);
}
/// <summary>
/// 加载记录
/// </summary>
/// <returns>记录集合</returns>
public List <GuestRecord> LoadRecord()
{
List <GuestRecord> list = new List <GuestRecord>();
string sql = string.Format(@"SELECT [RoomId],[Name],[CardNo],[Phone],CASE WHEN [Sex]=0 THEN '男' WHEN [Sex]=1 THEN '女' END,[Deposit],[ArrivalDate],[LeaveDate],[RoomTotal],[FoodTotal],R.[ID]
FROM [dbo].[GuestInfo] G,[dbo].[GuestRecord] R
WHERE G.ID=R.GuestID AND [LeaveDate] IS NOT NULL");
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
GuestRecord g = new GuestRecord()
{
RoomId = Convert.ToInt32(r[0]), Name = r[1].ToString(), CardNo = r[2].ToString(), Phone = r[3].ToString(), SexStr = r[4].ToString(), Deposit = Convert.ToDouble(r[5]), ArrivalDate = Convert.ToDateTime(r[6]), LeaveDate = Convert.ToDateTime(r[7]), RoomTotal = Convert.ToDouble(r[8]), FoodTotal = Convert.ToDouble(r[9]), ID = Convert.ToInt32(r[10])
};
list.Add(g);
}
r.Close();
return(list);
}