/// <summary>
/// 退房信息
/// </summary>
/// <param name="id">记录编号</param>
/// <returns>退房信息对象</returns>
public CheckOutInfo CheckOutInfo(string id)
{
CheckOutInfo outInfo = new DAL.CheckOutInfo();
string sql = string.Format(@"SELECT [Deposit],DATEDIFF(DAY,[ArrivalDate],
GETDATE())*(SELECT [TypePrice] FROM [dbo].[RoomType] WHERE [TypeId]=
(SELECT [RoomTypeId] FROM [dbo].[Room] WHERE [RoomId]=(select [RoomId] from [dbo].[GuestRecord] where [ID]={0}))),
(SELECT SUM([Num]*[Price])
FROM [dbo].[FoodOrder] F,[dbo].[FoodList] L
WHERE (F.FoodId=L.FoodId) AND [RoomId]=(SELECT [RoomId] FROM [dbo].[GuestRecord] WHERE [ID]={0})
AND [OrderDate]>=(SELECT [ArrivalDate] FROM [dbo].[GuestRecord] WHERE [ID]={0}))
FROM [dbo].[GuestRecord]
WHERE [RoomId]=(SELECT [RoomId] FROM [dbo].[GuestRecord] WHERE [ID]={0}) AND [LeaveDate] IS NULL AND [ID]={0}", id);
SqlDataReader r = DBHerper.Reader(sql);
if (r.Read())
{
outInfo.Deposit = Convert.ToDouble(r[0]);
outInfo.RoomTotal = Convert.ToDouble(r[1]);
if (r[2].ToString().Equals(string.Empty))
{
outInfo.FoodTotal = 0;
}
else
{
outInfo.FoodTotal = Convert.ToDouble(r[2]);
}
outInfo.TotalAmount = outInfo.FoodTotal + outInfo.RoomTotal - outInfo.Deposit;
}
r.Close();
return(outInfo);
}
/// <summary>
/// 查询全部房间类型名称
/// </summary>
/// <returns>房间类型集合</returns>
public List <string> Querys()
{
List <string> s = new List <string>();
string sql = "SELECT [TypeName] FROM [dbo].[RoomType] GROUP BY [TypeName]";
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
s.Add(r[0].ToString());
}
r.Close();
return(s);
}
/// <summary>
/// 添加树列表
/// </summary>
/// <param name="treeView">需要添加的控件</param>
public void ShowTreeView(TreeView treeView)
{
string all = "全部";
TreeNode node = treeView.Nodes.Add(all);
string sql = string.Format(@"SELECT TypeName FROM roomtype
WHERE TypeWindow=1");
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
string a = r[0].ToString();
node.Nodes.Add(a);
}
r.Close();
}
/// <summary>
/// 通过房间ID查询房间类型ID
/// </summary>
/// <param name="roomId">房间ID</param>
/// <returns>房间信息对象</returns>
public RoomInfo GetRoomTypeId(string roomId)
{
RoomInfo room = new RoomInfo();
string sql = string.Format(@"SELECT [RoomTypeId],[RoomStateId]
FROM [dbo].[Room]
WHERE [RoomId]='{0}'", roomId);
SqlDataReader r = DBHerper.Reader(sql);
if (r.Read())
{
room.RoomRype = r[0].ToString();
room.RoomState = r[1].ToString();
}
r.Close();
return(room);
}
/// <summary>
/// 获取食品信息
/// </summary>
/// <returns>商品集合</returns>
public List <FoodList> GetFood()
{
List <FoodList> f = new List <FoodList>();
string sql = string.Format(@"SELECT [FoodId], [FoodName], [Unit], [Price]
FROM [dbo].[FoodList]");
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
FoodList fl = new FoodList()
{
FoodId = Convert.ToInt32(r[0]), FoodName = r[1].ToString(), Unit = r[2].ToString(), Price = Convert.ToDouble(r[3])
};
f.Add(fl);
}
r.Close();
return(f);
}
/// <summary>
/// 获取全部房间类型
/// </summary>
/// <returns>房间类型列表</returns>
public List <RoomTypeList> GetTypeList()
{
List <RoomTypeList> list = new List <RoomTypeList>();
string sql = string.Format(@"SELECT TypeName+CASE WHEN TypeWindow=0 THEN ' | 无窗' WHEN TypeWindow=1 THEN ' | 有窗' END,TypeId
FROM RoomType");
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
RoomTypeList l = new RoomTypeList()
{
TypeName = r[0].ToString(), TypeID = r[1].ToString()
};
list.Add(l);
}
r.Close();
return(list);
}
/// <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);
}
/// <summary>
/// 根据类型绑定房间
/// </summary>
/// <param name="typeId">房间类型ID</param>
/// <returns>房间对象集合</returns>
public List <Room> BindingRoom(string typeId)
{
List <Room> list = new List <Room>();
string sql = string.Format(@"select [RoomId],[TypePrice]
from [dbo].[Room] R,[dbo].[RoomType] T
where(R.RoomTypeId=T.TypeId) and([RoomTypeId]='{0}' and [RoomStateId]='2')", typeId);
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
Room room = new Room()
{
RoomId = Convert.ToInt32(r["RoomId"]), TypePrice = Convert.ToDouble(r["TypePrice"])
};
list.Add(room);
}
r.Close();
return(list);
}
/// <summary>
/// 绑定房间类型
/// </summary>
/// <returns>房间类型对象集合</returns>
public List <RoomType> BindingRoomType()
{
List <RoomType> list = new List <RoomType>();
string sql = string.Format(@"SELECT [TypeName]+CASE WHEN [TypeWindow]=0 THEN ' | 无窗'
WHEN [TypeWindow]=1 THEN ' | 有窗'END,[TypeId]
FROM [dbo].[RoomType]");
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
RoomType type = new RoomType()
{
TypeName = r[0].ToString(), TypeId = Convert.ToInt32(r[1])
};
list.Add(type);
}
r.Close();
return(list);
}
/// <summary>
/// 获取全部房间信息系
/// </summary>
/// <returns>房间信息集合</returns>
public List <RoomInfo> GetRoomInfo()
{
List <RoomInfo> info = new List <RoomInfo>();
string sql = string.Format(@"SELECT [RoomId],[TypeName],CASE WHEN [TypeWindow]=1 THEN '有' WHEN [TypeWindow]=0 THEN '无'END,[RoomStateName]
FROM [dbo].[Room] R,[dbo].[RoomType] T,[dbo].[RoomState] S
WHERE R.RoomTypeId=T.TypeId AND R.RoomStateId=S.RoomStateId");
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
RoomInfo roomInfo = new RoomInfo()
{
Number = r[0].ToString(), RoomRype = r[1].ToString(), Window = r[2].ToString(), RoomState = r[3].ToString()
};
info.Add(roomInfo);
}
r.Close();
return(info);
}
/// <summary>
/// 获取全部房间信息并筛选
/// </summary>
/// <param name="id">房间ID</param>
/// <returns>房间信息列表</returns>
public List <RoomTypeList> GetRoomInfo(string id)
{
List <RoomTypeList> list = new List <RoomTypeList>();
string sql = string.Format(@"SELECT RoomId,RoomStateName,TypeName+CASE WHEN TypeWindow=0 THEN ' | 无窗' WHEN TypeWindow=1 THEN ' | 有窗' END
FROM RoomType AS T,Room AS R,RoomState AS S
WHERE (R.RoomTypeId=T.TypeId AND R.RoomStateId=S.RoomStateId) AND RoomId LIKE '%{0}%'", id);
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
RoomTypeList l = new RoomTypeList()
{
TypeID = r[0].ToString(), StateName = r[1].ToString(), TypeName = r[2].ToString()
};
list.Add(l);
}
r.Close();
return(list);
}
/// <summary>
/// 查询全部房间类型信息
/// </summary>
/// <returns></returns>
public List <RoomType> QueryInfo()
{
List <RoomType> li = new List <RoomType>();
string sql = string.Format(@"SELECT [TypeId],[TypeName],[BedNum],
CASE WHEN [TypeWindow]=1 THEN '有' WHEN [TypeWindow]=0 THEN '无'END ,
[TypePrice]
FROM [dbo].[RoomType]");
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
RoomType type = new RoomType()
{
TypeId = Convert.ToInt32(r[0]), TypeName = r[1].ToString(), BedNum = Convert.ToInt32(r[2]), TypeWindows = r[3].ToString(), TypePrice = Convert.ToDouble(r[4])
};
li.Add(type);
}
r.Close();
return(li);
}
/// <summary>
/// 筛选商品信息
/// </summary>
/// <param name="FoodName">商品名称</param>
/// <returns>商品信息集合</returns>
public List <FoodList> GetFood(string FoodName)
{
List <FoodList> f = new List <FoodList>();
string sql = string.Format(@"SELECT [FoodId], [FoodName], [Unit], [Price]
FROM [dbo].[FoodList]");
if (!FoodName.Trim().Equals(string.Empty))
{
sql += string.Format(" WHERE [FoodName] like '%{0}%'", FoodName);
}
SqlDataReader r = DBHerper.Reader(sql);
while (r.Read())
{
FoodList fl = new FoodList()
{
FoodId = Convert.ToInt32(r[0]), FoodName = r[1].ToString(), Unit = r[2].ToString(), Price = Convert.ToDouble(r[3])
};
f.Add(fl);
}
r.Close();
return(f);
}