客房管理系统VB+SQL.docx

数据库课程设计报告

题目：客房管理系统

时间：2008-6-27

客房管理系统
一、 课程设计的目的和意义
在当今经济和商务交往日益频繁的状况下，宾馆服务行业正面临客流量骤增的压力。越来越多的宾馆饭店都认识到传统的管理方法已经不能适合当今社会的需要，必须借助先进的计算机信息技术对宾馆服务进行管理。“客房管理系统”可以说是整个宾馆计算机信息系统的中心子系统，因为宾馆最主要的功能就是为旅客提供客房。设计客房管理这样一个系统，可以涉及到大多数SQL Server数据库的重要数据库对象、重要功能和特性，比如：视图、触发器和存储过程等。由此，通过这个课程设计可以加深对这些SQL Server数据库知识的学习、理解，积累在实际工程应用中运用各种数据库对象的经验，使学生掌握使用应用软件开发工具开发数据库管理系统的基本方法。在实用性方面，客房管理系统是很普遍的一种应用，选择该系统作为课程设计也可以为学生以后可能遇到的实际开发提供借鉴。
    本课程设计的主要目的是学习和练习SQL Server数据库的实际应用，所以选择SQL Server 2000作为数据库服务器。
二、 需求分析
系统主要实现对客房的管理功能。主要有管理员登陆、客户登记、房间管理、客户查询、房间查询、收入统计、管理员信息管理。
系统总体设计图如下：

三、 概念结构设计（E-R图）
四、 逻辑结构设计
将E-R图转换成关系模式：
管理员（编号，账号，密码，权限）
房 间（编号，类型，价格，状态，入住客户编号）
客 户（编号，姓名，性别，年龄，身份证号，住址，入住时间，预计天数，房间编号，退房时间，押金，住宿费，登记人）
关系规范化：
均已达到第三范式
五、 数据实施与维护
1、 建立数据库 打开SQL Server 2000企业管理器，建立数据库HotelRoom。
2、 添加数据表 在HotelRoom数据库中，添加表管理员信息表AdminInfo、房间信息表RoomInfo、客户信息表GuestInfo。

3、 建立关系图

4、 创建视图
CREATE VIEW dbo.ViewAdminInfo AS SELECT Aid, Aname, Alimit FROM dbo.AdminInfo 管理员信息视图
CREATE VIEW dbo.ViewAllCost AS SELECT SUM(Ccost) AS AllCost FROM dbo.GuestInfo 总收入视图
CREATE VIEW dbo.ViewBiaozhunCost AS SELECT SUM(a.Ccost) AS BiaozhunCost FROM dbo.GuestInfo a INNER JOIN dbo.RoomInfo b ON a.Rnum = b.Rnum WHERE (b.Rtype = '标准') 标准房收入视图
CREATE VIEW dbo.ViewCost AS SELECT dbo.ViewAllCost.AllCost,dbo.ViewPutongCost.putongCost, dbo.ViewBiaozhunCost.BiaozhunCost, dbo.ViewHaohuaCost.HaohuaCost FROM dbo.ViewAllCost CROSS JOIN dbo.ViewPutongCost CROSS JOIN dbo.ViewBiaozhunCost CROSS JOIN dbo.ViewHaohuaCost 收入视图
CREATE VIEW dbo.ViewEmptyRoomInfo AS SELECT Rnum, Rtype, Rprice, Rstates FROM dbo.RoomInfo WHERE (Rstates = '空') 空房信息视图
CREATE VIEW dbo.ViewFullRoomInfo AS SELECT dbo.RoomInfo.* FROM dbo.RoomInfo WHERE (Rstates = '满') 满房信息视图
CREATE VIEW dbo.ViewGuestInfo AS SELECT dbo.GuestInfo.* FROM dbo.GuestInfo 客户信息视图
CREATE VIEW dbo.ViewHaohuaCost AS SELECT SUM(a.Ccost) AS HaohuaCost FROM dbo.GuestInfo a INNER JOIN dbo.RoomInfo b ON a.Rnum = b.Rnum WHERE (b.Rtype = '豪华') 豪华房收入视图
CREATE VIEW dbo.ViewPutongCost AS SELECT SUM(a.Ccost) AS PutongCost FROM dbo.GuestInfo a INNER JOIN dbo.RoomInfo b ON a.Rnum = b.Rnum WHERE (b.Rtype = '普通') 普通房收入视图
CREATE VIEW dbo.ViewRoomInfo AS SELECT dbo.RoomInfo.* FROM dbo.RoomInfo 房间信息视图
5、 创建触发器
在表GuestInfo中，创建一个触发器：
CREATE TRIGGER insert_tri ON dbo.GuestInfo
FOR INSERT
AS
update RoomInfo
set Rstates='满',Cid=(select Cid from inserted)
where Rnum=(select Rnum from inserted)
功能：当向表GuestInfo中插入一个客户信息时，触发表RoomInfo的某房间的状态变为“满”
6、 备份数据库
给所建立的数据库进行备份
六、 界面设计与代码（用VB作为界面设计语言）
1、 登陆界面

