客房管理系统VB+SQL.doc

数据库课程设计报告题目：客房管理系统时间：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.GuestInfoFOR INSERT ASupdate 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 IfElseIf DataCombo1.Text = Then MsgBox 账号不能为空！, vbInformation + vbOKOnly, 错误信息 DataCombo1.SetFocusElse MsgBox 密码不能为空！, vbInformation + vbOKOnly, 错误信息 Text1.SetFocusEnd IfEnd SubPrivate Sub Command2_Click() 取消 EndEnd SubPrivate Sub Text1_KeyPress(KeyAscii As Integer) 在文本框按下输入的事件 If KeyAscii = 13 Then Command1_Click End IfEnd SubPrivate Sub dataCombo1_KeyPress(KeyAscii As Integer) 在文本框按下输入的事件 If KeyAscii = 13 Then Command1_Click End IfEnd Sub2、 客户登记界面Private Sub Command3_Click() 客户登记 Private Sub Command4_Click() 退房结算frmGuestAdd.Show frmCheckOut.ShowEnd Sub End SubPrivate Sub Command1_Click() 当前时间txt6.Text = Trim(Now()End SubPrivate 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 IfElse 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 IfEnd SubPrivate Sub Command3_Click() 取消Unload MeEnd SubPrivate Sub DataCombo1_Change() 选择房间Adodc2.RecordSource = select * from ViewEmptyRoomInfo where Rnum= + Trim(DataCombo1.Text) + Adodc2.RefreshSet DataGrid1.DataSource = Adodc2DataGrid1.RefreshEnd 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 IfEnd SubPrivate Sub Command2_Click() 费用结算Me.HidefrmPrice.ShowEnd SubPrivate Sub Command3_Click() 退出Unload MeEnd SubDim time As DateDim e As IntegerPrivate 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 MeElse Unload frmCheckOut Unload MeEnd IfEnd SubPrivate Sub Command2_Click()Unload frmCheckOutUnload MeEnd SubPrivate Sub Form_Load() 费用结算窗体加载事件Adodc2.RecordSource = select Rprice from ViewRoomInfo where Rnum= & frmCheckOut.DataCombo1.Text & 查询房间的价格Adodc2.RefreshDim 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.Refreshframe1.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 DoubleDim b As IntegerDim c As DoubleDim d As Integertime = Now()a = CDbl(time - Adodc1.Recordset.Fields(6) 精确时间b = CInt(time - Adodc1.Recordset.Fields(6) 对时间取整c = a - b 计算出小于一天的时间If c 0 Thend = b + 1 不足一天算一天Elsed = bEnd Ife = 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 Sub3、 房间管理界面41 / 41Private Sub Command7_Click()增加房间Command7.Enabled = FalseCommand8.Enabled = FalseCommand9.Enabled = FalseCommand10.Enabled = TrueCommand11.Enabled = Trueframe1.Enabled = Trueframe1.Caption = 增加房间Text1.BackColor = &H80000005Text2.BackColor = &H80000005Text3.BackColor = &H80000005Text4.BackColor = &H80000005Text2.Visible = FalseCombo3.Visible = TrueText4.Enabled = FalseText4.Text = 空Text1.Enabled = TrueText2.Enabled = TrueText3.Enabled = TrueText1.SetFocusEnd SubPrivate Sub Command8_Click()修改房间Command7.Enabled = FalseCommand8.Enabled = FalseCommand9.Enabled = FalseCommand10.Enabled = TrueCommand11.Enabled = Trueframe1.Enabled = Trueframe1.Caption = 修改房间Text1.Visible = FalseDataCombo2.Visible = TrueText2.BackColor = &H80000005Text3.BackColor = &H80000005Text4.BackColor = &H80000005Text4.Enabled = FalseText2.Enabled = FalseText3.Enabled = FalseDataCombo2.SetFocusEnd SubPrivate Sub Command9_Click()删除房间Command7.Enabled = FalseCommand8.Enabled = FalseCommand9.Enabled = FalseCommand10.Enabled = TrueCommand11.Enabled = Trueframe1.Enabled = Trueframe1.Caption = 删除房间Text1.Visible = FalseDataCombo2.Visible = TrueText2.BackColor = &H80000005Text3.BackColor = &H80000005Text4.BackColor = &H80000005Text2.Enabled = FalseText3.Enabled = FalseText4.Enabled = FalseDataCombo2.SetFocusEnd SubPrivate 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 IfEnd SubPrivate 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 IfElseIf 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 IfElseIf 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 IfEnd IfEnd SubPrivate Sub Command11_Click()取消Text1.Text = Text2.Text = Text3.Text = Text4.Text = Combo3.Text = DataCombo2.Text = frame1.Enabled = FalseCommand7.Enabled = TrueCommand8.Enabled = TrueCommand9.Enabled = TrueCommand10.Enabled = FalseCommand11.Enabled = Falseframe1.Caption = DataCombo2.Visible = FalseText1.Visible = TrueCombo3.Visible = FalseText2.Visible = TrueText1.BackColor = &H8000000FText2.BackColor = &H8000000FText3.BackColor = &H8000000FText4.BackColor = &H8000000FText4.Enabled = TrueSet Combo3.DataSource = NothingSet Text2.DataSource = NothingSet Text3.DataSource = NothingSet Text4.DataSource = NothingCombo3.RefreshText2.RefreshText3.RefreshText4.RefreshEnd Sub4、 客户查询界面Private Sub Command12_Click()精确查询-确定If txt1.Text = Thenck1.Value = 0End IfIf txt2.Text = Thenck2.Value = 0End IfIf txt3.Text = Thenck3.Value = 0End IfIf txt4.Text = Thenck4.Value = 0End IfIf txt5.Text = Thenck5.Value = 0End IfIf txt6.Text = Thenck6.Value = 0End IfIf txt1.Text = And txt2.Text = And txt3.Text = And txt4.Text = And txt5.Text = And txt6.Text = Then MsgBox 查询条件至少有一个不为空！, vbOKOnly + vbInformation, 错误信息Else Dim sql As String sql = Empty If txt1.Text Empty Then sql = (Cname = & txt1.Text & ) End If If txt2.Text Empty Then If sql Empty Then sql = sql & and (Csex = & txt2.Text & ) Else sql = (Csex = & txt2.Text & ) End If End If If txt3.Text Empty Then If sql Empty Then sql = sql & and (Cage = & Trim(txt3.Text) & ) Else sql = (Cage = & Trim(txt3.Text) & ) End If End If If txt4.Text Empty Then If sql Empty Then sql = sql & and (Cid = & Trim(txt4.Text) & ) Else sql = (Cid = & Trim(txt4.Text) & ) End If End If If txt5.Text Empty Then If sql Empty Then sql = sql & and (Rnum = & Trim(txt5.Text) & ) Else sql = (Rnum = & Trim(txt5.Text) & ) End If End If If txt6.Text Empty Then If sql Empty Then sql = sql & and (Cpnum = & txt6.Text & ) Else sql = (Cpnum = & txt6.Text & ) End If End If If sql Empty Then sql = select * from ViewGuestInfo where & sql End If Adodc10.RecordSource = sql Adodc10.Refresh Set DataGrid3.DataSource = Adodc10 DataGrid3.Refresh Frame2.Caption = 查询结果-精确查询End IfEnd SubPrivate Sub Command13_Click()清空ck1.Value = 0ck2.Value = 0ck3.Value = 0ck4.Value = 0ck5.Value = 0ck6.Value = 0txt1.Text = txt2.Text = txt3.Text = txt4.Text = txt5.Text = txt6.Text = End SubPrivate Sub Command15_Click()概况查询-确定If Combo1.Text = Then MsgBox 条件不能为空！请选择查询条件。, vbOKOnly + vbInformation, 提示信息 Combo1.SetFocusElse Select Case Combo1.Text Case 已入住客户 Adodc11.RecordSource = select * from ViewGuestInfo where Couttime is null Adodc11.Refresh Set DataGrid3.DataSource = Adodc11 DataGrid3.Refresh Case 已退房客户 Adodc11.RecordSource = select * from ViewGuestInfo where Couttime is not null Adodc11.Refresh Set DataGrid3.DataSource = Adodc11 DataGrid3.Refresh Case 普通房客户 Adodc11.RecordSource = select * from Vie