/// <summary>
/// 删除房间
/// </summary>
/// <param name="id">房间ID</param>
/// <returns>是否删除成功</returns>
public bool DeleteRoom(string id)
{
string sql = string.Format(@"DELETE Room
WHERE RoomId='{0}'", id);
return((Convert.ToInt32(DBHerper.NonQuery(sql)) != 0)? true : false);
}
/// <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>
/// <param name="id">房间ID</param>
/// <returns>是否有</returns>
public bool JudgeRoom(string id)
{
string sql = string.Format(@"SELECT count(*)
FROM GuestRecord
WHERE RoomId='{0}'", id);
return((Convert.ToInt32(DBHerper.Scalar(sql)) != 0) ? true : false);
}
/// <summary>
/// 修改房间信息
/// </summary>
/// <param name="room">房间信息对象</param>
/// <returns>是否修改成功</returns>
public bool UpdateRoomInfo(Room room)
{
string sql = string.Format(@"UPDATE [dbo].[Room]
SET [RoomTypeId]='{0}', [RoomStateId]='{1}'
WHERE [RoomId]='{2}'", room.RoomTypeId, room.RoomStateId, room.RoomId);
return((DBHerper.NonQuery(sql) == 1) ? true : false);
}
/// <summary>
/// 增加商品
/// </summary>
/// <param name="food">商品对象</param>
/// <returns>增加的商品编号</returns>
public object AddFoods(FoodList food)
{
string sql = string.Format(@"INSERT [dbo].[FoodList] ([FoodName], [Unit], [Price])
VALUES('{0}','{1}','{2}') SELECT @@IDENTITY", food.FoodName, food.Unit, food.Price);
object judge = DBHerper.Scalar(sql);
return(judge);
}
/// <summary>
/// 修改房间类型
/// </summary>
/// <param name="type">type</param>
/// <returns>是否修改成功</returns>
public bool UpdateType(RoomType type)
{
string sql = string.Format(@"UPDATE [dbo].[RoomType]
SET [TypeName]='{0}',[TypeWindow]='{1}',[BedNum]='{2}',[TypePrice]='{3}'
WHERE [TypeId]='{4}'", type.TypeName, type.TypeWindow, type.BedNum, type.TypePrice, type.TypeId);
return((DBHerper.NonQuery(sql) == 1) ? true : false);
}
/// <summary>
/// 新增房间类型
/// </summary>
/// <param name="type">房间类型对象</param>
/// <returns>新增的类型编号</returns>
public object AddType(RoomType type)
{
string sql = string.Format(@"INSERT [dbo].[RoomType] ([TypeName], [TypeWindow], [BedNum], [TypePrice])
VALUES('{0}','{1}','{2}','{3}') SELECT @@IDENTITY", type.TypeName, type.TypeWindow, type.BedNum, type.TypePrice);
object obj = DBHerper.Scalar(sql);
return(obj);
}
/// <summary>
/// 删除房间类型
/// </summary>
/// <param name="id">id</param>
/// <returns>是否删除成功</returns>
public bool DeleteType(string id)
{
string sql = string.Format(@"DELETE [dbo].[RoomType]
WHERE [TypeId]='{0}'", id);
object judge = DBHerper.NonQuery(sql);
return((Convert.ToInt32(judge) != 0) ? true : false);
}
/// <summary>
/// 获取房间状态
/// </summary>
/// <param name="roomId">房间编号</param>
/// <returns>房间状态</returns>
public int GetStatistics(string roomId)
{
string sql = string.Format(@"SELECT [RoomStateId]
FROM [dbo].[Room]
WHERE [RoomId]='{0}'", roomId);
int judge = Convert.ToInt32(DBHerper.Scalar(sql));
return(judge);
}
/// <summary>
/// 增加客户信息
/// </summary>
/// <param name="info">客户信息</param>
/// <returns>客户编号</returns>
public string AddGuestInfo(GuestInfo info)
{
string sql = string.Format(@"INSERT [dbo].[GuestInfo]([Name], [CardNo], [Phone], [Sex])
VALUES ('{0}','{1}','{2}','{3}')
SELECT @@IDENTITY", info.Name, info.CardNo, info.Phone, info.Sex);
string guestId = DBHerper.Scalar(sql).ToString();
return(guestId);
}
/// <summary>
/// 判断类型的房间是否存在
/// </summary>
/// <param name="id">类型ID</param>
/// <returns>是否存在</returns>
public bool JudgeTypeId(string id)
{
string sql = string.Format(@"SELECT COUNT(*)
FROM [dbo].[Room]
WHERE [RoomTypeId] ='{0}'", id);
object judge = DBHerper.Scalar(sql);
return((Convert.ToInt32(judge) != 0) ? true : false);
}
/// <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="RoomTotal">房款</param>
/// <param name="FoodTotal">消费</param>
/// <param name="id">房间号</param>
public void SCheckOut(string RoomTotal, string FoodTotal, string id)
{
string sql = string.Format(@"UPDATE [dbo].[GuestRecord]
SET [LeaveDate]=GETDATE(),[RoomTotal]='{0}',[FoodTotal]='{1}'
WHERE [RoomId]='{2}'
UPDATE [dbo].[Room]
SET [RoomStateId]=2
WHERE [RoomId]='{2}'", RoomTotal, FoodTotal, id);
DBHerper.NonQuery(sql);
}
/// <summary>
/// 修改客户信息
/// </summary>
/// <param name="info">客户信息</param>
/// <returns>客户编号</returns>
public string UpdateGuestInfo(GuestInfo info)
{
string sql = string.Format(@"UPDATE [dbo].[GuestInfo]
SET [Name]='{0}',[Phone]='{1}',[Sex]='{2}'
WHERE [CardNo]='{3}'
SELECT [ID]
FROM [dbo].[GuestInfo]
WHERE [CardNo]='{3}'", info.Name, info.Phone, info.Sex, info.CardNo);
string guestId = DBHerper.Scalar(sql).ToString();
return(guestId);
}
/// <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="id">商品ID</param>
/// <returns>是否删除成功</returns>
public bool DeleteFoods(string id)
{
string sql = string.Format(@"DELETE [dbo].[FoodList]
WHERE [FoodId]='{0}'", id);
int judge = DBHerper.NonQuery(sql);
if (judge == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <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是否有订单
/// </summary>
/// <param name="id">商品ID</param>
/// <returns>是否有订单</returns>
public bool JudgeFoodsOrders(string id)
{
string sql = string.Format(@"SELECT COUNT(*)
FROM [dbo].[FoodOrder]
WHERE [FoodId]='{0}'", id);
int judge = Convert.ToInt32(DBHerper.Scalar(sql));
if (judge != 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 修改商品
/// </summary>
/// <param name="food">商品对象</param>
/// <returns>是否修改成功</returns>
public bool UpdateFoods(FoodList food)
{
string sql = string.Format(@"UPDATE [dbo].[FoodList]
SET [FoodName]='{0}',[Unit]='{1}',[Price]='{2}'
WHERE [FoodId]='{3}'", food.FoodName, food.Unit, food.Price, food.FoodId);
int judge = Convert.ToInt32(DBHerper.NonQuery(sql));
if (judge != 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 通过身份证判断客户是否存在
/// </summary>
/// <param name="id">身份证</param>
/// <returns>是否存在</returns>
public bool CheckExist(string id)
{
string sql = string.Format(@"SELECT COUNT(*)
FROM [dbo].[GuestInfo]
WHERE [CardNo]='{0}'", id);
int judge = Convert.ToInt32(DBHerper.Scalar(sql));
if (judge != 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加房间信息
/// </summary>
/// <param name="room">房间对象</param>
/// <returns>是否增加成功</returns>
public bool AddRoom(Room room)
{
string sql = string.Format(@"SELECT count(*)
FROM Room
WHERE RoomId='{0}'", room.RoomId);
if (Convert.ToInt32(DBHerper.Scalar(sql)) != 0)
{
return(false);
}
else
{
sql = string.Format(@"INSERT Room VALUES ('{0}','{1}','{2}')", room.RoomId, room.RoomTypeId, room.RoomStateId);
return((Convert.ToInt32(DBHerper.NonQuery(sql)) == 1) ? true : false);
}
}
/// <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>
/// <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>
/// <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="room">房间对象</param>
/// <param name="id">要修改的id</param>
/// <returns>是否修改成功</returns>
public bool UpdateRoom(Room room, string id)
{
string sql = string.Format(@"SELECT count(*)
FROM Room
WHERE RoomId !='{0}' and RoomId='{1}'", id, room.RoomId);
if (Convert.ToInt32(DBHerper.Scalar(sql)) != 0)
{
return(false);
}
else
{
sql = string.Format(@"UPDATE Room
SET RoomId='{0}',RoomTypeId='{1}',RoomStateId='{2}'
WHERE RoomId='{3}'", room.RoomId, room.RoomTypeId, room.RoomStateId, id);
return((Convert.ToInt32(DBHerper.NonQuery(sql)) == 1) ? true : false);
}
}
/// <summary>
/// 检查用户名和密码是否正确
/// </summary>
/// <param name="admin">管理员对象</param>
/// <returns>是否正确</returns>
public bool Check(Admin admin)
{
Encrypt encrypt = new Encrypt();
string pwd = encrypt.EncryotPwd(admin.Pwd);
string sql = string.Format(@"SELECT COUNT(*)
FROM [dbo].[Admin]
WHERE [Admins]='{0}' AND [Pwd]='{1}'", admin.Admins, pwd);
try
{
int judge = Convert.ToInt32(DBHerper.Scalar(sql));
return((judge == 1) ? true : false);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return(false);
}
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="admin">管理员对象</param>
/// <returns>修改是否成功</returns>
public bool UpadtePwd(Admin admin)
{
Encrypt encrypt = new Encrypt();
string pwd = encrypt.EncryotPwd(admin.Pwd);
string sql = string.Format(@"UPDATE [dbo].[Admin]
SET [Pwd]='{0}'
WHERE [Admins]='{1}'", pwd, admin.Admins);
try
{
int judge = DBHerper.NonQuery(sql);
return((judge == 1) ? true : false);
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return(false);
}
}
/// <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="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);
}