Public Sub Command1_Click() ‘确定
If Trim(DataCombo1.Text) <> "" And Trim(Text1.Text) <> "" Then
Adodc1.RecordSource = "select * from AdminInfo where Aname='" + Trim(DataCombo1.Text) + "' and Amima='" + Trim(Text1.Text) + "'"
Adodc1.Refresh
If Adodc1.Recordset.RecordCount = 1 Then
Adodc1.Recordset.Close
frmMain.Show
Me.Hide
Else
MsgBox "错误的账号或密码！", vbInformation + vbOKOnly, "错误信息"
Text1.Text = ""
DataCombo1.SetFocus
End If
ElseIf DataCombo1.Text = "" Then
MsgBox "账号不能为空！", vbInformation + vbOKOnly, "错误信息"
DataCombo1.SetFocus
Else
MsgBox "密码不能为空！", vbInformation + vbOKOnly, "错误信息"
Text1.SetFocus
End If
End Sub
Private Sub Command2_Click() ‘取消
End
End Sub
Private Sub Text1_KeyPress(KeyAscii As Integer) ‘在文本框按下输入的事件
If KeyAscii = 13 Then
Command1_Click
End If
End Sub
Private Sub dataCombo1_KeyPress(KeyAscii As Integer) ‘在文本框按下输入的事件
If KeyAscii = 13 Then
Command1_Click
End If
End Sub
2、 客户登记界面

Private Sub Command3_Click() ‘客户登记 Private Sub Command4_Click() ‘退房结算
frmGuestAdd.Show frmCheckOut.Show
End Sub End Sub

Private Sub Command1_Click() ‘当前时间
txt6.Text = Trim(Now())
End Sub

Private Sub Command2_Click() ‘确定
If txt1.Text = "" Or txt2.Text = "" Or txt3.Text = "" Or txt4.Text = "" Or txt6.Text = "" Or txt8.Text = "" Or DataCombo1.Text = "" Then
MsgBox "请将蓝色部分填写完整！", vbInformation + vbOKOnly, "提示信息"
If txt1.Text = "" Then
txt1.SetFocus
ElseIf txt2.Text = "" Then
txt2.SetFocus
ElseIf txt3.Text = "" Then
txt3.SetFocus
ElseIf txt4.Text = "" Then
txt4.SetFocus
ElseIf txt6.Text = "" Then
txt6.SetFocus
ElseIf txt8.Text = "" Then
txt8.SetFocus
ElseIf DataCombo1.Text = "" Then
DataCombo1.SetFocus
End If
Else
Adodc3.RecordSource = "select Aid from ViewAdminInfo where Aname='" & frmLogin.DataCombo1.Text & "'"
Adodc3.Refresh
Dim aid As Integer
aid = Adodc3.Recordset.Fields(0)

Adodc4.RecordSource = "select * from GuestInfo"
Adodc4.Refresh
Adodc4.Recordset.AddNew
Adodc4.Recordset.Fields("Cname") = txt1.Text
Adodc4.Recordset.Fields("Csex") = txt2.Text
Adodc4.Recordset.Fields("Cage") = txt3.Text
Adodc4.Recordset.Fields("Cpnum") = txt4.Text
If txt6.Text <> "" Then
Adodc4.Recordset.Fields("Cadd") = txt5.Text
End If
Adodc4.Recordset.Fields("Cintime") = txt6.Text
If txt7.Text <> "" Then
Adodc4.Recordset.Fields("Cday") = txt7.Text
End If
Adodc4.Recordset.Fields("Cdeposit") = txt8.Text
Adodc4.Recordset.Fields("Rnum") = DataCombo1.Text
Adodc4.Recordset.Fields("Aid") = aid
Adodc4.Recordset.Update
frmMain.Adodc1.Refresh
frmMain.DataGrid1.Refresh
MsgBox "登记成功！", vbOKOnly + vbInformation, "提示信息"
Unload Me

