一、运行效果
二、实现过程
①、房间管理
新增房间
string sql = string.Format(@"insert into RoomTable(RoomID, Floor, TypeID, StateID)
values('{0}','{1}','{2}','{3}')", tbFangHao.Text, tbLouCeng.Text, cbLeiXing.SelectedValue.ToString(), cbZhuangTai.SelectedValue.ToString());
if (db.ExecuteSQLCommand(sql) > 0)
{
string aac = string.Format("{0}房间新增成功!", tbFangHao.Text);
MessageBox.Show(aac);
}
激活房卡
string sql = string.Format(@"insert into RoomIDCard(RoomID, RoomCard)
values('{0}','{1}')", FangHao, tbFangKa.Text);
int a = db.ExecuteSQLCommand(sql);
if (a > 0)
{
string aaac = string.Format("激活成功!房号为:{0},房卡为:{1}", FangHao, tbFangKa.Text);
MessageBox.Show(aaac);
}
else
{
string aaac = string.Format("{0}房卡激活失败!", FangHao);
MessageBox.Show(aaac);
}
②、房客管理
新增房客
string gender = "男";
if (rbNv.Checked)
{
gender = "女";
}
//向顾客表中插入数据
string sql = string.Format(@"insert into CustomerTable( Name, CarID, Phone, Balance, Type, Sex, Age)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", tbXingMing.Text, tbShenFenZheng.Text, tbDianHua.Text, tbJinE.Text, cbLeiXing.SelectedValue, gender, tbNianLing.Text);
if (db.ExecuteSQLCommand(sql) > 0)
{
string sj = DateTime.Now.ToString();
//新增到充值记录表
try
{
sql = string.Format(@"
insert into CustomerRecharge(RechargeTime, RechargeAmount, RechargeIDCardNumber, Gifts)
values('{0}','{1}','{2}','{3}')", sj, tbJinE.Text, tbShenFenZheng.Text, "新开会员");
if (db.ExecuteSQLCommand(sql) == 0)
{
MessageBox.Show("新增到充值记录表中失败!");
}
}
catch (Exception er)
{
MessageBox.Show(er.Message);
}
finally
{
db.CloseConnection();
}
string aacc = string.Format("{0}恭喜您成为{1}", tbXingMing.Text, cbLeiXing.Text);
MessageBox.Show(aacc);
this.Close();
}
else
{
MessageBox.Show("添加失败!");
}
新增房客折扣类型
string sql = string.Format(@"insert into CustomerTypeTable(Grade, Discount)
values('{0}','{1}')", tbLeiXing.Text, tbZheKou.Text);
if (db.ExecuteSQLCommand(sql) > 0)
{
MessageBox.Show("添加成功!");
}
③、开房管理
预定房间
int zhi = 0;
for (int i = 0; i
{
try
{
string sql = string.Format(@"insert into PredeterminedTable(reservationNumber, Name, Phone, RoomID, RoomType, PreconditioningTime, PreDepartureTime, Operator, Price, Type)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')"
, tbDingDanHao.Text, txtXingMing.Text, txtDianHua.Text, lvYiXuang.Items[i].SubItems[0].Text, lvYiXuang.Items[i].SubItems[1].Text, tpRuZhu.Text, tpYuLi.Text, "1", lvYiXuang.Items[i].SubItems[2].Text, "预定中");
if (db.ExecuteSQLCommand(sql) > 0)
{
zhi++;
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
finally
{
db.CloseConnection();
}
}
MessageBox.Show("成功预定" + zhi + "房!");
this.Close();
房客开房
if (tbXingMing.Text.Equals("") || tbNianLing.Text.Equals("") || tbShengFenngZheng.Text.Equals(""))
{
MessageBox.Show("抱歉!请认真填写所有信息!");
return;
}
if (Convert.ToDouble(tbZhaoLing.Text)
{
MessageBox.Show("抱歉!实付金额不足!");
return;
}
if (lvYiXuang.Items.Count == 0)
{
MessageBox.Show("抱歉!请选择入住房间!");
return;
}
string sex = "男";
if (rbNv.Checked)
{
sex = "女";
}
//将新顾客信息新增到顾客表
if (tbGuKe.Text.Equals("是"))
{
try
{
//新顾客余额为0
string yuE = "0";
//将顾客信息新增顾客表
string sql = string.Format(@"insert into CustomerTable(Name, CarID, Phone, Balance, Type, Sex, Age)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", tbXingMing.Text, tbShengFenngZheng.Text, tbDianHua.Text, yuE, "1", sex, tbNianLing.Text);
if (db.ExecuteSQLCommand(sql) > 0)
{
//新增成功
}
else
{
MessageBox.Show("顾客信息填入顾客表失败");
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
finally
{
db.CloseConnection();
}
}
try
{
//获取已选房间数
int a = lvYiXuang.Items.Count;
int fangShu = 0;
//循环输入已选房间
for (int i = 0; i
{
string FangJianHao = lvYiXuang.Items[i].Text.ToString();
try
{
string sql = "";
double zheKou = 1;
if (tbZhiKou.Text != "不打折")
{
zheKou = Convert.ToDouble(tbZhiKou.Text.Substring(0, 1)) / 10;
}
double FangJias = Convert.ToDouble(lvYiXuang.Items[i].SubItems[2].Text);
//在订单表中插入相应数据
if (cbFuKuangFangShi.Text == "现金支付")
{
sql = string.Format(@"insert into [dbo].[OrderTable](OrderID, RoomID, UName, Age, Deposit, CheckInTime, PreDepartureTime, Phone, CustomerType, CompanyName, Remarks, State, Address, Discounts, AmountReceived, PaymentMethod, Operator, Price)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')"
, tbDingDanHao.Text, FangJianHao, tbXingMing.Text, tbNianLing.Text, "0", tpRuZhu.Text, tpYuLi.Text, tbDianHua.Text, tbLeiXing.Text, FangJias * zheKou, tbBeiZhu.Text, "新开单", tbDiZhi.Text, tbZhiKou.Text, FangJias * zheKou, cbFuKuangFangShi.Text, CaoZuoYuan, FangJias);
}
else if (cbFuKuangFangShi.Text == "账户余额")
{
sql = string.Format(@"insert into [dbo].[OrderTable](OrderID, RoomID, UName, Age, Deposit, CheckInTime, PreDepartureTime, Phone, CustomerType, CompanyName, Remarks, State, Address, Discounts, AmountReceived, PaymentMethod, Operator, Price)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')"
, tbDingDanHao.Text, FangJianHao, tbXingMing.Text, tbNianLing.Text, FangJias * zheKou, tpRuZhu.Text, tpYuLi.Text, tbDianHua.Text, tbLeiXing.Text, "0", tbBeiZhu.Text, "新开单", tbDiZhi.Text, tbZhiKou.Text, FangJias * zheKou, cbFuKuangFangShi.Text, CaoZuoYuan, FangJias);
}
else if (cbFuKuangFangShi.Text == "现金+余额")
{
double xianJins = Convert.ToDouble(tbXiangJin.Text) / lvYiXuang.Items.Count;
double yuEss = Convert.ToDouble(tbYuEFuKuan.Text) / lvYiXuang.Items.Count;
sql = string.Format(@"insert into [dbo].[OrderTable](OrderID, RoomID, UName, Age, Deposit, CheckInTime, PreDepartureTime, Phone, CustomerType, CompanyName, Remarks, State, Address, Discounts, AmountReceived, PaymentMethod, Operator, Price)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}')"
, tbDingDanHao.Text, FangJianHao, tbXingMing.Text, tbNianLing.Text, yuEss, tpRuZhu.Text, tpYuLi.Text, tbDianHua.Text, tbLeiXing.Text, xianJins, tbBeiZhu.Text, "新开单", tbDiZhi.Text, tbZhiKou.Text, xianJins + yuEss, cbFuKuangFangShi.Text, CaoZuoYuan, FangJias);
}
if (db.ExecuteSQLCommand(sql) > 0)
{
fangShu++;
//修改房间状态
sql = string.Format(@"update RoomTable set StateID = 2
where RoomID ='{0}'", FangJianHao);
if (db.ExecuteSQLCommand(sql) == 0)
{
MessageBox.Show("修改房间状态失败,请手动修改!");
}
}
else
{
MessageBox.Show("入住失败!");
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
finally
{
db.CloseConnection();
}
}
if (fangShu == a)
{
MessageBox.Show("入住成功!");
Activation at = new Activation();
at.FangHao = lvYiXuang.Items[0].Text.ToString();
at.ShowDialog();
//语音提示
string YuYinTiShi = string.Format("找零{0}元", tbZhaoLing.Text);
SpeechSynthesizer synth = new SpeechSynthesizer();
synth.Speak(YuYinTiShi);
if (YuDingDan != null)
{
this.DialogResult = DialogResult.OK;
}
Win32.AnimateWindow(this.Handle, 300, Win32.AW_HOR_POSITIVE | Win32.AW_HIDE | Win32.AW_SLIDE);
this.Close();
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
finally
{
db.CloseConnection();
}
房客签入
string sql = string.Format(@"insert into CheckInTable(OrderID, UName, CarID, RoomID, CheckInTime, PreDepartureTime, Operator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')", DingDan, tbXingMing.Text, tbShengFengZheng.Text, tbFangHao.Text, ruZhu, yuLi, CaoZuoYuan);
int a = db.ExecuteSQLCommand(sql);
if ((a > 0))
{
string tiShi = string.Format("{0}顾客入住信息添加成功!", tbFangHao.Text);
MessageBox.Show(tiShi);
this.DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show("新增到入住表失败");
}
房客结账签出
string danHao = lvTuiFang.Items[0].SubItems[0].Text;
string xingMing = lvTuiFang.Items[0].SubItems[2].Text;
string dianHua = lvTuiFang.Items[0].SubItems[3].Text;
string tradeTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", DateTimeFormatInfo.InvariantInfo);
if (cbFuKuanFangShi.Text == "现金结账")
{
//在入住表中插入结账信息!
string sql = string.Format(@"insert into CheckoutTable(OrderID, UName, Phone, TotalCost, BalancePayment, PaymentMethod, CheckoutTime, Operator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", tbDingDanHao.Text, xingMing, dianHua, Convert.ToDouble(tbXianJin.Text) + Convert.ToDouble(tbChaJia.Text) - Convert.ToDouble(tbZhaoLing.Text), tbYuEZhiFu.Text, cbFuKuanFangShi.Text, tradeTime, CaoZuoYuan);
if (db.ExecuteSQLCommand(sql)
{
MessageBox.Show("结账失败");
return;
}
}
else if (cbFuKuanFangShi.Text == "余额结账")
{
//扣除账户余额
string sql = string.Format(@"update [dbo].[CustomerTable] set Balance -= {0}
where Name ='{1}' and Phone ='{2}'", Convert.ToDouble(tbYuEZhiFu.Text), xingMing, dianHua);
if (db.ExecuteSQLCommand(sql) > 0)
{
string sqls = string.Format(@"insert into MembershipConsumptionList(Name, Phone, AmountOfMoney, Type, Time)
values('{0}','{1}','{2}','{3}','{4}')", xingMing, dianHua, Convert.ToDouble(tbYuEZhiFu.Text), "房间消费", tradeTime);
db.ExecuteSQLCommand(sqls);
}
else
{
MessageBox.Show("修改余额失败!");
}
//在入住表中插入结账信息!
sql = string.Format(@"insert into CheckoutTable(OrderID, UName, Phone, TotalCost, BalancePayment, PaymentMethod, CheckoutTime, Operator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", tbDingDanHao.Text, xingMing, dianHua, Convert.ToDouble(tbXianJin.Text) + Convert.ToDouble(tbChaJia.Text) - Convert.ToDouble(tbZhaoLing.Text), tbYuEZhiFu.Text, cbFuKuanFangShi.Text, tradeTime, CaoZuoYuan);
if (db.ExecuteSQLCommand(sql)
{
MessageBox.Show("结账失败");
return;
}
}
else
{
//扣除账户余额
string sql = string.Format(@"update [dbo].[CustomerTable] set Balance -= {0}
where Name ='{1}' and Phone ='{2}'", Convert.ToDouble(tbYuEZhiFu.Text), xingMing, dianHua);
if (db.ExecuteSQLCommand(sql) > 0)
{
string sqls = string.Format(@"insert into MembershipConsumptionList(Name, Phone, AmountOfMoney, Type, Time)
values('{0}','{1}','{2}','{3}','{4}')", xingMing, dianHua, Convert.ToDouble(tbYuEZhiFu.Text), "房间消费", tradeTime);
db.ExecuteSQLCommand(sqls);
}
else
{
MessageBox.Show("修改余额失败!");
}
//在入住表中插入结账信息!
sql = string.Format(@"insert into CheckoutTable(OrderID, UName, Phone, TotalCost, BalancePayment, PaymentMethod, CheckoutTime, Operator)
values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", tbDingDanHao.Text, xingMing, dianHua, Convert.ToDouble(tbXianJin.Text) + Convert.ToDouble(tbChaJia.Text) - Convert.ToDouble(tbZhaoLing.Text), tbYuEZhiFu.Text, cbFuKuanFangShi.Text, tradeTime, CaoZuoYuan);
if (db.ExecuteSQLCommand(sql)
{
MessageBox.Show("结账失败");
return;
}
}
for (int i = 0; i
{
//循环修改房间状态,改为空脏房
string xiuGaiFangJian = string.Format(@"update RoomTable set StateID=3
where RoomID='{0}'", lvTuiFang.Items[i].SubItems[1].Text);
if (db.ExecuteSQLCommand(xiuGaiFangJian)
{
MessageBox.Show("房间状态修改失败!");
}
//循环修改订单状态,改为已结账
string xiuGaiDingDan = string.Format(@"update OrderTable set State='已结账'
where RoomID='{0}'", lvTuiFang.Items[i].SubItems[1].Text);
if (db.ExecuteSQLCommand(xiuGaiDingDan)
{
MessageBox.Show("订单状态修改失败!");
}
//删除激活的房卡
string shanChuFangKa = string.Format(@"delete from RoomIDCard
where RoomID = '{0}'", lvTuiFang.Items[i].SubItems[1].Text);
if (db.ExecuteSQLCommand(shanChuFangKa)
{
//MessageBox.Show("删除房卡失败!");
}
//删除入住表
string shanChuRuZhu = string.Format(@"delete from CheckInTable
where OrderID='{0}'", lvTuiFang.Items[i].SubItems[0].Text);
db.ExecuteSQLCommand(shanChuRuZhu);
}
④、员工管理
新增员工
ring sql = string.Format(@"insert into UserTable(UserName, PassWord, CarID, EmployeeName, Jurisdiction)
values('{0}','{1}','{2}','{3}','{4}')", txtZhangHao.Text, txtMiMa2.Text, txtShengFenZheng.Text, txtXingMing.Text, cbQuanXian.Text);
if (db.ExecuteSQLCommand(sql) > 0)
{
MessageBox.Show("新增员工成功!");
this.Close();
}
else
{
MessageBox.Show("新增员工失败!");
}
⑤、商品操作
新增商品
string sql = string.Format(@"insert into Commodity(Name, Company, Number, BuyingPrice, RetailPrice, Type)
values('{0}','{1}','{2}','{3}','{4}','{5}')", tbMingCheng.Text, tbDanWei.Text, tbShuLiang.Text, tbJinHuoJia.Text, tbLingShouJia.Text, cbLeiXing.SelectedValue);
if (db.ExecuteSQLCommand(sql) > 0)
{
MessageBox.Show("新增商品成功!");
this.Close();
}
商品结账
if (lvGouWuChe.Items.Count > 0)
{
string sql;
for (int i = 0; i
{
try
{
sql = string.Format(@"update [dbo].[Commodity] set Number-={0}
where Name = '{1}'", Convert.ToInt32(lvGouWuChe.Items[i].SubItems[3].Text), lvGouWuChe.Items[i].SubItems[0].Text);
if (db.ExecuteSQLCommand(sql) == 0)
{
MessageBox.Show("商品数量修改失败!");
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
finally
{
db.CloseConnection();
}
}
sql = string.Format(@"insert into MerchandiseOrderTable(OrderTable, CollectMoney, PaymentMethod)
values('{0}','{1}','{2}')", DateTime.Now.ToString(), tbHeJi.Text, cbFuKuangFangShi.Text);
if (db.ExecuteSQLCommand(sql) > 0)
{
MessageBox.Show("购买成功!");
tbHeJi.Text = "";
tbShiShou.Text = "";
tbZhaoLing.Text = "";
lvGouWuChe.Items.Clear();
}
}
else
{
MessageBox.Show("请选择商品!");
}
⑥、数据库操作类
数据库操作类包括执行SQL语句,查询返回单个值/表格等操作。
using System;
using System.Data;
using System.Data.SqlClient;
namespace S1Hotel
{
class DBHelper
{
//创建数据库连接
private static string str = @"Data Source=.;Initial Catalog=S1Hotel;Integrated Security=True";
private SqlConnection con = new SqlConnection(str);
///
/// 查询多个值的方法
///
///
///
///
public SqlDataReader SelectDataReader(string sql)
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
return com.ExecuteReader(CommandBehavior.CloseConnection);//返回SqlDataReader关闭SqlConnection对象连接
}
///
/// 备用关闭数据库
///
public void CloseConnection()
{
con.Close();
}
///
/// 增删改
///
/// SQL语句
///
public int ExecuteSQLCommand(string sql)
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
int a = com.ExecuteNonQuery();
con.Close();
return a;
}
///
/// 查询一张表的数据
///
/// SQL语句
///
///
public DataSet GetDataSet(string sql, string biaoMing)
{
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds, biaoMing);
return ds;
}
///
/// 查询单个值并返回int类型
///
///
///
public int GetSingleIntValue(string sql)
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
int a = 0;
var t = com.ExecuteScalar();
//当com.ExecuteScalar()等于空时就返回0;
if (t == DBNull.Value || t == null)
{
}
else
{
a = (int)com.ExecuteScalar();
}
con.Close();
return a;
}
///
/// 查询单个值并返回double类型
///
///
///
public double GetSingleDoubleValue(string sql)
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
double a = 0;
var t = com.ExecuteScalar();
//当com.ExecuteScalar()等于空时就返回0;
if (t == DBNull.Value || t == null)
{
}
else
{
a = Convert.ToDouble(com.ExecuteScalar());
}
con.Close();
return a;
&nbnbsp; }
///
/// 计算时间重叠
///
///
///
///
///
///
public bool CalcTimeOverlap(DateTime a, DateTime b, DateTime x, DateTime y)
{
//时间无重叠
return b >= x && y >= a;
/*
if ((b
return false;
return true;
*/
}
}
}
三、项目结构图
四、数据库结构图
五、注意事项
测试账号和密码都是1。