/// <summary>
/// DateGridView的绑定
/// </summary>
public void BindResult()
{
//创建数据库连接
DBHelper dbhelper = new DBHelper();
try
{
// 查询年级的sql语句
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT * From RoomDetailDB");
// 填充DataSet
SqlDataAdapter adapter = new SqlDataAdapter(sql.ToString(), dbhelper.Connection);
//绑定数据源
if (ds.Tables != null)
{
ds.Tables.Clear();
this.dgvRoomDetail.DataSource = null;
this.dgvRoomDetail.Rows.Clear();
}
adapter.Fill(ds);
this.dgvRoomDetail.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("系统发生错误！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void BindRoom()
{
//创建数据库连接
DBHelper dbhelper = new DBHelper();
try
{
// 查询科目的sql语句
string sql = "SELECT [RoomID] FROM [RoomDetailDB] where [RoomStatus] = 'empty'";
// 打开数据库连接
dbhelper.OpenConnection();
//填充DataSet
SqlDataAdapter adapter = new SqlDataAdapter(sql, dbhelper.Connection);
ds = new DataSet();
adapter.Fill(ds);
//绑定数据源
this.cmbRoom.DataSource = ds.Tables[0];
this.cmbRoom.ValueMember = "RoomID";
this.cmbRoom.DisplayMember = "RoomID";
}
catch (Exception ex)
{
MessageBox.Show("系统发生错误！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
//关闭连接
dbhelper.CloseConnection();
}
}
public void ShowManager()
{
//创建数据库连接
DBHelper dbhelper = new DBHelper();
DataSet ds = new DataSet();
try
{
// 查询年级的sql语句
StringBuilder sql = new StringBuilder("SELECT ID,Phone FROM ManagerDB");
//填充DataSet
//SqlDataAdapter adapter = new SqlDataAdapter(sql.ToString(), dbhelper.Connection);
SqlDataAdapter adapterStudent = new SqlDataAdapter();
SqlCommand command = new SqlCommand(sql.ToString(), dbhelper.Connection);
adapterStudent.SelectCommand = command;
// 填充前，先清空原有的数据
if (ds.Tables["ManagerDB"] != null)
{
ds.Tables["ManagerDB"].Clear();
}
adapterStudent.Fill(ds, "ManagerDB");
//绑定数据源
this.dataGridView1.DataSource = ds.Tables["ManagerDB"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "输入提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void btSave_Click(object sender, EventArgs e)
{
if (txtNewRecPhone.Text == string.Empty || txtNewRecPwd.Text == string.Empty || txtNewRecAccount.Text == string.Empty)
{
MessageBox.Show("信息输入不完全！请重新输入");
return;
}
else
{
DBHelper dbhelper = new DBHelper();
string CAPTION = "输入提示";
StringBuilder sb = new StringBuilder();//查询用的SQL语句
sb.AppendLine("insert into ReceptionistDB values ('"+txtNewRecAccount.Text+"','"+txtNewRecPwd.Text+"','"+txtNewRecPhone.Text+"')");
SqlCommand command = new SqlCommand(sb.ToString(), dbhelper.Connection);
// 打开数据库连接
dbhelper.OpenConnection();
// 执行命令
int result = command.ExecuteNonQuery();
// 根据操作结果给出提示信息
if (result < 1)
{
MessageBox.Show("增加失败，请重试！", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("增加成功！", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
dbhelper.CloseConnection();
}
this.Close();
}
public void Update(string s,string ID)
{
DBHelper dbhelper = new DBHelper();
SqlCommand command;
dbhelper.OpenConnection();
sb.AppendLine("update ReceptionistDB set Pwd = '" + s + "' where ID = '"+ID+"'");
command = new SqlCommand(sb.ToString(), dbhelper.Connection);
command.ExecuteNonQuery();
dbhelper.CloseConnection();
}
private void btQuery_Click(object sender, EventArgs e)
{
if (txtQueryManagerPhone.Text == string.Empty)
{
MessageBox.Show("请输入经理姓名");
return;
}
else
{
DBHelper dbhelper = new DBHelper();
DataSet ds = new DataSet();
try
{
// 查询年级的sql语句
StringBuilder sql = new StringBuilder("SELECT ID,Phone FROM ManagerDB where ID = '" + txtQueryManagerPhone.Text + "'");
//填充DataSet
//SqlDataAdapter adapter = new SqlDataAdapter(sql.ToString(), dbhelper.Connection);
SqlDataAdapter adapterStudent = new SqlDataAdapter();
SqlCommand command = new SqlCommand(sql.ToString(), dbhelper.Connection);
adapterStudent.SelectCommand = command;
dbhelper.OpenConnection();
int count = command.ExecuteNonQuery();
if (count <= 0)
{
MessageBox.Show("未找到该经理");
return;
}
else
{
// 填充前，先清空原有的数据
if (ds.Tables["ManagerDB"] != null)
{
ds.Tables["ManagerDB"].Clear();
}
adapterStudent.Fill(ds, "ManagerDB");
//绑定数据源
this.dataGridView1.DataSource = ds.Tables["ManagerDB"];
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "输入提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally {
dbhelper.CloseConnection();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
DBHelper dbhelper = new DBHelper();
string CAPTION = "输入提示";
StringBuilder sb = new StringBuilder();//查询用的SQL语句
sb.AppendLine("Select * from viplistDB where ID = '"+txtVipID.Text+"'");
SqlCommand command1 = new SqlCommand(sb.ToString(), dbhelper.Connection);
//打开链接
dbhelper.OpenConnection();
//执行查询语句
int count = Convert.ToInt32( command1.ExecuteScalar());
if (count > 0)
{
MessageBox.Show("该用户已经是VIP");
return;
}
dbhelper.CloseConnection();
try
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO VIPlistDB Values ('"+this.txtVipID.Text+"','"+this.txtVipName.Text+"')");
// 创建command对象
SqlCommand command = new SqlCommand(sql.ToString(), dbhelper.Connection);
// 打开数据库连接
dbhelper.OpenConnection();
// 执行命令
int result = command.ExecuteNonQuery();
// 根据操作结果给出提示信息
if (result < 1)
{
MessageBox.Show("增加失败，请重试！", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("增加成功！", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show("系统出现错误！", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
//关闭数据库连接
dbhelper.CloseConnection();
}
}
public void Update()
{
DBHelper dbhelper = new DBHelper();
SqlCommand command;
dbhelper.OpenConnection();
sb.AppendLine("update RoomDetailDB set RoomStatus = 'full',GuestName='" + guest.GuestName + "',GuestID='" + guest.GuestID + "',");
sb.AppendLine("VIP='" + guest.Vip + "',CheckInDate='" + hotelroom.CheckInDate + "',");
sb.AppendLine("CheckOutDate='" + hotelroom.ChechOutDate + "'");
sb.AppendLine("where roomid = '" + hotelroom.HotelRoomNo + "'");
command = new SqlCommand(sb.ToString(), dbhelper.Connection);
command.ExecuteNonQuery();
dbhelper.CloseConnection();
}
private void btTurnoverQuery_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
float sum = 0;
DBHelper dbhelper = new DBHelper();
string sql = "select * from IncomeDB where date between'"+dtpBeginDate.Value.ToShortDateString()+"'and '"+dtpEndDate.Value.ToShortDateString()+"'";
dbhelper.OpenConnection();
SqlCommand command = new SqlCommand(sql,dbhelper.Connection);
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
sum=sum+Convert .ToSingle( dr["Income"]);
}
MessageBox.Show("这一段时间累计收入"+sum.ToString ()+"元");
dbhelper.CloseConnection();
}
/// <summary>
/// 检索用户名、密码是否存在
/// </summary>
/// <param name="message">表示查询状态</param>
/// <returns>true的时候表示能在数据库中查找到对应的一项，false的时候表示不能在数据库中查找出来</returns>
public bool CheckUser(ref string message)
{
bool isVaildUser = false;
string id =txtReceptionistID. Text.Trim();
string pwd =this.txtReceptionistPwd .Text.Trim();
StringBuilder sb = new StringBuilder();//查询用的SQL语句
sb.AppendFormat("SELECT COUNT(*) FROM [ReceptionistDB] WHERE [ID]='{0}' AND [Pwd]='{1}'",id,pwd);
//执行查询
int count = 0;
DBHelper dbhelper = new DBHelper();
try
{
//创建command语句
SqlCommand command = new SqlCommand(sb.ToString(), dbhelper.Connection);
//打开链接
dbhelper.OpenConnection();
//执行查询语句
count = (int)command.ExecuteScalar();
//如果结果大于0，则表示通过账号密码验证
if (count > 0)
{
isVaildUser = true;
}
else
{
message = "用户名或密码不存在";
isVaildUser = false;
}
}
catch (Exception ex)
{
message = "数据库出错，请改正后再试";
isVaildUser = false;
}
finally
{
//关闭数据库
dbhelper.CloseConnection();
}
return isVaildUser;//当isVaildUser为false的时候，表示验证失败，为true的时候表示验证成功
}
private void btConfirmation_Click(object sender, EventArgs e)
{
try
{
DBHelper db = new DBHelper();
string sql = "Select * from RoomDetailDB where RoomID = '" + txtRoomID.Text + "'";
db.OpenConnection();
SqlCommand command = new SqlCommand(sql, db.Connection);
SqlDataReader dr = command.ExecuteReader();
dr.Read();
txtRoomPrice.Text = dr["RoomPrice"].ToString();
if (dr["RoomStatus"].ToString() == "empty")
{
rbempty.Checked = true;
rbbusy.Checked = false;
}
else
{
rbempty.Checked = false;
rbbusy.Checked = true;
}
txtGuestName.Text = dr["GuestName"].ToString();
txtGuestID.Text = dr["GuestID"].ToString();
if (dr["VIP"].ToString() == "yes")
{
rbVIP.Checked = true;
rbNotVIP.Checked = false;
}
else
{
rbVIP.Checked = false;
rbNotVIP.Checked = true;
}
dtpcheckintime.Value = Convert.ToDateTime(dr["CheckInDate"]);
dtpCheckOut.Value = Convert.ToDateTime(dr["CheckOutDate"]);
db.CloseConnection();
}
catch (Exception ex)
{
MessageBox.Show("无此房间或房间为空！");
}
}
public void CheckOut()
{
//结账部分
DBHelper dbhelper = new DBHelper();
string CAPTION = "输入提示";
StringBuilder sb = new StringBuilder();//查询用的SQL语句
sb.AppendLine("Select * from IncomeDB where Date = '" + dtpCheckOut.Value.ToShortDateString() + "'");
SqlCommand command1 = new SqlCommand(sb.ToString(), dbhelper.Connection);
//打开链接
dbhelper.OpenConnection();
//执行查询语句
int count = Convert.ToInt32(command1.ExecuteScalar());
if (count > 0)
{
SqlDataReader dr = command1.ExecuteReader();
dr.Read();
float sum = Convert.ToSingle(dr["Income"]);
TimeSpan ts = dtpCheckOut.Value - dtpcheckintime.Value;
int day = ts.Days;
//MessageBox.Show(day.ToString());
float price = Convert.ToSingle(txtRoomPrice.Text);
float k = 1;
if (rbVIP.Checked == true)
k = 0.9f;
sum = sum + price * day * k;
MessageBox.Show("需要支付"+(price * day * k).ToString()+"元");
dr.Close();
string sql = "update IncomeDB set Income ='" + sum + "' where date='" + dtpCheckOut.Value.ToShortDateString() + "'";
SqlCommand command2 = new SqlCommand(sql, dbhelper.Connection);
command2.ExecuteNonQuery();
}
else
{
float sum = 0;
TimeSpan ts = dtpCheckOut.Value - dtpcheckintime.Value;
int day = ts.Days;
//MessageBox.Show(day.ToString());
float price = Convert.ToSingle(txtRoomPrice.Text);
float k = 1;
if (rbVIP.Checked == true)
k = 0.9f;
sum = sum + price * day * k;
MessageBox.Show("需要支付" + (price * day * k).ToString() + "元");
string sql = "insert into IncomeDB values ('" + sum + "' ,'" + dtpCheckOut.Value.ToShortDateString() + "')";
SqlCommand command2 = new SqlCommand(sql, dbhelper.Connection);
command2.ExecuteNonQuery();
}
//房间状态更新部分
DBHelper dbhelper1 = new DBHelper();
StringBuilder sb1 = new StringBuilder();//查询用的SQL语句
sb.AppendLine("update RoomDetailDB set RoomStatus = 'empty',GuestName= null,GuestID=null,");
sb.AppendLine("VIP=null,CheckInDate=null,");
sb.AppendLine("CheckOutDate=null ");
sb.AppendLine("where roomid = '"+txtRoomID.Text+"'");
SqlCommand command3 = new SqlCommand(sb.ToString(), dbhelper.Connection);
command3.ExecuteNonQuery();
rm.BindResult();
this.Close();
}
private void btGuestIn_Click(object sender, EventArgs e)
{
hotelroom.HotelRoomNo = cmbRoom.SelectedValue.ToString();
hotelroom.CheckInDate = dtpGuestIn.Value;
hotelroom.Duration = Convert.ToInt32( txtDuration.Text);
hotelroom.ChechOutDate = dtpGuestIn.Value.AddDays(hotelroom.Duration);
guest.GuestID = txtGuestID.Text;
guest.GuestName = txtGuestName.Text;
//sb.AppendFormat("Select * from VIPlistDB where ID = '" + txtGuestID.Text + "'");
string sql = "Select * from VIPlistDB where ID = '"+txtGuestID.Text+"'";
int count = 0; // 数据库查询的结果
DBHelper dbhelper = new DBHelper();
try
{ // 创建Command命令
SqlCommand command = new SqlCommand(sql, dbhelper.Connection);
//SqlCommand command = new SqlCommand(sb.ToString(), dbhelper.Connection);
// 打开连接
dbhelper.OpenConnection();
// 执行查询语句
count = Convert.ToInt32(command.ExecuteScalar());
if (count > 0)
{
guest.Vip = IsVip.yes;
}
else
{
guest.Vip = IsVip.no;
}
//
// sql = "update RoomDetailDB set RoomStatus = 'full',GuestName='" + guest.GuestName + "',GuestID='" + guest.GuestID + "',VIP='" + guest.Vip + "',CheckInDate='"+hotelroom.CheckInDate+"',CheckOutDate='"+hotelroom.ChechOutDate+"' where roomid = '"+hotelroom.HotelRoomNo+"'";
}
catch (Exception ex)
{
MessageBox.Show( "系统发生错误，请稍后再试！");
}
finally
{
//关闭数据库连接
dbhelper.CloseConnection();
}
Update();
mfm.dgvRoomDetail.DataSource = null;
mfm.dgvRoomDetail.Rows.Clear();
mfm.BindResult();
this.Close();
}