End If
End Sub

Private Sub Command3_Click() ‘取消
Unload Me
End Sub

Private Sub DataCombo1_Change() ‘选择房间
Adodc2.RecordSource = "select * from ViewEmptyRoomInfo where Rnum='" + Trim(DataCombo1.Text) + " '"
Adodc2.Refresh
Set DataGrid1.DataSource = Adodc2
DataGrid1.Refresh
End Sub

Private Sub Command1_Click() ‘查看信息
If DataCombo1.Text <> "" Then
If Command1.Caption = "查看信息" Then
Adodc1.RecordSource = "select * from ViewGuestInfo where Rnum='" & DataCombo1.Text & "'and Couttime is null"
Adodc1.Refresh
RichTextBox1.Text = vbCrLf + " " + "编号：" + Trim(Adodc1.Recordset.Fields(0).Value) + " " + "姓名：" + Trim(Adodc1.Recordset.Fields(1).Value) + " " + "性别：" + Trim(Adodc1.Recordset.Fields(2).Value) + " " + "年龄：" + Trim(Adodc1.Recordset.Fields(3).Value) + vbCrLf + vbCrLf + " " + "身份证号：" + Trim(Adodc1.Recordset.Fields(4).Value) + vbCrLf + vbCrLf + " " + "入住时间：" + Trim(Adodc1.Recordset.Fields(6).Value) + vbCrLf + vbCrLf + " " + "押金：" + Trim(Adodc1.Recordset.Fields(10).Value) + " 元"
frame1.Caption = "显示信息-" + DataCombo1.Text
DataCombo1.Enabled = False
Command1.Caption = "取消"
Command2.Enabled = True
ElseIf Command1.Caption = "取消" Then
frame1.Caption = "显示信息"
RichTextBox1.Text = ""
DataCombo1.Enabled = True
DataCombo1.Text = ""
Command1.Caption = "查看信息"
Command2.Enabled = False
End If
Else
MsgBox "房间编号不能为空！", vbOKOnly + vbInformation, "错误信息"
DataCombo1.SetFocus
End If
End Sub

Private Sub Command2_Click() ‘费用结算
Me.Hide
frmPrice.Show
End Sub

Private Sub Command3_Click() ‘退出
Unload Me
End Sub

Dim time As Date
Dim e As Integer
Private Sub Command1_Click() ‘退房
If MsgBox("确定退房结帐吗？", vbOKCancel + vbInformation, "提示信息") = vbOK Then
Adodc3.RecordSource = "select Couttime,Ccost,Rnum from GuestInfo where Rnum='" & frmCheckOut.DataCombo1.Text & "'and Couttime is null" '"updata GuestInfo set Couttime=" & time & ",Ccost=" & e & ""
Adodc3.Refresh
Dim rnum As String
rnum = Adodc3.Recordset.Fields(2)
Adodc3.Recordset.Fields(0) = time
Adodc3.Recordset.Fields(1) = e
Adodc3.Recordset.Update

Adodc4.RecordSource = "select * from RoomInfo where Rnum='" & rnum & "'"
Adodc4.Refresh
Adodc4.Recordset.Fields(3) = "空"
Adodc4.Recordset.Fields(4) = Null
Adodc4.Recordset.Update

frmMain.Adodc1.Refresh
frmMain.Adodc6.Refresh
frmMain.DataGrid1.Refresh
MsgBox "退房成功！", vbOKOnly + vbInformation, "提示信息"
Unload frmCheckOut
Unload Me
Else
Unload frmCheckOut
Unload Me
End If
End Sub

Private Sub Command2_Click()
Unload frmCheckOut
Unload Me
End Sub

