客房管理系统VBSQL样本.doc

数据库课程设计报告题目：客房管理系统时间：-6-27客房管理系统一、 课程设计目和意义在当今经济和商务交往日益频繁状况下，宾馆服务行业正面临客流量骤增压力。越来越多宾馆饭店都结识到老式管理办法已经不能适合当今社会需要，必要借助先进计算机信息技术对宾馆服务进行管理。“客房管理系统”可以说是整个宾馆计算机信息系统中心子系统，由于宾馆最重要功能就是为旅客提供客房。设计客房管理这样一种系统，可以涉及到大多数SQL Server数据库重要数据库对象、重要功能和特性，例如：视图、触发器和存储过程等。由此，通过这个课程设计可以加深对这些SQL Server数据库知识学习、理解，积累在实际工程应用中运用各种数据库对象经验，使学生掌握使用应用软件开发工具开发数据库管理系统基本办法。在实用性方面，客房管理系统是很普遍一种应用，选取该系统作为课程设计也可觉得学生后来也许遇到实际开发提供借鉴。本课程设计重要目是学习和练习SQL Server数据库实际应用，因此选取SQL Server 作为数据库服务器。二、 需求分析系统重要实现对客房管理功能。重要有管理员登陆、客户登记、房间管理、客户查询、房间查询、收入记录、管理员信息管理。系统总体设计图如下：三、 概念构造设计（E-R图）四、 逻辑构造设计将E-R图转换成关系模式：管理员（编号，账号，密码，权限）房 间（编号，类型，价格，状态，入住客户编号）客 户（编号，姓名，性别，年龄，身份证号，住址，入住时间，预测天数，房间编号，退房时间，押金，住宿费，登记人） 关系规范化：均已达到第三范式五、 数据实行与维护1、 建立数据库 打开SQL Server 公司管理器，建立数据库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、 房间管理界面Private 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 = Nothing