Private Sub Form_Load() ‘费用结算窗体加载事件
Adodc2.RecordSource = "select Rprice from ViewRoomInfo where Rnum=" & frmCheckOut.DataCombo1.Text & "" '查询房间的价格
Adodc2.Refresh
Dim price As Integer '房间价格
price = CInt(Adodc2.Recordset.Fields(0)) '获取房间价格
Label2.Caption = "房间：" + frmCheckOut.DataCombo1.Text + " " + "单价：" + CStr(price) + "元/天"
Adodc1.RecordSource = "select * from ViewGuestInfo where Rnum='" & frmCheckOut.DataCombo1.Text & "'and Couttime is null" '查询客户信息
Adodc1.Refresh
frame1.Caption = "编号：" + Trim(Adodc1.Recordset.Fields(0).Value) + " 姓名：" + Trim(Adodc1.Recordset.Fields(1).Value) + " 性别：" + Trim(Adodc1.Recordset.Fields(2).Value) + " 年龄：" + Trim(Adodc1.Recordset.Fields(3).Value)
Dim a As Double
Dim b As Integer
Dim c As Double
Dim d As Integer
time = Now()
a = CDbl(time - Adodc1.Recordset.Fields(6)) '精确时间
b = CInt(time - Adodc1.Recordset.Fields(6)) '对时间取整
c = a - b '计算出小于一天的时间
If c > 0 Then
d = b + 1 '不足一天算一天
Else
d = b
End If
e = d * price '计算住宿费
RichTextBox1.Text = vbCrLf + " " + "入住时间：" + Trim(Adodc1.Recordset.Fields(6).Value) + vbCrLf + vbCrLf + " " + "当前时间：" + Trim(time) + vbCrLf + vbCrLf + " " + "共住 " + CStr(a) + " 天" + " " + "计 " + CStr(d) + " 天" + vbCrLf + vbCrLf + " " + "住宿费：" + CStr(e) + "元 " + "押金：" + Trim(Adodc1.Recordset.Fields(10).Value) + "元 " + "应收：" + Trim(e - CInt(Adodc1.Recordset.Fields(10))) + "元"
End Sub
3、 房间管理界面

Private Sub Command7_Click()‘增加房间
Command7.Enabled = False
Command8.Enabled = False
Command9.Enabled = False
Command10.Enabled = True
Command11.Enabled = True
frame1.Enabled = True
frame1.Caption = "增加房间"
Text1.BackColor = &H80000005
Text2.BackColor = &H80000005
Text3.BackColor = &H80000005
Text4.BackColor = &H80000005
Text2.Visible = False
Combo3.Visible = True
Text4.Enabled = False
Text4.Text = "空"
Text1.Enabled = True
Text2.Enabled = True
Text3.Enabled = True
Text1.SetFocus
End Sub

Private Sub Command8_Click()‘修改房间
Command7.Enabled = False
Command8.Enabled = False
Command9.Enabled = False
Command10.Enabled = True
Command11.Enabled = True
frame1.Enabled = True
frame1.Caption = "修改房间"
Text1.Visible = False
DataCombo2.Visible = True
Text2.BackColor = &H80000005
Text3.BackColor = &H80000005
Text4.BackColor = &H80000005
Text4.Enabled = False
Text2.Enabled = False
Text3.Enabled = False
DataCombo2.SetFocus
End Sub

Private Sub Command9_Click()‘删除房间
Command7.Enabled = False
Command8.Enabled = False
Command9.Enabled = False
Command10.Enabled = True
Command11.Enabled = True
frame1.Enabled = True
frame1.Caption = "删除房间"
Text1.Visible = False
DataCombo2.Visible = True
Text2.BackColor = &H80000005
Text3.BackColor = &H80000005
Text4.BackColor = &H80000005
Text2.Enabled = False
Text3.Enabled = False
Text4.Enabled = False
DataCombo2.SetFocus
End Sub

Private Sub DataCombo2_Change()‘
Adodc5.RecordSource = "select * from ViewEmptyRoomInfo where Rnum='" + Trim(DataCombo2.Text) + " '"
Adodc5.Refresh
Set Text2.DataSource = Adodc5
Set Text3.DataSource = Adodc5
Set Text4.DataSource = Adodc5
Text2.Refresh
Text3.Refresh
Text4.Refresh
If frame1.Caption = "修改房间" Then
Set Combo3.DataSource = Adodc5
Combo3.Refresh
Combo3.Enabled = True
Combo3.Visible = True
Text2.Visible = False
Text3.Enabled = True
End If
End Sub

Private Sub Command10_Click()‘确定
If frame1.Caption = "增加房间" Then
If Text1.Text <> "" And Combo3.Text <> "" And Text3.Text <> "" Then
If MsgBox("确定增加房间“" & Text1.Text & "”吗？", vbOKCancel + vbInformation, "提示信息") = vbOK Then
Adodc5.RecordSource = "select * from ViewRoomInfo where Rnum='" & Text1.Text & "'"
Adodc5.Refresh
If Adodc5.Recordset.RecordCount = 1 Then
MsgBox "你输入的房间编号“" & Text1.Text & "”重复！请重新输入。", vbInformation + vbOKOnly, "提示信息"
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text1.SetFocus
Else
Adodc5.CommandType = adCmdText
Adodc5.RecordSource = "select * from RoomInfo"
Adodc5.Recordset.AddNew
Adodc5.Recordset.Fields("Rnum") = Text1.Text
Adodc5.Recordset.Fields("Rtype") = Combo3.Text
Adodc5.Recordset.Fields("Rprice") = Text3.Text
Adodc5.Recordset.Fields("Rstates") = Text4.Text
Adodc5.Recordset.Update
Adodc2.Refresh
Set DataGrid2.DataSource = Adodc2
DataCombo2.Refresh
Adodc6.Refresh
MsgBox "增加房间“" & Text1.Text & "”成功！", vbInformation + vbOKOnly, "提示信息"
Command11_Click
End If
Else
Command11_Click
End If
Else
MsgBox "房间信息填写不完整，请继续填写！", vbOKOnly + vbInformation, "错误提示"
End If

ElseIf frame1.Caption = "修改房间" Then
If DataCombo2.Text <> "" Then
If MsgBox("确定要修改房间信息吗？", vbOKCancel + vbInformation, "提示信息") = vbOK Then
Adodc5.CommandType = adCmdText
Adodc5.RecordSource = "updata RoomInfo set Rtype='" & Text2.Text & "',Rprice='" & Text3.Text & "' where Rnum='" & DataCombo2.Text & "'"
Adodc5.Recordset.Update
Adodc2.Refresh
Set DataGrid2.DataSource = Adodc2
DataCombo2.Refresh
Adodc6.Refresh
MsgBox "房间信息修改成功！", vbOKOnly + vbInformation, "提示信息"
Command11_Click
Else
Command11_Click
End If
Else
MsgBox "请选择要修改的房间！", vbInformation + vbOKOnly, "提示信息"
DataCombo2.SetFocus
End If
ElseIf frame1.Caption = "删除房间" Then
If DataCombo2.Text <> "" Then
If MsgBox("确定删除房间“" & DataCombo2.Text & "”吗？", vbOKCancel + vbInformation, "提示信息") = vbOK Then
Adodc5.CommandType = adCmdText
Adodc5.RecordSource = "delete from RoomInfo where Rnum='" & DataCombo2.Text & "'"
Adodc5.Recordset.Delete
Adodc5.Recordset.Update
Adodc2.Refresh
Set DataGrid2.DataSource = Adodc2
DataGrid2.Refresh
Adodc6.Refresh
MsgBox "删除房间“" & DataCombo2.Text & "”成功！", vbInformation + vbOKOnly, "提示信息"
Command11_Click
Else
Command11_Click
End If
Else
MsgBox "请选择要删除的房间！", vbOKOnly + vbInformation, "提示信息"
DataCombo2.SetFocus
End If
End If
End Sub

Private Sub Command11_Click()‘取消
Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Combo3.Text = ""
DataCombo2.Text = ""
frame1.Enabled = False
Command7.Enabled = True
Command8.Enabled = True
Command9.Enabled = True
Command10.Enabled = False
Command11.Enabled = False
frame1.Caption = ""
DataCombo2.Visible = False
Text1.Visible = True
Combo3.Visible = False
Text2.Visible = True
Text1.BackColor = &H8000000F
Text2.BackColor = &H8000000F
Text3.BackColor = &H8000000F
Text4.BackColor = &H8000000F
Text4.Enabled = True
Set Combo3.D